RE: Inserting BLOB data into a MS SQL Database

Jeff Urlwin Wed, 06 Mar 2002 04:54:03 -0800

I used files of 8K, 150K, 55K. With only the 8K file the tests produced different results when using the 150K file... so be careful.

Updated my test to include reading back the values and test with the MD5 Digest (which is something you should do before using your code!!!! I was surprised at what I found!). Notes:

Oracle and SQL Server work with the [new] bind type detection (hence it's used here)

Access, didn't, so I had to bind it as SQL_LONGVARBINARY.

Oracle liked SQL_BLOB, but Access didn't.

I forget, now, what SQL Server liked...but the auto detection of the type triggered by the $dbh->{odbc_default_bind_type} = 0; works...

FYI -- I expect that a near-future version of DBD::ODBC will make the bind type detection the default. I don't think it will break much, if any, code...

Regards,

Jeff

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jeff Urlwin
Sent: Wednesday, March 06, 2002 7:39 AM
To: Matthew J. Orifici; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Inserting BLOB data into a MS SQL Database

[ 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

RE: Inserting BLOB data into a MS SQL Database

Reply via email to