RE: Executing a program through an ODBC/JDBC connection? -- MIDRANGE-L

For ODBC and JDBC, there are two naming conventions:

*SQL - SQL naming convention
*SYS - system naming convention

The default, for ODBC at least, is *SQL.

On Windows ODBC configuration, those are on the Server tab. For JDBC, it can be specified in the connection string.

With *SQL naming, you specify qualified names as schema.table. On the ODBC Server tab, there is a "Default SQL Schema or Library" entry, I'm a bit hazy on that as I have been using *SYS naming for my connections.

With *SYS naming, you specify qualified names as library/file, or just specify the library list on the ODBC Server tab.

You can specify the naming convention and library list in the connection string if you want to do it in a program.

Craig Pelkie

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Friday, September 27, 2024 14:23
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Executing a program through an ODBC/JDBC connection?

ODBC, JDBC, and ADO all allow you to set the library list on the connection.

Greg, you're actually hurting performance by changing the library list like that if you use different library lists for different things.

CHGLIBL causes full closes of ODP and corresponding full opens.

Must better to set it on the connection. And if you're using connection pooling, then you'll have basically seperate pools for each LIBL being used.

Charles

On Fri, Sep 27, 2024 at 12:42 PM Gregory Martin <mr.gamartin@xxxxxxxxx>
wrote:

We found a way to set the library list by calling a program in an ADO
connection. Not sure if it can be applied to ODBC too.

We have used the QSYS2.QCMDEXC routine and passed in the programs that
we call via the ADO connection in Excel. We had to create a temporary
file to combine the data and then pull it via SQL.

Example of our code from excel to call and set library list and then
execute a program.

'Use the IBM DB2 i5/OS OLE DB Provider and set the system to connect to
strConn = "PROVIDER=IBMDA400; DATA SOURCE=" & strSystem

'Now open the connection.
cni5OS.Open strConn
bcnOpen = True

Dim cmdi5OS As ADODB.Command
Set cmdi5OS = New ADODB.Command
bCmd = True

With cmdi5OS
.ActiveConnection = cni5OS
' Set Library List for Company XX
.CommandText = "{CALL QSYS2.QCMDEXC ('CALL SETLIBLC XX')}"
.CommandType = adCmdText
.Prepared = True
.Execute
' Execute Build of A/R Trial Balance
.CommandText = "{CALL QSYS2.QCMDEXC ('CALL ARP503CL')}"
.CommandType = adCmdText
.Prepared = True
.Execute
' Restore Library List
.CommandText = "{CALL QSYS2.QCMDEXC ('CALL RSTLIBLC')}"
.CommandType = adCmdText
.Prepared = True
.Execute
End With
Set cmdi5OS = Nothing
bCmd = False

Thanks,
Greg Martin
mailto:mr.gamartin@xxxxxxxxx

On Fri, Sep 27, 2024 at 2:27 PM John Yeung
<gallium.arsenide@xxxxxxxxx>
wrote:

On Fri, Sep 27, 2024 at 11:59 AM Charles Wilt
<charles.wilt@xxxxxxxxx>
wrote:

That has nothing to do with the fact that there's no store
procedure registered and everything to do with the (lack of)
library list

configured

for ODBC/JDBC.

It is possible to configure a library list for ODBC (and presumably
JDBC) from the client side, when setting up the connection. As far
as I can tell, this is rarely done. The usual thing to do, if
library list is important, is to wrap whatever it is you want to
call in a CLP that sets up the library list (and any other relevant
environment details), and call that wrapper.

My memory is hazy, but I *think* an ODBC connection will default to
the library list of whichever user profile was used to make the
connection.

Regardless, I think the CLP wrapper is the way to go, because that
is the most explicit, obvious, and maintainable approach. I avoid
relying on configuration details (like user profile/job description,
ODBC connection parameters, etc.) whenever possible.

One other thing to note: if for whatever reason CALL statements
aren't supported by the ODBC/JDBC client, you can always use the
QSYS2.QCMDEXC scalar function to issue an arbitrary CL command.

John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.