SqlManager.java

/* ************************************************************************
#
#  DivConq
#
#  http://divconq.com/
#
#  Copyright:
#    Copyright 2014 eTimeline, LLC. All rights reserved.
#
#  License:
#    See the license.txt file in the project's top-level directory for details.
#
#  Authors:
#    * Andy White
#
************************************************************************ */
package divconq.sql;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ConcurrentLinkedQueue;
import java.util.concurrent.Semaphore;
import java.util.concurrent.TimeUnit;

import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import divconq.hub.Hub;
import divconq.lang.op.FuncResult;
import divconq.lang.op.OperationContext;
import divconq.lang.op.OperationResult;
import divconq.struct.ListStruct;
import divconq.struct.RecordStruct;
import divconq.struct.Struct;
import divconq.struct.scalar.NullStruct;
import divconq.util.StringUtil;
import divconq.xml.XElement;

// TODO there is much much more to do to make this easier to support many different db engines
public class SqlManager {
	public final DateTimeFormatter stampFmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.SSS");

	protected Map<String, SqlDatabase> databases = new HashMap<String, SqlManager.SqlDatabase>();
	
	public String getNowAsString() {
		return this.stampFmt.print(new DateTime(DateTimeZone.UTC));
	}
	
	public String getDateAsString(DateTime dt) {
		return this.stampFmt.print(dt.toDateTime(DateTimeZone.UTC));
	}
	
	public String getDateAsString(long dt) {
		return this.stampFmt.print(new DateTime(dt, DateTimeZone.UTC));
	}
	
	public SqlDatabase getDatabase(String name) {
		return this.databases.get(name);
	}
	
	public void init(OperationResult or, XElement config) {
		if (config == null)
			return;
		
		for (XElement del : config.selectAll("Database")) {
			String name = del.getAttribute("Name", "default");
			
			SqlDatabase db = new SqlDatabase();
			db.name = name;
			db.init(or, del);
			
			this.databases.put(name, db);
		}
	}
	
	public void stop() {
		for (SqlDatabase db : this.databases.values())
			db.stop();
	}
	
	public class SqlDatabase {
		protected String connstring = null;
		protected String name = null;
		
		protected SqlEngine engine = null;
		
		// single connection style engines
		protected Connection conn = null;	
		protected Semaphore lock = new Semaphore(0);
		
		protected boolean poolmode = false;
		protected ConcurrentLinkedQueue<Connection> pool = new ConcurrentLinkedQueue<>();
	
		public void init(OperationResult or, XElement del) {
			if (del == null) 
				return;
	
			try {
				String driver = del.getAttribute("Driver");
				
		        Class.forName(driver);
	    		
		        this.connstring = Hub.instance.getClock().getObfuscator().decryptHexToString(
	    				del.getAttribute("Connection")
	    		);
		        
		        // if null then try unencrypted
		        if (this.connstring == null)
		        	this.connstring = del.getAttribute("Connection");
		        
		        if (this.connstring.startsWith("jdbc:h2:"))
		        	this.engine = SqlEngine.H2;
		        else if (this.connstring.startsWith("jdbc:sqlserver:"))
		        	this.engine = SqlEngine.SqlServer;
		        else if (this.connstring.startsWith("jdbc:mariadb:"))
		        	this.engine = SqlEngine.MariaDb;
		        else if (this.connstring.startsWith("jdbc:mysql:"))
		        	this.engine = SqlEngine.MySQL;
		        else {
					or.errorTr(189, this.connstring.substring(0, Math.min(this.connstring.length(), 15)));
					return;
		        }
		        
		        this.poolmode = "Pooled".equals(del.getAttribute("Mode"));
		        
		        if (this.engine == SqlEngine.SqlServer || this.engine == SqlEngine.MariaDb || this.engine == SqlEngine.MySQL) {
			        or.info(0, "Using database " + this.name + " with multiple connections.");
		        }
		        else {
			        this.conn = DriverManager.getConnection(this.connstring);
			        
			        or.info(0, "Connected to database " + this.name + " single connection.");
				
			        this.releaseConnection(this.conn);
		        }
			} 
			catch (Exception x) {
				or.errorTr(190, this.name, x);
				return;
			}
		}
		
