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

All,
 Thanks for the education, I hadn't previously taken the time to
differentiate the ways that SQL could be used to return data to the
RPG/CL in our legacy system. As I understand, we could:

1) Use IBM functions such as RUNSQLSTM, QMQRY or 3rd party utilities such
as ASC-Sequel to execute most SQL statements, unfortunately these include
no mechanism to return "SELECT" results directly to the calling RPG or CL
variables. Instead, the results must be first be written to a temporary
(QTEMP, whatever) object, then read into the calling program. In general,
using dynamic SQL may result in (slightly) slower performance than static
SQL because DB2 must parse and evaluate the SQL each time it is executed.

2) Purchase the IBM SQL pre-compiler 5722ST1 which allows any(?) SQL
command to be embedded into RPG and optionally return results directly to
RPG variables. The pre-compiler automatically creates, stores and executes
the SQL package information, resulting in a static SQL call which may
offer superior perfomance than dynamic SQL.

3) Use the "SQL Process Extended Dynamic" (QSQPRCED) api to execute SQL
commands and optionally return results directly to the calling HLL
variables. More complex than using the pre-compiler, the programmer is
responsible for creating, storing and executing the *SQLPKG objects used
by QSQPRCED and deciphering the resulting SQLDA structure. QSQPRCED
perfomance may be greater than dynamic SQL because the SQL commands need
not be PREPAREd for each use. Many RPG examples and freeware utilities are
available in the archives.

4) Use the SQL CLI apis to embed SQL and optionally return results
directly to the calling HLL variables. Like QSQPRCED, the programmer is
responsible for creating and running the SQL commands, but there is no
provision for saving and reusing an *SQLPKG. The SQL CLI interface uses an
industry-standard syntax that is based in the MS ODBC specificaions and
results in dynamic SQL calls. Scott Klement recently wrote some SQL CLI
articles and examples. Google is your friend.

Thanks again, JK

Last couple of years, it was embedded SQL I coded for a "history data"
inquiry and report project. It was a little too easy for what I was doing,
but I always intended to set myself up with SQL CLI for all the reasons
Scott listed. That's a skill you can use in any midrange shop that has RPG
or Cobol or C/CPP.

My personal bias is to get control close to the "metal". And once you
understand CLI, you can wrap all the procedures and service programs
around it you want to, to keep your application coding simple.

--Alan



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Scott Klement
Sent: Saturday, September 09, 2006 3:53 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: ISTKT & DYNSQL vs. 5722ST1 SQL Development Kit



Yet another option is the SQL CLI.  This is an interface to SQL that's
included with i5/OS (or OS/400) -- it does not require 5722-ST1. It can be
used from any ILE language including ILE RPG. (and even ILE CL starting
in V5R3, thanks to CL's *BYVAL support!)

IMHO, it's easier to use and the code is easier to read than the QSQPRCED
API.

For someone familiar with ILE concepts, it's only a little harder to use
than embedded SQL.  The only disadvantage is performance, since the
statements aren't prepared at compile time, only at run-time...   much
like the PREPARE statement in embedded SQL. But, I've never found the
performance difference to be very noticable.

Once of the things I really like about the CLI is that it just works.
I'm not constantly fighting with it over the new RPG features.  With
embedded SQL, I seem to keep running into things that don't work with the
precompiler. Such as trying to fetch into local variables.  Or using
nested data structures. Heck, embedded SQL didn't even support free format
RPG until V5R4.  None of these things were ever a problem with the CLI.

Another thing I like about the CLI is that I can use it to call stored
procedures and actually read the result set!  AFIAK, it's not possible to
read a stored procedure's result set with embedded SQL.

But, embedded SQL is a little simpler to code, and does perform better
especially when you have extremely complicated SQL statements.

Anyway... it's something to think about.

--
Scott Klement  http://www.scottklement.com


On Sat, 9 Sep 2006, Carel Teijgeler wrote:

1) You can create QMQRY objects with SQL statements in a SRCPF and the
CRTQMQRY command. Then run the QMQRY with STRQMQRY. You can pass
parameters
to the query for dynamic data selection. This can be wrapped in a CLP
with
a command interface.
2) You can use the QSQPRCED API. Midrange Computing had an interesting
article on it, search the website www.mcpressonline.com.

--