Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
I wrote the original RUNSQL command back in 1989. About a year ago, IBM told me they were shipping a "RUNSQL" CL Command I needed to modify my command name. I thought: Really, after 25 years, you finally ship a CL SQL command and it has to be named "RUNSQL"?
Anyway, just for grins, I migrated my version to RPG IV and rebuilt it this morning.
RUNSQL/RUNSQL SQL('select * from cozweb/cozcust')
This worked fine.
Then I passed the same SQL statement to the IBM RUNSQL command and got the following message:
RUNSQL SQL('select * from cozweb/cozcust') SQL statement not allowed. RUNSQLSTM command failed.
Apparently you can not use the SELECT statement and IBM also uses RUNSQLSTM as the engine. Not to sound like a jerk, but this is really a lame piece of crap! Any 3rd-rate Programmer could have built the IBM version of RUNSQL in 10 minutes using Embedded SQL within RPG IV and the EXECUTE IMMEDIATE statement. How is this an important addition to IBM i?
IBM's RUNSQL CL command is a #FAIL
Two things to consider:
Should it not run in batch?
How would you process the reslut set of a SELECT-statement in such case? If it allows a similar implementation like OPNQRYF, then it is definitely a big omission. If you want to see data from a file you still use RUNQRY.
But I agree: although I cannot use this command it lacks some important functionailty.
IBM has a tool for doing the SELECTs from a command line: STRQM and STRQMQRY.
Chris Ringer
Chris, RUNQRY displays files in a "SELECT * FROM xxx/yyyx" manner, but STRQM and STRQMQRY do not run queries (or SQL statements) from the Command line. They require a pre-existing QM Query and have to be created.
Arsenal, I simply would have preferred that SELECT * FROM xxx/yyy work interactively. I arleady have that, and do that with my own RUNSQL command, and it is odd that IBM choose to not include that capability in their version some 25 years after I wrote my version.
Bob, it takes 10 seconds to create a QM with "select * from cozweb/cozcust" in it. Would have been nice if IBM had linked the new RUNSQL statement to QM. If you want Ad Hoc queries, use STRSQL right? Or maybe you are pulling the query text from a file and want to do a RUNSQL SQL(&MySQL), then yeah, QM won't cut it.
Chris Ringer
I'm not trying to do anything. I'm disappointed that IBM did not include the "SELECT..." statement in their RUNSQL statement 25 years after I produced one that did include it. If they only hadn't hijacked my command name... :)
I'm using the same trick as jjcllhn and it works fine for me. Plus, I get to get rid of the silly QM query stuff that I've been trying to avoid like the plague for years.
Never had access to Bob's old RUNSQL command but the new one I do have works fine for me. Sorry Bob! :o)
What a it should do as a minimum is to give interactive results as Bob's does. But why not allow the same parameter as most other commands: Output . . . . . . . . . . . . . * *, *PRINT, *OUTFILE
And why not make it full featured so that you can direct output as *PDF, *HTML, *FTP, *CSV, *TXT
Why shouldn't the OS provide this functionality? Why should I have to do extra programming and/or package purchases just to be able to distribute DB query results???
Steve,
I do like you suggestion on the *PDF, *FTP, *CSV, *TXT etc output options! And indeed, why do we always have to make stuff to get these PC file types? As you say: should be in the OS!