MySQL Bugs: #28469: JDBC driver does not support comments in the beginning of sql statement

Bug #28469	JDBC driver does not support comments in the beginning of sql statement
Submitted:	16 May 2007 14:00	Modified:	18 Jul 2007 9:43
Reporter:	Ansis Svaza	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.14, 5.0.5, 5.1.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	comments, jdbc, metadata

Description:
PreparedStatement.getMetaData () returns null instead of asking server for metadata.

Despite server fully supports sql statements like "-- Comment here\nSELECT..." JDBC drivers has some code that do some kind of sql statement precheck before sending statement to server. As far driver has no such support for comments it decides wrong about need to ask server for metadata.

This problem slightly relates to BUG#25025 as far initial suggestion for bug fix was to introduce support for comments in the begining of sql statement.

My need for such kind of comments is to avoid NetBeans (Visual Web Pack) from reformatting sql statement text. Since IDE can not parse statement none changes is done. Easiest way to achieve it is to add comment to beginning of sql statement. Current implementation of driver hangs some functions of IDE with NullPointerException, select statement columns can not be identified etc.

As far server side supports such statements and I can not find mention of any constraints about location of comments I can not see why not support them.

How to repeat:
PreparedStatement stmt = conn.prepareStatement ("-- Comment\nSELECT somefield FROM sometable") ;
ResultSetMetaData rsmd = stmt.getMetaData () ;
if (rsmd == null) {
  System.out.println ("NULL meta data!") ;
}

Suggested fix:
Allow comments to be embedded before the insert.

I will post fixed version of com.mysql.jdbc.StringUtils into java@lists.mysql.com

Sounds like a duplicate of BUG#27400 (fixed in 5.0.6 which has already been released).

Sorry, not a duplicate, slightly different.

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/26881

Implementation for comments in the beginning of sql statement

Attachment: StringUtils.java (text/java), 43.01 KiB.

Test code for bugfix

Attachment: TestComments.java (text/java), 1.37 KiB.

Ansis, thanks for your report and patch provided but, as you can see, Mark has already wrote one.

Since there is a working driver it is OK for me. :) My implementation of StringUtils.getOffset() could be more correct on comments skipping than Statement.findStartOfStatement() except handling not-finished comments. My implementation will return end of statement as result, but Mark's one - beginning. In both cases statement is incorrect.

My thinking was rather than put a full-fledged SQL parser in the driver, we'll let the server deal with the eventual syntax error that will result from a non-terminated comment.

This has been documented in the 5.0.7 changelog: 

PreparedStatement.getMetaData() for statements containing leading one-line comments is not returned correctly.

As part of this fix, we also overhauled detection of DML for executeQuery() and SELECTs for executeUpdate() in plain and prepared statements to be aware of the same types of comments.

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/33375

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/33832

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/33909

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/34839

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/34840

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/34855

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/35011

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/35407

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/35410

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/35414

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/38019

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/38024