Hi Birgitta,
I was triggered (...) by your comment
Note: when creating a trigger all unqualified specified database objects in static SQL are resolved at compile time and stored in this way. So changing the library list at runtime or working with a different library list will not have any affect.<<
We don't use a lot of trigger programs and never ran into this yet.
You, among others, always say that you should put the DB logic in the database. We distribute our software to customer and cloud LPAR's on which different DB library names occur. Views and MQT's we create in de DB, but we make UDTF's and SP's in the applictation library and always use unqualified names and System Naming. This way we only have one copy of the SQL object. I would tend to do the same with a trigger program.
My question is: should we be aware of problems occuring when we use this strategy? How can these problems be circumvented?
Kind regards,
Martijn van Breden
lead software architect
So we'd need a few more details:
1. Is it an SQL or System Trigger?
2. When creating the Trigger are you using SQL or System Naming conventions.
3. Are you dealing with a current schema?
4. When using SQL Naming conventions how did you set the Dynamic default collection (DYNDFTCOL).
Note: when creating a trigger all unqualified specified database objects in static SQL are resolved at compile time and stored in this way. So changing the library list at runtime or working with a different library list will not have any affect.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"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!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jana Reimer via RPG400-L
Sent: Monday, 5 February 2024 12:59
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Cc: Jana Reimer <Jana.Reimer@xxxxxx>
Subject: JDBC and libearylist by dynamic SQL
Hello,
I have a problem with the combination of JDBC and SQL:
A before-update trigger is triggered via JDBC. Within the trigger, a
SELECT to another file is executed via an SQLRPG program.
This dynamic SQL always results in SQLCode -204 for PREPARE.
The library list is set correctly by the trigger at the time of the
SQL-PREPARE.
I have already checked this several times
all other accesses, whether SQL or native to other files, work without me
specifying a library.
It works with named ACTGRP
ACTGRP(*NEW) also works.
And it gets even crazier:
In the two programs, a dynamic SQL is used because there are different
WHERE conditions depending on
PREPARE sSQLFS1 FROM :wksql
DECLARE cSQLFS1 CURSOR FOR sSQLFS1
OPEN cSQLFS1
FETCH NEXT FROM cSQLFS1 INTO :record
CLOSE cSQLFS1
When I read with a static SQL, it works
C/EXEC SQL
C+ select *
C+ into : record
C+ from FILEB
c+ where field1 = :wkfield1
c/end-exec
What is the difference between these two SQL with respect to the LIBL
To me it looks like some circumstance is causing the SQL to revert back to
the LIBL of the JDBC.
Best regards
Jana Reimer
Herzliche Grüße
Jana Reimer
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
