net.sourceforge.jtds.jdbc
Class SQLParser

java.lang.Object
  net.sourceforge.jtds.jdbc.SQLParser

class SQLParser

extends java.lang.Object

Process JDBC escape strings and parameter markers in the SQL string.

This code recognizes the following escapes:

1. Date {d 'yyyy-mm-dd'}
2. Time {t 'hh:mm:ss'}
3. Timestamp {ts 'yyyy-mm-dd hh:mm:ss.nnn'}
4. ESCAPE {escape 'x'}
5. Function {fn xxxx([arg,arg...])} NB The concat(arg, arg) operator is converted to (arg + arg)
6. OuterJoin {oj .....}
7. Call {?=call proc [arg, arg...]} or {call proc [arg, arg...]}

Notes:

1. This code is designed to be as efficient as possible and as result the validation done here is limited.
2. SQL comments are parsed correctly thanks to code supplied by Joel Fouse.

Version:

$Id: SQLParser.java,v 1.29 2007-07-08 17:28:23 bheineman Exp $

Author:

Mike Hutchinson

Nested Class Summary

private static class

SQLParser.CachedSQLQuery Serialized version of a parsed SQL query (the value stored in the cache for a parsed SQL).

Field Summary

private static SimpleLRUCache	cache LRU cache of previously parsed SQL
private ConnectionJDBC2	connection Connection object for server specific parsing.
private static java.util.HashMap	cvMap Map of jdbc to server data types for convert
private int	d Current position in output buffer.
private static byte[]	dateMask Syntax mask for date escape.
private static java.util.HashMap	fnMap Map of jdbc to sybase function names.
private static boolean[]	identifierChar Lookup table to test if character is part of an identifier.
private char[]	in Input buffer with SQL statement.
private java.lang.String	keyWord First SQL keyword or identifier in statement.
private int	len Length of input buffer.
private static java.util.HashMap	msFnMap Map of jdbc to sql server function names.
private char[]	out Output buffer to contain parsed SQL.
private java.util.ArrayList	params Parameter list to be populated or null if no parameters are expected.
private java.lang.String	procName Procedure name in call escape.
private int	s Current position in input buffer.
private java.lang.String	sql Original SQL string
private java.lang.String	tableName First table name in from clause
private char	terminator Current expected terminator character.
private static byte[]	timeMask Syntax mask for time escape.
(package private) static byte[]	timestampMask Syntax mask for timestamp escape.

Constructor Summary

private

SQLParser(java.lang.String sqlIn, java.util.ArrayList paramList, ConnectionJDBC2 connection) Constructs a new parser object to process the supplied SQL.

Method Summary

