ch.rgw.tools.JdbcLink.java Source code

Java tutorial

Introduction

Here is the source code for ch.rgw.tools.JdbcLink.java

Source

/*******************************************************************************
 * Copyright (c) 2005-2011, G. Weirich and Elexis
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 *
 * Contributors:
 *    G. Weirich - initial implementation
 *    
 *******************************************************************************/

package ch.rgw.tools;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashMap;
import java.util.Properties;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;
import java.util.logging.Level;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
 * Weiterer Abstraktionslayer zum einfacheren Zugriff auf eine jdbc-fhige Datenbank
 */

public class JdbcLink {
    public static final String getVersion() {
        return "3.2.1";
    }

    public int lastErrorCode;
    public String lastErrorString;
    public int verMajor = 0;
    public int verMinor = 0;
    public String DBFlavor = null;
    private String sDrv;
    private String sConn;
    private String sUser;
    private String sPwd;

    private PoolingDataSource dataSource;
    private GenericObjectPool<Connection> connectionPool;
    // prepared statements are not released properly up until now, so keep 1 connection open
    private Connection preparedStatementConnection;

    private int keepAliveCount;
    private Timer keepAliveTimer = new Timer();

    private class KeepAliveTask extends TimerTask {

        private Connection connection;
        private PreparedStatement keapAliveStatement;

        public KeepAliveTask(Connection connection) throws SQLException {
            this.connection = connection;
            this.keapAliveStatement = connection.prepareStatement(VALIDATION_QUERY);
        }

        @Override
        public void run() {
            try {
                keapAliveStatement.execute();
            } catch (SQLException e) {
                lastErrorCode = CONNECTION_SQL_ERROR;
                lastErrorString = e.getMessage();
                keepAliveTimer.cancel();
                throw JdbcLinkExceptionTranslation.translateException(lastErrorString, e);
            }
        }
    }

    PreparedStatement preparedStatementKeepAlive;

    private static Log log;

    public static final int CONNECT_SUCCESS = 0;
    public static final int CONNECT_CLASSNOTFOUND = 1;
    public static final int CONNECT_FAILED = 2;
    public static final int CONNECT_UNKNOWN_ERROR = 10;
    public static final int TRANSACTION_COMMIT_FAILED = 21;
    public static final int TRANSACTION_ROLLBACK_FAILED = 22;
    public static final int TRANSACTION_COMMIT_NOT_SUPPORTED = 23;
    public static final int CONNECTION_CANT_CREATE_STATEMENT = 30;
    public static final int CONNECTION_CANT_PREPARE_STAMENT = 31;
    public static final int CONNECTION_SQL_ERROR = 40;

    public static final String DBFLAVOR_MYSQL = "mysql";
    public static final String DBFLAVOR_POSTGRESQL = "postgresql";
    public static final String DBFLAVOR_H2 = "h2";

    public static final String VALIDATION_QUERY = "SELECT 1;";

    static {
        log = Log.get("jdbcLink");
    }

    @SuppressWarnings("unused")
    private JdbcLink() { /* intentionally blank */
    }

    /**
     * Bequemlichkeitsmethode, um einen JdcbLink auf eine MySQL-Datenbank zu erhalten
     */
    public static JdbcLink createMySqlLink(String host, String database) {
        log.log(Level.INFO, "Creating MySQL-Link");
        String driver = "com.mysql.jdbc.Driver";
        String[] hostdetail = host.split(":");
        String hostname = hostdetail[0];
        String hostport = hostdetail.length > 1 ? hostdetail[1] : "3306";
        String connect = "jdbc:mysql://" + hostname + ":" + hostport + "/" + database;
        return new JdbcLink(driver, connect, DBFLAVOR_MYSQL);
    }

