Inserting a record from a DS using a SQL Statement from a Variable

[belstsrv]

Hi Everyone,

I've got a program that is destined to be used as a service program for an IWS web service. Being a service, it will need to access data for multiple clients (client is one of the things passed to the program from IWS). Because of that, the SQL statements I use all have to be created in variables so they can concatenate the client library to the file that is going to be accessed.

For example:

Val = 'select * from ' +              
   %trim(client:' ') + '/MAILBOX ' +
   'where EDMID ='  + %trim(mailID) ;

This ends up looking like:
select * from CUSTLIB/MAILBOX where EDMID = 123456

I am using declare, prepare, open and fetch and these selects work just fine (error handling removed for brevity).

Exec SQL
    Declare MailEntries cursor For MailboxSQL;

Exec SQL
    Prepare MailboxSQL From :Val;

Exec SQL
    Open MailEntries;

Exec SQL
Fetch MailEntries For 1 Rows Into :mail100 ;

"mail100" you see there is a dimensioned data structure that will hold all the fields from that record.

Later on I end up manipulating a few of those fields and I want to use that data to insert a new record in to the database file.

Then move the entry of that to a non-dimensioned array called UPD100.

Here is where it's odd.

If I hard code the library name for testing and run it, the insert works perfectly.

Exec SQL insert into custlib/mailbox values :upd100;

But, if I try this

Val = 'insert into ' +               
   %trim(client:' ') + '/MAILBOX ' 
   'values ( :upd100 ) ' ;  
Exec SQL execute immediate :Val;

It fails saying that:
Variable UPD100 not defined or not usable.

Is there some other method or technique I should be using or perhaps I am missing something?

Thanks in advance.

Brian

[TedHolt]

I can't remember running an insert dynamically, so I could be wrong, but I think you're going to have to include the values, with quotes & commas as appropriate, in the Val variable.

[B.Hauser]

Insert can be done dynamically, but not in this way. You cannot include Hostvariables in this way.
You can work with parameter markers (qusstion marks), one for each variable.
You need to replace the parameter markers in the Open statement (first question mar = first variable ... and so on)
Exec SQL Open YourCursor Using :Var1, Ver2, .... varN
I'm not sure whether you can use a Datastruckture in the open statement.

Question: Why do you want to hard code the library
Wouldn't it be possible specify unqualified tables and use the libarary list
An other way to use static SQL with varying libraries, would be to use the good old OVRDBF command to override the file (make sure you use the override scope *JOB or *CALLLVL - Default is *ACTGRPDFN)

Birgitta

[belstsrv]

Thank you for the responses.

I'll try and reply to both in one post here.

I can't remember running an insert dynamically, so I could be wrong, but I think you're going to have to include the values, with quotes & commas as appropriate, in the Val variable.

Do you mean still creating the SQL statement in a variable but using all the fields from the data structure within that statement so I would be inserting field1, field2, field3, and so on? I could do that, it would just be a bit more code. I saw several examples of using a data structure to do a record insert and since I already had the structure handy, that seemed to be the best/easiest way to go about it, but I could try this method, if need be.

You can work with parameter markers (qusstion marks), one for each variable.
You need to replace the parameter markers in the Open statement (first question mar = first variable ... and so on)
Exec SQL Open YourCursor Using :Var1, Ver2, .... varN

I did try this method, but was not using a cursor and such. I tried to do this:

Val = 'insert into ' +
   %trim(client:' ') + '/MAILBOX '
   'values ( ? ) ' ;
Exec SQL execute immediate :Val using :upd100

But this would not compile. I can't recall the exact error message, but I assume it was because I was trying to use the data structure as the variable and it did not like that.

Question: Why do you want to hard code the library
Wouldn't it be possible specify unqualified tables and use the libarary list
An other way to use static SQL with varying libraries, would be to use the good old OVRDBF command to override the file (make sure you use the override scope *JOB or *CALLLVL - Default is *ACTGRPDFN)

I actually want to do the exact opposite of hard coding it. This program will serve as a service program for an IWS web service. That web service can be used to maintain the data for a bunch of customers. From one of the parameters that gets passed in from the web service, we can figure out the library that we need to be working on. So, in the program, I am always concatenating the library in to the SQL statement since it will be the same code running all the time, but working with data in multiple libraries.

Since the web service is more or less running all the time, I do not think and OVRDBF or LIBL solution is viable since the job or like a submitted/never ending job with a base/static LIBL.

To me it seems like there should be a method or syntax to be able to execute this update via a variable when I can do it via a direct SQL statement without any problems.

I could make a SELECT structure that has static SQL statements for each customer but that seems like a terrible design since the program would need update every time we added a customer

Hoping someone has done a "soft coded" update like this and can shed some light on how they did it.

Thanks again for the replies so far!

[belstsrv]

Wanted to get back with the group and let you know I was able to get it to work, although it seems like there should be a better way.

What I did was to change the SQL statement from this:

Val = 'insert into ' + 
%trim(client:' ') + '/MAILBOX '
'values ( :upd100 ) ' ;

To this:

Val = 'insert into ' +                          
      %trim(client:' ') + '/MAILBOX ' +          
      'values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
Exec SQL prepare mailPrep from :Val;
Exec SQL execute mailPrep using :upd100;

I have as many question marks as their are fields in the file/fields in the DS.

This ends up working well.

Thanks everyone!