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.

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

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.

Got it! Thanks, Dale!

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!

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.

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

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?

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

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

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

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.

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.

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.