PIRL.Database
Class MySQL_Data_Port

java.lang.Object
  PIRL.Database.JDBC_Data_Port
      PIRL.Database.MySQL_Data_Port

All Implemented Interfaces:

Data_Port

public class MySQL_Data_Port
extends JDBC_Data_Port
implements Data_Port

Provides access to a MySQL database via a JDBC Data_Port.

Version:

1.23

Author:

Bradford Castalia, UA/PIRL

See Also:

JDBC_Data_Port

Field Summary

static String[]	Optional_Parameters Optional Configuration parameters.
static String	PORT The Configuration parameter to use when specifying a specific port for connection to the database server.
static String[][]	URL_Specs Required Configuration parameters and their default values.

Fields inherited from class PIRL.Database.JDBC_Data_Port

Case_Sensitive_Identifiers, Component_Delimiter, Treat_Schema_As_Catalog

Constructor Summary

MySQL_Data_Port()
Creates the abstract JDBC_Data_Port finalizer class and registers the class ID and name of the Data_Port type.

Method Summary

void	Open(Configuration configuration) Implements the Data_Port Open method.
Configuration	Parameters() Implements the Data_Port Parameters method.
Vector	Query(String SQL_query, int limit) Specialization of the interface's Query method to provide implementation of the limit functionality using the MySQL specific capability.

Methods inherited from class PIRL.Database.JDBC_Data_Port

Add_SQL_Listener, Case_Sensitive_Identifiers, Case_Sensitive_Identifiers, Catalog_Name, catalog_name, Catalogs, Close, composite_name, Config_Value, Configuration, Configure, Connection, Contents, Create, Create, Database_Catalog_Name, Database_Table_Name, Delete, Delete, Delete, Description, Field_Names, Field_Types, Fields, ID_Type, Insert, is_Open, Keys, List_String, Open_Data_Port, Remove_SQL_Listener, Rename, Rename, Select, Table_Name, table_name, Table_Reference_Component_Delimiter, Table_Reference, Tables, toString, Update, Update

Methods inherited from class java.lang.Object

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

Methods inherited from interface PIRL.Database.Data_Port

Add_SQL_Listener, Case_Sensitive_Identifiers, Case_Sensitive_Identifiers, Catalog_Name, Catalogs, Close, Configuration, Connection, Contents, Create, Create, Database_Catalog_Name, Database_Table_Name, Delete, Delete, Delete, Description, Field_Names, Field_Types, Fields, Insert, is_Open, Keys, Remove_SQL_Listener, Rename, Rename, Select, Table_Name, Table_Reference_Component_Delimiter, Table_Reference, Tables, toString, Update, Update

Field Detail

URL_Specs

public static final String[][] URL_Specs

Required Configuration parameters and their default values.

Database.DRIVER

com.mysql.jdbc.Driver

Configuration.HOST

localhost

Database.CATALOG

mysql

PORT

public static final String PORT

The Configuration parameter to use when specifying a specific port for connection to the database server.

See Also:

Constant Field Values

Optional_Parameters

public static final String[] Optional_Parameters

Optional Configuration parameters.

These parameters are based on the use of the MySQL Connector-J Driver, version 3.1.13.

Connection/Authentication Properties

user

The name of the user to connect as. [none]

password

The password to use when connecting. [none]

socketFactory

The name of the class that the driver should use for creating socket connections to the server. This class must implement the interface 'com.mysql.jdbc.SocketFactory' and have public no-args constructor. [com.mysql.jdbc.StandardSocketFactory]

connectTimeout

Timeout for socket connect (in milliseconds), with 0 being no timeout. Only works on JDK-1.4 or newer. [0]

socketTimeout

Timeout on network socket operations (0 means no timeout). [0]

useConfigs

Load the comma-delimited list of configuration properties before parsing the URL or applying user-specified properties. These configurations are explained in the 'Configurations' of the documentation.

interactiveClient

