B4J Question MSSQL Stored Procedure Problem

[Philip Chatzigeorgiadis]

I have this stored procedure in MSSQL

CREATE PROCEDURE [dbo].[PivotTimkat] @CUST nvarchar(25)
AS
DECLARE @ColumnNames NVARCHAR(MAX);
SELECT @ColumnNames = COALESCE(@ColumnNames + ', ', '') + QUOTENAME(IMNIA_APO)
FROM (SELECT DISTINCT TOP 5 TIMKATID,IMNIA_APO FROM TIMKAT_MASTER WHERE KOD_PEL=@CUST ORDER BY TIMKATID DESC) AS TIMOKATALOGOI;

-- Create the dynamic pivot query
DECLARE @DynamicPivotQuery NVARCHAR(MAX);
SET @DynamicPivotQuery = N'SELECT PERGR, ' + @ColumnNames + '
FROM (SELECT IMNIA_APO, PERGR, TIMI FROM TIMKAT_MASTER INNER JOIN TIMKAT_EIDH
ON TIMKAT_MASTER.TIMKATID = TIMKAT_EIDH.TIMKATID WHERE KOD_PEL=''' + @CUST + ''') AS SourceData
PIVOT (SUM(TIMI) FOR IMNIA_APO IN (' + @ColumnNames + ')) AS PivotTable;';

-- Execute the dynamic pivot query
EXEC sp_executesql @DynamicPivotQuery;

GO

It is executed fine in SSMS:

However, when I try to call it from B4x, like this:

Dim sql As SQL = DBM.GetSQL
    Dim obj As Object
    Dim Params As List
    Params.Initialize 
    Params.Add("30100026")
    obj=sql.CreateCallStatement("PivotTimkat(?)",Params)
    Log("A")
    Dim rs As ResultSet
    rs=sql.ExecCall(obj) '< ------- ERROS POINTS HERE
    Log("B")
    Do While rs.NextRow
        Log(rs.GetString2(0)) 
    Loop 
    sql.close

I get this error:

A
Error occurred on line: 227 (CustOrders)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
    at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.executeQuery(NewProxyCallableStatement.java:3500)
    at anywheresoftware.b4j.objects.SQL.ExecCall(SQL.java:396)
    at OceanAnaxWeb.DevMat.com.custorders._loadorders(custorders.java:273)
    at OceanAnaxWeb.DevMat.com.custorders._websocket_connected(custorders.java:541)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at jdk.internal.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at anywheresoftware.b4j.object.WebSocketModule$Adapter$ThreadHandler.run(WebSocketModule.java:204)
    at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:47)
    at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:43)
    at anywheresoftware.b4a.shell.ShellBA.startMessageLoop(ShellBA.java:121)
    at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:180)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:309)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at anywheresoftware.b4j.object.JServlet.createInstance(JServlet.java:81)
    at anywheresoftware.b4j.object.BackgroundWorkersManager$1.run(BackgroundWorkersManager.java:40)
    at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:47)
    at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:43)
    at anywheresoftware.b4a.shell.ShellBA.startMessageLoop(ShellBA.java:121)
    at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:180)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:309)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at OceanAnaxWeb.DevMat.com.main.main(main.java:29)

I have no idea what the '@P0 ' could be, as I have no such parameter in my code.
However, note that if I modify the stored procedure, removing the @CUST parameter (I hardcode the parameter value in the stored procedure), then no error occurs in B4J.

What am I doing wrong?

Thanks in advance for your time.

 

[Philip Chatzigeorgiadis]

I got it working by using this library:

SQL: Simplyfying Calling Stored Procedures

Updated 2018-08-16. Please note there are breaking changes to the new version of the library (SQLCallPRoc Version 1-10.zip) so it will work correctly with the Microsoft JDBC Driver. The sample for MYSQL has changed and there is a new sample for MS SQL Server. See post #5 for why the change was...

www.b4x.com

However, I still do not know what was causing the problem, when I used the Sql.CreateCallStatement and Sql.ExecCall approach.