Problem with Stored Procedure

[K.Forsythe]

If I understood you, the VB code calls the CL, and the CL calls the RPG, which returns the Result set. Yes?

[schmitty]

Problem with Stored Procedure

Yes, that is correct.

[K.Forsythe]

Problem with Stored Procedure

code?

[schmitty]

Problem with Stored Procedure

Cl code is below.

---

Code

[K.Forsythe]

Problem with Stored Procedure

I don't see why any of that would create a problem. But you're working into a pretty rare area. I would suggest taking the CLRPFM out of the CL and replace it with an embedded SQL statement in the RPGLE program. You can use "DELETE FROM PRTMPCSTPF" as a replacement for the CLRPFM command. Let me know how that works! Kevin

[schmitty]

Problem with Stored Procedure

Yes, I actually changed the RPG program to do that and it works fine. I was just wondering if there was something that I was missing. I have done the exact same thing with the clrpfm command in another program and it works just fine. Thanks for the help.

[K.Forsythe]

Problem with Stored Procedure

I've never seen that before.

[R.Daugherty]

Problem with Stored Procedure

My guess is that the stored procedure sees the file as exclusively locked by the CL program from when CLRPFM was performed, but of course the lock is released. I would retract that guess if stored procedures do something different when unable to access a locked file than return empty results. I would do an ALCOBJ *EXCL - CLRPFM - DLCOBJ *EXCL just to see if the stored procedure will read from it then. If it does, it would be good to know. rd

[deangriffiths]

Problem with Stored Procedure

Um...an area I know little about. What is the VB code to call the CL from Excel - and how do you add it? Would be cool!

[schmitty]

Problem with Stored Procedure

Got this figured out. It was a problem with my library list and two different copies of the file that I was working against. Thought that I had it set correctly by adding the library names to the library list in my CL program. Fixed by qualifying the file name in the RPG program with the correct library. Works fine now. Below is the code that I use in the Excel spreadsheet to call the stored procedure. I use a button on the spreadsheet to call the VB module which clears previous data and brings in the new.

---

Code

[schmitty]

Problem with Stored Procedure

I have created a stored procedure that calls a CL/RPGLE program to return a result set. I call the stored procedure from an Excel spreadsheet. The RPG program builds a temporary file, and then and embedded SQL procedure returns all of the records as the result set. The problem that I am encountering is in the CL program. I use clrpfm prior to calling the rpg program to clear the temporary file before repopulating it. When I do this, no records are returned to the Excel spreadsheet. If I comment out the clrpfm command, then records are returned. The temp file contains records after the call from Excel. Has anyone experienced the same behavior?

[David Abramowitz]

Problem with Stored Procedure

It's formally called VBA, and may be entered by the process of creating a macro (under the "tools" menu). With a little practice, you can create connection strings to your AS/400, and bring in data directly to the spreadsheet. Dave