Need help with DB2 Stored Procedure - getting the results

Dear VAST users,

I am trying to invoke a seemingly simple Stored Procedure which returns a number and use that number in Smalltalk.

Here's what the StoredProc looks like:

CREATE OR REPLACE PROCEDURE overall_sum (
    IN ACC_ID INT,
    IN START DATE,
    IN END DATE,
    OUT OVERALL DECIMAL(13,2)
    )
    LANGUAGE SQL READS SQL DATA
   
   
P1:BEGIN
    DECLARE sum_1, sum_2 DECIMAL(13,2);
    SET sum_1 = (select sum(xyz) ...);
    SET sum_2 = (select sum(zyx) ...);

   
    SET OVERALL = sum_1 + sum_2   

END

I tested the two statements and they return correct values. But using the StoredProcedure I get wrong results. The number in the Dictionary is a completely wromng number...

I call it with this snippet:

|sess qSpec values result|
sess := code to retrieve a dbSession.
qSpec :=     (AbtStoredProcSpec new)
                procName: 'overall_sum'.
qSpec
                inputRow: (
                    (AbtCompoundType new)
                        addField: (
                            (AbtOdbcLongIntegerField new)
                                name: 'ACC_ID';
                                procBindType: AbtOdbcConstants::Sql_Param_Input);
                        addField: (
                            (AbtOdbcDateField new)
                                name: 'start';
                                procBindType: AbtOdbcConstants::Sql_Param_Input);
                            addField: (
                            (AbtOdbcDateField new)
                                name: 'end';
                                procBindType: AbtOdbcConstants::Sql_Param_Input); addField: (
                            (AbtOdbcNumericField new)
                                name: 'result';
                                scale: 2;
                                precision: 13;
                                procBindType: AbtOdbcConstants::Sql_Param_Output)).
               
               
               
values := Dictionary new.
values
    at: 'ACC_ID' put: 466171;
    at: 'start' put: (Date newDay:1 monthIndex: 1 year: 2017);
    at: 'ende' put: (Date today).

result := sess accessor connection invokeStoredProcSpec: qSpec withInputDictionary: values .
result inspect.

The result is a Dictionary with the 3 input values and the resulting number, but the number is by magnitudes off the correct result of the statements if issued directly using plain SQL.

I also tried specifying an output row for the AbtProcSpec which includes only the output parameter and with all four parameters. The result is exactly the same number (about 9 million instead of ca 106,000)

So does anybody have any hints what I am doing wrong? Has anybody done something like this before and can share a code snippet?


Thanks in advance

Joachim

A little addition:


if I do

SET OVERALL=9999

the result in the Dictionary on the smalltalk side is 9999. Strange....


Am Dienstag, 12. September 2017 13:33:54 UTC+2 schrieb Joachim Tuchel:

Dear VAST users,

I am trying to invoke a seemingly simple Stored Procedure which returns a number and use that number in Smalltalk.

Here's what the StoredProc looks like:

CREATE OR REPLACE PROCEDURE overall_sum (
    IN ACC_ID INT,
    IN START DATE,
    IN END DATE,
    OUT OVERALL DECIMAL(13,2)
    )
    LANGUAGE SQL READS SQL DATA
   
   
P1:BEGIN
    DECLARE sum_1, sum_2 DECIMAL(13,2);
    SET sum_1 = (select sum(xyz) ...);
    SET sum_2 = (select sum(zyx) ...);

   
    SET OVERALL = sum_1 + sum_2   

END

I tested the two statements and they return correct values. But using the StoredProcedure I get wrong results. The number in the Dictionary is a completely wromng number...

I call it with this snippet:

|sess qSpec values result|
sess := code to retrieve a dbSession.
qSpec :=     (AbtStoredProcSpec new)
                procName: 'overall_sum'.
qSpec
                inputRow: (
                    (AbtCompoundType new)
                        addField: (
                            (AbtOdbcLongIntegerField new)
                                name: 'ACC_ID';
                                procBindType: AbtOdbcConstants::Sql_Param_Input);
                        addField: (
                            (AbtOdbcDateField new)
                                name: 'start';
                                procBindType: AbtOdbcConstants::Sql_Param_Input);
                            addField: (
                            (AbtOdbcDateField new)
                                name: 'end';
                                procBindType: AbtOdbcConstants::Sql_Param_Input); addField: (
                            (AbtOdbcNumericField new)
                                name: 'result';
                                scale: 2;
                                precision: 13;
                                procBindType: AbtOdbcConstants::Sql_Param_Output)).
               
               
               
values := Dictionary new.
values
    at: 'ACC_ID' put: 466171;
    at: 'start' put: (Date newDay:1 monthIndex: 1 year: 2017);
    at: 'ende' put: (Date today).

result := sess accessor connection invokeStoredProcSpec: qSpec withInputDictionary: values .
result inspect.

The result is a Dictionary with the 3 input values and the resulting number, but the number is by magnitudes off the correct result of the statements if issued directly using plain SQL.

I also tried specifying an output row for the AbtProcSpec which includes only the output parameter and with all four parameters. The result is exactly the same number (about 9 million instead of ca 106,000)

So does anybody have any hints what I am doing wrong? Has anybody done something like this before and can share a code snippet?


