Re: Embedded SQL in ILE RPG?
Martin Stoeberl
> the parameter of the create to commit(*none) or (better solution) start
> journaling for the file and use commitment control.
This didn't solve the problem:
The error code I reported was then I used static sql without a cursor
for inserting the new values, sorry. The error code for the dynamic
sql was 07003 / The statement identified in the EXECUTE statement is a
select-statement, or is not in a prepared state / -518. The problem
here was that I used a variable for the path like I use it for the
values (INSERT INTO ? VALUES(?, ?, ?)). Now I build up my sql
statement with EVAL (INSERT INTO 2/A VALUES(?, ?, ?))and it works! :)
cu
Martin
Jonathan Ball
> Hi together!
>
> I made my first steps in writing an ILE RPG program that uses embedded
> sql. The program should read physical file 'A' (we don't use an sql
> database at the moment) from library '1' and write the fields to
> physical fiel 'A' in library '2'. In prior we opened file 'A' in library
> '1' and opened file 'A' from library '2' after an OVRDBF.
> I tried to do this in sql with an static sql with cursor for reading and
> an dynamic sql without cursor for inserting the new row ("insert into
> 2/A values ...") but I have problems with the insert ... I get SQLSTATE
> 55019 / The table is in an invalid state for the operation / SQLCODE
> -7008 when I try to insert a new row.
> Can someone here give me a code snipplet that will do the job so I can
> try it out (it can't post my code because i am at home and cannot post
> to newsgroup from my workplace at the moment)!?
Dieter got it: it's because your program was probably
compiled with the default COMMIT(*CHG), so the
inserts/updates to your target table are expected to be
done under commitment control.
You have two choices, assuming you don't want to
journal your files:
1. Compile your program with COMMIT(*NONE)
2. In the program, specify SET OPTION COMMIT = *NONE
As Dieter also suggested, a third option would be to
journal your files and leave commitment control active
for the application, but there's probably not much
point in that.
Martin Stoeberl
> default COMMIT(*CHG), so the inserts/updates to your target table are
> expected to be done under commitment control.
>
> You have two choices, assuming you don't want to journal your files:
>
> 1. Compile your program with COMMIT(*NONE)
>
> 2. In the program, specify SET OPTION COMMIT = *NONE
>
>
> As Dieter also suggested, a third option would be to journal your files
> and leave commitment control active for the application, but there's
> probably not much point in that.
a COMMIT statement but I am not sure. I tried out a lot of things these
day ... :) I changed it to COMMIT(*NONE) like Dieter said and also fixed
another bug. Now it works! Perhaps I try it out tomorrow with
COMMIT(*CHG) to see if it works.
When I want to use COMMIT control with physical file's do I need journal
for all my files? Is COMMIT control / journaling necessary when I want
to use features like ROLLBACK?
cu
Martin
Birgitta Hauser
> When I want to use COMMIT control with physical file's do I need journal
> for all my files? Is COMMIT control / journaling necessary when I want
> to use features like ROLLBACK?
>
> cu
> Martin
Hi Martin,
If you want to use COMMIT and ROLLBACK for a specific file this file
must be journaled.
If you create a library with SQL (CREATE SCHEMA) and not with CRTLIB a
journal and journal receiver are automatically created in this
library. All files that are created in this library with the SQL
CREATE TABLE statement are automatically journaled.
Because it may not be the best solution to have the journal and
journal receiver in the same library as the data are, you can create a
journal in an other library. Before release V5R3M0 the files or tables
must be registered manually by using the CL command STRJRNPF. With
release V5R3MO you can create a dataarea QDFTJRN in your data library
that contains the qualified journal name. If this dataarea exists in
the data library all physical files (Created with SQL or CL) are
automatically registered in the specified journal at creation time.
If you compile your program by specifying COMMIT *CHG all files that
are changed by SQL UPDATE, INSERT or DELETE statements must be
journaled.
If you have to manipulate data with SQL, but don't want or can
register the file in the journal, you can add WITH NC to the UPDATE,
INSERT or DELETE statement. If a ROLLBACK is performed these
statements are not rolled back.
If you want to use COMMIT and ROLLBACK you have to start commitment
control for your job by using the CL command STRCMTCTL. Be aware the
default value for commitment scope is *ACTGRP. If your application is
not properly designed you can get some unexpected results. In this
case it would be the best to start commitment control with commitment
scope *JOB.
Birgitta
Dieter Bender
for using Commitment control you hav to start journaling (STRJRNPF) for all
files having updates under commit.
Commitment controll is prerequisite for using rollback or commit operation.
Dieter
Martin Stoeberl
Nice weekend!
Martin
Jonathan Ball
>> Dieter got it: it's because your program was probably compiled with
>> the default COMMIT(*CHG), so the inserts/updates to your target table
>> are expected to be done under commitment control.
>>
>> You have two choices, assuming you don't want to journal your files:
>>
>> 1. Compile your program with COMMIT(*NONE)
>>
>> 2. In the program, specify SET OPTION COMMIT = *NONE
>>
>>
>> As Dieter also suggested, a third option would be to journal your
>> files and leave commitment control active for the application, but
>> there's probably not much point in that.
>
> Hmmm, I don't know what I did exactly. I think I also tried it out with
> a COMMIT statement but I am not sure.
That wouldn't have helped, because the files weren't
journaled. Using commitment control requires that the
files be journaled.
> I tried out a lot of things these
> day ... :)
Ich glaube!
> I changed it to COMMIT(*NONE) like Dieter said and also fixed
> another bug. Now it works! Perhaps I try it out tomorrow with
> COMMIT(*CHG) to see if it works.
> When I want to use COMMIT control with physical file's do I need journal
> for all my files?
You need to journal any files that will have records
(rows) written, updated or deleted by the SQL
statements in the program. Files that are read-only
don't need to be journaled (for that program execution,
anyway).
> Is COMMIT control / journaling necessary when I want
> to use features like ROLLBACK?
Yes.
>
> cu
> Martin