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.

--
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.

--
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.