Re: Embedded SQL question

On 02-Sep-2014 06:28 -0500, rob@xxxxxxxxx wrote:

Bill Howie on 09/02/2014 07:23 AM wrote:

Not sure if this really falls in the RPG group or not, but since
there isn't an SQL group I will put it here. I have a program whose
concept is to take 3 incoming parameters (file name, field name,
and company number) and load them into an embedded SQL statement
and execute that statement.

Here is my code:

D File_Name S 10A
D Field_Name S 10A
D Company_Name S 2A
D Bogus_Field S 2A
D MySQLText S 300A varying
D Result S 100A varying

* Parameters:
* ~~~~~~~~~~~
*
C *Entry Plist
C Parm File_Name
C Parm Field_Name
C Parm Company_Name

/free

MySQLText = 'Update ' + %Trim(File_Name) +
' Set ' + %Trim(Field_Name) +
' = ' + %Trim(Company_Name);

exec sql
SET OPTION
commit=*none,
datfmt=*iso;

exec sql prepare SQL_Stmt from MySQLText;
exec sql execute SQL_Stmt;

*inlr = *on;

/end-free

Pretty basic. When it executes I get the following error:

Column or global variable MYSQLTEXT not found.
Prepared statement SQL_STMT not found.

Definitely an RPG question. Without a doubt.

FWiW: Given the errors would be identical, irrespective the host language, I disagree; I see the scenario as solely an SQL issue.

In fact, there seems little reason to even use any language other than SQL [ignoring the fact that the SQL routine is compiled as C]; i.e. use just the SQL, without any Host Language:

create procedure UpdCompanies
( File_Name in varchar(10)
, Field_Name in varchar(10)
, Company_Name in varchar(02)
) language sql
set option dbgview=*SOURCE,commit=*NONE,datfmt=*ISO
begin
declare MySQLText varchar(300) ;
declare dlm char(01) default '''';
set MySQLText =
'Update ' concat File_Name
concat ' Set ' concat Field_Name
concat ' = ' concat dlm concat Company_Name concat dlm ;
execute immediate MySQLText ;
end

But, the answer is change
exec sql prepare SQL_Stmt from MySQLText;
to
exec sql prepare SQL_Stmt from :MySQLText;
The former is looking for a variable outside of your RPG program.
The latter is looking for one of your RPG variables.

Since the introduction of the SQL Global Variable, places where only a Host Variable [syntax of colon followed by an identifier] had been allowed now also allow for a Global Variable. Some error messages are since necessarily more generic to cover the added variants for the use of [the term] /variable/; that particular error would be better to have omitted [presumably, never should have added] the term /global/ in the first level text.

FWiW, there is almost no reason in the described scenario to even do a PREPARE. Just EXECUTE IMMEDIATE the string in the Host Variable, much like the SQL variable in the above SQL routine. Additionally, even after the correction to use a Host language Variable in the scenario for the OP, the syntax of the statement is quite probably incorrect, in that the Company_Name is likely a character value to represent a character literal [constant] that must be delimited rather than a two-byte column-name; so again, much like the above SQL routine does, but in this case the concatenating of the apostrophe on either side of the Company_Name variable.