Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
softcoding a library in select statement?
has no ratings.
Published: 31 Aug 2011
Revised: 19 Feb 2014 - 1311 days ago
Last viewed on: 22 Sep 2017 (5428 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.

softcoding a library in select statement? Published by: Chris Proctor on 31 Aug 2011 view comments(14)

Good morning. I have a situation where I need to specify the library in the the SELECT statment, because it is pointing to a remote system and they may want it to access the test or the production file. I've coded a variable to contain the desired library name, but now I'm trying to figure out the best way to change the SQL statement.

 

Here's the current statement:

    exec sql select imitm 
      into :o_styitm
      from csuqadat/f4101lc 
      where imlitm = :o_$dsrp0;

 

Do I have to do something like the following?

        sqlstmt = 'select imitm into o_styitm from ' + %trim(libname) + '/f4101lc where imlitm = o_$dsrp0';
        exec sql :sqlstmt;

My problem with the above is that I will be excuting this code a ton of times to retrieve item information. Surprised Any suggestions would be appreciated.

 

Thanks!

Good morning. I have a situation where I need to specify the library in the the SELECT statment, because it is pointing to a remote system and they may want it to access the test or the production file. I've coded a variable to contain the desired library name, but now I'm trying to figure out the best way to change the SQL statement.

 

Here's the current statement:

    exec sql select imitm 
      into :o_styitm
      from csuqadat/f4101lc 
      where imlitm = :o_$dsrp0;

 

Do I have to do something like the following?

sqlstmt = 'select imitm into o_styitm from ' + %trim(libname) + '/f4101lc where imlitm = o_$dsrp0';

exec sql :sqlstmt;

 

 

 

My problem with the above is that I will be excuting this code a ton of times to retrieve item information. Surprised Any suggestions would be appreciated.

 

Thanks!

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

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: softcoding a library in select statement?
Posted: 6 years 23 days 7 hours 30 minutes ago

Not sure how being remote database affects this, but locally you'd do an EXECUTE IMMEDIATE, or a PREPARE and EXECUTE, depending on whether you were going to execute the statement only once or repeatedly. The appropriate library name is just part of the string (character variable) that makes up the statement.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: softcoding a library in select statement?
Posted: 6 years 23 days 7 hours 28 minutes ago

I would still have to use a char variable for the SQL command, right Dale?

Posted by: DaleB
Premium member *
Reading, PA
Comment on: softcoding a library in select statement?
Posted: 6 years 23 days 7 hours ago
Edited: Wed, 31 Aug, 2011 at 12:26:34 (2214 days ago)

Normally you concatenate everything into the character variable up front. You'll have to use a parameter marker for the INTO variable. I believe you'll need to CAST it to force the data type. Something like this:

     Dsqlstmt          S            200A
     DSTT_Normal       C                   '00000'
      /FREE
       sqlstmt = 'SELECT imitm INTO CAST(? AS CHAR(7)) FROM '
               + %TRIM(libname) + '/f4101lc WHERE imlitm = ?';
       Exec SQL PREPARE sqlstmt1 FROM :sqlstmt;
       If SQLSTATE<>STT_Normal;
         // PREPARE failed
       EndIf;
       // Only do the EXECUTE if the PREPARE was OK...
       Exec SQL EXECUTE sqlstmt1 USING :o_styitm, :o_$dsrp0;
      /END-FREE

You need PREPARE and EXECUTE, because of the parameter markers. The EXECUTE can be done repeatedly, with different values of o_styitm and o_$dsrp0, or even different variables in the USING.

But the table and schema name (the library name), cannot be variables. Every time you need to change the library name, you'll have to rebuild sqlstmt and PREPARE again.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: softcoding a library in select statement?
Posted: 6 years 23 days 6 hours 49 minutes ago

Got it! Thanks, Dale!

Posted by: chrisp
Premium member *
Portland, OR
Comment on: softcoding a library in select statement?
Posted: 6 years 23 days 5 hours 9 minutes ago

Hi Dale! Well, it doesn't like the CAST on the PREPARE. I've never used cast before, but it's giving me a -104 error. This is what the statement looks like:

 

select imitm into cast(? as char(24)) from csstgdat/f4101lc
where imlitm = ?                                           

Any ideas? Gracias, again!

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: softcoding a library in select statement?
Posted: 6 years 23 days 4 hours 58 minutes ago
Edited: Wed, 31 Aug, 2011 at 14:47:07 (2214 days ago)

Normally, I "just" do the following:

  1. Use *SYS naming schema
  2. Change the current lib to the target library
  3. Often set the SCHEMA = targetLib

Normally this works, and I don't have to use dynamic or runtime SQl to make it work. But if changing the library list or current library is out of the question, then the PREPARE is the way to go.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: softcoding a library in select statement?
Posted: 6 years 23 days 4 hours 18 minutes ago
Edited: Wed, 31 Aug, 2011 at 15:03:06 (2214 days ago)

I've never tried a parameter marker for the INTO variable(s). It's quite possible that that's simply not allowed. One possible workaround would be to use a cursor with the dynamic statment:

     Dsqlstmt          S            200A
     DSTT_Normal       C                   '00000'
      /FREE
       sqlstmt = 'SELECT imitm FROM '
               + %TRIM(libname) + '/f4101lc WHERE imlitm = ?';
       Exec SQL PREPARE sqlstmt1 FROM :sqlstmt;
       If SQLSTATE<>STT_Normal;
         // PREPARE failed
       Else;
         Exec SQL DECLARE Cursor1 AS CURSOR FOR sqlstmt1;
         // No need to check SQLSTATE; DECLARE is not executable
         Exec SQL OPEN Cursor1 USING :o_$dsrp0;
         If SQLSTATE<>STT_Normal;
           // OPEN of cursor failed
         EndIf;
       EndIf;
       // FETCH could be in a loop, and, of course, only FETCH if OPEN was OK
       Exec SQL FETCH Cursor1 INTO :o_styitm;
       If SQLSTATE<>STT_Normal;
         // FETCH failed (most likely EOF, but check SQLSTATE)
       EndIf;
       // And, eventually...
       Exec SQL CLOSE Cursor1;
      /END-FREE

Posted by: chrisp
Premium member *
Portland, OR
Comment on: softcoding a library in select statement?
Posted: 6 years 22 days 8 hours ago

Hi Bob... Thanks for your input. I think I'd like to look further into your approach, since everything I've been trying has not been working. Could you give me a little more detail? I'm not exactly sure what you mean by "Use *SYS naming schema" and "often set the schema=targetlib".

I'm trying to specify which library I access on a remote database, so I don't believe chgcurlib would work, right?

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: softcoding a library in select statement?
Posted: 6 years 22 days 7 hours 53 minutes ago

Is the file a DDM file? How is it associated with the remote system?

Posted by: chrisp
Premium member *
Portland, OR
Comment on: softcoding a library in select statement?
Posted: 6 years 22 days 7 hours 48 minutes ago

It's in the compile RDB() parm with an associated SQL package on the remote system.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: softcoding a library in select statement?
Posted: 6 years 22 days 7 hours 39 minutes ago

I guess what confuses me about using the parameter markers in the SQL statement is that it has to be defined in a string variable in order to execute the PREPARE, so why does it not accept a CAST or a '?' as being valid for the INTO variable????? I've even tried defining a work variable and put in in the INTO as "workvar" and ":workvar". Neither one worked!!!

Both times it came back with the sql error -104, telling me that the "token is invalid".

It's getting pretty frustrating. There has to be a way of making it work, I just haven't found it yet!! Cry


 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: softcoding a library in select statement?
Posted: 6 years 22 days 7 hours 32 minutes ago
Edited: Thu, 01 Sep, 2011 at 11:48:50 (2213 days ago)

No, you can't use parameter markers to identify an INTO variable. You can only use the value in a variable as data to be inserted in place of the parameter markers.

As far as switching libraries on a remote system via SQL at runtime--maybe you can do it, but I don't think you can. You would need to have different files set up and then point at the file that is in the library on the remote system.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: softcoding a library in select statement?
Posted: 6 years 22 days 7 hours 19 minutes ago
Edited: Thu, 01 Sep, 2011 at 12:05:56 (2213 days ago)

I didn't think so, Bob. So, none of the below SELECTS would be valid for a PREPARE?

 

sqlstmt = 'select fielda into :flda where fieldb = ?';


 sqlstmt = 'select fielda into flda where fieldb = ?';

 

If these aren't allowed, I'm assuming that there is no way, programmatically, to do what I'm trying to do short of a cursor, which doesn't seem like a good way of handling it, since it would open and close the cursor for every single record it processes, right?

 

Thanks, Bob.


 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: softcoding a library in select statement?
Posted: 6 years 22 days 6 hours 4 minutes ago
Edited: Wed, 14 Sep, 2011 at 10:27:42 (2200 days ago)

No Chris, I said that on the INTO clause, not everywhere you would want to use one.

 

sqlstmt = 'select fielda into flda where fieldb = ?'; 

 

That might work, but... wait, no I don't think you can code an INTO on a dynamic statement at all--have to get it out of that SQL area thingy--which I've done once in my life so I'm not expert on that.

 

sqlstmt = 'select fielda into ? where fieldb = xyz'; 

 This would not work either.