JSSP["JavaScript Server Pages"] = keeping.simple.things.simple;

JSSQL Description and Reference

Home   Downloads   JSSP Docs   JSSQL Docs   Dervish Docs   License   Examples   Feedback

JSSQL is a language extension for JavaScript. It is a part of the JSSP (JavaScript Server Pages) project.

The current version of JSSP is 0.2beta. This version is considered fairly feature-complete. The functionality described here is not expected to change much any more, though more features will be added.

JSSQL works with JDBC (Java DataBase Connectivity) version 3.0. This is the JDBC version that comes with J2SE 5.0. Other JDBC versions will be supported in the future.

This document describes how to use JSSQL on JSSP pages. A documentation about how to use JSSQL in a standalone environment will be available soon.

Any comments and bug reports are highly appreciated by leo_meyer@users.sourceforge.net!

Bug reports, feature requests and forum posts can be submitted on the JSSP Project Page.

Note: This document uses Google's code prettifier. In Internet Explorer you may get warning messages about active content. You can safely allow active content or simply ignore these messages.

Table of Contents


General
Example
JSSQL Reference
Database connections
Defining data sources
Creating connections
Implicit connections
When implicit connections can't be used
Explicit connections
Closing connections
Connection localization
Duplicating explicit connections (deprecated)
SQL Statements
Embedded SQL rules
Creating statements
Dynamic statements
Named placeholders
Indexed placeholders
Variable resolution
Placeholder type conversion
Cast directives
Conversion errors
Statement Type Map
Executing statements
Result Sets
Retrieving column objects
Arrays of column objects
Column objects
Localization
locale
Time zones
Output formatting
Working with unformatted values
ResultSet Type Map
Result set meta data
Column meta data
Using comments in embedded SQL
Supported SQL keywords
Using proprietary commands
Footnotes
 

General

JSSQL is a language extension for JavaScript that allows you to embed SQL statements in JavaScript code. The SQL statements are syntax-checked at compile time.

Additionally, the objects you use for the database access (connections, statements, result sets) contain convenience access methods for columns, settings and meta data.

JSSQL relies on the Java DataBase Connectivity package (JDBC). For a description of JDBC and how it is used see the Sun JDBC tutorial.

Some advantages of JSSQL over traditional JDBC with Java are:

The goal of JSSQL is to remove the hassle of database programming as much as possible, allowing you to concentrate on the "business logic".

JSSQL is a part of the JSSP (JavaScript Server Pages) project. For more information about how to use it in JSSP web applications, see the JSSP Description and Reference, section Defining data sources.

Example

<html>
<body>
<%
try {
	var stmt = datasource.SELECT * FROM Employees;
	var rset = stmt.run(); 

	while (rset.next()) {
		out.println("Name: " + rset.Name + ", Age: " + rset.Age + "<br>");
	}

} catch (e) {
	out.println("Error: " + e);
}
%>
</body>
</html>

This example does a SELECT on the table Employees of the predefined data source datasource. It then outputs the values of columns Name and Age for each record that has been found.

Note that closing allocated resources is not necessary. It is not even recommended as JSSP will handle it automatically.

JSSQL Reference

Database connections

Defining data sources

When using JSSQL on a JSSP page it is recommended that you define the data sources in the jssp.xml configuration file as described in Defining data sources in the JSSP configuration file.

It is almost always better to use connection pools as data sources because it will greatly improve performance. The section Defining data sources contains more information about connection pools.

Creating connections

Suppose you have defined a data source called "datasource". You can direcly create a SQL statement on this data source:

var stmt = datasource.SELECT * FROM Employees;

This creates a so-called implicit connection to the database.

Implicit connections

Implicit connections are created unique to the scope you are using them in. That means that you can do the following:

// a function that uses an implicit connection in its own scope
function selectRow(id) {
	var stmt = datasource.SELECT * FROM Employees WHERE ID = ?id?;
	stmt.id = id;
	var rset = stmt.run();
	while (rset.next()) {
		// do something with the row
	}
}

// the calling scope uses a different implicit connection
var stmt = datasource.SELECT ID FROM Employees;
var rset = stmt.run();
while (rset.next()) {
	selectRow(rset.ID);
}

This works because the function selectRow has its own scope, and implicit connections created in its scope do not affect the connections of other scopes.

Implicit connections are not directly visible to you as a programmer. Having them is a convenience because all you need to deal with are data sources and SQL statements.

When implicit connections can't be used

It is not possible to use dynamically created SQL statements with implicit connections. You will have to use an explicit connection for that.

