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

["Simonian, Guy" <SimonianG@AETNA.COM>]

Using dynamic SQL solves the text datatype restriction:

let $abc = '123... 300 chars of data.'
begin-sql -xp
insert into {DETAILDB} (out_line) values ($abc)
end-sql

Thanks all, especially Nathan T. & Tim Green.

> ----------
> From:         Nathan Treadway[SMTP:nathant@ONTKO.COM]
> Sent:         Tuesday, April 07, 1998 10:58 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: Use of Sybase text datatype is recognized as char
> datatype!
>
> > 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/
>