[#CONJ-63] prepareCall() for SELECT statement throws exception

[CONJ-63] prepareCall() for SELECT statement throws exception Created: 2013-09-12 Updated: 2013-09-13 Resolved: 2013-09-12
Status:	Closed
Project:	MariaDB Connector/J
Component/s:	None
Affects Version/s:	1.1.4
Fix Version/s:	None

Type:

Bug

Priority:

Major

Reporter:

Doug Kirk

Assignee:

Vladislav Vaintroub

Resolution:

Not a Bug

Votes:

Labels:

jdbc

Environment:

Ubuntu64 connecting to MariaDB 10.0.4 locally

Description

prepareCall() parses and returns a CallableStatement, a subclass of PreparedStatement in JDBC.

In both the Oracle ojdbc6 (connecting to an Oracle DB) and MySQL/J Connector drivers, doing the following works fine (of course the Oracle query selected from different table/schema):

scala> import com.mysql.jdbc.Driver

import com.mysql.jdbc.Driver

scala> import java.sql._

import java.sql._

scala> val conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "***")

conn: java.sql.Connection = com.mysql.jdbc.JDBC4Connection@59f66488

scala> val stmt = conn.prepareCall("select user, host from user")

stmt: java.sql.CallableStatement = com.mysql.jdbc.JDBC4CallableStatement@598b619b: select user, host from user

But when it is attempted using the MariaDB client, an exception is incorrectly thrown:

scala> import org.mariadb.jdbc.Driver

import org.mariadb.jdbc.Driver

scala> import java.sql._

import java.sql._

scala> val conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "***")

conn: java.sql.Connection = org.mariadb.jdbc.MySQLConnection@3ef84d4c

scala> val stmt = conn.prepareCall("select user, host from user")

java.sql.SQLSyntaxErrorException: invalid callable syntax

	at org.mariadb.jdbc.MySQLCallableStatement.<init>(MySQLCallableStatement.java:369)

	at org.mariadb.jdbc.MySQLConnection.prepareCall(MySQLConnection.java:171)

In JDBC, the only real difference is that a CallableStatement allows for OUT parameters. However, without foreknowledge of the SQL being prepared, or code to parse and guess which type of SQL string is being passed to the DB-access code, it is desirable to use CallableStatements in place of PreparedStatements for all queries in systems where stored procedures are used.

Comments

Comment by Vladislav Vaintroub [ 2013-09-12 ]

This is an interesting interpretation of JDBC, but I have to disagree If it works in ConnectorJ or Oracle, it is accident/ implementation detail, rather than intention or adherence to the standard.

Here is what official doc says about CallableStatement http://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html

The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

{call <procedure-name>[(<arg1>,<arg2>, ...)]}

Also, according to the documentation Connection.prepareCall(String sql) creates a CallableStatement object for calling database stored procedures. According to the documentation, conn.prepareCall does not create PreparedStatement for executing a SELECT.

You always can use prepareStatement for you purposes.

Comment by Vladislav Vaintroub [ 2013-09-12 ]

You also can use prepareStatement , and pass "CALL function(?,?)" to it, this is allowed. you can prepare everything, but CallableStatement ,in this driver, serves the single purpose, that is also documented in the JDBC API, calling stored procedures or functions.

Comment by Doug Kirk [ 2013-09-13 ]

You are of course free to disagree but I say you're wrong

It is, after all, a subclass of PreparedStatement, which supports all other statement types (aside from functions/procedures with OUT parameters). Why would a subclass add a statement type and not support those of the parent class? It breaks the Liskov substitution principle:

From Wikipedia:

It states that, in a computer program, if S is a subtype of T, then objects of type T may be replaced with objects of type S (i.e., objects of type S may be substituted for objects of type T) without altering any of the desirable properties of that program (correctness, task performed, etc.).

Comment by Vladislav Vaintroub [ 2013-09-13 ]

Please feel free to disagree with my disagreement with your statement I do not want to start a theoretical discussion here, but Liskov principle is about usage of the objects, not about creation of them, so your interpretation does not sound right ( your interpretation is that - "one should always be able to create objects of subtype S, to use them instead of T"

Generated at Thu Feb 08 03:12:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[CONJ-63] prepareCall() for SELECT statement throws exception Created: 2013-09-12 Updated: 2013-09-13 Resolved: 2013-09-12