Re: Using QSQPRCED and long field names

["Beppe Costagliola" <beppecosta@xxxxxxxx>]

The "Table 81. Field Descriptions for an Extended SQLVAR" in the
DB2 UDB for iSeries SQL Reference V5R2 says:

- For a label, the database manager sets this to the first 20 bytes of the
label.
- For a column name, the database manager sets this to the column name.

while the java method "getColumnLabel" returns the full label (with the
latest ptf's)




----- Original Message ----- 
From: "Larry Ducie" <Larry_Ducie@xxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Monday, March 14, 2005 9:50 PM
Subject: Using QSQPRCED and long field names


Hi all,

I am writing (completing) a RPG service program to generate XML data from
our database data via SQL. To do this I am using Prepared Statements stored
in a SQL package (*SQLPKG) that is created at job start-up. This is all
performed using Extended Dynamic SQL - via the QSQPRCED API.

All seems to be going well, and I am pleased with the performance, but I
have noticed that there is a limitation on the length of the field label
returned during a describe operation - the describe is performed into an
SQLDA data-structure. It would appear that the longest label that is passed
back is only 18 bytes long. The SQLDA field is 30 bytes long, and I thought
that I might be limited to 20 bytes of that (according tto the docs).

When I prepare my statements with SQL column labels (*SQLLABEL) greater than
20 bytes I don't get a warning from QSQPRCED. But when I try to retrieve the
labels they are truncated. This is a pain because I'm using the SQL column
label as the XML tag (or attribute) name and I don't really want a
limitation of 18 bytes on that.

Is this a limitation of the SQLDA? Is there an extended SQLDA that I can
describe my data into to get the full SQL column label? I believe this API
uses the same interface as the client-based JDBC and ODBC drivers used in
Client Access Express and the iSeries Toolbox for java components, and I
would have thought that they must support SQL column labels with a length
greater than 20 bytes. Am I mistaken? Is this actually a DB2 limitation?

Note: This is a RPGLE program, not SQLRPGLE. All interactions with the
database is via direct calls to QSQPRCED. But any help using embedded SQL is
welcome - I can always look at the pre-compiled code to see what occurred
beneath the covers. :-)


Sample:

SQL:
select field1 as [fieldOne], field2 as [fieldTwo], field3 as
[@fieldThreeAsAttribute] from file1 where key1 = ? and key2 = ?

During prepare the '[' and ']' are replaced with '"', but basically the SQL
statement above can be prepared as-is.

Resultant XML - when two rows are returned:
<?xml version="1.0" encoding="UTF-8" ?>
<root>
<row>
<fieldOne>value-of-field1</fieldOne>
<fieldTwo fieldThreeAsAttrib="value-of-field3">value-of-field2</fieldTwo>
 </row>
<row>
<fieldOne>value-of-field1</fieldOne>
<fieldTwo fieldThreeAsAttrib="value-of-field3">value-of-field2</fieldTwo>
 </row>
</root>

As you can see, the attribute name "fieldThreeAsAttribute" has been
truncated to "fieldThreeAsAttrib". (obviously the root and row tags are
softcoded and have been included in the example to make the XML well-formed)

(originally this module was going to simply write the header and details XML
in row-column format and I was going to use another module (already written)
to transform/merge the data using java and a xslt stylesheet. But this was
much easier that I'd imagined and seems fast enough. I'm hoping I will not
need the java transformer after all  - especially if it's to simply reformat
the tag names.)

Any and all help, hints, or advice would be most welcome.

Thanks in advance

Larry ducie
-- 
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.