Creating implicit connections is a performance compromise. It avoids the creation of too many simultaneous connections. The downside is that you cannot nest connections in the same scope:

var stmt = datasource.SELECT ID FROM Employees;
var rset = stmt.run();
while (rset.next()) {
	var stmt2 = datasource.SELECT * FROM Employees WHERE ID = ?id?;
	stmt2.id = rset.ID;		// <-- this fails!
	var rset2 = stmt2.run();
	while (rset2.next()) {
		// do something with the rows
	}
}

Reading from the first result set rset fails because opening a second implicit connection in the same scope (the second SELECT) re-uses the already opened implicit connection, closing its associated statements and result sets. Accessing closed result sets will throw an error with most JDBC drivers, so watch out for situations similar to this example.

It is perfectly ok, however, to use independent implicit connections:

var stmt = datasource.SELECT ID FROM Employees;
var rset = stmt.run();
while (rset.next()) {
	...
}
// it's ok to do a new query; the implicit connection will be reused
var stmt2 = datasource.SELECT * FROM Employees WHERE ID < 100;
var rset2 = stmt2.run();
while (rset2.next()) {
	...
}

Care must also be taken when calling functions recursively. This code will not work correctly:

this.recurse = function(id) {
	var stmt = customerDS.SELECT * FROM Customers WHERE ID >= ?id?;
	stmt.id = id;
var rset = stmt.run();
while (rset.next()) {
stdout.println("found customer with id " + rset.ID);
this.recurse(rset.ID.value + 1);
}

Calling this.recurse will not create a new scope, overwriting the rset of the caller.

Situations with recursive database queries are best avoided. If you can't avoid them it is better to use explicit connections (see below). For best performance you should use a connection pool (see Defining data sources) and close the connections if you don't need them any more. The above recursive example function would then be written like this:

this.recurse = function(id) {
	var connection = customerDS.getConnection();
	var stmt = connection.SELECT * FROM Customers WHERE ID >= ?id?;
	stmt.id = id;
var rset = stmt.run();
while (rset.next()) {
stdout.println("found customer with id " + rset.ID);
this.recurse(rset.ID.value + 1);
} connection.close(); // free resources explicitly
}

It is recommended that you use implicit connections wherever possible. You should structure your code such as to avoid the need for recursion or nested statements in the same scope.

Explicit connections

You can also create an explicit connection:

var connection = datasource.getConnection();	

Explicit connections should be used if:

Explicit connections are not automatically reused. They are not bound to a scope and can be passed around as arguments to other functions. SQL statements can be created from these connection objects in the same way as from data source objects. Calls to getConnection() will retrieve independent connection objects that can be freely nested.

The downside of explicit connections is that JSSP can't automatically keep the number of necessary connections minimal. Moreover, intelligent editors (should they ever become available) will not be able to provide context sensitive help for explicit connections in all cases.

Closing connections

JSSP will automatically keep track of connections and close them and all dependent resources when the script is done. It does not matter whether they have been created explicitly or implicitly.

This means that you don't have to worry about closing connections or other resources. However, you can not store connections in JSSP's session or the application object because they don't survive the current request.

In long-running scripts with many connections you can close explicit connections manually:

connection.close();

This will only be necessary in very rare instances, though. It is also recommended to use a connection pool (see Defining data sources) to improve performance when using many connections.

Connection localization

When connections are created from within JSSP pages their locale is set to the client browser's preferred locale. For explicit connections you can override this setting by assigning any other locale as defined by the class java.util.Locale (see Localization), e.g.:

connection.locale = Packages.java.util.Locale.JAPANESE;

To avoid the lengthy "Packages." syntax you can import the Java class Locale at the beginning of your script:

importClass(Packages.java.util.Locale);

You can then use it directly:

stmt.locale = Locale.JAPANESE;

To reset the locale to the browser's preferred locale you can use the messages object (see the JSSP documentation about the messages object):

connection.locale = messages.locale;

For implicit connections you can set the locale on the created statement object:

var stmt = datasource.SELECT * FROM Employees;
stmt.locale = Packages.java.util.Locale.JAPANESE;

Duplicating explicit connections (deprecated)

If you need to nest SQL statements you can duplicate an existing explicit connection using the method duplicate():

var connection2 = connection.duplicate();

Duplicating a connection can be necessary because some JDBC drivers do not allow multiple open statements on one connection. For this reason JSSQL manages exactly one statement object per connection. When closing a connection object (either through an explicit call to close() or an implicit closing mechanism) all duplicated connections and their associated resources are also closed. If you manually close connections, do so in the reverse order of their creation.

The use of duplicate() is deprecated, however. If you need additional explicit connections it is better to use getConnection() on the data source object.

SQL Statements

Embedded SQL rules

Embedded SQL statements are internally treated as functions of JavaScript objects. The JavaScript syntax is bypassed for embedded SQL from the initial keyword up to the first unqoted semicolon, allowing you to specify any possible SQL command. It is possible to define a SQL statement on any object without a syntax error. Logically, however, you would want to execute statements against a database, and in fact only connection objects and data sources support the SQL feature. If you try to run a SQL statement on a non-connection object the result will be a runtime error.

Make sure that you are using embedded SQL on data sources or explicit connection objects only.

Embedded SQL statements can contain comments starting with --.

SQL statements are validated at compile time. To see how to disable the validation see Using proprietary commands.

Creating statements

Statement objects can be created from explicit connection objects or from predefined data source objects. They contain the SQL command or query that you want to execute.

Statement objects are normally created using embedded SQL statements. An example:

var stmt = datasource.SELECT ID, NAME FROM EMPLOYEES;

Note that for embedded SQL statements a terminating semicolon is mandatory (as opposed to the standard JavaScript syntax where semicolons are optional).

The SELECT keyword tells JSSQL that a special SQL statement is following. The parser will treat all characters up to the first unquoted semicolon (; but not ';') as belonging to the SQL statement. SQL statements may span several lines. They may contain comments and named or indexed placeholders.

Only certain keywords are allowed for the first token of embedded SQL statements. These keywords are case-sensitive (all capitals).

Embedded SQL statements are "static" in the sense that they cannot be changed at runtime. If you want to build SQL statements at runtime you can create dynamic statements.

Dynamic statements

To create dynamic statements you need to use an explicit connection. In this case you would create the statement using prepareStatement():

var sql = "SELECT * FROM Employees WHERE " + column + " = ?value?";
var stmt = datasource.getConnection().prepareStatement(sql);

Statement objects obtained from the prepareStatement() function are functionally identical to those obtained by embedded SQL.

Created statement objects have dynamic properties that correspond with placeholders defined in the SQL command. Placeholders may be named or indexed.

Named placeholders

In SQL statements you can use named placeholders (e.g., ?Name?) as placeholders for values, as shown in this example:

var stmt = datasource.SELECT ID FROM EMPLOYEES 
				WHERE NAME = ?Name?;

stmt.Name = "Jack";

Named placeholders are case sensitive and may appear only once. Whitespace is not allowed in definitions of named placeholders.

Named placeholders can contain an optional cast directive that tells JSSQL how to handle type conversions. See cast directives for more details.

Statement objects behave a little different than other JavaScript objects. They only allow placeholder names when assigning property values. You are not allowed to set other properties on these objects to prevent possible logic errors originating in typing mistakes.

Before executing a statement all placeholders must have been set, or you will get a runtime error. Before throwing the runtime error, however, JSSQL tries to resolve unset placeholders from variables of the current scope. See variable resolution for details.

Indexed placeholders

Instead of named placeholders you can create indexed placeholders by using single question marks in the SQL command. To set their values use the setter methods of the statement object:

var stmt = connection.SELECT ID FROM EMPLOYEES 
				WHERE NAME = ?;

stmt.setString(1, "Jack");

Note that this way requires you to count the question marks and update the indexes whenever the order changes. Use indexed parameters only if absolutely necessary. You can combine single question marks and named placeholders in the same query. As a matter of fact you cannot address single question mark placeholders with a name. Placeholder indexes start from 1.

A list of possible setter methods is available in the documentation of the Java class PreparedStatement.

It is strongly recommended to only use named placeholders and set their values by name as this greatly improves code readability and maintainability.

Variable resolution

As there are some yet unresolved issues with the variable resolution, the following information does not apply.

In addition to addressing placeholders by their name you can also insert variable names into the SQL statement. If you haven't set those JSSQL will try to resolve them when you execute the statement. The variables will be resolved in the scope that is present at execution time. If a placeholder can not be resolved because its variable can not be found a runtime error will occur. Variable names are case sensitive, as a matter of fact. It is not possible to put JavaScript expressions into SQL statements. Function calls aren't allowed either.

Example:

var person = { name: 'Jack' };

var stmt = connection.SELECT ID FROM EMPLOYEES 
				WHERE NAME = ?person.name?;

var rset = stmt.run();

In this example, person.name will be resolved when run() is called and its value inserted into the placeholder.

Placeholder type conversion

When you set a placeholder in a statement (or when variable references are resolved) JSSQL calls an appropriate setter method of the underlying JDBC driver (see class PreparedStatement). As you may perhaps know there are numerous methods for setting statement parameters, each taking a different parameter type. In order to avoid conversion problems the correct methods should be called, otherwise the program might behave strangely. For example, when calling setInt with a float parameter decimals may be lost due to type conversion.

JSSQL tries to make type conversion easy for you. Its conversion rules are fairly simple. For placeholder conversion, JSSQL never examines the underlying database to determine the target type. It is up to you as a programmer to use the right data types in your program to help JSSQL determine the correct conversion type. This will usually not be a problem since you are supposed to know the database you are working with. 

JSSQL specifies a so-called Statement Type Map which defines how conversions into the JDBC subsystem take place. In this JSSQL version the Statement Type Map can not be changed. There is also a ResultSet Type Map which defines the opposite direction, namely how JDBC types are converted into JavaScript types; the ResultSet Type Map is described further below.

Since you can not only work with "native" JavaScript objects but also with instances of any Java class, JSSQL specifies conversion rules for some of the more frequently used Java classes as well. It is recommended that you work with native JavaScript objects wherever possible, though; not only because JSSQL is a JavaScript implementation but also to avoid conversion mistakes when converting to and from Java objects.

Cast directives

The Statement Type Map also describes allowed cast directives for certain data types. A cast directive is a hint to the JSSQL converter how it should handle the conversion of a certain placeholder. You can specify the type of named placeholders by prefixing them with a cast directive:

var stmt = connection.SELECT ID FROM EMPLOYEES 
	WHERE BIRTHDAY = ?(date)birthday?;

stmt.birthday = aDate;

Cast directives are put in round brackets (parentheses). In this example, (date) is a cast directive telling JSSQL that it should use a plain date (as opposed to a datetime value). There may be no whitespace between the cast directive and the placeholder. The following cast directives are allowed:

Cast directive Meaning
(date) Plain date with day precision
(time) Plain time with maximum precision (depends on the database)
(datetime) Date and time with maximum precision (depends on the database)
(numeric) Use a numeric setter

When setting a string into a placeholder with a cast directive of date, time, datetime or numeric the current locale settings of the statement are taken into account when parsing the value.

Note that the data types must be supported by the database and the underlying JDBC driver. This is not always the case. For example, Microsoft's SQL Server does not support date- or time-only data types. While conditions like this do rarely result in error messages information might get lost, so be sure to check how your database and driver handle these cases.

Conversion errors

There are certain error conditions that can occur when converting placeholder values. These are:

1. A conversion is not allowed by the Statement Type Map. For example, the program specifies that a boolean be cast to a date value. A ConversionError will be thrown.

2. A conversion can't be performed due to an error. For example, the program specifies that a string be cast to a date value but supplies a string that cannot be parsed to a date. A ConversionError will be thrown.

3. Finally, a value is not accepted by the JDBC driver. This can occur when you try to overflow a column, e.g. supply a value > 255 for a TINYINT column. It can also occur if you supply the wrong type of data, e.g. a non-numeric string for a NUMERIC column. In these cases a DatabaseError will be thrown.
These error cases are highly specific to the database and JDBC driver you use. Different drivers will almost certainly handle some conversions differently. If in doubt check the manual of your JDBC driver, or better yet, try it out.

Statement Type Map

This map describes how conversions into the database are handled by JSSQL. For conversions when reading database values, please see the ResultSet Type Map.

Source type Optional cast Allowed Conversion steps Setter method
String (date) Yes Create a java.util.Date by parsing the string (using the current locale). Continue with java.util.Date/(date). May throw errors. see Date/(date)
  (time) Yes Create a java.util.Date by parsing the string (using the current locale). Continue with java.util.Date/(time). May throw errors. see Date/(time)
  (datetime) Yes Create a java.util.Date by parsing the string (using the current locale). Continue with java.util.Date/(datetime). May throw errors. see Date/(datetime)
  (numeric) Yes Create a BigDecimal by parsing the string (using the current locale). Continue with BigDecimal. May throw errors. see BigDecimal
  - Yes - setString
Number (date) Yes Create a java.util.Date() omitting the time components and continue with java.util.Date/-. see Date/(date)
  (time) Yes Create a java.util.Date() omitting the date components and continue with java.util.Date/-. see Date/(time)
  (datetime) Yes Continue with java.util.Date/-. see Date/(datetime)
  (numeric) Yes - see Number/-
  - Yes Convert to a Java BigDecimal without losing information. setBigDecimal
Date (date) Yes Construct a java.util.Date from the millisecond value of the date object. Continue with java.util.Date/(date). see java.util.Date/
(date)
  (time) Yes Construct a java.util.Date from the millisecond value of the date object. Continue with java.util.Date/(time). see java.util.Date/
(time)
  (datetime) Yes Construct a java.util.Date from the millisecond value of the date object. Continue with java.util.Date/(datetime). see java.util.Date/
(datetime)
  (numeric) Yes Convert to a long value containing the millisecond value of the object. -
  - Yes Construct a java.util.Date from the millisecond value of the date object. Continue with java.util.Date/-. see java.util.Date/-
Boolean (date) No Error -
  (time) No Error -
  (datetime) No Error -
  (numeric) Yes false -> 0, true -> 1. Continue with Number. see Number/-
  - Yes - setBoolean
java.util.Date (date) Yes Remove time components and continue with java.util.Date/-. see java.util.Date/-
  (time) Yes Remove date components and continue with java.util.Date/-. see java.util.Date/-
  (datetime) Yes Continue with java.util.Date/-. see java.util.Date/-
  (numeric) Yes Construct a number from the millisecond value. see Number/-
  - Yes - setTimestamp
Decimal N/A, to be specified soon
Function any No Error  
Object any No Error  
Array any No Error  
java.lang.String see String
java.lang.Number see Number
java.math.BigDecimal see Number
any other
java.lang.Object
(date)/(time)/
(datetime)/
(numeric)
No Error -
  - Yes - setObject

If you need special access to fields like e.g. arrays or BLOBs you can use the setter methods of the PreparedStatement class. In this case, unfortunately, you cannot use named placeholders or variable resolution.

Executing statements

Statements are executed by calling the run() method. For SELECT statements this method returns a ResultSet. For INSERT, UPDATE and DELETE statements, this method returns the number of affected rows. For all other statements, this method returns a boolean value. Example:

var rset = stmt.run();

Most of the time you will be fine using run(). If you need more fine-grained control, you can use one of the other execute methods of the statement object.

Basically there are two types of statements in SQL - those who return a ResultSet object and those who don't. Those of the first kind are called queries (mostly SELECTs) and they can be executed by calling the executeQuery method on the statement:

var rset = stmt.executeQuery();

Statements that do not return a result set may return an integer value instead that indicates the number of affected rows. Examples for these statements are INSERTs, UPDATEs and DELETEs. These statements can be executed using executeUpdate():

var affectedRows = stmt.executeUpdate();

Other statements return a boolean. These statements can be executed using the execute method:

var success = stmt.execute();

 Have a look at the documentation of the Statement class for more information.

However, the preferred way is to use stmt.run() which will automatically determine the correct method and result type depending on the SQL statement. Using the wrong execute method for a certain statement type may result in errors.

Result Sets

The results retrieved from a database are ResultSet objects. The JSSQL ResultSet object contains a number of convenient improvements over the traditional JDBC way of doing things.

Retrieving column objects

You usually retrieve column values by using their name as a property:

var name = rset.Name;

The object returned by rset.Name in this example is called a column object. Column properties are case sensitive.

Please note that not all JDBC drivers preserve case in result sets. Therefore, addressing column values might require you to use all uppercase property names even though the database fields are capitalized differently (this is the case with HSQLDB, for example). Rather than ignoring case internally, JSSQL displays an error message with the closest possible match for the property, to give you the opportunity to understand what's going on.

You can also retrieve column objects by index. Indexes start from 1; this is a JDBC convention. For example, you can use:

var name = rset[1];

Usually you should avoid this method as it makes the code less robust and maintainable. It only makes sense when looping over a result set row.

Arrays of column objects

Some SQL queries can return more than one value for the same column name. This is the case with cross joins, for example:

var stmt = SELECT * FROM TEST, TEST AS TEST2;

The result set will contain all columns of the table TEST twice. JSSQL internally makes the columns accessible as arrays. In this example you can access the columns TEST.ID and TEST2.ID by using a numeric index:

var rset = stmt.run();
var test_id = rset.ID[0];
var test2_id = rset.ID[1];

It is always preferable to disambiguate columns in the SQL statement, however:

var stmt = SELECT TEST.ID AS TEST_ID, TEST2.ID AS TEST2_ID FROM TEST, TEST AS TEST2;

Column objects

To output the content of a column, print its column object, e.g.

out.print(rset.Name);

In a JSSP page you could use

<%= rset.Name %>

If you access a column object in a String context like this, it will be converted to a formatted string by calling its toString() method.

An important thing to know here is that the column objects returned by ResultSets are not the actual values that are stored in the database. Instead, they are objects that behave like them for a large number of common situations, but they also offer additional features.

One important feature of column objects is that they are never null. If the database value is NULL their string representation is the empty string. To know whether the corresponding value in the database is null, use the properties isNull or isNotNull:

if (rset.Name.isNull) {
    ... // Name is NULL
}
if (rset.Name.isNotNull) {
    ... // Name is not NULL
}

Alternatively you can test the value property for null:

if (rset.Name.value == null) {
    ... // Name is NULL
}

Internally a column object stores two values that are accessible via property names: value and javaValue. The actual instances stored in these properties may be the same object depending on the column type.

The value property contains the object that has been retrieved from the database according to the ResultSet Type Map. The ResultSet Type Map specifies how JDBC database types are converted into JavaScript objects. In some cases, e.g. when working with numeric values, it is important to use the value object instead of the column object because the column object is a formatted representation of the underlying value.

javaValue holds an instance of a Java type of the database value. You can use this value e.g. to do calulations on NUMERIC or DECIMAL types.

The column object's string representation (the output of its toString() function) is a formatted string depending on the underlying data type and the column object's locale.

For you as a programmer this means that you don't have to worry about output formatting. In most cases dates and numbers etc. will be formatted fine, so you can directly use the column object in a String context as output.

Localization

locale

Column objects support localization via their locale property. The locale setting of a connection object is handed down to statement objects at the time of their creation. These in turn pass it on to the column objects when these are created. Thus you can specify the locale settings for each level of the tree "connection -> statement -> column". For implicit connections the tree starts at the statement level. A ResultSet object has no locale property because of possible name conflicts with columns in the database. Note that changing the locale setting of an object has no effect on already created objects.

The locale setting affects how dates and numbers are to be formatted. It is automatically taken into account by the output formatters (see below). On statement objects the locale setting also determines how passed-in strings are converted to dates and numbers if cast directives are used.

Example:

// create an explicit connection
var connection = datasource.getConnection(); 
connection.locale = Locale.JAPANESE; // for all created statements

var stmt = connection.SELECT aColumn FROM aTable;
stmt.locale = Locale.ITALIAN;        // for this statement only

var rset = stmt.run();      // a ResultSet has no locale property

while (rset.next()) {
	var f = rset.aColumn;
	f.locale = Locale.FRENCH;      // for this column only
	out.println(f);
}

Time zones

As there are some yet unresolved issues with time zones the following information does not apply.

The timezone setting affects how dates and times are re-calculated on formatting. Internally all dates are stored as UTC dates. Dates do thus represent unique points on the time axis. When displaying the date to a user you can decide about how this point in time is presented to the user.

For example, a UTC date 1970-01-01 00:00:00.000 is 1970-01-01 01:00:00.000 in Greenwich Mean Time. 

They can be represented by a signed 32 value indicating the number of milliseconds since 1970-01-01 00:00:00.000 UTC.

JavaScript Date objects can be constructed by passing this value to the constructor Date(). When subsequently converting a Date to a string, the date may be converted to your local timezone. This may be confusing at first, but all you have to do is remember that the dates are internally UTC.

There are some issues with UTC dates that aren't easy to resolve, so JSSQL currently relies on the standard Java treatment of Date objects, which, albeit cumbersome, is at least consistent and documented. Perhaps we can in time come up with a more satisfying date handling mechanism.

Output formatting

When outputting a column using, e.g., out.print(rset.AMOUNT); or converting it to a String using, e.g., var s = String(rset.AMOUNT) you get a formatted JavaScript string. Depending on the column's data type the output will have been formatted by using one of the built-in formatters, unless you have specified your own formatter for the column. The type of formatter to be used as default is defined in the ResultSet Type Map. The formatter is assigned to the column object when the value is first accessed.

For example, a NUMERIC column will be retrieved as an instance of the Java class BigDecimal1. BigDecimal objects are by default formatted using the class NumberFormatter with the locale set for the column (the locale is taken from the statement object). The result is a string that looks just like a nice number in the language or region specified by the locale. You can override the locale for the column by setting its locale property.

You can define your own output formatter or use one of the predefined output formatters. The following formatters are predefined:

Formatter class name Description
ValueFormatter.DefaultFormatter Returns the String representation of the value. Any locale-specific conversions use the system's default locale. Special HTML characters (<, >, &, " and ') are escaped.
ValueFormatter.NumericFormatter For floating point values and BigDecimal. Returns a locale-specific formatted string representation with two decimals. For all other objects, returns their String representation.
ValueFormatter.NumericFormatterN with N in currently [0..12]. A numeric formatter with N specifying the number of decimals. The number of decimals is taken from the database field's definition (scale).
ValueFormatter.DateFormatter For JavaScript Date and Java Date objects (and subclasses). Returns a locale-specific formatted string representation of the date using the long date format (see class DateFormat). For all other objects, returns their String representation.
Takes locale settings into account.
ValueFormatter.TimeFormatter For JavaScript Date and Java Date objects (and subclasses). Returns a locale-specific formatted string representation of the time using the medium time format (see class DateFormat). For all other objects, returns their String representation.
Takes locale settings into account.
ValueFormatter.DateTimeFormatter For JavaScript Date and Java Date objects (and subclasses). Returns a locale-specific formatted string representation of date and time using the long date format and the medium time format (see class DateFormat). For all other objects, returns their String representation.
Takes locale settings into account.
ValueFormatter.BooleanFormatter For JavaScript and Java Booleans,  returns "true" or "false". For all other objects, returns their String representation.

