AW: Re: SQL Statement length

[Hauser <Hauser@xxxxxxxxxxxxxxx>]

Charakter variables in SQL are restricted to 32k. For karger Textes LOB variables are needed.Birgitta 


Von meinem Samsung Gerät gesendet.

-------- Ursprüngliche Nachricht --------
Von: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> 
Datum: 12.05.2016  22:34  (GMT+01:00) 
An: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx> 
Betreff: Re: SQL Statement length 

Chuck - it's help to see your code, if you can sanitize it for privacy - 
is the statement variable declard as CHAR or as VARCHAR? You DO have to 
break the statement up in order to fit in the 8-80 limits of RPG. Have 
you run it in debug to see what your statement REALLY looks like?

Cheers
Vern

On 5/12/2016 3:24 PM, Tyler, Matt wrote:
> There is no trick for the length if you set up your program correctly.  The system allows for a 32740 length statement.  I admit to coding a 10K size statement but nothing that comes close to the max.   It would be nice to know more about the "variable truncated" error, I could not find a standard message with that phrase.
>
> DCL-S Stmtsql Varchar( 32740 );
> DCL-S Wherestmt Varchar( 8000 );
> DCL-S Filter Char(1) INZ('A');
>
> Stmtsql = 'select * from MYTABLE';
> If Filter > ' ';
>     If %LEN( Wherestmt) > 0;
>       Wherestmt += ' And ';
>     Endif;
>     Wherestmt += 'FILTER = ''?'';
> Endif;
>
> Stmtsql += ' ' +Wherestmt;
>
> Exec sql prepare PREP_SQL from :Stmtsql;
> If Sqlstate <> '00000';
>     // Do some error routine
> Endif;
>
> exec sql declare MYCURSOR for PREP_SQL;
>
> exec sql open MYCURSOR using :Filter;
> If Sqlstate <> '00000';
>     // Do some error routine
> Endif;
>
> etc...
>
> Thanks, Matt
>
>
> -----Original Message-----
> From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Graves, Chuck
> Sent: Thursday, May 12, 2016 2:03 PM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: SQL Statement length
>
> Is there a "trick" to programming a long complex (6000 character) SQL statement in RPG?
>
> We keep getting a "variable truncated" error message.
>
>
>
> [Rodda Paint Company]
> Chuck Graves
> Director of Information Systems
> Rodda Paint Co.<http://www.roddapaint.com>
> 6107 N. Marine Drive
> Portland, Oregon 97203
> (503) 737-6042
>
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related questions.