Need help with DB2 Stored Procedure - getting the results

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Need help with DB2 Stored Procedure - getting the results

jtuchel
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


--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/va-smalltalk.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with DB2 Stored Procedure - getting the results

jtuchel
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


--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/va-smalltalk.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with DB2 Stored Procedure - getting the results

jtuchel
In reply to this post by jtuchel
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


--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/va-smalltalk.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with DB2 Stored Procedure - getting the results

jtuchel
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...

--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/va-smalltalk.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with DB2 Stored Procedure - getting the results

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:)

On Tuesday, September 12, 2017 at 8:58:52 AM UTC-4, Joachim Tuchel wrote:
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...

--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/va-smalltalk.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with DB2 Stored Procedure - getting the results

Long Haired David
In reply to this post by jtuchel

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!

basic test username was "demo". What does a tablet do? It starts you off with an Upper case character "Demo" - no login!

A complex system front to back, brought down by a simple failure!
David
Totally Objects

P.S. To John O'Keefe and others (must be some others) who are into Model Railroads, it runs a train schedule and routes freight cars across the railroad!

On Tuesday, September 12, 2017 at 1:58:52 PM UTC+1, Joachim Tuchel wrote:
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...

--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/va-smalltalk.
For more options, visit https://groups.google.com/d/optout.
David
Totally Objects
Doing Smalltalk since 1989
Reply | Threaded
Open this post in threaded view
|

Re: Need help with DB2 Stored Procedure - getting the results

jtuchel
In reply to this post by Seth Berman
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:)

--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/va-smalltalk.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with DB2 Stored Procedure - getting the results

jtuchel
In reply to this post by jtuchel
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...

--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/va-smalltalk.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need help with DB2 Stored Procedure - getting the results

jtuchel
In reply to this post by Long Haired David
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

--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/va-smalltalk.
For more options, visit https://groups.google.com/d/optout.