× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Cheers Joe, that suggestion put me on the right track to fix it. I had
to put the SET OPTIONS COMMIT = *CS command in the SQL stored procedure
itself.

I had read the following in the IBM documentation:

"The activation group of the ILE C program generated by the execution of
the SQL CREATE PROCEDURE statement is always set to *CALLER. This means
that an SQL procedure always runs in the same activation group as the
program that calls it. If the SQL procedure COMMITs any changes, all
changes within this activation group are committed."

I had assumed that because it was running in the same activation group
it would inherit it's commitment scope from a previous STRCMTCTL
command, this doesn't seem to be the case though and needs to be
explicitly set. Otherwise the stored procedure will automatically use
the system QDBCMTDFN commitment definition as opposed to the one scoped
to the activation group...

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Steven Harrison
Sent: Thursday, 6 January 2011 2:10 PM
To: RPG programming on the IBM i / System i
Subject: RE: Commitment Control with SQL...

The SQLRPGLE modules are compiled with a isolation level of *CS. This
shouldn't matter though as this is used when commitment control is
activated implicitly by SQL... The prior STRCMTCTL command overrides
this. If the program was trying to open a file without the COMMIT
keyword while commitment control was active it would just crash.
Commitment control is active... it is just not starting a commitment
cycle...

I tried the following which is leading me to believe that the SQL in the
stored procedures are not being included in the commitment transaction:

STRCMTCTL (Via QCMDEXC)

Exec SQL
Call AddProduct(:productId, :productName, :unitCost);

Exec SQL
UPDATE Products SET UnitCost = 10.00
WHERE ProductId = :productId;

RolBk;

I would assume that the rollback would rollback both the UPDATE and the
INSERT (which is called in the AddProduct stored procedure)

Instead the UPDATE is rolled back but the INSERT from the stored
procedure is still stored in the DB? There is no transaction SQL code in
the stored procedure so my understanding was that it would fall in the
commitment scope of the calling program. This doesn't seem to be
happening though?


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Thursday, 6 January 2011 1:32 PM
To: RPG programming on the IBM i / System i
Subject: Re: Commitment Control with SQL...

You have to specify that the files are under commitment control. For
SQL, it's either in the compile command or in the SET OPTIONS command in

the program itself. For traditional I/O, it's the COMMIT keyword on the

file specification.

Joe


Hi All,



I am using SQL stored procedures to perform the CRUD operations on my
database tables. These are called from an RPG service program. There
are
also still legacy files involved so I start commitment control using
the
STRCMTLCTL command in the main program before any files are opened or
SQL statements are performed. The SQL tables do not seem to be opening
under commitment control though? Changes made to the SQL database
tables
are permanently stored regardless of whether a commit or rollback is
called. And during execution if I view the resources under commitment
control in the activation group the SQL tables do not appear? Does
anyone know if I'm missing something I should be aware of? I was under
the impression that if I started commitment control manually before
any
SQL statements were performed then all transactions in the activation
group, regardless of whether native IO or SQL should run under the
same
commitment definition?



Cheers,



Steve







As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.