Set the CLIENT_INTERACTIVE flag, which tells MySQL to timeout connections based on INTERACTIVE_TIMEOUT instead of WAIT_TIMEOUT. [false]

propertiesTransform

An implementation of com.mysql.jdbc.ConnectionPropertiesTransform that the driver will use to modify URL properties passed to the driver before attempting a connection

useCompression

Use zlib compression when communicating with the server? [false]

High Availability and Clustering

autoReconnect

Should the driver attempt to re-connect if the connection dies (true or false)? [false]

autoReconnectForPools

Use a reconnection strategy appropriate for connection pools. [false]

failOverReadOnly

When failing over in autoReconnect mode, should the connection be set to 'read-only'? [true]

reconnectAtTxEnd

If autoReconnect is set to true, should the driver attempt reconnectionsat the end of every transaction? [false]

roundRobinLoadBalance

When autoReconnect is enabled, and failoverReadonly is false, should we pick hosts to connect to on a round-robin basis? [false]

queriesBeforeRetryMaster

Number of queries to issue before falling back to master when failed over (when using multi-host failover). Whichever condition is met first, 'queriesBeforeRetryMaster' or 'secondsBeforeRetryMaster' will cause an attempt to be made to reconnect to the master. [50]

secondsBeforeRetryMaster

How long should the driver wait (seconds), when failed over, before attempting to reconnect to the master server? Whichever condition is met first, 'queriesBeforeRetryMaster' or 'secondsBeforeRetryMaster' will cause an attempt to be made to reconnect to the master. [30]

enableDeprecatedAutoreconnect

Auto-reconnect functionality is deprecated starting with version 3.2, and will be removed in version 3.3. Set this property to 'true' to disable the check for the feature being configured. [false]

Security

allowMultiQueries

Allow the use of ';' to delimit multiple queries during one statement. [false]

useSSL

Use SSL when communicating with the server? [false]

requireSSL

Require SSL connection if useSSL=true? [false]

allowUrlInLocalInfile

Should the driver allow URLs in 'LOAD DATA LOCAL INFILE' statements? [false]

allowLoadLocalInfile

Should the driver allow use of 'LOAD DATA LOCAL INFILE...'. [true]

paranoid

Take measures to prevent exposure sensitive information in error messages and clear data structures holding sensitive data when possible? [false]

Performance

metadataCacheSize

The number of queries to cacheResultSetMetadata for if cacheResultSetMetaData is set to 'true' [50]

prepStmtCacheSize

If prepared statement caching is enabled, how many prepared statements should be cached? [25]

prepStmtCacheSqlLimit

If prepared statement caching is enabled, what's the largest SQL the driver will cache the parsing for? [256]

blobSendChunkSize

Chunk to use when sending BLOB/CLOBs via ServerPreparedStatements [1048576]

cacheCallableStmts

Should the driver cache the parsing stage of CallableStatements? [false]

cachePrepStmts

Should the driver cache the parsing stage of PreparedStatements? [false]

cacheResultSetMetadata

Should the driver cache ResultSetMetaData for Statements and PreparedStatements? (Req. JDK-1.4+) [false]

cacheServerConfiguration

Should the driver cache the results of 'SHOW VARIABLES' and 'SHOW COLLATION' on a per-URL basis? [false]

defaultFetchSize

The driver will call setFetchSize(n) with this value on all newly-created Statements [0]

dontTrackOpenResources

The JDBC specification requires the driver to automatically track and close resources, however if your application doesn't do a good job of explicitly calling close() on statements or result sets, this can cause memory leakage. Setting this property to true relaxes this constraint, and can be more memory efficient for some applications. [false]

dynamicCalendars

Should the driver retrieve the default calendar when required, or cache it per connection/session? [false]

elideSetAutoCommits

If using MySQL-4.1 or newer, should the driver only issue 'set autocommit=n' queries when the server's state doesn't match the requested state by Connection.setAutoCommit(boolean)? [false]

