Re: SQLLEN of variable length character field in SQLDA

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.