| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| How to call SQL SERVER stored procedure using ESQL |
« View previous topic :: View next topic » |
| Author |
Message
|
| vdharanikota |
 Posted: Tue Feb 17, 2015 2:07 am Post subject: How to call SQL SERVER stored procedure using ESQL |
 |
|
Novice
Joined: 07 Jul 2014
Posts: 18
|
Hi all
My requirement is... I will get dynamic http responses which contains 11 columns. Using ESQL I am able to store the data in the SQL table using Insert statement.
Now I have to do the same using stored procedure.
USE [EXRATES]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_exchange_rates_insert]
@base_currency varchar(50) = NULL ,
@currency VARCHAR(50) = NULL ,
@response_date datetime = NULL ,
@ask float = NULL ,
@bid float = NULL ,
@downloaded_date datetime = NULL ,
@high_ask float = NULL ,
@high_bid float = NULL ,
@low_ask float = NULL ,
@low_bid float = NULL ,
@midpoint float = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.EXCHANGERATES
(
base_currency ,
currency ,
response_date ,
ask ,
bid ,
downloaded_date ,
high_ask ,
high_bid ,
low_ask ,
low_bid ,
midpoint )
VALUES
(
@base_currency ,
@currency ,
@response_date ,
@ask ,
@bid ,
@downloaded_date ,
@high_ask ,
@high_bid ,
@low_ask ,
@low_bid ,
@midpoint
)
END
I have created a stored procedure in sql as above...
I was tried to call this stored procedure as below..
CALL sp_exchange_rates_insert(base_currency,currency,response_date,ask,bid,downloaded_date,high_ask,high_bid,low_ask,low_bid,midpoint) IN Database.ODAEXCRATES.dbo.ODAAUDITLOG;
can any one suggest how call this stored procedure
 |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Tue Feb 17, 2015 5:40 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20697
Location: LI,NY
|
Did you create the procedure type database with matching signature in ESQL? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| vdharanikota |
| Posted: Tue Feb 17, 2015 10:21 pm Post subject: |
|
|
Novice
Joined: 07 Jul 2014
Posts: 18
|
I have created a procedure like this.. correct me If I am wrong
CREATE PROCEDURE sp_exchange_rates_insert(IN base_currency CHARACTER,IN currency CHARACTER,IN response_date TIMESTAMP,IN ask DECIMAL,IN bid DECIMAL, IN downloaded_date TIMESTAMP,IN high_ask DECIMAL,IN high_bid DECIMAL,IN low_ask DECIMAL,IN low_bid DECIMAL,IN midpoint DECIMAL) LANGUAGE DATABASE EXTERNAL NAME "dbo.sp_exchange_rate_insert"; |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Feb 18, 2015 2:30 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| vdharanikota wrote: |
I have created a procedure like this.. correct me If I am wrong
CREATE PROCEDURE sp_exchange_rates_insert(IN base_currency CHARACTER,IN currency CHARACTER,IN response_date TIMESTAMP,IN ask DECIMAL,IN bid DECIMAL, IN downloaded_date TIMESTAMP,IN high_ask DECIMAL,IN high_bid DECIMAL,IN low_ask DECIMAL,IN low_bid DECIMAL,IN midpoint DECIMAL) LANGUAGE DATABASE EXTERNAL NAME "dbo.sp_exchange_rate_insert"; |
doesn't this look a lot better?
| Code: |
CREATE PROCEDURE sp_exchange_rates_insert(
IN base_currency CHARACTER,
IN currency CHARACTER,
IN response_date TIMESTAMP,
IN ask DECIMAL,
IN bid DECIMAL,
IN downloaded_date TIMESTAMP,
IN high_ask DECIMAL,
IN high_bid DECIMAL,
IN low_ask DECIMAL,
IN low_bid DECIMAL,
IN midpoint DECIMAL)
LANGUAGE DATABASE EXTERNAL NAME "dbo.sp_exchange_rate_insert";
|
IMHO, this is much easier to read and understand.
From my experience passing TIMESTAMPs is always an issue. 
Therefore, I generally pass them as CHAR strings and convert them to the right format in the Stored proc. Then I can be sure that executing the SP from SQLStudio will work the same as calling it from Broker.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| vdharanikota |
| Posted: Thu Feb 19, 2015 3:37 am Post subject: |
|
|
Novice
Joined: 07 Jul 2014
Posts: 18
|
Thank you for your response... now its working good after changing datatypes in esql, tables and in stored procedure....  |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
