Re: Calling Stored Procedures from WonderWare InTouch - SOLVED

In case anyone is reading this down the road, we finally got it working.

The key, apparently, is that SQLSetParamChar does not work in this context. Instead, we just filled SQLSetStatement with one big string, e.g.:
SQLSetStatement(MyConnectionID, "CALL MYLIB.MYPGM('ABCDE',12345");
and that works fine. You don't need to pad character parameters, and passing numbers seems to work just fine. So that's the answer :-)


Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> writes:

On 7/16/2010 4:51 PM, Michael Naughton wrote:

I'm working with a colleague on this. He has some applications that do SQL calls to our iSeries (basically, SELECT statements), and we'd like to turn them into stored procedures, but we're having trouble getting it to work. Everything we've tried so

far

results in either a message on the WonderWare side saying that the procedure can't be found, or the program running on the iSeries but not returning anything because the parameters have something wrong with them (they look okay, but they are being used
as keys and the chains don't find anything; if we hard-code the same values in the program, everything works fine).

From reading the documentation, we think the right things to do are:

use SQLSetStatement to create the statement containing the stored procedure
use SQLPrepareStatement to get a statement handle
use successive SQLSetParamChar's to add the parameters (they're all character parms right now: we're taking baby steps)
use SQLExecute to run the stored procedure

but when we try that we get the error that the stored procedure can't be found. If we change things so the SQLSetStatement includes the parameters in parentheses (and we eliminate the SQLSetParamChar's), then the procedure runs, but it doesn't find
anything.

Is there anyone who has done something like this who might have an example of what the code looks like? We would greatly appreciate it ....

PS: I don't think the stored procedure is a problem -- I've tested it in iSeries Navigator, and it seems to work fine ...

Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that any use is
strictly prohibited. If you have received this e-mail in error, please notify us immediately by replying to it and then delete it from your computer.