It looks like the sql server is running v 10.0.1600.
I've tried this in multiple tables and multiple dbs, all on the same sql server, using a modified version of the code from the DBD::Sybase documentation. One of my attempts is shown in the node referenced in my OP: sql2008 Filestream. The current table configuration in this example has col 1 varchar(40), col 2 datetime, col 3 uniqueidentifier, col 4 varchar(50), and col 5 varbinary(MAX) where col 5 is the column I'm trying to use as the blob column.
My latest attempt is on a different db, same server, into col 7 where the table is as follows: col 1 int, col 2, int, col 3 varchar(50), col 4 money, col 5 varchar(20), col 6 bit, col 7 image, col 8 varchar(MAX).
my $imagefile = $cgi->upload('image');
my $image;
while(<$imagefile>) { $image .= $_; }
my $binary = pack("H*", $image);
warn length($binary);
#my $size = length($image);
my $size = length($binary);
warn $size;
#according to documentation, "ct_data_info(CS_GET) will fail if the IM
+AGE/TEXT data for which the CS_IODESC is being fetched is NULL."
+
$dbh->do("UPDATE item SET image = '' WHERE id = '".$cgi->param('id').
+"' and image is null");
# first we need to find the CS_IODESC data for the data
my $sth = $dbh->prepare("select image from item where id = ?");
$sth->execute($cgi->param('id'));
while($sth->fetch) { # don't care about the data!
$sth->syb_ct_data_info('CS_GET', 1);
}
# OK - we have the CS_IODESC values, so do the update:
$sth->syb_ct_prepare_send() ;
# Set the size of the new data item (that we are inserting), and make
# the operation unlogged
$sth->syb_ct_data_info('CS_SET', 1, {total_txtlen => $size, log_on_upd
+ate => 0});
# now transfer the data (in a single chunk, this time)
# $sth->syb_ct_send_data($binary, length($binary));
# $sth->syb_ct_send_data($image, length($image));
# open the file, and store it in the db in 1024 byte chunks.
while($size){
my $to_read=$size>1024 ? 1024 : $size;
my $buff=substr($binary,0,$to_read,'');
# my $buff=substr($image,0,$to_read,'');
my $bytesread=length $buff;
# my $bytesread=read(IN,$buff,$to_read);
$size-=$bytesread;
# $size-=length $buff;
warn "sending data $bytesread $size";
$sth->syb_ct_send_data($buff, $bytesread) ;
}
# commit the operation
$sth->syb_ct_finish_send() ;
(Forgive the obvious sql injection problems. I plan on fixing this once I can get the d@mned thing to work!)
In all my attempts so far, I get the identical error as I posted in sql2008 Filestream and with the poster in inserting images in ms sql server.
DBD::Sybase::st syb_ct_send_data failed: Server message number=102 sev
+erity=15 state=1 line=1 server=SQL2008\SQL2008
text=Incorrect syntax near '0x00000000000000000000000000000000'.
I've been able to successfully insert/update blobs from windows scripts to this server on the same tables, but haven't had any luck with the FreeTDS/mssql combination.
Pray tell, WHICH drivers work?
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.