Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Lee Wenzler <Lee dot Wenzler at Roanoke dot com>
Subject: blob image data via DBD::Sybase
Date: Apr 25 2003 5:16PM

Hello all:

I'm using Sybperl on Sybase 12 (no problems on that side) and also
DBD::Sybase (version 0.94) to connect to a SQL 2000 server. I'm writing a
script that transfers print published material from a Sybase db to an SQL
2000 web publication db. Using the two modules have worked fine for
transferring text data, but now that I'm trying to add jpeg blob images from
Sybase and send them to SQL 2000, it looks like the DBD::Sybase module sees
the image data as a character string and barks because of the quotes. Here's
the first method I've used:

... $graphic gets filled with a graphic (I know that's working, I can write
it to a file and it displays fine in a browser).

$ms_dbh->do("if not exists (select * from graphicTable where someGraphicId =
   INSERT INTO graphicTable (someGraphicId, someStoryId, graphic)
	values ($someGraphicId, $someStoryId, 'placeholder')
   or die DBI->errstr;   
$query = qq(
	declare \@ptrval binary(16)
	select \@ptrval = TEXTPTR(graphic) from graphicTable where
someGraphicId = $someGraphicId
	  writetext graphicTable.graphic \@ptrval $graphic
$ms_dbh->do($query) or die DBI->errstr;  

This ends up erroring out with an SQL error: 
	Incorrect syntax near 'xxxx'.

I've tried quoting the $graphic variable with ms_dbh->quote($graphic), with
no help. I then get a missing quote error from SQL. If I run the $graphic
thru the Dblib dbsafestr($graphic), it will make it thru the SQL, but the
data is only the first four "JFIF" characters of the jpeg.

To eliminate possibility of SQL 2000 being the problem, I changed the
DBD::Sybase connector to log on to another Sybase 12 server and I get the
same results.

Next, I tried a completely different way to upload the graphic, using the
description from perldoc DBD::Sybase:

# first we need to find the CS_IODESC data for the data
$sth = $ms_dbh->prepare("select graphic from graphicTable where
someGraphicId = $someGraphicId");
while($sth->fetch) {    # don't care about the data!
	$sth->func('CS_GET', 1, 'ct_data_info');
# OK - we have the CS_IODESC values, so do the update:
# Set the size of the new data item (that we are inserting), and make
# the operation unlogged
$sth->func('CS_SET', 1, {total_txtlen => length($graphic), log_on_update =>
0}, 'ct_data_info');
# now transfer the data (in a single chunk, this time)
$sth->func($graphic, length($graphic), 'ct_send_data');
# commit the operation

When I try that, I get a core dump after the $sth->func('ct_prepare_send')
trys to execute. Hope that's not because of the 0.94 version, because when I
try the latest DBD::Sybase 1.00, the connection fails with this:

Unable for connect to SOMESERVER: Server message number=18456 severity=14
state=1 line=0 text=Login failed for user 'leew'.OpenClient message: LAYER =
(4) ORIGIN = (1) SEVERITY = (4) NUMBER = (44).

I've also tried to use the ODBC (is the O for obfuscated?), but so far it
hasn't worked.

Thanks for any suggestions.

Lee Wenzler
Publishing Systems Manager
The Roanoke Times
(540) 981-3322