Re: SQLLEN of variable length character field in SQLDA

["Gene Burns" <burns.gene@xxxxxxxxx>]

I guess I wasn't very clear on what I was trying to accomplish.

The variable-length field is not in the database.  It is a selection
criteria used for a substitution parameter in the where clause of the
select.
This is provided by the OPEN USING DESCRIPTOR :SQLDA statement.

This process works fine when the variable is a numeric or fixed-length and
the comparison is field = ?

But in this case I am trying to SELECT FLD1, FLD2, FLD3... WHERE FLD2 like
?   and have the OPEN USING DESCRIPTOR :SQLDA provide the value found in the
variable length field, which could be anything from 1 to 15 characters.  I
am just having trouble getting the OPEN to work if the field is a
variable-length.  I don't know how to determine the SQLLEN to use.

Gene

On 6/14/07, Carel Teijgeler <coteijgeler@xxxxxxxxx> wrote:
> Gene,
>
> If you use SQL function DESCRIBE of the result set into SQLDA (after a
> PREPARE) you will receive the length of the VARCHAR in the SQLDA. The
> length of a VARCHAR column in SQL is the actual length in bytes. So option
> b SQLLEN = %len(varfld) shpuld be  SQLLEN = %size(varfld)
>
> You have to assign a pointer to the variable to store the value. You can
> use the lenth of that variable.
>
> I always store character columns in a fixed length character variable (32
> bytes), no matter whether the column is fixed CHAR or VARCHAR; with
> VARCHAR
> I have to deal with the first two bytes being the length of the value. No
> problems with that.
>
> Regards,
> Carel Teijgeler
>
> *********** REPLY SEPARATOR  ***********
>
> On 14-6-2007 at 13:35 Gene Burns wrote:
>
> >I am trying to use open using descriptor :SQLDA in an SQLRPGLE
> program.  I
> am able to calculate the value for SQLLEN for a numeric and a fixed-length
> >character field and the program works.
> >
> >However, I cannot figure out the length for the variable-length character
> field.
> >
> >I have tried all of the following without success.
> >
> > SQLLEN = %len(varfld) * 256
> > SQLLEN = %len(varfld)
> > SQLLEN = *zero
> >
> >The message I am getting is below with the specified length changing with
> different entries to the field.
> >
> >Message . . . . :   Length in a varying-length or LOB host variable not
> valid.
> >Cause . . . . . :   Host variable *N was specified.  The value in the
> length portion of the variable length or LOB host variable is either
> negative or
> >  greater than the declared length.  If the host variable is graphic the
> length should be the number of DBCS characters. The host variable number
> is
> >  1.  The specified length is 50902.  The variable is declared to have
> length 3840.
> >Recovery  . . . :   Change the length portion of the varying-length or
> LOB
> host variable to a valid positive number or zero. Try the request again.
> >
> >Anyone have any ideas?
>
>
> --
> This is the RPG programming on the AS400 / 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.