/*
* This software and supporting documentation were developed by
*
* Siemens Corporate Technology
* Competence Center Knowledge Management and Business Transformation
* D-81730 Munich, Germany
*
* Authors (representing a really great team ;-) )
* Stefan B. Augustin, Thorbj�rn Hansen, Manfred Langen
*
* This software is Open Source under GNU General Public License (GPL).
* Read the text of this license in LICENSE.TXT
* or look at www.opensource.org/licenses/
*
* Once more we emphasize, that:
* THIS SOFTWARE IS MADE AVAILABLE, AS IS, WITHOUT ANY WARRANTY
* REGARDING THE SOFTWARE, ITS PERFORMANCE OR
* FITNESS FOR ANY PARTICULAR USE, FREEDOM FROM ANY COMPUTER DISEASES OR
* ITS CONFORMITY TO ANY SPECIFICATION. THE ENTIRE RISK AS TO QUALITY AND
* PERFORMANCE OF THE SOFTWARE IS WITH THE USER.
*
*/
// KFM_JdbcAdapter
// ************ package ******************************************************
package KFM.DB;
// ************ imports ******************************************************
import KFM.DateTimeServices.*;
import KFM.log.*;
import KFM.Exceptions.*;
import java.sql.*;
import java.io.*;
import java.util.*;
import java.rmi.server.UID;
/** KFM_JdbcAdapter provides an easy to use db-interface.
*
* <P>Note: Now cooperates with KFM_Database2!</P>
*
*
* <H2>Features</H2>
*
* <P>In contrast to other adapters classes this
* KFM_JdbcAdapter works with prepared statemets and supports multiple statements.</P>
*
* <P>There are also other adapter classes, like the Generic_JdbcAdapter which is deprecated.
* Or the KFM_GernericJdbcAdapter which is not deprecated but does not have same features as
* this adapter class.
* Note that the KFM_GenericJdbcAdapter will eventually be replaced bay this class.</P>
*
* <P>Use the KFM_JdbcAdapter in this way:</P>
*
* <PRE>
* KFM_Database tDb = new KFM_Database(...);
* try {
* tDb.establishConnection();
* KFM_JdbcAdapter kfmJdbcAdapt = new KFM_JdbcAdapter(tDb);
* kfmJdbcAdapt.open();
* int tStmtId = kfmJdbcAdapt.prepareStmt("SELECT ? FROM employee WHERE age=?;");
* try {
* kfmJdbcAdapt.setValue(tStmtId,1,"name");
* kfmJdbcAdapt.setValue(tStmtId,2,40);
* ResultSet rs = kfmJdbcAdapt.executeQuery(tStmtId);
* ...
* kfmJdbcAdapt.setValue(tStmtId,1,"address");
* ResultSet rs = kfmJdbcAdapt.executeQuery(tStmtId);
* ...
* } finally {
* kfmJdbcAdapt.closeStmt(tStmtId);
* kfmJdbcAdapt.close();
* }
* } finally {
* tDb.closeConnection();
* }
* </PRE>
*
*
* <H2>Caveat</H2>
*
* <P>There is a problem with writing NULL values into DB fields of several JDBC-SQL types with ACCESS
* (an exception is thrown).
* A test with Sybase 11.5 (via jConnect 4.1) resulted in no problems, however.</P>
*
* <P>These were the JDBC-SQL-Types (and their corresponding ACCESS types) working properly when writing
* NULL:</P>
*
* <PRE>
* TINYINT (Zahl, Feldgr�sse Byte)
* SMALLINT (Zahl, Feldgr�sse Integer)
* INTEGER (Zahl, Feldgr�sse Long Integer)
* REAL (Double)
* DOUBLE (Double)
* CHAR (Text)
* VARCHAR (Text)
* LONGVARCHAR (Memo)
* </PRE>
*
*
* <H2>See also</H2>
*
* <P>See also `KFM_Database�, which e.g. provides `isAccess� and `isSybase�.</P>
*
* @see KFM_Database
*
* @author h
* @version 0.001
*/
public class KFM_JdbcAdapter
{
private int cVarcharCount = 3990; //the size for varchar2 values in oracle
// ************************************************************
// Inner classes
// ************************************************************
/** A container to store a PreparedStatement and information about it. */
public static class KFM_PrepStmtItem
{
// ************************************************************
// Attributes
// ************************************************************
/** The prepared statement itself. */
protected PreparedStatement mPrepStmt = null;
/** The Id of the statement. */
protected int mId = 0;
/** The String (Sql-Mask) of the statement. */
protected String mStmtString = null;
/** These Objects define the values in the statement. */
protected Object[] mValues = null;
/** Create a new Container from the statement with his sqlmask.
*
* @param aPrepStmt the statement to store.
* @param aStmtString the sqlmask of the statement.
*/
public KFM_PrepStmtItem (PreparedStatement aPrepStmt, String aStmtString)
{
mPrepStmt = aPrepStmt;
mStmtString = aStmtString;
int tCounter = 0; //Count `?� in stmtstring.
int tStringStartPos = 0;
while((tStringStartPos = mStmtString.indexOf('?', tStringStartPos+1)) != -1)
tCounter++;
if(tCounter > 0) {
//If one or more ? exists, create a object for each.
mValues = new Object[tCounter];
removeValues(); // Set all objects to null.
}
}
/** Set the Id of an statement.
*
* @param aId statement id to set.
*/
public void setId (int aId)
{
mId = aId;
}
/** Insert an object in the object array on position aPos(-1).
*
* @param aObject the object to be inserted.
* @param aPos the position of the object in the array.
*
* Note: The position starts with 1 and ends with the amount of ? in the stmtstring.
* The array position starts with 0 and ends eith the amount of ? in the stmtstring -1.
* So an offset of -1 is used to store the Object in the array.
*/
public void addValue(Object aObject, int aPos)
{
if(aPos-1 <= mValues.length) {
mValues[aPos-1] = aObject;
} else {
throw new ProgrammerException("KFM_PrepStmtItem::addValue(Object,int):"
+ " arrayindex out of bounds.");
}
}
/** Create an integer object from an int and insert them into array by calling addValue(Object
* aObject, int aPos).
*
* @param aIntValue to create an integer object from.
* @param aPos postion for the IntergerObject in the array.
* @see #addValue(Object aObject, int aPos)
*/
public void addValue(int aIntValue, int aPos)
{
addValue(new Integer(aIntValue), aPos);
}
/** Create a Long object from a long and insert them into array by calling addValue(Object
* aObject, int aPos).
*
* @param aIntValue to create an integer object from.
* @param aPos postion for the IntergerObject in the array.
* @see #addValue(Object aObject, int aPos)
*/
public void addValue(long aIntValue, int aPos)
{
addValue(new Long(aIntValue), aPos);
}
/** Remove all values from the object array, and set them to null.
*/
public void removeValues()
{
for(int tCounter=0;tCounter<mValues.length;tCounter++)
mValues[tCounter]=null;
}
/** Get the preparedstatement stored in this object.
*
* @return preparedstatement in this object.
*/
public PreparedStatement getStmt()
{
return mPrepStmt;
}
/** Returns an string which represents the query of the statement.
*
* @return string which represents the query of this statement.
*/
public String toString()
{
StringBuffer tQueryString = new StringBuffer("");
int tStmtValueId = 0; //The values.
int tStartPos = 0;
int tEndPos;
while((tEndPos = mStmtString.indexOf('?',tStartPos))!=-1) {
//Search for ? in String.
tQueryString.append(mStmtString.substring(tStartPos,tEndPos)); //Append substring before ?
//to stringbuffer.
if(mValues[tStmtValueId] != null) //If a value exists
tQueryString.append(mValues[tStmtValueId].toString()); //add them into stringbuffer.
else
tQueryString.append("NULL"); //Otherwise append NULL.
tStmtValueId++; //Prepare for next loop.
tStartPos = tEndPos+1;
}
if(tStartPos<mStmtString.length()) //Add last part of the
tQueryString.append(mStmtString.substring(tStartPos)); //statementstring.
tQueryString.append(" (stmt: " + mId + ") "); //Show statement id.
return tQueryString.toString(); //Return stringvalue of
//the stringbuffer.
}
} // End of class StatementItem.
// ************************************************************
// Constants
// ************************************************************
public static final int illegalStmt = -1;
// ************************************************************
// Attributes
// ************************************************************
/** The Database to get the connection from. */
private KFM_Database mDatabase = null;
/** Vector containing all statements. */
private Vector mStatement = null;
/** Connection to db. */
protected Connection mConnection = null;
/** Log file. */
protected KFMLog mLog = null;
// ************************************************************
// Methods
// ************************************************************
/**
* DEPRECATED Constructor. Use the one with the new logging concept.
*
* Constructor init KFM_JdbcAdapter and stpr jdbc-connection information.
*
* @param KFM_Database to get Connection from.
* @deprecated
*/
public KFM_JdbcAdapter(KFM_Database aDatabase)
{
// mConnection = aDatabase.getConnection();
mDatabase = aDatabase;
mStatement = new Vector();
}
/**
* Constructor with new KFMLog. Will replace the old constructor above.
*
* @param KFM_Database to get Connection from.
*/
public KFM_JdbcAdapter(KFM_Database aDatabase, KFMLog aLog)
{
this(aDatabase);
this.mLog = aLog;
}
/** Accessor for mDatabase. */
public KFM_Database getDatabase () {
return mDatabase;
}
/**
* Closing all at the end of adapter lifetime.
*/
/* public void finalize()
{
try {
close();
} catch(Exception aE) {
// Exception not handled because class will no longer
// exist after finalize.
}
}*/
/** Test and load JDBC-driver and creates a connection to the DB.
*
* A KFM_JdbcAdapter can only be opened once. If the adapter is opened again a
* ProgrammerException will be thrown.
*
* @throws ProgrammerException on reopen a open database.
*
* @see KFMGenericJdbcAdapter
* @see #KFM_JdbcAdapter(KFM_Database)
*/
public synchronized void open()
{
if(mConnection != null) {
throw new ProgrammerException("KFM_JdbcAdapter::open: "
+ "Connection is already open.");
}
mConnection = mDatabase.getConnection();
}
/** Remove all prepared statements and closes connection (if open).
*
* Note: A KFM_JdbcAdapter can only be closed if it was opend before. Trying to close a
* non open KFM_JdbcAdapter will throw a ProgrammerException.
*
* @throws ProgrammerException if KFM_JdbcAdapter was not opened before.
* @throws KFM_SQLException on Statement or Connection closing problems.
*
* @see KFM_GenericJdbcAdapter
* @see #KFM_JdbcAdapter(KFM_Database)
*/
public synchronized void close()
throws KFM_SQLException
{
KFMSystem.log.debug("JDBCAdapter: Close");
if(mConnection == null) {
// Execption caused by calling close but not calling open before.
throw new ProgrammerException("KFM_JdbcAdapter::close: "
+ "close() was called, but connection is not open.");
}
KFM_PrepStmtItem tPrepStatementItem = null;
Enumeration aEnu = mStatement.elements(); //get all open statements.
while(aEnu.hasMoreElements()) {
tPrepStatementItem = (KFM_PrepStmtItem)aEnu.nextElement(); //For each statement:
// If statement exists, close it.
if(tPrepStatementItem != null) {
try {
tPrepStatementItem.getStmt().close();
tPrepStatementItem = null;
} catch (SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
}
}
mStatement.removeAllElements(); // Delete all statements.
mConnection = null;
// We don't have to close the connection if we use the kfm_database.
//
// try {
// mConnection.close();
// mConnection = null;
// } catch (SQLException aSqlE) {
// KFM_SQLException tKSE = new KFM_SQLException(aSqlE);
// throw new ProgrammerException(
// "KFM_JdbcAdapter::close: "
// + "Connection.close()" + " nested Exception is\n"
// + tKSE.getMessage());
}
/** Prepares a new created statement and returns the id of this new statement.
*
* @param aStmtId id of the statement
* @param aSqlMask sqlmask for the statement e.g. "Select ? from ?;"
*
* Note: All the ? have to be set to a value, by using the setValue method.
* Otherwise no query or update operation will throw an exception.
*
* @throws ProgrammerException when sqlMask is null or "".
* @throws KFM_SQLException on prepareStatement problems.
*/
public int prepareStmt(String aSqlMask)
throws KFM_SQLException
{
garbageCollection();
if(aSqlMask == null) {
throw new ProgrammerException("KFM_JdbcAdapter::prepareStmt: Sqlmask cannot be null.");
}
if(aSqlMask.equals("")) {
throw new ProgrammerException("KFM_JdbcAdapter::prepareStmt: Sqlmask cannot be \"\".");
}
PreparedStatement tPrepStatement = null;
int tStmtId = 0; // Id of the new statement.
try {
tPrepStatement = mConnection.prepareStatement(aSqlMask); //Get new statementItem.
KFM_PrepStmtItem tSmtItem = new KFM_PrepStmtItem(tPrepStatement, aSqlMask);
KFM_PrepStmtItem tFreeStatementItem = null; //Get a free place in
Enumeration tEnu=mStatement.elements(); //statementItem vector.
while(tEnu.hasMoreElements()) {
tFreeStatementItem = (KFM_PrepStmtItem) tEnu.nextElement();
if(tFreeStatementItem == null) {
break;
}
tStmtId++;
}
tSmtItem.setId(tStmtId); //Set found id to item.
if(tStmtId < mStatement.size())
mStatement.setElementAt(tSmtItem, tStmtId); //Replace empty vectorelement.
else //Free place found, and create a
mStatement.addElement(tSmtItem); //new vectorelement.
} catch(SQLException ex) {
// Prepare statement failed.
throw new KFM_SQLException(ex, aSqlMask);
//Y } catch(Exception aE) {
//Y //For vector problems.
//Y throw new ProgrammerException("KFM_JdbcAdapter::prepareStmt:"
//Y + " nested Exception is\n" +aE.getMessage());
}
return tStmtId; // Return found id of statement.
}
/**
* Closes a statement.
* @param aStmtId id of the statement you want to close.
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on closing problems.
*/
public void closeStmt(int aStmtId)
throws KFM_SQLException
{
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statementItem with aStmtId.
try {
tPrepStatementItem.getStmt().close();
} catch (SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
//Mark statementItem in vector as free.
mStatement.setElementAt(null,aStmtId);
// tPrepStatement is still in the vector, but if you remove it
// the id of all following statements will change :(.
// PS: only temprory use (I am looking for another way of
// implementing statement-ids).
//Y } catch(Exception aE) {
//Y // Something went completely wrong, perhaps statement not found.
//Y throw new ProgrammerException(
//Y "KFM_JdbcAdapter::closeStmt: "
//Y + " nested Exception is\n" + aE.getMessage());
//Y }
}
public void setNullIdValue (int aStmtId, int aParamNumber)
throws KFM_SQLException
{
if(mDatabase.isOracle()) {
// in oracle ids are stored as chars
setNull(aStmtId, aParamNumber, java.sql.Types.VARCHAR);
} else {
throw new ProgrammerException(
"KFM_JdbcAdapter::setNullCounter: DB is not oracle.");
}
}
/** Set a String or SQL NULL.
*
* Like `setValue(..., ..., String)�, except an empty String will be replaced by SQL NULL.
*
* This is because Sybase will return a SPACE for an empty string and screw up things.
*
* This is a hack, we'll do it nicely later on.
*/
public void setStringOrNull (int aStmtId, int aParamNumber, String aValue)
throws KFM_SQLException
{
if(aValue.equals("")) {
// I hope VARCHAR is correct.
setNull(aStmtId, aParamNumber, java.sql.Types.VARCHAR);
} else {
setValue(aStmtId, aParamNumber, aValue);
}
}
/*** Set the String value of a ? in the sqlmask.
*
* @param aStmtId id of the statement.
* @param aParamNumber number of the ? to be replaced (starting with 1).
* @param aValue String which replaces the ? (not null otherwise statement cannot be executed).
*
* Note: All the ? in the SqlMask have to be set to a value, before calling any
* query or update-operation. Otherwise an exception will be thrown.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on setString problems.
*/
public void setValue (int aStmtId, int aParamNumber, String aValue)
throws KFM_SQLException
{
if(aValue == null) {
throw new ProgrammerException(
"KFM_JdbcAdapter::setValue(String): "
+ "A value in a prepared statement cannot be null. "
+ "Use setNull to instert null values.");
}
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId);
try {
tPrepStatementItem.getStmt().setString(aParamNumber,aValue);
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
tPrepStatementItem.addValue(aValue, aParamNumber); //Add value to item.
mStatement.setElementAt(tPrepStatementItem, aStmtId); //Update vector.
//Y } catch(Exception aE) {
//Y throw new ProgrammerException("KFM_JdbcAdapter::setValue(String): " +
//Y " nested Exception is\n" +aE.getMessage());
//Y }
}
/** Set the integer value of a ? in the sqlmask.
*
* Note: All the ? in the SqlMask have to be set to a value, before calling any
* query or update-operation. Otherwise an exception will be thrown.
*
* Note: Do not use this method for MS-Access97 `Counter� values. You probably want `setIdValue�.
*
* Note: An MS-Access97 `long� value (or a `Counter� with type `long�) is a Java `int� value.
* Trying to set an MS-Access97 `long� value by using the Java method `setLong()� will result
* (if you execute the query) in a detailed descripted exception like:
*
* <pre>Treiber nicht in der Lage diese Operation auszuf�hren.</pre>
*
* @param aStmtId Id of the statement.
* @param aParamNumber Number of the ? to be replaced (starting with 1).
* @param aValue Intvalue which replaces the ?.
*
* @throws ProgrammerException On none exisiting statementid.
* @throws KFM_SQLException On setInt problems.
*/
public void setValue (int aStmtId, int aParamNumber, int aValue)
throws KFM_SQLException
{
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statement with aStmtId.
try {
tPrepStatementItem.getStmt().setInt(aParamNumber, aValue);
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
tPrepStatementItem.addValue(aValue, aParamNumber); //Set value to Item.
mStatement.setElementAt(tPrepStatementItem, aStmtId); //Update vector.
//Y } catch(Exception aE) {
//Y throw new ProgrammerException(
//Y "KFM_JdbcAdapter::setValue(int): "
//Y + " nested Exception is\n" +aE.getMessage());
//Y }
}
/** Set the long value of a ? in the sqlmask.
*
* Note: All the ? in the SqlMask have to be set to a value, before calling any
* query or update-operation. Otherwise an exception will be thrown.
*
* Note: Do not use this method for Sybase `Counter� values. You probably want `setIdValue�.
*
* Note: Only works on Sybase.
*
* Note: An MS-Access97 `long� value (or a `Counter� with type `long�) is a Java `int� value.
* Trying to set an MS-Access97 `long� value by using the Java method `setLong()� will result
* (if you execute the query) in a detailed descripted exception like:
*
* <pre>Treiber nicht in der Lage diese Operation auszuf�hren.</pre>
*
* @param aStmtId Id of the statement.
* @param aParamNumber Number of the ? to be replaced (starting with 1).
* @param aValue Intvalue which replaces the ?.
*
* @throws ProgrammerException On none exisiting statementid.
* @throws KFM_SQLException On setInt problems.
*/
public void setValue (int aStmtId, int aParamNumber, long aValue)
throws KFM_SQLException
{
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statement with aStmtId.
try {
tPrepStatementItem.getStmt().setLong(aParamNumber, aValue);
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
tPrepStatementItem.addValue(aValue, aParamNumber); //Set value to Item.
mStatement.setElementAt(tPrepStatementItem, aStmtId); //Update vector.
//Y } catch(Exception aE) {
//Y throw new ProgrammerException(
//Y "KFM_JdbcAdapter::setValue(int): "
//Y + " nested Exception is\n" +aE.getMessage());
//Y }
}
/** Set the Time value of a ? in the sqlmask.
*
* @param aStmtId id of the statement.
* @param aParamNumber number of the ? to be replaced (starting with 1).
* @param aValue Time which replaces the ? (not null otherwise statement cannot be executed).
*
* Note: All the ? in the SqlMask have to be set to a value, before calling any
* query or update-operation. Otherwise an exception will be thrown.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on setTime problems.
*/
public void setValue (int aStmtId, int aParamNumber, java.sql.Time aValue)
throws KFM_SQLException
{
if(aValue == null) {
throw new ProgrammerException(
"KFM_JdbcAdapter::setValue(Time): "
+ "A value in a prepared statement cannot be null. "
+ "Use setNull to instert null values.");
}
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statementItem with aStmtId.
try {
tPrepStatementItem.getStmt().setTime(aParamNumber,aValue);
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
tPrepStatementItem.addValue(aValue.toString(), aParamNumber); //Add value to item.
mStatement.setElementAt(tPrepStatementItem, aStmtId); //Update vector.
//Y } catch(Exception aE) {
//Y throw new ProgrammerException("KFM_JdbcAdapter::setValue(Time): " +
//Y " nested Exception is\n" +aE.getMessage());
//Y }
}
/** Set the KFM_Time value of a ? in the sqlmask.
*
* @param aStmtId id of the statement.
* @param aParamNumber number of the ? to be replaced (starting with 1).
* @param aValue KFM_Time which replaces the ? (not null otherwise statement cannot be executed).
*
* Note: All the ? in the SqlMask have to be set to a value, before calling any
* query or update-operation. Otherwise an exception will be thrown.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on setTime problems.
*/
public void setValue (int aStmtId, int aParamNumber, KFM_Time aValue)
throws KFM_SQLException
{
if(aValue == null) {
throw new ProgrammerException(
"KFM_JdbcAdapter::setValue(KFM_Time): "
+ "A value in a prepared statement cannot be null. "
+ "Use setNull to instert null values.");
}
java.sql.Time tTime = new Time(aValue.hour, aValue.minutes, aValue.seconds);
//Create java.sql.Time value.
setValue(aStmtId, aParamNumber, tTime); //Call setValue(time).
}
/** Set the Date value of a ? in the sqlmask.
*
* @param aStmtId id of the statement.
* @param aParamNumber number of the ? to be replaced (starting with 1).
* @param aValue Date which replaces the ? (not null otherwise statement cannot be executed).
*
* Note: All the ? in the SqlMask have to be set to a value, before calling any
* query or update-operation. Otherwise an exception will be thrown.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on setDate problems.
*/
public void setValue (int aStmtId, int aParamNumber, java.sql.Date aValue)
//if you don't use java.sql.Date class KFM.DB.Date will be used
throws KFM_SQLException
{
if(aValue == null) {
throw new ProgrammerException(
"KFM_JdbcAdapter::setValue(Date): "
+ "A value in a prepared statement cannot be null. "
+ "Use setNull to instert null values.");
}
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statement with aStmtId.
try {
tPrepStatementItem.getStmt().setDate(aParamNumber, aValue);
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
tPrepStatementItem.addValue(aValue.toString(), aParamNumber); //Set value to item.
mStatement.setElementAt(tPrepStatementItem, aStmtId); //Update vector.
//Y } catch(Exception aE) {
//Y throw new ProgrammerException("KFM_JdbcAdapter::setValue(Date): " +
//Y " nested Exception is\n" + aE.getMessage());
//Y }
}
/** Set the KFM_Date value of a ? in the sqlmask.
*
* @param aStmtId id of the statement.
* @param aParamNumber number of the ? to be replaced (starting with 1).
* @param aValue KFM_Date which replaces the ? (not null otherwise statement cannot be executed).
*
* Note: All the ? in the SqlMask have to be set to a value, before calling any
* query or update-operation. Otherwise an exception will be thrown.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on setDate problems.
*/
public void setValue (int aStmtId, int aParamNumber, KFM_Date aValue)
throws KFM_SQLException
{
if(aValue == null) {
throw new ProgrammerException(
"KFM_JdbcAdapter::setValue(KFM_Date): "
+ "A value in a prepared statement cannot be null. "
+ "Use setNull to instert null values.");
}
java.sql.Date tDate = new java.sql.Date(
// See java.util.date:
aValue.getYear() - 1900, // A year y is represented by the integer y�-�1900.
// KFM_Date 1900 is 1900!
aValue.getMonth() -1, // A month is represented by an integer form 0 to 11; 0 is January, 1 is
// February ... KFM_Date is between 1, 12!
aValue.getDay());
setValue(aStmtId, aParamNumber, tDate);
}
/** Set the Timestamp value of a ? in the sqlmask.
*
* @param aStmtId id of the statement.
* @param aParamNumber number of the ? to be replaced (starting with 1).
* @param aValue Timestamp which replaces the ? (not null otherwise statement cannot be executed).
*
* Note: All the ? in the SqlMask have to be set to a value, before calling any
* query or update-operation. Otherwise an exception will be thrown.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on setTimestamp problems.
*/
public void setValue (int aStmtId, int aParamNumber, java.sql.Timestamp aValue)
throws KFM_SQLException
{
if(aValue == null) {
throw new ProgrammerException(
"KFM_JdbcAdapter::setValue(Timestamp): "
+ "A value in a prepared statement cannot be null. "
+ "Use setNull to instert null values.");
}
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statement with aStmtId.
try {
tPrepStatementItem.getStmt().setTimestamp(aParamNumber,aValue);
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
tPrepStatementItem.addValue(aValue.toString(), aParamNumber); //Set value to item.
mStatement.setElementAt(tPrepStatementItem, aStmtId); //Update vector.
//Y } catch(Exception aE) {
//Y throw new ProgrammerException("KFM_JdbcAdapter::setValue(Timestamp): " +
//Y " nested Exception is\n" +aE.getMessage());
//Y }
}
/** Set the KFM_DateTime value of a ? in the sqlmask.
*
* @param aStmtId id of the statement.
* @param aParamNumber number of the ? to be replaced (starting with 1).
* @param aValue KFM_DateTime which replaces the ? (not null otherwise statement cannot be executed).
*
* Note: All the ? in the SqlMask have to be set to a value, before calling any
* query or update-operation. Otherwise an exception will be thrown.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on setTimestamp problems.
*/
public void setValue (int aStmtId, int aParamNumber, KFM_DateTime aValue)
throws KFM_SQLException
{
if(aValue == null) {
throw new ProgrammerException(
"KFM_JdbcAdapter::setValue(KFM_DateTime): "
+ "A value in a prepared statement cannot be null. "
+ "Use setNull to instert null values.");
}
//java.sql.Timestamp tTimestamp = new Timestamp(aValue.year -1900,
KFM_Timestamp tTimestamp = new KFM_Timestamp(
// See java.util.date:
aValue.year - 1900, // A year y is represented by the integer y�-�1900.
// kfm_DateTime 1900 is 1900!
aValue.month - 1, // A month is represented by an integer form 0 to 11;
// 0 is January, 1 is February ... kfm_DateTime is between 1, 12!
aValue.day,
aValue.hour,
aValue.minutes,
aValue.seconds,
0); // Nano seconds.
setValue(aStmtId, aParamNumber, tTimestamp);
}
/** Set the value of a ? in the sqlmask to null.
*
* The following java.sql.Types are supportet by access
* BINARY, BIT, CHAR, DOUBLE, INTEGER, NUMERIC, REAL, SMALLINT, TINYINT, VARBINARY, VARCHAR
*
* The java.sql.Types DATE, NULL, OTHER, TIME, TIMESTAMP returned the exception
* "SQL-Datentyp au�erhalb des zul�ssigen Bereichs", SQLState: S1004.
*
* The java.sql.Type BIGINT returned the exception
* "Treiber nicht in der Lage, diese Operation auszuf�hren", SQLState: S1C00.
*
* Thejava.sql.Types LONGVARBINARY, LONGVARCHAR returned the exception
* "Illegal reference in query", SQLState: S1000.
*
* @param aStmtId id of the statement.
* @param aParamNumber number of the ? to be replaced (starting with 1).
* @param aSqlType SQL type code defined by java.sql.Types.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on setNull problems.
*
* @see java.sql.Types
*/
public void setNull (int aStmtId, int aParamNumber, int aSqlType)
throws KFM_SQLException
{
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statement with aStmtId.
try {
tPrepStatementItem.getStmt().setNull(aParamNumber, aSqlType);
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
tPrepStatementItem.addValue("NULL", aParamNumber); //Set value to Item.
mStatement.setElementAt(tPrepStatementItem, aStmtId); //Update vector.
//Y } catch(Exception aE) {
//Y throw new ProgrammerException(
//Y "KFM_JdbcAdapter::setNull: "
//Y + " nested Exception is\n" +aE.getMessage());
//Y }
}
/** Clear all parameters of a statement identified by id.
*
* @param aStmtId id of the statement.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on clearParameters problems.
*/
public void clearParameters (int aStmtId)
throws KFM_SQLException
{
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statement with aStmtId.
try {
tPrepStatementItem.getStmt().clearParameters();
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
tPrepStatementItem.removeValues(); //remove item values.
mStatement.setElementAt(tPrepStatementItem, aStmtId); //Update vector.
//Y } catch(Exception aE) {
//Y throw new ProgrammerException("KFM_JdbcAdapter::clearParameters(): " +
//Y " nested Exception is\n" + aE.getMessage());
//Y }
}
/** Execute a SQL INSERT, UPDATE or DELETE statement
* (also, SQL statements that return nothing such as SQL DDL statements can be executed).
*
* Uses the sqlmask specified in prepareStmt.
* Use `executeQuery� for SQL-statements that return a Resultset.
*
* @param aStmtId id of the statement.
* @return either the row count for operation; or 0 for SQL statements that return nothing.
*
* @throws ProgrammerException on readonly database connection.
* Reason for: "You can't call executeUpdate on an autocommited connection, it's readonly."
* Autocommited connections are assumed to be readonly because ApplicationPage2 requires this.
* @throws ProgrammerException on connection problems.
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on executeUpdate or isReadOnly problems.
*/
public int executeUpdate(int aStmtId)
throws KFM_SQLException
{
try {
if(mConnection.isReadOnly()) {
throw new ProgrammerException("KFM_JdbcAdapter::executeUpdate: "
+ "Connection is readonly.");
}
} catch(SQLException ex) {
throw new KFM_SQLException(ex);
}
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statement with aStmtId.
String t = tPrepStatementItem.toString();
if(mDatabase instanceof KFM_Database2) {
if(mDatabase.getAutoCommit()) {
log_error("KFM_JdbcAdapter/KFM_Database2: Autocommited execute Update " + t, null);
throw new ProgrammerException("KFM_JdbcAdapter::executeUpdate: "
+ "You can't call executeUpdate on an autocommited connection, it's readonly.");
} else {
log_detail("KFM_JdbcAdapter/KFM_Database2: execute Update " + t);
((KFM_Database2) mDatabase).setDoingSomething(true);
}
} else {
log_detail("KFM_JdbcAdapter/KFM_Database: execute Update: " + t);
}
try {
return tPrepStatementItem.getStmt().executeUpdate();
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
//Y } catch(Exception aE) {
//Y throw new ProgrammerException(
//Y "KFM_JdbcAdapter::executeUpdate: nested Exception is\n" + aE.getMessage());
//Y }
}
/**
* Initializes a table with 'empty_clob()' for all clob entries.
* This is mandantory for all new tables columns.
*
* @param aClobNames Names of all clob entries to be initialized
* @param aTableName name of the table to be updated
* @param aWhere where clause
*/
public void initClobData(
String[] aClobNames,
String aTableName,
String aWhere)
throws KFM_SQLException
{
Statement tSt = null;
String tSQL = null;
try {
try {
Connection tCon = getDatabase().getConnection();
tCon.commit(); //mandantory for next select statement
tSt = tCon.createStatement();
tSQL = "Update ";
tSQL += aTableName;
tSQL += " set ";
for (int i = 0; i < aClobNames.length; i++ ){
tSQL += aClobNames[i] + "=empty_clob()";
if (i < (aClobNames.length - 1))
tSQL += ", ";
}
tSQL += " ";
tSQL += aWhere;
tSt.execute (tSQL);
} finally {
if(tSt != null) {
tSt.close();
}
}
} catch (SQLException e) {
throw new KFM_SQLException(e, tSQL);
}
}
/** Execute a SQL statement that returns a single ResultSet
* (typically this is a static SQL SELECT statement).
*
* To define a querystring use prepareStmt methode.
* Use `executeUpdate� for SQL-statements that produce no result.
*
* @param aStmtId id of the statement.
* @return a ResultSet that contains the data produced by the query; never null.
*
* @throws ProgrammerException on none exisiting statementid.
* @throws KFM_SQLException on executeQuery problems.
*/
public ResultSet executeQuery(int aStmtId)
throws KFM_SQLException
{
KFM_PrepStmtItem tPrepStatementItem = null;
//Y try {
tPrepStatementItem = getPreparedStmtItemNo(aStmtId); //Get statement with aStmtId.
String t = tPrepStatementItem.toString();
if(mDatabase instanceof KFM_Database2) {
if(mDatabase.getAutoCommit()) {
log_detail("KFM_JdbcAdapter/KFM_Database2: Autocommited execute Query " + t);
} else {
log_detail("KFM_JdbcAdapter/KFM_Database2: execute Query " + t);
((KFM_Database2) mDatabase).setDoingSomething(true);
}
} else {
log_detail("KFM_JdbcAdapter/KFM_Database: execute Query " + t);
}
try {
PreparedStatement tPs = tPrepStatementItem.getStmt();
return tPs.executeQuery();
} catch(SQLException ex) {
throw new KFM_SQLException(ex, tPrepStatementItem.toString());
}
//Y } catch(Exception aE) {
//Y throw new ProgrammerException(
//Y "KFM_JdbcAdapter::executeQuery: "
//Y + " nested Exception is\n" + aE.getMessage());
//Y }
}
/** Commit all statements since the last rollback/commit.
*
* ThH schrieb:
*
* > Mu� man immer `KFM_JdbcAdapter::commit()� aufrufen? Gibt es noch ein
* > Autocommit, oder ist das weggefallen? In der Schnittstelle taucht nix
* > zu Autocommit auf.
*
* GUS antwortete:
*
* Das Autocommit kannst Du direkt auf der KFM_Database ausf�hren - so Du
* m�chtest (also meistens ausschalten).
* Der Hintergrund: Man kann so mehrere KFM_JDBCAdapter verwendern und
* an einer zentralen Stelle bestimmen, da� nun alles oder nix commitet
* wird.
*
* @throws ProgrammerException on none open database connection.
* @throws KFM_SQLException on commit problems.
*/
public void commit()
throws KFM_SQLException
{
// Only try a commit on existing connection.
if(mConnection != null) {
try {
mConnection.commit();
} catch(SQLException ex) {
throw new KFM_SQLException(ex);
}
} else {
throw new ProgrammerException("KFM_JdbcAdapter::commit: " +
"Connection not opened.");
}
if(mDatabase instanceof KFM_Database2) {
log_detail("KFM_JdbcAdapter::commit");
((KFM_Database2) mDatabase).setDoingSomething(false);
}
}
/** Rollback <strong>all</strong> statements (since the last rollback/commit).
*
* @throws ProgrammerException on none open database connection.
* @throws KFM_SQLException on rollback problems.
*/
public void rollback()
throws KFM_SQLException
{
// Only try rollback /on existing connnection.
if(mConnection != null) {
try {
mConnection.rollback();
} catch(SQLException ex) {
throw new KFM_SQLException(ex);
}
} else {
throw new ProgrammerException(
"KFM_JdbcAdapter::rollback: Connection not opened.");
}
if(mDatabase instanceof KFM_Database2) {
log_detail("KFM_JdbcAdapter::rollback");
((KFM_Database2) mDatabase).setDoingSomething(false);
}
}
/** Remove all unused statements at the end of the statements vector.
*
* Note: Only the last statement in the vector can be removed, without changing the
* order (and the statementId!) of the statements.
*/
protected synchronized void garbageCollection()
{
if(mStatement != null) {
try {
while(mStatement.size()>0 && mStatement.lastElement() == null)
// While a last statement exists and is null, remove it
mStatement.removeElementAt(mStatement.size()-1);
} catch(NoSuchElementException aNoSuchElementException) {
// Normally never reached, because only the last existing element of the vector will be
//removed.
}
}
}
/*** Get statement with aStmtId from vector.
*
* @param aStmtId id of the statement.
* @return the statementItem on position aStmtId.
* @throws ProgrammerException on none existing statementId.
* @throws ProgrammerException on none existing statement.
*/
public synchronized KFM_PrepStmtItem getPreparedStmtItemNo(int aStmtId)
{
if(aStmtId < 0) {
//aStmtId is below lower bound.
throw new ProgrammerException(
"KFM_JdbcAdapter::getPreparedStmtItemNo: statement selection <= 0.");
}
if(aStmtId >= mStatement.size()) {
//aStmt is above upper bound.
throw new ProgrammerException(
"KFM_JdbcAdapter::getPreparedStmtItemNo: Statement " + aStmtId + " not found.");
}
KFM_PrepStmtItem tPrepStmtItem = (KFM_PrepStmtItem) mStatement.elementAt(aStmtId);
if(tPrepStmtItem == null) {
// Statement does not exist.
throw new ProgrammerException(
"KFM_JdbcAdapter::getPreparedStmtItemNo: "
+ "Statement " + aStmtId + " is not available. "
+ "Prepare statement before use.");
}
return tPrepStmtItem;
}
/**
* A helper method to log a line of info either to KFMSystem.log or to mLog (if set).
*/
private void log_detail(String aText)
{
if (mLog == null)
KFMSystem.log.detail(aText);
else
mLog.detail(aText);
}
/**
* A helper method to log an error either to KFMSystem.log or to mLog (if set).
*/
private void log_error(String aText, Exception ex)
{
if (mLog == null)
KFMSystem.log.error(aText, ex);
else
mLog.error(aText, ex);
}
/** Computes identities for oracle DB id entries.
*
* <P>It must be something that fits into a SQL `CHAR(40)� field.</P>
*
* <P>Currently the identity is constructed with an `java.rmi.server.UID� (which is unique enough for RMI,
* so we thougt it should be unique enough for our DB) and the IP-address of the host (without the dots).</P>
*
* <P>In JDK1.1, a UID has the format `unique:time:count� where unique is an int in hex, time a long in
* hex and count a short in hex, e.g. `71f051:e7ccffaf33:-7ffe�. Add the IP-address (without the dots),
* and you get something like `71f051:e7ccffaf33:-7ffe1392318628.�</P>
*
* <P>@@@ To do: This code should actually parse the UID and *verify* this format!!!</P>
*
* <P>@@@ To do: Refactor code and documentation of KFM_JdbcAdapter.setIdValue
* so that these concepts are *near* each other.</P>
*/
public static String computeIdentity()
{
String tIdent = null;
UID tUid = new UID();
tIdent = tUid.toString();
try {
java.net.InetAddress tInet = java.net.InetAddress.getLocalHost();
String tAddress = tInet.getHostAddress();
StringTokenizer tTokenizer = new StringTokenizer(tAddress, ".");
while (tTokenizer.hasMoreTokens()){
String tNext = tTokenizer.nextToken();
tIdent += tNext;
}
} catch (java.net.UnknownHostException ex) {
KFMSystem.log.error("", ex);
}
return tIdent;
}
/**
* Converts a string that is not included by 'setValue' but as it's value for the database..
* Especially it escapes single quotes for the database.
* @param aStr the string to convert
* @return a string with escaped single quotes
*/
public static String convertString(String aStr)
{
String tStr = aStr;
int tIndex = -2;
while ((tIndex = tStr.indexOf("'", (tIndex + 2))) != -1)
{
String tempStr = "";
if (tIndex < tStr.length())
tempStr = tStr.substring(tIndex, tStr.length());
tStr = tStr.substring(0, tIndex);
tStr += "'";
tStr += tempStr;
}
return tStr;
}
// ************************************************************
// Debug
// ************************************************************
/** Demonstrate how to use the KFM_JdbcAdapter.
*/
public static void main(String[] aArgv)
{
try {
KFM_Database tDb= new KFM_Database(
(KFMLog)null, //Sorry no logfilewriter.
"sun.jdbc.odbc.JdbcOdbcDriver",
"jdbc:odbc:ZT-KM", //The ZT-KM database.
"", //No defined username.
""); //No password.
tDb.establishConnection();
try {
// Create a new adapter on a DB with an established connection.
KFM_JdbcAdapter tJdbcAdapter = new KFM_JdbcAdapter(tDb);
tJdbcAdapter.open();
try {
// The query string.
//T String tSelect = "SELECT Id, V_Date FROM V_Statistic_Page_Visits WHERE V_Date > ?";
//T int tStmtId = tJdbcAdapter.prepareStmt(tSelect); //Create a prepared statment.
String tUpdate = "INSERT INTO Statistic_Visits (Username, Page_Id, Visiting_Date) "
+ " VALUES ('nulltest', 17, ?)";
int tStmtId = tJdbcAdapter.prepareStmt(tUpdate); //Create a prepared statment.
try {
//T tJdbcAdapter.setNull(tStmtId, 1, java.sql.Types.BINARY); //Set the values to null.
// tJdbcAdapter.setNull(tStmtId, 1, java.sql.Types.TIMESTAMP); //Set the values to null.
// OK: KFM_PrepStmtItem tPrepStatementItem = tJdbcAdapter.getPreparedStmtItemNo(tStmtId);
// tPrepStatementItem.getStmt().setTimestamp(1, new Timestamp(123456789));
// Ha! Gotcha! This works for Access, even if it should be a TIMESTAMP.
tJdbcAdapter.setNull(tStmtId, 1, java.sql.Types.INTEGER); //Set the values to null.
//T java.sql.ResultSet tRs = tJdbcAdapter.executeQuery(tStmtId); //Execute the query.
tJdbcAdapter.executeUpdate(tStmtId); // Execute the update.
//T try {
//T while(tRs.next()) { //While a next resultsetrow exist.
//T log_detail(tRs.getString(1)); //Get the Id.
//T }
//T } catch(SQLException aSqlEx) {// Catch possible resultsetexceptions.
//T log_detail("KFM_JdbcAdapter::main" + aSqlEx.getMessage());
//T }
} finally {
tJdbcAdapter.closeStmt(tStmtId);
}
} finally {
tJdbcAdapter.close();
}
} finally {
tDb.freeConnection();
}
} catch (Exception aE) {
KFMSystem.log.info("Exception on KFM_JdbcAdapter::main " + aE.getMessage());
}
} //End main.
} //End class KFM_JdbcAdapter.