Need help with DB2 Stored Procedure

I need help with calling a Stored Procedure in DB2 10.5 LUW

I have created a stored procedure (LANGUAGE SQL) that does an insert on a table. It has 3 input parameters and doesn't return anything. The procedure runs fine if I call it in IBM Data Studio, it inserts exactly the row I expect.

Calling the procedure from VA Smalltalk using the following code doesn't work:

| connection querySpec |

connection := AbtDbmSystem activeDatabaseConnection.
 
querySpec :=
   AbtQuerySpec new
      statement: 'CALL BSATZ_HIST(:bsatz_id, :grund, :BEARBEITER_LOESCHUNG)';
      hostVarsShape:
         (AbtCompoundType new
            addField:  ( AbtOdbcIntegerField new name: 'BSATZ_ID'; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcFixedCharField new name: 'GRUND'; count: 1; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcVarCharField new name: 'BEARBEITER_LOESCHUNG'; count: 50; procBindType: AbtOdbcConstants::Sql_Param_Input) );
      yourself.

dict := Dictionary new.
dict at: 'BSATZ_ID' put: 8962; at: 'GRUND' put: 'B'; at: 'BEARBEITER_LOESCHUNG' put: 'test'. 
connection executeQuerySpec: querySpec withValues: dict.

The answer is simply true (the object), but the row is not inserted in the database. No SQL error or anything, just no inserted row.

I also tried defining the querySpec with question marks instead of named parameters

querySpec := AbtQuerySpec new statement: '{CALL BSATZ_HIST(?,?,?)}'.

The result is the same.

So what am I doing wrong? Any hint is welcome

Joachim

... and no, this is not a case sensitivity problem. I also tried writing all parameter names in my code in capital letters...


Am Donnerstag, 19. Mai 2016 19:11:11 UTC+2 schrieb Joachim Tuchel:

I need help with calling a Stored Procedure in DB2 10.5 LUW

I have created a stored procedure (LANGUAGE SQL) that does an insert on a table. It has 3 input parameters and doesn't return anything. The procedure runs fine if I call it in IBM Data Studio, it inserts exactly the row I expect.

Calling the procedure from VA Smalltalk using the following code doesn't work:

| connection querySpec |

connection := AbtDbmSystem activeDatabaseConnection.
 
querySpec :=
   AbtQuerySpec new
      statement: 'CALL BSATZ_HIST(:bsatz_id, :grund, :BEARBEITER_LOESCHUNG)';
      hostVarsShape:
         (AbtCompoundType new
            addField:  ( AbtOdbcIntegerField new name: 'BSATZ_ID'; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcFixedCharField new name: 'GRUND'; count: 1; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcVarCharField new name: 'BEARBEITER_LOESCHUNG'; count: 50; procBindType: AbtOdbcConstants::Sql_Param_Input) );
      yourself.

dict := Dictionary new.
dict at: 'BSATZ_ID' put: 8962; at: 'GRUND' put: 'B'; at: 'BEARBEITER_LOESCHUNG' put: 'test'. 
connection executeQuerySpec: querySpec withValues: dict.

The answer is simply true (the object), but the row is not inserted in the database. No SQL error or anything, just no inserted row.

I also tried defining the querySpec with question marks instead of named parameters

querySpec := AbtQuerySpec new statement: '{CALL BSATZ_HIST(?,?,?)}'.

The result is the same.

So what am I doing wrong? Any hint is welcome

Joachim

On Thursday, May 19, 2016 at 1:11:11 PM UTC-4, Joachim Tuchel wrote:

I need help with calling a Stored Procedure in DB2 10.5 LUW

I have created a stored procedure (LANGUAGE SQL) that does an insert on a table. It has 3 input parameters and doesn't return anything. The procedure runs fine if I call it in IBM Data Studio, it inserts exactly the row I expect.

Calling the procedure from VA Smalltalk using the following code doesn't work:

| connection querySpec |

connection := AbtDbmSystem activeDatabaseConnection.
 
querySpec :=
   AbtQuerySpec new
      statement: 'CALL BSATZ_HIST(:bsatz_id, :grund, :BEARBEITER_LOESCHUNG)';
      hostVarsShape:
         (AbtCompoundType new
            addField:  ( AbtOdbcIntegerField new name: 'BSATZ_ID'; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcFixedCharField new name: 'GRUND'; count: 1; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcVarCharField new name: 'BEARBEITER_LOESCHUNG'; count: 50; procBindType: AbtOdbcConstants::Sql_Param_Input) );
      yourself.

