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

[Ken Gartman <osmium@EROLS.COM>]

Simonian, Guy wrote:
>
> Am trying to write to a Sybase "text" datatype which is supposed to hold
> up
> to 2 gig of ascii data, yet the errors I get indicate that Sybase thinks
> its getting char data;
>
> begin-setup
> begin-sql on-error=SKIP
> create table {DETAILDB} (out_line text null)
> end-sql
> end-setup
>
> 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.
>
> SQL: insert into tempdb..detail (out_line) values (@__p1)
>
> Error on line 155:
>    (SQR 3734) Couldn't compile SQL.
>
> SQRW: Program Aborting.
>
> Am running SQR V3.0.18.1 in a Sybase 11 environment.  The SQR client is
> on SunOS 5.5.1 and also on WinNT.  Sybase is under SunOS 5.5.1


Hi, Guy!
        Let's use a multi-step approach to debug this one.  Thus we can
isolate the problem into a smaller area of confusion.

1.  Create your table outside of SQR.  After you create it, verify its
        structure.  Are the table name, column name, and column data
type correct?

2.  Directly at the data base level, insert a constant into the table,
        with a SQL statement such as:
        INSERT INTO GUY_TEMP_1 (OUT_LINE) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIJJJJJJJJJJJJJJJJJJ')
;

4.  Do you get the expected results?  COMMIT ;  SELECT * FROM GUY_TEMP_1
;
        Do you once again get the expected results?
        insert into {DETAILDB} (out_line) values ($abc)

5.  If all is well, code a small, simple SQR:

        BEGIN-PROGRAM
                DO INSERTS
        END-PROGRAM
        BEGIN-PROCEDURE INSERTS
        BEGIN-SQL
        INSERT INTO GUY_TEMP_1 (OUT_LINE) VALUES
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIJJJJJJJJJJJJJJJJJJ')
;
        COMMIT ;
        END-SQL
        END-PROCEDURE INSERTS


6.  Execute your program with the -E and -S flags:

        SQR <connect string>  INSERT-PROGRAM.SQR -E -S

  Do you get any errors now?  I doubt that you will, but if so, the file
         INSERT-PROGRAM.ERR should have the details.

7.  Code up a program that uses variables:


        BEGIN-PROGRAM
                DO INSERTS
        END-PROGRAM
        BEGIN-PROCEDURE INSERTS
                LET $long-string =
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIJJJJJJJJJJJJJJJJJJ'

        BEGIN-SQL
        INSERT INTO GUY_TEMP_1 (OUT_LINE) VALUES ( $long-string ) ;
        COMMIT ;
        END-SQL
        END-PROCEDURE INSERTS

8.  Execute as before.  Any errors?  I guess this the step most likely
        to have errors.  Look up any errors in the SQR documentation.

9.  At the database level, once again check the results:

         SELECT * FROM GUY_TEMP_1 ;



                                Good luck, and happy computing
                                Ken Gartman