Hey, thanks for that!
Dan
Michael Peppler <[EMAIL PROTECTED]>
06/07/2004 17:57
To: [EMAIL PROTECTED]
cc: (bcc: Dan Horne/IT/AKLWHG/WHNZ)
Subject: [SPAM]* Re: DBD::Sybase and Text problems
On Tue, 2004-07-06 at 04:39, [EMAIL PROTECTED] wrote:
> My problem arose because I was trying to prepare a SQL INSERT statement
> with placeholders, where one of the columns is a TEXT datatype (using
> DBD::Sybase). As the error message pointed out, "This is illegal in a
> dynamic PREPARE statement."
>
> So I'd like to know what the alternatives are. As far as I can
understand
> from the documentation, an UPDATE to a TEXT column requires a select
(with
> CS_GET) followed by a CS_SET. However, I wish to do an insert., so I'm
> not quite clear what is required.. Can anyone provide some pointers ,
> please?
If the TEXT column isn't too large you can insert it directly by
interpolating it into the INSERT statement after passing it through
$dbh->quote() ("large" here depends on how much memory you have, etc.)
If you want to use the Sybase BLOB write interface, then you need to
first insert the row that will have the TEXT column with NULL or an
empty string for the TEXT data. Then go through the "update a text
column with ct_send_data" procedure, which goes like this (paraphrased
from t/xblob.t):
# Insert the row with an empty text/image column:
$dbh->do("insert blob_test(id, data) values(1, '')");
# Now we need to fetch the internal information (CS_IODESC, in the
# Sybase API docs) for this data item
my $sth = $dbh->prepare("select id, data from blob_test");
$sth->execute;
while($sth->fetch) {
$sth->func('CS_GET', 2, 'ct_data_info') || print $sth->errstr, "\n";
}
$sth->func('ct_prepare_send') || print $sth->errstr, "\n";
# Tell the API that you want to write length($image) bytes, and that you
# want the write to be logged):
$sth->func('CS_SET', 2, {total_txtlen => length($image),
log_on_update=>1}, 'ct_data_info') || print $sth->errstr, "\n";
# Send the data ($image) to the server - this will write the content of
# $image to the "data" column of the row we just inserted
$sth->func($image, length($image), 'ct_send_data') || print
$sth->errstr, "\n";
$sth->func('ct_finish_send') || print $sth->errstr, "\n";
# All done.
Yes, it is incredibly convoluted. At some point I may rework this so
that it looks better...
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html
***************************************************************************
This eMail may contain privileged and confidential information intended
only for the use of the intended recipient. If you are not the intended
recipient of this message, any use, dissemination, distribution or
reproduction of this message is prohibited. Any views expressed in this
message are those of the individual sender and may not necessarily reflect
the views of APN News & Media NZ Ltd.
For more information on APN News & Media NZ Ltd please visit our
web site at http://www.apn.co.nz
***************************************************************************