holdResultsOpenOverStatementClose

Should the driver close result sets on Statement.close() as required by the JDBC specification? [false]

rewriteBatchedStatements

Should the driver use multiqueries (irregardless of the setting of "allowMultiQueries") as well as rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() is called? Notice that this has the potential for SQL injection if using plain java.sql.Statements and your code doesn't sanitize input correctly. Notice that for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option, and that if you don't specify stream lengths when using PreparedStatement.set*Stream(),the driver won't be able to determine the optimium number of parameters per batch and you might receive anan error from the driver that the resultant packet is too large. Statement.getGeneratedKeys() for these rewritten statements only works when the entire batch includes INSERT statements. [false]

useFastIntParsing

Use internal String->Integer conversion routines to avoid excessive object creation? [true]

useLocalSessionState

Should the driver refer to the internal values of autocommit and transaction isolation that are set by Connection.setAutoCommit() and Connection.setTransactionIsolation(), rather than querying the database? [false]

useReadAheadInput

Use newer, optimized non-blocking, buffered input stream when reading from the server? [true]

Debugging and Profiling Properties

logger

The name of a class that implements 'com.mysql.jdbc.log.Log' that will be used to log messages to.(default logs to STDERR) [com.mysql.jdbc.log.StandardLogger]

profileSQL

Trace queries and their execution/fetch times to the configured logger? [false]

reportMetricsIntervalMillis

If 'gatherPerfMetrics' is enabled, how often should they be logged (in ms)? [30000]

maxQuerySizeToLog

Controls the maximum length/size of a query that will get logged when profiling or tracing [2048]

packetDebugBufferSize

The maximum number of packets to retain when 'enablePacketDebug' is true [20]

slowQueryThresholdMillis

If 'logSlowQueries' is enabled, how long should a query (in ms) before it is logged as 'slow'? [2000]

useUsageAdvisor

Should the driver issue 'usage' warnings advising proper and efficient usage of JDBC and MySQL Connector/J to the log? [false]

autoGenerateTestcaseScript

Should the driver dump the SQL it is executing, including server-side prepared statements to STDERR? [false]

dumpMetadataOnColumnNotFound

Should the driver dump the field-level metadata of a result set into the exception message when ResultSet.findColumn() fails? [false]

dumpQueriesOnException

Should the driver dump the contents of the query sent to the server in the message for SQLExceptions? [false]

enablePacketDebug

When enabled, a ring-buffer of 'packetDebugBufferSize' packets will be kept, and dumped when exceptions are thrown in key areas in the driver's code [false]

explainSlowQueries

If 'logSlowQueries' is enabled, should the driver automatically issue an 'EXPLAIN' on the server and send the results to the configured log at a WARN level? [false]

logSlowQueries

Should queries that take longer than 'slowQueryThresholdMillis' be logged? [false]

traceProtocol

Should trace-level network protocol be logged? [false]

Miscelaneous Properties

useUnicode

Should the driver use Unicode character encodings when handling strings? Should only be used when the driver can't determine the character set mapping, or you are trying to 'force' the driver to use a character set that MySQL either doesn't natively support (such as UTF-8)? [false]

characterEncoding

If useUnicode is true, what character encoding should the driver use when dealing with strings? [autodetect]

characterSetResults

Character set to tell the server to return results as.

connectionCollation

If set, tells the server to use this collation via 'set collation_connection'

sessionVariables

A comma-separated list of name/value pairs to be sent as SET SESSION ... to the server when the driver connects.

allowNanAndInf

Should the driver allow NaN or +/- INF values in PreparedStatement.setDouble()? [false]

autoClosePStmtStreams

Should the driver automatically call .close() on streams/readers passed as arguments via set*() methods? [false]

autoDeserialize

Should the driver automatically detect and de-serialize objects stored in BLOB fields? [false]

capitalizeTypeNames

Capitalize type names in DatabaseMetaData? (usually only useful when using WebObjects) [false]