Formatters are stateless singletons. They do not store state, so they can be reused.

Optionally, you can specify a custom formatter that does the formatting for you. To set the formatter, use the column object's property formatter. Example:

// Define a custom formatter for the column AMOUNT
// do not do this in a loop!
var amountFormatter = 
	new CustomFormatter(
		function(column) {
			// do not evaluate "column" here,
			// otherwise you'll get a stack overflow!

			// enclose the value in brackets
			return "[" + column.value + "]";
		}
	);

...

while (rset.next()) {

	// set the custom formatter for column AMOUNT
	rset.AMOUNT.formatter = amountFormatter;

	out.println(rset.AMOUNT);
}

CustomFormatter is a Java class that takes a JavaScript function object as only parameter of its constructor. When the column is to be formatted, CustomFormatter passes the column object (column) to its JavaScript function. This function must return the formatted result as a JavaScript object. In this example, it returns a string representation of the amount value (in case of a NUMERIC column, BigDecimal.toString(), formatted using the default locale) enclosed in square brackets, as a JavaScript string.

Do not evaluate the expression column.toString() in formatting functions as this will result in a recursive call (stack overflow). Be careful: This also applies to all uses of column in a String context as well (such as string comparisons). Always use column.value instead.

Working with unformatted values

If you don't want to work with formatted JavaScript strings (which is inconvenient if you want to do calculations on values) you can access the values directly:

