Webyog Forums > Field Says Binary/image, But It's Not..?

Full Version: Field Says Binary/image, But It's Not..?

Webyog Forums > SQLyog > SQLyog BETA Discussions

nicmar

Apr 25 2006, 02:54 PM

Some rows in my database are called "Binary/Image" and i can't see the actual data.
When I export to Excel it looks like this:

2006-03-20 1505-1535 hos John Doe (Påminn=0)\0

What's binary about this and why can't I view it in SQLYog?

I'm using 5.1 Beta 6 currently..

peterlaursen

Apr 25 2006, 04:11 PM

this term "Binary/Image" is not very precise any more. It means that it is a datatype that cannot be displayed in the grid for one of the reasons:

1) It is binary data, that cannot be represented in a meaningful way using characters
2) It is too long to 'fit in'

1) is BLOBs (used for images, media files etc)
2) is TEXTs (used for character data too long to be char or varchar)

Plan for the future include:
"Enhancements to the BLOB-viewer: BLOB-viewer should be resizable and there should be an option to display binary data in binary/hex view (like common hex-editors and the Windows Regedit program). Also support for the MySQL version 5 BIT datatype should be implemented. Finally it also should be considered to use (could be optional) the BLOB-viewer for very big VARCHARs. This is actualized by MySQL version 5, that allows for VARCHAR size of 65000+ characters."

I think you may have a TEXT column and not a CHAR/VARCHAR column?

Ritesh

Apr 25 2006, 11:58 PM

QUOTE

2006-03-20 1505-1535 hos John Doe (Påminn=0)\0

If you see there is an extra \0 in the data which means an extra NULL character in the data. You will need to strip that out. SQLyog identifies whether a data is BINARY or not by checking for a NULL character till the length sent by MySQL and there is indeed a NULL. I dont know how it came but once you remove it, it will solve the issue.

peterlaursen

Apr 26 2006, 12:15 AM

aha .. Ritesh caught the point here .. :-)
Now even I understand the question!
I'll add an example:

You can export .sql file, edit it and import. Have a look at attached file.

The SQL-export for this looks like:

CODE

/*
SQLyog Enterprise - MySQL GUI v5.12 BETA
Host - 5.1.9-beta-log : Database - test
*********************************************************************
Server version : 5.1.9-beta-log
*/

SET NAMES utf8;

SET SQL_MODE='';

create database if not exists `test`;

USE `test`;

/*Table structure for table `tablename1` */

DROP TABLE IF EXISTS `tablename1`;

CREATE TABLE `tablename1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `t` varchar(50) DEFAULT NULL,
  `vb` varbinary(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `tablename1` */

insert into `tablename1` (`id`,`t`,`vb`) values (1,'hos John Doe (PÃ¥minn=0)\\0','hos John Doe (Påminn=0)\0');

Note the difference between '\ \0' and '\0' in the SQL-file for the two columns
First column has an escaped \ followed by '\0'. This is valid for '\0'. '\0' alone implies that data are binary (not character data)

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.