clobberStreamingResults

This will cause a 'streaming' ResultSet to be automatically closed, and any oustanding data still streaming from the server to be discarded if another query is executed before all the data has been read from the server. [false]

continueBatchOnError

Should the driver continue processing batch commands if one statement fails? The JDBC spec allows either way. [true]

CreateDatabaseIfNotExist

Creates the database given in the URL if it doesn't yet exist. Assumes the configured user has permissions to create databases. [false]

emptyStringsConvertToZero

Should the driver allow conversions from empty string fields to numeric values of '0'? [true]

emulateLocators

[false]

emulateUnsupportedPstmts

Should the driver detect prepared statements that are not supported by the server, and replace them with client-side emulated versions? [true]

ignoreNonTxTables

Ignore non-transactional table warning for rollback? [false]

jdbcCompliantTruncation

Should the driver throw java.sql.DataTruncation exceptions when data is truncated as is required by the JDBC specification when connected to a server that supports warnings(MySQL 4.1.0 and newer)? [true]

maxRows

The maximum number of rows to return (<=0 means return all rows). [-1]

noDatetimeStringSync

Don't ensure that ResultSet.getDatetimeType().toString().equals(ResultSet.getString()) [false]

nullCatalogMeansCurrent

When DatabaseMetadataMethods ask for a 'catalog' parameter, does the value null mean use the current catalog? (this is not JDBC-compliant, but follows legacy behavior from earlier versions of the driver) [true]

nullNamePatternMatchesAll

Should DatabaseMetaData methods that accept *pattern parameters treat null the same as '%' (this is not JDBC-compliant, however older versions of the driver accepted this departure from the specification) [true]

overrideSupportsIntegrityEnhancementFacility

Should the driver return "true" for DatabaseMetaData.supportsIntegrityEnhancementFacility() even if the database doesn't support it to workaround applications that require this method to return "true" to signal support of foreign keys, even though the SQL specification states that this facility contains much more than just foreign key support (one such application being OpenOffice)? [false]

pedantic

Follow the JDBC spec to the letter. [false]

processEscapeCodesForPrepStmts

Should the driver process escape codes in queries that are prepared? [true]

relaxAutoCommit

If the version of MySQL the driver connects to does not support transactions, still allow calls to commit(), rollback() and setAutoCommit()? [false]

retainStatementAfterResultSetClose

Should the driver retain the Statement reference in a ResultSet after ResultSet.close() has been called. This is not JDBC-compliant after JDBC-4.0. [false]

rollbackOnPooledClose

Should the driver issue a rollback() when the logical connection in a pool is closed? [true]

runningCTS13

Enables workarounds for bugs in Sun's JDBC compliance testsuite version 1.3 [false]

serverTimezone

Override detection/mapping of timezone. Used when timezone from server doesn't map to Java timezone. [none]

strictUpdates

Should the driver do strict checking (all primary keys selected) of updatable result sets? [true]

tinyInt1isBit

Should the driver treat the datatype TINYINT(1) as the BIT type (because the server silently converts BIT -> TINYINT(1) when creating tables)? [true]

transformedBitIsBoolean

If the driver converts TINYINT(1) to a different type, should it use BOOLEAN instead of BIT for future compatibility with MySQL-5.0, as MySQL-5.0 has a BIT type? [false]

ultraDevHack

Create PreparedStatements for prepareCall() when required, because UltraDev is broken and issues a prepareCall() for _all_ statements? [false]

useGmtMillisForDatetimes

Convert between session timezone and GMT before creating Date and Timestamp instances (value of "false" is legacy behavior, "true" leads to more JDBC-compliant behavior. [false]

useHostsInPrivileges

Add '@hostname' to users in DatabaseMetaData.getColumn/TablePrivileges() (true/false), defaults to 'true'. [true]

useOldUTF8Behavior

Use the UTF-8 behavior the driver did when communicating with 4.0 and older servers [false]

useOnlyServerErrorMessages

Don't prepend 'standard' SQLState error messages to error messages returned by the server. [true]

useServerPrepStmts

Use server-side prepared statements if the server supports them? [true]

useSqlStateCodes

Use SQL Standard state codes instead of 'legacy' X/Open/SQL state codes (true/false), default is 'true' [true]

useStreamLengthsInPrepStmts

Honor stream length parameter in PreparedStatement/ResultSet.setXXXStream() method calls? [true]

useTimezone

Convert time/date types between client and server timezones? [false]

useUnbufferedInput

Don't use BufferedInputStream for reading data from the server? [true]

yearIsDateType

Should the JDBC driver treat the MySQL type "YEAR" as a java.sql.Date, or as a SHORT? [true]

zeroDateTimeBehavior

What should happen when the driver encounters DATETIME values that are composed entirely of zeroes (used by MySQL to represent invalid dates)? Valid values are 'exception', 'round' and 'convertToNull'. [exception]

gatherPerfMetrics

Should the driver gather performance metrics, and report them via the configured logger every 'reportMetricsIntervalMillis' milliseconds? [false]

useNewIO

Should the driver use the java.nio.* interfaces for network communication? [false]

callableStmtCacheSize

If 'cacheCallableStmts' is enabled, how many callable statements should be cached? [100]

initialTimeout

If autoReconnect is enabled, the initial time to wait between re-connect attempts (seconds). [2]

maxReconnects

If autoReconnect is enabled, how many times should the driver attempt to reconnect? [3]

Constructor Detail

MySQL_Data_Port

public MySQL_Data_Port()

Creates the abstract JDBC_Data_Port finalizer class and registers the class ID and name of the Data_Port type.

Method Detail

Parameters

public Configuration Parameters()
                         throws Database_Exception

Implements the Data_Port Parameters method.

Specified by:

Parameters in interface Data_Port

Returns:

A Configuration of Required and Optional Data_Port parameters.

Throws:

Database_Exception - If the Data_Port could not be accessed.

See Also:

Data_Port.Parameters()

Open

public void Open(Configuration configuration)
          throws Database_Exception

Implements the Data_Port Open method.

The Configuration that is supplied is conditionally set (existing parameters of the same name are not reset) with the required URL specifications default values. Then the Configuration is provided to the base JDBC_Data_Port Configure method to load the JDBC driver and save the Configuration.

The URL specifying how the connection will be made to the database server has the form:

jdbc:mysql://[HOST][:PORT]/CATALOG[?param1=value1[&param2=value2][...]]

All of the parameters will be sought in the Configuration. The paramN options are in the optional parameters list. Parameters with these names in the Configuration will be included in the URL, otherwise they will be omitted.

The URL specification is provided to the JDBC_Data_Port Open_Data_Port method which opens a connection to the database server.

Specified by:

Open in interface Data_Port

Specified by:

Open in class JDBC_Data_Port

Parameters:

configuration - The Configuration for this Data_Port.

Throws:

Database_Exception - If no Configuration is provided or the JDBC_Data_Port fails to load the driver or make the connection to the database server.

See Also:

Configuration

Query

public Vector Query(String SQL_query,
                    int limit)
             throws Database_Exception

Specialization of the interface's Query method to provide implementation of the limit functionality using the MySQL specific capability.

Specified by:

Query in interface Data_Port

Overrides:

Query in class JDBC_Data_Port

Parameters:

SQL_query - The syntax of the query string is database dependent. A typical example is an SQL "SELECT" statement.

limit - The maximum number of records to return. If negative, there will be no limit to the number of records returned. If zero, no records will be returned.

Returns:

A data table in form of a Vector of String Vectors (records). The first record is the field names for the field values in all subsequent records.

Throws:

Database_Exception - If the Data_Port is not open or the operation on the database server failed.

See Also:

JDBC_Data_Port.Query(String, int)