Re: Stored procedure error

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.