call DB2 stored procedure?
-
has anyone here used DB2Connect to call a DB2 stored procedure. I'm having trouble registering the input/output parameters. here is my code Dim conn As New DB2Connection("DATABASE=DB2DSST;UID=c132;PWD=*******;") conn.Open() Dim trans As DB2Transaction = conn.BeginTransaction() Dim cmd As DB2Command = conn.CreateCommand() Dim procName As String = "MG1006SP" Dim procCall As String = "CALL MG1006SP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" cmd.Transaction = trans cmd.CommandType = CommandType.Text cmd.CommandText = procCall ' Register input-output and output parameters for the DB2Command cmd.Parameters.Add("DB2_ID", "DSST") cmd.Parameters.Add("GRP_ID", "19 ") cmd.Parameters.Add("LN1_ADDR", DB2Type.Char, 55) cmd.Parameters.Add("LN2_ADDR", DB2Type.Char, 55) cmd.Parameters.Add("CITY_NM", DB2Type.Char, 30) cmd.Parameters.Add("ST_CD", DB2Type.Char, 2) cmd.Parameters.Add("ZIP5_CD", DB2Type.Char, 5) cmd.Parameters.Add("ZIP4_CD", DB2Type.Char, 4) cmd.Parameters.Add("INFO_CHG_DT", DB2Type.Date, 4) cmd.Parameters.Add("AIBRETRN", DB2Type.Integer, 4) cmd.Parameters.Add("AIBREASN", DB2Type.Integer, 4) cmd.Parameters.Add("SQLCODE", DB2Type.Integer, 4) cmd.Parameters.Add("SQLSTATE", DB2Type.Char, 5) cmd.Parameters.Add("SQLMESSAGE", DB2Type.VarChar, 1320) cmd.Parameters.Add("APPL_RTN_CD", DB2Type.Char, 5) cmd.Parameters.Add("APPL_MESSAGE", DB2Type.VarChar, 500) ' Call the stored procedure Console.WriteLine(" Call stored procedure named " & procName) cmd.ExecuteNonQuery()
-
has anyone here used DB2Connect to call a DB2 stored procedure. I'm having trouble registering the input/output parameters. here is my code Dim conn As New DB2Connection("DATABASE=DB2DSST;UID=c132;PWD=*******;") conn.Open() Dim trans As DB2Transaction = conn.BeginTransaction() Dim cmd As DB2Command = conn.CreateCommand() Dim procName As String = "MG1006SP" Dim procCall As String = "CALL MG1006SP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" cmd.Transaction = trans cmd.CommandType = CommandType.Text cmd.CommandText = procCall ' Register input-output and output parameters for the DB2Command cmd.Parameters.Add("DB2_ID", "DSST") cmd.Parameters.Add("GRP_ID", "19 ") cmd.Parameters.Add("LN1_ADDR", DB2Type.Char, 55) cmd.Parameters.Add("LN2_ADDR", DB2Type.Char, 55) cmd.Parameters.Add("CITY_NM", DB2Type.Char, 30) cmd.Parameters.Add("ST_CD", DB2Type.Char, 2) cmd.Parameters.Add("ZIP5_CD", DB2Type.Char, 5) cmd.Parameters.Add("ZIP4_CD", DB2Type.Char, 4) cmd.Parameters.Add("INFO_CHG_DT", DB2Type.Date, 4) cmd.Parameters.Add("AIBRETRN", DB2Type.Integer, 4) cmd.Parameters.Add("AIBREASN", DB2Type.Integer, 4) cmd.Parameters.Add("SQLCODE", DB2Type.Integer, 4) cmd.Parameters.Add("SQLSTATE", DB2Type.Char, 5) cmd.Parameters.Add("SQLMESSAGE", DB2Type.VarChar, 1320) cmd.Parameters.Add("APPL_RTN_CD", DB2Type.Char, 5) cmd.Parameters.Add("APPL_MESSAGE", DB2Type.VarChar, 500) ' Call the stored procedure Console.WriteLine(" Call stored procedure named " & procName) cmd.ExecuteNonQuery()
We use DataDirect Connect for .NET[^] to connect to DB2. We simply set the CommandText property to the name of the stored procedure and set CommandType to CommandType.StoredProcedure. For an example of using IBM's data provider - which appears to use the same scheme - see the SpClient.vb sample[^].
DoEvents: Generating unexpected recursion since 1991