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

On 29-Jul-2014 14:40 -0500, Needles,Stephen J wrote:

Ok... I figured out what caused the SQL0805 error... <<SNIP>>

Moving along :-)

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.

IIRC that indicates the sender of the message has blanks in the replacement variable &1; i.e. the message handler replaces the &1 with the trimmed result of all blanks, which is the empty string. That is likely a defect, even if the -312 condition is validly being issued. The SQL pre-compiler probably is supposed to properly identify the variable name reference; irrespective the specification is not a defined variable in the host language, or is otherwise unusable.

<<SNIP>>

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_)

We do not know what field /KEY/ is defined as in the Record Rormat, nor what the RPG says is the type\size declaration of variable D_KEY

dtest_row ds
d key like(d_key)

Still struggling along.

I would suggest trying to declare the variable KEY in different ways than shown, testing to see if\what variants might /circumvent/ the issue. I would try each of:

as DS component with explicit typing; not the LIKE()
standalone variable with the LIKE()
standalone variable with explicit typing, not the LIKE()
as DS component with LIKE() but no prefix in ext-DS and LIKE()

If /key/ is a DATE data type, then be sure to specify the SQL SET OPTION DATFMT=*ISO [or SQL compiler Date Format attribute] because IIRC, that mismatch between the RPG date format and the SQL date format is a common origin for -312; due to the default DATFMT(*JOB).