![JSSP["JavaScript Server Pages"] = keeping.simple.things.simple;](images/jssp_logo.png){kind=logo}
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.
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.
<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.
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.
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 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.
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.
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.
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.
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;
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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;
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.
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);
}
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.
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.
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.
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.
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 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)
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;
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 |
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;
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.