private void	append(char character) Adds the given character to out, incrementing d by and expanding out by a fixed number of characters if necessary.
private void	append(char[] chars)
private void	callEscape() Processes the JDBC {call procedure [(?
private java.lang.String	copyKeyWord() Copies over possible SQL keyword eg 'SELECT'
private void	copyLiteral(java.lang.String txt) Inserts a String literal in the output buffer.
private void	copyParam(java.lang.String name, int pos) Builds a new parameter item.
private java.lang.String	copyParamName() Copies an embedded parameter name to the output buffer.
private java.lang.String	copyProcName() Copies an embedded stored procedure identifier over to the output buffer.
private void	copyString() Copies over an embedded string literal unchanged.
private void	copyWhiteSpace() Copies over white space.
private void	escape() Processes the JDBC escape sequences.
private void	functionEscape() Processes the JDBC escape {fn function()}.
private static SimpleLRUCache	getCache(ConnectionJDBC2 connection) Retrieves the statement cache, creating it if required.
private boolean	getDateTimeField(byte[] mask) Utility routine to validate date and time escapes.
private java.lang.String	getTableName() Extracts the first table name following the keyword FROM.
private static boolean	isIdentifier(int ch) Determines if character could be part of an SQL identifier.
private void	likeEscape() Processes the JDBC escape {escape 'X'}.
private void	mustbe(char c, boolean copy) Checks that the next character is as expected.
private void	outerJoinEscape() Processes the JDBC escape {oj left outer join etc}.
(package private) java.lang.String[]	parse(boolean extractTable) Parses the SQL statement processing JDBC escapes and parameter markers.
(package private) static java.lang.String[]	parse(java.lang.String sql, java.util.ArrayList paramList, ConnectionJDBC2 connection, boolean extractTable) Parse the SQL statement processing JDBC escapes and parameter markers.
private void	skipMultiComments() Skips multi-line comments
private void	skipSingleComments() Skips single-line comments.
private void	skipWhiteSpace()

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

cache

private static SimpleLRUCache cache

LRU cache of previously parsed SQL

sql

private final java.lang.String sql

Original SQL string

in

private final char[] in

Input buffer with SQL statement.

s

private int s

Current position in input buffer.

len

private final int len

Length of input buffer.

out

private char[] out

Output buffer to contain parsed SQL.

d

private int d

Current position in output buffer.

params

private final java.util.ArrayList params

Parameter list to be populated or null if no parameters are expected.

terminator

private char terminator

Current expected terminator character.

procName

private java.lang.String procName

Procedure name in call escape.

keyWord

private java.lang.String keyWord

First SQL keyword or identifier in statement.

tableName

private java.lang.String tableName

First table name in from clause

connection

private final ConnectionJDBC2 connection

Connection object for server specific parsing.

identifierChar

private static boolean[] identifierChar

Lookup table to test if character is part of an identifier.

timeMask

private static final byte[] timeMask

Syntax mask for time escape.

dateMask

private static final byte[] dateMask

Syntax mask for date escape.

timestampMask

static final byte[] timestampMask

Syntax mask for timestamp escape.

fnMap

private static java.util.HashMap fnMap

Map of jdbc to sybase function names.

msFnMap

private static java.util.HashMap msFnMap

Map of jdbc to sql server function names.

cvMap

private static java.util.HashMap cvMap

Map of jdbc to server data types for convert

Constructor Detail

SQLParser

private SQLParser(java.lang.String sqlIn,
                  java.util.ArrayList paramList,
                  ConnectionJDBC2 connection)

Constructs a new parser object to process the supplied SQL.

Parameters:

sqlIn - the SQL statement to parse

paramList - the parameter list array to populate or null if no parameters are expected

connection - the parent Connection object

Method Detail

parse

static java.lang.String[] parse(java.lang.String sql,
                                java.util.ArrayList paramList,
                                ConnectionJDBC2 connection,
                                boolean extractTable)
                         throws java.sql.SQLException

Parse the SQL statement processing JDBC escapes and parameter markers.

Parameters:

extractTable - true to return the first table name in the FROM clause of a select

Returns:

The processed SQL statement, any procedure name, the first SQL keyword and (optionally) the first table name as elements 0 1, 2 and 3 of the returned String[].

Throws:

java.sql.SQLException - if a parse error occurs

getCache

private static SimpleLRUCache getCache(ConnectionJDBC2 connection)

Retrieves the statement cache, creating it if required.

Returns:

the cache as a SimpleLRUCache

isIdentifier

private static boolean isIdentifier(int ch)

Determines if character could be part of an SQL identifier.

Characters > 127 are assumed to be unicode letters in other languages than english which is reasonable in this application.

Parameters:

ch - the character to test.

Returns:

boolean true if ch in A-Z a-z 0-9 @ $ # _.

copyLiteral

private void copyLiteral(java.lang.String txt)
                  throws java.sql.SQLException

Inserts a String literal in the output buffer.

Parameters:

txt - The text to insert.

Throws:

java.sql.SQLException

copyString

private void copyString()

Copies over an embedded string literal unchanged.

copyKeyWord

private java.lang.String copyKeyWord()

Copies over possible SQL keyword eg 'SELECT'

copyParam

private void copyParam(java.lang.String name,
                       int pos)
                throws java.sql.SQLException

Builds a new parameter item.

Parameters:

name - Optional parameter name or null.

pos - The parameter marker position in the output buffer.

Throws:

java.sql.SQLException

copyProcName

private java.lang.String copyProcName()
                               throws java.sql.SQLException

Copies an embedded stored procedure identifier over to the output buffer.

Returns:

The identifier as a String.

Throws:

java.sql.SQLException

copyParamName

private java.lang.String copyParamName()

Copies an embedded parameter name to the output buffer.

Returns:

The identifier as a String.

copyWhiteSpace

private void copyWhiteSpace()

Copies over white space.

mustbe

private void mustbe(char c,
                    boolean copy)
             throws java.sql.SQLException

Checks that the next character is as expected.

Parameters:

c - The expected character.

copy - True if found character should be copied.

Throws:

java.sql.SQLException - if expected characeter not found.

skipWhiteSpace

private void skipWhiteSpace()
                     throws java.sql.SQLException

Throws:

java.sql.SQLException

skipSingleComments

private void skipSingleComments()

Skips single-line comments.

skipMultiComments

private void skipMultiComments()
                        throws java.sql.SQLException

Skips multi-line comments

Throws:

java.sql.SQLException

callEscape

private void callEscape()
                 throws java.sql.SQLException

Processes the JDBC {call procedure [(?,?,?)]} type escape.

Throws:

java.sql.SQLException - if an error occurs

getDateTimeField

private boolean getDateTimeField(byte[] mask)
                          throws java.sql.SQLException

Utility routine to validate date and time escapes.

Parameters:

mask - The validation mask

Returns:

True if the escape was valid and processed OK.

Throws:

java.sql.SQLException

outerJoinEscape

private void outerJoinEscape()
                      throws java.sql.SQLException

Processes the JDBC escape {oj left outer join etc}.

Throws:

java.sql.SQLException

functionEscape

private void functionEscape()
                     throws java.sql.SQLException

Processes the JDBC escape {fn function()}.

Throws:

java.sql.SQLException

likeEscape

private void likeEscape()
                 throws java.sql.SQLException

Processes the JDBC escape {escape 'X'}.

Throws:

java.sql.SQLException

escape

private void escape()
             throws java.sql.SQLException

Processes the JDBC escape sequences.

Throws:

java.sql.SQLException

getTableName

private java.lang.String getTableName()
                               throws java.sql.SQLException

Extracts the first table name following the keyword FROM.

Returns:

the table name as a String

Throws:

java.sql.SQLException

append

private final void append(char[] chars)

append

private final void append(char character)

Adds the given character to out, incrementing d by and expanding out by a fixed number of characters if necessary.

parse

java.lang.String[] parse(boolean extractTable)
                   throws java.sql.SQLException

Parses the SQL statement processing JDBC escapes and parameter markers.

Parameters:

extractTable - true to return the first table name in the FROM clause of a select

Returns:

The processed SQL statement, any procedure name, the first SQL keyword and (optionally) the first table name as elements 0 1, 2 and 3 of the returned String[].

Throws:

java.sql.SQLException