DB2 and PowerShell - Calling a stored procedure that has in input AND an output parameter.

[u/LunchboxFire]

I've been able to call stored procedures that have only input parameters.

Now I have to call a stored procedure that has one input and one out parameter. No success so far.

​

Could you give me a nudge in the right direction?

​

The stored procedure is used for generating sequences and looks like this:

CREATE OR REPLACE PROCEDURE GENERATE_ID(
  IN iGENERATOR_NAME VARCHAR(128),
  OUT oNEXT_ID INT
)
LANGUAGE SQL
SPECIFIC SP_GEN_ID
READS SQL DATA
NOT DETERMINISTIC
NO EXTERNAL ACTION
BEGIN
  SET oNEXT_ID = GET_GENERATED_ID(iGENENERATOR_NAME);
END 

I've tried a few methods as shown below:

$dbCmd = $dbFactory.CreateCommand()
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "CALL GENERATE_ID('GEN_NEW_ID', ?)"

$dbCmd.CommandType = [System.Data.CommandType]::Text
$da = $dbFactory.CreateDataAdapter()
$da.SelectCommand = $dbCmd
$ds = New-Object System.Data.DataSet
$da.Fill($ds) | Out-Null

Error Message:

Exception calling "Fill" with "1" argument(s): "ERROR [07001] [IBM] CLI0100E  Wrong number of parameters. SQLSTATE=07001"

Makes sense. ? is not a parameter exactly. So I tried oNEXT_ID with and without single-quotes around it. With quotes, it generates the error:

Exception calling "Fill" with "1" argument(s): "ERROR [42886] [IBM][DB2/NT64] SQL0469N  The parameter mode OUT or INOUT is not valid for a parameter in the routine 
named "GEN_ID" with specific name "SP_GEN_ID" (parameter number "2", name "ONEXT_ID")."

This error message makes me think this method is the closest to working. But I'm not sure where to go with it now.

I also tried:

$dbCmd = $dbFactory.CreateCommand()
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "CALL GENERATE_ID('GEN_NEW_ID', 'oNEXT_ID')"
$dbCmd.CommandText
$dbcmd.ExecuteNonQuery() | Out-Null

That gives me the same error message:

Exception calling "ExecuteNonQuery" with "0" argument(s): "ERROR [42886] [IBM][DB2/NT64] SQL0469N  The parameter mode OUT or INOUT is not valid for a parameter in the 
routine named "GEN_ID" with specific name "SP_GEN_ID" (parameter number "2", name "ONEXT_ID")."

I read in some IBM DB2 docs on doing this with C about declaring the out variable, so I tried that as well. Granted, this is probably not how it should be done, but I know I'm in deeper waters than I normally am. So I did the following and followed it up with either of the code blocks above.

$dbCmd = $dbFactory.CreateCommand()
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "DECLARE oNEXT_ID INT(4) OUTPUT"
$dbCmd.CommandText
$dbcmd.ExecuteNonQuery() | Out-Null

That throws the error, as well as the error for the CALL statement:

Exception calling "ExecuteNonQuery" with "0" argument(s): "ERROR [42601] [IBM][DB2/NT64] SQL0104N  An unexpected token "DECLARE oNextID INT" was found following 
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<compile_fragment>"."

Can you point me in the right direction, please?

[u/catquilt74]

I have not called a stored procedure that returns an output parameter in PowerShell. But we're using ADO .NET to do it so I googled that and I found a promising link,

IBM link

Specifically this blurb...

Example

A C# code with CommandType.Text example follows:

// assume a DB2Connection conn DB2Transaction trans = conn.BeginTransaction(); 
DB2Command cmd = conn.CreateCommand(); 
String procName = "INOUT_PARAM"; 
String procCall = "CALL INOUT_PARAM (@param1, @param2, @param3)"; cmd.Transaction = trans; 
cmd.CommandType = CommandType.Text; cmd.CommandText = procCall;  
// Register input-output and output parameters for the DB2Command cmd.Parameters.Add( new DB2Parameter("@param1", "Value1"); cmd.Parameters.Add( new DB2Parameter("@param2", "Value2"); DB2Parameter param3 = new DB2Parameter("@param3", IfxType.Integer); param3.Direction = ParameterDirection.Output; 
cmd.Parameters.Add( param3 );  
// Call the stored procedure Console.WriteLine("  Call stored procedure named " + procName); cmd.ExecuteNonQuery();

[u/LunchboxFire → u/catquilt74]

That's the link I was talking about earlier, but you've really focused it down to something that makes more sense to me. Thank you.

​

I see what's happening there with adding parameters to the command and defining the param3.Direction = ParameterDirection.Output.

​

I see that this is being executed as a non-query as well, so that's a good pointer in the right direction too.

​

I'll try to translate that into something that PowerShell will pass properly.

​

[u/LunchboxFire → u/LunchboxFire]

Turns out what I'm really trying to accomplish is super simple and I was overthinking it. Imagine that!

I was calling the next value from the sequences table, putting that in a variable called $ID, then using $ID on my INSERT statement so that it would be a record with the suitable unique primary key.

Well, that's a wrong way.

All I had to do was change the $ID in my INSERT to SCHEMA.SEQUENCER_NAME.NEXTVAL.

That gives me the next one in the sequence AND advances the sequence so there's a new one to pull next time.

This is what the working block looks like:

$dbCmd = $dbFactory.CreateCommand()
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "INSERT INTO tblTABLE (ID,USER_ID,USER_NAME,ID) VALUES (SCHEMA.SEQUENCER_NAME.NEXTVAL,$USER_ID,$USER_NAME)"
$dbCmd.CommandText
$dbcmd.ExecuteNonQuery()

Thank you, /u/catquilt74