dict := Dictionary new.
dict at: 'BSATZ_ID' put: 8962; at: 'GRUND' put: 'B'; at: 'BEARBEITER_LOESCHUNG' put: 'test'. 
connection executeQuerySpec: querySpec withValues: dict.

The answer is simply true (the object), but the row is not inserted in the database. No SQL error or anything, just no inserted row.

I also tried defining the querySpec with question marks instead of named parameters

querySpec := AbtQuerySpec new statement: '{CALL BSATZ_HIST(?,?,?)}'.

The result is the same.

So what am I doing wrong? Any hint is welcome

Joachim

On Thursday, May 19, 2016 at 10:11:11 AM UTC-7, Joachim Tuchel wrote:

I need help with calling a Stored Procedure in DB2 10.5 LUW

I have created a stored procedure (LANGUAGE SQL) that does an insert on a table. It has 3 input parameters and doesn't return anything. The procedure runs fine if I call it in IBM Data Studio, it inserts exactly the row I expect.

Calling the procedure from VA Smalltalk using the following code doesn't work:

| connection querySpec |

connection := AbtDbmSystem activeDatabaseConnection.
 
querySpec :=
   AbtQuerySpec new
      statement: 'CALL BSATZ_HIST(:bsatz_id, :grund, :BEARBEITER_LOESCHUNG)';
      hostVarsShape:
         (AbtCompoundType new
            addField:  ( AbtOdbcIntegerField new name: 'BSATZ_ID'; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcFixedCharField new name: 'GRUND'; count: 1; procBindType: AbtOdbcConstants::Sql_Param_Input);

             addField: (AbtOdbcVarCharField new name: 'BEARBEITER_LOESCHUNG'; count: 50; procBindType: AbtOdbcConstants::Sql_Param_Input) );
      yourself.

dict := Dictionary new.
dict at: 'BSATZ_ID' put: 8962; at: 'GRUND' put: 'B'; at: 'BEARBEITER_LOESCHUNG' put: 'test'. 
connection executeQuerySpec: querySpec withValues: dict.

The answer is simply true (the object), but the row is not inserted in the database. No SQL error or anything, just no inserted row.

I also tried defining the querySpec with question marks instead of named parameters

querySpec := AbtQuerySpec new statement: '{CALL BSATZ_HIST(?,?,?)}'.

The result is the same.

So what am I doing wrong? Any hint is welcome

Joachim

May not help, but it is the first thing that jumped into my mind since you were not getting an error.

Steven,

sometimes it is hard to answer posts like yours. Because you immediately know that guy really fought himself whether he should really write such obvious stuff into a public forum. I am glad you did.

So this was surely the most obvious thing to check first. But I decided to go backwards ;-)

The short story is: the COMMIT was the missing piece. The rows are now inserted.

Here are a few things I've learned now in this exercise:

• The stored procedure cannot be added if you use the statemen COMMIT TRANSACTION; it has to be COMMIT;
• It is not sufficient to commit the transaction in Smalltalk code, it has to be in the stored procedure itself. So this did not work:
  connection executeQuerySpec: querySpec withValues: dict; commitUnitOfWork.
  I guess that is something you just know if you know all about Stored Procedures. I find it remarkable anyways, because if I use IBM Data Studio to invoke the Stred Procedure, it gets committed automatically, even without the COMMIT. To me this sounds illogical, because obviously Data Studio commits the transaction...

Maybe these things are of help to anybody.

Thanks a lot, Steven!

Joachim

Am Donnerstag, 19. Mai 2016 21:42:52 UTC+2 schrieb Steven LaFavor:

May not help, but it is the first thing that jumped into my mind since you were not getting an error.

Do you need to do a COMMIT TRANSACTION on the database?

Richard,


yes, there was a bug with CHAR(1) fields for DB2. I had reported it to Instantiations (I had troubles using CHAR(1) as a subclass discriminator in Glorp) and it was fixed somwehere around 8.5/8.6

In fact I tried passing both a Character and a String into the SP, and after Steven's suggestion solved my problem I could test that both options work the same.

Thanks for answering. I am glad I got some ideas. This forum is highly underestimated ;-)

Joachim



Am Donnerstag, 19. Mai 2016 21:19:39 UTC+2 schrieb Richard Sargent:

On Thursday, May 19, 2016 at 10:11:11 AM UTC-7, Joachim Tuchel wrote:

I need help with calling a Stored Procedure in DB2 10.5 LUW

