Re: AW: Results returned with interactive SQL, but not embedded

Thanks Birgitta!

#1. The SET OPTION is the very first statement in the code before anything else runs, RPGIV or SQl.
#2. After each "INSERT..." statement is executed, the SQLCODE value is 100.
#3. The naming system is SQL.
#4. The code runs overrides before the INSERT statements that "should be" redirecting the SQL statement to the proper library. (It does this in other programs successfully).
#5. I considered the SET CURRENT SCHEMA. Are there any considerations for running it embedded? Will the tables be redirected in the Insert clause as an override would do?

Alan Cassidy

----- Original Message -----

From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, May 26, 2016 11:31:02 AM
Subject: AW: Results returned with interactive SQL, but not embedded

The SET OPTION statement is never executed but represents the "H-Specs" for SQL.
Because the source is scanned sequentially for SQL-Statements by the SQL Precompiler, the SET OPTION statement must be the very first SQL-Statement in your source.
Did you check SQLCODE or SQLSTATE?
What naming conventions are used (SQL or SYSTEM-Naming).
When System Naming is used is the library list set correctly, are there any overrides or is the SET CURRENT SCHEMA executed?

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"


-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Alan
Gesendet: Thursday, 26.5 2016 17:19
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: Results returned with interactive SQL, but not embedded

Good question.
I have the program compiling with COMMIT(*NONE) for the CRTSQLRPGI command, plus the first statement after the declares is "exec sql set option commit = *NONE".

That's how it is set in other programs I've done as well.

Thanks for the mention.



----- Original Message -----

From: "Jason Cawood" <jason@xxxxxxxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, May 26, 2016 10:34:21 AM
Subject: Re: Results returned with interactive SQL, but not embedded

is it commitment control?

-Jason

On May 26, 2016, at 7:20 AM, Alan <cfuture@xxxxxxxxxxx> wrote:



Hey folks,



I don’t remember a problem like this, I’ve done this embedded SQL lots of times.

I have a sequence of SQL statements that first clear the records (with a DELETE) in two work files.

The first one is populated, then the second, with INSERT/SELECT statements.



Works like a charm when I run in in Navigator and in interactive STRSQL.

I get 737 rows and then 68, and the results are correct.



BUT! ?? When I run it embedded, I get nothing. With the EXACT same statement and syntax, with two differences:

One, I’m running the interactive version over one database schema (library) at a time, with no overrides, whereas the embedded version runs an override first because this is going to run using the data from a bunch of schemas.

Two, Because of the above, I use the host-variable :Dtaset to include the library name in the embedded run, but in the one-library interactive version it’s CHAR(‘ABC’, 3) as Dtaset.



The overrides are done using a service program procedure. The program is compiled with ACTGRP(*NEW), and the service program with ACTGRP(*CALLER). So it runs in the same activation group, which is the default for the OVRDBF command.



I’ve done this a lot, I don’t get what’s different now. Any ideas?



THANKS!!



--Alan Cassidy
CassidyA@xxxxxxx

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.