Thanks in advance

Joachim

Never mind,

this is not related to VAST at all. I am debugging the stored procedure using Data Studio and the "magic number" I am getting on the Smalltalk side occurs there as well.

So I first need to learn how to properly use Stored Procedures and their input parameters - independently of Smalltalk...

Another day passes by - at least I am learning something new ;-)

Joachim




Am Dienstag, 12. September 2017 13:33:54 UTC+2 schrieb Joachim Tuchel:

Dear VAST users,

I am trying to invoke a seemingly simple Stored Procedure which returns a number and use that number in Smalltalk.

Here's what the StoredProc looks like:

CREATE OR REPLACE PROCEDURE overall_sum (
    IN ACC_ID INT,
    IN START DATE,
    IN END DATE,
    OUT OVERALL DECIMAL(13,2)
    )
    LANGUAGE SQL READS SQL DATA
   
   
P1:BEGIN
    DECLARE sum_1, sum_2 DECIMAL(13,2);
    SET sum_1 = (select sum(xyz) ...);
    SET sum_2 = (select sum(zyx) ...);

   
    SET OVERALL = sum_1 + sum_2   

END

I tested the two statements and they return correct values. But using the StoredProcedure I get wrong results. The number in the Dictionary is a completely wromng number...

I call it with this snippet:

|sess qSpec values result|
sess := code to retrieve a dbSession.
qSpec :=     (AbtStoredProcSpec new)
                procName: 'overall_sum'.
qSpec
                inputRow: (
                    (AbtCompoundType new)
                        addField: (
                            (AbtOdbcLongIntegerField new)
                                name: 'ACC_ID';
                                procBindType: AbtOdbcConstants::Sql_Param_Input);
                        addField: (
                            (AbtOdbcDateField new)
                                name: 'start';
                                procBindType: AbtOdbcConstants::Sql_Param_Input);
                            addField: (
                            (AbtOdbcDateField new)
                                name: 'end';
                                procBindType: AbtOdbcConstants::Sql_Param_Input); addField: (
                            (AbtOdbcNumericField new)
                                name: 'result';
                                scale: 2;
                                precision: 13;
                                procBindType: AbtOdbcConstants::Sql_Param_Output)).
               
               
               
values := Dictionary new.
values
    at: 'ACC_ID' put: 466171;
    at: 'start' put: (Date newDay:1 monthIndex: 1 year: 2017);
    at: 'ende' put: (Date today).

result := sess accessor connection invokeStoredProcSpec: qSpec withInputDictionary: values .
result inspect.

The result is a Dictionary with the 3 input values and the resulting number, but the number is by magnitudes off the correct result of the statements if issued directly using plain SQL.

I also tried specifying an output row for the AbtProcSpec which includes only the output parameter and with all four parameters. The result is exactly the same number (about 9 million instead of ca 106,000)

So does anybody have any hints what I am doing wrong? Has anybody done something like this before and can share a code snippet?


Thanks in advance

Joachim

Okay, I think I solved it. It was a problem related to the stupid programmer syndrome. StoredProcedures are CaSe SenSitive, so if you have in IN parameter

IN startdate DATE

you MUST use it in exactly like that in the SQL code within the stored procedure, not as startDate or StartDate or STARTDATE.

I feel stupid, but some people say red cheeks look good on me...

"I feel stupid, but some people say red cheeks look good on me..."

I have been building a software package for the last module of my Open University degree. It is a VAST backend with a Riak database. The front is an Android app running on a Lenovo 10" tablet. I have been using Cordova to do this - Cordova uses a mix of HTML and JavaScript to build an Android APK file. To build and test, I have had Cordova run Google Chrome. I have been logging in over the last few weeks and it has been working fine. I moved to the Lenovo tablet but nothing worked!

Seth,

Happy to help ;-)

Joachim

Am Dienstag, 12. September 2017 16:44:11 UTC+2 schrieb Seth Berman:

"I feel stupid, but some people say red cheeks look good on me..."

+1...I'm going to remember this line.  Shouldn't be too long from now until I get to use it:)

Just for completeness,

these strange numbers also occur when the result of a query in that proc is NULL (e.g. when you build a sum() on a list of zero elements) and you do some math with it. Not sure what these numbers are about, but they can trick you into losing lots of time looking for the wrong problems ;-)

I can really recommend IBM Data Studio for debugging such Problems in Stored Procedures. Far from a Smalltalk debugger, but it drills a few holes into the otherwise black box of what's going on behind that CALL MyProc()  curtain...

Joachim




Am Dienstag, 12. September 2017 14:58:52 UTC+2 schrieb Joachim Tuchel:

Okay, I think I solved it. It was a problem related to the stupid programmer syndrome. StoredProcedures are CaSe SenSitive, so if you have in IN parameter

IN startdate DATE

you MUST use it in exactly like that in the SQL code within the stored procedure, not as startDate or StartDate or STARTDATE.

I feel stupid, but some people say red cheeks look good on me...

David,

I hate days when I find what I was hunting for for hours or days are such stupid, freaking nonsense problems. They make me feel so small und stupid. And cost real lifetime and nerves - and money.

So I feel your pain.

Joachim