var amount = rset.AMOUNT.value; will get you the JavaScript object that has been retrieved from the database according to the ResultSet Type Map.

If you need the actual Java object that has been retrieved from the database you can use the property javaValue:

var javaAmount = rset.AMOUNT.javaValue;

Be careful as these values can be null.

It will be rarely necessary to use Java objects, though. In case you need to handle special fields like arrays or BLOBs you can also use the special functions of the ResultSet class to access these fields. The exact nature of the data and access will depend on your database and JDBC driver.

ResultSet Type Map

The ResultSet Type Map defines how JDBC data types are mapped to JavaScript objects when database values are read. JSSQL tries to map JDBC types to JavaScript types whenever possible. If this is not possible, the closest Java type will be used. This type may depend on your JDBC driver implementation.

For conversions of values that go into the database system the Statement Type Map is used.

JDBC type javaValue type value type Formatter
CHAR java.lang.String String DefaultFormatter
VARCHAR java.lang.String String  DefaultFormatter
LONGVARCHAR java.lang.String String  DefaultFormatter
NUMERIC java.math.BigDecimal java.math.BigDecimal1 NumericFormatterN
DECIMAL java.math.BigDecimal java.math.BigDecimal1 NumericFormatterN
  NumericFormatterN: N is the number of decimals of the database field (scale) if the scale is within [0..12], and the default number of decimals of the platform default locale's currency otherwise
