RE: ISTKT & DYNSQL vs. 5722ST1 SQL Development Kit

Sorry for the late reply, been abroad for a holiday.

Others have answered most of the other points. The rest is by heart, still
off from work.

If you have downloaded the toolkit from SourceForge, you should als o have
the source of the DYNSQL. The compiled *SRVPGM of DYNSQL does not lack any
functionality like the IBM SQLRPG precompiler.

In the source tou can see, that DYNSQL uses the SQLDA; it can't do
otherwise, as it does not know which SQL statement to run. In fact,
embedded SQL can run statements using descriptor SQLDA.

So both embedded SQL ( I would like to call it SQLRPG, as SQL CLI and
QSQPRCED are also forms of SQL, embedde in an HLL) and the API QSQPRCED
uses the SQLDA (the first may, the second must).
 
The SQLDA is a DS split in two parts:
1) Info of the number of entries in the second part (subfields SQLN and
SQLD) and the total length of the DS (length 16).
2) An array with each element info of a column in the result set (length 80
for an element).

Appendices C and D in the SQL Reference tells you more on the SQLDA and the
SQL data types.

1) The SQLDA can be used to get the name/column text, length and data type
of each column in the result set.
Set SQLN and SQLD to 0 and with the SQL function DESCRIBE you get the
number of columns in the result set (returned in subfield SQLD).
Use this value (subfield SQLN = subfield SQLD) to build up the correct
lengt of SQLDA to get the info using SQL function DESCRIBE

2) Another use for the SQLDA is to pass values to an SQL statement (which
contains parameter markers) with the SQL functions OPEN and EXECUTE
(IMMEDIATELY)
The values should be passed with the correct data type, a specified length
and the address of the programme variable, that contains the value to be
passed.

3) The last use for SQLDA I know of, is to retrieve the result set after an
SQL function FETCH.
The SQLDA should be set up for each column in the result set with the
correct datatype, the appropriate length (use DESCRIBE) and address of the
programme variable in which SQL returns the fetched data. (I think the
appropriate length should match the length of the programme variable, don't
know for sure, but that is what I pass into the SQLDA)

Scott mentioned SQL CLI, another alternative. I do not think using SQL CLI
is easier than using the SQLDA. In both cases you have to link adresses of
programme variables to the SQL call; in SQL CLI you may use the BindParm
API to pass variables for the SQLExec API, and the BindCol API to get the
data with each SQLFetch API. (And here SQL CLI uses other definitions for
the data types, as you use with SQLDA).

Like SQL CLI you run dynamic SQL using SQLDA. DYNSQL is an example. 
I do not think, however, that you should write a seperate programme for
each SQL statement that you want to run.

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 8-9-2006 at 18:42 JK wrote:

Thanks for the reply. Yes, YDRC (you did recall correctly), DYNSQL came

as a V5R1 *SRVPGM and I could see the /EXEC commands as I stepped through

with the debugger.

The important thing is that DYNSQL allows me to start using embedded SQL

- even if it is limited to V5R1 and is used for picayune things like
count(*).

My main goal was to find whether DYNSQL lacks any major functionality as

compared with IBM's precompiler.

a) I don't anticipate needing more than 8 cursors open at one time and

assume that closing one cursor frees it up for another use. That shouldn't

be a problem as long as it is bound by the program's AG.

b) Right now I don't know enough to know whether metadata is important to

me so you've given me something else to google!

c) I believe that QMQRY objects cannot use the 'select' statement, as

there is no mechanism to return results to a calling program.

d) I've fiddled around with QSQPRCED a little - even wrote a *SRVPGM that

allows me to run a simple SQL command from RPG - but I became thoroughly

lost inside the SQLDA and ended up with something no better than QMQRY. 

Our company's recent change of ownership adds a little urgency to my

desire to incorporate SQL into our legacy system. I want to be able to talk

intelligently to the new home office M$-SQL techies when they start the

systems evaluation in November.

Question: am I correct in my understanding that the main difference

between dynamic and static SQL is that static SQL uses an already-prepared

statement, an *SQLPKG? And that the IBM precompiler simply creates and

names those *SQLPKG objects for you?