		public void stop() {
			try {
				if (this.engine == SqlEngine.SqlServer || this.engine == SqlEngine.MariaDb || this.engine == SqlEngine.MySQL) {
					if (this.poolmode) {
						Connection conn = this.pool.poll();
						
						while (conn != null) {
							try {
								conn.close();
							} 
							catch (Exception x) {
								// unimportant
							}
							
							conn = this.pool.poll();
						}
					}
				}
				else if (this.engine == SqlEngine.H2) 
					this.conn.close();
			} 
			catch (Exception x) {
				// unimportant
			}
		}
		
		public Connection acquireConnection() {
			Hub.instance.getCountManager().allocateNumberCounter("dcSqlAcquireConnection").increment();
			
			if (this.engine == SqlEngine.SqlServer  || this.engine == SqlEngine.MariaDb  || this.engine == SqlEngine.MySQL) {
		        try {
					Connection conn = null;

					if (this.poolmode) {
						conn = this.pool.poll();
						
						if ((conn != null) && !conn.isValid(2)) 
							conn = null;
					}
					
					if (conn == null) {
						conn = DriverManager.getConnection(this.connstring);
						Hub.instance.getCountManager().allocateNumberCounter("dcSqlConnectionCreate").increment();
					}
					
					return conn;
				} 
		        catch (SQLException x) {
				}
	        }
	        else if (this.engine == SqlEngine.H2) {
				try {
					this.lock.acquire();
					return this.conn;
				} 
				catch (InterruptedException e) {
				}
	        }
			
			Hub.instance.getCountManager().allocateNumberCounter("dcSqlAcquireConnectionFail").increment();
			
			return null;
		}
		
		public void releaseConnection(Connection conn) {
			Hub.instance.getCountManager().allocateNumberCounter("dcSqlReleaseConnection").increment();
			
			if (this.engine == SqlEngine.SqlServer || this.engine == SqlEngine.MariaDb || this.engine == SqlEngine.MySQL) {
				if (this.poolmode) {
					this.pool.add(conn);
					return;
				}
				
		        try {
					conn.close();
				} 
		        catch (SQLException x) {
				}
	        }
	        else if (this.engine == SqlEngine.H2) {
	        	this.lock.release();
	        }
		}

		public SqlEngine getEngine() {
			return this.engine;
		}
		
		public boolean testConnection() {
			Connection conn = this.acquireConnection();
			
			if (conn == null) 
				return false;
		    
	    	this.releaseConnection(conn);
	    	
		    return true;
		}
		
		// warning - may not use same connection between calls
		public String getLastIdSql() {
			if (this.engine == SqlEngine.H2)
				return "SELECT IDENTITY() AS lid";
			
			if (this.engine == SqlEngine.SqlServer)
				return "SELECT @@IDENTITY AS lid";
			
			if (this.engine == SqlEngine.MariaDb || this.engine == SqlEngine.MySQL)
				return "SELECT LAST_INSERT_ID() AS lid";
			
			return null;
		}
		
		/*
		 * We should always talk in UTC...
		 *  
		 * @return
		 */
		public String nowFunc() {
			if (this.getEngine() == SqlEngine.SqlServer)
				return "GETUTCDATE()";
			
			if (this.getEngine() == SqlEngine.MariaDb || this.getEngine() == SqlEngine.MySQL)
				return "UTC_TIMESTAMP()";

			return "NOW()";
		}
		
		// TODO
		// only support MINUTES at present
		public String timeUnit(TimeUnit unit) {
			if (unit == TimeUnit.MINUTES)
				return "MINUTE";
			
			return null;
		}
		
		public String modNowFunc(TimeUnit unit, int amt) {
			return this.modTimeFunc(this.nowFunc(), unit, amt);
		}
		
		public String modTimeFunc(String time, TimeUnit unit, int amt) {
			String unitname = this.timeUnit(unit);
			
			// h2 syntax
			String expr = "DATEADD('" + unitname + "', " + amt + ", " + time + ") ";
			
			if (this.getEngine() == SqlEngine.SqlServer)
				expr = "DATEADD(" + unitname + ", " + amt + ", " + time + ") ";
			
			if (this.getEngine() == SqlEngine.MariaDb || this.getEngine() == SqlEngine.MySQL)
				expr = "DATE_ADD(" + time + ", INTERVAL " + amt + " " + unitname + ") ";
			
			return expr;
		}
		
