| Author |
Message
|
| ata_nitjsr |
 Posted: Sun Jul 16, 2023 6:48 am Post subject: SQL Server Store proc error |
 |
|
Acolyte
Joined: 08 Apr 2007
Posts: 56
|
I am trying to call a SQL server stored proc, Which return me an integer by taking time stamp as an input.
I am getting an error of procedure not found any help is apricated
| Code: |
Store proc definition :
CREATE PROCEDURE callStoredProc(IN ts TIMESTAMP)
RETURNS INTEGER
LANGUAGE DATABASE
--DYNAMIC RESULT SETS 1
EXTERNAL NAME "CMS.dbo.claimsProc";
--call yesterday store proc
DECLARE ts TIMESTAMP;
SET ts = '2021-04-21 08:34:16';
SET I = callStoredProc(ts) ;
|
Error in debug :
| Code: |
RecoverableException
File:CHARACTER:C:\ci\product-build\WMB\src\DataFlowEngine\MessageServices\ImbDatabaseManager.cpp
Line:INTEGER:3260
Function:CHARACTER:ImbDatabaseManager::getDBProcedureParameterInformation
Type:CHARACTER:ComIbmDatabaseConnectionManager
Name:CHARACTER:ComIbmDatabaseConnectionManager
Label:CHARACTER:ComIbmDatabaseConnectionManager
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2920
Text:CHARACTER:The procedure is unknown to the database and no definition could be found.
Insert
Type:INTEGER:5
Text:CHARACTER:CMSDB.CMS.dbo.claimsProc
|
SQL proc when called from sql server editor:
| Code: |
USE [CMS]
GO
DECLARE @return_value int
EXEC @return_value = dbo.claimsProc
@param = '2021-04-21 08:34:16'
SELECT 'Return Value' = @return_value
GO
|
Please let me know what i am missing  |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Sun Jul 16, 2023 7:10 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
Possibly passing a string and not a timestamp to the proc? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| ata_nitjsr |
| Posted: Sun Jul 16, 2023 10:50 pm Post subject: |
|
|
Acolyte
Joined: 08 Apr 2007
Posts: 56
|
Thanks team for looking into my issue
fjb_saper
| Quote: |
Possibly passing a string and not a timestamp to the proc? |
ts is in timestamp format:
| Code: |
ts:TIMESTAMP:java.util.GregorianCalendar[time=1619008456000,areFields
Set=true,areAllFieldsSet=false,lenient=true,zone=sun.util.calendar.ZoneInfo
[id="America/New_York",offset=-18000000,dstSavings=3600000,useDaylig
ht=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=America/Ne
w_York,offset=-18000000,dstSavings=3600000,useDaylight=true,startYear
=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7
200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfW
eek=1,endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,minimalDay
sInFirstWeek=1,ERA=?,YEAR=2021,MONTH=3,WEEK_OF_YEAR=?,WEEK_O
F_MONTH=?,DAY_OF_MONTH=21,DAY_OF_YEAR=?,DAY_OF_WEEK=?,DAY_
OF_WEEK_IN_MONTH=?,AM_PM=0,HOUR=8,HOUR_OF_DAY=8,MINUTE=34
,SECOND=16,MILLISECOND=0,ZONE_OFFSET=?,DST_OFFSET=?] |
|
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jul 17, 2023 2:00 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
This error: "The procedure is unknown to the database and no definition could be found." means that a matching procedure definition could not be found. Can you post the SQL Server procedure definition?
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 |
|
|
| ata_nitjsr |
| Posted: Mon Jul 17, 2023 2:32 am Post subject: |
|
|
Acolyte
Joined: 08 Apr 2007
Posts: 56
|
SQL proc:
| Code: |
USE [CMS]
GO
/****** Object: StoredProcedure [dbo].[claimsProc] Script Date: 7/17/2023 6:29:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[claimsProc](@param DATETIME) as
begin
BEGIN TRANSACTION
execute sp_executesql N'truncate table [dbo].CLM_HDR_YSTR';
insert into [dbo].CLM_HDR_YSTR select [ACTVN_PLAN_CD],[ADJ_RSN] ,[ADJ_TYP]..........
few more insert and delete statement
,[UPD_ON_TS],[SRC_SYS_CD] from [dbo].CLM_LN_CURR where cr_on_ts < @param;
delete from [dbo].CLM_LN_CURR where cr_on_ts < @param;
end;
|
|
|
| Back to top |
|
|
| ata_nitjsr |
| Posted: Mon Jul 17, 2023 3:20 am Post subject: Existing Java Code |
|
|
Acolyte
Joined: 08 Apr 2007
Posts: 56
|
This is the existing Java code which is running for last 6 yr that i am replacing with ACE esql. it might help in analysis the same stored proc is called here call CMS.dbo.claimsProc
| Code: |
package claims.dao;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import javax.naming.NamingException;
import claims.logger.ClaimsLogger;
public class ClaimsProcDAO extends GenericDAO {
ClaimsLogger logger = GenericDAO.getLogger(ClaimsDAO.class.getName());
DBConnection conHandler = new DBConnection();
public void callStoredProc(Timestamp ts) throws SQLException,
NamingException {
{
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = conHandler.getClaimsDBConnection();
CallableStatement cs = con.prepareCall("{call CMS.dbo.claimsProc(?)}");
cs.setTimestamp(1, (java.sql.Timestamp)ts);
cs.execute();
} catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
conHandler.closeDBResources(rs, ps);
DBConnection.closeConnection(con);
}
}
}
}
|
|
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jul 17, 2023 4:16 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
Is that all the code? It seems to show only the call and not any return processing? If it is all the code then remove the RETURNS INTEGER from the ESQL definitions as it does not appear to return anything?
It would also be useful to see the Stored Procedure SQL signature as well if you can post that?
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 |
|
|
| ata_nitjsr |
| Posted: Mon Jul 17, 2023 4:30 am Post subject: |
|
|
Acolyte
Joined: 08 Apr 2007
Posts: 56
|
| Quote: |
SQL proc:
Code:
USE [CMS]
GO
/****** Object: StoredProcedure [dbo].[claimsProc] Script Date: 7/17/2023 6:29:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[claimsProc](@param DATETIME) as
begin
BEGIN TRANSACTION
execute sp_executesql N'truncate table [dbo].CLM_HDR_YSTR';
insert into [dbo].CLM_HDR_YSTR select [ACTVN_PLAN_CD],[ADJ_RSN] ,[ADJ_TYP]..........
few more insert and delete statement
,[UPD_ON_TS],[SRC_SYS_CD] from [dbo].CLM_LN_CURR where cr_on_ts < @param;
delete from [dbo].CLM_LN_CURR where cr_on_ts < @param;
end;
|
I also did the following change but the error is same
| Code: |
DECLARE ts TIMESTAMP;-- CURRENT_TIMESTAMP;
SET ts = '1983-07-12 21:30:55';
CALL callStoredProc(ts) ;
CREATE PROCEDURE callStoredProc(IN ts TIMESTAMP)
--RETURNS INTEGER
LANGUAGE DATABASE
--DYNAMIC RESULT SETS 1
EXTERNAL NAME "CMS.dbo.claimsProc"; |
|
|
| Back to top |
|
|
| ata_nitjsr |
| Posted: Mon Jul 17, 2023 4:35 am Post subject: |
|
|
Acolyte
Joined: 08 Apr 2007
Posts: 56
|
As the java code says (java.sql.Timestamp)ts
i use:
SET ts = '1983-07-12 21:30:55.888';
Still the error is same. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jul 17, 2023 6:34 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
Hmm it might be worth trying EXTERNAL NAME "dbo.claimsProc"; in the ESQL definition
_________________
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 |
|
|
| ata_nitjsr |
| Posted: Mon Jul 17, 2023 7:22 am Post subject: |
|
|
Acolyte
Joined: 08 Apr 2007
Posts: 56
|
Thanks for your input I have tried those before coming to the forum
normal insert statement is working fine with (CMS.dbo.CLM_HDR_CURR):
SET INSERT_CLAIMS = 'INSERT INTO CMS.dbo.CLM_HDR_CURR (SPRTEAM,ASGNTEAM,... |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jul 18, 2023 1:11 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
OK, so this is odd. I can't see any obvious reason why it is not working. The only real difference I can see is that your procedure does not have
which the examples say is needed for result sets to be returned but should not matter with this procedure. If I were you I would try and get the sample from the docs working if you have time. If not, you should raise a PMR for support to look into it.
Assuming you want to try the sample first, the docs https://www.ibm.com/docs/en/app-connect/12.0?topic=statements-create-procedure-statement have this example for Database routine example 4 for SQL Server
ESQL Definition:
| Code: |
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;
SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );
CREATE PROCEDURE swapParms (
IN parm1 CHARACTER,
INOUT parm2 CHARACTER,
INOUT parm3 CHARACTER
)
LANGUAGE DATABASE
EXTERNAL NAME dbSwapParms; |
With this procedure definition in test1.sql:
| Code: |
-- SQLServer Example Stored Procedure
DROP PROCEDURE dbSwapParms
go
CREATE PROCEDURE dbSwapParms
@in_param CHAR(32),
@out_param CHAR(32) OUT,
@inout_param CHAR(32) OUT
AS
SET NOCOUNT ON
SET @out_param = @inout_param
SET @inout_param = @in_param
go |
Which you can test is defined with
| Code: |
| isql -UuserID -Ppassword -Sserver -ddatasource -itest1.sql |
If the above does work then try swapping the CHAR(32) to TIMESTAMP and see if that works for this smaller example...
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 |
|
|
| ata_nitjsr |
| Posted: Tue Jul 18, 2023 2:44 am Post subject: |
|
|
Acolyte
Joined: 08 Apr 2007
Posts: 56
|
Fyi: Character datatype also give me same error.
As recommended I will reach to IBM PMR team. Will keep the link posted on the progress. |
|
| Back to top |
|
|
| ghoshly |
| Posted: Tue Jul 25, 2023 1:21 pm Post subject: User permission |
|
|
Partisan
Joined: 10 Jan 2008
Posts: 325
|
| Please make sure the user used via mqsisetdbparms command has given the execute permission by DBA. |
|
| Back to top |
|
|
| ata_nitjsr |
| Posted: Thu Jul 27, 2023 12:51 am Post subject: |
|
|
Acolyte
Joined: 08 Apr 2007
Posts: 56
|
The issue is resolved by IBM PMR the :
EXTERNAL NAME "dbo.claimsProc"; should be used instead of EXTERNAL NAME "CMS.dbo.claimsProc";
but for sql query i have used SET INSERT_CLAIMS = 'INSERT INTO CMS.dbo.CLM_HDR_CURR (SPRTEAM,ASGNTEAM,...
Thanks all for your time and valuable input.
Last edited by ata_nitjsr on Thu Jul 27, 2023 12:57 am; edited 1 time in total |
|
| Back to top |
|
|
|
|
