Forum OpenACS Development: db_string fails for large text columns

1: db_string fails for large text columns

Posted by Ryan Gallimore on 02/26/07 05:54 PM

I tried editing a varchar(2000) column with a string of length 28K, and it failed. So after increasing the length of the column to varchar(50000) it still failed.

But copying and pasting the sql into postgresql worked fine.

Does this indicate a problem with large string support and the db_ functions?

2: Re: db_string fails for large text columns (response to 1)

Posted by Dave Bauer on 02/26/07 06:53 PM

I think a little more information is needed?

Can you paste the code you tried to use, and the error message that occured when it "failed"?

db_string is for selecting text only, and I do not know of any issues with selecting large text from postgresql with db_string.

2000 characters is not a large string really at least not ar far as postgresql is concerned.

3: Re: db_string fails for large text columns (response to 2)

Posted by Ryan Gallimore on 02/27/07 02:13 AM

Here's the error message:

Error: POST http://site.com/acknowledgements/admin/details?
referred by "http://site.com/acknowledgements/admin/details";
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")

ERROR: value too long for type character varying(2000)
CONTEXT: SQL statement "update acknowledgments set shortname = $1 , title = $2 , description = $3 , global_p = $4 \
, ack_mode = $5 , ack_phrase = $6 , group_id = $7 , due_date = $8 where ack_id = $9 "
PL/pgSQL function "acknowledgment__edit" line 13 at SQL statement

SQL: select acknowledgment__edit('1','terms_of_use','Terms of Use','<div class=Section1>...[28K characters]...','f','checkbox',NULL,'-2',NULL)
while executing
"ns_pg_bind 0or1row nsdb0 {select acknowledgment__edit(:ack_id,:shortname,:title,:description,:global_p,:mode,:phrase,:\
group_id,:date)}"
("uplevel" body line 1)
invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql]"
("postgresql" arm line 2)
invoked from within
"switch $driverkey {
oracle {
return [uplevel $ulevel [list ns_ora $type $db $sql] $args]
}
..."

4: Re: db_string fails for large text columns (response to 1)

Posted by Dave Bauer on 02/27/07 02:46 AM

This is a call to a plpgsql function. The error message tells you the problem is in the SQL statement update acknowledgements.... There are two problems that could occur 1) the column you are updating is not long enough. 2) The plpgsql function argument is not long enough. If you changed the column in the table but did not change the function definition, it would try to put the value into the function argument which would be too small.

SQL: select acknowledgment__edit('1','terms_of_use','Terms of Use','
...[28K characters]...','f','checkbox',NULL,'-2',NULL)