Mercurial > hg > monetdb-java
changeset 271:4880267d0fe1
Added implementation of java.sql.CallableStatement interface, test program and updated the ChangeLog and release notes.
This implementation resolves request: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6402
| author | Martin van Dinther <martin.van.dinther@monetdbsolutions.com> |
|---|---|
| date | Thu, 21 Mar 2019 18:57:07 +0100 (2019-03-21) |
| parents | 926afbe567f5 |
| children | 928df7febec4 |
| files | ChangeLog release.txt src/main/java/nl/cwi/monetdb/jdbc/MonetCallableStatement.java src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java tests/Test_CallableStmt.java tests/build.xml |
| diffstat | 6 files changed, 861 insertions(+), 15 deletions(-) [+] |
line wrap: on
line diff
--- a/ChangeLog +++ b/ChangeLog @@ -1,6 +1,11 @@ # ChangeLog file for monetdb-java # This file is updated with Maddlog +* Thu Mar 21 2019 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> +- Added implementation of java.sql.CallableStatement interface. Some standard + Java applications require this JDBC interface for executing SQL stored procedures. + This implementation resolves request: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6402 + * Thu Mar 7 2019 Martin van Dinther <martin.van.dinther@monetdbsolutions.com> - Improved MonetDatabaseMetaData methods: - getNumericFunctions(): it now includes functions: degrees, fuse, ms_round, ms_str, ms_trunc and radians.
--- a/release.txt +++ b/release.txt @@ -55,11 +55,9 @@ Currently implemented JDBC 4.1 interface * java.sql.Driver * java.sql.Connection - The next features/methods are NOT implemented: + The next features/methods are NOT useable/supported: - createArrayOf, createNClob, createStruct, createSQLXML - createStatement with result set holdability - - prepareCall (CallableStatement is not supported) - see also: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6402 - prepareStatement with array of column indices or column names - setHoldability (close/hold cursors over commit is not configurable) @@ -70,7 +68,7 @@ Currently implemented JDBC 4.1 interface * java.sql.DatabaseMetaData * java.sql.Statement - The next features/methods are NOT implemented: + The next features/methods are NOT useable/supported: - cancel (query execution cannot be terminated, once started) see also: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6222 - execute with column indices or names @@ -80,7 +78,7 @@ Currently implemented JDBC 4.1 interface - setEscapeProcessing on * java.sql.PreparedStatement - The next features/methods are NOT implemented: + The next features/methods are NOT useable/supported: - setArray - setAsciiStream, setBinaryStream, setUnicodeStream - setBlob @@ -89,8 +87,17 @@ Currently implemented JDBC 4.1 interface * java.sql.ParameterMetaData + * java.sql.CallableStatement + The next methods are NOT useable/supported: + - all getXyz(parameterIndex/parameterName, ...) methods because + output parameters in stored procedures are not supported by MonetDB + - all registerOutParameter(parameterIndex/parameterName, int sqlType, ...) methods + because output parameters in stored procedures are not supported by MonetDB + - wasNull() method because output parameters in stored procedures are + not supported by MonetDB + * java.sql.ResultSet - The next features/methods are NOT implemented: + The next features/methods are NOT useable/supported: - getArray - getAsciiStream, getUnicodeStream - getNClob @@ -105,12 +112,12 @@ Currently implemented JDBC 4.1 interface * java.sql.Blob A simple implementation using a byte[] to store the whole BLOB - The next features/methods are NOT implemented: + The next features/methods are NOT useable/supported: - setBinaryStream * java.sql.Clob A simple implementation using a StringBuilder to store the whole CLOB - The next features/methods are NOT implemented: + The next features/methods are NOT useable/supported: - getAsciiStream - setAsciiStream - setCharacterStream @@ -124,8 +131,6 @@ Currently implemented JDBC 4.1 interface The next java.sql.* interfaces are NOT implemented: * java.sql.Array - * java.sql.CallableStatement (use Statement or PreparedStatement instead) - see also: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6402 * java.sql.NClob * java.sql.Ref * java.sql.Rowid
new file mode 100644 --- /dev/null +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetCallableStatement.java @@ -0,0 +1,635 @@ +/* + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + * + * Copyright 1997 - July 2008 CWI, August 2008 - 2019 MonetDB B.V. + */ + +package nl.cwi.monetdb.jdbc; + +import java.io.InputStream; +import java.io.Reader; +import java.math.BigDecimal; +import java.net.URL; +import java.sql.Array; +import java.sql.Blob; +import java.sql.CallableStatement; +import java.sql.Clob; +import java.sql.Date; +import java.sql.NClob; +import java.sql.PreparedStatement; +import java.sql.Ref; +import java.sql.RowId; +import java.sql.SQLException; +import java.sql.SQLFeatureNotSupportedException; +import java.sql.SQLXML; +import java.sql.Time; +import java.sql.Timestamp; +import java.util.Calendar; +import java.util.Map; + +/** + * A {@link CallableStatement} suitable for the MonetDB database. + * + * 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 (MonetDB does not support this) and one that does not. + * If used, the result parameter must be registered as an OUT parameter (MonetDB does not support this). + * The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1. + * + * <code> + * { call procedure-name [ (arg1, arg2, ...) ] } + * { ?= call procedure-name [ (arg1, arg2, ...) ] } + * </code> + * + * IN parameter values are set using the set methods inherited from PreparedStatement. + * The type of all OUT parameters must be registered prior to executing the stored procedure; + * their values are retrieved after execution via the get methods provided here. + * Note: MonetDB does not support OUT or INOUT parameters. Only input parameters are supported. + * + * A CallableStatement can return one ResultSet object or multiple ResultSet objects. + * Multiple ResultSet objects are handled using operations inherited from Statement. + * + * For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters. + * + * This implementation of the CallableStatement interface reuses the implementation of MonetPreparedStatement for + * preparing the call statement, bind parameter values and execute the call, possibly multiple times with different parameter values. + * + * Note: currently we can not implement: + * - all getXyz(parameterIndex/parameterName, ...) methods + * - all registerOutParameter(parameterIndex/parameterName, int sqlType, ...) methods + * - wasNull() method + * because output parameters in stored procedures are not supported by MonetDB. + * + * @author Martin van Dinther + * @version 1.0 + */ + +public class MonetCallableStatement + extends MonetPreparedStatement + implements CallableStatement +{ + /** + * MonetCallableStatement constructor which checks the arguments for validity. + * A MonetCallableStatement is backed by a {@link MonetPreparedStatement}, + * which deals with most of the required stuff of this class. + * + * @param connection the connection that created this Statement + * @param resultSetType type of {@link ResultSet} to produce + * @param resultSetConcurrency concurrency of ResultSet to produce + * @param callQuery - an SQL CALL statement that may contain one or more '?' parameter placeholders. + * Typically this statement is specified using JDBC call escape syntax: + * { call procedure_name [(?,?, ...)] } + * or + * { ?= call procedure_name [(?,?, ...)] } + * @throws SQLException if an error occurs during creation + * @throws IllegalArgumentException is one of the arguments is null or empty + */ + MonetCallableStatement( + MonetConnection connection, + int resultSetType, + int resultSetConcurrency, + int resultSetHoldability, + String callQuery) + throws SQLException, IllegalArgumentException + { + super( + connection, + resultSetType, + resultSetConcurrency, + resultSetHoldability, + removeEscapes(callQuery) + ); + } + + /** parse call query string on + * { [?=] call <procedure-name> [(<arg1>,<arg2>, ...)] } + * and remove the JDBC escapes pairs: { and } + */ + private static String removeEscapes(String query) { + if (query == null) + return null; + + int firstAccOpen = query.indexOf("{"); + if (firstAccOpen == -1) + // nothing to remove + return query; + + int len = query.length(); + StringBuilder buf = new StringBuilder(len); + int countAccolades = 0; + // simple scanner which copies all characters except the first '{' and next '}' character + // we currently do not check if 'call' appears after the first '{' and before the '}' character + // we currently also do not deal correctly with { or } appearing as comment or as part of a string value + for (int i = 0; i < len; i++) { + char c = query.charAt(i); + switch (c) { + case '{': + countAccolades++; + if (i == firstAccOpen) + continue; + else + buf.append(c); + break; + case '}': + countAccolades--; + if (i > firstAccOpen && countAccolades == 0) + continue; + else + buf.append(c); + break; + default: + buf.append(c); + } + } + return buf.toString(); + } + + /** utility method to convert a parameter name to an int (which represents the parameter index) + * this will only succeed for strings like: "1", "2", "3", etc + * throws SQLException if it cannot convert the string to an integer number + */ + private int nameToIndex(String parameterName) throws SQLException { + if (parameterName == null) + throw new SQLException("Missing parameterName value", "22010"); + try { + return Integer.parseInt(parameterName); + } catch (NumberFormatException nfe) { + throw new SQLException("Cannot convert parameterName '" + parameterName + "'to integer value", "22010"); + } + } + + + // methods of interface CallableStatement + + // all getXyz(parameterIndex/parameterName, ...) methods are NOT supported + // because output parameters in stored procedures are not supported by MonetDB + @Override + public Array getArray(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getArray"); + } + @Override + public Array getArray(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getArray"); + } + @Override + public BigDecimal getBigDecimal(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getBigDecimal"); + } + @Override + @Deprecated + public BigDecimal getBigDecimal(int parameterIndex, int scale) throws SQLException { + throw newSQLFeatureNotSupportedException("getBigDecimal"); + } + @Override + public BigDecimal getBigDecimal(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getBigDecimal"); + } + @Override + public Blob getBlob(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getBlob"); + } + @Override + public Blob getBlob(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getBlob"); + } + @Override + public boolean getBoolean(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getBoolean"); + } + @Override + public boolean getBoolean(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getBoolean"); + } + @Override + public byte getByte(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getByte"); + } + @Override + public byte getByte(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getByte"); + } + @Override + public byte[] getBytes(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getBytes"); + } + @Override + public byte[] getBytes(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getBytes"); + } + @Override + public Reader getCharacterStream(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getCharacterStream"); + } + @Override + public Reader getCharacterStream(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getCharacterStream"); + } + @Override + public Clob getClob(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getClob"); + } + @Override + public Clob getClob(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getClob"); + } + @Override + public Date getDate(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getDate"); + } + @Override + public Date getDate(int parameterIndex, Calendar cal) throws SQLException { + throw newSQLFeatureNotSupportedException("getDate"); + } + @Override + public Date getDate(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getDate"); + } + @Override + public Date getDate(String parameterName, Calendar cal) throws SQLException { + throw newSQLFeatureNotSupportedException("getDate"); + } + @Override + public double getDouble(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getDouble"); + } + @Override + public double getDouble(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getDouble"); + } + @Override + public float getFloat(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getFloat"); + } + @Override + public float getFloat(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getFloat"); + } + @Override + public int getInt(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getInt"); + } + @Override + public int getInt(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getInt"); + } + @Override + public long getLong(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getLong"); + } + @Override + public long getLong(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getLong"); + } + @Override + public Reader getNCharacterStream(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getNCharacterStream"); + } + @Override + public Reader getNCharacterStream(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getNCharacterStream"); + } + @Override + public NClob getNClob(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getNClob"); + } + @Override + public NClob getNClob(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getNClob"); + } + @Override + public String getNString(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getNString"); + } + @Override + public String getNString(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getNString"); + } + @Override + public Object getObject(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getObject"); + } + @Override + public <T> T getObject(int parameterIndex, Class<T> type) throws SQLException { + throw newSQLFeatureNotSupportedException("getObject"); + } + @Override + public Object getObject(int parameterIndex, Map<String,Class<?>> map) throws SQLException { + throw newSQLFeatureNotSupportedException("getObject"); + } + @Override + public Object getObject(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getObject"); + } + @Override + public <T> T getObject(String parameterName, Class<T> type) throws SQLException { + throw newSQLFeatureNotSupportedException("getObject"); + } + @Override + public Object getObject(String parameterName, Map<String,Class<?>> map) throws SQLException { + throw newSQLFeatureNotSupportedException("getObject"); + } + @Override + public Ref getRef(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getRef"); + } + @Override + public Ref getRef(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getRef"); + } + @Override + public RowId getRowId(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getRowId"); + } + @Override + public RowId getRowId(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getRowId"); + } + @Override + public short getShort(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getShort"); + } + @Override + public short getShort(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getShort"); + } + @Override + public SQLXML getSQLXML(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getSQLXML"); + } + @Override + public SQLXML getSQLXML(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getSQLXML"); + } + @Override + public String getString(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getString"); + } + @Override + public String getString(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getString"); + } + @Override + public Time getTime(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getTime"); + } + @Override + public Time getTime(int parameterIndex, Calendar cal) throws SQLException { + throw newSQLFeatureNotSupportedException("getTime"); + } + @Override + public Time getTime(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getTime"); + } + @Override + public Time getTime(String parameterName, Calendar cal) throws SQLException { + throw newSQLFeatureNotSupportedException("getTime"); + } + @Override + public Timestamp getTimestamp(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getTimestamp"); + } + @Override + public Timestamp getTimestamp(int parameterIndex, Calendar cal) throws SQLException { + throw newSQLFeatureNotSupportedException("getTimestamp"); + } + @Override + public Timestamp getTimestamp(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getTimestamp"); + } + @Override + public Timestamp getTimestamp(String parameterName, Calendar cal) throws SQLException { + throw newSQLFeatureNotSupportedException("getTimestamp"); + } + @Override + public URL getURL(int parameterIndex) throws SQLException { + throw newSQLFeatureNotSupportedException("getURL"); + } + @Override + public URL getURL(String parameterName) throws SQLException { + throw newSQLFeatureNotSupportedException("getURL"); + } + + + // all registerOutParameter(parameterIndex/parameterName, int sqlType, ...) methods are NOT supported + // because output parameters in stored procedures are not supported by MonetDB + @Override + public void registerOutParameter(int parameterIndex, int sqlType) throws SQLException { + throw newSQLFeatureNotSupportedException("registerOutParameter"); + } + @Override + public void registerOutParameter(int parameterIndex, int sqlType, int scale) throws SQLException { + throw newSQLFeatureNotSupportedException("registerOutParameter"); + } + @Override + public void registerOutParameter(int parameterIndex, int sqlType, String typeName) throws SQLException { + throw newSQLFeatureNotSupportedException("registerOutParameter"); + } + @Override + public void registerOutParameter(String parameterName, int sqlType) throws SQLException { + throw newSQLFeatureNotSupportedException("registerOutParameter"); + } + @Override + public void registerOutParameter(String parameterName, int sqlType, int scale) throws SQLException { + throw newSQLFeatureNotSupportedException("registerOutParameter"); + } + @Override + public void registerOutParameter(String parameterName, int sqlType, String typeName) throws SQLException { + throw newSQLFeatureNotSupportedException("registerOutParameter"); + } + + + // all setXyz(parameterName, ...) methods are mapped to setXyz(nameToIndex(parameterName), ...) methods + // this only works for parameter names "1", "2", "3", etc. + @Override + public void setAsciiStream(String parameterName, InputStream x) throws SQLException { + setAsciiStream(nameToIndex(parameterName), x); + } + @Override + public void setAsciiStream(String parameterName, InputStream x, int length) throws SQLException { + setAsciiStream(nameToIndex(parameterName), x, length); + } + @Override + public void setAsciiStream(String parameterName, InputStream x, long length) throws SQLException { + setAsciiStream(nameToIndex(parameterName), x, length); + } + @Override + public void setBigDecimal(String parameterName, BigDecimal x) throws SQLException { + setBigDecimal(nameToIndex(parameterName), x); + } + @Override + public void setBinaryStream(String parameterName, InputStream x) throws SQLException { + setBinaryStream(nameToIndex(parameterName), x); + } + @Override + public void setBinaryStream(String parameterName, InputStream x, int length) throws SQLException { + setBinaryStream(nameToIndex(parameterName), x, length); + } + @Override + public void setBinaryStream(String parameterName, InputStream x, long length) throws SQLException { + setBinaryStream(nameToIndex(parameterName), x, length); + } + @Override + public void setBlob(String parameterName, Blob x) throws SQLException { + setBlob(nameToIndex(parameterName), x); + } + @Override + public void setBlob(String parameterName, InputStream inputStream) throws SQLException { + setBlob(nameToIndex(parameterName), inputStream); + } + @Override + public void setBlob(String parameterName, InputStream inputStream, long length) throws SQLException { + setBlob(nameToIndex(parameterName), inputStream, length); + } + @Override + public void setBoolean(String parameterName, boolean x) throws SQLException { + setBoolean(nameToIndex(parameterName), x); + } + @Override + public void setByte(String parameterName, byte x) throws SQLException { + setByte(nameToIndex(parameterName), x); + } + @Override + public void setBytes(String parameterName, byte[] x) throws SQLException { + setBytes(nameToIndex(parameterName), x); + } + @Override + public void setCharacterStream(String parameterName, Reader reader) throws SQLException { + setCharacterStream(nameToIndex(parameterName), reader); + } + @Override + public void setCharacterStream(String parameterName, Reader reader, int length) throws SQLException { + setCharacterStream(nameToIndex(parameterName), reader, length); + } + @Override + public void setCharacterStream(String parameterName, Reader reader, long length) throws SQLException { + setCharacterStream(nameToIndex(parameterName), reader, length); + } + @Override + public void setClob(String parameterName, Clob x) throws SQLException { + setClob(nameToIndex(parameterName), x); + } + @Override + public void setClob(String parameterName, Reader reader) throws SQLException { + setClob(nameToIndex(parameterName), reader); + } + @Override + public void setClob(String parameterName, Reader reader, long length) throws SQLException { + setClob(nameToIndex(parameterName), reader, length); + } + @Override + public void setDate(String parameterName, Date x) throws SQLException { + setDate(nameToIndex(parameterName), x); + } + @Override + public void setDate(String parameterName, Date x, Calendar cal) throws SQLException { + setDate(nameToIndex(parameterName), x, cal); + } + @Override + public void setDouble(String parameterName, double x) throws SQLException { + setDouble(nameToIndex(parameterName), x); + } + @Override + public void setFloat(String parameterName, float x) throws SQLException { + setFloat(nameToIndex(parameterName), x); + } + @Override + public void setInt(String parameterName, int x) throws SQLException { + setInt(nameToIndex(parameterName), x); + } + @Override + public void setLong(String parameterName, long x) throws SQLException { + setLong(nameToIndex(parameterName), x); + } + @Override + public void setNCharacterStream(String parameterName, Reader value) throws SQLException { + setNCharacterStream(nameToIndex(parameterName), value); + } + @Override + public void setNCharacterStream(String parameterName, Reader value, long length) throws SQLException { + setNCharacterStream(nameToIndex(parameterName), value, length); + } + @Override + public void setNClob(String parameterName, NClob value) throws SQLException { + setNClob(nameToIndex(parameterName), value); + } + @Override + public void setNClob(String parameterName, Reader reader) throws SQLException { + setNClob(nameToIndex(parameterName), reader); + } + @Override + public void setNClob(String parameterName, Reader reader, long length) throws SQLException { + setNClob(nameToIndex(parameterName), reader, length); + } + @Override + public void setNString(String parameterName, String value) throws SQLException { + setNString(nameToIndex(parameterName), value); + } + @Override + public void setNull(String parameterName, int sqlType) throws SQLException { + setNull(nameToIndex(parameterName), sqlType); + } + @Override + public void setNull(String parameterName, int sqlType, String typeName) throws SQLException { + setNull(nameToIndex(parameterName), sqlType, typeName); + } + @Override + public void setObject(String parameterName, Object x) throws SQLException { + setObject(nameToIndex(parameterName), x); + } + @Override + public void setObject(String parameterName, Object x, int targetSqlType) throws SQLException { + setObject(nameToIndex(parameterName), x, targetSqlType); + } + @Override + public void setObject(String parameterName, Object x, int targetSqlType, int scale) throws SQLException { + setObject(nameToIndex(parameterName), x, targetSqlType, scale); + } + @Override + public void setRowId(String parameterName, RowId x) throws SQLException { + setRowId(nameToIndex(parameterName), x); + } + @Override + public void setShort(String parameterName, short x) throws SQLException { + setShort(nameToIndex(parameterName), x); + } + @Override + public void setSQLXML(String parameterName, SQLXML xmlObject) throws SQLException { + setSQLXML(nameToIndex(parameterName), xmlObject); + } + @Override + public void setString(String parameterName, String x) throws SQLException { + setString(nameToIndex(parameterName), x); + } + @Override + public void setTime(String parameterName, Time x) throws SQLException { + setTime(nameToIndex(parameterName), x); + } + @Override + public void setTime(String parameterName, Time x, Calendar cal) throws SQLException { + setTime(nameToIndex(parameterName), x, cal); + } + @Override + public void setTimestamp(String parameterName, Timestamp x) throws SQLException { + setTimestamp(nameToIndex(parameterName), x); + } + @Override + public void setTimestamp(String parameterName, Timestamp x, Calendar cal) throws SQLException { + setTimestamp(nameToIndex(parameterName), x, cal); + } + @Override + public void setURL(String parameterName, URL val) throws SQLException { + setURL(nameToIndex(parameterName), val); + } + + /* Retrieves whether the last OUT parameter read had the value of SQL NULL. */ + @Override + public boolean wasNull() throws SQLException { + // wasNull() method is NOT supported + // because output parameters in stored procedures are not supported by MonetDB + throw newSQLFeatureNotSupportedException("wasNull"); + } + + // end methods interface CallableStatement +}
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java @@ -156,7 +156,7 @@ public class MonetConnection private boolean queriedCommentsTable = false; private boolean hasCommentsTable = false; - /** The last set query timeout on the server as used by Statement and PreparedStatement (and CallableStatement in future) */ + /** The last set query timeout on the server as used by Statement, PreparedStatement and CallableStatement */ protected int lastSetQueryTimeout = 0; // 0 means no timeout, which is the default on the server @@ -502,8 +502,7 @@ public class MonetConnection } catch (IllegalArgumentException e) { throw new SQLException(e.toString(), "M0M03"); } - // we don't have to catch SQLException because that is declared to - // be thrown + // we don't have to catch SQLException because that is declared to be thrown } /** @@ -726,8 +725,21 @@ public class MonetConnection public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { - throw newSQLFeatureNotSupportedException("prepareCall"); - /* a request to implement prepareCall() has already been logged, see https://www.monetdb.org/bugzilla/show_bug.cgi?id=6402 */ + try { + CallableStatement ret = new MonetCallableStatement( + this, + resultSetType, + resultSetConcurrency, + resultSetHoldability, + sql + ); + // store it in the map for when we close... + statements.put(ret, null); + return ret; + } catch (IllegalArgumentException e) { + throw new SQLException(e.toString(), "M0M03"); + } + // we don't have to catch SQLException because that is declared to be thrown } /**
new file mode 100644 --- /dev/null +++ b/tests/Test_CallableStmt.java @@ -0,0 +1,182 @@ +/* + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + * + * Copyright 1997 - July 2008 CWI, August 2008 - 2019 MonetDB B.V. + */ + +import java.sql.*; + +public class Test_CallableStmt { + public static void main(String[] args) throws Exception { + Connection con = DriverManager.getConnection(args[0]); + Statement stmt = null; + CallableStatement cstmt = null; + try { + String tbl_nm = "tbl6402"; + String proc_nm = "proc6402"; + + stmt = con.createStatement(); + + // create a test table. + stmt.executeUpdate("CREATE TABLE IF NOT EXISTS " + tbl_nm + " (tint int, tdouble double, tbool boolean, tvarchar varchar(15), tclob clob, turl url, tclen int);"); + System.out.println("Created table: " + tbl_nm); + + // create a procedure with multiple different IN parameters which inserts a row into a table of which one column is computed. + stmt.executeUpdate("CREATE PROCEDURE " + proc_nm + " (myint int, mydouble double, mybool boolean, myvarchar varchar(15), myclob clob, myurl url) BEGIN" + + " INSERT INTO " + tbl_nm + " (tint, tdouble, tbool, tvarchar, tclob, turl, tclen) VALUES (myint, mydouble, mybool, myvarchar, myclob, myurl, LENGTH(myvarchar) + LENGTH(myclob)); " + + "END;"); + System.out.println("Created procedure: " + proc_nm); + + // make sure we can call the procedure the old way (as string) + stmt.executeUpdate("call " + proc_nm + "(1, 1.1, true,'one','ONE', 'www.monetdb.org');"); + System.out.println("Called procedure (1): " + proc_nm); + showContents(con, tbl_nm); + + + // now use a CallableStament object + cstmt = con.prepareCall(" { call " + proc_nm + " (?,?, ?, ? , ?,?) } ;"); + System.out.println("Prepared Callable procedure: " + proc_nm); + + // specify first set of params + cstmt.setInt(1, 2); + cstmt.setDouble(2, 2.02); + cstmt.setBoolean(3, true); + cstmt.setString(4, "Two"); + Clob myclob = con.createClob(); + myclob.setString(1, "TWOs"); + cstmt.setClob(5, myclob); + cstmt.setString(6, "http://www.monetdb.org/"); + cstmt.execute(); + System.out.println("Called Prepared procedure (1): " + proc_nm); + showParams(cstmt); + showContents(con, tbl_nm); + + myclob.setString(1, "TREEs"); + // specify second set of params (some (1 and 3 and 5) are left the same) + cstmt.setDouble(2, 3.02); + cstmt.setString(4, "Tree"); + cstmt.setURL(6, new java.net.URL("https://www.monetdb.org/")); + cstmt.execute(); + System.out.println("Called Prepared procedure (2): " + proc_nm); + // showParams(cstmt); + showContents(con, tbl_nm); + + // specify third set of params (some (1 and 2) are left the same) + cstmt.setInt(1, 4); + cstmt.setBoolean(3, false); + cstmt.setString(4, "Four"); + cstmt.executeUpdate(); + System.out.println("Called Prepared procedure (3): " + proc_nm); + showContents(con, tbl_nm); + + // test setNull() also + cstmt.setNull(3, Types.BOOLEAN); + cstmt.setNull(5, Types.CLOB); + cstmt.setNull(2, Types.DOUBLE); + cstmt.setNull(4, Types.VARCHAR); + cstmt.setNull(1, Types.INTEGER); + cstmt.executeUpdate(); + System.out.println("Called Prepared procedure (with NULLs): " + proc_nm); + showContents(con, tbl_nm); + + + System.out.println("Test completed. Cleanup procedure and table."); + stmt.execute("DROP PROCEDURE IF EXISTS " + proc_nm + ";"); + stmt.execute("DROP TABLE IF EXISTS " + tbl_nm + ";"); + + } catch (SQLException e) { + System.out.println("main failed: " + e.getMessage()); + System.out.println("ABORTING TEST"); + } finally { + try { + if (cstmt != null) + cstmt.close(); + if (stmt != null) + stmt.close(); + } catch (SQLException e) { /* ignore */ } + } + + con.close(); + } + + + // some utility methods for showing table content and params meta data + static void showContents(Connection con, String tblnm) { + Statement stmt = null; + ResultSet rs = null; + try { + stmt = con.createStatement(); + rs = stmt.executeQuery("SELECT * FROM " + tblnm); + if (rs != null) { + ResultSetMetaData rsmd = rs.getMetaData(); + System.out.println("Table " + tblnm + " has " + rsmd.getColumnCount() + " columns:"); + for (int col = 1; col <= rsmd.getColumnCount(); col++) { + System.out.print("\t" + rsmd.getColumnLabel(col)); + } + System.out.println(); + while (rs.next()) { + for (int col = 1; col <= rsmd.getColumnCount(); col++) { + System.out.print("\t" + rs.getString(col)); + } + System.out.println(); + } + } else + System.out.println("failed to execute query: SELECT * FROM " + tblnm); + } catch (SQLException e) { + System.out.println("showContents failed: " + e.getMessage()); + } finally { + try { + if (rs != null) + rs.close(); + if (stmt != null) + stmt.close(); + } catch (SQLException e) { /* ignore */ } + } + } + + static void showParams(PreparedStatement stmt) { + try { + ParameterMetaData pmd = stmt.getParameterMetaData(); + System.out.println(pmd.getParameterCount() + " parameters reported:"); + for (int parm = 1; parm <= pmd.getParameterCount(); parm++) { + System.out.print(parm + "."); + int nullable = pmd.isNullable(parm); + System.out.println("\tnullable " + nullable + " (" + paramNullableName(nullable) + ")"); + System.out.println("\tsigned " + pmd.isSigned(parm)); + System.out.println("\tprecision " + pmd.getPrecision(parm)); + System.out.println("\tscale " + pmd.getScale(parm)); + System.out.println("\ttype " + pmd.getParameterType(parm)); + System.out.println("\ttypename " + pmd.getParameterTypeName(parm)); + System.out.println("\tclassname " + pmd.getParameterClassName(parm)); + int mode = pmd.getParameterMode(parm); + System.out.println("\tmode " + mode + " (" + paramModeName(mode) + ")"); + } + } catch (SQLException e) { + System.out.println("showParams failed: " + e.getMessage()); + } + } + + static String paramNullableName(int nullable) { + if (nullable == ParameterMetaData.parameterNoNulls) + return "NO"; + if (nullable == ParameterMetaData.parameterNullable) + return "YA"; + if (nullable == ParameterMetaData.parameterNullableUnknown) + return "UNKNOWN"; + return "INVALID" + nullable; + } + + static String paramModeName(int mode) { + if (mode == ParameterMetaData.parameterModeIn) + return "IN"; + if (mode == ParameterMetaData.parameterModeInOut) + return "INOUT"; + if (mode == ParameterMetaData.parameterModeOut) + return "OUT"; + if (mode == ParameterMetaData.parameterModeUnknown) + return "UNKNOWN"; + return "INVALID" + mode; + } +}
--- a/tests/build.xml +++ b/tests/build.xml @@ -121,6 +121,7 @@ Copyright 1997 - July 2008 CWI, August 2 <antcall target="Test_PStimedate" /> <antcall target="Test_PStimezone" /> <antcall target="Test_PStypes" /> + <antcall target="Test_CallableStmt" /> <antcall target="Test_Rbooleans" /> <antcall target="Test_Rmetadata" /> <antcall target="Test_Rpositioning" /> @@ -160,6 +161,12 @@ Copyright 1997 - July 2008 CWI, August 2 </antcall> </target> + <target name="Test_CallableStmt"> + <antcall target="test_class"> + <param name="test.class" value="Test_CallableStmt" /> + </antcall> + </target> + <target name="Test_Cautocommit"> <antcall target="test_class"> <param name="test.class" value="Test_Cautocommit" />