英文:
Using a variable for a database file in embeded SQL in free RPG
问题
我想编写一个SQL语句，无论想要的数据文件是什么，都可以正常工作。
示例：
exec sql select * from :variable;
我在“：”处遇到了SQL0104错误
“令牌无效。有效令牌：(NEW FINAL TABLE UNNEST LATERAL MLTABLE JSON_TABLE<IDENTIFIER>)”
我尝试在不同的语句中使用“insert into :variable”，但我仍然遇到相同的错误。
也许这是不可能的，我必须将数据库文件硬编码到我的代码中，或者是否有其他方法可以绕过这个问题？
谢谢
英文:
I would like to write an sql statment, that works no matter what data file is wanted.
example:
exec sql select * from :variable;
I am getting a SQL0104 Error at the ":"
"Token not vaild. Valid Token :(NEW FINAL TABLE UNNEST LATERAL MLTABLE JSON_TABLE<IDENTIFIER>"
I tried to do it in different statemenst with "insert into :variable", but I get the same Error.
Is it maybe not possible and I have to hard code the databasefile into my code or is there a way around?
Thanks
答案1
得分: 2
您不能使用具有可变表名的静态SQL。
您需要使用EXECUTE IMMEDIATE或PREPARE和EXECUTE来使用动态SQL。
wMySql = 'insert into ' + wTableName + ' values (1, ''ABC'')';
exec SQL
execute immediate :wMySql;
请注意，SELECT * FROM TABLE不是可以动态运行的语句。就此而言，静态版本没有意义。SELECT ... INTO :myData FROM MYTABLE是有效的静态语句，但不能动态使用。相反，您需要使用VALUES (SELECT * from MYTABLE) into :myData，这可以动态运行。
这里是一个使用动态SQL的IBM示例。通常情况下，您会在循环中看到FETCH以返回多行，如写入的情况下，这将仅返回第一行然后结束。这还展示了在语句中使用替代变量?的用法。这是一种最佳实践，因为它可以防止SQL注入攻击。但请注意，您不能将替代变量用于表名或列名。
//**************************************************
//* 声明程序变量。 *
//* STMT初始化为 *
//* 列出的SQL语句。 *
//**************************************************
DCL-S EMPNUM CHAR(6);
DCL-S NAME CHAR(15);
DCL-S STMT CHAR(500)
INZ('SELECT LASTNAME FROM CORPDATA.EMPLOYEE +
WHERE EMPNO = ?');
...
//**********************************************************
//* 根据声明部分中初始化的STMT准备STMT *
//**********************************************************
EXEC SQL PREPARE S1 FROM :STMT;
//*************************************
//* 为STMT声明游标 *
//*************************************
EXEC SQL DECLARE C1 CURSOR FOR S1;
//*******************************************************
//* 分配员工号以在选择语句中使用 *
//*******************************************************
EMPNUM = '000110';
//**********************
//* 打开游标 *
//**********************
EXEC SQL OPEN C1 USING :EMPNUM;
//*********************************************
//* 检索记录并将LASTNAME的值放入NAME *
//*********************************************
EXEC SQL FETCH C1 INTO :NAME;
//******************************
//* 在这里处理NAME的程序 *
//******************************
...
//******************
//* 关闭游标 *
//******************
EXEC SQL CLOSE C1;
英文:
You can't use static SQL with a variable table name.
You'd need to use dynamic SQL via EXECUTE IMMEDIATE or PREPARE and EXECUTE
wMySql = 'insert into ' + wTableName + ' values (1,''ABC'')';
exec SQL
execute immediate :wMySql;
Note that SELECT * FROM TABLE is not a statement that can be dynamically run. For that matter, a static version doesn't make sense. SELECT ... INTO :myData FROM MYTABLE is a valid static statement, but can't be used dynamically. Instead you'd need to use VALUES (SELECT * from MYTABLE) into :myData which can be run dynamically.
Here's an IBM example using dynamic SQL.
Note normally you'd see the FETCH in a loop to return multiple rows, as written this will just return the first row then end. This also show the use of replacement variables ? in the statement. Which is a best practice as it prevents SQL injection attacks. However note that you can't use a replacement variable for the table name or column names.
//**************************************************
//* Declare program variables. *
//* STMT initialized to the *
//* listed SQL statement. *
//**************************************************
DCL-S EMPNUM CHAR(6);
DCL-S NAME CHAR(15);
DCL-S STMT CHAR(500)
INZ('SELECT LASTNAME FROM CORPDATA.EMPLOYEE +
WHERE EMPNO = ?');
...
//*************************************************************
//* Prepare STMT as initialized in declare section *
//*************************************************************
EXEC SQL PREPARE S1 FROM :STMT;
//*************************************
//* Declare Cursor for STMT *
//*************************************
EXEC SQL DECLARE C1 CURSOR FOR S1;
//*****************************************************
//* Assign employee number to use in select statement *
//*****************************************************
EMPNUM = '000110';
//**********************
//* Open Cursor *
//**********************
EXEC SQL OPEN C1 USING :EMPNUM;
//***********************************************
//* Fetch record and put value of *
//* LASTNAME into NAME *
//***********************************************
EXEC SQL FETCH C1 INTO :NAME;
//********************************
//* Program processes NAME here *
//********************************
...
//******************
//* Close cursor *
//******************
EXEC SQL CLOSE C1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论