Erbix

Home > Javascript Server-side Reference > JavaScript Database Connectivity

JavaScript Database Connectivity (JSDBC)

The execute and queryForList in erbix/query module provide easy access to the database. However, the functionality is rather limited. The JSDBC interface provides a functionality similar to the Java Database Connectivity (JDBC). Currently supported objects are:

Users do not need to instantiate JSDBC classes. A Connection object is returned by require("erbix/query").getConnection(); Statement and PreparedStatement objects are created by calling createStatement or prepareStatement methods on a Connection object and so on: 

var connection = require("erbix/query").getConnection();
var statement = connection.createStatement();
var resultSet = statement.executeQuery("SELECT version()");
var resultSetMetaData = resultSet.getMetaData();

Some functions will require the use of static constants. Accessing static members is made by using the jsdbc package accessor in the erbix/query module: 

var jdbc = require("erbix/query").jsdbc;
var generateKeysConstant = jdbc.Statement.RETURN_GENERATED_KEYS;

Connection

The Connection object is returned by the getConnection() function called either on the query.js module or on a Query object. The user will not instantiate Connection objects to access the database.

A Connection object has the following methods (more details):

  • void clearWarnings()
    Clears all warnings reported for this Connection object.
  • void close()
    Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.
  • void commit()
    Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.
  • Statement createStatement()
    Creates a Statement object for sending SQL statements to the database.
  • Statement createStatement(int resultSetType, int resultSetConcurrency)
    Creates a Statement object that will generate ResultSet objects with the given type and concurrency.
  • Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
    Creates a Statement object that will generate ResultSet objects with the given type, concurrency, and holdability.
  • Struct createStruct(String typeName, Object[] attributes)
    Factory method for creating Struct objects.
  • boolean getAutoCommit()
    Retrieves the current auto-commit mode for this Connection object.
  • int getHoldability()
    Retrieves the current holdability of ResultSet objects created using this Connection object.
  • int getTransactionIsolation()
    Retrieves this Connection object's current transaction isolation level.
  • SQLWarning getWarnings()
    Retrieves the first warning reported by calls on this Connection object.
  • boolean isClosed()
    Retrieves whether this Connection object has been closed.
  • boolean isReadOnly()
    Retrieves whether this Connection object is in read-only mode.
  • boolean isValid(int timeout)
    Returns true if the connection has not been closed and is still valid.
  • String nativeSQL(String sql)
    Converts the given SQL statement into the system's native SQL grammar.
  • PreparedStatement prepareStatement(String sql)
    Creates a PreparedStatement object for sending parameterized SQL statements to the database.
  • PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
    Creates a default PreparedStatement object that has the capability to retrieve auto-generated keys.
  • PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
    Creates a PreparedStatement object that will generate ResultSet objects with the given type and concurrency.
  • PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)
    Creates a PreparedStatement object that will generate ResultSet objects with the given type, concurrency, and holdability.
  • PreparedStatement prepareStatement(String sql, String[] columnNames)
    Creates a default PreparedStatement object capable of returning the auto-generated keys designated by the given array.
  • void releaseSavepoint(Savepoint savepoint)
    Removes the specified Savepoint and subsequent Savepoint objects from the current transaction.
  • void rollback()
    Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.
  • void rollback(Savepoint savepoint)
    Undoes all changes made after the given Savepoint object was set.
  • void setAutoCommit(boolean autoCommit)
    Sets this connection's auto-commit mode to the given state.
  • void setHoldability(int holdability)
    Changes the default holdability of ResultSet objects created using this Connection object to the given holdability.
  • void setReadOnly(boolean readOnly)
    Puts this connection in read-only mode as a hint to the driver to enable database optimizations.
  • Savepoint setSavepoint()
    Creates an unnamed savepoint in the current transaction and returns the new Savepoint object that represents it.
  • Savepoint setSavepoint(String name)
    Creates a savepoint with the given name in the current transaction and returns the new Savepoint object that represents it.
  • void setTransactionIsolation(int level)
    Attempts to change the transaction isolation level for this Connection object to the one given.

