You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Christian Schwanke (JIRA)" <ji...@apache.org> on 2007/04/16 09:33:15 UTC
[jira] Created: (DERBY-2550) Types.NULL is not accepted when using
setNull on a PreparedStatment
Types.NULL is not accepted when using setNull on a PreparedStatment
-------------------------------------------------------------------
Key: DERBY-2550
URL: https://issues.apache.org/jira/browse/DERBY-2550
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.2.2.0
Environment: Ubuntu 6.10, Java 5 and Mac OSX 10.4, Java5
Spring 2.x
Derby "Embedded" mode
Reporter: Christian Schwanke
Inserting data into table using a PreparedStatement will fail, if the setNull() method is used with Types.NULL.
I have tracked down the problem to the method "isJDBCTypeEquivalent(int existingType, int jdbcTypeId)" in class "org.apache.derby.iapi.types.DataTypeDescriptor" (Line 922).
This method checks the current column type against the type specified by the application. The setNull() method will throw an error, if the types do not match. The problem here is, that isJDBCTypeEquivalent will not accept Types.NULL as an valid equivalent to the column type.
When writing the JDBC code by hand one can avoid the problem - but this is quite annoying since the Jdbc-Support provided by the Spring-Framework will use setNull() with Types.NULL making it impossible to use Derby with Spring's plain JdbcTemplate.
Example:
String preparedSql = "INSERT INTO demo (stringValue) VALUES (?)";
PreparedStatement pstmt = con.prepareStatement(preparedSql);
// this will work, since the given type is equivalent
pstmt.setString(1, null);
pstmt.execute();
// this will fail, since Types.NULL is not recognized as equivalent
pstmt.setNull(1, Types.NULL);
pstmt.execute();
The exception thrown is
"java.sql.SQLException: An attempt was made to get a data value of type 'VARCHAR' from a data value of type '0'"
As far as I can see, it is sufficient to modify the first part of the isJDBCTypeEquivalent-method. At least, it solved my problems.
Current:
// Any type matches itself.
if (existingType == jdbcTypeId)
return true;
Fix:
// Any type matches itself.
if (existingType == jdbcTypeId || jdbcTypeId == Types.NULL)
return true;
I've attached a simple TestCase to reproduce the problem.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2550) Types.NULL is not accepted when using
setNull on a PreparedStatment
Posted by "Christian Schwanke (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2550?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Christian Schwanke updated DERBY-2550:
--------------------------------------
Attachment: SimpleEmbeddedTestCase.java
A simple JUnit-Testcase to reproduce the problem
> Types.NULL is not accepted when using setNull on a PreparedStatment
> -------------------------------------------------------------------
>
> Key: DERBY-2550
> URL: https://issues.apache.org/jira/browse/DERBY-2550
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.2.2.0
> Environment: Ubuntu 6.10, Java 5 and Mac OSX 10.4, Java5
> Spring 2.x
> Derby "Embedded" mode
> Reporter: Christian Schwanke
> Attachments: SimpleEmbeddedTestCase.java
>
>
> Inserting data into table using a PreparedStatement will fail, if the setNull() method is used with Types.NULL.
> I have tracked down the problem to the method "isJDBCTypeEquivalent(int existingType, int jdbcTypeId)" in class "org.apache.derby.iapi.types.DataTypeDescriptor" (Line 922).
> This method checks the current column type against the type specified by the application. The setNull() method will throw an error, if the types do not match. The problem here is, that isJDBCTypeEquivalent will not accept Types.NULL as an valid equivalent to the column type.
> When writing the JDBC code by hand one can avoid the problem - but this is quite annoying since the Jdbc-Support provided by the Spring-Framework will use setNull() with Types.NULL making it impossible to use Derby with Spring's plain JdbcTemplate.
> Example:
> String preparedSql = "INSERT INTO demo (stringValue) VALUES (?)";
> PreparedStatement pstmt = con.prepareStatement(preparedSql);
> // this will work, since the given type is equivalent
> pstmt.setString(1, null);
> pstmt.execute();
> // this will fail, since Types.NULL is not recognized as equivalent
> pstmt.setNull(1, Types.NULL);
> pstmt.execute();
> The exception thrown is
> "java.sql.SQLException: An attempt was made to get a data value of type 'VARCHAR' from a data value of type '0'"
> As far as I can see, it is sufficient to modify the first part of the isJDBCTypeEquivalent-method. At least, it solved my problems.
> Current:
> // Any type matches itself.
> if (existingType == jdbcTypeId)
> return true;
> Fix:
> // Any type matches itself.
> if (existingType == jdbcTypeId || jdbcTypeId == Types.NULL)
> return true;
> I've attached a simple TestCase to reproduce the problem.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2550) Types.NULL is not accepted when
using setNull on a PreparedStatment
Posted by "Christian Schwanke (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2550?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12489074 ]
Christian Schwanke commented on DERBY-2550:
-------------------------------------------
Hi,
sorry, I missed that one on the mailing list (although I searched quite a bit using the error message on JIRA and the web :-()
The way I see it now, this error is not derby related but is some kind of spring-bug, since using a sql-value-constant as a type parameter seems to be wrong (On the other hand, specifying value-constants in a class named "Types" isn't consistent either :-))
Since this is not derby's fault, I'm closing this issue.
Thanks for your quick help!
Regards,
Christian
> Types.NULL is not accepted when using setNull on a PreparedStatment
> -------------------------------------------------------------------
>
> Key: DERBY-2550
> URL: https://issues.apache.org/jira/browse/DERBY-2550
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.2.2.0
> Environment: Ubuntu 6.10, Java 5 and Mac OSX 10.4, Java5
> Spring 2.x
> Derby "Embedded" mode
> Reporter: Christian Schwanke
> Attachments: SimpleEmbeddedTestCase.java
>
>
> Inserting data into table using a PreparedStatement will fail, if the setNull() method is used with Types.NULL.
> I have tracked down the problem to the method "isJDBCTypeEquivalent(int existingType, int jdbcTypeId)" in class "org.apache.derby.iapi.types.DataTypeDescriptor" (Line 922).
> This method checks the current column type against the type specified by the application. The setNull() method will throw an error, if the types do not match. The problem here is, that isJDBCTypeEquivalent will not accept Types.NULL as an valid equivalent to the column type.
> When writing the JDBC code by hand one can avoid the problem - but this is quite annoying since the Jdbc-Support provided by the Spring-Framework will use setNull() with Types.NULL making it impossible to use Derby with Spring's plain JdbcTemplate.
> Example:
> String preparedSql = "INSERT INTO demo (stringValue) VALUES (?)";
> PreparedStatement pstmt = con.prepareStatement(preparedSql);
> // this will work, since the given type is equivalent
> pstmt.setString(1, null);
> pstmt.execute();
> // this will fail, since Types.NULL is not recognized as equivalent
> pstmt.setNull(1, Types.NULL);
> pstmt.execute();
> The exception thrown is
> "java.sql.SQLException: An attempt was made to get a data value of type 'VARCHAR' from a data value of type '0'"
> As far as I can see, it is sufficient to modify the first part of the isJDBCTypeEquivalent-method. At least, it solved my problems.
> Current:
> // Any type matches itself.
> if (existingType == jdbcTypeId)
> return true;
> Fix:
> // Any type matches itself.
> if (existingType == jdbcTypeId || jdbcTypeId == Types.NULL)
> return true;
> I've attached a simple TestCase to reproduce the problem.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-2550) Types.NULL is not accepted when using
setNull on a PreparedStatment
Posted by "Christian Schwanke (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2550?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Christian Schwanke closed DERBY-2550.
-------------------------------------
Resolution: Won't Fix
Derby Info: (was: [Existing Application Impact, Patch Available])
Derby's behaviour is correct - bug can be worked around using different spring methods.
> Types.NULL is not accepted when using setNull on a PreparedStatment
> -------------------------------------------------------------------
>
> Key: DERBY-2550
> URL: https://issues.apache.org/jira/browse/DERBY-2550
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.2.2.0
> Environment: Ubuntu 6.10, Java 5 and Mac OSX 10.4, Java5
> Spring 2.x
> Derby "Embedded" mode
> Reporter: Christian Schwanke
> Attachments: SimpleEmbeddedTestCase.java
>
>
> Inserting data into table using a PreparedStatement will fail, if the setNull() method is used with Types.NULL.
> I have tracked down the problem to the method "isJDBCTypeEquivalent(int existingType, int jdbcTypeId)" in class "org.apache.derby.iapi.types.DataTypeDescriptor" (Line 922).
> This method checks the current column type against the type specified by the application. The setNull() method will throw an error, if the types do not match. The problem here is, that isJDBCTypeEquivalent will not accept Types.NULL as an valid equivalent to the column type.
> When writing the JDBC code by hand one can avoid the problem - but this is quite annoying since the Jdbc-Support provided by the Spring-Framework will use setNull() with Types.NULL making it impossible to use Derby with Spring's plain JdbcTemplate.
> Example:
> String preparedSql = "INSERT INTO demo (stringValue) VALUES (?)";
> PreparedStatement pstmt = con.prepareStatement(preparedSql);
> // this will work, since the given type is equivalent
> pstmt.setString(1, null);
> pstmt.execute();
> // this will fail, since Types.NULL is not recognized as equivalent
> pstmt.setNull(1, Types.NULL);
> pstmt.execute();
> The exception thrown is
> "java.sql.SQLException: An attempt was made to get a data value of type 'VARCHAR' from a data value of type '0'"
> As far as I can see, it is sufficient to modify the first part of the isJDBCTypeEquivalent-method. At least, it solved my problems.
> Current:
> // Any type matches itself.
> if (existingType == jdbcTypeId)
> return true;
> Fix:
> // Any type matches itself.
> if (existingType == jdbcTypeId || jdbcTypeId == Types.NULL)
> return true;
> I've attached a simple TestCase to reproduce the problem.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2550) Types.NULL is not accepted when
using setNull on a PreparedStatment
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2550?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12489050 ]
Knut Anders Hatlen commented on DERBY-2550:
-------------------------------------------
Hi Christian,
This Spring issue has been discussed earlier. See this thread: http://mail-archives.apache.org/mod_mbox/db-derby-user/200607.mbox/%3ce8d7dh$5an$1@sea.gmane.org%3e
Note that Types.NULL identifies an SQL value (NULL), not an SQL type (http://java.sun.com/javase/6/docs/api/java/sql/Types.html#NULL).
> Types.NULL is not accepted when using setNull on a PreparedStatment
> -------------------------------------------------------------------
>
> Key: DERBY-2550
> URL: https://issues.apache.org/jira/browse/DERBY-2550
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.2.2.0
> Environment: Ubuntu 6.10, Java 5 and Mac OSX 10.4, Java5
> Spring 2.x
> Derby "Embedded" mode
> Reporter: Christian Schwanke
> Attachments: SimpleEmbeddedTestCase.java
>
>
> Inserting data into table using a PreparedStatement will fail, if the setNull() method is used with Types.NULL.
> I have tracked down the problem to the method "isJDBCTypeEquivalent(int existingType, int jdbcTypeId)" in class "org.apache.derby.iapi.types.DataTypeDescriptor" (Line 922).
> This method checks the current column type against the type specified by the application. The setNull() method will throw an error, if the types do not match. The problem here is, that isJDBCTypeEquivalent will not accept Types.NULL as an valid equivalent to the column type.
> When writing the JDBC code by hand one can avoid the problem - but this is quite annoying since the Jdbc-Support provided by the Spring-Framework will use setNull() with Types.NULL making it impossible to use Derby with Spring's plain JdbcTemplate.
> Example:
> String preparedSql = "INSERT INTO demo (stringValue) VALUES (?)";
> PreparedStatement pstmt = con.prepareStatement(preparedSql);
> // this will work, since the given type is equivalent
> pstmt.setString(1, null);
> pstmt.execute();
> // this will fail, since Types.NULL is not recognized as equivalent
> pstmt.setNull(1, Types.NULL);
> pstmt.execute();
> The exception thrown is
> "java.sql.SQLException: An attempt was made to get a data value of type 'VARCHAR' from a data value of type '0'"
> As far as I can see, it is sufficient to modify the first part of the isJDBCTypeEquivalent-method. At least, it solved my problems.
> Current:
> // Any type matches itself.
> if (existingType == jdbcTypeId)
> return true;
> Fix:
> // Any type matches itself.
> if (existingType == jdbcTypeId || jdbcTypeId == Types.NULL)
> return true;
> I've attached a simple TestCase to reproduce the problem.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.