I hate ODBC
Many people don’t like the idea of putting binary files in a database. I on the other hand have always enjoyed having all data, textual and binary, in the same place. What better than having it all nicely tucked away in a comfy database. The site is handled by the version handling system and the data is backed up in the normal db backup routine. But when the system is a RedHat 7.3 using ASP mixed with PHP with ODBC connections to an MS SQL Server I really start to grind my teeth.
I didn’t want to rewrite things for free. So when I took the site onboard I only had to rewrite the actual file uploading. As it is using SUN One ASP (former Chilisoft ASP) I had to use the Chili component for this. It all went smoothly until I tried to put the uploaded data into the database. For some reason I just couldn’t get the Recordset to be updatable.
Set rs1 = Server.CreateObject("ADODB.Recordset")
sql = "select id, prodpic from product where id = '" & ref & "'"
rs1.Open sql, conn, 3, 3
rs1.Fields("bild").appendChunk picturechunk
rs1("bild").value = fblob
rs1.Update
As I don’t really like, or know, ASP (I haven’t touched VB since VB4 was new and Microsoft said it was as fast as compiled C++) I decided that the quickest way out of this would be to convert the script to PHP. As the application is a horrible mix already I figured it wouldn’t make things worse. That should do it. No problem at all.
Well it was a problem. ODBC has this limitation on the size of an SQL statement. You can only send through SQL statements with a maximum of 4096 bytes (or something like that). I knew this and I also knew you solve it by using a combination of the methods odbc_prepare and odbc_execute. You have two different ways of sending binary data this way. Well the ODBC driver (Free TDS) puked on me again and again. Go figure.
Then as the genius I am I said to myself. Chunks, concatenation. Yeah. Something like below should work. I’ve done the same with MySQL queries in some circumstances.
$fdata = base64_encode(get_file_contents($filepath));
$total = strlen($fdata);
$pos = 0;
while ($pos < $total)
{
$len = ($pos + 2048 > $total) ? $total - $pos : 2048;
$substr = substr($fdata, $pos, $len);
$sql = "update product set prodpic = prodpic + '$substr' where id='$ref'";
db_exec($sql);
$pos += $len;
}
After puzzling awhile over the weird results this gave I turned to the almighty web for answers. It turns out MS SQL Server only supports concatenation of datatypes not capable of storing large amounts of data. So for the datatypes image and text it is impossible. Wonderful.
So now I just save the files on the filesystem instead. Have to setup backup routines for the machine though. Something I thought would take about an hour to fix finally took more than two days of coding and research.
Hopefully I learn some valueable lesson from all of this but I’m sure I won’t.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
