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
