| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Stored Procedure Error in WMB 8.0 |
« View previous topic :: View next topic » |
| Author |
Message
|
| vishBroker |
 Posted: Mon May 07, 2012 9:20 am Post subject: Stored Procedure Error in WMB 8.0 |
 |
|
Centurion
Joined: 08 Dec 2010
Posts: 135
|
Hi,
I am getting error while calling stored procedure for SQL Server as well as Oracle from WMB v8.0
Configuration Details -
SQL DB - (from mqsicvp command)
SQL Server ver 8.0 sp4
-----
===========================
databaseProviderVersion = 08.00.2282
driverVersion = 06.00.0150 (b0102, U0089)
driverOdbcVersion = 03.52
driverManagerVersion = 03.52.0002.0002
driverManagerOdbcVersion = 03.52
databaseProviderName = Microsoft SQL Server
datasourceServerName = XXXXX
databaseName = BrokerData
odbcDatasourceName = SCADADB1
driverName = UKmsss24.so
supportsStoredProcedures = Yes
...
============================
------
Oracle DB - (from mqsicvp command)
---------
===========================
databaseProviderVersion = 11.02.0000 Oracle 11.2.0.1.0
driverVersion = 06.00.0254 (B0179, U0089)
driverOdbcVersion = 03.52
driverManagerVersion = 03.52.0002.0002
driverManagerOdbcVersion = 03.52
databaseProviderName = Oracle
datasourceServerName = x.x.x.x
databaseName = N/A
odbcDatasourceName = GMSDEV
driverName = UKora24.so
supportsStoredProcedures = Yes
......
===========================
---------
Broker - Version 8
OS for Broker - Red Hat Enterprise Linux Server release 6.2 (Santiago)
I have migrated code from broker v6.1 to v8.0. The stored procedures which are working for broker v6.1 are NOT working in broker v8.0.
The error that I get for stored procedures - SQL Server is - =========================
Text:CHARACTER:The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure. Insert
Type:INTEGER:5
Text:CHARACTER:SCADADB1.dbo.cm0usp
Insert
Type:INTEGER:2
Text:CHARACTER:1238
===================================
The error that I get for stored procedures - Oracle is ======================================
....
Number:INTEGER:2321
Text:CHARACTER:Root SQL exception
Insert
Type:INTEGER:2
Text:CHARACTER:-1
Insert
Type:INTEGER:14
Text:CHARACTER:/opt/ibm/IE02/2.0.0/lib/libodbcinterface.so
DatabaseException
File:CHARACTER:/build/S000_P/src/DataFlowEngine/ImbOdbc.cpp
Line:INTEGER:3316
Function:CHARACTER:ImbOdbcConnection::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2322
Text:CHARACTER:Child SQL exception
Insert
Type:INTEGER:5
Text:CHARACTER:HY000
Insert
Type:INTEGER:2
Text:CHARACTER:0
Insert
Type:INTEGER:5
Text:CHARACTER:[unixODBC][IBM][ODBC Oracle Wire Protocol driver]Error in SQL Parser.
DatabaseException
File:CHARACTER:/build/S000_P/src/DataFlowEngine/ImbOdbc.cpp
Line:INTEGER:3316
Function:CHARACTER:ImbOdbcConnection::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2322
Text:CHARACTER:Child SQL exception
Insert
Type:INTEGER:5
Text:CHARACTER:HY000
Insert
Type:INTEGER:2
Text:CHARACTER:0
Insert
Type:INTEGER:5
Text:CHARACTER:[unixODBC][IBM][ODBC Oracle Wire Protocol driver]General error.Procedure Name Too Long=========================================================
I have checked that - stored procedure name, signature and parameters DO match.
(And these stored procedure are working fine in version 6.0 in production env)
Kindly suggest/guide.
Regards,
Vishnu. |
|
| Back to top |
|
 |
