Jump to content


Photo

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


  • Please log in to reply
4 replies to this topic

#1 nicmar

nicmar

    Advanced Member

  • Members
  • PipPipPip
  • 44 posts

Posted 25 April 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..

#2 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 25 April 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?
Computers make your grey hair come off ....

Peter Laursen
Webyog

#3 Ritesh

Ritesh

    Advanced Member

  • Members
  • PipPipPip
  • 2,539 posts

Posted 25 April 2006 - 11:58 PM

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.
Ritesh

#4 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 26 April 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:

/*
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)

Attached Files

  • Attached File  abc.jpg   22.84KB   61 downloads

Computers make your grey hair come off ....

Peter Laursen
Webyog

#5 Al Jey

Al Jey

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 27 August 2010 - 08:26 AM

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.

Ritesh,
You rock dude, you just saved my life (and I mean it), TRIM(TRAILING '\0' FROM columnName) totally did the trick for me; I've been fiddling with the damn thing forever... :D :D :D




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users