Re: Accessing Remote Database using SQL - requesting an SQL Package?

On 28-Jul-2014 13:55 -0500, Needles,Stephen J wrote:

I am on release 7.1 and the latest TR.

I had a program that used DDM to read data from a remote IBMi (same
release level). Recent changes have caused a CLOB column to be added to
the table. This renders DDM a problem.

If the data in the new column is not required of the existing app., then the DDMF pointing to a Logical File (LF) that omits the LOB column, or casts the CLOB column to a VARCHAR [with the obvious length limitations] may be an option.

So I thought to use SQL to solve the issue.

<<SNIP>>

// connect to remote machine - tested the connection during one
// of the runs and verified connectivity
exec sql CONNECT TO remote_machine USER :user USING :pwd;

// prepare TESTCursor to retrieve data from remote system
exec sql close TESTCursor;
exec sql declare TESTCursor cursor for select * from needles.TEST;
exec sql open TESTCursor; /* <-- Failure point */
<<SNIP>>

With the new three-part naming, I expect the following [noting: I have never used\tested the capability] may be supported without any requirement for an SQL Package?:

select * from remote_machine.needles.TEST

At the point where the cursor TESTCursor is opened, I encounter an
SQL0805 error.

"SQL package TESTPGM99 in LIBRARY not found at DRDA Server."
<<SNIP>>

I've no experience of consciously using SQL Packages before and, in
spite of RTFM, I can't figure this out.

The Create SQL Package (CRTSQLPKG) or the Create SQL ILE RPG Object (CRTSQLRPGI) with the RDB(remote_machine) SQLPKG(*OBJLIB/*OBJ) should cause a package to be created for the program object that is created on the local system.

I recall reading some docs mentioning new support for implicit package creation, but given I have no access to any systems, I did not read anything other than the cursory comment that there is such a thing. For the described scenario, such a capability would seem apropos. Maybe that support is only for static rather than dynamic SQL.

FWiW: I was going to test a source with effectively the above [remaining; non-snipped quoted SQL] and record a script for creating a functional scenario from that source, but my setup for the special-case Remote Database Directory Entry named ME [i.e. RDB(ME) that points to the IP LOOPBACK] was not [no longer; long since, had been] operational on the public v5r3 system I was trying to use.