Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
IBM i RUNSQL CL Command
has no ratings.
Published: 19 Jun 2013
Revised: 16 Aug 2013 - 1348 days ago
Last viewed on: 24 Apr 2017 (4896 views) 

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.

IBM i RUNSQL CL Command Published by: Bob Cozzi on 19 Jun 2013 view comments(9)

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

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: jjcllhn
Premium member *
Comment on: IBM i RUNSQL CL Command
Posted: 3 years 10 months 4 days 12 hours 38 minutes ago
Edited: Fri, 21 Jun, 2013 at 08:25:17 (1404 days ago)
FWIW, IBM's RUNSQL doesn't allow a SELECT statement as the SQL variable. Statements seem to be limited to SQL statements that create, delete or modify data objects. SELECT is not one of the allowed statements- you'd need to direct the query output to a file for it to be useful in a batch context- something along the the lines that *OUTFILE does for RUNQRY. I agree that IBM couldn't replicate that functionality, or come up with a more de!--script--ive message than "SQL statement not allowed" is pretty lame. Maybe be useful in situations where CL is your only tool, and you want to replace RUNQRY and get the improvements of the new SQL engine over the classic. How important is that as a use case? Wouldn't be very important at my shop. I imagine something like this should work (have to imagine, coz we're still 5.4 until later this summer and IBM's RUNSQL is uplevel): RUNSQL SQL('CREATE TABLE QTEMP/MYCUST AS ( SELECT * FROM cozweb/cozcust ) WITH DATA')
Posted by: arsenal
Premium member *
Comment on: IBM i RUNSQL CL Command
Posted: 3 years 10 months 3 days 18 hours 56 minutes ago

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.

Posted by: Ringer
Premium member *
Comment on: IBM i RUNSQL CL Command
Posted: 3 years 10 months 3 days 15 hours 14 minutes ago

IBM has a tool for doing the SELECTs from a command line: STRQM and STRQMQRY. 

Chris Ringer

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: IBM i RUNSQL CL Command
Posted: 3 years 10 months 3 days 14 hours 28 minutes ago
Edited: Sat, 03 Aug, 2013 at 16:07:51 (1361 days ago)

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.

Posted by: Ringer
Premium member *
Comment on: IBM i RUNSQL CL Command
Posted: 3 years 10 months 3 days 9 hours 41 minutes ago

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

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: IBM i RUNSQL CL Command
Posted: 3 years 10 months 2 days 13 hours 7 minutes ago

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... :)

Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: IBM i RUNSQL CL Command
Posted: 3 years 8 months 19 days 22 hours 17 minutes ago

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)

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: IBM i RUNSQL CL Command
Posted: 3 years 8 months 19 days 13 hours 10 minutes ago

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???

 

 

Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: IBM i RUNSQL CL Command
Posted: 3 years 8 months 9 days 1 hours 16 minutes ago

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!