Midrange News for the IBM i Community


Posted by: WestCoastGuy
Trouble with RUNSQL command
has no ratings.
Published: 20 Mar 2012
Revised: 23 Jan 2013 - 1765 days ago
Last viewed on: 22 Nov 2017 (6334 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.

Trouble with RUNSQL command Published by: WestCoastGuy on 20 Mar 2012 view comments(9)

I have a RUNSQL statement in my CL program -

RUNSQL REQUEST(&QRYSLT) OUTPUT(*OUTFILE) +
    OUTFILE(IA#REPORT) MBROPT(*ADD)      

 

The sql statement is

select * from kevinv/ia#lchsd where hsacno = '10028 '  and hscucd = 'USD'  and hsatro = 'C'  and hsupsn = '11628'  and hsupso = 'BQ'  and hspdat >= 20110401   and hspdat <= 20110531                  

 

The outfile file name is a copy of the queried file , ia#lchsd.

Here is the part of the job log when it runs;

  5500 - CHKOBJ OBJ(*LIBL/IA#REPORT) OBJTYPE(*FILE) MBR(*FIRST)         
  6600 - CPYF FROMFILE(' '/RUNSQL) TOFILE(*LIBL/IA#REPORT) TOMBR(*FIRST)
  MBROPT('*ADD') FMTOPT(*NOCHK)                                         
 15400 - RCVMSG MSGDTA(&MSGDTA) MSGID(&MSGID)                           
 15600 - SNDPGMMSG MSGID(CPD0078) MSGF(QCPFMSG) MSGDTA(X'00000001D6C2D1')
  MSGTYPE(*DIAG)                                                        
Value  for parameter OBJ not a valid name.    

   Error found on CHKOBJ command.                                   
 15400 - RCVMSG MSGDTA(&MSGDTA) MSGID(&MSGID)                    
 15600 - SNDPGMMSG MSGID(CPD0078) MSGF(QCPFMSG)                  
  MSGDTA(X'00000001D6E4E3C6C9D3C5') MSGTYPE(*DIAG)               
Value  for parameter OUTFILE not a valid name.                   
 15400 - RCVMSG MSGDTA(&MSGDTA) MSGID(&MSGID)                    
 15600 - SNDPGMMSG MSGID(CPF0001) MSGF(QCPFMSG) MSGDTA('STRQMQRY')
  MSGTYPE(*DIAG)                                                 
Error found on STRQMQRY command.                                 
 15400 - RCVMSG MSGDTA(&MSGDTA) MSGID(&MSGID)                    
 15600 - SNDPGMMSG MSGID(CPD0078) MSGF(QCPFMSG)                  
  MSGDTA(X'00000001C6D9D6D4C6C9D3C5') MSGTYPE(*DIAG)             
Value  for parameter FROMFILE not a valid name.                  
                                                                 

Thanks in advance!

                       

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

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Trouble with RUNSQL command
Posted: 5 years 8 months 5 days 6 hours 29 minutes ago

Who's RUNSQL command are you using? The one I wrote in the 1980's or one of the myriad clones that came along later? Do you have the source?

The CHKOBJ command looks fine and runs fine on my v7.1 and v5r2 systems.

The CPYF command is obviously not coded correctly--the FROMFILE can't be forward slash RUNSQL "/RUNSQL". I'm speculating that you have an error handler installed in the CL (aka "Message Forwarding" routine). Are the errors you're seeing related to your code here or from elsewhere?

Posted by: WestCoastGuy
Premium member *
Comment on: Trouble with RUNSQL command
Posted: 5 years 8 months 5 days 6 hours 23 minutes ago

Bob,

 

I am running the IBM version of RUNSQL.

I didnt code the copy file command - is that the result of what the 'RUNSQL' translated it to?

Here are more details;

 4000 - STRQMQRY QMQRY(RUNSQLQ) OUTPUT('*OUTFILE') OUTFILE(' '/RUNSQL)   
OUTMBR(*FIRST '*REPLACE') ALWQRYDFN(*YES) SETVAR((LINE01 'select * from 
kevinv/ia#lchsd where hsacno = ''10028 ''') (LINE02 'and hscucd =       
''USD''  and hsatro = ''C''  and hsupsn = ''116') (LINE03 '28''  and    
hsupso = ''BQ''  and hspdat >= 20110401   and hs') (LINE04 'pdat <=     
20110531') (LINE05 ' ') (LINE06 ' ') (LINE07 ' ') (LINE08 ' ') (LINE09 '
') (LINE10 ' '))                                                        
5500 - CHKOBJ OBJ(KEVINV/IA#REPORT) OBJTYPE(*FILE) MBR(*FIRST)          
5800 - CPYF FROMFILE(' '/RUNSQL) TOFILE(KEVINV/IA#REPORT) TOMBR(*FIRST) 
MBROPT('*REPLACE') CRTFILE(*YES) FMTOPT(*NOCHK)                         

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Trouble with RUNSQL command
Posted: 5 years 8 months 5 days 5 hours 50 minutes ago

There is no IBM-supplied RUNSQL command.

There is the IBM RUNSQLSTM command, which runs SQL out of a source file member.

Posted by: WestCoastGuy
Premium member *
Comment on: Trouble with RUNSQL command
Posted: 5 years 8 months 5 days 5 hours 41 minutes ago

Bob,

 

This is what the command RUNSQL looks like in my cl program ;

RUNSQL REQUEST(&QRYSLT) OUTPUT(*OUTFILE) +
    OUTFILE(IA#REPORT) MBROPT(*ADD)      

 

where &qryslt is the value of the select statement that I showed in my initial post

 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Trouble with RUNSQL command
Posted: 5 years 8 months 4 days 9 hours 23 minutes ago

Kind of looks like someone wrote a wrapper for QM, for some reason. You could do a WRKCMD RUNSQL and try to figure out the details, possibly even find source for the CPP.

No matter what RUNSQL really is, FROMFILE(' '/RUNSQL) is obviously a problem. Are you passing a parameter somewhere with blanks where a library name should be?

Posted by: Viking
Premium member *
CA
Comment on: Trouble with RUNSQL command
Posted: 5 years 2 months 7 days 23 hours 52 minutes ago
Edited: Mon, 17 Sep, 2012 at 16:23:11 (1893 days ago)

To follow up on this old thread, IBM does now have a RUNSQL command which was made available via PTF to 6.1 and 7.1.  This new command could cause confusion/problems if someone already has a RUNSQL command they created in the past.  Here is a link to an article that discusses this issue:

http://www.itjungle.com/fhg/fhg053012-story01.html

 

WestCoastGuy posted the example RUNSQL REQUEST(...) and I think DaleB is right that he is using a wrapper for QM.  Many people created a RUNSQL command to simplify running SQL from CL and therefore have programs with RUNSQL REQUEST(...).  But the new IBM RUNSQL command does not have REQUEST as a valid keyword, so these programs will suddenly fail (unless the programs qualify the command with the library where their custom RUNSQL command is) once PTFs are applied that gives them the new IBM RUNSQL command (since IBM's command is in QSYS and will probably be found first by the program.)

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Trouble with RUNSQL command
Posted: 5 years 2 months 7 days 20 hours 43 minutes ago

Right Viking, I saw that IBM announced and had some words with them regarding their choice of name. Their response "IBM reserves the right to use any CL command name so if you create your own, you may have to change that name in the future."

In other words "Fuck you Bob."  Good thing this is probably the final new CL command for a decade since nobody at IBM knows how to code CL anymore.

Posted by: TFisher
Premium member *
Comment on: Trouble with RUNSQL command
Posted: 5 years 1 months 20 days 5 hours 56 minutes ago
Edited: Wed, 03 Oct, 2012 at 10:42:54 (1877 days ago)

IBM has released their version of RUNSQL in V7. 

 

It's always important to review their release notes telling you what's new in a release before you install it.  I have had new IBM system commands that are named the same as my commands in the past (at least once, if not twice).  We had to delay installing a new release once so I could rename my command and check out programs that used my command to fix them.

 

By the way, you don't have to know CL to create commands since the CPP can be in any language.  I expect to continue to see new commands in future releases.

 

Regarding the errors posted, I don't see anything really related with the RUNSQL command. But from what I see in your joblog that you posted, it appears that your problem is the CPYF command - the library is not valid (as Bob said):

 

CPYF FROMFILE(' '/RUNSQL)

Posted by: sarge
Premium member *
United States
Comment on: Trouble with RUNSQL command
Posted: 5 years 1 months 20 days 1 hours 52 minutes ago

one of the reasons that i had "GPL" in the library list for all profiles. created this right after my first upgrade that wiped out a couple of commands that were created by my staff, into QGPL, of course.

-sarge