Perl Guru Forums: Perl Programming Help: DBI: Updating text column in Sybase

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

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

dave5#
New User

Sep 5, 2002, 6:06 AM

Post #1 of 3 (3030 views)

Updating text column in Sybase	Can't Post

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

Post #2 of 3 (3027 views)

Re: [dave5#] Updating text column in Sybase [In reply to]	Can't Post

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

Post #3 of 3 (2854 views)

Re: [dave5#] Updating text column in Sybase [In reply to]	Can't Post

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.

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives