DB2 V8 NFM Cobol SP called from VB.net sql

Roy Asamoto

DB2 V8 NFM Cobol SP called from VB.net sql
Hi,

We are trying to use VB.net to call a mainframe COBOL stored procedure
that will return a result set to the client. The developer wants to be
able to just call the SP and have it return the result set. We set up a
simple SP that has a cursor that it opens and then returns to the
calling application.

EXEC SQL
DECLARE C1 CURSOR WITH RETURN FOR
SELECT SUBSYSTEM
,SEQ_NO
,START_RUN_TS
,END_RUN_TS
FROM SUBSYS_RUN_TS
WHERE SUBSYSTEM = :IN-SUBSYSTEM
FOR FETCH ONLY WITH UR
END-EXEC.

We tested this with Crystal reports 11 (going through DB2 connect) and
it works fine. When the developer tested it with his vb.net it failed.


System.Runtime.InteropServices.COMException (0x80040E14): The
conversation was deallocated with AP_ABEND. SQLSTATE: 08S01, SQLCODE:
-576

at ADODB.RecordsetClass.Open(Object Source, Object ActiveConnection,
CursorTypeEnum CursorType, LockTypeEnum LockType, Int32 Options)

at DB2.testDB2.Getdb2RecordSet() in
c:\inetpub\wwwroot\DB2\testDB2.aspx.vb:line 178

The SP is called and exits correctly (we display the SQLCODE before
leaving it), but the calling application returns an sqlstate 08S01
SQLCODE = -576. The SQLSTATE seems to be related to a connection
failure, I haven't been able to find the -576.

The developer found the following on the Microsoft site:

Does the new ODBC Driver or new OLE DB Provider for DB2 support
returning DB2 stored procedure result sets?
No. However, we intend to support this functionality in the next major
release of Host Integration Server.

Questions:

1. Has any one been able to do this with Microsoft's Host integration
Server 2000 SP 2?
2. Has anyone been able to do this with Microsoft's Host Integration
Server 2004?
3. Can the vb.net application be easily adjusted to point to the DB2
connect driver?

On the development side, the preference would be to use the Host
integration servers OLE DB Provider for DB2, but if that won't work and
it's easy to point them to the DB2 connect driver, I'm all for that
instead.

Thanks,
Roy
* This message was scanned by the corporate mail server for viruses and objectionable content.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm