ODBC connection with stored procedure on iSeries as data source

I have created a stored procedure on the iSeries that creates a cursor and opens it. I am trying to write my report to use the stored procedure. I cannot get the data source to work. How do I create my data source so that it uses the stored procedure? My SP has three parameters I am trying to pass from the report. The parms are created in the layout.

Thank you

Figured it out. Had to use this syntax: Call procname ('parm1', parm2, 'parm3')

|||

I was looking for an answer to my stored procedure problem and I came across your question/answer...I was hoping you'd help me out...we are using ODBC and an iseries and since I'm very much of a newbie to VB and .net, I am using point and click when I can...can you tell me what I'm doing wrong? I have an asp.net master and content pages...the content pages have datagrids (Gridview1) and a SQLDataSource control...Any help is greatly appreciated!

Imports IBM.Data.DB2.iSeries

Partial Class SSNName

Inherits System.Web.UI.Page

Dim ssnpsn As String = "123456789"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim conn As New iDB2Connection("DataSource=datasourcename")

Try

conn.Open()

Catch ex As Exception

End Try

Dim sqlSSN As String = "call libraryname.SP_QSSNNAME(@.ssn)"

Dim cmdSSN As New iDB2Command(sqlSSN, conn)

cmdSSN.Parameters.Add("@.ssn", iDB2DbType.iDB2Numeric, 9)

cmdSSN.Parameters("@.ssn").Value = ssnpsn

Dim dr As iDB2DataReader

dr = cmdSSN.ExecuteReader()

Gridview1.DataSource = dr

Gridview1.DataBind()

conn.Close()

End Sub

End Class

|||

I have been using the iSeries for some time now and I find this approach the best.

1. Create a new class under App_code

2. Create a function with the follwing code:

Shared Function GetCatByCatID(ByVal CatID As Integer) As System.Data.DataSet

Dim connectionString As String = ConfigurationManager.ConnectionStrings("gsafleetcs").ConnectionString

Dim cnn As IBM.Data.DB2.iSeries.iDB2Connection = New IBM.Data.DB2.iSeries.iDB2Connection(connectionString)

Dim proc As String = "GetCatInfo"

Dim dbCmd As IBM.Data.DB2.iSeries.iDB2Command = New IBM.Data.DB2.iSeries.iDB2Command

dbCmd.CommandText = proc

dbCmd.CommandType = Data.CommandType.StoredProcedure

dbCmd.Connection = cnn

Dim dbParam_ID As System.Data.IDataParameter = New IBM.Data.DB2.iSeries.iDB2Parameter

dbParam_ID.ParameterName = "@.CatID"

dbParam_ID.Value = CatID

dbParam_ID.DbType = Data.DbType.Int32

dbParam_ID.Direction = Data.ParameterDirection.Input

dbCmd.Parameters.Add(dbParam_ID)

Dim dataAdapter As IBM.Data.DB2.iSeries.iDB2DataAdapter = New IBM.Data.DB2.iSeries.iDB2DataAdapter

Dim dataSet As System.Data.DataSet = New System.Data.DataSet

dataAdapter.SelectCommand = dbCmd

dataAdapter.Fill(dataSet)

dbCmd.Dispose()

cnn.Close()

Return dataSet

End Function

3. Then in your asp page where the grid view create a Object datasource, unclick the Show only data components to expose you class name, then click next and choose you approiate method and finally define your parameter(s).

These Rebooks helped a lot:

1. IBM Redbook Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries

2. Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET

Good Luck

|||

Are you using the Report Designer?

In my Dataset definition I've defined my Name, Data Source, Command Type as 'Stored Procedure' and in the Query String I put the name of the stored procedure. When I execute it responds with

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Object reference not set to an instance of an object.

ADDITIONAL INFORMATION:

Object reference not set to an instance of an object. (System.Data)

BUTTONS:

OK

I know it finds the store procedure, becuase at first I had a typo and the error message stated it could not find it. When I fix the typo I recieved the above error message. Here is the stored procedure:

BEGIN
DECLARE C1 CURSOR FOR
SELECT PEMS . EMS1548 . EMSDCO , PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT , PEMS . FUELDET . FUDTYP , PEMS . FUELDET . FUDFQ AS GALS , PEMS . FUELDET . FUDFD AS DOLS , PEMS . FUELDET . FUDDAT
FROM PEMS . EMS1548 , PEMS . EMS1547 , PEMS . FUELDET
WHERE PEMS . EMS1548 . EMSDCO = PEMS . EMS1547 . EMSDPT AND PEMS . EMS1547 . EMSIDC = PEMS . FUELDET . FUDIDX AND
( PEMS . FUELDET . FUDTYP IN ( 'UNL' , 'DSL' ) ) AND ( PEMS . FUELDET . FUDEQT IN ( '022406' , '016460' , '026352' , '017068' , '023207' , '021870' , '019334' , '022754' , '020608' , '019337' ) )
ORDER BY PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT ;
OPEN C1 ;
END

Any ideas would be appreciated.

|||

I found the solution:

I was missing "RESULT SET 1" before the LANGUAGE SQL and "SET RESULT SETS CURSOR C1;" after the OPEN C1; statement