Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Posts
    302
    Thanks
    68
    Thanked 8 Times in 8 Posts

    How to insert and compare VARBINARY items?

    Let's say I have a table like this, which contains data (format is irrelevant):

    Code:
    create table datastore ( rawdata varbinary(22) );
    First, I need to insert some data, stored in a PHP variable $rawdata into the database. Something like:

    PHP Code:
    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:

    PHP Code:
    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?

    Last edited by XmisterIS; 07-05-2011 at 04:41 PM.

  • #2
    Regular Coder
    Join Date
    Jun 2011
    Posts
    103
    Thanks
    0
    Thanked 13 Times in 13 Posts
    look here

  • #3
    Regular Coder
    Join Date
    Jun 2010
    Posts
    302
    Thanks
    68
    Thanked 8 Times in 8 Posts
    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:

    PHP Code:
    $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!

  • #4
    Regular Coder
    Join Date
    Jun 2011
    Posts
    103
    Thanks
    0
    Thanked 13 Times in 13 Posts
    not 100% sure on what you mean, however the ord() function translates data into ASCII characters.

  • #5
    Regular Coder
    Join Date
    Jun 2010
    Posts
    302
    Thanks
    68
    Thanked 8 Times in 8 Posts
    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:

    PHP Code:
    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 ...
    Last edited by XmisterIS; 07-06-2011 at 08:45 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •