com.jdbwc.core.util
Class SQLResultsParser

java.lang.Object
  extended by com.jdbwc.core.util.SQLResultsParser

public class SQLResultsParser
extends java.lang.Object

This class is designed to parse an SQL SELECT String into table and field variables suitable for building ResultSetMetaData from.
At the moment its a bit limited but should be ok for most traditional sql.
Some SQL99 is also translated correctly.
If we cant produce accurate metadata here we usually throw an exception to ResultSetMetaData.

This class can handle a variety of FUNCTIONS including nested functions and table JOINS.
Nested functions will result in the first valid String parameter of a multi-parameter function being used as the actual column name.
The values alias (if any) will remain unchanged.

Version:
1.0.0.0
Author:
Tim Gall (Oz-DevWorX)

Constructor Summary
SQLResultsParser(WCConnection connection, java.lang.String sqlString)
          Constructs a new SQLParser ready to return the ResultSetMetaData for the sqlString parameter.

If the MySQL server is version 5.0.0 or greater INFORMATION_SCHEMA metaDataTables will be used to gather metaData in a single pass.
INFORMATION_SCHEMA metaDataTables will provide better performance.

Otherwise, built-in MySQL query functions will be used to gather metaData in 2 passes.
 
Method Summary
 SQLField[] getFields()
           
static boolean isSqlAResultType(java.lang.String sql)
          Check an SQL String for traces of ResultType keywords.
static boolean isSqlARoutine(java.lang.String sql)
          Check an SQL String for traces of Routine keywords used for creating and dropping functions and procedures from the database.
static java.lang.String replaceCustomDelimiters(java.lang.String sql)
          Find instances of the DELIMITER keyword, PHP doesnt support it anymore.
Contrary to some popular beliefs, mysql and mysqli extensions for php will both throw errors at the DELIMITER keyword.
Fortunately though, PHP doesnt require the delimiter to be changed.
Good'ol ; is fine, even in complex stored routine syntax.
DELIMITER most likely conflicts with a php/apache keyword or variable or whatever which would explain why support has been omitted.

HISTORICAL: I think it used to be supported in some earlier mysqli libraries, earlier than that I have no idea.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Constructor Detail

SQLResultsParser

public SQLResultsParser(WCConnection connection,
                        java.lang.String sqlString)
                 throws java.sql.SQLException
Constructs a new SQLParser ready to return the ResultSetMetaData for the sqlString parameter.

If the MySQL server is version 5.0.0 or greater INFORMATION_SCHEMA metaDataTables will be used to gather metaData in a single pass.
INFORMATION_SCHEMA metaDataTables will provide better performance.

Otherwise, built-in MySQL query functions will be used to gather metaData in 2 passes.

Parameters:
connection - Database Connection
sqlString - A valid SQL String
Throws:
java.sql.SQLException - if the connection or sqlString are not valid for this Constructor.
Method Detail

getFields

public SQLField[] getFields()
                     throws java.sql.SQLException
Throws:
java.sql.SQLException

isSqlARoutine

public static boolean isSqlARoutine(java.lang.String sql)
Check an SQL String for traces of Routine keywords used for creating and dropping functions and procedures from the database. In specific this method avoids us passing the keyword DELIMITER to the database as it produces unwanted results. Instead we process it in a specific way on the serverside end.

Parameters:
sql - SQL String to check for traces of an SQL Routine.
Returns:
true if sql contains any Routine keywords.

isSqlAResultType

public static boolean isSqlAResultType(java.lang.String sql)
Check an SQL String for traces of ResultType keywords.

Parameters:
sql - SQL String to check for traces of a SQL ResultType.
Returns:
true if sql contains any ResultType keywords.

replaceCustomDelimiters

public static java.lang.String replaceCustomDelimiters(java.lang.String sql)
Find instances of the DELIMITER keyword, PHP doesnt support it anymore.
Contrary to some popular beliefs, mysql and mysqli extensions for php will both throw errors at the DELIMITER keyword.
Fortunately though, PHP doesnt require the delimiter to be changed.
Good'ol ; is fine, even in complex stored routine syntax.
DELIMITER most likely conflicts with a php/apache keyword or variable or whatever which would explain why support has been omitted.

HISTORICAL: I think it used to be supported in some earlier mysqli libraries, earlier than that I have no idea.

Parameters:
sql - query String which might contain the DELIMITER keyword.
Returns:
sql String suitable for running via PHP.