...

View Full Version : How to insert and compare VARBINARY items?



XmisterIS
07-05-2011, 04:34 PM
Let's say I have a table like this, which contains data (format is irrelevant):


create table datastore ( rawdata varbinary(22) );

First, I need to insert some data, stored in a PHP variable $rawdata into the database. Something like:


mysql_query("insert into datastore set rawdata = '$rawdata'", $connection);

Now, let's say I read in a binary data set from somewhere and stick in in a PHP variable, call it $newdata.

I'd like to be able to see if the data already exists in the table by constructing a query in PHP along the lines of:


mysql_query("select count(0) from datastore where rawdata = '$newdata'", $connection);

Of course this doesn't work, because $rawdata and $newdata are both binary and this confuses MySQL because it's expecting them to be strings.

So how can I tell MySQL that I want to insert and compare two binary things rather than two strings?

:confused:

Fugix
07-05-2011, 06:05 PM
look here (http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html)

XmisterIS
07-05-2011, 08:33 PM
Hi Fugix, thanks for that. I had loked at that page and tried to go on what was there, but I think I'm just being unbelievably stupid and missing something here ...

In the example given, they use "\0" to represent NULL, so going along the same lines, I tried:

insert into datastore set rawdata = '\1\2\3\4\5\6\7\10'. I assume that the backslash indicates octal? Hence I'm going from \7 to \10. Or perhaps it's not octal? I'm not sure, I'm floundering around here trying to figure it out!

Then I wrote the following bit of PHP to test it:



$result = mysql_query("select rawdata from datastore");
$row = mysql_fetch_assoc($result);
$data = $row["rawdata"];

for ($i=0; $i<8; $i++)
echo ord($data{$i})." ";

Unfortunately, instead of giving me:

1 2 3 4 5 6 7 8

I got

49 50 51 52 53 54 55 49

which are just ascii characters "1", "2", etc.

So I guess I'm just being very very stupid and I'm missing one little crucial thing here but I don't know what it is!

Fugix
07-05-2011, 08:41 PM
not 100% sure on what you mean, however the ord() function translates data into ASCII characters.

XmisterIS
07-06-2011, 08:32 AM
Yes, I know ord translates strings to their ascii value euqivalents - which in the case given above would be the non-printing characters 1 through 8 (which incidentally are SOH, STX, ETX, EOT, ENQ, ACK, BEL, BS).

What I'm trying to do is insert binary data into the database (in this case the byes 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07 and 0x08) and then retrieve them. But what is happening is that instead of the bytes with values 1 to 8 being inserted, MySQL is inserting the ASCII characters "1", "2", etc.

I need to insert just plain, unmodified binary but I can't seem to be able to do this without MySQL converting those bytes to the ASCII character representations of the digits in the byte values (e.g. instead of inserting 0x01, MySQL is reading and inserting the string "1", which is not 0x01, it's 0x31).

EDIT: Ah! I am getting somewhere with this now after having slept on it (I was tired and frustrated with it last night!)

By trial and error I've found that this seems to work:



mysql_query("delete from datastore"); //Make sure there's only one test row.
mysql_query("insert into datastore set rawdata = concat(0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08)"); // *** THIS IS THE MAGIC LINE ***
$result = mysql_query("select rawdata from datastore");
$row = mysql_fetch_assoc($result);
$data = $row["rawdata"];

for ($i=0; $i<8; $i++)
echo ord($data{$i})." ";

This gives the result:

1 2 3 4 5 6 7 8

Ok, so now I have a way of inserting raw binary data using concat.

BUT, that will result in one hell of a big old INSERT statement when my dataset is large (e.g. several Kb). So I'm still looking for something better than concat ...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum