//----------------------------------------------------------------------------
//
// Module: SimpleTextStatement.java
//
// Description: Implementation of the JDBC Statement interface
//
// Author: Karl Moss
//
// Copyright: (C) 1996,1997 Karl Moss. All rights reserved.
// You may study, use, modify and distribute this example
// for any purpose, provided that this copyright notice
// appears in all copies. This example is provided WITHOUT
// WARRANTY either expressed or implied.
//----------------------------------------------------------------------------
package jdbc.SimpleText;
//----------------------------------------------------------------------------
// A Statement object is used for executing a static SQL statement
// and obtaining the results produced by it.
//
// Only one ResultSet per Statement can be open at any point in
// time. Therefore, if the reading of one ResultSet is interleaved with
// the reading of another, each must have been generated by different
// Statements.
//----------------------------------------------------------------------------
// NOTE - this is an implementation of the JDBC API version 1.20
//---------------------------------------------------------------------------
import java.sql.*;
import java.util.Hashtable;
import java.io.*;
public class SimpleTextStatement
extends SimpleTextObject
implements SimpleTextIStatement
{
//------------------------------------------------------------------------
// initialize
//------------------------------------------------------------------------
public void initialize(
SimpleTextIConnection con)
throws SQLException
{
// Save the owning connection object
ownerConnection = con;
}
//------------------------------------------------------------------------
// executeQuery - JDBC API
// Execute a SQL statement that returns a single ResultSet.
//
// sql typically this is a static SQL SELECT statement
//
// Returns the table of data produced by the SQL statement
//------------------------------------------------------------------------
public ResultSet executeQuery(
String sql)
throws SQLException
{
if (traceOn()) {
trace("@executeQuery(" + sql + ")");
}
java.sql.ResultSet rs = null;
// Execute the query. If execute returns true, then a result set
// exists
if (execute(sql)) {
rs = getResultSet();
}
return rs;
}
//------------------------------------------------------------------------
// executeUpdate - JDBC API
// Execute a SQL INSERT, UPDATE or DELETE statement. In addition,
// SQL statements that return nothing such as SQL DDL statements
// can be executed.
//
// sql a SQL INSERT, UPDATE or DELETE statement or a SQL
// statement that returns nothing
//
// Returns either the row count for INSERT, UPDATE or DELETE; or 0
// for SQL statements that return nothing
//------------------------------------------------------------------------
public int executeUpdate(
String sql)
throws SQLException
{
if (traceOn()) {
trace("@executeUpdate(" + sql + ")");
}
int count = -1;
// Execute the query. If execute returns false, then an update
// count exists.
if (execute(sql) == false) {
count = getUpdateCount();
}
return count;
}
//------------------------------------------------------------------------
// close - JDBC API
// In many cases, it is desirable to immediately release a
// Statements's database and JDBC resources instead of waiting for
// this to happen when it is automatically closed; the close
// method provides this immediate release.
//
// Note: A Statement is automatically closed when it is
// garbage collected. When a Statement is closed its current
// ResultSet, if one exists, is also closed.
//------------------------------------------------------------------------
public void close()
throws SQLException
{
// If we have a current result set, close it
if (currentResultSet != null) {
currentResultSet.close();
currentResultSet = null;
}
}
//------------------------------------------------------------------------
// getMaxFieldSize - JDBC API
// The maxFieldSize limit (in bytes) is the maximum amount of data
// returned for any column value; it only applies to BINARY,
// VARBINARY, LONGVARBINARY, CHAR, VARCHAR, and LONGVARCHAR
// columns. If the limit is exceeded, the excess data is silently
// discarded.
//
// Returns the current max column size limit; zero means unlimited
//------------------------------------------------------------------------
public int getMaxFieldSize()
throws SQLException
{
// The SimpleText driver does not have a limit on size
return 0;
}
//------------------------------------------------------------------------
// setMaxFieldSize - JDBC API
// The maxFieldSize limit (in bytes) is set to limit the size of
// data that can be returned for any column value; it only applies
// to BINARY, VARBINARY, LONGVARBINARY, CHAR, VARCHAR, and
// LONGVARCHAR fields. If the limit is exceeded, the excess data
// is silently discarded.
//
// max the new max column size limit; zero means unlimited
//------------------------------------------------------------------------
public void setMaxFieldSize(
int max)
throws SQLException
{
// The SimpleText driver does not allow the maximum field size to
// be set
if (max != 0) {
throw DriverNotCapable();
}
}
//------------------------------------------------------------------------
// getMaxRows - JDBC API
// The maxRows limit is the maximum number of rows that a
// ResultSet can contain. If the limit is exceeded, the excess
// rows are silently dropped.
//
// Returns the current max row limit; zero means unlimited
//------------------------------------------------------------------------
public int getMaxRows()
throws SQLException
{
// The SimpleText driver does not have a limit on the number
// of rows that can be returned
return 0;
}
//------------------------------------------------------------------------
// setMaxRows - JDBC API
// The maxRows limit is set to limit the number of rows that any
// ResultSet can contain. If the limit is exceeded, the excess
// rows are silently dropped.
//
// max the new max rows limit; zero means unlimited
//------------------------------------------------------------------------
public void setMaxRows(
int max)
throws SQLException
{
// The SimpleText driver does not allow the maximum number of rows
// to be set
if (max != 0) {
throw DriverNotCapable();
}
}
//------------------------------------------------------------------------
// setEscapeProcessing - JDBC API
// If escape scanning is on (the default) the driver will do
// escape substitution before sending the SQL to the database.
//
// enable true to enable; false to disable
//------------------------------------------------------------------------
public void setEscapeProcessing(
boolean enable)
throws SQLException
{
// The SimpleText driver does not support escape sequence expansion
if (enable) {
throw DriverNotCapable();
}
}
//------------------------------------------------------------------------
// getQueryTimeout - JDBC API
// The queryTimeout limit is the number of seconds the driver will
// wait for a Statement to execute. If the limit is exceeded a
// SQLException is thrown.
//
// Returns the current query timeout limit in seconds; zero means
// unlimited
//------------------------------------------------------------------------
public int getQueryTimeout()
throws SQLException
{
// The SimpleText driver does not have a query timeout
return 0;
}
//------------------------------------------------------------------------
// setQueryTimeout - JDBC API
// The queryTimeout limit is the number of seconds the driver will
// wait for a Statement to execute. If the limit is exceeded a
// SQLException is thrown.
//
// seconds the new query timeout limit in seconds; zero means unlimited
//------------------------------------------------------------------------
public void setQueryTimeout(
int seconds)
throws SQLException
{
// The SimpleText driver does not support query timeouts
if (seconds != 0) {
throw DriverNotCapable();
}
}
//------------------------------------------------------------------------
// cancel - JDBC API
// Cancel can be used by one thread to cancel a statement that
// is being executed by another thread.
//------------------------------------------------------------------------
public void cancel()
throws SQLException
{
// No-op for the SimpleText driver
}
//------------------------------------------------------------------------
// getWarnings - JDBC API
// The first warning reported by calls on this Statement is
// returned. A Statment's execute methods clear its SQLWarning
// chain. Subsequent Statement warnings will be chained to this
// SQLWarning.
//
// Note: The warning chain is automatically cleared each time
// a statement is (re)executed.
//
// Note: If you are processing a ResultSet then any
// warnings associated with ResultSet reads will be chained on the
// ResultSet object.
//
// Returns the first SQLWarning or null
//------------------------------------------------------------------------
public SQLWarning getWarnings()
throws SQLException
{
return lastWarning;
}
//------------------------------------------------------------------------
// clearWarnings - JDBC API
// After this call getWarnings returns null until a new warning is
// reported for this Statement.
//------------------------------------------------------------------------
public void clearWarnings()
throws SQLException
{
setWarning(null);
}
//------------------------------------------------------------------------
// setWarning
// Sets the given SQLWarning in the warning chain. If null, the
// chain is reset
//------------------------------------------------------------------------
protected void setWarning(
SQLWarning warning)
{
if (warning == null) {
lastWarning = null;
}
else {
SQLWarning chain = lastWarning;
// Find the end of the chain
while (chain.getNextWarning() != null) {
chain = chain.getNextWarning();
}
// We're at the end of the chain. Add the new warning
chain.setNextWarning(warning);
}
}
//------------------------------------------------------------------------
// setCursorName - JDBC API
// setCursorname defines the SQL cursor name that will be used by
// subsequent Statement execute methods. This name can then be
// used in SQL positioned update/delete statements to identify the
// current row in the ResultSet generated by this statement. If
// the database doesn't support positioned update/delete, this
// method is a noop.
//
// Note: By definition, positioned update/delete
// execution must be done by a different Statement than the one
// which generated the ResultSet being used for positioning. Also,
// cursor names must be unique within a Connection.
//
// name the new cursor name.
//------------------------------------------------------------------------
public void setCursorName(
String name)
throws SQLException
{
// The SimpleText driver does not support positioned updates. Per
// the spec, this is a no-op
}
//------------------------------------------------------------------------
// execute - JDBC API
// Execute a SQL statement that may return multiple results.
// Under some (uncommon) situations a single SQL statement may return
// multiple result sets and/or update counts. Normally you can ignore
// this, unless you're executing a stored procedure that you know may
// return multiple results, or unless you're dynamically executing an
// unknown SQL string. The "execute", "getMoreResults", "getResultSet"
// and "getUpdateCount" methods let you navigate through multiple results.
//
// The "execute" method executes a SQL statement and indicates the
// form of the first result. You can then use getResultSet or
// getUpdateCount to retrieve the result, and getMoreResults to
// move to any subsequent result(s).
//
// sql any SQL statement
// Returns true if the first result is a ResultSet; false if it is an
// integer
//------------------------------------------------------------------------
public boolean execute(
String sql)
throws SQLException
{
resultSetColumns = null;
// Convert the SQL statement into native syntax
sql = ownerConnection.nativeSQL(sql);
// Save the SQL statement
sqlStatement = sql;
// First, parse the sql statement into a String array
parsedSQL = ownerConnection.parseSQL(sql);
// Now validate the SQL statement and execute it.
// Returns true if a result set exists.
boolean rc = prepare(false);
return rc;
}
//------------------------------------------------------------------------
// getResultSet - JDBC API
// Returns the current result as a ResultSet. It
// should only be called once per result.
//
// Returns the current result as a ResultSet; null if it is an integer
//------------------------------------------------------------------------
public ResultSet getResultSet()
throws SQLException
{
// If there are no column to be returned, return null
if (resultSetColumns == null) {
return null;
}
SimpleTextResultSet rs = new SimpleTextResultSet();
rs.initialize(this, resultSetCatalog, resultSetTable,
resultSetColumns, resultSetFilter);
// Save our current result set
currentResultSet = rs;
return rs;
}
//------------------------------------------------------------------------
// getUpdateCount - JDBC API
// getUpdateCount returns the current result, which should be an
// integer value. It should only be called once per result.
//
// The only way to tell for sure that the result is an update
// count is to first test to see if it is a ResultSet. If it is
// not a ResultSet it is an update count.
//
// Returns the current result as an integer; zero if it is a ResultSet
//------------------------------------------------------------------------
public int getUpdateCount()
throws SQLException
{
return updateCount;
}
//------------------------------------------------------------------------
// getMoreResults - JDBC API
// getMoreResults moves to a Statement's next result. It returns true if
// this result is a ResultSet. getMoreResults also implicitly
// closes any current ResultSet obtained with getResultSet.
//
// Returns true if the next result is a ResultSet; false if it is an
// integer
//------------------------------------------------------------------------
public boolean getMoreResults()
throws SQLException
{
// The SimpleText driver does not support multiple result sets
throw DriverNotCapable();
}
//------------------------------------------------------------------------
// getStatementType
// Given a parsed SQL statement (in a String array), determine the
// type of sql statement present. If the sql statement is not known,
// an exception is raised
//------------------------------------------------------------------------
public int getStatementType(
String sql[])
throws SQLException
{
int type = 0;
// There are no sql statements with less than 2 words
if (sql.length < 2) {
throw new SQLException("Invalid SQL statement");
}
if (sql[0].equalsIgnoreCase("SELECT")) {
type = SimpleTextDefine.SQL_SELECT;
}
else if (sql[0].equalsIgnoreCase("INSERT")) {
type = SimpleTextDefine.SQL_INSERT;
}
else if (sql[0].equalsIgnoreCase("CREATE")) {
type = SimpleTextDefine.SQL_CREATE;
}
else if (sql[0].equalsIgnoreCase("DROP")) {
type = SimpleTextDefine.SQL_DROP;
}
else {
throw new SQLException("Invalid SQL statement: " + sql[0]);
}
return type;
}
//------------------------------------------------------------------------
// prepare
// Prepare the already parsed SQL statement.
// Returns true if a result set exists.
//------------------------------------------------------------------------
protected boolean prepare(
boolean prepareOnly)
throws SQLException
{
boolean resultSet = false;
// Determine the type of statement present
statementType = getStatementType(parsedSQL);
// Perform action depending upon the SQL statement type
switch (statementType) {
// CREATE statement
case SimpleTextDefine.SQL_CREATE:
// If attempting to prepare a DDL (Data Definition Language)
// statement, raise an exception
if (prepareOnly) {
throw new SQLException("DDL statements cannot be prepared");
}
// Create the table
createTable();
updateCount = 0;
break;
// DROP statement
case SimpleTextDefine.SQL_DROP:
// If attempting to prepare a DDL (Data Definition Language)
// statement, raise an exception
if (prepareOnly) {
throw new SQLException("DDL statements cannot be prepared");
}
// Drop the table
dropTable();
updateCount = 0;
break;
// INSERT statement
case SimpleTextDefine.SQL_INSERT:
// Insert data into the table
insert(prepareOnly);
updateCount = 1;
break;
// SELECT statement
case SimpleTextDefine.SQL_SELECT:
// Select data from the table
select(prepareOnly);
resultSet = true;
updateCount = -1;
break;
default:
throw new SQLException("Unknown SQL statement type: " +
statementType);
}
return resultSet;
}
//------------------------------------------------------------------------
// createTable
// Attempt to create the table from the parsed SQL statement.
//
// Grammar:
//
// create-statement ::= CREATE TABLE table-name
// (column-element [,column-element] ...)
//
// column-element ::= column-identifier data-type
//
//------------------------------------------------------------------------
protected void createTable()
throws SQLException
{
// The minimum SQL statement must have 7 elements:
//
// CREATE TABLE foo (COL VARCHAR)
if (parsedSQL.length < 7) {
throw new SQLException ("Invalid CREATE statement");
}
// The next word must be TABLE; this is the only type of
// CREATE that the SimpleText driver supports
if (!parsedSQL[1].equalsIgnoreCase("TABLE")) {
throw new SQLException("CREATE must be followed by TABLE");
}
// Make sure we are not in read-only mode
if (ownerConnection.isReadOnly()) {
throw new SQLException(
"Unable to CREATE TABLE: connection is read-only");
}
// The next word is the table name. Verify that it does not
// contain any invalid characters
validateName(parsedSQL[2], "table");
// The next word should be an open paren
if (!parsedSQL[3].equals("(")) {
throw new SQLException(
"Invalid CREATE TABLE statement: missing paren '('");
}
// Now we can step through the other parameters. The format should
// be:
//
// ( column type [, column type] ... )
//
// We will build a text line that describes each of the columns.
// This line will be the first line in our simple text file.
//
// Numeric column names start with '#'
// Binary column names start with '@'
// All other names are considered to be varchar
String line = "";
String columnName;
String typeName;
int word = 4;
boolean gotCloseParen = false;
int numCols = 0;
boolean hasBinary = false;
// Keep a Hashtable of all of the column names so we can check
// for duplicates
Hashtable names = new Hashtable();
while ((word < parsedSQL.length) &&
(!gotCloseParen)) {
// Get the column name to create and validate
columnName = parsedSQL[word].toUpperCase();
validateName(columnName, "column");
if (names.get(columnName) != null) {
throw new SQLException("Duplicate column name: " + columnName);
}
names.put(columnName, "");
word++;
// The next column should be the type
if (word == parsedSQL.length) {
throw new SQLException("Missing column type");
}
typeName = parsedSQL[word];
if (numCols > 0) {
line += ",";
}
numCols++;
// Validate the type
if (typeName.equalsIgnoreCase("VARCHAR")) {
line += columnName;
}
else if (typeName.equalsIgnoreCase("NUMBER")) {
line += SimpleTextDefine.COL_TYPE_NUMBER + columnName;
}
else if (typeName.equalsIgnoreCase("BINARY")) {
line += SimpleTextDefine.COL_TYPE_BINARY + columnName;
hasBinary = true;
}
else {
throw new SQLException("Invalid column type: " + typeName);
}
word++;
if (word == parsedSQL.length) {
throw new SQLException("Missing close paren");
}
// The next word must either be a comma, indicating more
// columns, or the closing paren
if (parsedSQL[word].equals(")")) {
gotCloseParen = true;
word++;
break;
}
else if (!parsedSQL[word].equals(",")) {
throw new SQLException("Invalid character near: " +
columnName + " " + typeName);
}
word++;
}
// If we got here and did not find a closing paren, raise an error
if (!gotCloseParen) {
throw new SQLException("Missing close paren");
}
// We could check for extra junk at the end of the statement, but
// we'll just ignore it
// Verify that the file does not already exist
String fileName = parsedSQL[2].toUpperCase();
String fullFile = fileName + SimpleTextDefine.DATA_FILE_EXT;
String fullPath = ownerConnection.getCatalog() + "/" + fullFile;
File f = new File (fullPath);
if (f.exists()) {
throw new SQLException("Table already exists: " + fileName);
}
// Create the table
try {
RandomAccessFile raf = new RandomAccessFile(f, "rw");
// Brand the file
raf.writeBytes(SimpleTextDefine.DATA_FILE_EXT);
// Write the column info
raf.writeBytes(line);
raf.writeBytes("\n");
raf.close();
}
catch (IOException ex) {
throw new SQLException("Error accessing file " + fullPath +
": " + ex.getMessage());
}
// If a binary data type existed, create the binary data file now
fullFile = fileName + SimpleTextDefine.BINARY_FILE_EXT;
fullPath = ownerConnection.getCatalog() + "/" + fullFile;
f = new File (fullPath);
// Create the binary table
try {
RandomAccessFile raf = new RandomAccessFile(f, "rw");
raf.close();
}
catch (IOException ex) {
throw new SQLException("Error accessing file " + fullPath +
": " + ex.getMessage());
}
}
//------------------------------------------------------------------------
// dropTable
// Attempt to drop a table
//
// Grammar:
//
// drop-statement ::= DROP TABLE table-name
//
//------------------------------------------------------------------------
protected void dropTable()
throws SQLException
{
// The SQL statement must have 3 elements:
//
// DROP TABLE table
if (parsedSQL.length != 3) {
throw new SQLException ("Invalid DROP statement");
}
// The next word must be TABLE; this is the only type of
// DROP that the SimpleText driver supports
if (!parsedSQL[1].equalsIgnoreCase("TABLE")) {
throw new SQLException("DROP must be followed by TABLE");
}
// Make sure we are not in read-only mode
if (ownerConnection.isReadOnly()) {
throw new SQLException(
"Unable to DROP TABLE: connection is read-only");
}
// The next word is the table name. Verify that it does not
// contain any invalid characters
validateName(parsedSQL[2], "table");
// Verify that the file exists
String fileName = parsedSQL[2].toUpperCase();
String fullFile = fileName + SimpleTextDefine.DATA_FILE_EXT;
String fullPath = ownerConnection.getCatalog() + "/" + fullFile;
File f = new File (fullPath);
if (!f.exists()) {
throw new SQLException("Table does not exist: " + fileName);
}
// Delete the file
f.delete();
// If a binary data file exists, delete it now
fullFile = fileName + SimpleTextDefine.BINARY_FILE_EXT;
fullPath = ownerConnection.getCatalog() + "/" + fullFile;
f = new File (fullPath);
if (f.exists()) {
f.delete();
}
}
//------------------------------------------------------------------------
// insert
// Attempt to insert data into a table
//
// Grammar:
//
// insert-statement ::= INSERT INTO table-name
// [(column-identifier [,column-identifier]...)]
// VALUES (insert-value [,insert-value]...)
//
//------------------------------------------------------------------------
synchronized protected void insert(
boolean prepareOnly)
throws SQLException
{
// The SQL statement must have at least 7 elements:
//
// INSERT INTO table VALUES (value)
if (parsedSQL.length <= 7) {
throw new SQLException ("Invalid INSERT statement");
}
// The next word must be INTO
if (!parsedSQL[1].equalsIgnoreCase("INTO")) {
throw new SQLException("INSERT must be followed by INTO");
}
// Make sure we are not in read-only mode
if (ownerConnection.isReadOnly()) {
throw new SQLException(
"Unable to INSERT: connection is read-only");
}
// The next word is the table name. Verify that it does not
// contain any invalid characters
String tableName = parsedSQL[2];
validateName(tableName, "table");
// Verify that the file exists. If getColumns returns null,
// the table does not exist
Hashtable columnList = ownerConnection.getColumns(
ownerConnection.getCatalog(), tableName);
if (columnList == null) {
throw new SQLException("Table does not exist: " + tableName);
}
int pos = 3;
Hashtable insertList = null;
Hashtable valueList = null;
int colNo = 1;
SimpleTextColumn column;
SimpleTextColumn column2;
String name;
// If the next word is a paren '(', the column names are being
// specified. Build a list of columns that will have data
// inserted
if (parsedSQL[pos].equals("(")) {
insertList = new Hashtable();
pos++;
if (pos >= parsedSQL.length) {
throw new SQLException ("Invalid INSERT statement");
}
// Build our insert list. Get each comma separated name until
// we read a close paren
pos = buildList(parsedSQL, pos, ")", insertList);
// Make sure at least one column was given
if (insertList.size() == 0) {
throw new SQLException ("No columns given");
}
// Now that we have the insert list, verify each name is in
// our target table and get the type and precision
for (int i = 1; i <= insertList.size(); i++) {
column = (SimpleTextColumn) insertList.get(new Integer(i));
column2 = findColumn(columnList, column.name);
if (column2 == null) {
throw new SQLException("Column does not exist: " +
column.name);
}
column.type = column2.type;
column.precision = column2.precision;
}
// Position to the next word after the closing paren
pos++;
if (pos >= parsedSQL.length) {
throw new SQLException(
"Invalid INSERT statement; missing VALUES clause");
}
}
// The next word is VALUES; no column list was given, so assume
// all columns in the table
else if (parsedSQL[pos].equalsIgnoreCase("VALUES")) {
insertList = new Hashtable();
// Build the insertList with all columns in the table
for (colNo = 1; colNo <= columnList.size(); colNo++) {
column2 = (SimpleTextColumn)columnList.get(new Integer(colNo));
if (column2 == null) {
throw new SQLException("Invalid column number: " + colNo);
}
column = new SimpleTextColumn(column2.name);
column.type = column2.type;
column.precision = column2.precision;
insertList.put(new Integer(colNo), column);
}
}
else {
// Invalid SQL statement
throw new SQLException(
"Invalid INSERT statement, no VALUES clause");
}
// The next word must be VALUES. If there was an insert list,
// we have positioned past it.
if (!parsedSQL[pos].equalsIgnoreCase("VALUES")) {
throw new SQLException(
"Invalid INSERT statement; missing VALUES clause");
}
pos++;
if (pos >= parsedSQL.length) {
throw new SQLException (
"Invalid INSERT statement, missing values");
}
// The next word must be the open paren that starts the values
if (!parsedSQL[pos].equals("(")) {
throw new SQLException (
"Invalid INSERT statement, missing values");
}
pos++;
if (pos >= parsedSQL.length) {
throw new SQLException (
"Invalid INSERT statement, missing values");
}
// Build our value list. Get each comma separated value until
// we read a close paren
valueList = new Hashtable();
pos = buildList(parsedSQL, pos, ")", valueList);
// We could check for junk after the INSERT statement, but we won't
// Verify the the number of insert items matches the number
// of data items
if (insertList.size() != valueList.size()) {
throw new SQLException("Number of values does not equal the number of items in the insert list");
}
// Verify the data is correct
validateData(insertList, valueList, prepareOnly);
// If we are just preparing the statement, exit now
if (prepareOnly) {
return;
}
// Now we can build the line that will get written to the
// simple text file. If there is any binary data, write it first
// so that we know what the offset will be.
String sdfPath = ownerConnection.getCatalog() + "/" + tableName +
SimpleTextDefine.DATA_FILE_EXT;
String sbfPath = ownerConnection.getCatalog() + "/" + tableName +
SimpleTextDefine.BINARY_FILE_EXT;
File sdf = new File(sdfPath);
File sbf = new File(sbfPath);
RandomAccessFile rafsdf = null;
RandomAccessFile rafsbf = null;
if (!sdf.exists()) {
throw new SQLException("Text file does not exist: " + sdfPath);
}
String line = "";
long binaryPos = 0;
for (int i = 1; i <= columnList.size(); i++) {
column2 = (SimpleTextColumn) columnList.get(new Integer(i));
// Separate the data by a comma
if (i > 1) {
line += ",";
}
// If there is no data for this column, skip it
colNo = findColumnNumber(insertList, column2.name);
if (colNo == 0) {
// No data, put in defaults
switch(column2.type) {
case Types.VARCHAR:
line += "''";
break;
case Types.VARBINARY:
line += "-1";
break;
default:
line += "0";
break;
}
continue;
}
column = (SimpleTextColumn) valueList.get(new Integer(colNo));
if (column2.type == Types.VARBINARY) {
if (rafsbf == null) {
if (!sbf.exists()) {
throw new SQLException("Binary file does not exist: "
+ sbfPath);
}
try {
rafsbf = new RandomAccessFile(sbf, "rw");
// Position to the end of file
rafsbf.seek(rafsbf.length());
}
catch (Exception ex) {
throw new SQLException("Unable to access " +
sbfPath + ": " + ex.getMessage());
}
}
try {
// Get the current position
binaryPos = rafsbf.getFilePointer();
// Create a new CommonValue with the hex digits (remove
// the quotes.
CommonValue value = new CommonValue(
column.name.substring(1, column.name.length() - 1));
// Now let CommonValue convert the hex string into
// a byte array
byte b[] = value.getBytes();
// Write the length first
rafsbf.writeInt(b.length);
// Write the data
rafsbf.write(b);
}
catch (Exception ex) {
throw new SQLException("Unable to access " +
sbfPath + " for column " + i +
": " + ex.getMessage());
}
// Put the offset pointer in the line
line += binaryPos;
}
// Else some kind of text data, put directly in the line
else {
line += column.name;
}
}
// If the binary file was opened, close it now
if (rafsbf != null) {
try {
rafsbf.close();
}
catch (Exception ex) {
throw new SQLException("Unable to close " +
sbfPath + ": " + ex.getMessage());
}
}
// Now that we have the data line, write it out to the text
// file
long seekPos;
String msg = "";
try {
msg = "open";
rafsdf = new RandomAccessFile(sdf, "rw");
msg = "get length";
seekPos = rafsdf.length();
// Position to the end of file
msg = "seek";
rafsdf.seek(seekPos);
// Write the data
msg = "write";
rafsdf.writeBytes(line);
rafsdf.writeBytes("\n");
msg = "close";
rafsdf.close();
}
catch (Exception ex) {
ex.printStackTrace();
throw new SQLException("Unable to " + msg + " " +
sdfPath + ": " + ex.getMessage());
}
}
//------------------------------------------------------------------------
// select
// Select data from a table
//
// Grammar:
//
// select-statement ::= SELECT select-list FROM table-name
// [WHERE search-condition]
//
// select-list ::= * | column-identifier [,column-identifier]...
// search-condition ::= column-identifier comparison-operator literal
// comparison-operator ::= < | > | = | <>
//
//------------------------------------------------------------------------
protected void select(
boolean prepareOnly)
throws SQLException
{
// Initialize the filter object
resultSetFilter = null;
// The SQL statement must have at least 4 elements:
//
// SELECT * FROM table
if (parsedSQL.length < 4) {
throw new SQLException ("Invalid SELECT statement");
}
Hashtable selectList = new Hashtable();
int pos = 1;
// Build our select list. Get each comma separated name until
// we read a 'FROM'
pos = buildList(parsedSQL, pos, "FROM", selectList);
// There must be at least one column
if (selectList.size() == 0) {
throw new SQLException("Select list must be specified");
}
// Increment past the 'FROM' word. This is the table name
pos++;
if (pos >= parsedSQL.length) {
throw new SQLException("Missing table name");
}
// The next word is the table name. Verify that it does not
// contain any invalid characters
String tableName = parsedSQL[pos];
validateName(tableName, "table");
// Verify that the file exists. If getColumns returns null,
// the table does not exist
Hashtable columnList = ownerConnection.getColumns(
ownerConnection.getCatalog(), tableName);
if (columnList == null) {
throw new SQLException("Table does not exist: " + tableName);
}
// No go back through the select list and verify that each
// column specified is contained in the table. Also expand
// any * to be all columns
Hashtable validList = new Hashtable();
int validCount = 0;
SimpleTextColumn column;
SimpleTextColumn column2;
for (int i = 1; i <= selectList.size(); i++) {
// Get the next column from the select list
column = (SimpleTextColumn) selectList.get(new Integer(i));
// If it's an *, expand it to all columns in the table
if (column.name.equals("*")) {
for (int j = 1; j <= columnList.size(); j++) {
column2 = (SimpleTextColumn)columnList.get(new Integer(j));
validCount++;
validList.put(new Integer(validCount), column2);
}
}
else {
// Make sure the column exists in the table
column2 = findColumn(columnList, column.name);
if (column2 == null) {
throw new SQLException("Column not found: " + column.name);
}
// Put column on our valid list
validCount++;
validList.put(new Integer(validCount), column2);
}
}
// Now we know the table exists and have a list of valid columns.
// Process the WHERE clause if one exists
pos++;
if (pos < parsedSQL.length) {
// The next word should be WHERE
if (!parsedSQL[pos].equalsIgnoreCase ("WHERE")) {
throw new SQLException("WHERE clause expected");
}
// Create a filter object
resultSetFilter = new SimpleTextFilter();
pos++;
if (pos >= parsedSQL.length) {
throw new SQLException(
"Column name expected after WHERE clause");
}
// The next word is a column name. Make sure it exists in
// the table
resultSetFilter.column = findColumn(columnList, parsedSQL[pos]);
if (resultSetFilter.column == null) {
throw new SQLException("Column not found: " + parsedSQL[pos]);
}
// Make sure the column is searchable
if (!resultSetFilter.column.searchable) {
throw new SQLException(
"Column is not searchable: " + parsedSQL[pos]);
}
pos++;
// The next word is the operator. Some operators may take
// 2 words (i.e <>)
if (pos >= parsedSQL.length) {
throw new SQLException("Operator expected in WHERE clause");
}
if (parsedSQL[pos].equals("=")) {
resultSetFilter.operator = SimpleTextFilter.OP_EQ;
}
else if (parsedSQL[pos].equals("<")) {
resultSetFilter.operator = SimpleTextFilter.OP_LT;
}
else if (parsedSQL[pos].equals(">")) {
resultSetFilter.operator = SimpleTextFilter.OP_GT;
}
else {
throw new SQLException("Invalid operator: " + parsedSQL[pos]);
}
// The next word may be our value, or it may be the second part
// of an operator.
pos++;
if (pos >= parsedSQL.length) {
throw new SQLException("Value expected in WHERE clause");
}
if ((resultSetFilter.operator == SimpleTextFilter.OP_LT) &&
(parsedSQL[pos].equals(">"))) {
resultSetFilter.operator = SimpleTextFilter.OP_NE;
pos++;
if (pos >= parsedSQL.length) {
throw new SQLException("Value expected in WHERE clause");
}
}
// Get the data value and validate
Hashtable whereList = new Hashtable();
Hashtable dataList = new Hashtable();
column = new SimpleTextColumn(parsedSQL[pos]);
whereList.put(new Integer(1), resultSetFilter.column);
dataList.put(new Integer(1), column);
validateData(whereList, dataList, prepareOnly);
String s = parsedSQL[pos];
// validateData could have massaged the data value (such as
// in executing a prepared statement with parameters). Get
// the value back
s = ((SimpleTextColumn) dataList.get(new Integer(1))).name;
// Strip off any quotes
if (s.startsWith("'") &&
s.endsWith("'")) {
s = s.substring(1,s.length() - 1);
}
resultSetFilter.value = new CommonValue(s);
pos++;
// Check for extra junk at the end of the statement
if (pos < parsedSQL.length) {
throw new SQLException(
"Invalid characters following WHERE clause");
}
}
// Set the catalog name, table name, and column Hashtable for
// the result set
resultSetCatalog = ownerConnection.getCatalog();
resultSetTable = tableName;
resultSetColumns = validList;
}
//------------------------------------------------------------------------
// findColumn
// Given a SimpleTextColumn Hashtable and a column name, return
// the SimpleTextColumn that matches. Null if no match. The column
// numbers are 1-based
//------------------------------------------------------------------------
protected SimpleTextColumn findColumn(
Hashtable list,
String name)
{
SimpleTextColumn column;
for (int i = 1; i <= list.size(); i++) {
column = (SimpleTextColumn) list.get(new Integer(i));
if (column != null) {
if (column.name.equalsIgnoreCase(name)) {
return column;
}
}
}
return null;
}
//------------------------------------------------------------------------
// findColumnNumber
// Given a SimpleTextColumn Hashtable and a column name, return
// the column number that matches. 0 if no match. The column
// numbers are 1-based
//------------------------------------------------------------------------
protected int findColumnNumber(
Hashtable list,
String name)
{
SimpleTextColumn column;
for (int i = 1; i <= list.size(); i++) {
column = (SimpleTextColumn) list.get(new Integer(i));
if (column != null) {
if (column.name.equalsIgnoreCase(name)) {
return i;
}
}
}
return 0;
}
//------------------------------------------------------------------------
// buildList
// Given a parsed SQL statement, the current position, and the ending
// word, build a list of the comma separated words from the SQL
// statement. This is used for the insert column list, insert values,
// and select list. Returns the new position in the parsed SQL
//------------------------------------------------------------------------
public int buildList(
String sql[],
int pos,
String endWord,
Hashtable list)
throws SQLException
{
SimpleTextColumn column;
boolean done = false;
String name;
int colNo = 1;
// Loop while more data is present
while (!done) {
// Get the next column
name = sql[pos];
column = new SimpleTextColumn(name);
list.put(new Integer(colNo), column);
colNo++;
pos++;
if (pos >= sql.length) {
if (endWord.length() > 0) {
throw new SQLException (
"Invalid statement after " + name);
}
else {
done = true;
break;
}
}
// If the next word is not a comma, it must be our ending
// word
if (!sql[pos].equals(",")) {
// Found the ending word? exit the loop
if (sql[pos].equalsIgnoreCase(endWord)) {
done = true;
break;
}
if (endWord.length() == 0) {
throw new SQLException("Invalid data format");
}
throw new SQLException (
"Invalid statement after " + name);
}
pos++;
if (pos >= sql.length) {
if (endWord.length() > 0) {
throw new SQLException (
"Invalid statement after " + name);
}
else {
done = true;
break;
}
}
}
return pos;
}
//------------------------------------------------------------------------
// validateData
// Given an insert list and a data list, verify the each data element
// is proper for the given type and precision
//------------------------------------------------------------------------
protected void validateData(
Hashtable insertList,
Hashtable dataList,
boolean prepareOnly)
throws SQLException
{
SimpleTextColumn insert;
SimpleTextColumn data;
int precision = 0;
int paramNum = 0;
// Init number of parameters if we are preparing
if (prepareOnly) {
paramCount = 0;
}
for (int i = 1; i <= insertList.size(); i++) {
insert = (SimpleTextColumn) insertList.get(new Integer(i));
data = (SimpleTextColumn) dataList.get(new Integer(i));
// If a parameter marker is found, either continue to the
// next list item because we are preparing, or replace it
// with a bound parameter value
if (data.name.equals("?")) {
if (prepareOnly) {
// Increment number of parameter markers
paramCount++;
continue;
}
// Increment current parameter number
paramNum++;
// Get String value for the bound parameter from the
// boundParams Hashtable. If it is not found, throw
// an exception indicating that not all of the parameters
// have been set.
if (boundParams != null) {
String s = (String) boundParams.get(new Integer(paramNum));
if (s == null) {
throw new SQLException(
"Not all parameters have been set");
}
// Set the value into the SimpleTextColumn entry
// If the data is a string or binary type, enclose it
// in quotes
switch(insert.type) {
case Types.VARCHAR:
case Types.VARBINARY:
data.name = "'" + s + "'";
break;
default:
data.name = s;
break;
}
}
}
switch(insert.type) {
case Types.VARCHAR:
if (!data.name.startsWith("'") ||
(data.name.length() < 2) ||
!data.name.endsWith("'")) {
throw new SQLException(
"String data must be enclosed in single quotes: " +
data.name);
}
precision = data.name.length() - 2;
break;
case Types.INTEGER:
try {
Integer.valueOf(data.name);
}
catch (Exception ex) {
throw new SQLException("Invalid numeric data: " +
data.name);
}
precision = data.name.length();
break;
case Types.BINARY:
if (!data.name.startsWith("'") ||
(data.name.length() < 2) ||
!data.name.endsWith("'")) {
throw new SQLException(
"Binary data must be enclosed in single quotes: " +
data.name);
}
if ((data.name.length() % 2) != 0) {
throw new SQLException(
"Binary data must have even number of hex digits:" +
data.name);
}
precision = (data.name.length() - 2) / 2;
break;
}
if (precision > insert.precision) {
throw new SQLException("Invalid data precision for " +
insert.name);
}
}
}
//------------------------------------------------------------------------
// validateName
// Verify that the given name does not contain any invalid characters.
// This will be used for both table names and column names
//------------------------------------------------------------------------
protected void validateName(
String name,
String type)
throws SQLException
{
// Invalid characters other than a-z, 0-9, and A-Z
String invalid = "@#./\\()";
char c;
int j;
for (int i = 0; i < name.length(); i++) {
c = name.charAt(i);
// If it's not an alpha numeric or numeric character,
// check the list of invalid characters
if (!((c >= 'a') && (c <= 'z')) &&
!((c >= '0') && (c <= '9')) &&
!((c >= 'A') && (c <= 'Z'))) {
for (j = 0; j < invalid.length(); j++) {
if (c == invalid.charAt(j)) {
throw new SQLException("Invalid " + type + " name: " +
name);
}
}
}
}
}
//------------------------------------------------------------------------
// getConnection
// Returns the owner connection object
//------------------------------------------------------------------------
public SimpleTextIConnection getConnection()
{
return ownerConnection;
}
// Owning connection object
protected SimpleTextIConnection ownerConnection;
// SQLWarning chain
protected SQLWarning lastWarning;
// The current SQL statement
protected String sqlStatement;
// The String array of parsed SQL words
protected String parsedSQL[];
// The current SQL statement type (i.e. SQL_SELECT, SQL_CREATE, etc.)
protected int statementType;
// Update count for the last statement that executed
protected int updateCount;
// Attributes used for creating a result set
String resultSetCatalog;
String resultSetTable;
Hashtable resultSetColumns;
// If a filter exists for a select statement, a SimpleTextFilter object
// will be created
SimpleTextFilter resultSetFilter;
// Our current result set
ResultSet currentResultSet;
// A Hashtable for each bound parameter. Only valid for PreparedStatements
Hashtable boundParams;
// The count of parameter markers. Only valid for PreparedStatements
int paramCount;
}
|