/*
* $Id: SQLModule.java,v 1.30 2002/09/16 08:05:03 jkl Exp $
*
* Copyright (c) 2002 Njet Communications Ltd. All Rights Reserved.
*
* Use is subject to license terms, as defined in
* Anvil Sofware License, Version 1.1. See LICENSE
* file, or http://njet.org/license-1.1.txt
*/
package anvil.core.sql;
import anvil.core.Any;
import anvil.database.CannotReturnPooledConnectionException;
import anvil.database.PooledConnection;
import anvil.database.ConnectionManager;
import anvil.database.NoConnectionPoolException;
import anvil.script.Context;
import anvil.util.SQLUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DriverManager;
///
/// @module anvil.sql
/// Provides access to relation databases through the JDBC interface.
public class SQLModule
{
public static final Any BIT = Any.create(java.sql.Types.BIT);
public static final Any TINYINT = Any.create(java.sql.Types.TINYINT);
public static final Any SMALLINT = Any.create(java.sql.Types.SMALLINT);
public static final Any INTEGER = Any.create(java.sql.Types.INTEGER);
public static final Any BIGINT = Any.create(java.sql.Types.BIGINT);
public static final Any FLOAT = Any.create(java.sql.Types.FLOAT);
public static final Any REAL = Any.create(java.sql.Types.REAL);
public static final Any DOUBLE = Any.create(java.sql.Types.DOUBLE);
public static final Any NUMERIC = Any.create(java.sql.Types.NUMERIC);
public static final Any DECIMAL = Any.create(java.sql.Types.DECIMAL);
public static final Any CHAR = Any.create(java.sql.Types.CHAR);
public static final Any VARCHAR = Any.create(java.sql.Types.VARCHAR);
public static final Any LONGVARCHAR = Any.create(java.sql.Types.LONGVARCHAR);
public static final Any DATE = Any.create(java.sql.Types.DATE);
public static final Any TIME = Any.create(java.sql.Types.TIME);
public static final Any TIMESTAMP = Any.create(java.sql.Types.TIMESTAMP);
public static final Any BINARY = Any.create(java.sql.Types.BINARY);
public static final Any VARBINARY = Any.create(java.sql.Types.VARBINARY);
public static final Any LONGVARBINARY = Any.create(java.sql.Types.LONGVARBINARY);
public static final Any NULL = Any.create(java.sql.Types.NULL);
public static final Any OTHER = Any.create(java.sql.Types.OTHER);
public static final Any JAVA_OBJECT = Any.create(java.sql.Types.JAVA_OBJECT);
public static final Any DISTINCT = Any.create(java.sql.Types.DISTINCT);
public static final Any STRUCT = Any.create(java.sql.Types.STRUCT);
public static final Any ARRAY = Any.create(java.sql.Types.ARRAY);
public static final Any BLOB = Any.create(java.sql.Types.BLOB);
public static final Any CLOB = Any.create(java.sql.Types.CLOB);
public static final Any REF = Any.create(java.sql.Types.REF);
private static final PooledConnection acquireConnection(Context context, String ckey, boolean autoCommit, boolean readOnly)
{
String catalog = null;
int i = ckey.indexOf(':');
if (i>0) {
catalog = ckey.substring(i+1);
ckey = ckey.substring(0, i);
}
PooledConnection connImpl = null;
Connection conn = null;
try {
ConnectionManager manager = context.address().getZone().getManagerFor(ckey);
connImpl = manager.acquire(ckey);
conn = (Connection)connImpl.getConnection();
if (catalog != null) {
conn.setCatalog(catalog);
}
conn.setAutoCommit(autoCommit);
conn.setReadOnly(readOnly);
return connImpl;
} catch (CannotReturnPooledConnectionException e) {
throw context.AcquireError(e.getMessage());
} catch (NoConnectionPoolException e) {
throw context.AcquireError("No such pool: " + ckey);
} catch (SQLException e) {
if (conn != null) {
try {
conn.close();
} catch (Exception e2) {
}
}
connImpl.release();
throw context.exception(e);
}
}
/// @function query
/// Executes a query in a given connection which is acquired from
/// configured pool named 'poolName'. 'poolName' may also contains database
/// catalog, separated with colon from the name of pool. For instance,
/// <code>"mypool:mycatalog"</code>.
///
/// <p>'querystring' is parsed so that given parameters are embedded to
/// placeholders in string.</p>
/// Placeholders:<br>
/// <blockquote>
/// <strong>?</strong> Embeds next parameter according to
/// its type (i.e. strings are escaped and quoted)<br>
/// <strong>$</strong> Embeds next parameter as it is
/// (result of tostring())<br>
/// </blockquote>
///
/// @synopsis object query(string poolName, string querystring, [ object parameters, ... ] )
/// @param poolName Name of connection pool, may contain database catalog
/// @param querystring query string
/// @param parameters to be embedded
/// @return <code>null</code> if error occurred, <code>ResultSet</code>
/// if 'querystring' was an select query (or similar),
/// otherwise returns update count as integer.
/// @throws AcquireError If connection could not be retrieved
/// @throws SQLError If SQL error occured
public static final Any query(Context context, String ckey, String query, Any[] parameters)
{
PooledConnection connImpl = acquireConnection(context, ckey, true, false);
if (connImpl == null) {
return Any.NULL;
}
Connection conn = (Connection)connImpl.getConnection();
try {
Statement stmt = conn.createStatement();
stmt.setEscapeProcessing(false);
String queryString;
if (parameters.length == 0) {
queryString = query;
} else {
queryString = SQLUtil.buildQueryString(query, parameters, 0);
}
Any returnValue;
if (stmt.execute(queryString)) {
returnValue = new AnyResultSet(stmt.getResultSet(), stmt);
} else {
returnValue = Any.create(stmt.getUpdateCount());
stmt.close();
}
return returnValue;
} catch (SQLException e) {
try {
conn.close();
} catch (Exception e2) {
}
throw context.exception(e);
} finally {
connImpl.release();
}
}
/// @function acquire
/// Acquires connection explicitly.
/// 'poolName' has same rules as in query function.
/// @synopsis Connection acquire(string poolName, [ boolean autoCommit, boolean readOnly ])
/// @param poolName Name of connection pool, may contain database catalog
/// @param autoCommit auto commit
/// @param readOnly read only
/// @return Acquired connection
/// @throws AcquireError If connection could not be retrieved
/// @throws SQLError If SQL error occured
public static final Object[] p_acquire = { null, "key", "*autoCommit", Boolean.TRUE, "*readOnly", Boolean.FALSE };
public static final Any acquire(Context context, String ckey, boolean autoCommit, boolean readOnly)
{
PooledConnection connImpl = acquireConnection(context, ckey, autoCommit, readOnly);
if (connImpl == null) {
return Any.NULL;
} else {
return new AnyConnection(connImpl);
}
}
/// @function connect
/// Creates connection to some database. Returned connection must
/// be close with <code>Connection.close</code> method since connections
/// returned are not pooled.
/// @synopsis Connection connect(string url, string username, string password)
/// @throws SQLError If SQL error occured
public static final Object[] p_connect = { null, "url", "username", "password" };
public static final Any connect(Context context, String url, String username, String password)
{
try {
Connection connection =
DriverManager.getConnection(url, username, password);
return new AnyConnection(connection);
} catch (SQLException e) {
throw context.exception(e);
}
}
public static final anvil.script.compiler.NativeNamespace __module__ =
new anvil.script.compiler.NativeNamespace(
"sql",
SQLModule.class,
new Class[] {
anvil.core.sql.oracle.OracleModule.class,
AnyConnection.class,
AnyResultSet.class,
AnyMetaData.class,
AnyStatement.class,
anvil.core.Throwables.SQLError.class,
},
//DOC{{
""+
" \n" +
" @module anvil.sql\n" +
" Provides access to relation databases through the JDBC interface.\n" +
" @function query\n" +
" Executes a query in a given connection which is acquired from\n" +
" configured pool named 'poolName'. 'poolName' may also contains database\n" +
" catalog, separated with colon from the name of pool. For instance,\n" +
" <code>\"mypool:mycatalog\"</code>.\n" +
"\n" +
" <p>'querystring' is parsed so that given parameters are embedded to\n" +
" placeholders in string.</p>\n" +
" Placeholders:<br>\n" +
" <blockquote>\n" +
" <strong>?</strong> Embeds next parameter according to \n" +
" its type (i.e. strings are escaped and quoted)<br>\n" +
" <strong>$</strong> Embeds next parameter as it is \n" +
" (result of tostring())<br>\n" +
" </blockquote>\n" +
"\n" +
" @synopsis object query(string poolName, string querystring, [ object parameters, ... ] )\n" +
" @param poolName Name of connection pool, may contain database catalog\n" +
" @param querystring query string\n" +
" @param parameters to be embedded\n" +
" @return <code>null</code> if error occurred, <code>ResultSet</code>\n" +
" if 'querystring' was an select query (or similar),\n" +
" otherwise returns update count as integer.\n" +
" @throws AcquireError If connection could not be retrieved\n" +
" @throws SQLError If SQL error occured\n" +
" @function acquire\n" +
" Acquires connection explicitly. \n" +
" 'poolName' has same rules as in query function.\n" +
" @synopsis Connection acquire(string poolName, [ boolean autoCommit, boolean readOnly ])\n" +
" @param poolName Name of connection pool, may contain database catalog\n" +
" @param autoCommit auto commit\n" +
" @param readOnly read only\n" +
" @return Acquired connection\n" +
" @throws AcquireError If connection could not be retrieved\n" +
" @throws SQLError If SQL error occured\n" +
" @function connect\n" +
" Creates connection to some database. Returned connection must\n" +
" be close with <code>Connection.close</code> method since connections\n" +
" returned are not pooled.\n" +
" @synopsis Connection connect(string url, string username, string password)\n" +
" @throws SQLError If SQL error occured\n"
//}}DOC
);
}