SMF 2.0.X varbinary data

[butchs]

I created a table with a varbinary column.  My attempts at using "$smcFunc['db_insert']('insert',..." to save the binary data appear to be in vein.  It appears that SMF seems to only support int, string, text, array_string, float, identifier and raw.

What is the best way to insert the binary data into a SMF DB?

[emanuele]

If it works with insert (honestly I never tried), I'd say raw.

[butchs]

I am wrestling with a few thousand lines of code for ipv6 support.  It worked in php before putting in SMF now I am debugging in SMF.  The only part I did not test before transferring the code over was DB insertion.  By this point the ip has been tested as valid.  I hate to binhex the string first and waste processor time if I can go direct....  Maybe I am inserting into the DB correctly and need to find a bug elsewhere?  I am not sure since binary is not a default type.

Here is the DB...

Code Select Expand

$smcFunc['db_create_table']($db_prefix.'log_ff_challenges',
array(
array(
'name' => 'id',
'type' => 'int',
'size' => 11,
'null' => false,
'default' => '',
'auto' => true,
'unsigned' => true,
),
array(
'name' => 'ip',
'type' => 'varbinary',
'size' => 39,
'null' => false,
'default' => '',
'auto' => false,
'unsigned' => false,
),
array(
'name' => 'date',
'type' => 'varchar',
'size' => 19,
'null' => false,
'default' => '0000-00-00 00:00:00',
'auto' => false,
'unsigned' => false,
),
array(
'name' => 'result',
'type' => 'varchar',
'size' => 255,
'null' => false,
'default' => '',
'auto' => false,
'unsigned' => false,
),
),
array(
array(
'type' => 'primary',
'columns' => array('id')
),
array(
'type' => 'index',
'columns' => array('ip'),
'size' => 40,
),
),
'ignore'
);


I tried insert below but it did not seem to work....

Code Select Expand

$request = $smcFunc['db_insert']('insert',
'{db_prefix}log_ff_challenges',
array(
'ip' => 'raw',
'date' => 'string',
'result' => 'string',
         ),
array(
$forumfirewall_ip,
$date,
$result,
),
array()
);

This is my next attempt (untested - waiting for a rainy day)...

Code Select Expand

$smcFunc['db_query']('', '
REPLACE INTO {db_prefix}log_ff_challenges
SET
ip = {raw:ip},
date = {string:date},
result = {string:result}',
array(
'ip' => $forumfirewall_ip,
'date' => $date,
'result' => $result,
)
);

[emanuele]

hmm... dunno...
Do you need to escape varbinary in any way?

[butchs]

It seems my test server does not use the varbinary type.  Instead I used the TINYBLOB with null size and 'ip' => 'string'.  All appears to be working for now.

Thanks for the help.