Bug #21267 NEP is thrown when using parameter metadata
Submitted: 25 Jul 2006 7:46 Modified: 9 Mar 2007 11:51
Reporter: wu mingxia Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.0.0-beta OS:Microsoft Windows (XP)
Assigned to: CPU Architecture:Any

[25 Jul 2006 7:46] wu mingxia
Description:
When I use mysql-connector-java-5.0.0-beta-bin.jar to connect mysql database, I write a sql text like "select id, name from myTable where id = ?", it works ok for myTable has id,name columns. But if I change the sql "select id, namm from myTable where id =?" where namm does not exist in myTable. But if I use parameterMetadata.getParameterCount(), it will return 1.It means there is one parameter metadata available. So I use parameterMetadata.getParameterType( param ), NEP will be thrown out. 
The error stack is:
java.lang.NullPointerException
	at com.mysql.jdbc.ResultSetMetaData.getField(ResultSetMetaData.java:381)
	at com.mysql.jdbc.ResultSetMetaData.getColumnType(ResultSetMetaData.java:259)
	at com.mysql.jdbc.MysqlParameterMetadata.getParameterType(MysqlParameterMetadata.java:80)
	at org.eclipse.birt.report.data.oda.jdbc.ParameterMetaData.getParameterType(ParameterMetaData.java:134)
	at org.eclipse.datatools.connectivity.oda.design.ui.designsession.DesignSessionUtil.toDataSetParametersDesign(DesignSessionUtil.java:484)

How to repeat:
write a invalid sql text with parameter place holder, the parameter metadata will show there is parameter, but when get the detail infomation of parameter, NEP.

Suggested fix:
If the sql is not correct, the parameter metadata should be unavailabe and the count should be zero.
[25 Jul 2006 9:24] wu mingxia
This bug also happens in 3.0 and 3.1 version
[25 Jul 2006 12:45] Tonci Grgin
Hello and thanks for your bug report. 
Verified as described by reporter:
JDK 1.6
mysql-connector-java 3.1.12, 3.1.13 and 5.0 2006-07-04 snapshot

    public static void main(String args[]) throws Exception {
        Connection testConn = null;
        PreparedStatement ps = null;
        java.sql.ParameterMetaData psMD = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            testConn = DriverManager.getConnection("jdbc:mysql://10.192.192.99:3307/test", "root", "");
            System.out.println("Connected");
            ps = testConn.prepareStatement(Query);
            ps.setInt(1,1);
            psMD = ps. getParameterMetaData();
            System.out.println("Param cnt: "+psMD.getParameterCount());
            System.out.println("Param Mode: "+psMD.getParameterMode(1));
            System.out.println("Param Type: "+psMD.getParameterType(1)); << NPE here.

init:
deps-jar:
compile:
run:
Connected
Param cnt: 1
Param Mode: 1
Exception in thread "main" java.lang.NullPointerException
        at com.mysql.jdbc.ResultSetMetaData.getField(ResultSetMetaData.java:381)
        at com.mysql.jdbc.ResultSetMetaData.getColumnType(ResultSetMetaData.java:259)
        at com.mysql.jdbc.MysqlParameterMetadata.getParameterType(MysqlParameterMetadata.java:80)
        at xxx.Main.main(Main.java:55)
Java Result: 1
[25 Jul 2006 12:52] Tonci Grgin
DDL:
CREATE TABLE `bug21141` (
`Col1` int(11) NOT NULL,
`Col2` varchar(45) default NULL,
`Col3` varchar(45) default NULL,
PRIMARY KEY  (`Col1`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
private static final String Query = "SELECT Col1, Col2,Col4 FROM bug21141 WHERE Col1=?";
Offending part is ... ,Col4 FROM ... which reffers to non-existent column.
[25 Jul 2006 13:36] Mark Matthews
This bug is related to the code that handles the fact that not all statements are supported for server-side prepare, therefore the syntax error that is raised when the original statement is sent to the server for prepare causes the driver to fall back to client-side emulation of prepared statements. These variants of prepared statements only have parameter count metadata available to them. 

I'll put in a check for this corner case, so that you get a better error message.
[18 Oct 2006 7:14] Aaron Luchko
I just ran into this issue where an incorrect SQL statement leads to a NPE when fetching the parameter metadata. Since running the same broken query results in a SQLException wouldn't throwing a SQLException when accessing the broken parameter make more sense?
[14 Feb 2007 1:20] Mark Matthews
The issue is that we can't tell from the client side, if the syntax error is because the statement basically isn't preparable (so we prepare it client-side), or if it's just a syntax error (which won't show up as a SQLException until we try and execute the statement we prepared using client-side statements).
[23 Feb 2007 21:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20509
[1 Mar 2007 19:11] Mark Matthews
Fixed in 5.0.5
[9 Mar 2007 11:51] MC Brown
A note has been placed in the 5.0.5 changelog.