		public String formatColumn(String name) {
			if (this.getEngine() == SqlEngine.SqlServer)
				return "[" + name + "]";
			
			if (this.getEngine() == SqlEngine.MariaDb || this.getEngine() == SqlEngine.MySQL)
				return "`" + name + "`";

			// TODO check what H2 uses...
			
			return name;
		}
		
		public void processException(Exception x, OperationResult or) {
			if (x instanceof SQLException) {
				SQLException sx = (SQLException)x;
				
				if (this.getEngine() == SqlEngine.MariaDb || this.getEngine() == SqlEngine.MySQL) {
					// duplicate id error - this is not always an error for the caller
					// we treat it as an error return code but log it as an Info
					if (sx.getErrorCode() == 1062) {
						or.exit(194, OperationContext.get().tr("_code_194", this.name, x));
						return;
					}
				}
				else if (this.getEngine() == SqlEngine.SqlServer) {
					if (sx.getErrorCode() == 2627) {
						or.exit(194, OperationContext.get().tr("_code_194", this.name, x));
						return;
					}
				}
				
				// TODO add other databases
				
				or.errorTr(195, this.name, ((SQLException) x).getErrorCode(), x);	    	
				return;
			}
			
			or.errorTr(186, this.name, x);	    	
		}
		
		public FuncResult<Integer> executeFreestyle(String sql, Object... params) {
			FuncResult<Integer> res = new FuncResult<Integer>();
			
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
		    
		    try {					
			    FuncResult<PreparedStatement> psres = this.prepStatement(conn, sql, params);
			    
			    if (res.hasErrors())
			    	return res;
			    
			    PreparedStatement pstmt = psres.getResult();
		    	
				Hub.instance.getCountManager().countObjects("dcSqlExecuteCount", pstmt);
				
				res.setResult(pstmt.executeUpdate());
				
    			pstmt.close();
		    } 
		    catch (Exception x) {
		    	this.processException(x, res);
		    	
				Hub.instance.getCountManager().countObjects("dcSqlExecuteFail", sql);
		    } 
		    finally {
		    	this.releaseConnection(conn);
		    }
		    
			return res;
		}
		
		// return a list of records where each row is a record in this collection
		// -- NOTE: column names are all lower case
		public FuncResult<ListStruct> executeQueryFreestyle(String sql, Object... params) {
			FuncResult<ListStruct> res = new FuncResult<ListStruct>();
			ListStruct list = new ListStruct();
			
			res.setResult(list);
			
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
		    ResultSet rs = null;
		    
		    try {					
			    FuncResult<PreparedStatement> psres = this.prepStatement(conn, sql, params);
			    
			    if (res.hasErrors())
			    	return res;
			    
			    PreparedStatement pstmt = psres.getResult();
		    	
				Hub.instance.getCountManager().countObjects("dcSqlQueryCount", pstmt);
		    	
				// MariaDB hint that this turns on streaming... review TODO
				//pstmt.setFetchSize(Integer.MIN_VALUE);
				
				rs = pstmt.executeQuery();
				
				ResultSetMetaData md = rs.getMetaData();
			    int columns = md.getColumnCount();
			    
				while (rs.next()) {
					RecordStruct rec = new RecordStruct();
					
					for(int i=1; i<=columns; i++) 
						rec.setField(md.getColumnLabel(i).toLowerCase(), rs.getObject(i));
					
					list.addItem(rec);
				}				
				
    			pstmt.close();
		    } 
		    catch (Exception x) {
		    	this.processException(x, res);
		    	
				Hub.instance.getCountManager().countObjects("dcSqlQueryFail", sql);
		    } 
		    finally {
		    	try {
		    		if (rs != null)
		    			rs.close();
				} 
		    	catch (SQLException x) {
				}
		    	
		    	this.releaseConnection(conn);
		    }
		    
			return res;
		}
		
