/* ======================================================================
   Parts Copyright 2006 University of Leeds, Oxford University, University of the Highlands and Islands.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

====================================================================== */

package org.bodington.sqldatabase;

import java.lang.reflect.*;
import java.sql.*;
import java.util.*;
import java.math.*;

import org.apache.log4j.Logger;

import org.bodington.database.*;
import org.bodington.server.*;
import org.bodington.pool.*;
import org.bodington.util.SoftCache;

/**
 * An implementation of org.bodington.Database that uses an SQL
 * database for storage of objects. As Bodington now has several ways
 * of getting a database connection from a pool, the property sqldatabase.pool allows
 * this to be configured.
 * 
 * This class should work when autocommit is both on and off. When autocommit is on 
 * some methods will switch it off perform a set of SQL statements inside a transaction,
 * then commit the transaction and set autocommit back on. When autocommit is off
 * this class never commits the transaction. This enables the calling code to manage
 * the transaction if it wishes. 
 * 
 * <p>
 * Properties:
 * <table border="1">
 * <tr>
 *  <th>Property</th><th>Description</th><th>Default</th><th>Required</th>
 * </tr>
 * <tr>
 *  <td>sqldatabase.pool</td>
 *  <td>The database pooling code to use. 
 *   <ul>
 *    <li>bodington - {@link org.bodington.database.BodingtonConnectionPool}</li>
 *    <li>commons - {@link org.bodington.database.CommonsConnectionPool}</li>
 *    <li>jndi - {@link org.bodington.database.JNDIConnectionPool}</li>
 *   </ul>
 *  </td> 
 *  <td>bodington</td>
 *  <td>No</td>
 * </tr>
 * <tr>
 *  <td>sqldatabase.driver</td>
 *  <td>
 *   The JDBC driver class. This allow Bodington to know the database it is connected to
 *   and adjust its behaviour. It is recommended that you set this although not required
 *   for some databases.
 *  </td>
 *  <td></td>
 *  <td>No</td>
 * </tr>
 * <tr>
 *  <td>sqldatabase.objectcache.interval</td>
 *  <td>The time in seconds between checks (and cleans) of the cache.</td>
 *  <td>20</td>
 *  <td>No</td>
 * </tr>
 * </table>
 * 
 * @author Jon Maber
 * @author buckett
 * 
 * @see org.bodington.database.ConnectionPool
 * @see org.bodington.database.JNDIConnectionPool
 * @see org.bodington.database.CommonsConnectionPool
 * @see org.bodington.database.BodingtonConnectionPool
 */

