Clear Filters
Clear Filters

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

9 views (last 30 days)
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?

Answers (1)

Swastik
Swastik on 22 May 2024 at 8:40
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.

Categories

Find more on Manage Products in Help Center and File Exchange

Products


Release

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!