    /**
     * Bequemlichkeitsmethode, um einen JdbcLink auf eine InProcess laufende HSQL-Datenbank zu
     * erhalten
     * 
     * @param database
     *            ein Dateiname fr die zu erzeugende bzw. zu verwendende Datenbank
     */
    public static JdbcLink createInProcHsqlDBLink(String database) {
        log.log(Level.INFO, "Creating HSQL-In-Proc-Link");
        String driver = "org.hsqldb.jdbcDriver";
        String connect = "jdbc:hsqldb:" + database;
        return new JdbcLink(driver, connect, "hsqldb");
    }

    /**
     * Bequemlichkeitsmethode, um einen JdbcLink auf eine als Server laufende HSQL-Datenbank zu
     * erhalten
     * 
     * @param host
     *            Server, auf dem die Datenbank luft.
     */
    public static JdbcLink createHsqlDBLink(String host) {
        log.log(Level.INFO, "Creating HSQL-Link");
        String driver = "org.hsqldb.jdbcDriver";
        String connect = "jdbc:hsqldb:hsql://" + host;
        return new JdbcLink(driver, connect, "hsqldb");
    }

    /**
     * Bequemlichkeitsmethode, um einen Link auf eine H2-Datenbank zu bekommen. Da der
     * mysql-compatibility-mode fr ALTER commands nicht korrekt funktioniert, wird ein h2 DBFlavor
     * fr die bersetzung der Statements bergeben.
     * 
     * @param database
     * @return
     */
    public static JdbcLink createH2Link(String database) {
        log.log(Level.INFO, "Creating H2-Link");
        String driver = "org.h2.Driver";
        String prefix = "jdbc:h2:";
        if (database.contains(".zip!")) {
            prefix += "zip:";
        }
        String connect = prefix + database + ";AUTO_SERVER=TRUE";
        return new JdbcLink(driver, connect, DBFLAVOR_H2);
    }

    /**
     * Bequemlichkeitsmethode fr einen JdbcLink auf einen 4D-Server
     * 
     * @param host
     *            de Server, auf dem die 4D-Datenbnak luft
     * @return
     */
    public static JdbcLink create4DLink(String host) {
        log.log(Level.INFO, "Creating 4D-Link");
        String driver = "com.fourd.jdbc.DriverImpl";
        String connect = "jdbc:4d:" + host + ":19813";
        return new JdbcLink(driver, connect, "4d");
    }

    /**
     * Bequemlichkeitsmethode fr einen JdbcLink auf einen PostgreSQL- Server
     * 
     * @param host
     * @return
     */
    public static JdbcLink createPostgreSQLLink(String host, String database) {
        log.log(Level.INFO, "Creating PostgreSQL-Link");
        String driver = "org.postgresql.Driver";
        String[] hostdetail = host.split(":");
        String hostname = hostdetail[0];
        String hostport = hostdetail.length > 1 ? hostdetail[1] : "5432";

        String connect = "jdbc:postgresql://" + hostname + ":" + hostport + "/" + database;
        return new JdbcLink(driver, connect, DBFLAVOR_POSTGRESQL);
    }

    public static JdbcLink createODBCLink(String dsn) {
        log.log(Level.INFO, "Creating ODBC-Link");
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String connect = "jdbc:odbc:" + dsn;
        return new JdbcLink(driver, connect, "ODBC");
    }

    /**
     * Erstelle einen neuen jdbcLink. Es wird kein Connect-Versuch gemacht, das heisst, der
     * Konstruktor wird nie scheitern.
     * 
     * @param driver
     *            Treiber-String (wie org.hsql.jdbc)
     * @param connect
     *            Connect-String (wie jdbc:odbc:data)
     */
    public JdbcLink(String driver, String connect, String flavor) {
        sDrv = driver;
        sConn = connect;
        DBFlavor = flavor.toLowerCase();
    }

