| Author |
Message
|
| bharu |
 Posted: Mon Jul 16, 2012 1:39 am Post subject: Error accessing stored procedure |
 |
|
Newbie
Joined: 16 Jul 2012
Posts: 9
|
Hi All,
while accessing the stored procedure from WMB.I faced some issue,Need to pass IN1 and IN2 parameter. Initially IN1 was character and IN2 was Date datatype.I need to pass IN2 as the date format 'MM/dd/yyyy'. Esql doesn't support this format so IN2 parameter datatype is changed to varchar(10).Try by casting the format as character and passed it to the IN2 parameter of stored procedure call as char.It error out with the below exception "[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification".
Below is the Sample:
CALL sampleprocedure(In1,In2,Environment.Variables.ResultSet1[]);
[In1 and In2 are declared as CHAR].
CREATE PROCEDURE sampleprocedure(IN InParm1 CHARACTER, IN InParm2 CHARACTER)
LANGUAGE DATABASE DYNAMIC RESULT SETS 1
EXTERNAL NAME "sample procedure";  [/b] |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Jul 16, 2012 3:44 am Post subject: Re: Error accessing stored procedure |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| bharu wrote: |
| I need to pass IN2 as the date format 'MM/dd/yyyy'. |
This is not a date format. This is a character representation of a date.
The mapping between ESQL and database types is clearly documented.
You should not, if you have a DATE object in your database, have any requirement to format it in a given way. The ODBC driver should just handle this.
If the mapping between data types does not give you the correct data type for your table definition, then you should do an appropriate cast inside the stored procedure, not in Broker. |
|
| Back to top |
|
|
| bharu |
| Posted: Mon Jul 16, 2012 5:18 am Post subject: |
|
|
Newbie
Joined: 16 Jul 2012
Posts: 9
|
Thank you so much for your valuable response.
Now i am able to Hit the stored procedure.The stored procedure need to return N number of records.I am using the DYNAMIC RESULT SET to retrieve all the records.It doesn't return any value.
Below is the Sample:
CALL sampleprocedure(In1,In2,Environment.Variables.ResultSet1[]);
CREATE PROCEDURE sampleprocedure(IN InParm1 CHARACTER, IN InParm2 CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "sampleprocedure"; |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Jul 16, 2012 5:23 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Are you sure that the data you are expecting is indeed in the first dynamic result set? |
|
| Back to top |
|
|
| bharu |
| Posted: Mon Jul 16, 2012 5:43 am Post subject: |
|
|
Newbie
Joined: 16 Jul 2012
Posts: 9
|
I am using
CALL sampleprocedure(In1,In2,Environment.Variables.ResultSet[]); to completely reterive all the records.Its hits the store procedure but doesn't return any value. Where this need to be fixed in the Esql are in the stored procedure. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Jul 16, 2012 7:57 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| bharu wrote: |
I am using
CALL sampleprocedure(In1,In2,Environment.Variables.ResultSet[]); to completely reterive all the records.Its hits the store procedure but doesn't return any value. Where this need to be fixed in the Esql are in the stored procedure. |
I have just recently seen a case where a stored procedure returned MANY dynamic result sets, based on the number of selects actually used.
The data that was being looked for was not in the FIRST dynamic result set. So the definition of the procedure had to be altered to retrieve MORE dynamic result sets.
Nothing you have said has eliminated this as a match to your scenario.
I ask again.
Are you sure that the data you are expecting is indeed in the first dynamic result set? |
|
| Back to top |
|
|
| bharu |
| Posted: Mon Jul 16, 2012 9:20 pm Post subject: |
|
|
Newbie
Joined: 16 Jul 2012
Posts: 9
|
Thank you Mqjeff
I am new to this stored procedure concept.Will you please help me what should be defined in the stored procedure to execute and return the multiple records.How can i check the dynamic result set position. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Jul 17, 2012 1:31 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| bharu wrote: |
Thank you Mqjeff
I am new to this stored procedure concept.Will you please help me what should be defined in the stored procedure to execute and return the multiple records.How can i check the dynamic result set position. |
Run the stored procedure - with the same input - from a database client. |
|
| Back to top |
|
|
| bharu |
| Posted: Wed Jul 25, 2012 4:24 am Post subject: |
|
|
Newbie
Joined: 16 Jul 2012
Posts: 9
|
| The issue was solved .It was the problem with stored procedure NOCOUNT only. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Jul 25, 2012 7:48 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| bharu wrote: |
| It was the problem with stored procedure NOCOUNT only. |
Can you please elaborate? |
|
| Back to top |
|
|
|
|