BOOLEAN java.lang.Boolean Boolean BooleanFormatter
BIT java.lang.Boolean Boolean BooleanFormatter
TINYINT java.lang.Byte Number DefaultFormatter
SMALLINT java.lang.Short Number DefaultFormatter
INTEGER java.lang.Integer Number DefaultFormatter
BIGINT java.lang.Long Number (may be inaccurate!) DefaultFormatter
DATE java.sql.Date Date with hr/min/sec/msec components set to zero DateFormatter
TIME java.sql.Time Date with day/month/year components set to 1/1/1970 TimeFormatter
TIMESTAMP java.sql.Timestamp Date DateTimeFormatter
REAL java.lang.Float Number DefaultFormatter
FLOAT java.lang.Double Number DefaultFormatter
DOUBLE java.lang.Double Number DefaultFormatter
BINARY byte[] Array of Number objects DefaultFormatter
VARBINARY byte[] Array of Number objects DefaultFormatter
LONGVARBINARY byte[] Array of Number objects DefaultFormatter
CLOB Clob String DefaultFormatter
BLOB Blob Array of Number objects DefaultFormatter
ARRAY Java Array Java Array DefaultFormatter
DISTINCT mapping of underlying type mapping of underlying type DefaultFormatter
STRUCT Struct Struct DefaultFormatter
REF Ref Ref DefaultFormatter
JAVA_OBJECT mapping of underlying type mapping of underlying type DefaultFormatter

