Re: Stored procedure error

Steve,

It's telling you what the problem is...Column WLQMTO not in specified tables

You think it is, thus it's a library list issue.

do a
wrkf *ALL/F4801 ( or wrkf *ALL/F3112 whichever should have the
field) and see if there is a version of the file somplace without the
field.

Another thought, you could have the RPG program do a DSPLIBL
OUTPUT(*PRINT) so that you can see that the library list in effect is
what you think it is.

Lastly, make sure you check SQLSTATE or SQLCODE after the embedded SQL code.

Good luck,
Charles



On Fri, Apr 9, 2010 at 12:16 PM, sjl <sjl_abc@xxxxxxxxxxx> wrote:

A coworker is having problems with a stored procedure which uses an RPGIV
program.

In the job log for the job which is servicing the requests from the VB.Net
program, the following error messages are appearing (my notes in
parentheses):

User XXXXXXXXX from client xxx.xx.xxx.xxx connected to server.
Library xxxxxxxxxx added to library list. (data library)
Library yyyyyyyyy added to library list. (object library)
Column WLQMTO not in specified tables.  (it is!)
Cursor SOQ not open.

See his email message below...

Best Regards,
Steve


One of our engineers has written software in VB.Net 2008 to support the YYY
Project.

This software requires information from the IBM I so I wrote a stored
procedure that will return the data that he needs.  It worked properly back
in September when validation runs on 2 different applications using red
specials where successfully processed in the production environment.

Something has happened between September and now that has caused this issue.

I created the stored procedure using the following command:

       Create Procedure QGPL/Proc_AutoMill_Lens_Data
         (In Parm1 Char(8))
         Result Set 1
         Language RPGLE
         Not Deterministic
         Reads SQL Data
         External Name ATM001R
         Parameter Style General

The actual SQL that is executed is embedded in an RPG program (ATM001R) and
the following is the statement executed:

       Select Substr(WALITM,1,6) As Style,
              ConCat(Trim(WAWR03),
                Substr(WALITM,8,3))
                As Power,
              WAUORG As Started,
              WAUORG - WLQMTO As Rejects
         From F4801
                Join
              F3112
                On WADOCO = WLDOCO
       Where WALOTN = :LotNumber
          And WLQMTO <> 0

As you can see, it is just a simple Select statement that returns 4 columns.
We are using the .Net Data Provider that comes with iSeries Access for
Windows.  Here is an example of the VB code that executes the procedure:

       ' Set command text for calling the procedure
     cmdAS400.CommandText = "Call QGPL.Proc_Automill_Lens_Data(@LotNumber)"
     ' Set procedure parameters
     cmdAS400.DeriveParameters()
     cmdAS400.Parameters(0).Value = strLotNumber
     ' Call the procedure
     drAS400 = cmdAS400.ExecuteReader
     ' Loop through records
     Do While drAS400.Read
        ' Store result set fields
             txtStyle.Text = encoding.GetString(drAS400(0))
             txtPower.Text = encoding.GetString(drAS400(1))
             txtStartQuantity.Text = drAS400(2).ToString
        txtRejectQuantity.Text = drAS400(3).ToString
     Loop

When executed, Proc_Automill_Lens_Data returns no data in the Production
environment.

However it will return data in any other environment and we have thoroughly
tested that.  I have compared all of the objects involved in all of the
environments and they are identical to the very best of our knowledge.

Security granted our service user ID account (XXXXXXXXX) full authority
(just temporarily) to test that this wasn't simply a permissions issue, but
the extra permissions made no difference.

Additionally I used my own credentials and it still returned no data so we
have ruled out permissions as a possibility.  For an entire week we have
tried everything we can think of and still we see no reason why this doesn't
work in Production.  There must be some kind of constraint in Production
that doesn't exist anywhere else.




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.