Home: Perl Programming Help: DBI:
Updating text column in Sybase



dave5#
New User

Sep 5, 2002, 6:06 AM


Views: 6259
Updating text column in Sybase

Can anyone throw any light on this for me please???

I am trying to send an update SQL command to change the contents of a table called entity which has columns :

partition_no smallint 2
idx numeric 9
entity_type smallint 2
msg_data text 16


The command is submitted via $dbh->ct_execute($sql);

where

$sql =update adm_del..entity set msg_data = '<?zzz zzzzzzz="1.0" zzzzzzzz="zzz-8859-1"?><zzzzzzzzzz zzzzzzzz="zzzzzzz" zzzzzz="zzzzzz"><zzzzzzzzz zzzz="zzzzzzzz" zzz="z" zzz="1"><zzz zzz="z" zzz="1" zz="1.62198"/><zzzzzzzzzz z="zzzzzzzz" zzz="z" zzz="1"/><zzzzzzzzzzzzz zzz="z" zzz="1" zz="1"/><zzzzzzzz zzz="z" zzz="1" zz="zzzzz"/><zzzz zzz="z" zzz="1" zz="zzz zzz zzzzz" zzzzz="zzzz"/><zzzzzzzzzzzz z="60152" zzz="z" zzz="1" zzzzzzzzzzzzzzzz="zzzzz" zzz="1.62198"><zzzzzzzz z="zzzzz"/><zzzzzzz z="1"/></zzzzzzzzzzzz><zzzzz zzz="z" zzz="1"><zzz zzz="z" zzz="1" zz="60152"/><zzzzzzzz zzz="z" zzz="1" zz="zzzzz"/><zzzzzzz zzz="z" zzz="1" zz="1"/><zzzz zzz="z" zzz="1" zz="2.10001" zzzzz="zzzz"/></zzzzz></zzzzzzzzz></zzzzzzzzzz>' where idx=62198 and entity_type=13

I have changed all the meaningful / sensitive data to z's.

I get the error:

Server message:
Message number: 105, Severity 15, State 1, Line 1
Server 'LONOTGDEVSYB2'
Message String: Unclosed quote before the character string '<?zzz zzzzzzz="1.0" zzzzzzzz="zzz-8859-1"?><zzzzzzzzzz zzzzzzzz="zzzzzzz" zzzzzz="zzzzzz"><zzzzzzzzz zzzz="zzzzzzzz" zzz="z" zzz="1"><zzz zzz="z" zzz="1" zz="1.62198"/><zzzzzzzzzz z="zzzzzzzz" zzz="z" zzz="1"/><zzzzzzzzzzzzz zzz="z"'.


In my perl script I output the contents of $sql and have cut and pasted it into an isql session and it works fine there.

It looks to my untrained eye as if the ct_execute function can't handle the size of text string $sql, is this true?

Or do I need to use a different method when updating a text column?



rgds

Dave


fashimpaur
User

Sep 5, 2002, 6:12 AM


Views: 6256
Re: [dave5#] Updating text column in Sybase

Dave,

Is it possible that where you think there is a double quote that it is a single quote in the data? Or possibly an apostrophe? These need a single (') backtick before them in most SQL statements.

Forgive me if you already checked this. I am looking for the things that have
bit me in the past.

Good Luck,
Dennis

$a="c323745335d3221214b364d545".
"a362532582521254c3640504c3729".
"2f493759214b3635554c3040606a0",
print unpack"u*",pack "h*",$a,"\n\n";


localfilmmaker
stranger

Feb 9, 2004, 2:58 PM


Views: 6083
Re: [dave5#] Updating text column in Sybase

It looks to me like a quote issue, and not a length issue. One way to possibly fix your problem is to use the $dbh->quote() method on your msg_data string like this:
$sql = "UPDATE entities SET msg_data = '". $dbh->quote('<zzzz......>') ."' WHERE idx=62198 AND entity_type=13";

Or, use a place holder in your query and then pass your string to the execute method, like this:
$sql = 'UPDATE entities SET msg_date = ? WHERE idx=62198 AND entity_type=13';
$sth = $dbh->prepare($sql);
$sth->execute('<zzzzz.......>');

I recommend using place holders because DBI will automagically escape any needed characters that might screw things up, and it makes your SQL nicer to look at.