RE: Inserting BLOB data into a MS SQL Database

[Jeff Urlwin]

[ Cross posted to dbi-users, as the long insert code might be handy for others ]   Matthew,   There are a few things I'm noticing: 1)    are you using DBI qw(:sql_types)?     What's the value of SQL_BINARY (just in case);     Normally, I use the value of: DBI::SQL_BINARY...     hint: run with "perl -w" and use strict!  That will tell you if SQL_BINARY is a value or a problem...     (this is probably at least a contributing factor)   2)    What is the column type on the table where you are inserting? Have you tried binding with SQL_LONGVARBINARY instead of SQL_BINARY?  I don't see in the code where it would make much of a difference to DBD::ODBC, but it may to MS SQL Server.    3) With later versions of DBD::ODBC and MS SQL Server, you may be able to do this (but that probably doesn't work with Oracle):     $dbh = DBI->connect()     $dbh->{odbc_default_bind_type} = 0;     ...     $sth = $dbh->prepare( etc );     $sth->bind_param(1, $value); # note, no binding type!   See the attached example, which uses DBI_DSN, DBI_USER, DBI_PASS environment variables for connection, user id and password.  Either set those environment variables or change the connect call.  You'll have to change the create table.  This worked for me with Oracle, SQL Server and Access.  If the create table mess gets in your way, you can change it as you want.  If get_info is not supported with your DBI, etc, just change all that to create the table you want.  It reads the file names on the command line and puts them into the test table...   If that doesn't work, if you could send a small, self contained sample, size of your images, etc., that would be helpful.     (also, I would like to know the type of column you are using, if different from 'image')   Thanks,   Jeff  -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Matthew J. Orifici Sent: Tuesday, March 05, 2002 3:48 PM To: [EMAIL PROTECTED] Subject: Inserting BLOB data into a MS SQL Database I am trying to insert uploaded images and pdf files into an image field in a MS SQL database on a Win 2000 server.   I set my dbh->{LongReadLen} = 1000000;  and dbh->{LongTruncOk} = 1;   That is supposed to apply only to Select statements but I put it in anyway. I am getting the following error:       *** 'E:\TEK-Client-Websites \CC-Admin-New-Item-Submit.aspl' log message at: 2002/03/05 14:20:54 [Tue Mar  5 14:20:54 2002] -e: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]String data, right truncation (SQL-22001)(DBD: st_execute/SQLExecute err=-1) at E:\TEK-Client-Websites \CC-Admin-New-Item-Submit.aspl line 174. Precompiler: Execute Error:             Filename:[E:\TEK-Client-Websites \CC-Admin-New-Item-Submit.aspl]             Package:[PerlEx::Precompiler::e_::tek2dclient2dwebsites::www_cc2dsales_com::dataadmin::cc2dadmin2dnew2ditem2dsubmit_aspl] Params[] Could not execute SQL statement, maybe invalid? at E:\TEK-Client-Websites \CC-Admin-New-Item-Submit.aspl line 174.   The “String data, right truncation” is the root of the issue and I can’t get around it.   Here is a snippet of the code in question:     my $sql = "INSERT INTO t_Items (f_SubCat,f_PartNo,f_Name,f_ItemDescription,f_Picture,f_Icon,f_PDF,f_EmbedPicture,f_EmbedIcon,f_EmbedPDF) VALUES ($subcatid,'$partnumber','$partname','$partdescription','$picturetype','$icontype','$pdftype',?,?,?)"; my $sth = $CCSALESInsert::dbh->prepare($sql) || die "Couldn't prepare statement: " . $dbh->errstr;   $sth->bind_param( 1, $picture, SQL_BINARY); $sth->bind_param( 2, $icon, SQL_BINARY); $sth->bind_param( 3, $pdf, SQL_BINARY);   $sth->execute() || die "Could not execute SQL statement, maybe invalid?"; $sth->finish || die "Could not finish query";                If anyone can help I would be in their debt. I have been stuck on this for 2 days and I am getting no where.   Thanks,   Matt

longbin.pl
Description: Binary data