RE: Creating SQL Stored Procedures and Trigger programswithout DB2Query Manager and SQL Development Kit...

["Steven Harrison" <steven.harrison@xxxxxxxxxxxxxxxxx>]

Interesting, 

I like that the CLI API is compliant with ODBC. I've had some experience
with ODBC and .Net Data Providers so it looks like it could be a good
fit. I'm only writing some trigger programs at the moment but it looks
like maybe something to come back and have a look at down the line. 

I see someone mentioned that the CLI code is run by the system in a
separate job though. If that's the case would that mean in would run in
a different activation group, which as far as I know would make
commitment control impossible? I haven't really had time to look into
the nuts and bolts of the CLI API so I could be way of the mark here...

Steve 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Klement
Sent: Tuesday, 22 September 2009 5:45 AM
To: RPG programming on the IBM i / System i
Subject: Re: Creating SQL Stored Procedures and Trigger programswithout
DB2Query Manager and SQL Development Kit...

Hello,

> You lose nothing by not being able to retrieve exactly what is done
with 
> the precompiler.  There are things that the SQL CLI does that the 
> precompiler cannot but there is nothing in the reverse (IMHO).  And
I've 
> never used SQL CLI.

I'm not sure that I understand what you're saying here... but there are 
at least two things the precompiler does that CLI cannot:

a) It does part of the work at compile (or pre-compile) time.  CLI has 
to do all of the work at runtime.

b) External stored procedures using embedded SQL can return result sets 
via the "Set result sets" function.  This capability doesn't exist in
CLI.

c) CLI has trouble when you have more than one program using CLI in the 
call stack.  If you open a CLI environment, then call another program 
that tries to allocate a new environment (such as CPYTOIMPF) the second 
open will fail.  (You have to close the environment in the first program

  before calling the 2nd.)


On the other hand, CLI:

a) Lets you get the result set (not just parameters) from a stored 
procedure call.

b) Is included with IBM i, nothing else to buy.

c) follows the industry-standard CLI API (ODBC compliant), so apps 
written to use it (in particular, C programs) can be made platorm
neutral.

Hope that helps someone :)