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.