DataCaster SQL Guide

DataCaster follows the SQL standard closely in the SQL implementation provided. Not all features in SQL are implemented, but most of the features commonly used are supported. DataCaster provides full coverage of the SQL 92 entry-level, and a good fraction of the SQL-92 transitional and intermediate level features. Please see the DataCaster SQL-92 test results for more details on the SQL standard support in DataCaster.

This guide does not provide any introductory or tutorial material on SQL. A number of excellent SQL guides are available on the web, in textbooks, and other sources. Please refer to one of those sources to learn how to use SQL.

This guide provides information on how to use SQL in DataCaster, and discuss some specific SQL features, particularly features that provide additional capabilities not found in the standard, or features that may differ from other databases users may be familar with. The following assumes a basic knowledge of SQL and relational databases.

Using SQL with DataCaster

SQL can be used with DataCaster in a number of ways.

• JDBC
• User API
• WebAdmin SQL interface.
• Console
• Jython scripts
• Run script command line tool
• Execute statement command line tool

For Java applications, the most common use of SQL will be via the JDBC interface. In a few cases, applications may use the Java User API provided by DataCaster, particularly when combined with other usage of the User API.

Ad-hoc queries can be performed by users and administrators using any of the other ways of using SQL with DataCaster.

Please see the SQL statement reference for more information on the list of statements and the syntax for each SQL statement.

SQL Scripts

DataCaster enables running a set of SQL statements as an SQL script. However, support for SQL scripts is limited, with no control flow support. For users needing proper scripting with control flow, using Jython with the Java User or Admin API is the best option available now.

SQL scripts in DataCaster are primarily a series of SQL statements executed in sequence. User variables can be used to pass values between statements, as exlained below.

User variables

SQL scripts can use the SET statement to set user variables that can be used in subsequent statements. This set applies to expressions and SELECT queries, and has the form

SET @INSERTED_ID = LAST_INSERT_ID(); 

which in this case is used to get the IDENTITY values inserted in a prior INSERT statement. This @INSERTED_ID can be used in subsequent statements as a variable, as in the following:

SELECT * FROM mytable where ID = @INSERTED_ID 

A handy use of this feature is in using the results of prior queries in subsequent statements.

SET @firstresults = SELECT * FROM mytable where id > 3 

The table @firstresults can then be used in subsequent queries in the same session.

SQL Statement Extensions

The following are the extensions to standard SQL statements provided with DataCaster.

LIMIT Clause

SQL queries can be used with a LIMIT clause at the end of the statement to provide a limit and offset capability for the results of any SQL query. The syntax is

LIMIT [offset] max_tuples

where offset determines how many tuples to skip before returning the first tuple, and max_tuples determines the maximum number of tuples returned.

CREATE VIEW Options

A key feature of dataCaster is the ability to define materialized views from remote databases, which can be kept refreshed by periodic polling of the remote database. DataCaster supports extensions to the CREATE VIEW statement to define such views. Please see the CREATE VIEW page for the syntax.

Additional SQL Statements

A number of additional SQL statements not specified in the standard are provided with DataCaster.

SET User Variable

This statement is used to set session variables for use is subsequent SQL statements. This may be as part of an SQL script or part os an interactive session in the command-line console.

SET TEMPLATE_PARAMETER

This statement is used to set template parameter values for a session. These tempate parameters allow changing database objects based on the configured values. For example, the transaction timeout template parameter can be set to control the timeout for the next transaction created in this session.

TEXTSEARCH Statements

