I tested using the three-part name via STRSQL to do the following tasks:
-> Retrieve a key from the remote table
select key into :source_key from remote_machine.needles.test order by row_timestamp fetch first row only;
-> copy the keyed row from the remote table to the local version
insert into local_machine.needles.test (select * from remote_machine.needles.test where key = :source_key);
-> delete the keyed row from the remote table
delete from remote_machine.needles.test where key = :source_key;
this seems like it will work (assuming that it can be encapsulated within an RPG program).
I retested without the CONNECT stuff to see if the cursor could be opened with the three-part name and found that the SQL0805 error persists.
exec sql close TESTCursor;
exec sql declare TESTCursor cursor for
select * from remote_machine.needles.TEST;
exec sql open TESTCursor;
exec sql fetch TESTCursor into :TEST;
after executing the OPEN, the SQLCOD = -000000805.
Steve Needles
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Tuesday, July 29, 2014 8:19 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Accessing Remote Database using SQL - requesting an SQL Package?
On 29-Jul-2014 06:47 -0500, Needles,Stephen J wrote:
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.
Hmm. I meant to imply ridding of all of the CONNECT stuff; i.e. just code the one statement to effect the redirect to the other system with the system-qualified table-reference. If the statement(s) include the system name, I suppose there should be an implicit connect, and thus that an explicit connection would be undesirable.?
<<SNIP>>
I tried to create the SQL package as you describe (CRTSQLPKG) and
while the command's execution indicated success, this was not the
case.
Was the *SQLPKG object created [owned to the user profile for the request]? The package also should be tracked in the catalogs; SYSPACKAGE based-on data in QADBPKG [access to data authorized via QADBLPKG, beyond the catalog VIEW].
The run time error continues (SQL0805).
<<SNIP>>
I was originally under the impression from the OP that the connection and statement had been tested with Start Interactive SQL (STRSQL), but I reread that, and I am now unsure. Possibly there could be an issue with the RDB\DRDA setup; if the STRSQL CONNECT and SELECT from the remote_machine function well, then probably everything is OK with that.
I would also separately test the "select * from remote_machine.needles.test" within a STRSQL [that does not first ask for a connection; defaulting to a *LOCAL connection] to verify that request operates without any issues nor requiring anything extra.
--
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
