| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| RESOLVED executing SQLServer stored proc from Broker 8.0.0.2 |
« View previous topic :: View next topic » |
| Author |
Message
|
| smuktineni |
 Posted: Fri May 17, 2013 10:13 am Post subject: RESOLVED executing SQLServer stored proc from Broker 8.0.0.2 |
 |
|
Apprentice
Joined: 28 Aug 2003
Posts: 33
Location: Omaha
|
Trying to call a SQL server stored procedure from ESQL and it reaches the SQL server but doesn't execute it!
Platform Windows 7 64 bit with SP1
Broker V8.0.0.2 on Windows 64 bit
SQL Server 11.0.3321
SQL Driver for ODBC: SQL Server Native Client 11.0
| Code: |
C:\Program Files\IBM\MQSI\8.0.0.2>[b]mqsicvp[/b] MB8BRKR -n <ODBCdataSource>
BIP8270I: Connected to Datasource 'ODBCdataSource' as user 'xxxxxxxx'. The datasource platform is 'Microsoft SQL Server', version '11.00.3321'.
===========================
databaseProviderVersion = 11.00.3321
driverVersion = 11.00.2100
driverOdbcVersion = 03.80
driverManagerVersion = 03.80.7601.0000
driverManagerOdbcVersion = 03.80.0000
databaseProviderName = Microsoft SQL Server
datasourceServerName = xxxxxxxx
databaseName = MQAudit
odbcDatasourceName = ODBCdataSource
driverName = sqlncli11.dll
|
The default database has been set on DSN.
ESQL code
| Code: |
CREATE PROCEDURE AUDIT_SAVE (
IN i_AUDIT_ID INTEGER NULLABLE, --bigint
IN i_SOURCE_APPLICATION CHARACTER, --varchar](10)
IN i_CREATED_BY CHARACTER, --varchar](50)
IN i_VERSION INTEGER, --int
IN i_UPDATED_BY CHARACTER NULLABLE, --varchar](50)
IN i_AUDIT_STATUS_ID INTEGER, --tinyint
IN i_COMPLETION_DATE TIMESTAMP NULLABLE, --datetime
OUT o_AUDIT_ID INTEGER ) --bigint
LANGUAGE DATABASE
EXTERNAL NAME "dbo.AUDIT.AUDIT_SAVE";
|
| Code: |
| CALL mqsi.AUDIT_SAVE(null, 'appId', 'user', 1, null, 1, null, Environment.Var.AuditId) IN DataSource.ODBCdataSource; |
I also have DECLARE CONTINUE HANDLER FOR SQLSTATE LIKE '%' and the SQLCODE is 0.
Looking at the SQL Server trace the proc call makes it to the server. SQL server executes a bunch of system statements to identify the stored procedure, proc signature etc and then it stops, proc is not executed. I can post the SQL trace later if that helps.
FYI...code below works (executes the proc), but I can't get the output!
| Code: |
SET sql_AuditInsert = 'execute AUDIT_SAVE @i_SOURCE_APPLICATION = ?, @i_CREATED_BY = ?, @i_VERSION = 1, @i_UPDATED_BY = null, @i_AUDIT_STATUS_ID = 1, @i_COMPLETION_DATE = NULL, @o_AUDIT_ID = null';
SET Environment.Var.AuditId = PASSTHRU(sql_AuditInsert, 'appId', 'user', 1, null, null, 1, null); |
Thanks in advance
Last edited by smuktineni on Thu May 23, 2013 5:47 am; edited 1 time in total |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Fri May 17, 2013 10:19 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
I'm not sure your SQLServer version is fully supported by Message Broker. Can't make heads or tails of the SPCR tool, but WMB V7 only supported
Microsoft SQL Server 2005 (plus Service Pack 1)
Microsoft SQL Server 2008
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| smuktineni |
| Posted: Fri May 17, 2013 10:57 am Post subject: |
|
|
Apprentice
Joined: 28 Aug 2003
Posts: 33
Location: Omaha
|
|
| Back to top |
|
|
| dogorsy |
| Posted: Fri May 17, 2013 10:21 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
Can you be more explicit when you say 'it does not work'?
do you mean the call is executed and get the wrong value?, you get an error ?
a user trace will show you the input parameter values for the call and the resulting output values. so, run with user trace and prove us that the call does not work.
Please include the SQLserver stored procedure.
Also, comment out the HANDLER, that way if there is an error it will show straight away. |
|
| Back to top |
|
|
| smuktineni |
| Posted: Wed May 22, 2013 1:22 pm Post subject: |
|
|
Apprentice
Joined: 28 Aug 2003
Posts: 33
Location: Omaha
|
The call is not executed.
I updated the SP call to below as i_COMPLETION_DATE is not NULLABLE and also updated the CREATE PROCEDURE to reflect the same.
| Code: |
| CALL usp_AUDIT_SAVE(null, 'SMART', 'Tester', 1, 'Tester', 1, CAST( CURRENT_TIMESTAMP AS TIMESTAMP FORMAT 'yyyy-MM-dd-HH.mm.ss.SSS'), audiId); |
Commenting the handler showed the details! Now I have somewhat similar problem as below link, except that I am using v11 of the driver and SQL Server 2012.
http://www.mqseries.net/phpBB2/viewtopic.php?p=350800&sid=5952f7389831a3736d5fbf39a1876e63
SQL SP signature
| Code: |
CREATE PROCEDURE [dbo].usp_AUDIT_SAVE
@i_AUDIT_ID [bigint] = NULL,
@i_SOURCE_APPLICATION [varchar](10) ,
@i_CREATED_BY [varchar](50),
@i_VERSION [int],
@i_UPDATED_BY [varchar](50),
@i_AUDIT_STATUS_ID [tinyint],
@i_COMPLETION_DATE [datetime],
@o_AUDIT_ID [bigint] OUTPUT
AS |
Trace
| Code: |
2013-05-22 15:41:57.047636 6064 RecoverableException BIP2488E: ('mqsi.audit_RecordToDB.Main', '47.5') Error detected whilst executing the SQL statement ''usp_AUDIT_SAVE(NULL, 'SMART', 'Tester', 1, 'Tester', 1, CAST(CURRENT_TIMESTAMP AS TIMESTAMP FORMAT 'yyyy-MM-dd-HH.mm.ss.SSS'), audiId);''.
2013-05-22 15:41:57.047672 6064 RecoverableException BIP2921E: The procedure ''MQAUDIT.dbo.usp_AUDIT_SAVE'' with '8' parameters could not be matched with a corresponding Database stored procedure.
Stored Procedures must match the ESQL CREATE PROCEDURE definition in the following ways: 1: The ESQL parameters must all be of SCALAR type. 2: The directions of each ESQL and Database parameter (IN / OUT / INOUT) must match. 3: The number of parameters in both the ESQL and database definitions must match.
Ensure these conditions are met and redeploy the message flow.
2013-05-22 15:41:59.201248 6064 UserTrace BIP2540I: Node 'mqsi.SMARTRequestMain.Trace': Finished evaluating expression ''CURRENT_TIMESTAMP'' at ('', '1.56'). The result was ''TIMESTAMP '2013-05-22 15:41:59.201101'''
|
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 22, 2013 2:10 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| smuktineni wrote: |
| Code: |
| CAST( CURRENT_TIMESTAMP AS TIMESTAMP FORMAT 'yyyy-MM-dd-HH.mm.ss.SSS') |
|
this doesn't do what you think it does. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed May 22, 2013 9:04 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
You also need to post your ESQL CREATE PROCEDURE, in that, you need to ensure that the parameters match the ones in the SQLServer procedure.
The cast of a CURRENT_TIMESTAMP AS TIMESTAMP is unnecessary, it is already a timestamp.
the clue is in this bit of the user trace:
Stored Procedures must match the ESQL CREATE PROCEDURE definition in the following ways: 1: The ESQL parameters must all be of SCALAR type. 2: The directions of each ESQL and Database parameter (IN / OUT / INOUT) must match. 3: The number of parameters in both the ESQL and database definitions must match.
and as you have not included all of your stored procedure, it is difficult to say what is wrong, but if the number of parameters is wrong, it probably is because you need to include the
SET NOCOUNT ON in your SQLServer stored procedure. That should go straight after the signature:
CREATE PROC <parameters>
AS
SET NOCOUNT ON |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed May 22, 2013 10:46 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
Beware:
your SQLServer signature has:
@o_AUDIT_ID [bigint] OUTPUT
by default, in sqlserver all parameters are input, so OUTPUT means INPUT AND OUTPUT, so in your ESQL signature the matching parameter MUST be of type INOUT |
|
| Back to top |
|
|
| smuktineni |
| Posted: Thu May 23, 2013 5:46 am Post subject: Resolved |
|
|
Apprentice
Joined: 28 Aug 2003
Posts: 33
Location: Omaha
|
ESQL signature was the problem. Once I updated to INOUT it worked like a charm.  Tx very much dogorsy. I wasn't aware of SQL Server behavior as I am mostly with DB2 and Oracle.
On the timestamp casting, I was trying to send a 3 digit precision for milliseconds instead of 6. This was based on sample data from the SQL table. Part of trial and error  |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu May 23, 2013 5:49 am Post subject: Re: Resolved |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| smuktineni wrote: |
| On the timestamp casting, I was trying to send a 3 digit precision for milliseconds instead of 6. This was based on sample data from the SQL table. Part of trial and error |
A timestamp doesn't have any precision.
A character representation of a timestamp does.
CASTING TO or FROM a character representation of a timestamp uses a FORMAT. Casting a TIMESTAMP to a TIMESTAMP doesn't use a FORMAT, and doesn't alter the data in any way. |
|
| 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
|
|
|
|
