/*
* $Id: SQLUtil.java,v 1.19 2002/09/16 08:05:07 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.util;
import anvil.core.Any;
import anvil.core.AnyBinary;
import anvil.core.AnyString;
import anvil.core.time.AnyCalendar;
import anvil.script.Context;
import java.io.IOException;
import java.io.InputStream;
import java.io.ByteArrayInputStream;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.util.Calendar;
/**
* class SQLUtil
*
* @author: Jani Lehtim�ki
*/
public class SQLUtil
{
public static final int MAGIC_LIMIT = 512;
public static final String buildQueryString(String query, Any[] parameters, int offset)
{
if (query.indexOf('?')>-1) {
int n = query.length();
int param = offset;
int paramCount = parameters.length;
char ch;
Any data;
StringBuffer buffer = new StringBuffer(1 + n + n/2);
int i = 0;
while(i<n) {
switch((ch = query.charAt(i++))) {
case '?':
if (i<n && query.charAt(i) == '?') {
buffer.append('?');
i++;
break;
}
data = ((parameters != null) && (param < paramCount)) ? parameters[param++] : Any.NULL;
switch(data.typeOf()) {
case Any.IS_NULL:
case Any.IS_UNDEFINED:
buffer.append("null");
break;
case Any.IS_BOOLEAN:
buffer.append(data.toBoolean() ? "'true'" : "'false'");
break;
case Any.IS_INT:
buffer.append(data.toLong());
break;
case Any.IS_DOUBLE:
buffer.append(data.toDouble());
break;
case Any.IS_STRING:
case Any.IS_BUFFER:
case Any.IS_BINARY:
buffer.append('\'');
Conversions.escape(buffer, data.toString(), true);
buffer.append('\'');
break;
default:
buffer.append("null");
break;
}
break;
case '$':
if (i<n && query.charAt(i) == '$') {
buffer.append('$');
i++;
} else {
data = ((parameters != null) && (param < paramCount)) ? parameters[param++] : Any.NULL;
buffer.append(data.toString());
}
break;
default:
buffer.append(ch);
}
}
return buffer.toString();
} else {
return query;
}
}
public static final Any query(Connection connection, String query, Any[] parameters)
{
try {
query = buildQueryString(query, parameters, 0);
Statement stmt = connection.createStatement();
} catch (SQLException e) {
}
return Any.NULL;
}
public static Any getField(ResultSet set, ResultSetMetaData meta, int field) throws SQLException
{
if ((field >= 1) && (field <= meta.getColumnCount())) {
switch(meta.getColumnType(field)) {
case Types.NULL:
return Any.NULL;
case Types.BIT:
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
long l = set.getLong(field);
return set.wasNull() ? Any.NULL : Any.create(l);
case Types.NUMERIC:
case Types.DECIMAL:
case Types.DOUBLE:
case Types.FLOAT:
case Types.REAL:
double d = set.getDouble(field);
return set.wasNull() ? Any.NULL : Any.create(d);
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
return Any.create(set.getString(field));
case Types.TIME:
{
Time time = set.getTime(field);
if (time != null) {
Context context = Context.getInstance();
Calendar cal = Calendar.getInstance(context.getTimeZone(), context.getLocale());
cal.setTime(time);
return new AnyCalendar(cal);
} else {
return Any.NULL;
}
}
case Types.TIMESTAMP:
{
Timestamp time = set.getTimestamp(field);
if (time != null) {
Context context = Context.getInstance();
Calendar cal = Calendar.getInstance(context.getTimeZone(), context.getLocale());
cal.setTime(time);
return new AnyCalendar(cal);
} else {
return Any.NULL;
}
}
case Types.DATE:
{
Date date = set.getDate(field);
if (date != null) {
Context context = Context.getInstance();
Calendar cal = Calendar.getInstance(context.getTimeZone(), context.getLocale());
cal.setTime(date);
return new AnyCalendar(cal);
} else {
return Any.NULL;
}
}
case Types.BLOB:
Blob blob = set.getBlob(field);
if (blob != null) {
return new AnyString(new String(blob.getBytes(0, (int)blob.length())));
} else {
return Any.NULL;
}
case Types.CLOB:
Clob clob = set.getClob(field);
return Any.create(clob.getSubString(1, (int)clob.length()));
case Types.JAVA_OBJECT:
return Any.create(set.getObject(field));
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
byte[] bytes = set.getBytes(field);
if (bytes != null) {
return new AnyBinary(bytes);
} else {
return Any.NULL;
}
case Types.REF:
return Any.create(set.getRef(field));
case Types.DISTINCT:
case Types.STRUCT:
case Types.ARRAY:
case Types.OTHER:
return Any.NULL;
default:
return Any.NULL;
}
} else {
return Any.NULL;
}
}
public static void setField(PreparedStatement set, int field, Any data) throws SQLException
{
switch(data.typeOf()) {
case Any.IS_NEG_INF:
set.setDouble(field, Double.NEGATIVE_INFINITY);
break;
case Any.IS_INF:
set.setDouble(field, Double.POSITIVE_INFINITY);
break;
case Any.IS_UNDEFINED:
case Any.IS_NULL:
set.setNull(field, Types.CHAR);
break;
case Any.IS_BOOLEAN:
set.setBoolean(field, data.toBoolean());
break;
case Any.IS_INT:
set.setLong(field, data.toLong());
break;
case Any.IS_DOUBLE:
set.setDouble(field, data.toDouble());
break;
case Any.IS_STRING:
case Any.IS_BUFFER:
{
String str = data.toString();
int length = str.length();
if (length > MAGIC_LIMIT) {
set.setBinaryStream(field, new ByteArrayInputStream(
Conversions.getBytes(str)), length);
} else {
set.setString(field, data.toString());
}
}
break;
case Any.IS_BINARY:
{
byte[] bytes = data.toBinary();
int length = bytes.length;
if (length > MAGIC_LIMIT) {
set.setBinaryStream(field, new ByteArrayInputStream(bytes), length);
} else {
set.setBytes(field, data.toBinary());
}
}
break;
case Any.IS_PATTERN:
case Any.IS_MAP:
case Any.IS_RANGE:
case Any.IS_ENUMERATION:
case Any.IS_TUPLE:
case Any.IS_LIST:
case Any.IS_ARRAY:
case Any.IS_REF:
set.setString(field, data.toString());
break;
case Any.IS_CLASS:
default:
if (data instanceof anvil.core.io.AnyInputStream) {
try {
InputStream input = (InputStream)data.toObject();
set.setBinaryStream(field, input, input.available());
} catch (IOException e) {
throw new SQLException("Exception while setting binary stream: "+e);
}
} else {
set.setString(field, data.toString());
}
break;
}
}
public static Any getField(CallableStatement stmt, ResultSetMetaData meta, int field) throws SQLException
{
if ((field >= 1) && (field <= meta.getColumnCount())) {
switch(meta.getColumnType(field)) {
case Types.NULL:
return Any.NULL;
case Types.BIT:
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
long l = stmt.getLong(field);
return stmt.wasNull() ? Any.NULL : Any.create(l);
case Types.NUMERIC:
case Types.DECIMAL:
case Types.DOUBLE:
case Types.FLOAT:
case Types.REAL:
double d = stmt.getDouble(field);
return stmt.wasNull() ? Any.NULL : Any.create(d);
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
return Any.create(stmt.getString(field));
case Types.TIME:
{
Time time = stmt.getTime(field);
if (time != null) {
Context context = Context.getInstance();
Calendar cal = Calendar.getInstance(context.getTimeZone(), context.getLocale());
cal.setTime(time);
return new AnyCalendar(cal);
} else {
return Any.NULL;
}
}
case Types.TIMESTAMP:
{
Timestamp time = stmt.getTimestamp(field);
if (time != null) {
Context context = Context.getInstance();
Calendar cal = Calendar.getInstance(context.getTimeZone(), context.getLocale());
cal.setTime(time);
return new AnyCalendar(cal);
} else {
return Any.NULL;
}
}
case Types.DATE:
{
Date date = stmt.getDate(field);
if (date != null) {
Context context = Context.getInstance();
Calendar cal = Calendar.getInstance(context.getTimeZone(), context.getLocale());
cal.setTime(date);
return new AnyCalendar(cal);
} else {
return Any.NULL;
}
}
case Types.BLOB:
Blob blob = stmt.getBlob(field);
if (blob != null) {
return new AnyString(new String(blob.getBytes(0, (int)blob.length())));
} else {
return Any.NULL;
}
case Types.CLOB:
Clob clob = stmt.getClob(field);
return Any.create(clob.getSubString(1, (int)clob.length()));
case Types.JAVA_OBJECT:
return Any.create(stmt.getObject(field));
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
byte[] bytes = stmt.getBytes(field);
if (bytes != null) {
return new AnyBinary(bytes);
} else {
return Any.NULL;
}
case Types.REF:
return Any.create(stmt.getRef(field));
case Types.DISTINCT:
case Types.STRUCT:
case Types.ARRAY:
case Types.OTHER:
return Any.NULL;
default:
return Any.NULL;
}
} else {
return Any.NULL;
}
}
}