The Connection object has the following static fields:

  • static int TRANSACTION_NONE
    A constant indicating that transactions are not supported.
  • static int TRANSACTION_READ_COMMITTED
    A constant indicating that dirty reads are prevented; non-repeatable reads and phantom reads can occur.
  • static int TRANSACTION_READ_UNCOMMITTED
    A constant indicating that dirty reads, non-repeatable reads and phantom reads can occur.
  • static int TRANSACTION_REPEATABLE_READ
    A constant indicating that dirty reads and non-repeatable reads are prevented; phantom reads can occur.
  • static int TRANSACTION_SERIALIZABLE
    A constant indicating that dirty reads, non-repeatable reads and phantom reads are prevented.
back to summary

Statement

The Statement object is returned by the Connection.createStatement functions.

The Statement object has the following static fields:

  • static int CLOSE_ALL_RESULTS
    The constant indicating that all ResultSet objects that have previously been kept open should be closed when calling getMoreResults.
  • static int CLOSE_CURRENT_RESULT
    The constant indicating that the current ResultSet object should be closed when calling getMoreResults.
  • static int EXECUTE_FAILED
    The constant indicating that an error occured while executing a batch statement.
  • static int KEEP_CURRENT_RESULT
    The constant indicating that the current ResultSet object should not be closed when calling getMoreResults.
  • static int NO_GENERATED_KEYS
    The constant indicating that generated keys should not be made available for retrieval.
  • static int RETURN_GENERATED_KEYS
    The constant indicating that generated keys should be made available for retrieval.
  • static int SUCCESS_NO_INFO
    The constant indicating that a batch statement executed successfully but that no count of the number of rows it affected is available.

The Statement object has the following methods:

  • void addBatch(String sql)
    Adds the given SQL command to the current list of commmands for this Statement object.
  • void cancel()
    Cancels this Statement object if both the DBMS and driver support aborting an SQL statement.
  • void clearBatch()
    Empties this Statement object's current list of SQL commands.
  • void clearWarnings()
    Clears all the warnings reported on this Statement object.
  • void close()
    Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.
  • boolean execute(String sql)
    Executes the given SQL statement, which may return multiple results.
  • boolean execute(String sql, int autoGeneratedKeys)
    Executes the given SQL statement, which may return multiple results, and signals the driver that any auto-generated keys should be made available for retrieval.
  • boolean execute(String sql, String[] columnNames)
    Executes the given SQL statement, which may return multiple results, and signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval.
  • int[] executeBatch()
    Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
  • ResultSet executeQuery(String sql)
    Executes the given SQL statement, which returns a single ResultSet object.
  • int executeUpdate(String sql)
    Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
  • int executeUpdate(String sql, int autoGeneratedKeys)
    Executes the given SQL statement and signals the driver with the given flag about whether the auto-generated keys produced by this Statement object should be made available for retrieval.
  • int executeUpdate(String sql, String[] columnNames)
    Executes the given SQL statement and signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval.
  • Connection getConnection()
    Retrieves the Connection object that produced this Statement object.
  • int getFetchDirection()
    Retrieves the direction for fetching rows from database tables that is the default for result sets generated from this Statement object.
  • int getFetchSize()
    Retrieves the number of result set rows that is the default fetch size for ResultSet objects generated from this Statement object.
  • ResultSet getGeneratedKeys()
    Retrieves any auto-generated keys created as a result of executing this Statement object.
  • int getMaxFieldSize()
    Retrieves the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object.
  • int getMaxRows()
    Retrieves the maximum number of rows that a ResultSet object produced by this Statement object can contain.
  • boolean getMoreResults()
    Moves to this Statement object's next result, returns true if it is a ResultSet object, and implicitly closes any current ResultSet object(s)
    obtained with the method getResultSet.
  • boolean getMoreResults(int current)
    Moves to this Statement object's next result, deals with any current ResultSet object(s)
    according to the instructions specified by the given flag, and returns true if the next result is a ResultSet object.
  • int getQueryTimeout()
    Retrieves the number of seconds the driver will wait for a Statement object to execute.
  • ResultSet getResultSet()
    Retrieves the current result as a ResultSet object.
  • int getResultSetConcurrency()
    Retrieves the result set concurrency for ResultSet objects generated by this Statement object.
  • int getResultSetHoldability()
    Retrieves the result set holdability for ResultSet objects generated by this Statement object.
  • int getResultSetType()
    Retrieves the result set type for ResultSet objects generated by this Statement object.
  • int getUpdateCount()
    Retrieves the current result as an update count; if the result is a ResultSet object or there are no more results, -1 is returned.
  • SQLWarning getWarnings()
    Retrieves the first warning reported by calls on this Statement object.
  • boolean isClosed()
    Retrieves whether this Statement object has been closed.
  • boolean isPoolable()
    Returns a value indicating whether the Statement is poolable or not.
  • void setCursorName(String name)
    Sets the SQL cursor name to the given String, which will be used by subsequent Statement object execute methods.
  • void setEscapeProcessing(boolean enable)
    Sets escape processing on or off.
  • void setFetchDirection(int direction)
    Gives the driver a hint as to the direction in which rows will be processed in ResultSet objects created using this Statement object.
  • void setFetchSize(int rows)
    Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects genrated by this Statement.
  • void setMaxFieldSize(int max)
    Sets the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object.
  • void setMaxRows(int max)
    Sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number.
  • void setPoolable(boolean poolable)
    Requests that a Statement be pooled or not pooled.
  • void setQueryTimeout(int seconds)
    Sets the number of seconds the driver will wait for a Statement object to execute to the given number of seconds.