public class SqlDatabase extends Database
	{
    
    private static Logger log = Logger.getLogger(SqlDatabase.class);
    
	boolean destroyed = false;


	static boolean jdbc_unicode_string_literals = true;
	static boolean jdbc_is_oracle = false;
	static boolean jdbc_tinyint_glitch = false;
                
        
	int pool_size, pool_user_max, pool_user_greedy_max;
	Connection sys_con=null;
	ConnectionPool connection_pool;
	
	Statement sys_statement=null;


	Hashtable types_by_type, types_by_java_class;
	


	SoftCache object_cache;

	BigDecimal overflow_big_decimal[];
	BigInteger overflow_big_integer[];

	/**
	 * Constructor does very little.
	 * 
	 * @exception java.lang.Exception Not clear yet what exceptions should be thrown.
	 */


	public SqlDatabase()
		throws Exception
		{
		}

	/**
	 * Uses properties from BuildingServer to find database and
	 * log into it.
	 * 
	 * @exception java.lang.Exception Not clear yet what exceptions to throw.
	 * @exception BuildingServerException If the connection pool can't be setup.
	 */
		
	public void init( Properties properties )
		throws Exception
		{
		StringBuffer nines = new StringBuffer();
		nines.append( "1" );
		overflow_big_decimal = new BigDecimal[100];
		for ( int n=0; n<100; n++ )
			{
			overflow_big_decimal[n] = new BigDecimal( nines.toString() );
			nines.append( "0" );
			}
			
		// zero precision and zero scale may indicate unknown values so allow anything through...
		overflow_big_decimal[0] = new BigDecimal( nines.toString() );

		
		connection_pool = getConnectionPool(properties);
		if (connection_pool == null)
		{
			throw new BuildingServerException("Connection Pool not available");
		}
       	
		sys_con = connection_pool.getConnection(null);
	    
	    DatabaseMetaData dbmd = sys_con.getMetaData();
	    log.info( "Initialising Database" );
	    log.info("Database Product: " + dbmd.getDatabaseProductName() );
	    log.info("Database Version: " + dbmd.getDatabaseProductVersion() );
	    log.info("JDBC Driver: " + dbmd.getDriverName() );
	    log.info("Driver Version: " + dbmd.getDriverVersion() );
        log.info("Autocommit: "+ sys_con.getAutoCommit() );
        log.info("Transaction Isolation: "+ sys_con.getTransactionIsolation());
        if (! sys_con.getAutoCommit() )
            log.error("Autocommit should be enabled on database connections.");
        
		sys_statement = sys_con.createStatement();
		
        jdbc_unicode_string_literals = checkUnicodeStrings();
        
        checkDatabase(properties.getProperty("sqldatabase.driver"));
	    
        int interval = 20;
		try
		{
		    interval = Integer.parseInt(properties.getProperty( "sqldatabase.objectcache.interval"));
		}
		catch (NumberFormatException nfe)
		{}
        object_cache = new SoftCache( interval );  
		
		super.init( properties );
		
		sys_statement.close();
		connection_pool.freeConnection(sys_con);
		sys_statement = null;
		sys_con = null;
		}

        
    /**
     * Set the flags for dealing with database bugs. As you can't reliably find
     * out the driver from a connection we have to get the driver from the 
     * properties.
     * @param driver The class name of the JDBC driver.
     */
    private void checkDatabase(final String driver)
    {
        if (driver == null)
            return;
        
        if (driver.equals("oracle.jdbc.driver.OracleDriver"))
        {
            jdbc_is_oracle = true;
        }
        else if ( driver.equals("connect.microsoft.MicrosoftDriver"))
        {
            jdbc_tinyint_glitch = true;
        }
    }

    /**
     * Attempt to setup the connection pool.
     * @param properties
     */
    private ConnectionPool getConnectionPool(Properties properties)
    {
    		String poolCode = properties.getProperty("sqldatabase.pool", "bodington");
    		if ( ("commons").equalsIgnoreCase(poolCode)) 
    		{
    			try
    			{
    				ConnectionPool pool =  new CommonsConnectionPool(properties);
    				pool.freeConnection(pool.getConnection(null));
    				return pool;
    			}
    			catch (ConnectionPoolException cpe)
    			{
    				log.error("Commons Database Connection Failed.", cpe);
    			}
    		}
    		else if ( ("jndi").equalsIgnoreCase(poolCode))
    		{
    			try
    			{
    				return new JNDIConnectionPool(properties);
    			}
    			catch (ConnectionPoolException cpe)
    			{
    				log.error("JNDI Database Connection Failed", cpe);
    			}
    		}
    		else if ( ("bodington").equalsIgnoreCase(poolCode))
    		{
    			try
    			{
    				return new BodingtonConnectionPool(properties);
    			}
    			catch (ConnectionPoolException cpe)
    			{
    				log.error("Bodington ConnectionPool Failed.", cpe);
    			}
    		}
    		else
    		{
    			log.error("No connection pool specified. Please set sqldatabase.pool property");
    		}

		return null;
    }

    /**
     * Check if the database handles UNICODE strings.
     * These are strings prefixed with a N. 
     */
    private boolean checkUnicodeStrings()
    {

        try
        {
            ResultSet results = sys_statement.executeQuery( "select * from resources where name = N'thingy doo dah whatsit'" );
            results.close();
        }
        catch ( SQLException sqlex )
        {
            // some database will throw exception because it doesn't like the N
            // PostgreSQL 7.4 doesn't
            return false;
        }
        return true;
    }

    public void destroy()
    {
	    	destroyed = true;
	    	// make sure the cache stops its thread
	    	object_cache.destroy();
	    	// make it and all its contents garbage
	    	object_cache=null;
	    	// make connection pool and the connections garbage
	    	connection_pool = null;
    }
    
    
	/**
	 * Looks in the tables "classes" and "fields" to find out
	 * all the different types of java class that are stored in
	 * the database.
	 * 
	 * @exception org.bodington.server.BuildingServerException Thrown for various error conditions.
	 * @exception java.sql.SQLException Untrapped SQL problems while loading types.
	 */

	public void loadTypes()
		throws BuildingServerException, SQLException
		{
		SqlClass sqlclass, supersqlclass, foreignsqlclass;
		SqlField sqlfield;
		ResultSet results;
		int type, scale, precision;
		Class sqlpersistentobject;
		
		try
			{
			sqlpersistentobject = Class.forName( "org.bodington.sqldatabase.SqlPersistentObject" );
			}
		catch ( Exception ex )
			{
			log.fatal( ex.getMessage(), ex );
			throw new BuildingServerException( "Unable to find class SqlPersistentObject\n" + ex.toString() );
			}
		
		
		types_by_type = new Hashtable();
		types_by_java_class = new Hashtable();
		
		results=sys_statement.executeQuery( "SELECT * FROM classes" );
		while ( results.next() )
			{
			sqlclass= new SqlClass();
			sqlclass.type = results.getInt( "type" );
			if ( results.wasNull() )
				throw new BuildingServerException( "Null type in classes table" );

			sqlclass.super_type = results.getInt( "super_type" );
			if ( results.wasNull() )
				sqlclass.super_type=0;

			sqlclass.db_name =  results.getString( "db_name" );
			if ( results.wasNull() )
				sqlclass.db_name = null;
			
			sqlclass.table_name =  results.getString( "table_name" );
			if ( results.wasNull() )
				sqlclass.table_name = null;
			
			sqlclass.java_class =  results.getString( "java_class" );
			if ( results.wasNull() )
				throw new BuildingServerException( "Null java class in classes table" );
			

			sqlclass.archive =  results.getString( "archive" );
			if ( results.wasNull() )
				sqlclass.archive=null;
			

			if ( sqlclass.getDataClass()==null )
				{
				BuildingServer.codeTrace( "Problem getting Class for database types." );
				throw new BuildingServerException( "No class file in CLASSPATH for database class name: " + sqlclass.java_class );
				}
			
			types_by_type.put( new Integer( sqlclass.type ), sqlclass );
			types_by_java_class.put( sqlclass.java_class , sqlclass );
			log.info( "Loaded database class: " + sqlclass.java_class );
			}
		results.close();

		//cross reference super classes and sub classes
		for ( Enumeration enumeration= types_by_type.elements(); enumeration.hasMoreElements(); )
			{
			sqlclass = (SqlClass)enumeration.nextElement();
			if ( !sqlpersistentobject.isAssignableFrom( sqlclass.getDataClass() ) )
				throw new BuildingServerException( "Type must be sub-class of SqlPersistentObject" );

			if ( sqlclass.super_type == 0 )
				continue;
			supersqlclass = (SqlClass)types_by_type.get( new Integer( sqlclass.super_type ) );
			if ( supersqlclass == null )
				throw new BuildingServerException( "Classes table contains class with non-existent super class" );
			if ( supersqlclass.super_type!=0 )
				throw new BuildingServerException( "Subclasses deeper than one level not supported." );

			if ( !supersqlclass.getDataClass().isAssignableFrom( sqlclass.getDataClass() ) )
				throw new BuildingServerException( "Type " + sqlclass.getDataClass() + " must be sub-class of Super-Type " + supersqlclass.getDataClass() );

			sqlclass.super_class = supersqlclass;
			supersqlclass.sub_classes.put( new Integer( sqlclass.type ), sqlclass );
			}
		
		
		//get the fields
		results=sys_statement.executeQuery( "SELECT * FROM fields ORDER BY type, sequence" );
		while ( results.next() )
			{
			sqlfield= new SqlField();
			sqlfield.type = results.getInt( "type" );
			if ( results.wasNull() )
				throw new BuildingServerException( "Null type in fields table" );

			sqlfield.field =  results.getString( "field" );
			if ( results.wasNull() )
				throw new BuildingServerException( "Null field in fields table" );
			
			sqlfield.set_method_name =  results.getString( "set_method_name" );
			sqlfield.get_method_name =  results.getString( "get_method_name" );
			sqlfield.set_java_class =  results.getString( "set_java_class" );
			sqlfield.get_java_class =  results.getString( "get_java_class" );

			sqlfield.flags = results.getInt( "flags" );
			if ( results.wasNull() )
				sqlfield.flags=0;

			sqlfield.foreign_type = results.getInt( "foreign_type" );
			if ( results.wasNull() )
				sqlfield.foreign_type=0;
			sqlfield.set_method_name_foreign =  results.getString( "set_method_name_foreign" );
			sqlfield.get_method_name_foreign =  results.getString( "get_method_name_foreign" );
				
			sqlclass = (SqlClass)types_by_type.get( new Integer( sqlfield.type ) );
			if ( sqlclass != null )
				{
				if ( (sqlfield.flags & SqlField.PK) != 0 )
					sqlclass.primary_key_field = sqlfield;
				sqlclass.fields_in_order.addElement( sqlfield );
				sqlclass.fields.put( sqlfield.field, sqlfield );
				}
			}
		results.close();
		
		String[] primative_names= { "boolean", "char", "byte", "short", "int", "long", "float", "double", "void" };
		Class[] primative_classes= { Boolean.TYPE, Character.TYPE, Byte.TYPE, Short.TYPE, Integer.TYPE, 
										Long.TYPE, Float.TYPE, Double.TYPE, Void.TYPE };

		// run through all the loaded data and set references to Class and Method objects
		Class getp[] = new Class[1];
		Class setp[] = new Class[1];
		for ( Enumeration enumeration= types_by_type.elements(); enumeration.hasMoreElements(); )
			{
			sqlclass = (SqlClass)enumeration.nextElement();
			sqlclass.update();
			sqlfield=null;
			try
				{
				for ( int i=0; i< sqlclass.fields_in_order.size(); i++ )
					{
					sqlfield = (SqlField)sqlclass.fields_in_order.elementAt( i );
					if ( sqlfield.set_method_name!=null && sqlfield.set_java_class!=null )
						{
						boolean primative=false;
						for ( int j=0; j<primative_names.length; j++ )
							{
							if ( sqlfield.set_java_class.equals( primative_names[j] ) )
								{
								primative=true;
								setp[0] = primative_classes[j];
								break;
								}
							}
						if ( !primative )
							setp[0] = Class.forName( sqlfield.set_java_class );
						//BuildingServer.codeTrace( "Looking for " + sqlfield.set_method_name + "( " + setp[0] + ")" );
						sqlfield.set_method = sqlclass.getDataClass().getMethod( sqlfield.set_method_name, setp );
						}
					if ( sqlfield.get_method_name!=null && sqlfield.get_java_class!=null )
						{
						sqlfield.get_method = sqlclass.getDataClass().getMethod( sqlfield.get_method_name, null );
						}
					}
				}
			catch ( Exception ex )
				{
				BuildingServer.codeTrace( "Exception getting Class for database types." );
				BuildingServer.codeTrace( sqlclass.java_class );
				log.error( ex.getMessage(), ex );
				throw new BuildingServerException( 
					"Exception getting Class for database types." + sqlclass.java_class + 
					" " + sqlfield==null?"null":sqlfield.field + "\n" + ex );
				}
			}
				
			// Now check meta data on tables to see if they work with the declared data types
		for ( Enumeration enumeration= types_by_type.elements(); enumeration.hasMoreElements(); )
			{
			sqlclass = (SqlClass)enumeration.nextElement();
			
			// no need to check class if it doesn't have a table of data
			if ( sqlclass.table_name == null )
				continue;
				
			try
				{
				// query should return empty data set but all the correct meta data
				results=sys_statement.executeQuery(
							"SELECT * FROM " + sqlclass.table_name + 
							" WHERE " + sqlclass.primary_key_field.field + " IS NULL" );
				ResultSetMetaData metadata = results.getMetaData();
				int columns = metadata.getColumnCount();
				if ( columns==0 )
					throw new BuildingServerException( "No columns in table: " + sqlclass.table_name );
					
				Hashtable column_types = new Hashtable();
				Hashtable column_precision = new Hashtable();
				Hashtable column_scale = new Hashtable();
				for ( int c=1; c<=columns; c++ )
					{
					//Logger.getLogger( "org.bodington" ).info( "Table: " + sqlclass.table_name + " Field: " + metadata.getColumnName( c ) );
					column_types.put( metadata.getColumnName( c ), new Integer( c ) );
					if ( metadata.getColumnName( c ).equalsIgnoreCase( sqlclass.primary_key_field.field ) )
						{
						if ( jdbc_is_oracle )
							{
							if ( metadata.getColumnType( c ) != Types.NUMERIC )
								throw new BuildingServerException( "Primary key field on table " + sqlclass.table_name + " must be SQL type NUMERIC for Oracle." );
							}
						else
							{
							if ( metadata.getColumnType( c ) != Types.INTEGER )
								throw new BuildingServerException( "Primary key field on table " + sqlclass.table_name + " must be SQL type INTEGER." );
							}
						}
					}

				Integer sqltypecolumn;
				for ( int i=0; i< sqlclass.fields_in_order.size(); i++ )
					{
					sqlfield = (SqlField)sqlclass.fields_in_order.elementAt( i );
					sqltypecolumn = (Integer)column_types.get( sqlfield.field );
					if ( sqltypecolumn==null )
						{
						sqltypecolumn = (Integer)column_types.get( sqlfield.field.toUpperCase() );
						if ( sqltypecolumn==null )
							throw new BuildingServerException( "Column " + sqlfield.field + " not found in table " + sqlclass.table_name + "." );
						}
					
					type = metadata.getColumnType( sqltypecolumn.intValue() );
					precision = metadata.getPrecision( sqltypecolumn.intValue() );
					scale = metadata.getScale( sqltypecolumn.intValue() );
					
					// bodge to cope with JDBC drivers that fail to report precision and scale
					if ( precision == 0 && scale == 0 && (type == Types.NUMERIC || type == Types.DECIMAL) )
						{
						precision = 10;
						if ( sqlfield.set_java_class.equals( "java.math.BigInteger" ) )
							scale =		0;
						else
							scale =		10;
						}
					
					sqlfield.sql_type=type;
					sqlfield.sql_precision=precision;
					sqlfield.sql_scale=scale;

					log.debug( sqlfield.sql_type +" "+ sqlfield.set_java_class + " " + sqlfield.get_java_class + " precision = " + precision + " scale = " + scale );
					
					if ( type == Types.TINYINT )
						{
						if ( !sqlfield.get_java_class.equals( "int" ) &&
						     !sqlfield.get_java_class.equals( "short" ) &&
						     !sqlfield.get_java_class.equals( "byte" ) &&
						     !sqlfield.get_java_class.equals( "java.lang.Integer" ) &&
						     !sqlfield.get_java_class.equals( "java.lang.Short" ) &&
						     !sqlfield.get_java_class.equals( "java.lang.Byte" )       )
							throw new BuildingServerException( 
								"TINYINT Incompatible data types for get method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field );
						if ( !sqlfield.set_java_class.equals( "byte" ) &&
						     !sqlfield.set_java_class.equals( "java.lang.Byte" )       )
							throw new BuildingServerException( 
								"TINYINT Incompatible data types for set method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field + " type = " + sqlfield.set_java_class );
						continue;
						}

					if ( type == Types.SMALLINT )
						{
						if ( !sqlfield.get_java_class.equals( "int" ) &&
						     !sqlfield.get_java_class.equals( "short" ) &&
						     !sqlfield.get_java_class.equals( "byte" ) &&
						     !sqlfield.get_java_class.equals( "java.lang.Integer" ) &&
						     !sqlfield.get_java_class.equals( "java.lang.Short" ) &&
						     !sqlfield.get_java_class.equals( "java.lang.Byte" )       )
							throw new BuildingServerException( 
								"SMALLINT Incompatible data types for get method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field );
						//narrowing data conversion OK
						if ( !sqlfield.set_java_class.equals( "short" ) &&
						     !sqlfield.set_java_class.equals( "java.lang.Short" ) && 
						     !sqlfield.set_java_class.equals( "byte" ) &&
						     !sqlfield.set_java_class.equals( "java.lang.Byte" )    )
							throw new BuildingServerException( 
								"SMALLINT Incompatible data types for set method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field + " type = " + sqlfield.set_java_class );
						continue;
						}

					if ( type == Types.INTEGER )
						{
						if ( !sqlfield.get_java_class.equals( "int" ) &&
						     !sqlfield.get_java_class.equals( "short" ) &&
						     !sqlfield.get_java_class.equals( "byte" ) &&
						     !sqlfield.get_java_class.equals( "org.bodington.database.PrimaryKey" ) &&
						     !sqlfield.get_java_class.equals( "java.lang.Integer" ) &&
						     !sqlfield.get_java_class.equals( "java.lang.Short" ) &&
						     !sqlfield.get_java_class.equals( "java.lang.Byte" )       )
							throw new BuildingServerException( 
								"INTEGER Incompatible data types for get method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field );
						if ( !sqlfield.set_java_class.equals( "int" ) &&
						     !sqlfield.set_java_class.equals( "long" ) &&
						     !sqlfield.set_java_class.equals( "org.bodington.database.PrimaryKey" ) &&
						     !sqlfield.set_java_class.equals( "java.lang.Integer" ) &&
						     !sqlfield.set_java_class.equals( "java.lang.Long" )       )
							throw new BuildingServerException( 
								"INTEGER Incompatible data types for set method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field + " type = " + sqlfield.set_java_class );
						continue;
						}

					if ( type == Types.NUMERIC || type == Types.DECIMAL )
						{
						
						boolean ok=false;

						if ( jdbc_is_oracle && sqlfield.get_java_class.equals( "org.bodington.database.PrimaryKey" ) )
							{
							log.info( "PrimaryKey...." );
							if ( precision<10 )
								throw new BuildingServerException( 
									"Incompatible data types for get method (DB lacks sufficient precision to store an int). Table = " + 
									sqlclass.table_name + " Field = " + sqlfield.field );
							ok=true;
							if ( jdbc_is_oracle )
								sqlfield.sql_type	= Types.INTEGER;
							}

						if ( sqlfield.get_java_class.equals( "int" ) || sqlfield.get_java_class.equals( "java.lang.Integer" ) )
							{
							if ( precision<10 )
								throw new BuildingServerException( 
									"Incompatible data types for get method (DB lacks sufficient precision to store an int). Table = " + 
									sqlclass.table_name + " Field = " + sqlfield.field );
							ok=true;
							if ( jdbc_is_oracle )
								sqlfield.sql_type	= Types.INTEGER;
							}
						if ( sqlfield.get_java_class.equals( "byte" ) || sqlfield.get_java_class.equals( "java.lang.Byte" ) )
							{
							if ( precision<3 )
								throw new BuildingServerException( 
									"Incompatible data types for get method (DB lacks sufficient precision to store a byte). Table = " + 
									sqlclass.table_name + " Field = " + sqlfield.field );
							ok=true;
							if ( jdbc_is_oracle )
								sqlfield.sql_type	= Types.TINYINT;
							}
						if ( sqlfield.get_java_class.equals( "short" ) || sqlfield.get_java_class.equals( "java.lang.Short" ) )
							{
							if ( precision<5 )
								throw new BuildingServerException( 
									"Incompatible data types for get method (DB lacks sufficient precision to store a short). Table = " + 
									sqlclass.table_name + " Field = " + sqlfield.field );
							ok=true;
							if ( jdbc_is_oracle )
								sqlfield.sql_type	= Types.SMALLINT;
							}
						if ( sqlfield.get_java_class.equals( "long" ) || sqlfield.get_java_class.equals( "java.lang.Long" ) )
							{
							if ( precision<19 )
								throw new BuildingServerException( 
									"Incompatible data types for get method (DB lacks sufficient precision to store a long). Table = " + 
									sqlclass.table_name + " Field = " + sqlfield.field );
							ok=true;
							if ( jdbc_is_oracle )
								sqlfield.sql_type	= Types.BIGINT;
							}
						if ( sqlfield.get_java_class.equals( "float" ) || sqlfield.get_java_class.equals( "java.lang.Float" ) || 
						     sqlfield.get_java_class.equals( "double" ) || sqlfield.get_java_class.equals( "java.lang.Double" ) ||
						     sqlfield.get_java_class.equals( "java.math.BigDecimal" ) )
							{
							log.warn( "possible data incompatability for get method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field+ " Type = " + sqlfield.get_java_class );
							ok=true;
							}
						if ( sqlfield.get_java_class.equals( "java.math.BigInteger" ) )
							{
							if ( scale>0 )
								throw new BuildingServerException( 
									"Incompatible data types for get method (DB field is non integer). Table = " + 
									sqlclass.table_name + " Field = " + sqlfield.field );
							log.warn( "possible data incompatability for get method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field + " Type = " + sqlfield.get_java_class );
							ok=true;
							}


						if ( !ok )
							throw new BuildingServerException( 
								"Incompatible data types for get method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field );
							
						ok = false;
						
						if ( jdbc_is_oracle && sqlfield.get_java_class.equals( "org.bodington.database.PrimaryKey" ) )
							{
							if ( scale>0 )
								throw new BuildingServerException( 
									"Incompatible data types for set method (DB field is non integer). Table = " + 
									sqlclass.table_name + " Field = " + sqlfield.field );
							ok = true;
							}
							
						if ( sqlfield.set_java_class.equals( "int" ) || sqlfield.set_java_class.equals( "java.lang.Integer" ) ||
							sqlfield.set_java_class.equals( "byte" ) || sqlfield.set_java_class.equals( "java.lang.Byte" ) ||
							sqlfield.set_java_class.equals( "short" ) || sqlfield.set_java_class.equals( "java.lang.Short" ) ||
							sqlfield.set_java_class.equals( "long" ) || sqlfield.set_java_class.equals( "java.lang.Long" ) ||
							sqlfield.set_java_class.equals( "java.math.BigInteger" ) )
							{
							if ( scale>0 )
								throw new BuildingServerException( 
									"Incompatible data types for set method (DB field is non integer). Table = " + 
									sqlclass.table_name + " Field = " + sqlfield.field );
							ok = true;
							}
							
						if ( sqlfield.set_java_class.equals( "float" ) || sqlfield.set_java_class.equals( "java.lang.Float" ) ||
							sqlfield.set_java_class.equals( "double" ) || sqlfield.set_java_class.equals( "java.lang.Double" ) ||
							sqlfield.set_java_class.equals( "java.math.BigDecimal" ) )
							{
							ok = true;
							}
							
						if ( !ok )
							throw new BuildingServerException( 
								"Incompatible data types for get method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field );

						continue;
						}

					if ( type == Types.CHAR        ||
					     type == Types.VARCHAR     || 
					     type == Types.LONGVARCHAR     )
						{
						if ( !sqlfield.get_java_class.equals( "java.lang.String" ) )
							throw new BuildingServerException( 
								"Incompatible data types for get method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field );
						if ( !sqlfield.set_java_class.equals( "java.lang.String" ) )
							throw new BuildingServerException( 
								"Incompatible data types for set method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field + " type = " + sqlfield.set_java_class );
						continue;
						}

					if ( type == Types.TIMESTAMP )
						{
						if ( !sqlfield.get_java_class.equals( "java.sql.Timestamp" ) )
							throw new BuildingServerException( 
								"Incompatible data types for get method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field );
						if ( !sqlfield.set_java_class.equals( "java.sql.Timestamp" )   )
							throw new BuildingServerException( 
								"Incompatible data types for set method. Table = " + sqlclass.table_name + " Field = " + sqlfield.field + " type = " + sqlfield.set_java_class );
						continue;
						}
						
					throw new BuildingServerException( 
						"Unsupported SQL Type.  Table = "  + sqlclass.table_name + " Field = " + sqlfield.field );
					}
					
				results.close();
				}
			catch ( Exception ex )
				{
				BuildingServer.codeTrace( "Exception getting Class for database types." );
				BuildingServer.codeTrace( sqlclass.java_class );
				log.error( ex.getMessage(), ex );
				throw new BuildingServerException( "Exception getting Class for database types." + sqlclass.java_class + ex );
				}
			
			
			//sqlclass.dump();
			}


		// set up foreign refrences		
		for ( Enumeration enumeration= types_by_type.elements(); enumeration.hasMoreElements(); )
			{
			sqlclass = (SqlClass)enumeration.nextElement();
			try
				{
				for ( int i=0; i< sqlclass.fields_in_order.size(); i++ )
					{
					sqlfield = (SqlField)sqlclass.fields_in_order.elementAt( i );
					if ( (sqlfield.flags & SqlField.FK)!=0 )
						{
						foreignsqlclass=null;
						if ( sqlfield.foreign_type>0 )
							foreignsqlclass=(SqlClass)types_by_type.get( new Integer( sqlfield.foreign_type ) );
							
						if ( sqlfield.set_method_name_foreign!=null )
							{
							if ( foreignsqlclass==null )
								setp[0]=Class.forName( "org.bodington.sqldatabase.SqlPersistentObject" );
							else
								setp[0] = Class.forName( foreignsqlclass.java_class );
							//BuildingServer.codeTrace( "Looking for " + sqlfield.set_method_name_foreign + "( " + setp[0] + ")" );
							sqlfield.set_method_foreign = sqlclass.getDataClass().getMethod( sqlfield.set_method_name_foreign, setp );
							}
						if ( sqlfield.get_method_name_foreign!=null )
							{
							sqlfield.get_method_foreign = sqlclass.getDataClass().getMethod( sqlfield.get_method_name_foreign, null );
							}
						
						}
					}
				}
			catch ( Exception ex )
				{
				BuildingServer.codeTrace( "Exception getting Class for database types." );
				BuildingServer.codeTrace( sqlclass.java_class );
				log.error( ex.getMessage(), ex );
				throw new BuildingServerException( "Exception getting Class for database types." + sqlclass.java_class + ex );
				}
			//sqlclass.dump();
			}
		
		
		}

	/**
	 * Will eventually look in configuration information and
	 * then preload certain data objects.
	 * 
	 * @exception org.bodington.server.BuildingServerException Thrown for various problems.
	 * @exception java.sql.SQLException Thrown if the database isn't behaving.
	 */
	
	public void preloadData()
		throws BuildingServerException, SQLException
		{
		ResultSet results;
		results=sys_statement.executeQuery( "SELECT max(id) FROM objects" );
		if ( results==null )
			throw new BuildingServerException( "Unable to find primary key from database." );
		if ( !results.next() )
			throw new BuildingServerException( "Unable to find primary key from database." );

		// as long as the table exists and is accessible max(id) should return one record
		setNextPrimaryKey( new PrimaryKey( results.getInt( 1 )+1 ) );
		// but if the table is empty result will be null
		if ( results.wasNull() )
			setNextPrimaryKey( new PrimaryKey( 1 ) );
		results.close();
		}

	/**
	 * Allows caller to specify that referenced objects should be 
	 * recursively loaded.
	 * 
	 * @param primary_key The primary key of the requested object.
	 * @param java_class_name A class name to enforce or null if any type is acceptable.
	 * @return The requested object or null if not found.
	 * @exception org.bodington.server.BuildingServerException Thrown for various error conditions.
	 */
		
	public PersistentObject findPersistentObject( PrimaryKey primary_key, String java_class_name )
		throws BuildingServerException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );
		if ( java_class_name==null )
			throw new BuildingServerException( "java_class_name must be specified." );
		if ( primary_key==null )
		    return null;
		PersistentObject obj = (PersistentObject)object_cache.get( primary_key );
		if ( obj!=null ) return obj;
		return findPersistentObject( "id = " + primary_key, java_class_name );
		}

	public PersistentObject findPersistentObject( IndexKey index_key, String java_class_name )
		throws BuildingServerException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );
		if ( java_class_name==null )
			throw new BuildingServerException( "java_class_name must be specified." );
		if ( index_key==null )
		    return null;
		// if the cache knows for certain the record deosn't exists
		// return null now.
		if ( object_cache.notExists( index_key ) )
		    return null;
		    
		// maybe the cache knows it exists and has it
		PersistentObject obj = (PersistentObject)object_cache.get( index_key );
		if ( obj!=null )
		    return obj;
		
		// so the cache doesn't know one way or the other so go to the data store
		// (and along the way give the object to the cache if it exists)
		String where = index_key.whereClause();
		obj = findPersistentObject( where, java_class_name );
		if ( obj != null )
		    return obj;
		    
		// it wasn't in the data store so tell the cache
	    object_cache.put( index_key );
		return null;
		}

	public PersistentObject findPersistentObject( String where_clause, String java_class_name )
		throws BuildingServerException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );
		Enumeration enumeration = findPersistentObjects( where_clause, null, java_class_name );
		if ( !enumeration.hasMoreElements() )
			return null;
		PersistentObject obj = (PersistentObject)enumeration.nextElement();
		if ( enumeration.hasMoreElements() )
		{
		    log.warn("Looking for one object, found more (where: "+ where_clause+ ") (class: "+ java_class_name+ ") (found: "+ obj.getPrimaryKey()+ ")" );
			throw new BuildingServerException( "More than one record was found with the same id." );
		}
		return  obj;
		}

	public Enumeration findPersistentObjects( String where_clause, String order_by_clause, String java_class_name )
		throws BuildingServerException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );
		SqlClass sqlclass;
		sqlclass = (SqlClass)types_by_java_class.get( java_class_name );
		if ( sqlclass == null )
			throw new BuildingServerException( "Unknown java class for database: " + java_class_name );

		if ( sqlclass.sub_classes.isEmpty() )
			return findHomogenousPOs( where_clause, order_by_clause, sqlclass );
			
		return findHeterogenousPOs( where_clause, order_by_clause, sqlclass );
		}


	private Enumeration findHeterogenousPOs( String where_clause, String order_by_clause, SqlClass sqlclass )
		throws BuildingServerException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );
		// first make a list of primarykeys using the super table, where and order by clauses
		int index;
		PrimaryKey key;
		Vector big_list= new Vector();
		Hashtable key_list= new Hashtable();
		StringBuffer id_list = new StringBuffer();
		Enumeration objenum;
		
		id_list.append( "id IN (" );
		Enumeration keyenum = findPrimaryKeys( where_clause, order_by_clause, sqlclass.java_class );
		//record sequence of results hashed by key and use ids to make a where clause
		for ( index=0; keyenum.hasMoreElements(); index++ )
			{
			key = (PrimaryKey)keyenum.nextElement();
			key_list.put( key, new Integer( index ) );
			if ( index>0 )
				id_list.append( ", " );
			id_list.append( key.toString() );
			}
		id_list.append( ")" );

		//was nothing found?  Return empty list.
		if ( index==0 )
			return big_list.elements();
			
		// for each sub class table find records that are in the list found above.
		SqlClass sub_class;
		Integer position;
		PersistentObject obj;

		objenum = findHomogenousPOs( id_list.toString(), null, sqlclass );
		while ( objenum.hasMoreElements() )
			{
			obj = (PersistentObject)objenum.nextElement();
			position = (Integer)key_list.get( obj.getPrimaryKey() );
			if ( position!=null )
				{
				if ( position.intValue()>=big_list.size() )
					big_list.setSize(position.intValue()+1 );
				big_list.setElementAt( obj , position.intValue() );
				}
			}
			
		for ( Enumeration enumeration = sqlclass.sub_classes.elements(); enumeration.hasMoreElements(); )
			{
			sub_class = (SqlClass)enumeration.nextElement();
			objenum = findHomogenousPOs( id_list.toString(), null, sub_class );
			while ( objenum.hasMoreElements() )
				{
				obj = (PersistentObject)objenum.nextElement();
				position = (Integer)key_list.get( obj.getPrimaryKey() );
				if ( position!=null )
					{
					if ( position.intValue()>=big_list.size() )
						big_list.setSize(position.intValue()+1 );
					big_list.setElementAt( obj , position.intValue() );
					}
				}
			}
			
		// trim out unfound elements (how could there be unfound elements?)
		for ( int i=0; i< big_list.size(); i++ )
			if ( big_list.elementAt( i ) == null )
				big_list.removeElementAt( i-- );
		
		return big_list.elements();
		}

	public int countPersistentObjects(String where_clause, String java_class_name)
        throws BuildingServerException
    {
        if (destroyed)
            throw new IllegalStateException("SqlDatabase is destroyed");
        BuildingContext context = BuildingContext.getContext();
        if (context == null)
            throw new BuildingServerException(
                "Can't find objects - no context.");
		
		SqlClass sqlclass = (SqlClass)types_by_java_class.get( java_class_name );
		if ( sqlclass == null )
			throw new BuildingServerException( "Unknown java class for database." );
		
        Connection con;
        try
        {
            con = context.getConnection();
        }
        catch (ObjectPoolException e1)
        {
            throw new BuildingServerException(
                "Can't get database connection from pool");
        }
        if (con == null)
            throw new BuildingServerException(
                "Can't find objects - no database connection.");

        PreparedStatement st = null;
        ResultSet results = null;
        StringBuffer sql = new StringBuffer(200);

        try
        {

            sql.append("SELECT count(*) FROM objects");
            if (sqlclass.super_class != null
                && sqlclass.super_class.table_name != null)
            {
                sql.append(", ");
                sql.append(sqlclass.super_class.table_name);
            }
            if (sqlclass.table_name != null)
            {
                sql.append(", ");
                sql.append(sqlclass.table_name);
            }
            sql.append(" WHERE type = ");
            sql.append(sqlclass.type);
            if (sqlclass.table_name != null)
            {
                sql.append(" AND id = ");
                sql.append(sqlclass.primary_key_field.field);
            }
            if (sqlclass.super_class != null
                && sqlclass.super_class.table_name != null)
            {
                sql.append(" AND id = ");
                sql.append(sqlclass.super_class.primary_key_field.field);
            }
            sql.append(" AND ( ");
            sql.append(where_clause);
            sql.append(" )");

            st = con.prepareStatement(sql.toString());
            st.execute();
            results = st.getResultSet();
             results.next();
             return results.getInt(1);
        }
        catch (SQLException sqle)
        {
            throw new BuildingServerException("Database error fetching data: "
                + sqle);
        }
        finally
        {
            try
            {
                if (st != null)
                {
                    st.close();
                }
                if (results != null)
                {
                    results.close();
                }
            }
            catch (SQLException sqle)
            {
                log.warn("Problem tidying up.");
            }
        }
    }

	/**
	 * Will fetch a list of objects given an SQL like 
	 * WHERE clause. This only supports one level of inheritance so tables can't
	 * have grandparents.
	 * 
	 * @param where_clause Search pattern in style of and SQL WHERE clause (without the 'WHERE')
	 * @return An enumeration of the objects found.  (The database results set might
	 * remain open until the callerd completes the enumeration.)
	 */
	
	private Enumeration findHomogenousPOs( String where_clause, String order_by_clause, SqlClass sqlclass )
		throws BuildingServerException
		{
	    if ( destroyed )
	        throw new IllegalStateException( "SqlDatabase is destroyed" );

	    ResultSet results;
	    PersistentObject return_object;
	    StringBuffer sql = new StringBuffer( 200 );

	    if ( sqlclass == null )
	        throw new BuildingServerException( "Unknown java class for database." );

	    Vector objects = new Vector();
	    Statement st = null;

	    try
	    {
	        BuildingContext context = BuildingContext.getContext();
	        if ( context==null )
	            throw new BuildingServerException( "Can't find objects - no context." );

	        Connection con = context.getConnection();
	        if ( con==null )
	            throw new BuildingServerException( "Can't find objects - no database connection." );



	        st = con.createStatement();

	        sql.append(   "SELECT * FROM objects"         );
	        if ( sqlclass.super_class != null && sqlclass.super_class.table_name != null )
	        {
	            sql.append( ", " );
	            sql.append( sqlclass.super_class.table_name );
	        }
	        if ( sqlclass.table_name != null )
	        {
	            sql.append( ", " );
	            sql.append(   sqlclass.table_name           );
	        }
	        sql.append(   " WHERE type = "				);
	        sql.append(   sqlclass.type 				);
	        if ( sqlclass.table_name != null )
	        {
	            sql.append(   " AND id = "                  );
	            sql.append(   sqlclass.primary_key_field.field  );
	        }
	        if ( sqlclass.super_class != null && sqlclass.super_class.table_name != null )
	        {
	            sql.append(   " AND id = "                    );
	            sql.append(   sqlclass.super_class.primary_key_field.field  );
	        }
	        sql.append(   " AND ( "                         );
	        sql.append(   where_clause                      );
	        sql.append(   " )"                             );
	        if ( order_by_clause != null )
	        {
	            sql.append( " ORDER BY " );
	            sql.append( order_by_clause );
	        }

	        results = st.executeQuery( sql.toString() );
	        if ( results==null )
	        {
	            return objects.elements();  //i.e return an empty enumeration
	        }

	        int pk;
	        PrimaryKey primary_key;
	        for ( int n=0; results.next(); n++ )
	        {
	            if ( (n%100) == 99 )
	            {
	                if ( Runtime.getRuntime().freeMemory()< (1024*1024*10) )
	                {
	                    results.close();
	                    throw new BuildingServerException( 
	                        "The search for records in the database was abandonded because " + 
	                        "the Bodington System has insufficient free memory for the operation. " +
	                        "(Because too many records were found or many other users are searching " +
	                    "for large numbers of records.)" );
	                }
	            }

	            pk = results.getInt( "id" );
	            if ( results.wasNull() )
	                continue;
	            primary_key = new PrimaryKey( pk );
	            return_object = (PersistentObject)object_cache.get( primary_key );
	            if ( return_object==null )
	            {
	                return_object=fetchObjectFromResultSet( results, sqlclass );
	                if ( return_object!=null ) 
                    {
                        return_object.clearUnsaved();                       
                        object_cache.put( primary_key, return_object );
                    }
	            }
	            if ( return_object!=null )
	                objects.addElement( return_object );
	        }
	        results.close();

	        for ( Enumeration enumeration = objects.elements(); enumeration.hasMoreElements(); )
	        {
	            return_object=(PersistentObject)enumeration.nextElement();
	            fetchReferencedObjects( return_object, sqlclass );

	        }

	    }
	    catch ( ObjectPoolException opex )
	    {
	        throw new BuildingServerException( opex );
	    }
	    catch ( SQLException sqlex )
	    {
	        log.error("SQL Error on: "+ sql, sqlex );
	        BuildingServer.codeTrace( sqlex.toString() );
	        // Think it's better to throw the exception so that the
	        // whole request is aborted.  Otherwise, a lengthy loop
	        // might continue running for a long time repeatedly
	        // calling this routine.
	        throw new BuildingServerException( "Database error fetching data: " + sqlex.toString() );
	        //objects.removeAllElements();
	        //return objects.elements();
	    }
	    catch ( InvocationTargetException itex )
	    {
	        log.error( itex.getMessage(), itex );
	        BuildingServer.codeTrace( itex.toString() );
	        throw new BuildingServerException( "Database error loading a record: " + itex.toString() );
	        //objects.removeAllElements();
	        //return objects.elements();
	    }
	    catch ( IllegalAccessException iaex  )
	    {
	        log.error( iaex.getMessage(), iaex );
	        BuildingServer.codeTrace( iaex.toString() );
	        throw new BuildingServerException( "Database error loading fields into a record: " + iaex.toString() );
	        //objects.removeAllElements();
	        //return objects.elements();
	    }
	    finally
	    {
	        try
	        {
	            if (st != null)
	            {
	                st.close();
	            }
	        } 
	        catch (Exception e)
	        {
	            log.warn("Failed to close statement");
	        }
	    }

	    return objects.elements();
		}

	/**
	 * Fetches just the primary keys for a list of objects given an SQL like 
	 * WHERE clause.
	 * 
	 * @param where_clause Search pattern in style of and SQL WHERE clause (without the 'WHERE')
	 * @return An enumeration of the objects found.  (The database results set might
	 * remain open until the caller completes the enumeration.  Would require special 
	 * implementation of Enumeration.)
	 */
	
	public Enumeration findPrimaryKeys( String where_clause, String order_by_clause, String java_class_name )
		throws BuildingServerException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );

                int type;
		SqlClass sqlclass;
		String string_return;
		StringBuffer sql;
		
		Vector primary_keys = new Vector();
		
		sqlclass = (SqlClass)types_by_java_class.get( java_class_name );
		if ( sqlclass == null )
			throw new BuildingServerException( "Unknown java class for database." );
		
		try
			{
			BuildingContext context = BuildingContext.getContext();
			if ( context==null )
				return null;
				
			Connection con = context.getConnection();
			if ( con==null )
				throw new BuildingServerException( "Can't find objects - no context." );

			Statement st = con.createStatement();
			
			sql = new StringBuffer();
			sql.append( "SELECT id FROM objects" );
			if ( sqlclass.table_name!=null )
				{
				sql.append( ", " );
				sql.append( sqlclass.table_name );
				}
			sql.append( " WHERE" );
			if ( sqlclass.table_name!=null )
				{
				sql.append( " id = " );
				sql.append( sqlclass.primary_key_field.field  );
				sql.append( " AND" );
				}
			sql.append( " ( " );
			sql.append( where_clause );
			sql.append( " )" );
			if ( order_by_clause != null )
			    {
			    sql.append( " ORDER BY " );
			    sql.append( order_by_clause );
			    }

			ResultSet results = st.executeQuery( sql.toString() );
			if ( results==null )
				{
				st.close();
				return null;
				}
				
			int pk;
			PrimaryKey primary_key;
			while ( results.next() )
				{
				pk = results.getInt( "id" );
				if ( results.wasNull() )
					continue;
				primary_key = new PrimaryKey( pk );
				primary_keys.addElement( primary_key );
				}
			results.close();
			st.close();
			}
		catch ( ObjectPoolException opex )
			{
			throw new BuildingServerException( opex );
			}
		catch ( SQLException sqlex )
			{
			log.error( sqlex.getMessage(), sqlex );
			sqlex.printStackTrace();
			BuildingServer.codeTrace( sqlex.toString() );
			throw new BuildingServerException( "Database error fetching data: " + sqlex.toString() );
			//return null;
			}

		return primary_keys.elements();
		}

	/**
	 * Sychronises the object with the database.  Calls the "get" methods
	 * of the object and fills in a prepared statement using the values
	 * found.
	 * 
	 * @param obj The object to synchronise.
	 * @exception org.bodington.server.BuildingServerException Thrown for various error conditions.
	 */
	
	public void updateObject( PersistentObject obj )
		throws BuildingServerException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );

                SqlClass sqlclass;
		SqlField sqlfield;
		int rows, i, p;
		boolean localTransaction;
		Connection con;
		
		sqlclass = (SqlClass)types_by_java_class.get( obj.getClass().getName() );
		if ( sqlclass==null )
			throw new BuildingServerException( "Can't save object - unknown type." );
		
		try
			{
			BuildingContext context = BuildingContext.getContext();
			if ( context==null )
				throw new BuildingServerException( "Can't save object - no context." );
				
			con = context.getConnection();
			if ( con==null )
				throw new BuildingServerException( "Can't save object - no database connection." );
			// are we in a transaction already or do we need to set one up
			// for this little bit of work?
			localTransaction=con.getAutoCommit();
			}		
		catch ( ObjectPoolException opex )
			{
			throw new BuildingServerException( opex );
			}
		catch ( SQLException sqlex )
			{
			log.error( sqlex.getMessage(), sqlex );
			BuildingServer.codeTrace( sqlex.toString() );
			throw new BuildingServerException( sqlex.toString() );
			}

		try
			{
			if ( localTransaction )
				con.setAutoCommit( false );
				
			PreparedStatement prepst;
			
			if ( sqlclass.super_class!=null && sqlclass.super_class.table_name != null )
				{
				prepst = con.prepareStatement( sqlclass.super_class.update_by_pk_sql ); 

				p=0;
				for ( i=0; i< sqlclass.super_class.fields_in_order.size(); i++ )
					{
					sqlfield = (SqlField)sqlclass.super_class.fields_in_order.elementAt( i );
				
					if ( sqlfield == sqlclass.super_class.primary_key_field )
						continue;
				
					setUpdateParameter( prepst, p+1, sqlfield, obj );
					
					p++;
					}
					
				prepst.setInt( sqlclass.super_class.fields_in_order.size(), obj.getPrimaryKey().intValue() );
				
				rows = prepst.executeUpdate();
				prepst.close();
				if ( rows<1 )
					throw new BuildingServerException( "Attempt to save object failed: record doesn't already exist." );
				}
			
			if ( sqlclass.table_name !=null )
				{
				prepst = con.prepareStatement( sqlclass.update_by_pk_sql ); 

				p=0;
				for ( i=0; i< sqlclass.fields_in_order.size(); i++ )
					{
					sqlfield = (SqlField)sqlclass.fields_in_order.elementAt( i );
				
					if ( sqlfield == sqlclass.primary_key_field )
						continue;
				
					setUpdateParameter( prepst, p+1, sqlfield, obj );
					
					p++;
					}
					
				prepst.setInt( sqlclass.fields_in_order.size(), obj.getPrimaryKey().intValue() );
				
				rows = prepst.executeUpdate();
				prepst.close();
				if ( rows<1 )
					throw new BuildingServerException( "Attempt to save object failed: record doesn't already exist." );
				}
				
			if ( localTransaction )
				con.commit();

			obj.clearUnsaved();
			
			//put into cache again to update indices
			object_cache.put( obj.getPrimaryKey(), obj );
			}		
		catch ( Exception ex )
			{
			log.error( ex.getMessage(), ex );
			BuildingServer.codeTrace( ex.toString() );
			try
				{
				con.rollback();
				}
			catch ( SQLException sqlex )
				{
				log.error( sqlex.getMessage(), sqlex );
				BuildingServer.codeTrace( "Unable to rollback transaction:" );
				BuildingServer.codeTrace( sqlex.toString() );
				}
			throw new BuildingServerException( ex.toString() );
			}
		finally
			{
			if ( localTransaction )
				try
					{
					con.setAutoCommit( true );
					}
				catch ( SQLException sqlex )
					{
					log.error( sqlex.getMessage(), sqlex );
					BuildingServer.codeTrace( "Unable to switch off autocommit:" );
					BuildingServer.codeTrace( sqlex.toString() );
					}
			}
		
		}

	/**
	 * Inserts a newly instantiated SqlPersistentObject into the database
	 * and updates the primary key field of the object.
	 * 
	 * @param obj The object to insert.
	 * @exception org.bodington.server.BuildingServerException Throws an exception if the primary key of the object is not null, 
	 * which would indicate that the object already exists in the database.
	 */
	
	public void insertObject( PersistentObject obj )
		throws BuildingServerException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );

                SqlClass sqlclass;
		SqlField sqlfield;
		int rows, i;
		boolean localTransaction;
		Connection con;
		SQLWarning warning;
		
		if ( obj.getPrimaryKey()!=null )
			throw new BuildingServerException( "Can't insert as new object - already saved." );

		sqlclass = (SqlClass)types_by_java_class.get( obj.getClass().getName() );
		if ( sqlclass==null )
			throw new BuildingServerException( "Can't save object - unknown type." );
		
		try
			{
			BuildingContext context = BuildingContext.getContext();
			if ( context==null )
				throw new BuildingServerException( "Can't save object - no context." );
				
			con = context.getConnection();
			if ( con == null )
				throw new BuildingServerException( "Can't save object - no database connection." );
			
			// are we in a transaction already or do we need to set one up
			// for this little bit of work?
			localTransaction=con.getAutoCommit();
			}		
		catch ( ObjectPoolException opex )
			{
			throw new BuildingServerException( opex );
			}
		catch ( SQLException sqlex )
			{
			log.error( sqlex.getMessage(), sqlex );
			BuildingServer.codeTrace( sqlex.toString() );
			throw new BuildingServerException( sqlex.toString() );
			}
		
		PreparedStatement prepst = null;
		try
			{
			if ( localTransaction )
				con.setAutoCommit( false );
				
			obj.setPrimaryKey( nextPrimaryKey() );
			PreparedStatement prepst_a = con.prepareStatement( "INSERT INTO objects( id, type ) VALUES ( ? , ? )" );
			prepst_a.setInt( 1, obj.getPrimaryKey().intValue() );
			prepst_a.setInt( 2, sqlclass.type );
			rows = prepst_a.executeUpdate();
			prepst_a.close();
			if ( rows<1 )
				throw new BuildingServerException( "Attempt to insert object failed." );

			if ( sqlclass.super_class!=null && sqlclass.super_class.table_name!=null )
				{
				prepst = con.prepareStatement( sqlclass.super_class.insert_sql ); 
				for ( i=0; i< sqlclass.super_class.fields_in_order.size(); i++ )
					{
					sqlfield = (SqlField)sqlclass.super_class.fields_in_order.elementAt( i );
					
					setUpdateParameter( prepst, i+1, sqlfield, obj );
					}
				rows = prepst.executeUpdate();
				warning = prepst.getWarnings();
				while ( warning!=null )
					{
					log.warn( "SQL WARNING: " + warning.getMessage() );
					warning = warning.getNextWarning();
					}
				prepst.close();

				if ( rows<1 )
					throw new BuildingServerException( "Attempt to insert object failed." );

				}

			if ( sqlclass.table_name !=null )
				{
				prepst = con.prepareStatement( sqlclass.insert_sql ); 
				for ( i=0; i< sqlclass.fields_in_order.size(); i++ )
					{
					sqlfield = (SqlField)sqlclass.fields_in_order.elementAt( i );
				
					setUpdateParameter( prepst, i+1, sqlfield, obj );
					}
				rows = prepst.executeUpdate();
				warning = prepst.getWarnings();
				while ( warning!=null )
					{
					log.warn( "SQL WARNING: " + warning.getMessage() );
					warning = warning.getNextWarning();
					}
				prepst.close();

				if ( rows<1 )
					throw new BuildingServerException( "Attempt to insert object failed." );
				}
				
			if ( localTransaction )
				con.commit();
			
			obj.clearUnsaved();
			// now cache it...
			object_cache.put( obj.getPrimaryKey(), obj );
			}		
		catch ( Exception ex )
			{
			log.error( (prepst == null)?"":prepst.toString() + "\n"+ ex.getMessage(), ex );
			obj.setPrimaryKey( null );
			BuildingServer.codeTrace( ex.toString() );
			try
				{
				con.rollback();
				}
			catch ( SQLException sqlex )
				{
				log.error( sqlex.getMessage(), sqlex );
				BuildingServer.codeTrace( "Unable to rollback transaction:" );
				BuildingServer.codeTrace( sqlex.toString() );
				}
			throw new BuildingServerException( ex.toString() );
			}
		finally
			{
			if ( localTransaction )
				try
					{
					con.setAutoCommit( true );
					}
				catch ( SQLException sqlex )
					{
					log.error( sqlex.getMessage(), sqlex );
					BuildingServer.codeTrace( "Unable to switch off autocommit:" );
					BuildingServer.codeTrace( sqlex.toString() );
					}
			}
		
		}

	/**
	 * Deletes an instantiated SqlPersistentObject from the database.
	 * 
	 * @param obj The object to delete.
	 * @exception org.bodington.server.BuildingServerException Throws an exception if the primary key of the object is null, 
	 * which would indicate that the object already exists in the database.
	 */
	
	public void deleteObject( PersistentObject obj )
		throws BuildingServerException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );
                
		SqlClass sqlclass;
		SqlField sqlfield;
		int rows;
		boolean localTransaction;
		Connection con;
		
		if ( obj.getPrimaryKey()==null )
			throw new BuildingServerException( "Can't delete object - not a saved object." );

		sqlclass = (SqlClass)types_by_java_class.get( obj.getClass().getName() );
		if ( sqlclass==null )
			throw new BuildingServerException( "Can't delete object - unknown type." );
		
		try
			{
			BuildingContext context = BuildingContext.getContext();
			if ( context==null )
				throw new BuildingServerException( "Can't save object - no context." );
				
			con = context.getConnection();
			if ( con==null )
				throw new BuildingServerException( "Can't save object - no database connection." );
			
			// are we in a transaction already or do we need to set one up
			// for this little bit of work?
			localTransaction=con.getAutoCommit();
			}		
		catch ( ObjectPoolException opex )
			{
			throw new BuildingServerException( opex );
			}
		catch ( SQLException sqlex )
			{
			log.error( sqlex.getMessage(), sqlex );
			BuildingServer.codeTrace( sqlex.toString() );
			throw new BuildingServerException( sqlex.toString() );
			}

		try
			{
			if ( localTransaction )
				con.setAutoCommit( false );
			
			if ( sqlclass.table_name!=null )
				{
				PreparedStatement prepst = con.prepareStatement( sqlclass.delete_sql ); 
				setUpdateParameter( prepst, 1, sqlclass.primary_key_field, obj );
				rows = prepst.executeUpdate();
				prepst.close();
				if ( rows<1 )
					throw new BuildingServerException( "Attempt to delete object failed." );
				}
				
			if ( sqlclass.super_class!=null && sqlclass.super_class.table_name!=null )
				{
				PreparedStatement prepst_sup = con.prepareStatement( sqlclass.super_class.delete_sql ); 
				setUpdateParameter( prepst_sup, 1, sqlclass.super_class.primary_key_field, obj );
				rows = prepst_sup.executeUpdate();
				prepst_sup.close();
				if ( rows<1 )
					throw new BuildingServerException( "Attempt to delete object failed." );
				}

			PreparedStatement prepst_a = con.prepareStatement( "DELETE FROM objects WHERE id =  ?" );
			prepst_a.setInt( 1, obj.getPrimaryKey().intValue() );
			rows = prepst_a.executeUpdate();
			prepst_a.close();
			if ( rows<1 )
				throw new BuildingServerException( "Attempt to delete object failed." );

			if ( localTransaction )
				con.commit();
			
			obj.clearUnsaved();
			object_cache.remove( obj.getPrimaryKey() );
			}		
		catch ( Exception ex )
			{
			log.error( ex.getMessage(), ex );
			BuildingServer.codeTrace( ex.toString() );
			try
				{
				con.rollback();
				}
			catch ( SQLException sqlex )
				{
				log.error( sqlex.getMessage(), sqlex );
				BuildingServer.codeTrace( "Unable to rollback transaction:" );
				BuildingServer.codeTrace( sqlex.toString() );
				}
			throw new BuildingServerException( ex.toString() );
			}
		finally
			{
			if ( localTransaction )
				try
					{
					con.setAutoCommit( true );
					}
				catch ( SQLException sqlex )
					{
					log.error( sqlex.getMessage(), sqlex );
					BuildingServer.codeTrace( "Unable to switch off autocommit:" );
					BuildingServer.codeTrace( sqlex.toString() );
					}
			}
		
		}




	/**
	 * Sets a parameter in a prepared statement for a particular field.
	 * 
	 * @param prepst  The prepared statement to complete.
	 * @param pos The parameter number to fill in.
	 * @param sqlfield The field decriptor for the object.
	 * @param obj The object to fetch the value from.
	 * @exception java.sql.SQLException Thrown if there are SQL errors to report.
	 */
	
	
	
	private void setUpdateParameter( PreparedStatement prepst, int pos, SqlField sqlfield, PersistentObject obj )
		throws SQLException, InvocationTargetException, IllegalAccessException, BuildingServerException
		{
		BigDecimal big, over;
		BigInteger over_int;		
		
		if ( sqlfield.get_java_class.equals( "org.bodington.database.PrimaryKey" ) )
			{
			if ( sqlfield.sql_type != Types.INTEGER )
				throw new BuildingServerException( "Unable to store object - can only put primary key into an INTEGER database field." );
				
			PrimaryKey ret_key = (PrimaryKey)sqlfield.get_method.invoke( obj, null );
			if ( ret_key==null )
				// throw new BuildingServerException( "Unable to store object - can't have null primary key." );
				// actually if this is a foreign ref that allows nulls, we should allow nulls to be set.
				prepst.setNull( pos, sqlfield.sql_type );
		    else
			    prepst.setInt( pos, ret_key.intValue() );
			return;
			}
		
		if ( sqlfield.get_java_class.equals( "java.lang.String" ) )
			{
			String ret_string = (String)sqlfield.get_method.invoke( obj, null );
			if ( ret_string==null )
				prepst.setNull( pos, sqlfield.sql_type );
			else
				prepst.setString( pos, ret_string );
			return;
			}

		if ( sqlfield.get_java_class.equals( "java.sql.Timestamp" ) )
			{
			java.sql.Timestamp ret_timestamp = (java.sql.Timestamp)sqlfield.get_method.invoke( obj, null );
			if ( ret_timestamp==null )
				prepst.setNull( pos, sqlfield.sql_type );
			else
				prepst.setTimestamp( pos, ret_timestamp );
			return;
			}
		
		if ( sqlfield.get_java_class.equals( "java.sql.BigInteger" ) )
			{
		    if ( sqlfield.sql_type!=Types.NUMERIC && sqlfield.sql_type!=Types.DECIMAL )
				throw new BuildingServerException( "Can only store BigInteger in NUMERIC/DECIMAL field." );
				
			java.math.BigInteger ret_bigint = (java.math.BigInteger)sqlfield.get_method.invoke( obj, null );
			if ( ret_bigint==null )
				prepst.setNull( pos, sqlfield.sql_type );
			else
				{
				over_int = overflow_big_integer[sqlfield.sql_precision - sqlfield.sql_scale];
				if ( ret_bigint.abs().compareTo( over_int ) >= 0 )
					throw new BuildingServerException( "Can't store number in the database because it is too big." );
				prepst.setBigDecimal( pos, new BigDecimal( ret_bigint ) );
				}
			return;
			}

		if ( sqlfield.get_java_class.equals( "java.sql.BigDecimal" ) )
			{
		    if ( sqlfield.sql_type!=Types.NUMERIC && sqlfield.sql_type!=Types.DECIMAL )
				throw new BuildingServerException( "Can only store BigDecimal in NUMERIC/DECIMAL field." );
				
			java.math.BigDecimal ret_bigdec = (java.math.BigDecimal)sqlfield.get_method.invoke( obj, null );
			if ( ret_bigdec==null )
				prepst.setNull( pos, sqlfield.sql_type );
			else
				{
				over = overflow_big_decimal[sqlfield.sql_precision - sqlfield.sql_scale];
				
				try
					{
					// try to decrease scale to database scale if database scale is known
					if ( ret_bigdec.scale() > sqlfield.sql_scale && sqlfield.sql_scale !=0 && sqlfield.sql_precision != 0 )
						ret_bigdec = ret_bigdec.setScale( sqlfield.sql_scale );
					}
				catch ( ArithmeticException aex )
					{
					throw new BuildingServerException( 
						"Can't store number in the database because precision will be lost. Number = " +
						ret_bigdec.toString() + " db scale = " + sqlfield.sql_scale );
					}
					
				if ( ret_bigdec.abs().compareTo( over ) >= 0 )
					throw new BuildingServerException( "Can't store number in the database because it is too big." );
				prepst.setBigDecimal( pos, ret_bigdec );
				}
			return;
			}

		//got this far - must be sub class of java.lang.Number or unsupported java type.

		Object value = sqlfield.get_method.invoke( obj, null );
		if ( value==null )
			{
			prepst.setNull( pos, sqlfield.sql_type );
			return;
			}

		if ( !(value instanceof Number) )
			throw new BuildingServerException( "Unable to store object - unsupported java data type." );

		Number ret_number=(Number)value;
		boolean is_integer=true;
		if ( (value instanceof Double ) )
			{
			is_integer=false;
			Double d = (Double)value;
			if ( d.isInfinite() )
				throw new BuildingServerException( "Can't store infinity in the database." );
			if ( d.isNaN() )
				throw new BuildingServerException( "Can't store 'not a number' in the database." );
			}
		if ( (value instanceof Float ) )
			{
			is_integer=false;
			Float d = (Float)value;
			if ( d.isInfinite() )
				throw new BuildingServerException( "Can't store infinity in the database." );
			if ( d.isNaN() )
				throw new BuildingServerException( "Can't store 'not a number' in the database." );
			}

		
		switch ( sqlfield.sql_type )
			{
			case Types.NUMERIC:
			case Types.DECIMAL:
				if ( is_integer )
					big = new BigDecimal( ret_number.toString() );
				else
					big = new BigDecimal( ret_number.doubleValue() );
				
				if ( big.scale() > sqlfield.sql_scale && sqlfield.sql_scale !=0 && sqlfield.sql_precision != 0 )
					throw new BuildingServerException( 
						"Can't store number in the database because precision will be lost. Number = " +
						big.toString() + " Scale = " +
						big.scale() + " db scale = " + sqlfield.sql_scale );
				
				over = overflow_big_decimal[sqlfield.sql_precision - sqlfield.sql_scale];
				if ( big.abs().compareTo( over ) >= 0 )
					throw new BuildingServerException( "Can't store number in the database because it is too big." );
				
				prepst.setBigDecimal( pos, big );
				return;
				
			case Types.TINYINT:
			    if ( !(ret_number instanceof Byte) )
					throw new BuildingServerException( "Can only store byte in TINYINT field." );
                if ( this.jdbc_tinyint_glitch )
                {
                    // need to convert signed byte to unsigned int
                    int n = ((int)ret_number.byteValue()) & 0xff;
                    prepst.setInt( pos, n );
                }
                else
                    prepst.setByte( pos, ret_number.byteValue() );
				return;
				
			case Types.SMALLINT:
			    if ( !(ret_number instanceof Short) && !(ret_number instanceof Byte) )
					throw new BuildingServerException( "Can only store short or byte in SMALLINT field." );
				prepst.setShort( pos, ret_number.shortValue() );
				return;

			case Types.INTEGER:
			    if ( !(ret_number instanceof Integer) && !(ret_number instanceof Short) && !(ret_number instanceof Byte) )
					throw new BuildingServerException( "Can only store byte, short or int in INTEGER field." );
				prepst.setInt( pos, ret_number.intValue() );
				return;

			case Types.BIGINT:
			    if ( !(ret_number instanceof Long) && !(ret_number instanceof Integer) && !(ret_number instanceof Short) && !(ret_number instanceof Byte) )
					throw new BuildingServerException( "Can only store byte, short, int or long in BIGINT field." );
				prepst.setLong( pos, ret_number.longValue() );
				return;
				
			case Types.FLOAT:
			    if ( !(ret_number instanceof Double) )
					throw new BuildingServerException( "Can only store double in FLOAT field." );
				prepst.setDouble( pos, ret_number.doubleValue() );
				return;

			case Types.REAL:
			    if ( !(ret_number instanceof Float) )
					throw new BuildingServerException( "Can only store float in REAL field." );
				prepst.setFloat( pos, ret_number.floatValue() );
				return;
			}

		throw new BuildingServerException( "Unsupported number conversion." );
		}


	private void fetchReferencedObjects( PersistentObject return_object, SqlClass sqlclass )
		throws SQLException, InvocationTargetException, IllegalAccessException, BuildingServerException
		{
		SqlClass foreignsqlclass;
		SqlField sqlfield;
		PrimaryKey foreign_key;
		String foreign_class_name;
		//Object foreign_object;
		//Object[] p = new Object[1];
				
		//BuildingServer.codeTrace( "Recursively loading referenced objects" );
		for ( int i=0; i< sqlclass.fields_in_order.size(); i++ )
			{
			sqlfield = (SqlField)sqlclass.fields_in_order.elementAt( i );
			// Don't find foreign object if this field isn't a foreign key
			if ( (sqlfield.flags & SqlField.FK)==0 )
				continue;
					
			// Don't find foreign object if there aren't appropriate methods
			//if ( sqlfield.get_method_foreign == null || sqlfield.set_method_foreign == null || sqlfield.get_method == null )
			//	continue;
				
			// Don't find foreign object if there isn't a key value for one.
			foreign_key = (PrimaryKey)sqlfield.get_method.invoke( return_object, null );
			if ( foreign_key==null )
				continue;
					
			// Don't find foreign object if it is already set.
			//foreign_object = sqlfield.get_method_foreign.invoke( return_object, null );
			//if ( foreign_object != null )
			//	continue;
					
			foreignsqlclass=null;
			if ( sqlfield.foreign_type>0 )
			foreignsqlclass = (SqlClass)types_by_type.get( new Integer(sqlfield.foreign_type) );
					
			if ( foreignsqlclass==null )
				foreign_class_name="org.bodington.sqldatabase.SqlPersistentObject";
			else
				foreign_class_name=foreignsqlclass.java_class;

			//find the object so it is cached and then forget the reference.
			findPersistentObject( foreign_key, foreign_class_name );
			//foreign_object=findPersistentObject( foreign_key, foreign_class_name );
					
			//if ( foreign_object==null )
			//	continue;
			//p[0]=foreign_object;
			//sqlfield.set_method_foreign.invoke( return_object, p );
			}

		//  then let object do its own loading stuff...
		return_object.loadReferencedObjects();
		}



	private PersistentObject fetchObjectFromResultSet( ResultSet results, SqlClass expected_sqlclass )
		throws SQLException, IllegalAccessException, BuildingServerException
		{
		int type;
		SqlClass sqlclass;
		SqlField sqlfield;
		PersistentObject return_object;
		
		type = results.getInt( "type" );
		if ( results.wasNull() )
			return null;
					
		sqlclass = (SqlClass)types_by_type.get( new Integer( type ) );
		if ( sqlclass==null )
			return null;

		if ( expected_sqlclass!=null && expected_sqlclass!=sqlclass )
			return null;
					
		try
			{
			return_object = (PersistentObject)sqlclass.getDataClass().newInstance();
			}
		catch ( Exception ex )
			{
			BuildingServer.codeTrace( ex.toString() );
			log.error( ex.getMessage(), ex );
			return null;
			}
		
		if ( sqlclass.super_class!=null )
			{
			for ( int j=0; j< sqlclass.super_class.fields_in_order.size(); j++ )
				{
				sqlfield = (SqlField)sqlclass.super_class.fields_in_order.elementAt( j );
				try
					{
					setPropertyFromResults( results, sqlfield, return_object );
					}
				catch ( InvocationTargetException itex )
					{
					Throwable ex = itex.getTargetException();
					BuildingContext.trace( "Exception getting object fields from database." );
					if ( ex !=null )
					    log.error( ex.getMessage(), ex );
					throw new BuildingServerException( "Exception getting object fields from database." );
					}
				}
			}
			
		for ( int i=0; i< sqlclass.fields_in_order.size(); i++ )
			{
			sqlfield = (SqlField)sqlclass.fields_in_order.elementAt( i );
			try
				{
				setPropertyFromResults( results, sqlfield, return_object );
				}
			catch ( InvocationTargetException itex )
				{
				Throwable ex = itex.getTargetException();
				BuildingContext.trace( "Exception getting object fields from database" );
				if ( ex !=null )
				    log.error( ex.getMessage(), ex );
				throw new BuildingServerException( "Exception getting object fields from database." );
				}
			}
		
		return return_object;
		}

	/**
	 * Gets a field value from a ResultsSet and sets it in an object.
	 * 
	 * @param results The ResultsSet containing values.
	 * @param sqlfield The field descriptor.
	 * @param obj The object to set.
	 * @exception java.sql.SQLException Thrown if there is a database error.
	 */
	
	private void setPropertyFromResults( ResultSet results, SqlField sqlfield, PersistentObject obj )
		throws SQLException, InvocationTargetException, IllegalAccessException, BuildingServerException
		{
		Object first=null, second;
		Object[] params = new Object[1];
		boolean found=false;

		//start by fetching data from the record
		switch ( sqlfield.sql_type )
			{
			case Types.INTEGER:
				first = new Integer( results.getInt( sqlfield.field ) );
				break;

			case Types.SMALLINT:
				first = new Short( results.getShort( sqlfield.field ) );
				break;
			
			case Types.TINYINT:
				first = new Byte( results.getByte( sqlfield.field ) );
				break;
				
			case Types.CHAR:
			case Types.VARCHAR:
		    case Types.LONGVARCHAR:
				first = results.getString( sqlfield.field );
				break;

			case Types.TIMESTAMP:
				first = results.getTimestamp( sqlfield.field );
				break;
				
			case Types.DECIMAL:
			case Types.NUMERIC:
				first = results.getBigDecimal( sqlfield.field, sqlfield.sql_scale );
				break;
			
			default:
				throw new BuildingServerException( "Unsupported SQL data type." );
			}
		
		if ( results.wasNull() )
			first=null;
		

		if ( sqlfield.set_java_class.equals( "org.bodington.database.PrimaryKey" ) )
			{
			if ( sqlfield.sql_type != Types.INTEGER )
				throw new BuildingServerException( "Unsupported data conversion." );
			
			if ( first==null )
			// Some foreign keys can be null....
			//	throw new BuildingServerException( "Can't have NULL primary key in database." );
			    params[0]=null;
			else
    			params[0] = new PrimaryKey( ((Number)first).intValue() );
			sqlfield.set_method.invoke( obj, params );
			return;
			}

        // allow narrowing data conversion 
		if ( sqlfield.set_java_class.equals( "byte" ) || 
		     sqlfield.set_java_class.equals( "java.lang.Byte" ) )
			{
			if ( sqlfield.sql_type != Types.TINYINT && 
			     sqlfield.sql_type != Types.SMALLINT && 
			     sqlfield.sql_type != Types.INTEGER 	)
				throw new BuildingServerException( "Unsupported data conversion." );
			
			if ( first==null )
				{
				if ( sqlfield.set_java_class.equals( "byte" ) )
					params[0] = new Byte( (byte)0 );
				else
					params[0] = null;
				}
			else
				params[0] = new Byte( ((Number)first).byteValue() );
			sqlfield.set_method.invoke( obj, params );
			return;
			}

		if ( sqlfield.set_java_class.equals( "int" ) || 
		     sqlfield.set_java_class.equals( "java.lang.Integer" ) )
			{
			if ( sqlfield.sql_type != Types.TINYINT &&
			     sqlfield.sql_type != Types.SMALLINT &&
			     sqlfield.sql_type != Types.INTEGER )
				throw new BuildingServerException( "Unsupported data conversion." );
			
			if ( first==null )
				{
				if ( sqlfield.set_java_class.equals( "int" ) )
					params[0] = new Integer( 0 );
				else
					params[0] = null;
				}
			else
				params[0] = new Integer( ((Number)first).intValue() );
			sqlfield.set_method.invoke( obj, params );
			return;
			}

		if ( sqlfield.set_java_class.equals( "long" ) ||
		     sqlfield.set_java_class.equals( "java.lang.Long" ) )
			{
			if ( sqlfield.sql_type != Types.TINYINT &&
			     sqlfield.sql_type != Types.SMALLINT &&
			     sqlfield.sql_type != Types.INTEGER )
				throw new BuildingServerException( "Unsupported data conversion." );
			
			if ( first==null )
				{
				if ( sqlfield.set_java_class.equals( "long" ) )
					params[0] = new Long( 0 );
				else
					params[0] = null;
				}
			else
				params[0] = new Long( ((Number)first).longValue() );
			sqlfield.set_method.invoke( obj, params );
			return;
			}

		if ( sqlfield.set_java_class.equals( "double" ) ||
		     sqlfield.set_java_class.equals( "java.lang.Double" ) )
			{
			if ( sqlfield.sql_type != Types.DECIMAL &&
			     sqlfield.sql_type != Types.NUMERIC    )
				throw new BuildingServerException( "Unsupported data conversion." );
			
			BigDecimal bigdec = (BigDecimal)first;

			if ( first==null )
				{
				if ( sqlfield.set_java_class.equals( "double" ) )
					params[0] = new Double( 0 );
				else
					params[0] = null;
				}
			else
				{
				double d = bigdec.doubleValue();
				if ( Double.isInfinite( d ) || Double.isNaN( d ) )
					throw new BuildingServerException( "Data out of range - database has a number too big for a double." );
				params[0] = new Double( d );
				}
			sqlfield.set_method.invoke( obj, params );
			return;
			}

		if ( sqlfield.set_java_class.equals( "float" ) ||
		     sqlfield.set_java_class.equals( "java.lang.Float" ) )
			{
			if ( sqlfield.sql_type != Types.DECIMAL &&
			     sqlfield.sql_type != Types.NUMERIC    )
				throw new BuildingServerException( "Unsupported data conversion." );
			
			BigDecimal bigdec = (BigDecimal)first;

			if ( first==null )
				{
				if ( sqlfield.set_java_class.equals( "float" ) )
					params[0] = new Float( 0 );
				else
					params[0] = null;
				}
			else
				{
				float d = bigdec.floatValue();
				if ( Float.isInfinite( d ) || Float.isNaN( d ) )
					throw new BuildingServerException( "Data out of range - database has a number too big for a float." );
				params[0] = new Float( d );
				}
			sqlfield.set_method.invoke( obj, params );
			return;
			}

		if ( sqlfield.set_java_class.equals( "java.math.BigInteger" ) )
			{
			if ( sqlfield.sql_type != Types.DECIMAL &&
			     sqlfield.sql_type != Types.NUMERIC    )
				throw new BuildingServerException( "Unsupported data conversion." );
			
			BigDecimal bigdec = (BigDecimal)first;

			if ( first==null )
				params[0] = null;
			else
				{
				if ( bigdec.scale() >0 )
					throw new BuildingServerException( "Unsupported data conversion (loss of precision)." );
				params[0] = bigdec.toBigInteger();
				}
			sqlfield.set_method.invoke( obj, params );
			return;
			}

		if ( sqlfield.set_java_class.equals( "java.math.BigDecimal" ) )
			{
			if ( sqlfield.sql_type != Types.DECIMAL &&
			     sqlfield.sql_type != Types.NUMERIC    )
				throw new BuildingServerException( "Unsupported data conversion." );
			
			params[0] = first;
			sqlfield.set_method.invoke( obj, params );
			return;
			}

		if ( sqlfield.set_java_class.equals( "java.lang.String" ) )
			{
			params[0] = first;
			sqlfield.set_method.invoke( obj, params );
			return;
			}

		if ( sqlfield.set_java_class.equals( "java.sql.Timestamp" ) )
			{
			params[0] = first;
			sqlfield.set_method.invoke( obj, params );
			return;
			}

		throw new BuildingServerException( "Unable to load object - unsupported java data type." );
		}


	public static String quotedSQL( String sql )
		{
        if (sql == null)
            return "''";
		StringBuffer buffer = new StringBuffer( sql.length()+10 );
                if ( jdbc_unicode_string_literals )
                    buffer.append( 'N' );
		buffer.append( '\'' );
		char c;
		for ( int i=0; i<sql.length(); i++ )
			{
			c = sql.charAt( i );
			buffer.append( c );
			if ( c =='\'' )
				buffer.append( c );
			}
		buffer.append( '\'' );
		return buffer.toString();
		}

	public void initContext(BuildingContext context)
		throws ObjectPoolException
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );
                
		try
			{
			if ( context.peekConnection() != null)
				{
				connection_pool.freeConnection( context.peekConnection() );
				}
				
			Object owner = context.getPoolOwner();
			context.setConnection( connection_pool.getConnection( owner ) );
			}
		catch ( ConnectionPoolException cpe )
		{
		    log.error( cpe.getMessage(), cpe );
		    if ( cpe.getCause() != null )
		    {
		        log.error( "Underlying cause : " + cpe.getCause().getMessage(), cpe.getCause() );
		    }
		    throw new ObjectPoolException(-1, cpe, cpe.getMessage());
		}
		}

	public void freeContext(BuildingContext context)
		{
                if ( destroyed )
                    throw new IllegalStateException( "SqlDatabase is destroyed" );
                
		if ( context == null )
			return;
		try
			{
			if ( context.peekConnection() != null)
				{
				connection_pool.freeConnection( context.peekConnection() );
				}
			}
		catch ( ConnectionPoolException opex )
		{
		    log.error( opex.getMessage(), opex );
		    if ( opex.getCause() != null )
		    {
		        log.error( "Underlying cause : " + opex.getCause().getMessage(), opex.getCause() );
		    }
		}
		context.setConnection( null );
		}
	}