		// return a list of records where each row is a record in this collection
		public FuncResult<ListStruct> executeQueryPage(SqlSelect[] select, String from, String where, String groupby, String orderby, int offset, int pagesize, Object... params) {
			FuncResult<ListStruct> res = new FuncResult<ListStruct>();
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			// prepare
		    FuncResult<PreparedStatement> psres = this.prepPage(conn, select, from, where, groupby, orderby, offset, pagesize, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    // execute
	    	res = callAndFormat(select, pstmt);

	    	try {
	    		if (pstmt != null)
	    			pstmt.close();
			} 
	    	catch (SQLException x) {
			}
	    	
	    	// release
	    	this.releaseConnection(conn);
		    
			return res;
		}
		
		// return a list of records where each row is a record in this collection
		public FuncResult<ListStruct> executeQueryLimit(SqlSelect[] select, String from, String where, String groupby, String orderby, int limit, boolean distinct, Object... params) {
			FuncResult<ListStruct> res = new FuncResult<ListStruct>();
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			// prepare
		    FuncResult<PreparedStatement> psres = this.prepLimit(conn, select, from, where, groupby, orderby, limit, distinct, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    // execute
	    	res = callAndFormat(select, pstmt);

	    	try {
	    		if (pstmt != null)
	    			pstmt.close();
			} 
	    	catch (SQLException x) {
			}
	    	
	    	// release
	    	this.releaseConnection(conn);
		    
			return res;
		}
		
		// return a list of records where each row is a record in this collection
		public FuncResult<ListStruct> executeQuery(SqlSelect[] select, String from, String where, String groupby, String orderby, Object... params) {
			FuncResult<ListStruct> res = new FuncResult<ListStruct>();
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			// prepare
		    FuncResult<PreparedStatement> psres = this.prep(conn, select, from, where, groupby, orderby, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    // execute
	    	res = this.callAndFormat(select, pstmt);

	    	try {
	    		if (pstmt != null)
	    			pstmt.close();
			} 
	    	catch (SQLException x) {
			}
	    	
	    	// release
	    	this.releaseConnection(conn);
		    
			return res;
		}
		
		// return a single value (row/column) from table 
		public FuncResult<Struct> executeQueryScalar(SqlSelect select, String from, String where, String orderby, Object... params) {
			FuncResult<Struct> res = new FuncResult<Struct>();
    		res.setResult(NullStruct.instance);
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			SqlSelect[] selects = new SqlSelect[] { select };
			
			// prepare
		    FuncResult<PreparedStatement> psres = this.prep(conn, selects, from, where, null, orderby, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    // execute
	    	FuncResult<ListStruct> res2 = this.callAndFormat(selects, pstmt);
	    	
	    	ListStruct lrs = res2.getResult();
	    	
	    	if (lrs.getSize() > 0) {
	    		RecordStruct rec = lrs.getItemAsRecord(0);
	    		
	    		res.setResult(rec.getField(select.name));
	    	}

	    	try {
	    		if (pstmt != null)
	    			pstmt.close();
			} 
	    	catch (SQLException x) {
			}
	    	
	    	// release
	    	this.releaseConnection(conn);
		    
			return res;
		}
		
		// return a single String value (row/column) from table 
		public String executeQueryString(String col, String from, String where, String orderby, Object... params) {
			FuncResult<Struct> rsres = this.executeQueryScalar(new SqlSelectString(col), from, where, orderby, params);
			
			if (rsres.hasErrors() || rsres.isEmptyResult()) 
				return null;
			
			return Struct.objectToString(rsres.getResult());
		}
		
		// return a single Integer value (row/column) from table 
		public Long executeQueryInteger(String col, String from, String where, String orderby, Object... params) {
			FuncResult<Struct> rsres = this.executeQueryScalar(new SqlSelectInteger(col), from, where, orderby, params);
			
			if (rsres.hasErrors() || rsres.isEmptyResult()) 
				return null;
			
			return Struct.objectToInteger(rsres.getResult());
		}
		
		// return a single Boolean value (row/column) from table 
		public Boolean executeQueryBoolean(String col, String from, String where, String orderby, Object... params) {
			FuncResult<Struct> rsres = this.executeQueryScalar(new SqlSelectBoolean(col), from, where, orderby, params);
			
			if (rsres.hasErrors() || rsres.isEmptyResult()) 
				return null;
			
			return Struct.objectToBoolean(rsres.getResult());
		}
		
		// return a single row from table 
		public FuncResult<RecordStruct> executeQueryRecord(SqlSelect[] selects, String from, String where, Object... params) {
			FuncResult<RecordStruct> res = new FuncResult<RecordStruct>();
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			// prepare
		    FuncResult<PreparedStatement> psres = this.prep(conn, selects, from, where, null, null, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    // execute
	    	FuncResult<ListStruct> res2 = this.callAndFormat(selects, pstmt);
	    	
	    	ListStruct lrs = res2.getResult();
	    	
	    	if (lrs.getSize() > 0) 
	    		res.setResult(lrs.getItemAsRecord(0));
	    	else
	    		res.setResult(new RecordStruct());	// always return something - caller should do an empty check

	    	try {
	    		if (pstmt != null)
	    			pstmt.close();
			} 
	    	catch (SQLException x) {
			}
	    	
	    	// release
	    	this.releaseConnection(conn);
		    
			return res;
		}
		
		// return a single row (the first) from table 
		public FuncResult<RecordStruct> executeQueryRecordFirst(SqlSelect[] selects, String from, String where, String orderby, Object... params) {
			FuncResult<RecordStruct> res = new FuncResult<RecordStruct>();
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			// prepare
		    FuncResult<PreparedStatement> psres = this.prep(conn, selects, from, where, null, orderby, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    // execute
	    	FuncResult<ListStruct> res2 = this.callAndFormat(selects, pstmt);
	    	
	    	ListStruct lrs = res2.getResult();
	    	
	    	if (lrs.getSize() > 0) 
	    		res.setResult(lrs.getItemAsRecord(0));
	    	else
	    		res.setResult(new RecordStruct());	// always return something - caller should do an empty check

	    	try {
	    		if (pstmt != null)
	    			pstmt.close();
			} 
	    	catch (SQLException x) {
			}
	    	
	    	// release
	    	this.releaseConnection(conn);
		    
			return res;
		}
		
		// return a list of records where each row is a record in this collection
		public FuncResult<ListStruct> callAndFormat(SqlSelect[] select, PreparedStatement pstmt) {
			FuncResult<ListStruct> res = new FuncResult<ListStruct>();
			ListStruct list = new ListStruct();
			
			res.setResult(list);
			
		    ResultSet rs = null;
		    
		    try {					
				Hub.instance.getCountManager().countObjects("dcSqlQueryCount", pstmt);
		    	
				// MariaDB hint that this turns on streaming... review TODO
				//pstmt.setFetchSize(Integer.MIN_VALUE);
				
				rs = pstmt.executeQuery();
				
				ResultSetMetaData md = rs.getMetaData();
			    int columns = md.getColumnCount();
			    
			    if (columns > select.length) {
					res.error(1, "Mismatched column name list");		// TODO code tr
					return res;
			    }
			    
				while (rs.next()) {
					RecordStruct rec = new RecordStruct();
					
					for(int i=1; i<=columns; i++) {
						String name = select[i - 1].name;
						
						rec.setField(name, select[i - 1].format(rs.getObject(i)));
					}
					
					list.addItem(rec);
				}				
		    } 
		    catch (Exception x) {
		    	this.processException(x, res);
		    	
				Hub.instance.getCountManager().countObjects("dcSqlQueryFail", pstmt.toString());
		    } 
		    finally {
		    	try {
		    		if (rs != null)
		    			rs.close();
				} 
		    	catch (SQLException x) {
				}
		    }
		    
			return res;
		}

		// caller needs to close statememt
		// will not return open statement and errors
		public FuncResult<PreparedStatement> prepStatement(Connection conn, String sql, Object... params) {
			FuncResult<PreparedStatement> res = new FuncResult<PreparedStatement>();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
		    PreparedStatement pstmt = null;
		    
		    try {					
		    	pstmt = conn.prepareStatement(sql); 

		    	for (int i = 0; i < params.length; i++) {
		    		Object param = params[i];
		    		
		    		// null params are intentionally not supported - allows us to optionally add params to a complex query
		    		// for NULL support see SqlNull enum
		    		if (param == null)
		    			continue;
		    		
		    		if (param instanceof DateTime)
		    			param = SqlManager.this.getDateAsString((DateTime) param);
		    		
		    		if (param instanceof String) {
		    			if (this.engine == SqlEngine.MariaDb || this.engine == SqlEngine.MySQL)
		    				pstmt.setString(i + 1, (String)param);
		    			else if (this.engine == SqlEngine.SqlServer) 
		    				pstmt.setNString(i + 1, (String)param);
		    			else if (this.engine == SqlEngine.H2) 
		    				pstmt.setNString(i + 1, (String)param);
		    			
		    			continue;
		    		}
		    		
		    		if (param instanceof BigDecimal) {
		    			pstmt.setBigDecimal(i + 1, (BigDecimal) param);
		    			continue;
		    		}
		    		
		    		if (param instanceof Double) {
		    			pstmt.setDouble(i + 1, (double) param);
		    			continue;
		    		}
		    		
		    		if (param instanceof Integer) {
		    			pstmt.setInt(i + 1, (int) param);
		    			continue;
		    		}
		    		
		    		if (param instanceof Long) {
		    			pstmt.setLong(i + 1, (long) param);
		    			continue;
		    		}
		    		
		    		if (param instanceof SqlNull) {
		    			if (param == SqlNull.DateTime)
		    				pstmt.setNull(i + 1, Types.DATE);
		    			else if (param == SqlNull.VarChar) {
			    			if (this.engine == SqlEngine.MariaDb || this.engine == SqlEngine.MySQL)
			    				pstmt.setNull(i + 1, Types.VARCHAR);
			    			else if (this.engine == SqlEngine.SqlServer) 
			    				pstmt.setNull(i + 1, Types.NVARCHAR);
			    			else if (this.engine == SqlEngine.H2) 
			    				pstmt.setNull(i + 1, Types.NVARCHAR);
		    			}
		    			else if (param == SqlNull.BigDecimal)
		    				pstmt.setNull(i + 1, Types.DECIMAL);
		    			else if (param == SqlNull.Double)
		    				pstmt.setNull(i + 1, Types.FLOAT);
		    			else if (param == SqlNull.Int)
		    				pstmt.setNull(i + 1, Types.INTEGER);
		    			else if (param == SqlNull.Long)
		    				pstmt.setNull(i + 1, Types.BIGINT);
		    			else if (param == SqlNull.Text)
		    				pstmt.setNull(i + 1, Types.CLOB);		// TODO test
		    			
		    			continue;
		    		}
		    	}
				
				res.setResult(pstmt);
		    } 
		    catch (Exception x) {
		    	this.processException(x, res);
		    	
				Hub.instance.getCountManager().countObjects("dcSqlPrepFail", sql);
		    	
		    	try {
		    		if (pstmt != null)
		    			pstmt.close();
				} 
		    	catch (SQLException x2) {
				}
		    } 
		    
			return res;
		}

		// caller needs to close statememt
		public FuncResult<PreparedStatement> prepPage(Connection conn, SqlSelect[] select, String from, String where, String groupby, String orderby, int offset, int pagesize, Object... params) {
			// if connection is bad/missing then just try again later
			if (conn == null) {
				FuncResult<PreparedStatement> res = new FuncResult<PreparedStatement>();
				res.errorTr(185, this.name);
				return res;
			}
		    
		    String sql = "SELECT ";
		    
		    for (int i = 0; i < select.length; i++) {
		    	if (i > 0)
		    		sql += ", ";
		    	
		    	sql += select[i].toSql(this);
		    }
		    
		    sql += " FROM " + from;
		    
		    if (StringUtil.isNotEmpty(where))
		    	sql += " WHERE " + where;
		    
		    if (StringUtil.isNotEmpty(groupby))
		    	sql += " GROUP BY " + groupby;
		    
	        if (StringUtil.isEmpty(orderby)) {
				FuncResult<PreparedStatement> res = new FuncResult<PreparedStatement>();
				res.error(1, "Order By required with paging");
				return res;
			}

			if (this.engine == SqlEngine.MariaDb || this.engine == SqlEngine.MySQL) {
		        sql = "SELECT * FROM ( " + sql + " ) AS recset ORDER BY " 
		        	+ orderby + " LIMIT " + offset + "," + pagesize + ";";
			}
			else if (this.engine == SqlEngine.SqlServer) {
		        sql = "WITH RecordPager AS ( " 
		        		+ "SELECT *, ROW_NUMBER() OVER (ORDER BY " + orderby + ") AS RowNumber " 
		        		+ "FROM ( " + sql + " ) AS recset " 
		       		+ ") "
		       		+ "SELECT * FROM RecordPager WHERE RowNumber BETWEEN " + (offset + 1) 
		       		+ " AND " + (offset + pagesize);				
			}
			else if (this.engine == SqlEngine.H2) {
				// TODO
			}
		    
			// TODO support for other dbms
			// http://en.wikipedia.org/wiki/Select_(SQL)
			// http://stackoverflow.com/questions/2771439/jdbc-pagination
			// http://stackoverflow.com/questions/1986998/resultset-to-pagination
			// http://stackoverflow.com/questions/971964/limit-10-20-in-sqlserver
			
			return this.prepStatement(conn, sql, params);
		}

		// caller needs to close statememt
		public FuncResult<PreparedStatement> prepLimit(Connection conn, SqlSelect[] select, String from, String where, String groupby, String orderby, int limit, boolean distinct, Object... params) {
			// if connection is bad/missing then just try again later
			if (conn == null) {
				FuncResult<PreparedStatement> res = new FuncResult<PreparedStatement>();
				res.errorTr(185, this.name);
				return res;
			}
		    
		    String sql = "SELECT ";
		    
		    if (distinct)
		    	sql += "DISTINCT ";
		    
		    for (int i = 0; i < select.length; i++) {
		    	if (i > 0)
		    		sql += ", ";
		    	
		    	sql += select[i].toSql(this);
		    }
		    
		    sql += " FROM " + from;
		    
		    if (StringUtil.isNotEmpty(where))
		    	sql += " WHERE " + where;
		    
		    if (StringUtil.isNotEmpty(groupby))
		    	sql += " GROUP BY " + groupby;
		    
	        if (StringUtil.isEmpty(orderby)) {
				FuncResult<PreparedStatement> res = new FuncResult<PreparedStatement>();
				res.error(1, "Order By required with limit");
				return res;
			}

			if (this.engine == SqlEngine.MariaDb || this.engine == SqlEngine.MySQL) {
		        sql = "SELECT * FROM ( " + sql + " ) AS unset ORDER BY " + orderby + " LIMIT " + limit + ";";
			}
			else if (this.engine == SqlEngine.SqlServer) {
		        sql = "SELECT TOP " + limit + " * FROM ( " + sql + " ) AS unset ORDER BY " + orderby;				
			}
			else if (this.engine == SqlEngine.H2) {
				// TODO
			}
		    
			return this.prepStatement(conn, sql, params);
		}

		// caller needs to close statememt
		public FuncResult<PreparedStatement> prep(Connection conn, SqlSelect[] select, String from, String where, String groupby, String orderby, Object... params) {
			// if connection is bad/missing then just try again later
			if (conn == null) {
				FuncResult<PreparedStatement> res = new FuncResult<PreparedStatement>();
				res.errorTr(185, this.name);
				return res;
			}
		    
		    String sql = "SELECT ";
		    
		    for (int i = 0; i < select.length; i++) {
		    	if (i > 0)
		    		sql += ", ";
		    	
		    	sql += select[i].toSql(this);
		    }
		    
		    sql += " FROM " + from;
		    
		    if (StringUtil.isNotEmpty(where))
		    	sql += " WHERE " + where;
		    
		    if (StringUtil.isNotEmpty(groupby))
		    	sql += " GROUP BY " + groupby;
		    
		    if (StringUtil.isNotEmpty(orderby))
		    	sql += " ORDER BY " + orderby;
		    
			return this.prepStatement(conn, sql, params);
		}
		
		public FuncResult<Integer> executeUpdate(String sql, Object... params) {
			FuncResult<Integer> res = new FuncResult<Integer>();
			res.setResult(0);
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			// prep
		    FuncResult<PreparedStatement> psres = this.prepStatement(conn, sql, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    try {					
				Hub.instance.getCountManager().countObjects("dcSqlUpdateCount", pstmt);
		    
				// execute
				int cnt = pstmt.executeUpdate(); 

				res.setResult(cnt);
		    } 
		    catch (Exception x) {
		    	this.processException(x, res);
		    	
				Hub.instance.getCountManager().countObjects("dcSqlUpdateFail", sql);
		    } 
		    finally {
		    	try {
		    		if (pstmt != null)
		    			pstmt.close();
				} 
		    	catch (SQLException x) {
				}
		    	
		    	// release
		    	this.releaseConnection(conn);
		    }
		    
			return res;
		}
		
		public FuncResult<Integer> executeDelete(String sql, Object... params) {
			FuncResult<Integer> res = new FuncResult<Integer>();
			res.setResult(0);
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			// prep
		    FuncResult<PreparedStatement> psres = this.prepStatement(conn, sql, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    try {					
				Hub.instance.getCountManager().countObjects("dcSqlDeleteCount", pstmt);
		    
				// execute
				int cnt = pstmt.executeUpdate(); 

				res.setResult(cnt);
		    } 
		    catch (Exception x) {
		    	this.processException(x, res);
		    	
				Hub.instance.getCountManager().countObjects("dcSqlDeleteFail", sql);
		    } 
		    finally {
		    	try {
		    		if (pstmt != null)
		    			pstmt.close();
				} 
		    	catch (SQLException x) {
				}
		    	
		    	// release
		    	this.releaseConnection(conn);
		    }
		    
			return res;
		}
				
		public FuncResult<Long> executeInsertReturnId(String sql, Object... params) {
			FuncResult<Long> res = new FuncResult<Long>();
			res.setResult(0L);
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			// prep
		    FuncResult<PreparedStatement> psres = this.prepStatement(conn, sql, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    try {					
				Hub.instance.getCountManager().countObjects("dcSqlInsertCount", pstmt);
		    	
				// execute
				int cnt = pstmt.executeUpdate(); 

				pstmt.close();
				
				if (cnt == 1) {
					pstmt = conn.prepareStatement(this.getLastIdSql());
					
					ResultSet rs = pstmt.executeQuery();
					
					if (rs.next()) 
						res.setResult(rs.getLong("lid"));
				}
		    } 
		    catch (Exception x) {
		    	this.processException(x, res);
		    	
				Hub.instance.getCountManager().countObjects("dcSqlInsertFail", sql);
		    } 
		    finally {
		    	try {
		    		if (pstmt != null)
		    			pstmt.close();
				} 
		    	catch (SQLException x) {
				}
		    	
		    	// release
		    	this.releaseConnection(conn);
		    }
		    
			return res;
		}

		// TODO look into "get generated keys"
		public FuncResult<Integer> executeInsert(String sql, Object... params) {
			FuncResult<Integer> res = new FuncResult<Integer>();
			res.setResult(0);
			
			// acquire
			Connection conn = this.acquireConnection();
			
			// if connection is bad/missing then just try again later
			if (conn == null) {
				res.errorTr(185, this.name);
				return res;
			}
			
			// prep
		    FuncResult<PreparedStatement> psres = this.prepStatement(conn, sql, params);
		    
		    if (res.hasErrors())
		    	return res;
		    
		    PreparedStatement pstmt = psres.getResult();
		    
		    try {					
				Hub.instance.getCountManager().countObjects("dcSqlInsertCount", pstmt);
		    	
				// execute
				int cnt = pstmt.executeUpdate(); 

				pstmt.close();

				res.setResult(cnt);
		    } 
		    catch (Exception x) {
		    	this.processException(x, res);
		    	
				Hub.instance.getCountManager().countObjects("dcSqlInsertFail", sql);
		    } 
		    finally {
		    	try {
		    		if (pstmt != null)
		    			pstmt.close();
				} 
		    	catch (SQLException x) {
				}
		    	
		    	// release
		    	this.releaseConnection(conn);
		    }
		    
			return res;
		}
	}
}