Question on embedded SQL INSERT

[dacust]

Yes. Try using dynamic SQL. Build the SQL statement in a variable any way you like. Then use the PREPARE INTO statement. In this way you can dynamically build a statement with any number of variables, changing WHERE clauses, ORDER, etc. See example code below. -dan

---

Code

[EDIguru]

Question on embedded SQL INSERT

With the help of another programmers here that's what I've been working on since I posted the original message. I've got the variable loading into the sequel statement now, but it does not execute. SQLstt shows code 55019, "Table is in an invalid state". We aren't using a PREPARE INTO statement though, so I'm going to look over your code. Thanks.

[EDIguru]

Question on embedded SQL INSERT

Below is what I am trying to do, but still no success. Sqlstmt is defined as a 256 byte character field. Fypfil is a variable that holds the value of the overridden input file. When I run this in debug Sqlstmt correctly shows the entire statement, including the correct file name in place of fypfil. When I step through the rest of the SQL statements, SQLstt shows an error code of 55019-the table is in an invalid state. If I check open files both Rds120f and RDS102f(fypfil) show that they are open, but no input/output has occured. I can run this command interactively in SQL on the same two files and it executes and dumps the data from Rds102f to Rds120f.

---

Code

[dacust]

Question on embedded SQL INSERT

I just tested the code and it works for me. The override you are doing, is it a different physical file or a different member in the same file? I did an override to a different physical file and it worked. Maybe there is another problem with the table? See exact code I used below: -dan

---

Code

[EDIguru]

Question on embedded SQL INSERT

It's a physical file. See file specs below and some of the variable definitions. Rds101F is defined as an Extfile with a variable name and usropn. The record format for that file was then externally described as well. This all works in another RPG program I recently wrote that actually OUTPUTS the data to overridden variable files instead. This was the subject of another discussion under the RPG forum. Now I am trying to do some conditional reporting on that data and I just flipped this around to override it as an input file now as there are different data files to be used as input depending on the fiscal period. So the program determines the fiscal period and there is a sub-routine that overrides the file and opens it, which executes BEFORE the SQL statements. After that in debug the value shown in Sqlstmt does plug in the file name as it should. I am starting to wonder if I am having a problem with authority to tables in SQL possibly--although the Select statement I am trying to execute will run interactively with the file name hard-coded.

---

Code

[dacust]

Question on embedded SQL INSERT

Ahhh. You don't need to declare or open the file in the RPG. The SQL will do all that. All you need to do is issue the override. -dan

[EDIguru]

Question on embedded SQL INSERT

I have to have this file in the F Specs in order to do this override and it has to be usropn. See code below. By changing the value in fypfil, the file override occurs. So I tried closing the file again right before the SQL statement--somewhat redundant, but I wanted to see what would happen. The SQL statement still returns the error code 55019, and the file is not open. It appears that SQL statement did not execute at all since the file did not open.

---

Code

[EDIguru]

Question on embedded SQL INSERT

I also went back and tried taking the output file out of the F specs so that the RPG would not open that either. It did not work. We just tried putting these SQL statements in a generic program without the file override and that is not even working.

[EDIguru]

Question on embedded SQL INSERT

The problem was with journaling which I am not too familiar with--yet. Once the program was compiled with Commitment Control set to *none, the program worked as originally coded. Thanks for your input.

[dacust]

Question on embedded SQL INSERT

Shoot. I'm sorry. We have the default on our compile command for commit(*none) so I completely forgot about that. If you are not doing journaling it has to be compiled that way. -dan

[EDIguru]

Question on embedded SQL INSERT

No problem--we finally got it anyway and learned something! When you confirmed my code worked, we started looking for other problems. Did I understand right that we had to set this to commit(*none) because our system apparently isn't doing journaling?

[EDIguru]

Question on embedded SQL INSERT

Can anyone tell me if it is possible to use a variable file name in the "From file" parameter of the INSERT statement? Just started using SQL so bear with me if I'm not explaining this right. I have an RPG program that is using an input file that is overridden within the RPG using an EXTFILE(variable) based on what fiscal period happens to be processing. I would like to conditionally insert those records using the SQL Statement to another file.

[dacust]

Question on embedded SQL INSERT

Yes. Using commitment control, the records are written to the files as you update/write/delete them. If you issue a roll back instead of a commit, commitment control uses the journal to back_out/replace/reverse_update the records back to their original state. At least that's the theory as I understand it, not having done it myself. The default for RPGSQL assumes you are journaling your files. We don't journal at my shop, either, so several years ago we changed the default to the command. It's been so long since I got that error it didn't ring a bell. Glad it's working, now. -dan

[buck.calabro@commsoft.net]

Question on embedded SQL INSERT

> The default for RPGSQL assumes you are journaling your files. I'm late to the party but I use SET OPTION instead of tinkering with the command. c/exec sql c+ set option commit = *none c/end-exec I'd take a look at the SQL manual - there are several other good options you might want to look at. --buck