RE: SQL CLI

Hi Carel,

It's my intention to move all I/O to serviceprogram's and also hide its
details for the application.
I have a set of standard "get" and "set" commands through which I update
a datastructure in the serviceprogram.
So I want to do something like this for example:
Invoice_New()
Invoice_setInvoiceDate()
Invoice_setExpiringDate()
:
Invoice_Write()

I wrote a few test routines and it worked fine. In my Invoice_new()
routine I do a check to a generic IO routine named
IOCLI_ConnectionPrepare(EnvHandle : ConHandle). If there is already an
Enviroment/Connection handle established, it will return the existing
handle.

But I'm not sure if this is a good approach. What if I need to do a call
to another program that would try to initiate an environment/connection
for itself. It does not know about the existing handles. No problem when
it starts: it will use the existing handle. But when it ends, it will
free the connection. When it returns to the first calling program there
will be a problem because the handles no longer exist. 
Do I have to put my SQLConnect and SQLDisconnect in a routine which runs
in its own Activation Group? And then only the first time do a connect
and never free the connection? Then it would keep the existing handles
if programs leave. 

This is the point where my thoughts are struggling. How to set up up
good initial work and how to terminate correctly.

Thanks,
Arco Simonse


> -----Oorspronkelijk bericht-----
> Van: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Namens Carel Teijgeler
> Verzonden: maandag 11 juli 2005 19:09
> Aan: rpg400-l@xxxxxxxxxxxx
> Onderwerp: 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? 
DISCLAIMER:
This message contains information that may be privileged or
confidential and is the property of C.Meijer B.V. It is intended only for the 
person to whom it is addressed. If you are not the intended recipient, you are 
not authorized to read, print, retain, copy,disseminate, distribute, or use 
this message or any part thereof. If you
receive this message in error, please notify the sender immediately and delete 
all copies of this message. 

This footnote also confirms that this email message has been swept by the 
presence of computer viruses