SQLRPGLE and the PREPARE Keyword

[Fooch]

I am trying to use SQL to remotely change files on other machines. They exist on different libraries on different machines, so I HAVE to dynamically change the Library, and possibly file, so I need to PREPARE an SQL statement...but I am having some issues using fields in the PREPARE statement. Here is the code as it sits, not working:

SQLString = 'Select Count(*) Into ? From ' + %Trim(Library)+ '/' + %Trim(File)Where TRMNUM = :NewTrm';

Exec SQL
PREPARE CheckIfExists FROM :SQLString;

Exec SQL
Execute CheckIfExists USING :Count;

And it Says that "?" is invalid...but isn't that the SQL wildcard? I tried replacing the ? with :Count, but it doesn't like that, says the value is "unusable". I read that you can't use RPG variables in the PREPARE statement...so can anyone help me out with how to use this correctly?

 

[vbMax]

Try the following...

SQLString = 'SELECT COUNT(*) FROM ' +
%Trim(Library)+ '/' + %Trim(File) +
' WHERE TRMNUM = ''' + NewTrm + '''';
EXEC SQL PREPARE SQL_STMT FROM :SQLString;
EXEC SQL DECLARE C1 CURSOR FOR SQL_STMT;
EXEC SQL OPEN C1;
EXEC SQL FETCH C1 INTO S1;


Note: This example works if TRMNUM is a character field. If TRMNUM
is numeric, use the following line.
SQLString = 'SELECT COUNT(*) FROM ' +
%Trim(Library)+ '/' + %Trim(File) +
' WHERE TRMNUM = ' + NewTrm;

- vbMax

 

[Fooch]

It worked! thanks.