Tweet
Bill, If you are returning a limited amount of data, you can load the parameters and if they are defined as input/output the value will return to the caller. If you are trying to return a result set, you need to return the cursor. I believe that this works better when the SP is being called from a Client application rather than embedded SQL in RPG, but this may have been resolved in the latest releases. You can find an example of this in my book (shameless plug) SQL for eServer i5 and iSeries.
-
-
Stored Procedures and Embedded SQL
I've got your book, and I still don't see how to return the data to my program. We had some "consultants" come in and they evaluated a mission critical app. Among some of the things they required was a mapping of our data. When they found out we were writing new applications using embedded SQL they told us we should be using SP's. The application is green screen and all RPG with a smattering of CL. I don't know enough about SQL (yet) to argue with them. What I am understanding from your reply is that stored procedures in a green screen application are not a viable option. Since we don't give command line to users nor the ability to use STRSQL, then SP's are a waste of my time. Is this accurate? Bill -
Stored Procedures and Embedded SQL
The only way to call a procedure and return a result set from it, is to use the CLI APIs (not in my book) or to make the call from a client app that uses an ODBC/JDBC type connection (Which in turn uses the CLI APIs) I have never been able to find a way to return a result set to an RPG Program. If someone knows how to accomplish that without using the CLI API's I would love to hear about it! The whole idea of returning result sets is focussed on client/server apps, so reading them into an RPG program doesn't seem to be a big concern for IBM. There are other work arounds that might be useful in certain situations. If you have a particular situation to look at, we could talk about alternative ways to use calls without returning result sets. Sorry that was probably not the answer you wanted to hear. Good luck and stay in touch if you have more questions. KevinComment
-
Stored Procedures and Embedded SQL
> I have never been able to find a way to return a result set to an RPG Program. Have the RPG program call a Java class that traverses the result set and puts it into an array. I think that's way more trouble than it's worth. On the other hand, the consultants' recommendations need to be weighed against the clear indications that they are unfamiliar with iSeries and RPG. Meaning I wouldn't be concerned about trying to use stored procedures for green screen programs. --buckComment
-
Stored Procedures and Embedded SQL
Thank you, both of you. I kind of figured as much while they were here, based on some things that were said, but couldn't really argue about SQL. Kevin; Your book has been a great help. One suggestion would be to have more "real life" examples. I have never been one to start small and simple with anything. If I am going to use a tool I am going to use it as completely as possible. Again, thank you Kevin and Buck. BillComment
-
Stored Procedures and Embedded SQL
I finally figured out how to create a SP, but I have not been able to figure out how to use it. I created a SP that retrieves data from 3 different tables and it uses 2 parms, a store number and a date. How do I get the data from the SP to my program???? I know this probably sounds elementary to most of you, but we are learning SQL by T&E. We have 3 books that address SP's one of them with examples. The problem is that the examples all pertain to updating a table. I would like to use the SP in a program and am wondering how to return the data in interactive SQL? I do the call to the procedure with the parameters and after a few seconds I get screen control back, but no data to look at. TIA Bill BarnesComment
-
Stored Procedures and Embedded SQL
I am glad the book has been of use. Thanks for the suggestion about the real life examples. KevinComment
Comment