Not all of these conversions may work as expected with all JDBC drivers. 

Given f as the column object, the value is accessible via f.value. The Java value is accessible via f.javaValue.

Result set meta data

To get an array of available columns from a result set, use the method getColumns(). This method returns an array of available column names. You can iterate over the array to retrieve column objects dynamically.

Example:

var columns = rset.getColumns();
for (c in columns) {
    var v = rset[columns[c]];
    out.print(v + "\t");
}  

Result set meta data, just like column meta data, will only be available if there are actual rows in the result set. Without rows you have to resort to the standard JDBC way of obtaining DatabaseMetaData.

Column meta data

Column meta data can be accessed via column objects. Example:

var columnLength = rset.NAME.displaySize;

The following meta data properties are currently available:

Meta Data Property Meaning
name The name of the column
catalog The catalog that contains this column (may be empty with some database drivers)
schema The schema that contains this column (may be empty with some database drivers)
table The table that contains this column
type The JDBC column type, an integer constant defined in the class java.sql.Types
typeName The JDBC type name
label The display label of the column
displaySize The display size
precision Contains the precision for numeric columns. For other column types this value is not defined
scale Contains the scale for numeric columns. For other column types this value is not defined
isAutoIncrement true if the column is an auto increment column
isCaseSensitive true if the database handles this column in a case sensitive way
isCurrency true if this column contains a currency value
isDefinitelyWritable true if the column can be written
isNullable given f as the column object, this value may be one of the constants f.columnNullable, f.columnNoNulls or f.columnNullableUnknown
isReadOnly true if the column is not writable
isSearchable true if the column is searchable
isSigned true if the column is a signed number
isWritable true if the column is writable

