Ok...i figured out what caused the SQL0805 error...
The library I was developing in didn't exist on the remote_machine, so the compiler couldn't get the SQLPKG to the remote_machine. I had the library created on the remote_machine and now the SQL0805 error is gone. The SQLPKG had someplace to go that the compiler could recognize.
But now I get an SQL0312 error (Variable xxx not defined or usable), except that no variable is noted. And the only variable in the SQL is readily viewed when debugging the code in STRDBG.
"Variable not defined or not usable." Note: There are two blanks after "Variable" where the 'bad' variable should be. The description for this the SQL0312 message does not indicate what to do if there is no identified variable. I looked in the great and powerful internet and discovered that Google doesn't know everything. I found no references that were like this instance.
The only variable in that SQL is defined using like() from the table.
exec sql insert into local_machine.needles.test
(select * from remote_machine.needles.test
where key = :key );
dtest e ds extname(test)
d prefix(d_)
dtest_row ds
d key like(d_key)
Still struggling along.
Steve Needles
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Needles,Stephen J
Sent: Tuesday, July 29, 2014 10:54 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: Accessing Remote Database using SQL - requesting an SQL Package?
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
--
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.
midrange.com