    /**
     * Verbindung zur Datenbank herstellen
     * 
     * TODO return value is always true because exception is thrown on error
     * 
     * @param user
     *            Username, kann null sein
     * @param password
     *            Passwort, kann null sein
     * @return errcode
     * 
     * @throws JdbcLinkException
     */
    public boolean connect(String user, String password) {
        Exception cause = null;
        try {
            sUser = user;
            sPwd = password;
            Driver driver = (Driver) Class.forName(sDrv).newInstance();
            verMajor = driver.getMajorVersion();
            verMinor = driver.getMinorVersion();

            log.log(Level.INFO, "Loading database driver " + sDrv);
            log.log(Level.INFO, "Connecting with database " + sConn);

            //
            // First, we'll create a ConnectionFactory that the
            // pool will use to create Connections.
            //
            Properties properties = new Properties();
            properties.put("user", user);
            properties.put("password", password);

            ConnectionFactory connectionFactory = new DriverConnectionFactory(driver, sConn, properties);
            //
            // Next we'll create the PoolableConnectionFactory, which wraps
            // the "real" Connections created by the ConnectionFactory with
            // the classes that implement the pooling functionality.
            //
            connectionPool = new GenericObjectPool<Connection>(null);
            // configure the connection pool
            connectionPool.setMaxActive(32);
            connectionPool.setMinIdle(2);
            connectionPool.setMaxWait(10000);
            connectionPool.setTestOnBorrow(true);

            new PoolableConnectionFactory(connectionFactory, connectionPool, null, VALIDATION_QUERY, false, true);
            dataSource = new PoolingDataSource(connectionPool);

            // test establishing a connection
            Connection conn = dataSource.getConnection();
            conn.close();

            lastErrorCode = CONNECT_SUCCESS;
            lastErrorString = "Connect successful";
            log.log("Connect successful", Log.DEBUGMSG);
            return true;
        } catch (ClassNotFoundException ex) {
            lastErrorCode = CONNECT_CLASSNOTFOUND;
            lastErrorString = "Class not found exception: " + ex.getMessage();
            cause = ex;
        } catch (InstantiationException e) {
            lastErrorCode = CONNECT_UNKNOWN_ERROR;
            lastErrorString = "Instantiation exception: " + e.getMessage();
            cause = e;
        } catch (IllegalAccessException e) {
            lastErrorCode = CONNECT_UNKNOWN_ERROR;
            lastErrorString = "Illegal access exception: " + e.getMessage();
            cause = e;
        } catch (SQLException e) {
            lastErrorCode = CONNECT_UNKNOWN_ERROR;
            lastErrorString = "SQL exception: " + e.getMessage();
            cause = e;
        } catch (IllegalStateException e) {
            lastErrorCode = CONNECT_UNKNOWN_ERROR;
            lastErrorString = "Illegal state exception: " + e.getMessage();
            cause = e;
        }
        throw JdbcLinkExceptionTranslation.translateException("Connect failed: " + lastErrorString, cause);
    }

    /**
     * Utility-Funktion zum Einpacken von Strings in Hochkommata und escapen illegaler Zeichen
     * 
     * @param s
     *            der String
     * @return Datenbankkonform eingepackte String
     */
    public static String wrap(String s) {
        if (StringTool.isNothing(s)) {
            return "''";
        }
        try {
            return wrap(s.getBytes("UTF-8"), DBFLAVOR_MYSQL);
        } catch (UnsupportedEncodingException e) {
            ExHandler.handle(e);
            return wrap(s.getBytes(), DBFLAVOR_MYSQL);
        }
    }

    public String wrapFlavored(String s) {
        if (StringTool.isNothing(s)) {
            return "''";
        }
        try {
            return wrap(s.getBytes("UTF-8"), DBFlavor);
        } catch (UnsupportedEncodingException e) {
            ExHandler.handle(e);
            return wrap(s.getBytes(), DBFlavor);
        }
    }

