0

I have a small problem and i'm sure someone have the solution for it I have a binary data converted from a set of unsigned integers

$_BIN=pack('I*', 3563547,6587568,5468456,6458568,4568568);

and i want to insert it in a table that contain a varbinary column

$strSQL = "INSERT INTO table_name (id, ba) VALUES($id, $_BIN)";

for some reason i get the following error

Erreur SQL : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_' at line 1 SQL string : INSERT INTO table_name (id, ba) VALUES(467, ('ùZ¢O ^,¦EòÇjÞ|²EÎóÇjÞ|²EÎóp>)

Hoping you guys can help me out

Gibnem
  • 675
  • 1
  • 8
  • 9

1 Answers1

1

This works for me:

DROP TABLE IF EXISTS `binary`;
CREATE TABLE `binary`(
id INT NOT NULL AUTO_INCREMENT, 
ba VARBINARY( 100 ),
PRIMARY KEY(`id`)
) ENGINE=MYISAM;

and PHP code (using PDO):

<?php
$db = new PDO('mysql:dbname=test;host=localhost:4040','xxx','xxxx');

$_BIN=pack('I*', 3563547,6587568,5468456,6458568,4568568);

$rs = $db->prepare("INSERT INTO `binary`(`ba`) VALUES(?)");

$rs->execute(array($_BIN));

UPDATE

See below the code based on your suggested one with named parameters:

<?php
$_SNB = 23;
$_USR = "toto";
$_BIN = pack('I*', 24325, 2556456, 547577, 675746, 535646, 4564575, 575474, 4735);

$db = new PDO('mysql:host=localhost:4040;dbname=test','xxxx','xxxx');
$db->exec('SET CHARACTER SET utf8');
$rs = $db->prepare("INSERT INTO `spw_license` (`serial_num`, `user`, `ba_struct`) 
                  VALUES(:serial_num, :username, :ba_struct)");

$rs->bindValue(':serial_num', $_SNB, PDO::PARAM_INT);
$rs->bindValue(':username', $_USR);
$rs->bindValue(':ba_struct', $_BIN, PDO::PARAM_LOB);

try {
    if(!$rs->execute()) {
        var_dump($db->errorInfo());
    }
} catch(Exception $e) {
    echo 'got Exception: ' . $e->getMessage() . PHP_EOL;
}

Here is how the table looks like after this is launched:

mysql> select * from `spw_license`;
+------------+------+----------------------------------+
| serial_num | user | ba_struct                        |
+------------+------+----------------------------------+
|         23 | toto | ♣_  (☻' ∙ вO
 ^ _жE Є ⌂↕   |
+------------+------+----------------------------------+
1 row in set (0.00 sec)
Alexey
  • 3,414
  • 7
  • 26
  • 44
  • many thanks Alexy but in all the rest of the code i'm not working with PDO i don't know if it is really the best thing to do now and change all the rest. I guess i need to do in MySql – Gibnem Mar 26 '13 at 10:56
  • then try to $strSQL = spintf("INSERT INTO table_name (id, ba) VALUES($id, '%s')",mysql_real_escape_string($_BIN)); – Alexey Mar 26 '13 at 10:58
  • what was the error? Can you install mysql-proxy and connect through it and udate your question with the query as you see it in mysql-proxy? – Alexey Mar 26 '13 at 16:43
  • Hi Alexy, I tried writing my queries using the PDO but what i got on the database is an hex representation of my data on the table column and not real binary data – Gibnem Mar 26 '13 at 18:53
  • did you check that it's really hex with mysql CLI client? Or any other client? And what if you do select in php PDO (same script), will it be hex or not? – Alexey Mar 26 '13 at 18:55
  • Hi Alexey, i'm giving more details on my problem on the following post Many thanks for all – Gibnem Mar 27 '13 at 12:10