Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

 Brahmeswara Rao (Brahmesh@) Skandiabanken Brahmeswara Rao
Skandiabanken
SE
Brahmesh@ Member since 2013 183 posts
Skandiabanken
Posted: Nov 7, 2019
Last activity: Nov 12, 2019
Posted: 7 Nov 2019 9:56 EST
Last activity: 12 Nov 2019 3:52 EST
Closed
Solved

Unable to call SQL Server store procedure from Pega.

Report

Unable to make call to store procedure of Sql server 2016..

No issue with executing the store proc directly in sql server studio.

pxSQLStatementPre Call usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )
pxSQLStatementPost Call usp_skan_case_purge ( ? , ?, ? )

Show More

Unable to make call to store procedure of Sql server 2016..

No issue with executing the store proc directly in sql server studio.

pxSQLStatementPre Call usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )
pxSQLStatementPost Call usp_skan_case_purge ( ? , ?, ? )

pxRDBSQLVendorError1 102
pxRDBSQLVendorMessage1 Incorrect syntax near '@P0'.
pxRDBError There was a problem performing a database query: There was a problem getting a list: code: 102 SQLState: S0001 Message: Incorrect syntax near '@P0'. DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'. | SQL Code: 102 | SQL State: S0001
pzStatus valid

Pega 7.3.1

Store procedure :-

CREATE PROCEDURE usp_skan_case_purge
@case_ins_key VARCHAR(128),
@error_status VARCHAR(32) output,
@error_message VARCHAR(1024) output
AS
BEGIN

BEGIN TRY
BEGIN TRANSACTION;

DECLARE @PYID VARCHAR(16);

/* Get pyID from pzInsKey as what comes after first space * 
SET @PYID = SUBSTRING(@case_ins_key, CHARINDEX(' ', @case_ins_key) +1, DATALENGTH(@case_ins_key) - CHARINDEX(' ', @case_ins_key) +1 );

/* Application specific DML queries */

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF (Xact_state()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;

IF (Xact_state()) = 1
BEGIN
COMMIT TRANSACTION;
END;

SET @error_status='Error';
SELECT @error_message = ERROR_MESSAGE();
THROW;

END CATCH

END;


Show Less
Data Integration
Posted: 3 years ago
Posted: 8 Nov 2019 1:51 EST
Basil (Vasilii) Pozdeev (vaspoz) BPM Company Senior System Architect
BPM Company
NL
Report

db procedure is fine, could you please provide an sql query sending from pega?
Posted: 3 years ago
Posted: 8 Nov 2019 3:18 EST
Brahmeswara Rao (Brahmesh@) Skandiabanken Brahmeswara Rao
Skandiabanken
SE
Report

Thanks for response .Below are the query & error details .

Show More

Thanks for response .Below are the query & error details .

pxSQLStatementPre Call usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )
pxRDBSQLVendorError1 102
pxRDBSQLVendorMessage1 Incorrect syntax near '@P0'.
pxRDBError There was a problem performing a database query: There was a problem getting a list: code: 102 SQLState: S0001 Message: Incorrect syntax near '@P0'. DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'. | SQL Code: 102 | SQL State: S0001
pzStatus valid

 


Show Less
Posted: 3 years ago
Posted: 8 Nov 2019 5:44 EST
Basil (Vasilii) Pozdeev (vaspoz) BPM Company Senior System Architect
BPM Company
NL
Report

hm, interesting. How do you make a call, through sql connector?
Posted: 3 years ago
Posted: 10 Nov 2019 9:59 EST
Brahmeswara Rao (Brahmesh@) Skandiabanken Brahmeswara Rao
Skandiabanken
SE
Report

Yes ..using RDB-List method ( Connect-Sql).
Posted: 3 years ago
Posted: 11 Nov 2019 3:24 EST
Basil (Vasilii) Pozdeev (vaspoz) BPM Company Senior System Architect
BPM Company
NL
Report

Firstly, I think you should use RDB-Save.

In Save your syntax must be like:

\{Call SCHEMA_NAME.usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )\}

 
Posted: 3 years ago
Posted: 11 Nov 2019 6:28 EST
Brahmeswara Rao (Brahmesh@) Skandiabanken Brahmeswara Rao
Skandiabanken
SE
Report

Thanks for response , still getting the issue ,seems query parsing is not happening as expected.

Trying to call below syntax from RDB-Save.

Call pegaDATA.usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT}

 

 


Error in RDB-Save 
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 102 SQLState: S0001 Message: Incorrect syntax near '.'. 
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. 
 | SQL Code: 102 | SQL State: S0001 
 
	at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:354) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:333) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.Lister.convertSqlExceptionToDatabaseException(Lister.java:577) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.Lister.listWithResultPackager(Lister.java:421) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.Lister.list(Lister.java:185) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.DBQueryExecutor.executeRDB(DBQueryExecutor.java:123) ~[prprivate.jar:?] 

 

Show More

Thanks for response , still getting the issue ,seems query parsing is not happening as expected.

Trying to call below syntax from RDB-Save.

Call pegaDATA.usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT}

 

 


Error in RDB-Save 
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 102 SQLState: S0001 Message: Incorrect syntax near '.'. 
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. 
 | SQL Code: 102 | SQL State: S0001 
 
	at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:354) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:333) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.Lister.convertSqlExceptionToDatabaseException(Lister.java:577) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.Lister.listWithResultPackager(Lister.java:421) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.Lister.list(Lister.java:185) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.DBQueryExecutor.executeRDB(DBQueryExecutor.java:123) ~[prprivate.jar:?] 

 


Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. 
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:548) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:479) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7344) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713) ~[mssql-jdbc-6.4.0.jre8.jar:?] 

Show Less
Posted: 3 years ago
Posted: 11 Nov 2019 6:37 EST
Basil (Vasilii) Pozdeev (vaspoz) BPM Company Senior System Architect
BPM Company
NL
Report

could you attach a screenshot of your rdb-save rule?

Accepted Solution

Posted: 3 years ago
Posted: 11 Nov 2019 10:21 EST
Basil (Vasilii) Pozdeev (vaspoz) BPM Company Senior System Architect
BPM Company
NL
Report

A I mentioned above, the syntax MUST be (including symbols \{ and \}):

\{Call SCHEMA_NAME.usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )\}

Antony Lawrence R
Posted: 3 years ago
Posted: 12 Nov 2019 3:48 EST
Brahmeswara Rao (Brahmesh@) Skandiabanken Brahmeswara Rao
Skandiabanken
SE
Report

Thanks it worked ,But curious to know why we need curly brackets {/  /} around query , Is syntax specific to Pega / SQL server ?.

In earlier versions of pega,(7.1.8) ,we used to call store procedure of oracle & postgres with out having curly brackets if i'm not wrong .

why RDB-Save instead RDB-List
Posted: 3 years ago
Posted: 12 Nov 2019 3:52 EST
Basil (Vasilii) Pozdeev (vaspoz) BPM Company Senior System Architect
BPM Company
NL
Report

to be honest, I don't know) but glad that it works

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice