Re: Use of Sybase text datatype is recognized as char datatype!

[Nathan Treadway <nathant@ONTKO.COM>]

> begin-procedure main
> let $abc = '1234567...300 characters of data....'
> begin-sql
>         insert into {DETAILDB} (out_line) values ($abc)
> end-sql
> end-procedure
>
>
> -------When I run this, I get the following bind errors.  It looks like
> SQR is passing char data, even though the field is defined as type
> text.:
>                                --------------------------
> (SQR 5528) Sybase dbbind: ct_param error in cursor 119:
>    (138) ct_param(): user api layer: external error: A data length of
> 300 exceeds the maximum length allowed for CHAR data.
> (SQR 5528) Sybase ct_send error in cursor 119:
>    (138) ct_param(): user api layer: external error: A data length of
> 300 exceeds the maximum length allowed for CHAR data.

I haven't used Sybase text columns and have only briefly used SQR for
Sybase, but it wouldn't surprise me if SQR didn't make the distinction
between CHAR and TEXT.  You might check the release notes in your SQR
distribution or with SQRIBE technical support to find out for sure.
(Normally, Sybase would do an implicit conversion "on the way in", and so
there'd be no need to make the destinction.  )

One thing to look at is the CONVERT function; using this solves many of
these kinds of Sybase problems.  However, I'm guessing it won't help in this
case, because the bind variable will probably still be considered to be a
CHAR and still subject to the 300 length limit.

If Convert doesn't help, you'll probably need to do dynamic SQL.  This
avoids using bind variables, and thus should avoid the 300 length limit.  It
might, however, be slower than a non-dynamic solution.  I'm pretty sure this
will work, since it's basically equivalent to doing a hard-coded INSERT
statement, which you said (in an another message) did work.  See the SQR
manual sections on dynamic SQL.  I don't have the page numbers for V3 here,
but it's near the front, before the beginning of the command descriptions.

For example, give this a try:
begin-procedure main
let $abc = '1234567...300 characters of data....'


let $quoted_abc = '''' || $abc || ''''
begin-sql
        insert into {DETAILDB} (out_line) values ([$quoted_abc])
end-sql
end-procedure


Hope this helps.

                                                        Nathan
----------------------------------------------------------------------------
Nathan Treadway    | Ray Ontko & Co. | Software consulting services
nathant@ontko.com  | Richmond, IN    | http://www.ontko.com/