Chuck,
I tried using the three-part name scheme as well without success in the creation of the cursor. I still get the SQL0805 error on the open of the cursor.
I did discover that you cannot mix locations within an SQL when using the three-part naming scheme, at least not in this example:
Delete from remote_machine.needles.test where fld1 in (select fld1 from local_machine.needles.test)
The result is an SQL0512 error: Statement references objects in multiple databases.
I tried to create the SQL package as you describe (CRTSQLPKG) and while the command's execution indicated success, this was not the case. The run time error continues (SQL0805).
BTW - the CLOB data is required...all of it.
I'll continue struggling with it today.
Steve Needles
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, July 28, 2014 4:54 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: 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.
--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
________________________________
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.
TRVDiscDefault::1201
midrange.com
