Why do RUNSQL and RUNSQLSTM not run like STRSQL

[John192]

Hi,


When trying to execute SQL queries why do they run when we do STRSQL and the same SQL query does not run when we try to run them using RUNSQL and RUNSQLSTM commands?



Thanks

[Paulster]

Hi John,

It could be something as simple as qualified names that would cause an SQL to run in one but not the other. Like MYLIB.MYFILE versus MYLIB/MYFILE. What sort of errors do you get?

Personally, I never use STRSQL to develop anything. The thing is really old and does not support any of the new stuff that has been added last decades. Instead I use the 'Run SQL Scripts' option in ACS. That way, what I develop there fits quite well for embedding in RPG, or in CLLE with the RUNSQL command.

Remember that RUNSQL only allows a limited set of SQL statements. From what I recall, it was a few years ago I last used it, you cannot do a 'SELECT * FROM MYTABLE'.

Happy coding!
-----------------
Paul
Sweden

[B.Hauser]

If you try to execute a SELECT Statement with RUNSQL or RUNSQLSTM it will not work. For SELECT Statements you need to define an output because you normally want to do something with the output, i.e. display it, modify the content and then update the row etc.

RUNSQL supports only commands like Insert, Update, Delete, CREATE TABLE ...
RUNSQLSTM executes only an SQL script where is no way to receive a result of the SELECT statement.

Birgitta

[MFisher]

Personally, I never use STRSQL to develop anything. The thing is really old and does not support any of the new stuff that has been added last decades. Instead I use the 'Run SQL Scripts' option in ACS. That way, what I develop there fits quite well for embedding in RPG, or in CLLE with the RUNSQL command.

Same here.

[GlennGG]

What do you mean when you say they don't run? Can you provide more specifics? It can't find a table, a function, or what?
When you prompt RUNSQL or RUNSQLSTM, is NAMING set to *SYS or *SQL?
In STRSQL, F13, option 1, is Naming convention set to the same value?
Are you qualifying your tables/functions?

I'd stop using STRSQL and only use Run SQL Scripts in ACS but the same issue arises there too. You must set your environment for your needs.

[Paulster]

STRSQL and WRKQRY both use the old SQL engine that has not been enhanced or optimized bij IBM in over a decade. The reason being that IBM chose to enhance a more modern DB2 and SQL engine. They probably found the old engine to be a dead end.
You can still use the old stuff but it is not self-learning as the new stuff is. As a result, the new stuff will not only be faster, but any enhancements will be deployed in the new engine only.
You could compare it to RPGII and the latest in RPG free.

Happy coding
----------------
Paulster
Sweden

[B.Hauser]

@Paulster,
that is not or no longer true.
Since Release 7.2 all database access (incl. Native I/O, Query/400, OPNQRYF, UPDDTA) is performed with the SQE.
CQE is/should no longer be used.
In 7.2 and 7.3 you could use the QAQQINI SQE_NATIVE_ACCESS Option to reroute Query/400 and OPNQRYF queries to the CQE.
But since Release 7.4 this option is ignored, so everything is runnig with SQE.