back to summary

PreparedStatement

The PreparedStatement object is returned by the Connection.prepareStatement methods.

The PreparedStatement object has all the methods of Statement and the following methods:

  • void addBatch()
    Adds a set of parameters to this PreparedStatement object's batch of commands.
  • void clearParameters()
    Clears the current parameter values immediately.
  • boolean execute()
    Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.
  • ResultSet executeQuery()
    Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
  • int executeUpdate()
    Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML)
    statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
  • ResultSetMetaData getMetaData()
    Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed.
  • ParameterMetaData getParameterMetaData()
    Retrieves the number, types and properties of this PreparedStatement object's parameters.
  • void setArray(int parameterIndex, Array x)
    Sets the designated parameter to the given java.
    • sql.Array object.
  • void setAsciiStream(int parameterIndex, InputStream x)
    Sets the designated parameter to the given input stream.
  • void setAsciiStream(int parameterIndex, InputStream x, int length)
    Sets the designated parameter to the given input stream, which will have the specified number of bytes.
  • void setAsciiStream(int parameterIndex, InputStream x, long length)
    Sets the designated parameter to the given input stream, which will have the specified number of bytes.
  • void setBigDecimal(int parameterIndex, BigDecimal x)
    Sets the designated parameter to the given java.
    • math.BigDecimal value.
  • void setBinaryStream(int parameterIndex, InputStream x)
    Sets the designated parameter to the given input stream.
  • void setBinaryStream(int parameterIndex, InputStream x, int length)
    Sets the designated parameter to the given input stream, which will have the specified number of bytes.
  • void setBinaryStream(int parameterIndex, InputStream x, long length)
    Sets the designated parameter to the given input stream, which will have the specified number of bytes.
  • void setBoolean(int parameterIndex, boolean x)
    Sets the designated parameter to the given Java boolean value.
  • void setByte(int parameterIndex, byte x)
    Sets the designated parameter to the given Java byte value.
  • void setBytes(int parameterIndex, byte[] x)
    Sets the designated parameter to the given Java array of bytes.
  • void setDate(int parameterIndex, Date x)
    Sets the designated parameter to the given java.
    • sql.Date value using the default time zone of the virtual machine that is running the application.
  • void setDouble(int parameterIndex, double x)
    Sets the designated parameter to the given Java double value.
  • void setFloat(int parameterIndex, float x)
    Sets the designated parameter to the given Java float value.
  • void setInt(int parameterIndex, int x)
    Sets the designated parameter to the given Java int value.
  • void setLong(int parameterIndex, long x)
    Sets the designated parameter to the given Java long value.
  • void setNull(int parameterIndex, int sqlType)
    Sets the designated parameter to SQL NULL.
  • void setNull(int parameterIndex, int sqlType, String typeName)
    Sets the designated parameter to SQL NULL.
  • void setObject(int parameterIndex, Object x)
    Sets the value of the designated parameter using the given object.
  • void setShort(int parameterIndex, short x)
    Sets the designated parameter to the given Java short value.
  • void setString(int parameterIndex, String x)
    Sets the designated parameter to the given Java String value.
  • void setTime(int parameterIndex, Time x)
    Sets the designated parameter to the given java.
    • sql.Time value.
  • void setTimestamp(int parameterIndex, Timestamp x)
    Sets the designated parameter to the given java.
    • sql.Timestamp value.
