Re: SQL CLI

Arco,

It really depends whether you want to write a generic solution (one programme 
to run any SQL statement) or not; work with multiple SQL statements in one 
programme or not; using one or more connections or not. More insight in your 
intensions will help.

As you already have read:
You need one environment handle per job.
You can have more connection handles in the environment, and I think it is 
logical to connect to a data source (remote or local) only once.
Per connection you can have one or more SQL statements (thus statement 
handles). 

But: an environment handle is required to allocate a connection handle and a 
connection handle is required for a statement handle. 

Thus: depending on your chosen scenario the calling programme may control the 
connection handles and statement handles.

You can manage the allocation and freeing of handles in subprocedures, but the 
handle should be passed back to the calling programme. 

Perhaps you can check the handle value being 0 (zero), then it has not been 
allocated, yet.

Any programme that allocates a statement should free it, the same applies for a 
connection handle.
 
You can wrap your original programme in an initial programme that allocates and 
free all handles; downside is you have to pass all connection and statement 
handles to the main programme. 

Just my thoughts

Regards,
Carel Teijgeler

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

On 11-7-05 at 13:10 Simonse, Arco \(CMK\) wrote:

>Currently I'm doing some research on using the SQL CLI. I wrote a few
>serviceprograms to hide database work, and used the CLI functions in it.
>I want to extend this concept. The routines in which I want to use the
>CLI are serviceroutines wich will run in the Activation Group of the caller.
>
>I'm not sure where to place the SQLAllocEnv, SQLAllocConnect and
>SQLConnect calls for the allocation of environment and connection handles. 
>The manual says "there can be only one active environment at any one time per 
>application." 
>And for the connection: "iSeries does not support multiple simultaneous
>connections to the same data source in a single job."
>
>So it looks that I have to let the calling program do the allocation of
>the handles and the database connection. But I don't want the calling
>program to know anything about the underlaying database handling. 
>I thought about a trap-door routine in the serviceroutines which prepare
>a connection if it was not established. But then the problem arises what
>to do when leaving the calling program. It does not free connection and
>handles automaticly since a database connection is scoped to the job.
>
>Are there people who have expererience with the use of the SQL CLI in such 
>situation?