'database:​preparedst​atement:No​Parameters​' error when trying to call stored procedure with prepared statement

I'm basically trying to call a stored precedure using prepared statements (Matlab 2021a). The docs specify that one should do it this way:

query = "{CALL dbo.getSupplierInfo(?)}";
pstmt = databasePreparedStatement(conn,query)

Now, running a standard query and calling a stored procedure using execute works perfectly well for me and returns with no errors (I'm going to use mock names):

query = "CALL mySchema.myProcedure(1)";
execute(connection, query)

message =

struct with fields:

myProcedure: 1

message is returned in higher level function after try/catch and 1 indicates that it was successful.

But when I'm trying to do the same with prepared statements, as follows:

query = "{CALL mySchema.myProcedure(?)}";
pstmt = databasePreparedStatement(connection, query);

I get an error in the second line (top element of stack):

Error using database.preparedstatement.SQLPreparedStatement (line 96)

Invalid SQL statement. SQL prepared statement must have at least one SQL parameter.

(ErrorID: database:preparedstatement:NoParameters)

From what I understand, the question marks are there to indicate that a given query is a prepared statement, and they are a placeholder for values, so that you can bind your parameters' values to the query later on. If i have specified a parameter in my query, then why is this error being raised? Am I missing something obvious? It might be helpful to add that prepared statements work with INSERT and UPDATE queries perfectly well for me. As a side question, why does CALL query need curly brackets for prepared statement?

Answer 1

[Swastik]

Hello @Bartosz Dutka,

It looks like you are encountering an issue where using a correct query with one parameter in “databasePreparedStatement” results in an error message indicating “NoParameters”, despite the query being correctly formed. This issue might be due to a bug in the Database Toolbox R2020b, where “databasePreparedStatement” throws an incorrect error message for various query-related issues, not just for the absence of parameters but also for other problems such as insufficient parameters or incorrect table names.

To address this problem and obtain more accurate error messages that can help you pinpoint the issue, a modification in the Database Toolbox source code is suggested.

Locate the source file “database.preparedstatement.SQLPreparedStatement” using:

edit database.preparedstatement.SQLPreparedStatement

Scroll to lines 95-97 of the file, where you will find the following code block:

if pstmt.ParameterCount <= 0 
    error(message('database:preparedstatement:NoParameters')); 
end 

Replace this code with the following to improve error reporting:

if pstmt.ParameterCount < 0 
    error(message('database:database:JDBCDriverError', char(pstmt.PreparedStatementObject.getErrorMessage()))); 
end 
if pstmt.ParameterCount == 0 
    error(message('database:preparedstatement:NoParameters')); 
end 

This modification checks for a negative parameter count to throw a JDBC driver error with a detailed message, while still correctly handling the case where no parameters are provided.

I hope this solution assists you in overcoming the error reporting issue with “databasePreparedStatement”, making it easier to debug and refine your database queries.