[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help returning DB2 stored procedure value

Posted on 2007-10-18
9
Medium Priority
?
508 Views
Last Modified: 2013-12-20
Hello,

Let me preface this question by saying I've never tried calling an AS400/DB2 program from Coldfusion. So please assume I know nothing and respond with as much 'hand holding' info as you see fit.

I've been tasked with inserting a record in a DB2 table on our AS400. Doing a basic insert is not a big deal. The problem is the insert requires a recID which is generated by calling a program. The program, when   called, should return the recID thus providing the one piece needed prior to inserting a record. I'm stuck on calling the program and retrieving the recID.

The code I'm trying is:

<CFSTOREDPROC PROCEDURE="myLIB.myPRG"
              DATASOURCE="myCONN"
              RETURNCODE="Yes">

<CFPROCPARAM dbvarname="myDBVar"
                   type="out"
                   value="0"
                   CFSQLTYPE="CF_SQL_CHAR"
                   MAXLENGTH="8">

<CFPROCRESULT maxrows="1"
                    NAME="myIDVAR"
                    RESULTSET="1">

</CFSTOREDPROC>

When executing the above I am met with the following error:
 Error Executing Database Query.
 [SQL0204] myPRG in myLIB type *N not found.

If I execute the same CFSTOREDPROC omitting the CFPROCPARAM the AS400 gives the following error:
.........................................
Cause . . . . . :   RPG procedure myPRG in program myLIB/myPRG at
  statement 98 had an error due to a pointer not being correctly set. The  
  cause of the error is most likely one of the following:                  
    -- A basing pointer was not set.                                        
    -- A procedure pointer was not set.                                    
    -- The pointer was set, but the object it referenced has been destroyed.
    -- A parameter was not passed to the program containing the procedure.  
    -- A parameter was not passed to the procedure by its caller within the
  program.                                                                  
...................................................

I just need the AS400 program to return the recID value to CF so I can perform the appropriate insert.

Resources I've referenced but am not completely understanding:
http://www.oreilly.com/catalog/coldfusion/chapter/ch11.html#94945
http://www.firstserv.com/support/coldfusion/documentation/coldfusion_5/Advanced_ColdFusion_Administration/datasources_ADV_MJS5.html
0
Comment
Question by:Activar
  • 6
  • 3
9 Comments
 

Author Comment

by:Activar
ID: 20100742
I failed to mention that we are using CF7.
0
 
LVL 3

Accepted Solution

by:
WaldenL earned 2000 total points
ID: 20100826
What is the RPG parameter definition (prototype or PList) look like (what fields and sizes)? You can't call an RPG program directly, you need to define a SQL stored proc wrapper around it using the CREATE PROCEDURE sql statement on the 400, has this been done? And what are it's parameter definition.

Also, while you say the RPG program "returns" the recID, I'll be it's not via a return code, but rather via an output parameter, so what are you expected to see in the RetrunCode of the proc, you told CF you expect one.

I think you've got the basic idea down fine, it's just that there's an impedence mis-match between the RPG parms, the stored proc parms as defined on the 400 and the parms you've defined in CF.

If you can post the parameter defintions (RPG Code is ok :) and the SQL parms then we'll have a better idea.

-Walden
0
 

Author Comment

by:Activar
ID: 20100993
Walden - Thanks for the quick reply!

The RPG field is 8 character Alpha. I shared your questions with the DB2 guys and they say .NET apps are calling RPG programs directly. I don't think the CREATE PROCEDURE statement has been run on the 400. I know the program is being called because of the messages I'm seeing on the 400.

You are right, the 400 does not return the recID via return code but rather a pointer/output parameter. I stuck the return code stuff in there just to see if I'd get anything back.

- Activar
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:Activar
ID: 20101035
Some additional CF Debug data

 Error Executing Database Query.
[SQL0204] myPRG in myLIB type *N not found.
 
58 : <CFPROCRESULT maxrows="1"
59 :                     NAME="myIDVAR"
60 :                     RESULTSET="1">
61 :
62 : </CFSTOREDPROC>

SQL         { (param 1) = call myLIB.myPRG( (param 2) )}
DATASOURCE         myCONN
VENDORERRORCODE         -204
SQLSTATE         42704
0
 
LVL 3

Expert Comment

by:WaldenL
ID: 20101227
As a regular .NET programmer I'd love to know how they have .Net calling RPG apps, but that's another topic. :)

Three thoughts, one, while .NET does require it somehow, CF does, therefore create proc must be called.

Two, the 400 is, at the right (or wrong :) ) level, case sensative. Try putting your library and program name in all upper case.

Three, does the user you're using in your connection have authority to that program? Try logging onto a green-screen as that user and see that you can get to the program (DSPPGM)

-Walden
0
 

Author Comment

by:Activar
ID: 20101706
You sir are 100% correct. It all depends on who you ask.
The .NET guys are running the CREATE PROCEDURE command on the 400. I have since had them run it on my little program and the results are better but I'm still not sure how to get the recid output. I'll be posting the CREATE PROCEDURE SQL in a moment. Maybe you can help me move this thing along?.
0
 

Author Comment

by:Activar
ID: 20101935
Create Procedure command:
..................................................................
CREATE PROCEDURE PCX . GETQIRNO (

      OUT "QIRNO" CHAR ( 8 ) )

      LANGUAGE CL

      SPECIFIC PCX . GETQIRNO

      NOT DETERMINISTIC

      MODIFIES  
SQL DATA

      CALLED ON NULL INPUT

      EXTERNAL NAME 'MYSYS/QLRGETQIR'

      PARAMETER STYLE GENERAL
.........................................................................

I'm now running the following in CF and getting the error
 Element QIRNO is undefined in CFSTOREDPROC.

<CFSTOREDPROC PROCEDURE="PCX.GETQIRNO"
              DATASOURCE="MYCONN">

<CFPROCPARAM TYPE="OUT"
                   cfsqltype="cf_sql_varchar"
                   variable="QIRNO"
                   >

<CFPROCRESULT MAXROWS="1"
              NAME="QIRNO"
              RESULTSET="1">

</CFSTOREDPROC>


Thanks again for the help Walden - it is greatly appreciated.
0
 
LVL 3

Expert Comment

by:WaldenL
ID: 20102251
IIRC, the name of the parm isn't important it's the position that counts. In either case, since there's only one.. :) However, the [variable="QIRNO"] tells cfprocparm where to put the result, not what the name of the parm is. You're thinking of dbvarname. This is from one of our pages:

<cfprocparam dbvarname="@StudyEvent_ID" cfsqltype="CF_SQL_INTEGER" type="Out" variable="StudyEvent_ID">

Do you have a QIRNO defined in your CF page? Also, I don't see a reason for the CFPROCRESULT since the proc isn't returning a result set. Set the variable to something in your CF code, and remove the CFPROCRESULT.

-Walden
0
 

Author Comment

by:Activar
ID: 20102491
Walden - Your advice was right on. I am able to get what I'm after with the following. I was over complicating things. Thanks again!

<CFSTOREDPROC PROCEDURE="PCX.GETQIRNO" DATASOURCE="MYCONN">

  <CFPROCPARAM TYPE="OUT"
    CFSQLTYPE="CF_SQL_VARCHAR"
    VARIABLE="myQIRNO" NULL="NO">

</CFSTOREDPROC>

<CFOUTPUT>Your QIR Number: #myQIRNO#</CFOUTPUT>
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
What You Need to Know when Searching for a Webhost Provider
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
Suggested Courses
Course of the Month20 days, 4 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question