back to summary

ResultSet

The ResultSet object is returned by the querying methods in Statement and PreparedStatement

objects.

The ResultSet object has the following static fields:

  • static int CLOSE_CURSORS_AT_COMMIT
    The constant indicating that open ResultSet objects with this holdability will be closed when the current transaction is commited.
  • static int CONCUR_READ_ONLY
    The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.
  • static int CONCUR_UPDATABLE
    The constant indicating the concurrency mode for a ResultSet object that may be updated.
  • static int FETCH_FORWARD
    The constant indicating that the rows in a result set will be processed in a forward direction; first-to-last.
  • static int FETCH_REVERSE
    The constant indicating that the rows in a result set will be processed in a reverse direction; last-to-first.
  • static int FETCH_UNKNOWN
    The constant indicating that the order in which rows in a result set will be processed is unknown.
  • static int HOLD_CURSORS_OVER_COMMIT
    The constant indicating that open ResultSet objects with this holdability will remain open when the current transaction is commited.
  • static int TYPE_FORWARD_ONLY
    The constant indicating the type for a ResultSet object whose cursor may move only forward.
  • static int TYPE_SCROLL_INSENSITIVE
    The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet.
  • static int TYPE_SCROLL_SENSITIVE
    The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet.

The ResultSet object has the following methods:

  • boolean absolute(int row)
    Moves the cursor to the given row number in this ResultSet object.
  • void afterLast()
    Moves the cursor to the end of this ResultSet object, just after the last row.
  • void beforeFirst()
    Moves the cursor to the front of this ResultSet object, just before the first row.
  • void cancelRowUpdates()
    Cancels the updates made to the current row in this ResultSet object.
  • void clearWarnings()
    Clears all warnings reported on this ResultSet object.
  • void close()
    Releases this ResultSet object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.
  • void deleteRow()
    Deletes the current row from this ResultSet object and from the underlying database.
  • int findColumn(String columnLabel)
    Maps the given ResultSet column label to its ResultSet column index.
  • boolean first()
    Moves the cursor to the first row in this ResultSet object.
  • boolean getBoolean(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a boolean in the Java programming language.
  • boolean getBoolean(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a boolean in the Java programming language.
  • byte getByte(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a byte in the Java programming language.
  • byte getByte(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a byte in the Java programming language.
  • byte[] getBytes(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language.
  • byte[] getBytes(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language.
  • int getConcurrency()
    Retrieves the concurrency mode of this ResultSet object.
  • String getCursorName()
    Retrieves the name of the SQL cursor used by this ResultSet object.
  • Date getDate(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a java.
    • sql.Date object in the Java programming language.
  • Date getDate(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a java.
    • sql.Date object in the Java programming language.
  • double getDouble(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a double in the Java programming language.
  • double getDouble(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a double in the Java programming language.
  • int getFetchDirection()
    Retrieves the fetch direction for this ResultSet object.
  • int getFetchSize()
    Retrieves the fetch size for this ResultSet object.
  • float getFloat(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a float in the Java programming language.
  • float getFloat(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a float in the Java programming language.
  • int getHoldability()
    Retrieves the holdability of this ResultSet object
  • int getInt(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.
  • int getInt(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.
  • long getLong(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a long in the Java programming language.
  • long getLong(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a long in the Java programming language.
  • ResultSetMetaData getMetaData()
    Retrieves the number, types and properties of this ResultSet object's columns.
  • String getNString(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.
  • String getNString(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.
  • Object getObject(int columnIndex)
    Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.
  • int getRow()
    Retrieves the current row number.
  • short getShort(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a short in the Java programming language.
  • short getShort(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a short in the Java programming language.
  • Statement getStatement()
    Retrieves the Statement object that produced this ResultSet object.
  • String getString(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.
  • String getString(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.
  • Time getTime(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a java.
    • sql.Time object in the Java programming language.
  • Time getTime(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a java.
    • sql.Time object in the Java programming language.
  • Timestamp getTimestamp(int columnIndex)
    Retrieves the value of the designated column in the current row of this ResultSet object as a java.
    • sql.Timestamp object in the Java programming language.
  • Timestamp getTimestamp(String columnLabel)
    Retrieves the value of the designated column in the current row of this ResultSet object as a java.
    • sql.Timestamp object in the Java programming language.
  • int getType()
    Retrieves the type of this ResultSet object.
  • SQLWarning getWarnings()
    Retrieves the first warning reported by calls on this ResultSet object.
  • void insertRow()
    Inserts the contents of the insert row into this ResultSet object and into the database.
  • boolean isAfterLast()
    Retrieves whether the cursor is after the last row in this ResultSet object.
  • boolean isBeforeFirst()
    Retrieves whether the cursor is before the first row in this ResultSet object.
  • boolean isClosed()
    Retrieves whether this ResultSet object has been closed.
  • boolean isFirst()
    Retrieves whether the cursor is on the first row of this ResultSet object.
  • boolean isLast()
    Retrieves whether the cursor is on the last row of this ResultSet object.
  • boolean last()
    Moves the cursor to the last row in this ResultSet object.
  • void moveToCurrentRow()
    Moves the cursor to the remembered cursor position, usually the current row.
  • void moveToInsertRow()
    Moves the cursor to the insert row.
  • boolean next()
    Moves the cursor forward one row from its current position.
  • boolean previous()
    Moves the cursor to the previous row in this ResultSet object.
  • void refreshRow()
    Refreshes the current row with its most recent value in the database.
  • boolean relative(int rows)
    Moves the cursor a relative number of rows, either positive or negative.
  • boolean rowDeleted()
    Retrieves whether a row has been deleted.
  • boolean rowInserted()
    Retrieves whether the current row has had an insertion.
  • boolean rowUpdated()
    Retrieves whether the current row has been updated.
  • void setFetchDirection(int direction)
    Gives a hint as to the direction in which the rows in this ResultSet object will be processed.
  • void setFetchSize(int rows)
    Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object.
  • void updateArray(int columnIndex, Array x)
    Updates the designated column with a java.
    • sql.Array value.
  • void updateArray(String columnLabel, Array x)
    Updates the designated column with a java.
    • sql.Array value.
  • void updateAsciiStream(int columnIndex, InputStream x)
    Updates the designated column with an ascii stream value.
  • void updateAsciiStream(int columnIndex, InputStream x, int length)
    Updates the designated column with an ascii stream value, which will have the specified number of bytes.
  • void updateAsciiStream(int columnIndex, InputStream x, long length)
    Updates the designated column with an ascii stream value, which will have the specified number of bytes.
  • void updateAsciiStream(String columnLabel, InputStream x)
    Updates the designated column with an ascii stream value.
  • void updateAsciiStream(String columnLabel, InputStream x, int length)
    Updates the designated column with an ascii stream value, which will have the specified number of bytes.
  • void updateAsciiStream(String columnLabel, InputStream x, long length)
    Updates the designated column with an ascii stream value, which will have the specified number of bytes.
  • void updateBigDecimal(int columnIndex, BigDecimal x)
    Updates the designated column with a java.
    • math.BigDecimal value.
  • void updateBigDecimal(String columnLabel, BigDecimal x)
    Updates the designated column with a java.
    • sql.BigDecimal value.
  • void updateBinaryStream(int columnIndex, InputStream x)
    Updates the designated column with a binary stream value.
  • void updateBinaryStream(int columnIndex, InputStream x, int length)
    Updates the designated column with a binary stream value, which will have the specified number of bytes.
  • void updateBinaryStream(int columnIndex, InputStream x, long length)
    Updates the designated column with a binary stream value, which will have the specified number of bytes.
  • void updateBinaryStream(String columnLabel, InputStream x)
    Updates the designated column with a binary stream value.
  • void updateBinaryStream(String columnLabel, InputStream x, int length)
    Updates the designated column with a binary stream value, which will have the specified number of bytes.
  • void updateBinaryStream(String columnLabel, InputStream x, long length)
    Updates the designated column with a binary stream value, which will have the specified number of bytes.
  • void updateBoolean(int columnIndex, boolean x)
    Updates the designated column with a boolean value.
  • void updateBoolean(String columnLabel, boolean x)
    Updates the designated column with a boolean value.
  • void updateByte(int columnIndex, byte x)
    Updates the designated column with a byte value.
  • void updateByte(String columnLabel, byte x)
    Updates the designated column with a byte value.
  • void updateBytes(int columnIndex, byte[] x)
    Updates the designated column with a byte array value.
  • void updateBytes(String columnLabel, byte[] x)
    Updates the designated column with a byte array value.
  • void updateDate(int columnIndex, Date x)
    Updates the designated column with a java.
    • sql.Date value.
  • void updateDate(String columnLabel, Date x)
    Updates the designated column with a java.
    • sql.Date value.
  • void updateDouble(int columnIndex, double x)
    Updates the designated column with a double value.
  • void updateDouble(String columnLabel, double x)
    Updates the designated column with a double value.
  • void updateFloat(int columnIndex, float x)
    Updates the designated column with a float value.
  • void updateFloat(String columnLabel, float x)
    Updates the designated column with a float value.
  • void updateInt(int columnIndex, int x)
    Updates the designated column with an int value.
  • void updateInt(String columnLabel, int x)
    Updates the designated column with an int value.
  • void updateLong(int columnIndex, long x)
    Updates the designated column with a long value.
  • void updateLong(String columnLabel, long x)
    Updates the designated column with a long value.
  • void updateNString(int columnIndex, String nString)
    Updates the designated column with a String value.
  • void updateNString(String columnLabel, String nString)
    Updates the designated column with a String value.
  • void updateNull(int columnIndex)
    Updates the designated column with a null value.
  • void updateNull(String columnLabel)
    Updates the designated column with a null value.
  • void updateObject(int columnIndex, Object x)
    Updates the designated column with an Object value.
  • void updateObject(String columnLabel, Object x)
    Updates the designated column with an Object value.
  • void updateRow()
    Updates the underlying database with the new contents of the current row of this ResultSet object.
  • void updateRowId(int columnIndex, RowId x)
    Updates the designated column with a RowId value.
  • void updateShort(int columnIndex, short x)
    Updates the designated column with a short value.
  • void updateShort(String columnLabel, short x)
    Updates the designated column with a short value.
  • void updateString(int columnIndex, String x)
    Updates the designated column with a String value.
  • void updateString(String columnLabel, String x)
    Updates the designated column with a String value.
  • void updateTime(int columnIndex, Time x)
    Updates the designated column with a java.
    • sql.Time value.
  • void updateTime(String columnLabel, Time x)
    Updates the designated column with a java.
    • sql.Time value.
  • void updateTimestamp(int columnIndex, Timestamp x)
    Updates the designated column with a java.
    • sql.Timestamp value.
  • void updateTimestamp(String columnLabel, Timestamp x)
    Updates the designated column with a java.
    • sql.Timestamp value.
  • boolean wasNull()
    Reports whether the last column read had a value of SQL NULL.
back to summary

ResultSetMetaData

The ResultSetMetaData object is returned by the ResultSet.getMetaData function.

The ResultSetMetaData object has the following static fields:

  • static int columnNoNulls
    The constant indicating that a column does not allow NULL values.
  • static int columnNullable
    The constant indicating that a column allows NULL values.
  • static int columnNullableUnknown
    The constant indicating that the nullability of a column's values is unknown.

The ResultSetMetaData object has the following methods:

  • String getCatalogName(int column)
    Gets the designated column's table's catalog name.
  • String getColumnClassName(int column)
    Returns the fully-qualified name of the Java class whose instances are manufactured if the method ResultSet.
    • getObject is called to retrieve a value from the column.
  • int getColumnCount()
    Returns the number of columns in this ResultSet object.
  • int getColumnDisplaySize(int column)
    Indicates the designated column's normal maximum width in characters.
  • String getColumnLabel(int column)
    Gets the designated column's suggested title for use in printouts and displays.
  • String getColumnName(int column)
    Get the designated column's name.
  • int getColumnType(int column)
    Retrieves the designated column's SQL type.
  • String getColumnTypeName(int column)
    Retrieves the designated column's database-specific type name.
  • int getPrecision(int column)
    Get the designated column's specified column size.
  • int getScale(int column)
    Gets the designated column's number of digits to right of the decimal point.
  • String getSchemaName(int column)
    Get the designated column's table's schema.
  • String getTableName(int column)
    Gets the designated column's table name.
  • boolean isAutoIncrement(int column)
    Indicates whether the designated column is automatically numbered.
  • boolean isCaseSensitive(int column)
    Indicates whether a column's case matters.
  • boolean isCurrency(int column)
    Indicates whether the designated column is a cash value.
  • boolean isDefinitelyWritable(int column)
    Indicates whether a write on the designated column will definitely succeed.
  • int isNullable(int column)
    Indicates the nullability of values in the designated column.
  • boolean isReadOnly(int column)
    Indicates whether the designated column is definitely not writable.
  • boolean isSearchable(int column)
    Indicates whether the designated column can be used in a where clause.
  • boolean isSigned(int column)
    Indicates whether values in the designated column are signed numbers.
  • boolean isWritable(int column)
    Indicates whether it is possible for a write on the designated column to succeed.
back to summary

ParameterMetaData

The ParameterMetaData object is returned by the PreparedStatement.getParameterMetaData function.

The ParameterMetaData object has the following methods:

  • static int parameterModeIn
    The constant indicating that the parameter's mode is IN.
  • static int parameterModeInOut
    The constant indicating that the parameter's mode is INOUT.
  • static int parameterModeOut
    The constant indicating that the parameter's mode is OUT.
  • static int parameterModeUnknown
    The constant indicating that the mode of the parameter is unknown.
  • static int parameterNoNulls
    The constant indicating that a parameter will not allow NULL values.
  • static int parameterNullable
    The constant indicating that a parameter will allow NULL values.
  • static int parameterNullableUnknown
    The constant indicating that the nullability of a parameter is unknown.

The ParameterMedaData object has the following methods:

  • String getParameterClassName(int param)
    Retrieves the fully-qualified name of the Java class whose instances should be passed to the method PreparedStatement.
    • setObject.
  • int getParameterCount()
    Retrieves the number of parameters in the PreparedStatement object for which this ParameterMetaData object contains information.
  • int getParameterMode(int param)
    Retrieves the designated parameter's mode.
  • int getParameterType(int param)
    Retrieves the designated parameter's SQL type.
  • String getParameterTypeName(int param)
    Retrieves the designated parameter's database-specific type name.
  • int getPrecision(int param)
    Retrieves the designated parameter's specified column size.
  • int getScale(int param)
    Retrieves the designated parameter's number of digits to right of the decimal point.
  • int isNullable(int param)
    Retrieves whether null values are allowed in the designated parameter.
  • boolean isSigned(int param)
    Retrieves whether values for the designated parameter can be signed numbers.
back to summary

Savepoint

The Savepoint object is returned by the Connection.setSavepoint function.

The Savepoint object has the following methods:

  • int getSavepointId()
    Retrieves the generated ID for the savepoint that this Savepoint object represents.
  • String getSavepointName()
    Retrieves the name of the savepoint that this Savepoint object represents.
back to summary

Demo Code

jsdbc.js 


export("jdbc", 
       "query", "update", "execute", "insert", 
       "rsToList",
       "transaction"
);

var jdbc = require("erbix/query").jsdbc;

/** 
 * Add parameters to a preparedStatement.
 * @param stmt PreparedStatement object.
 * @param params array of parameters. The number of parameters
 * must be equal to the number of '?' in the prepared SQL string.
 * @return the PreparedStatement.
 */
var add = function(stmt, params) {
  if (params) {
    for (var i = 0; i < params.length; i++) {
      stmt.setObject(i + 1, params[i]);
    }
  }
  return stmt;
};
  
/**
 * Convert a ResultSet to an array of JS objects. The name of 
 * the columns become the keys of the objects.
 * @param rs ResultSet object.
 * @return an array of objects with the keys matching the column names.
 */
var rsToList = function(rs) {
  var result = [];
  var meta = rs.getMetaData();
  var n = meta.getColumnCount();
  var line = 0;
  while (rs.next()) {
    result[line] = {};
    for (var i = 1; i <= n; i++){
      result[line][meta.getColumnName(i)] = rs.getObject(i);
    }
    line++;
  }
  return result;
};

/**
 * Executes a query and returns an array of JS objects with the keys 
 * matching the result column names.
 * @param conn Connection object returned by a Query.getConnection() call.
 * @param sql SQL string which may contain '?' placeholders.
 * @param params an array of parameters.
 * @return an array of objects.
 */
this.query = function(conn, sql, params) {
  var stmt = add(conn.prepareStatement(sql), params);
  var rs = stmt.executeQuery();
  var list = rsToList(rs);
  rs.close();
  stmt.close();
  return list;
};

/**
  * Executes an update statement and returns 
  * 1) number of affected rows or 2) undefined.
  * @param conn Connection object returned by a Query.getConnection() call.
  * @param sql SQL string which may contain '?' placeholders.
  * @param params an array of parameters.
  * @return 1) number of affected rows or 2) undefined.
  */
var update = function(conn, sql, params) {
  var stmt = add(conn.prepareStatement(sql), params);
  var result = stmt.executeUpdate();
  stmt.close();
  return result;
};

/**
  * Executes an insert statement and returns an array of objects with 
  * the auto-generated fields.
  * @param conn Connection object returned by a Query.getConnection() call.
  * @param sql SQL string which may contain '?' placeholders.
  * @param params an array of parameters.
  * @return an array of objects with the keys matching the names of the 
  * auto-generated columns.
  */
var insert = function(conn, sql, params) {
  var stmt = add(conn.prepareStatement(sql, 
           jdbc.Statement.RETURN_GENERATED_KEYS), params);
  if (stmt.executeUpdate() > 0) {
    var rs = stmt.getGeneratedKeys();
    var list = rsToList(rs);
    rs.close();
    stmt.close();
    return list;
  }
  stmt.close();
  return [];
};

/**
  * Executes a statement that may return any number of ResultSet objects.
  * @param conn Connection object returned by a Query.getConnection() call.
  * @param sql SQL string which may contain '?' placeholders.
  * @param params an array of parameters.
  * @return an array of arrays of objects.
  */
var execute = function(conn, sql, params) {
  var result = [];
  var stmt = add(conn.prepareStatement(sql), params);
  stmt.execute();
  var rs = stmt.getResultSet();
  if (rs != null) {
    var r = 0;
    result[r++] = rsToList(rs);
    // Close the previos ResultSet and point to the next.
    while (stmt.getMoreResults()) {
      result[r++] = rsToList(stmt.getResultSet());
    }
  }
  stmt.close();
  return result;
};


/**
  * Run a function in a transaction.
  * @param func function with an input parameter of type Connection.
  * @return a function that executes the input 
  * function with a database transaction.
  */
 var transaction = function(func) {
   return function() {
    var result = null;
     try {
       // Get a new Connection.
       var conn = require("erbix/query").getConnection()
       conn.setAutoCommit(false);
       result = func(conn) || 0;
      // Nothing happened, commit the changes to the database.
      conn.commit();
     } catch(ex) {
      // Error: rollback changes.
       if (conn != null && !conn.isClosed()) {
         conn.rollback();
         conn.close();
       }
     } finally {
       if (conn != null && !conn.isClosed()) {
        // Make sure the conection is closed.
         conn.setAutoCommit(true);
         conn.close();
       }
      return result;
     }
   };
 };
 // end of jsdbc.js

In some other module in the same folder as jsdbc.js: 



var myDbFunction = require("./jsdbc").transaction(function(conn) {
    var jsdbc = require("./jsdbc");
    var list = jsdbc.query(conn, "SELECT version() AS v;", []);
    // Do any number of database updates in a transaction...
    // ...
    
    // Return the PostgreSQL version.
    return list[0].v;
  });


var main = function(req) {
 return {
      status: 200,
      headers: {
        "Content-Type": "text/plain"
      },
      body: ["Version: " + myDbFunction()]
    };
};
back to summary