    /**
     * Utility-Funktion zum Datenbankkonformen Verpacken von byte arrays zwecks Einfgen in
     * BLOB-Felder.
     * 
     * @param flavor
     *            TODO
     * @param b
     *            das rohe byte array
     * @return das verpackte array in Form eines String
     */
    public static String wrap(byte[] in, String flavor) {

        byte[] out = new byte[2 * in.length + 2];
        int j = 0;
        out[j++] = '\'';
        for (int i = 0; i < in.length; i++) {
            switch (in[i]) {
            case 0:
            case 34:

            case '\'':
                if (flavor.startsWith(DBFLAVOR_POSTGRESQL) || flavor.startsWith("hsql")) {
                    out[j++] = '\'';
                    break;
                }
            case 92:
                boolean before = (i > 1 && in[i - 1] == 92);
                boolean after = (i < in.length - 1 && in[i + 1] == 92);
                if (!before && !after) {
                    out[j++] = '\\';
                }
            }
            out[j++] = in[i];
        }
        out[j++] = '\'';
        try {
            return new String(out, 0, j, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            ExHandler.handle(e);
            return null;
        }
    }

    public Connection getKeepAliveConnection() {
        log.log(Level.INFO, "Creating new keep alive connection [" + keepAliveCount + "]");
        Connection conncetion;
        try {
            conncetion = dataSource.getConnection();
            keepAliveTimer.scheduleAtFixedRate(new KeepAliveTask(conncetion), 5000, 5000);
        } catch (SQLException ex) {
            lastErrorCode = CONNECT_FAILED;
            lastErrorString = "SQL exception: " + ex.getMessage();
            throw JdbcLinkExceptionTranslation.translateException("Connect failed: " + lastErrorString, ex);
        }
        return conncetion;
    }

    /**
     * This method is deprecated. Use the methods getStatement and releaseStatement instead.
     * 
     * @deprecated
     * @return
     */
    public Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException ex) {
            lastErrorCode = CONNECT_FAILED;
            lastErrorString = "SQL exception: " + ex.getMessage();
            throw JdbcLinkExceptionTranslation.translateException("Connect failed: " + lastErrorString, ex);
        }
    }

    public String getDriverName() {
        return sDrv;
    }

    public String getConnectString() {
        return sConn;
    }

    /**
     * Ent Statement aus dem pool beziehen. Jedes mit getStatement bezogene Statement MUSS mit
     * releaseStatement wieder zurckgegeben werden.
     * 
     * @return ein Stm (JdbcLink-spezifische Statement-Variante)
     */
    public Stm getStatement() {
        checkLink();
        return createStatement();
    }

    private Stm createStatement() {
        try {
            return new Stm();
        } catch (SQLException ex) {
            lastErrorCode = CONNECTION_CANT_CREATE_STATEMENT;
            lastErrorString = ex.getMessage();
            throw JdbcLinkExceptionTranslation.translateException(lastErrorString, ex);
        }
    }

    /**
     * Ein Stm - Statement in den pool zurckgeben. Die Zahl der im pool zu haltenden Statements
     * wird mit keepStatements definiert.
     * 
     * @param s
     */

    public void releaseStatement(Stm s) {
        if (s != null) {
            s.delete();
        }
    }

    private void checkLink() {
        if (dataSource == null) {
            throw new JdbcLinkException("JdbcLink closed");
        }
    }

    private HashMap<PreparedStatement, Connection> preparedConnections = new HashMap<PreparedStatement, Connection>();

    /**
     * Create a new PreparedStatement.
     * 
     * <b>IMPORTANT</b> Release the resource after using, with releasePreparedStatement mehtod.
     * 
     * @param sql
     * @return
     */
    public PreparedStatement getPreparedStatement(String sql) {
        try {
            Connection connection = dataSource.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            preparedConnections.put(statement, connection);
            return statement;
        } catch (SQLException e) {
            lastErrorCode = CONNECTION_CANT_PREPARE_STAMENT;
            lastErrorString = e.getMessage();
            throw JdbcLinkExceptionTranslation.translateException(lastErrorString, e);
        }
    }

    /**
     * Release the resources of a PreparedStatement.
     * 
     * @param statement
     */
    public void releasePreparedStatement(PreparedStatement statement) {
        Connection connection = preparedConnections.get(statement);
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw JdbcLinkExceptionTranslation.translateException(e);
            }
        }
        preparedConnections.remove(statement);
    }

    /**
     * Ein Prepared Statement anlegen
     * 
     * This method is deprecated. Use the methods getPreparedStatement and releasePreparedStatement
     * instead.
     * 
     * @deprecated
     * @param sql
     *            Abfrage fr das statement (eizusetzende Parameter mssen als ? gesetzt sein
     * @return das vorkompilierte PreparedStatement
     */
    public synchronized PreparedStatement prepareStatement(String sql) {
        checkLink();
        try {
            if (preparedStatementConnection == null) {
                preparedStatementConnection = getKeepAliveConnection();
            }
            return preparedStatementConnection.prepareStatement(sql);
        } catch (SQLException ex) {
            lastErrorCode = CONNECTION_CANT_PREPARE_STAMENT;
            lastErrorString = ex.getMessage();
            throw JdbcLinkExceptionTranslation.translateException(lastErrorString, ex);
        }
    }

    public static final int INTEGRAL = 1;
    public static final int TEXT = 2;
    public static final int BINARY = 3;
    public static final int OTHER = 4;

    public static int generalType(int t) {
        switch (t) {
        case Types.BIGINT:
        case Types.BIT:
        case Types.BOOLEAN:
        case Types.INTEGER:
        case Types.SMALLINT:
        case Types.TINYINT:
            return INTEGRAL;

        case Types.VARCHAR:
        case Types.CHAR:
        case Types.LONGVARCHAR:
            return TEXT;

        case Types.BINARY:
        case Types.BLOB:
        case Types.CLOB:
        case Types.LONGVARBINARY:
        case Types.VARBINARY:
            return BINARY;

        default:
            return OTHER;

        }
    }

    /**
     * Einen String-Value aus dem aktuellen Datensatz des ResultSets holen. Es wird garantiert, dass
     * immer etwas zurckgeliefert wird (" " fr den leeren Sring)
     * 
     * @param r
     *            ResultSet
     * @param field
     *            Feldname
     * @return den String
     * @throws Exception
     *             Wenn das Feld nicht definiert ist.
     */
    public static String getString(ResultSet r, String field) throws Exception {
        String res = r.getString(field);
        if (StringTool.isNothing(res)) {
            return " ";
        }

        return res;
    }

    /**
     * Verbindung zur Datenbank lsen
     * 
     */
    public synchronized void disconnect() {
        try {
            if (preparedStatementConnection != null) {
                preparedStatementConnection.close();
            }
            connectionPool.close();
        } catch (Exception e) {
            // ignore
        } finally {
            dataSource = null;
        }
        log.log("Disconnected", Log.INFOS);
    }

    /**
     * Anfrage, ob die Verbindung steht
     * 
     * @todo Muss implementiert werden
     * @return true wenn die Verbindung steht.
     */
    public boolean isAlive() {
        return true;
    }

    /**
     * Unscharfes Suchen im ResultSet.
     * 
     * @param r
     *            das zu durchsuchende ResultSet
     * @param field
     *            Name des interessiernden Felds
     * @param m
     *            (vorher konfigurierter) fuzzyMatcher mit der Suchbedingung
     * @see ch.rgw.tools.FuzzyMatcher
     * @return true wenn gefunden; das ResultSet steht auf der ersten oder einzigen Fundstelle.
     */
    public static boolean nextMatch(ResultSet r, String field, FuzzyMatcher m) {
        try {
            while (r.next()) {
                if (m.match(r.getString(field))) {
                    return true;
                }
            }
            return false;
        } catch (SQLException ex) {
            ExHandler.handle(ex);
        }
        return false;
    }

    public String dbDriver() {
        return sDrv;
    }

    /**
     * Einen String-Wert abfragen. Temporres Statement erzeugen
     * 
     * @param sql
     *            SQL-String, der ein VARCHAR-oder Text-Feld liefern sollte
     * @return den gefundenen String oder null: nicht gefunden
     */
    public String queryString(String sql) {
        Stm stm = getStatement();
        String res = stm.queryString(sql);
        releaseStatement(stm);
        return res;
    }

    public int queryInt(String sql) {
        Stm stm = getStatement();
        int res = stm.queryInt(sql);
        releaseStatement(stm);
        return res;
    }

    public boolean execScript(InputStream i, boolean translate, boolean stopOnError) {
        Stm stm = getStatement();
        boolean ret = stm.execScript(i, translate, stopOnError);
        releaseStatement(stm);
        return ret;
    }

    /**
     * Wrapper fr Stm#exec
     * 
     * @author gerry
     * 
     */
    public int exec(final String sql) {
        Stm stm = getStatement();
        int res = stm.exec(sql);
        releaseStatement(stm);
        return res;
    }

    public class Stm {
        private Connection conn;
        private Statement stm;

        private void checkStm() {
            if (stm == null || conn == null)
                throw new JdbcLinkException("Statement not valid!");
        }

        private boolean reconnect() {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
                log.log(Level.WARNING, "JdbcLink.Stm - trying reconnect");
                conn = getConnection();
                stm = conn.createStatement();
                return true;
            } catch (SQLException ex) {
                log.log(Level.SEVERE, "JdbcLink.Stm - reconnect failed " + ex.getMessage());
                lastErrorCode = ex.getErrorCode();
                lastErrorString = ex.getMessage();
                return false;
            } catch (JdbcLinkException je) {
                log.log(Level.SEVERE, "JdbcLink.Stm - Reconnect failed " + je.getMessage());
                return false;
            }
        }

        Stm() throws SQLException {
            try {
                conn = getConnection();
                stm = conn.createStatement();
            } catch (SQLException se) {
                log.log(Level.WARNING, "need reconnect " + se.getMessage());
                if (!reconnect()) {
                    throw se;
                }
            }
        }

        public boolean isClosed() {
            checkStm();
            if (DBFLAVOR_POSTGRESQL.equals(DBFlavor)) {
                return false;
            }
            try {
                return stm.isClosed();
            } catch (SQLException ex) {
                ExHandler.handle(ex);
                return false;
            } catch (UnsupportedOperationException ex) {
                ExHandler.handle(ex);
                return false;
            }
        }

        public void delete() {
            try {
                // stm.cancel();
                if (stm != null && !stm.isClosed()) {
                    stm.close();
                }
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException ex) {
                ExHandler.handle(ex);
                /* egal */
            }
            stm = null;
            conn = null;
        }

        /**
         * Eine execute auf die Datanbank ausfhren
         * 
         * @param SQLText
         *            Von der Datenbank verstandener SQL-String
         * @return Zahl der affected rows.
         */
        public int exec(final String sql) {
            return internalExec(sql, false);
        }

        private int internalExec(final String SQLText, final boolean inError) {
            checkStm();
            // log.log("executing " + SQLText, Log.DEBUGMSG);
            try {
                return stm.executeUpdate(SQLText);
            } catch (SQLException e) {
                if (!inError) {
                    if (connect(sUser, sPwd)) {
                        return internalExec(SQLText, true);
                    }
                }

                boolean throwException = DatabaseNativeExceptionHandler.handleException(DBFlavor, e);
                if (throwException) {
                    throw JdbcLinkExceptionTranslation.translateException("Fehler bei: " + SQLText, e);
                }
                return 1;
            }
        }

        /**
         * Eine SQL-Anfrage an die Datenbank senden. Versucht bei einem Fehler zuerst die Verbindung
         * wieder herzustellen
         * 
         * @param SQLText
         *            ein Query String in von der Datenbank verstandener Syntax
         * @return ein ResultSet oder null bei Fehler
         * @throws JdbcException
         */
        public ResultSet query(final String SQLText) {
            return internalQuery(SQLText, false);
        }

        private ResultSet internalQuery(final String SQLText, final boolean inError) {
            checkStm();
            ResultSet res = null;
            // log.log("querying " + SQLText, Log.DEBUGMSG);
            try {
                res = stm.executeQuery(SQLText);
                return res;
            } catch (SQLException e) {
                if (!inError) {
                    // try to solve the problem with a simpel reconnect
                    if (reconnect()) {
                        log.log(Level.WARNING, "Reconnect");
                        return internalQuery(SQLText, true);
                    }
                }
                lastErrorString = e.getMessage();
                lastErrorCode = CONNECTION_SQL_ERROR;
                throw JdbcLinkExceptionTranslation.translateException(lastErrorString, e);
            }
        }

        /**
         * Eine Anzahl Werte als Vector zurckliefern
         * 
         * @param sql
         *            SQL-String, der die Werte liefert
         * @param fields
         *            interessierende Felder
         * @return einen Vector aus Object[] Arrays mit den interessierenden Feldern aller
         *         gefundenen Datenstze
         */
        @SuppressWarnings("unchecked")
        public Vector queryList(String sql, String[] fields) {
            Vector rs = new Vector();
            log.log("executing " + sql, Log.DEBUGMSG);
            ResultSet res = internalQuery(sql, false);
            try {
                if (res != null) {
                    while (res.next()) {
                        Object[] o = new Object[fields.length];
                        for (int i = 0; i < fields.length; i++) {
                            o[i] = res.getObject(fields[i]);
                        }
                        if (fields.length == 1) {
                            rs.add(o[0]);
                        } else {
                            rs.add(o);
                        }
                    }
                }
            } catch (SQLException ex) {
                ExHandler.handle(ex);
            }
            return rs;
        }

        public String queryString(String sql) {
            ResultSet res = internalQuery(sql, false);
            try {
                if (res != null && res.next()) {
                    String r = res.getString(1);
                    if ((r == null) || (r.equals("null")) || (r.equals(""))) {
                        return "";
                    }
                    return r;
                }
                return null;
            } catch (SQLException ex) {
                ExHandler.handle(ex);
            }
            return null;
        }

        /**
         * Einen Integer-Wert abfragen.
         * 
         * @param sql
         *            SQL-String, der ein Integer-Feld liefern sollte
         * @return den ersten der Suchbedingung entsprechenden Integer-Wert oder -1: Wert nicht
         *         gefunden.
         */
        public int queryInt(String sql) {
            ResultSet res = internalQuery(sql, false);
            try {
                if (res != null && res.next()) {
                    return res.getInt(1);
                }
            } catch (SQLException ex) {
                ExHandler.handle(ex);
            }
            return -1;
        }

        /**
         * Ein SQL-Script einlesen und ausfhren. alles nach # bis zum Zeilenende wird ignoriert
         * 
         * @param s
         *            der InputStream mit dem Script
         * @param translate
         *            true, wenn das Script zu den bekannten Dialekten bersetzt werden soll
         * @param stopOnError
         *            true: Abbruch des Scripts, wenn ein Fehler auftritt
         * @return false wenn ein Fehler passiert ist.
         */
        public boolean execScript(InputStream s, boolean translate, boolean stopOnError) {
            String sql = "<none>";
            if (s == null) {
                return false;
            }
            // autoc=conn.getAutoCommit();
            // setAutoCommit(false);
            while ((sql = readStatement(s)) != null) {
                log.log(sql, Log.DEBUGMSG);
                if (translate) {
                    sql = translateFlavor(sql);
                }
                System.out.println(sql);
                try {
                    stm.execute(sql);
                } catch (SQLException ex) {
                    ExHandler.handle(ex);
                    if (stopOnError == true) {
                        return false;
                    }
                }
            }
            // commit();
            return true;
        }

    }

    public static String readStatement(InputStream is) {
        StringBuffer inp = new StringBuffer(1000);
        String sql = "<none>";
        try {
            int c;
            boolean comment = false;
            while (((c = is.read()) != -1)) {
                if (c == ';') {
                    break;
                }
                if (c == '#') {
                    comment = true;
                }
                if ((c == '\r') || (c == '\n')) {
                    comment = false;
                }
                if (comment == false) {
                    inp.append((char) c);
                }
            }
            // sql=inp.toString().replace("#.+$","");
            // sql=sql.replace("--[^\\r]*","").trim();
            sql = inp.toString().replaceAll("[\\n\\r\\t]", " ");
            sql = sql.replaceAll(" {2,}", " ").trim();
            if (sql.length() < 4) {
                return null;
            }
            return sql;
        } catch (IOException ex) {
            ExHandler.handle(ex);
            return null;
        }

    }

    public boolean dumpTable(BufferedWriter w, String name) throws Exception {
        Stm stm = getStatement();
        ResultSet res = stm.query("SELECT * from " + name);
        ResultSetMetaData rm = res.getMetaData();
        int cols = rm.getColumnCount();
        String[] ColNames = new String[cols];
        int[] colTypes = new int[cols];
        w.write("CREATE TABLE " + name + "(");
        for (int i = 0; i < cols; i++) {
            ColNames[i] = rm.getColumnName(i + 1);
            colTypes[i] = rm.getColumnType(i + 1);
            w.write(ColNames[i] + " " + colTypes[i] + ",\n");
        }
        w.write(");");

        while ((res != null) && (res.next() == true)) {
            w.write("INSERT INTO " + name + " (");
            for (int i = 0; i < cols; i++) {
                w.write(ColNames[i]);
                if (i < cols - 1) {
                    w.write(",");
                }
            }
            w.write(") VALUES (");
            for (int i = 0; i < cols; i++) {
                Object o = res.getObject(ColNames[i]);
                switch (JdbcLink.generalType(colTypes[i])) {
                case JdbcLink.INTEGRAL:
                    if (o == null) {
                        w.write("0");
                    } else {
                        w.write(Integer.toString(((Integer) o).intValue()));
                    }
                    break;
                case JdbcLink.TEXT:
                    if (o == null) {
                        w.write(JdbcLink.wrap("null"));
                    } else {
                        w.write(JdbcLink.wrap((String) o));
                    }
                    break;

                default:
                    String t = o.getClass().getName();
                    log.log("Unknown type " + t, Log.ERRORS);
                    throw new Exception("Cant write " + t);

                }
                if (i < cols - 1) {
                    w.write(",");
                }
            }
            w.write(");");
            w.newLine();
        }
        res.close();
        releaseStatement(stm);
        return true;
    }

    /**
     * Einen SQL-String in die bekannten flavors bersetzen. Basisdialekt ist mysql
     */
    public String translateFlavor(String sql) {
        // sql=sql.toLowerCase();
        // TODO: Konzept fr case-sensitiveness klarer definieren
        if (DBFlavor.equalsIgnoreCase(DBFLAVOR_POSTGRESQL)) {
            sql = sql.replaceAll("BLOB", "BYTEA");
            sql = sql.replaceAll("DROP INDEX (.+?) ON .+?;", "DROP INDEX $1;");
            sql = sql.replaceAll("MODIFY\\s+(\\w+)\\s+(.+)", "ALTER COLUMN $1 TYPE $2");
            sql = sql.replaceAll("SIGNED", "INT");
        } else if (DBFlavor.startsWith("hsqldb") || DBFlavor.startsWith(DBFLAVOR_H2)) {
            sql = sql.replaceAll("TEXT", "LONGVARCHAR");
            sql = sql.replaceAll("BLOB", "LONGVARBINARY");
            sql = sql.replaceAll("CREATE +TABLE", "CREATE CACHED TABLE");
            sql = sql.replaceAll("DROP INDEX (.+?) ON .+?;", "DROP INDEX $1;");
            sql = sql.replaceAll("MODIFY (.+)", "ALTER COLUMN $1");
        } else if (DBFlavor.equalsIgnoreCase(DBFLAVOR_MYSQL)) {
            sql = sql.replaceAll("BLOB", "LONGBLOB");
            sql = sql.replaceAll("TEXT", "LONGTEXT");
            /* experimental - do not use */
        } else if (DBFlavor.equalsIgnoreCase("db2")) {
            sql = sql.replaceAll("VARCHAR\\s*\\([0-9]+\\)", "VARCHAR");
            sql = sql.replaceAll("TEXT", "CLOB");
            /* /experimental */
        }
        return sql;
    }
}