ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker (ACE) SupportSQL Server Store proc error

Post new topicReply to topic Goto page 1, 2  Next
SQL Server Store proc error View previous topic :: View next topic
Author Message
ata_nitjsr
PostPosted: Sun Jul 16, 2023 6:48 am Post subject: SQL Server Store proc error Reply with quote

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
View user's profile Send private message
fjb_saper
PostPosted: Sun Jul 16, 2023 7:10 pm Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20707
Location: LI,NY

Possibly passing a string and not a timestamp to the proc?
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
ata_nitjsr
PostPosted: Sun Jul 16, 2023 10:50 pm Post subject: Reply with quote

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
View user's profile Send private message
mgk
PostPosted: Mon Jul 17, 2023 2:00 am Post subject: Reply with quote

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
View user's profile Send private message
ata_nitjsr
PostPosted: Mon Jul 17, 2023 2:32 am Post subject: Reply with quote

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
View user's profile Send private message
ata_nitjsr
PostPosted: Mon Jul 17, 2023 3:20 am Post subject: Existing Java Code Reply with quote

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
View user's profile Send private message
mgk
PostPosted: Mon Jul 17, 2023 4:16 am Post subject: Reply with quote

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
View user's profile Send private message
ata_nitjsr
PostPosted: Mon Jul 17, 2023 4:30 am Post subject: Reply with quote

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
View user's profile Send private message
ata_nitjsr
PostPosted: Mon Jul 17, 2023 4:35 am Post subject: Reply with quote

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
View user's profile Send private message
mgk
PostPosted: Mon Jul 17, 2023 6:34 am Post subject: Reply with quote

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
View user's profile Send private message
ata_nitjsr
PostPosted: Mon Jul 17, 2023 7:22 am Post subject: Reply with quote

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
View user's profile Send private message
mgk
PostPosted: Tue Jul 18, 2023 1:11 am Post subject: Reply with quote

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
Code:
SET NOCOUNT ON
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
View user's profile Send private message
ata_nitjsr
PostPosted: Tue Jul 18, 2023 2:44 am Post subject: Reply with quote

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
View user's profile Send private message
ghoshly
PostPosted: Tue Jul 25, 2023 1:21 pm Post subject: User permission Reply with quote

Partisan

Joined: 10 Jan 2008
Posts: 327

Please make sure the user used via mqsisetdbparms command has given the execute permission by DBA.
Back to top
View user's profile Send private message
ata_nitjsr
PostPosted: Thu Jul 27, 2023 12:51 am Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum IndexWebSphere Message Broker (ACE) SupportSQL Server Store proc error
Jump to:



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
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.