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



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.