| vishBroker |
| Posted: Mon May 07, 2012 9:27 am Post subject: |
|
|
Centurion
Joined: 08 Dec 2010
Posts: 135
|
The stored procedure that I am calling have following dataTypes
1. Char
2. Int
3. Float
4. Date
5. TimeStamp
FYI - I created sample stored procedure with Char,Int,Float and Date DataType and it still fails. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Mon May 07, 2012 9:27 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| What is the Datatype of parameters in SP and what is the code that invokes it from ESQL? Please post the code in code tags. |
|
| Back to top |
|
|
| vishBroker |
| Posted: Mon May 07, 2012 9:33 am Post subject: |
|
|
Centurion
Joined: 08 Dec 2010
Posts: 135
|
1.Following is code for Oracle Procedure
| Code: |
CALL INSERT_HOURLY(
Meter_Station,
Meter,
dateGMSTIME,
--changed GMSTIME cast for testing
int_cmCurrentRecordIndex,
subhour,
InputRoot.MRM.cmHourlyFlowDuration,
Round(InputRoot.MRM.cmHourlyVolume,3 MODE ROUND_HALF_UP),
Round(InputRoot.MRM.cmHourlyTemperature,3 MODE ROUND_HALF_UP),
Round(InputRoot.MRM.cmHourlyEnergy,3 MODE ROUND_HALF_UP),
Round(InputRoot.MRM.cmHourlyStaticPressure,3 MODE ROUND_HALF_UP),
Round(InputRoot.MRM.cmHourlyDiffPressure,3 MODE ROUND_HALF_UP),
-- HourlyFlowExtension calculation for Brad Massey 02/23/2011 - JSH
-- InputRoot.MRM.cmHourlyFlowExtension * 62.42796 / 1000.0,
InputRoot.MRM.cmHourlyFlowExtension ,
0,
dateGMTIME ,
offset,
6);
|
This is defination of stored procedure in Broker
| Code: |
CREATE PROCEDURE INSERT_HOURLY (
in METER_STATION integer,
in METER integer,
in PROD_DATE_TIME DATE,
in SEQUENCE_NUM integer,
in SUBHOUR integer,
in FLOWTIME FLOAT,
in MCF_1473 FLOAT,
in TEMPERATURE FLOAT,
in ENERGY FLOAT,
in PSIG_PRES FLOAT,
in DP FLOAT,
in SQRT_PRES_EXT FLOAT,
in PB_STATUS integer,
in GMT_TIME DATE,
in GMT_OFFSET INTEGER,
in DATA_SOURCE INTEGER)
LANGUAGE DATABASE
EXTERNAL NAME "VAS.INSERT_HOURLY";
|
2. Following is stored procedure for SQL server
| Code: |
CALL cm0usp(
fromhost,
InputRoot.MQRFH2.psc.Topic,
report,
CURRENT_TIMESTAMP,
meternum,
InputRoot.MRM.cmHourlyDate,
InputRoot.MRM.cmHourlyTime,
GMSTIME,
InputRoot.MRM.cmHourlyFlowDuration,
InputRoot.MRM.cmHourlyVolume,
InputRoot.MRM.cmHourlyEnergy,
-- HourlyFlowExtension calculation for Brad Massey 02/23/2011 - JSH
-- InputRoot.MRM.cmHourlyFlowExtension * 62.42796 / 1000.0,
InputRoot.MRM.cmHourlyFlowExtension,
InputRoot.MRM.cmHourlyTemperature,
InputRoot.MRM.cmHourlyStaticPressure,
InputRoot.MRM.cmHourlyDiffPressure,
InputRoot.MRM.cmHourlyVolume1000,
InputRoot.MRM.cmHourlyMass,
InputRoot.MRM.cmHourlyRelativeDensity,
InputRoot.MRM.cmCurrentRecordIndex,
InputRoot.MRM.cmMeterName) IN Database.{DB}.dbo;
|
The definition of this stored procedure in broker
| Code: |
CREATE PROCEDURE cm0usp (
in fromhost character,
in pubsubject character,
in report character,
in arrived timestamp,
in meterNum character,
in hourlydate FLOAT,
in hourlytime FLOAT,
in gmstimestamp timestamp,
in hourlyflowduration FLOAT,
in hourlyvolume FLOAT,
in hourlyenergy FLOAT,
in hourlyflowextension FLOAT,
in hourlytemperature FLOAT,
in hourlystaticpressure FLOAT,
in hourlydiffpressure FLOAT,
in hourlyvolume1000 FLOAT,
in hourlymass FLOAT,
in hourlyrelativedensity FLOAT,
in currentrecordindex FLOAT,
in meternumv character)
LANGUAGE DATABASE
EXTERNAL NAME "dbo.cm0usp";
|
Kindly let me know, if you need more details. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Mon May 07, 2012 9:36 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
How have you declared and defined for the variables Meter_Station,
Meter in esql? |
|
| Back to top |
|
|
| mgk |
| Posted: Mon May 07, 2012 9:39 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Clearly this should work as you are migrating from 6.x to 8 so therefore, please raise a PMR to get this resolved as quickly as possible.
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| vishBroker |
| Posted: Mon May 07, 2012 9:55 am Post subject: |
|
|
Centurion
Joined: 08 Dec 2010
Posts: 135
|
@KASH
====
DECLARE Meter_Station INTEGER;
===
@MGK
==
Thanks. I have opened PMR with IBM.
== |
|
| Back to top |
|
|
| vishBroker |
| Posted: Fri May 18, 2012 7:14 am Post subject: |
|
|
Centurion
Joined: 08 Dec 2010
Posts: 135
|
The issue is fixed.
The workaround was to check 'In-Line ESQL when compiling .msgflow' in .bar editor.
THis helps WMB to take different path while creating .bar files.
In nutshell - If you are facing problems while calling stored procedure with error 'procedure name too long' or 'sql parser error', then 1. verify you are using proper dirvers for connectivity.
2. Enable 'IN-Line ESQL when compiling .msgflow' while building .bar file.
Hope this helps. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Fri May 18, 2012 7:16 pm Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| vishBroker wrote: |
2. Enable 'IN-Line ESQL when compiling .msgflow' while building .bar file.
|
What is the purpose of this and what does it achieve? Can you please elaborate? |
|
| Back to top |
|
|
| vishBroker |
| Posted: Sat May 19, 2012 5:01 am Post subject: |
|
|
Centurion
Joined: 08 Dec 2010
Posts: 135
|
'In-Line ESQL when compiling .msgFlow' option makes broker to take different code path.
Some more details - I was getting error while calling stored procedure -with 'Name Too Long'.
In order to debug issue, I created sample procedure just for testing and it ran successfully. I modified some values(No CODE change, only static value of parameter) and deployed .bar file and it began to fail with 'Name Too Long' Error.
Next day when I recomplied same flow and deployed, it was working fine again.
(All this time - 'In Line ESQL ' option was NOT selected.
Then, I came to know about this 'In-Line ESQL' option on .bar editor and when I checked it and complied .bar files,deployed them.
It did not failed afterwords.
Somehow it MIGHT be working like this- When I checked ServiceTrace for 'procedure Name Too Long' error - It was showing something like below (Recursive Calls to same procedure)
===
....
ComIbmDatabaseConnectionManager , '{ CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL { CALL VAS.INSERT_HOURLY ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) }.INSERT_HOURLY ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) }.INSERT_HOURLY ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) }.INSERT_HOURLY ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) }.INSERT_HOURLY ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) }.INSERT_HOURLY ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) }.INSERT_HOURLY ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) }.INSERT_HOURLY
......
===
But when I enabled that option - there was no recursive calling in the serviceTrace.
Checked with IBM - they say - there is some defect in the broker and they are expecting it to be fixed in next fixPack.
Hope this helps. Let me know, if you need more details. |
|
| Back to top |
|
|
| thomaspt |
| Posted: Thu Aug 02, 2012 3:12 am Post subject: Where is the In-Line ESQL |
|
|
Newbie
Joined: 01 Aug 2012
Posts: 4
|
Could you let me know where the In-Line ESQL option is.
-Got it. For Reference - Double click on the bar (Opens the bar editors) on the prepare screen scroll to the right( Missed this as was working on a small screen with a VM). |
|
| 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
|
|
|
|