I have created a stored procedure (LANGUAGE SQL) that does an insert on a table. It has 3 input parameters and doesn't return anything. The procedure runs fine if I call it in IBM Data Studio, it inserts exactly the row I expect.

Calling the procedure from VA Smalltalk using the following code doesn't work:

| connection querySpec |

connection := AbtDbmSystem activeDatabaseConnection.
 
querySpec :=
   AbtQuerySpec new
      statement: 'CALL BSATZ_HIST(:bsatz_id, :grund, :BEARBEITER_LOESCHUNG)';
      hostVarsShape:
         (AbtCompoundType new
            addField:  ( AbtOdbcIntegerField new name: 'BSATZ_ID'; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcFixedCharField new name: 'GRUND'; count: 1; procBindType: AbtOdbcConstants::Sql_Param_Input);

Joachim, I have a very vague memory of something like this where the character field of size 1 requires the use of a Character.
I have no idea whether it was in the context of SQL or something else.

             addField: (AbtOdbcVarCharField new name: 'BEARBEITER_LOESCHUNG'; count: 50; procBindType: AbtOdbcConstants::Sql_Param_Input) );
      yourself.

dict := Dictionary new.
dict at: 'BSATZ_ID' put: 8962; at: 'GRUND' put: 'B'; at: 'BEARBEITER_LOESCHUNG' put: 'test'. 
connection executeQuerySpec: querySpec withValues: dict.

The answer is simply true (the object), but the row is not inserted in the database. No SQL error or anything, just no inserted row.

I also tried defining the querySpec with question marks instead of named parameters

querySpec := AbtQuerySpec new statement: '{CALL BSATZ_HIST(?,?,?)}'.

The result is the same.

So what am I doing wrong? Any hint is welcome

Joachim

Lou,

this is much shorter, because you leave out the whole Field definitions thingie. I like the shortness of this. I guess there are some limititations when it comes to Dates, Times and especially Timestamps ond other types that need special formatting...?

Thanks for sharing the snippet.

Joachim



Am Donnerstag, 19. Mai 2016 20:00:47 UTC+2 schrieb Louis LaBrunda:

Hi Joachim,

I have only done this with MS/SQL and ODBC (not my choice but that's life).  The following works.

| connection querySpec panelString statement resultSet result errorText |

connection := AbtDbmSystem activeDatabaseConnection.

querySpec := AbtQuerySpec new.

panelString := ' (''%1'', ''%2'', ''%3'')' bindWith: panel with: aDate printString with: clientId.

statement := '{CALL %1%2}' bindWith: countsSpName with: panelString.

querySpec statement: statement.

resultSet := connection resultSetsFromQuerySpec: querySpec.

Hope it helps.

Lou

On Thursday, May 19, 2016 at 1:11:11 PM UTC-4, Joachim Tuchel wrote:

I need help with calling a Stored Procedure in DB2 10.5 LUW

I have created a stored procedure (LANGUAGE SQL) that does an insert on a table. It has 3 input parameters and doesn't return anything. The procedure runs fine if I call it in IBM Data Studio, it inserts exactly the row I expect.

Calling the procedure from VA Smalltalk using the following code doesn't work:

| connection querySpec |

connection := AbtDbmSystem activeDatabaseConnection.
 
querySpec :=
   AbtQuerySpec new
      statement: 'CALL BSATZ_HIST(:bsatz_id, :grund, :BEARBEITER_LOESCHUNG)';
      hostVarsShape:
         (AbtCompoundType new
            addField:  ( AbtOdbcIntegerField new name: 'BSATZ_ID'; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcFixedCharField new name: 'GRUND'; count: 1; procBindType: AbtOdbcConstants::Sql_Param_Input);
             addField: (AbtOdbcVarCharField new name: 'BEARBEITER_LOESCHUNG'; count: 50; procBindType: AbtOdbcConstants::Sql_Param_Input) );
      yourself.

dict := Dictionary new.
dict at: 'BSATZ_ID' put: 8962; at: 'GRUND' put: 'B'; at: 'BEARBEITER_LOESCHUNG' put: 'test'. 
connection executeQuerySpec: querySpec withValues: dict.

The answer is simply true (the object), but the row is not inserted in the database. No SQL error or anything, just no inserted row.

I also tried defining the querySpec with question marks instead of named parameters

querySpec := AbtQuerySpec new statement: '{CALL BSATZ_HIST(?,?,?)}'.

The result is the same.

So what am I doing wrong? Any hint is welcome

Joachim