There are a number of occasions where the database server will return additional information about the query just executed, whether it's error messages or database-specific data types in the result set; from SQLExplorer 3.5 the DatabaseProduct interface allows you to add support for these proprietary features into your implementation. Specifically, implementing a DatabaseProduct derived class allows you to:
This stage is optional - if you do not provide an implementation then a sensible default will be used but if your database supports stored procedures you should seriously consider implementing error message retrieval/parsing.
The rest of this page serves as a walkthrough for implementing your own DatabaseProduct, and is based on the implementation for the Oracle database. The default implementation (used when there is no DatabaseProduct for the database) is net.sourceforge.dbproduct.DefaultDatabaseProduct.
SQLExplorer uses reflection to locate a DatabaseProduct for your database, based on the database driver alias (I.E. the part of the database URL which identifies which java.sql.Driver to load - for Oracle this is "oracle", for MySQL it is "mysql", etc). This is used to load a class called "net.sourceforge.sqlexplorer.aliasname.dbproduct.DatabaseProduct" must provide a public static method called getProductInstance() which returns a singleton instance of net.sourceforge.sqlexplorer.dbproduct.DatabaseProduct.
For example:
private static DatabaseProduct s_instance = null;
public static DatabaseProduct getProductInstance() {
if (s_instance == null)
s_instance = new DatabaseProduct();
return s_instance;
}
The first and simplest methods to be implemented are getDriver() and createDataset(); getDriver() simply loads your java.sql.Driver class and returns an instance of it. Because of the way Eclipse loads classes, it is very important that your driver is loaded using the ClassLoader of DatabaseProduct and not the default or you will get ClassNotFound exceptions if you use any proprietary classes provided by your database manufacturer. The following implementation should be used by all DatabaseProduct implementations:
public Driver getDriver(ISQLDriver driver) throws ClassNotFoundException {
try {
ClassLoader loader = new SQLDriverClassLoader(getClass().getClassLoader(), driver);
Class driverCls = loader.loadClass(driver.getDriverClassName());
return (Driver)driverCls.newInstance();
} catch(MalformedURLException e) {
throw new ClassNotFoundException(e.getMessage(), e);
} catch(InstantiationException e) {
throw new ClassNotFoundException(e.getMessage(), e);
} catch(IllegalAccessException e) {
throw new ClassNotFoundException(e.getMessage(), e);
}
}
the createDataSet() method is used to instantiate a DataSet object for a given ResultSet; you only need to implement a DataSet-derived class if you want to add support for custom data types, otherwise simply instantiate the DataSet class and return it. The default implementation simply uses DataSet:
public DataSet createDataSet(ISQLAlias alias, ResultSet resultSet) throws SQLException {
return new DataSet(null, resultSet, null);
}
Error and status messages are returned from the database server in a number of ways, and there is a method in DatabaseProduct for each:
All three methods return a java.util.Collection of SQLEditor.Message objects, each of which is used as a single line in the Messages tab.
getServerMessages() is expected to return status messages from the server; in Oracle, that means the result of the general-purpose DBMS_OUPUT logging package, but if your database does not have a similar feature you can just return null.
getErrorMessages(SQLConnection connection, SQLException e, int lineNoOffset) is used when a SQLException has been raised and should examine the SQLException to locate and extract any line numbers. Where the exception contains multiple error messages you should split it into separate SQLEditor.Messages, one for each error. At the very least, you must return one SQLEditor.Message or the Messages tab will remain empty. Finally, because the error messages returned by the server will be relative to the start of the query it received but the query may be several lines down in the original SQL text (I.E. the user has a script with several queries and the database thinks each query starts on line 1) you must adjust the line numbers by adding lineNoOffset. For example:
public Collection<Message> getErrorMessages(SQLConnection connection, SQLException e, int lineNoOffset) throws SQLException {
LinkedList<SQLEditor.Message> messages = new LinkedList<SQLEditor.Message>();
//
// Parse the SQLException here
//
// Create the message and return
messages.add(new SQLEditor.Message(false, lineNo + lineNoOffset, charNo, text));
return;
}
getErrorMessages(SQLConnection, Query) is used when a query has been successfully executed but if it's a stored procedure it may have compilation errors; this method should return them.
In order to execute a SQL script (whether ANSI-SQL or a stored procedure) against a database SQLExplorer has to be able to distinguish where one SQL statement ends and another begins. While this is fairly straightforward for standard SQL statements because a simple character delimiter (usually a semi-colon) is used to separate the statements in a natural manner, stored procedures are more complicated because they typically include semi-colons and there is no explicit end without using additional separator characters. The basic parser included in SQLExplorer does just this - it looks for special separator characters.
The method getQueryParser() is called to return an instance of QueryParser for a given SQL string; the QueryParser must be able to separate the SQL text into multiple queries. The simplest implementation of this is BasicQueryParser - this is the parser used before v3.5, and works by using separator characters.
However, SQLExplorer includes support for more complicated parsers which read the SQL and interpret the grammar to determine where a query ends, which makes for a much more natural programming experience. This is not as complicated as it first sounds; firstly, the base class AbstractQueryParser and it's counterpart Tokenizer provide extensive support for easily reading and processing the query, and secondly it is not necessary to write a full grammar of the underlying database.
AbstractQueryParser uses an instance of Tokenizer to split the text into individual Tokens; each token is either a word, a number, a comment, or a string. You access the tokens by repeatedly calling nextToken() until it returns null, but you can also "unget" the tokens, look ahead at the upcoming tokens, and look back the at tokens you've previously seen (or, up to last 5 anyway) - this look ahead/look behind approach makes it really easy to process a grammar, especially when you're only implementing a small part of the real grammar used by the database. The principal methods provided by AbstractQueryParser are:
Firstly, derive a class from AbstractQueryParser and implement the parseQueries() method; parseQueries() should use the methods listed above to locate the edges of each query and perform the separation, calling addQuery() to store the individual Query.
Parsing a full grammar for your database platform would be a complex and difficult process, not to mention the fact that new releases of the database could make your parser incompatible - but you should note that it is not necessary to parse an entire grammar. Generally speaking, all you have to do is a) detect the start of a stored procedure, and b) count begin/end pairs in the code until you have matched every "end" to a "begin". The only gotcha here is that the "end" command may be used for more than just pairing with "begin"; for example, Oracle has "BEGIN...END" and "IF...THEN...END IF", so you have to count the "IF" etc as an implicit begin. Don't stop parsing immediately after the begin/end count reaches zero either - you should do it at the first semi-colon after the end, or the at the end of the Tokens.
Have a look at the net.sourceforge.sqlexplorer.oracle.dbproduct.OracleQueryParser for a working example - you should find it fairly self-explanitory but if you need any help please post in the Developer forum and I'll provide any help I can.
There is a test case project under the SQL Explorer CVS tree with a module called "net.sourceforge.sqlexplorer.test"; this there are two test case applications, one for the Tokenizer (TokenizerTest) and another for the QueryParsers (QueryTest). QueryTest is hard coded to test the OracleQueryParser at the moment - just edit it to put your own one in.
The test apps look for files called "tok_test*.xml" or "qry_test*.xml" which contain test data and expected results; see the comments in the .java files for details.