Overview

Database Drivers

Database access is performed using JDBC. Each different database requires a specific Java database driver. It is simplest to use native Java drivers (Type 4). These are generally provided by the database vendor or by third-parties. The JARs for the driver must be present in the JEQL_HOME/lib directory, so that they are autoloaded when JEQL starts up. The Java class that implements the JDBC database driver is specified in the driver: command parameter.

Datatypes

JEQL maps standard JDBC-supported database datatypes to and from the standard JEQL internal types.

Geometry is not a standard JDBC type, and is fairly non-standard across databases. Hence it must be handled specially, by using conversion functions in JEQL and/or in the DB query. Geometry values can often be read as WKT or hex-encoded WKB. It is often possible to write geometry from JEQL to a database by providing it as WKT or hex-encoded WKB, and using a suitable conversion function on the database side in the INSERT statement.

Product-specific Information

Information on how to provide product-specific for various database products is provided on this page. It provides a reference and examples for specifying arguments for the driver:, url: and sql: parameters, and advice on how to map Geometry types into JEQL.

Common Command Parameters

Database commands require that the database connection parameters be specified. Database access is performed using JDBC, so connection parameters are in standard JDBC format. The parameters are common across all database commands. The following parameters may be supplied:
driver: String [Input]
(Required) The Java class name for the database driver (as specified to JDBC).

Example:
driver: "org.h2.Driver"

url: String [Input]
(Required) The JDBC URL specifying the database host and instance. Each database product defines the URL format to use for it.

Example:
url: "jdbc:h2:tcp://localhost/~/test" url: "jdbc:sqlite:sample.db"

user: String [Input]
(Optional) The username to connect under.
password: String [Input]
(Optional) The password to use to connect with.

DbReader

Reads a result set specified by a query from a database into a JEQL table.

Parameters

(anonymous) Table [Output]
The output table containing the rows read from the database. The table is read in streaming mode by default.
sql: String [Input]
The query to use to produce the output result set.
table: String [Input]
The table name to read from. The command uses a query with the standard syntax "SELECT * FROM table". If this is not supported by the database being accessed, the sql: parameter must be used instead.
fetchSize: Integer [Input]
The fetch size to use for querying. Default is 1000.

DbWriter

Writes a JEQL table to a database table.

Parameters

(anonymous) Table [Input]
A JEQL table to be written to the database.
table: String [Input]
The name of the database table to insert the rows into. The column names used are the column names of the supplied JEQL table.
values: String [Input]
Specifies the contents of the VALUES clause for the SQL INSERT statement which will be excuted for each row in the input table. The clause must contain "?" parameters which will be populated by the columns of each written row. Database functions can be used in this clause to transform input values before they are written to the destination table.

If not specified, "?,?,?,..." is used.

Example:
values: "?, ?, ?"
values: "?, ?, ST_GeometryFromText(?, 3005)"

If values: is specified, table: must also be provided.

sql: String [Input]
Fully specifies the parameterized SQL INSERT statement which will be excuted for each row in the input table. This is useful if the table name needs to be qualified with a schema, or are different from the JEQL column names. The statement's VALUES clause must contain "?" parameters which will be populated by the columns of each written row. Database functions can be used in this clause to transform input values before they are written to the destination table.

Example:
sql: "INSERT INTO test (id, name, value) VALUES (?, ?, ?)"
sql: "INSERT INTO tbl-name (id, name, geom) VALUES (?, ?, ST_GeometryFromText(?, 3005) ) "

batchSize: Integer [Input]
The number of statements to execute in a single batch. Default is 1. Choosing a larger value such as 100 can often improve performance, by reducing the number of requests to the database server.
commitSize: Integer [Input]
The number of statements to execute between each commit. Values can be:
  • < 0 : rollback at end of execution (useful for testing)
  • 0 : commit at end of execution only. This prevents any updates if an error is encountered.
  • 1 : autocommit after every statement
  • n > 1: commit after every n statements
Default value is 1 (autocommit). If batchSize is specified, the commit size will be rounded up to a multiple of the batch size.

DbExec

Executes a DDL command on a database. Example uses are creating, modifying or deleting database objects such as tables or views.

Parameters

sql: String [Input]
The SQL statement to execute.

DbMetadata

Reports metadata about a database. Useful to list schemas and tables which are available from a given database connection.

Parameters

(anonymous) Table [Output]
A table containing the metadata rows read from the database. The table schema is determined by the type of object queried.

I columnPattern: String
I columns: boolean
I driver: String
I indexes: boolean
I object: String
I password: String
I procs: boolean
I schemaPattern: String
I tablePattern: String
I tables: boolean

Example of Usage

The following script demonstrates executing DDL and queries against a SQLite database. 

dbClass = "org.sqlite.JDBC";
dbUrl = "jdbc:sqlite:sample.db";

DbExec driver: dbClass url: dbUrl
	sql:
$"CREATE TABLE TEST
(ID INTEGER NOT NULL,
NAME VARCHAR(25)
)
";

tin = select
	i ID,
	"first_" + i + "_" + Date.now() NAME
		from Generate.sequence(1, 10);

DbWriter tin   driver: dbClass	url: dbUrl
	table: "TEST"
	batchSize: 2	commitSize: 0;

DbReader tcnt driver: dbClass url: dbUrl
	sql: "SELECT count(*) FROM TEST;" ;

Print "# rows in table is " + Val(tcnt);

DbReader tout driver: dbClass url: dbUrl
	sql: "SELECT * FROM TEST;" ;

Print tout;

DbExec 	driver: dbClass	url: dbUrl
	sql: "DROP TABLE TEST;" ;