The SET TEXTSEARCH statement is used to add text search capability to a table. DataCaster provides a Lucene-based implementation of Text Search that can be enabled for any table in the database. Apache Lucene is a widely used Apache search library ( see http://lucene.apache.org). The Lucene-based implementation of search provided in dataCore supports adding search capability to one or more columns of a table in the database. Once search is enabled, any insert, update and delete will result in the search columns being indexed for later text search queries.

Use the SQL SELECT statement, or the API to perform search queries. The SQL SELECT statements use a new TEXTSEARCH condition to search text. The API search function is provided via the Table interface.

RENAME TABLE

Rename Table is used to rename an existing table name within a schema. Optionally columns can also be renamed along with table.

Configurable and Schema Functions

DataCaster allows adding your own SQL functions that can be invoked in SQL statements. These custom functions can be defined in two ways.

• Configured Functions These functions are defined in an XML file and are available to all users in all databases.
• Schema Functions These functions can be added to, or dropped from, any schema at any time.

These functions are called in SQL statements and each function invokes a pre-defined specific Java method when called.

Configured System-wide Functions

DataCaster supports configurable functions via the functions.xml config file in the db directory. This allows specifying static or non-static Java methods be invoked when SQL functions are used in SQL statements. These functions are available in all databases to all users.

DataCaster has very few built-in functions based on the SQL standard. Most functions available in DataCaster are defined via the functions.xml file. This allows an installation to easily add or customize the functions available to users.

The functions.xml conf file specifies the list of configured Functions, each of which needs a FunctionClass instance to implement the function. The supported attributes of Function are:

1. name: Function name used to specify the function.
2. FunctionClass: The class name of the Function class to be used, which must have a no-arg constructor if the method used is not static.

The FunctionProperty tags in the configuration of each function are as follows:

• MethodName: Required parameter for the method name to be used.
• ReturnType: Required parameter for the return type of the function.

For each function a set of zero or more Argument elements are specified. The Argument element has the following attributes

• name: The name of the argument, which is useful only to store the data in the system catalogs and hence must be unique across all Arguments in a method.
• type: The SQL data type of the argument.

Sample XML for a Function

The following is a sample of the XML for a function called MYMIN that call java.lang.Math.min as detailed below.

<Functions>
  <Function name="MYMIN" FunctionClass="java.lang.Math">
    <FunctionProperty name="MethodName" value="min" />
    <FunctionProperty name="ReturnType" value="INTEGER" />
    <Argument name="arg1" Type="INT">
        <ArgumentProperty name="Direction" value="IN" />
    </Argument>
    <Argument name="arg2" Type="INT">
    </Argument>
  </Function>
</Functions>

Schema Functions

DataCaster also allows schema functions in a similar manner. Schema functions can be added to a schema and are recorded in the system catalogs. These are also Java methods invoked when SQL functions are used in statements. The key difference is that they are attached to a schema and may be added and removed at any time.

Schema functions are cached in the schema and the Java method class is instantiated each time the function is called if the method is not static. If the method is static the Java method class is not instantiated.

Creating Schema Functions

SQL Functions can be created (i.e. defined for use in SQL statements) via the API or using the CREATE FUNCTION SQL statement SQL statements.

Using the API, the Schema method createFunction() is used to create a shcema method.

With SQL, the

CREATE FUNCTION

statement is used to create a customer function.

create FUNCTION mycos angle DOUBLE RETURNS DOUBLE 'java.lang.Math' 'cos'

Using Schema Functions

Once schema functions are defined they are used in the same manner as all other SQL functions, and used within SQL statements.

Dropping Schema Functions

Schema functions can be dropped using the DataCaster API, or using SQL statements. The schema method dropRoutine() us used to drop a schema function when it is no longeer required.

Using SQL, statement DROP ROUTINE is used to drop a previously added schema function.

Configured and Schema Stored Procedures

DataCaster allows adding your own SQL procedures that can be invoked in SQL statements. These custom procedures can be defined in two ways.

• Configured Procedures These procedures are defined in an XML file and are available to all users in all databases.
• Schema Procedures These procedures can be added to, or dropped from, any schema at any time.

These procedures are called in SQL statements and each procedure invokes a pre-defined specific Java method when called.

Configured System-wide Procedures

DataCaster supports configurable procedures via the procedures.xml config file in the db directory. This allows specifying static or non-static Java methods be invoked when SQL procedures are used in SQL statements. These procedures are available in all databases to all users.

DataCaster has very few built-in procedures based on the SQL standard. Most procedures available in DataCaster are defined via the procedures.xml file. This allows an installation to easily add or customize the procedures available to users.

The procedures.xml conf file specifies the list of configured Procedures, each of which needs a ProcedureClass instance to implement the procedure. The supported attributes of Procedure are:

1. name: Procedure name used to specify the procedure.
2. ProcedureClass: The class name of the Procedure class to be used, which must have a no-arg constructor if the method used is not static.

The ProcedureProperty tags in the configuration of each procedure are as follows:

• MethodName: Required parameter for the method name to be used.
• ReturnType: Required parameter for the return type of the procedure.

For each procedure a set of zero or more Argument elements are specified. The Argument element has the following attributes

• name: The name of the argument, which is useful only to store the data in the system catalogs and hence must be unique across all Arguments in a method.
• type: The SQL data type of the argument.

Sample XML for a Procedure

The following is a sample of the XML for a procedure called MYMIN that call java.lang.Math.min as detailed below.

<Procedures>
  <Procedure name="MYMIN" ProcedureClass="java.lang.Math">
    <ProcedureProperty name="MethodName" value="min" />
    <ProcedureProperty name="ReturnType" value="INTEGER" />
    <Argument name="arg1" Type="INT">
        <ArgumentProperty name="Direction" value="IN" />
    </Argument>
    <Argument name="arg2" Type="INT">
    </Argument>
  </Procedure>
</Procedures>

Schema Procedures

DataCaster also allows schema procedures in a similar manner. Schema procedures can be added to a schema and are recorded in the system catalogs. These are also Java methods invoked when SQL procedures are used in statements. The key difference is that they are attached to a schema and may be added and removed at any time.

Schema procedures are cached in the schema and the Java method class is instantiated each time the procedure is called if the method is not static. If the method is static the Java method class is not instantiated.

Creating Schema Procedures

SQL Procedures can be created (i.e. defined for use in SQL statements) via the API or using the CREATE PROCEDURE SQL statement SQL statements.

Using the API, the Schema method createProcedure() is used to create a shcema method.

With SQL, the

CREATE PROCEDURE

statement is used to create a customer procedure.

create PROCEDURE mycos angle DOUBLE RETURNS DOUBLE 'java.lang.Math' 'cos'

Using Schema Procedures

Once schema procedures are defined they are used in the same manner as all other SQL procedures, and used within SQL statements.

Droping Schema Procedures

Schema procedures can be dropped using the DataCaster API, or using SQL statements. The schema method dropRoutine() us used to drop a schema procedure when it is no longeer required.

Using SQL, statement DROP ROUTINE is used to drop a previously added schema procedure.