Please note that the meaning of these meta data values may depend on the specific JDBC driver you use. Not all values may be useful depending on how the database and JDBC driver implement the specification.

For a complete example see dbmeta.jssp in the jssp_example.war distribution. (Link to the download section)

Using comments in embedded SQL

You can use SQL comments starting with -- to comment out the rest of a line:

var stmt = connection.SELECT ID, NAME
    -- this is an example comment
    FROM TEST;

However, you cannot use unquoted semicolons in comments. For example, the following is invalid syntax:

var stmt = connection.SELECT ID, NAME
    -- this is an invalid comment;
    FROM TEST;

Supported SQL keywords

Only certain keywords are recognized as the beginning of SQL statements. They are case-sensitive. The keywords currently supported are:

SELECT
INSERT
UPDATE
DELETE
CREATE
DROP
ALTER
RENAME
GRANT
REVOKE
LOCK
UNLOCK
BEGIN
COMMIT
ROLLBACK
AUDIT
NOAUDIT
LOAD
UNLOAD
SET
COMMENT
EXPLAIN
TRUNCATE

Using proprietary commands

JSSQL uses JSqlParser as its internal validator of SQL statements. JSqlParser parses against the SQL-92 standard, so most SQL statements should run fine.

Sometimes, if working with special database systems you may want to disable JSSQL's internal SQL parser to allow some special syntax or keywords. To disable the syntax check, insert the keyword <proprietary> after the first keyword of your SQL statement (there may only be whitespace in between):

var specialStatement = connection.SELECT <proprietary>
    my_special_database_select_statement;

 

Footnotes

1 Note that this is likely to change in the future; JavaScript does not yet provide a native Decimal type, but the language specification is expected to change and JSSQL will probably adopt the new data type.


Document version: 0.9, 2008-03-03. Author: Leo Meyer, leo_meyer@users.sourceforge.net

This file is a part of the JSSP project documentation at http://jssp.sourceforge.net.

The JSSP project is hosted by Sourceforge. SourceForge.net Logo