Unable to call SQL Server store procedure from Pega.
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 ( ? , ?, ? ) |
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;
BPM Company
NL
db procedure is fine, could you please provide an sql query sending from pega?
CGI
SE
Thanks for response .Below are the query & error details .
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 |
BPM Company
NL
hm, interesting. How do you make a call, through sql connector?
CGI
SE
Yes ..using RDB-List method ( Connect-Sql).
BPM Company
NL
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} )\}
CGI
SE
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:?]
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:?]
BPM Company
NL
could you attach a screenshot of your rdb-save rule?
CGI
SE
Accepted Solution
BPM Company
NL
A I mentioned above, the syntax MUST be (including symbols \{ and \}):
\{Call SCHEMA_NAME.usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )\}
CGI
SE
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
BPM Company
NL
to be honest, I don't know) but glad that it works