KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > knowgate > dataobjs > DBBind


1 /*
2   Copyright (C) 2003 Know Gate S.L. All rights reserved.
3                       C/Oņa, 107 1š2 28050 Madrid (Spain)
4
5   Redistribution and use in source and binary forms, with or without
6   modification, are permitted provided that the following conditions
7   are met:
8
9   1. Redistributions of source code must retain the above copyright
10      notice, this list of conditions and the following disclaimer.
11
12   2. The end-user documentation included with the redistribution,
13      if any, must include the following acknowledgment:
14      "This product includes software parts from hipergate
15      (http://www.hipergate.org/)."
16      Alternately, this acknowledgment may appear in the software itself,
17      if and wherever such third-party acknowledgments normally appear.
18
19   3. The name hipergate must not be used to endorse or promote products
20      derived from this software without prior written permission.
21      Products derived from this software may not be called hipergate,
22      nor may hipergate appear in their name, without prior written
23      permission.
24
25   This library is distributed in the hope that it will be useful,
26   but WITHOUT ANY WARRANTY; without even the implied warranty of
27   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
28
29   You should have received a copy of hipergate License with this code;
30   if not, visit http://www.hipergate.org or mail to info@hipergate.org
31 */

32
33 package com.knowgate.dataobjs;
34
35 import java.security.AccessControlException JavaDoc;
36
37 import java.lang.System JavaDoc;
38 import java.util.HashMap JavaDoc;
39 import java.util.Iterator JavaDoc;
40
41 import java.sql.DriverManager JavaDoc;
42 import java.sql.Connection JavaDoc;
43 import java.sql.ResultSet JavaDoc;
44 import java.sql.DatabaseMetaData JavaDoc;
45 import java.sql.CallableStatement JavaDoc;
46 import java.sql.Statement JavaDoc;
47 import java.sql.SQLException JavaDoc;
48 import java.sql.Timestamp JavaDoc;
49
50 import com.knowgate.debug.DebugFile;
51 import com.knowgate.misc.Environment;
52 import com.knowgate.misc.Gadgets;
53 import com.knowgate.jdc.JDCConnection;
54 import com.knowgate.jdc.JDCConnectionPool;
55
56 import java.beans.Beans JavaDoc;
57
58 /**
59  * <p>Singleton object for database binding.</p>
60  * @author Sergio Montoro Ten
61  * @version 3.0
62  */

63
64 public class DBBind extends Beans JavaDoc {
65
66   // *****************
67
// Private Variables
68

69   private JDCConnectionPool oConnPool;
70   private String JavaDoc sProfileName;
71   private String JavaDoc sDatabaseProductName;
72   private int iDatabaseProductId;
73   private Exception JavaDoc oConnectXcpt;
74
75   private static HashMap JavaDoc oGlobalTableMap;
76
77   private HashMap JavaDoc oTableMap;
78
79   private static final String JavaDoc VERSION = "3.0.0";
80
81   // ***********
82
// Constructor
83

84   /**
85    * <p>Create DBBind.</p>
86    * Read database connection properties from hipergate.cnf.<br>
87    * This file must be placed at the directory pointed by KNOWGATE_PROFILES
88    * environment variable.<br>
89    * By defualt hipergate.cnf is placed on C:\WINNT\ for Windows Systems and
90    * /etc/ for UNIX Systems.
91    *
92    */

93   public DBBind() {
94
95     // This is a special variable only set after DriverManager.getConnection() from initialize()
96
// If DriverManager.getConnection() fails the the exception will be stored and
97
// re-thrown each time DBBind.getConnection() is called.
98
oConnectXcpt = null;
99
100     try {
101       initialize("hipergate");
102     } catch (Exception JavaDoc e) {
103        oConnectXcpt=e;
104        if (DebugFile.trace) DebugFile.writeln(e.getClass().getName() + " " + e.getMessage());
105     }
106   }
107
108   /**
109    * <p>Create DBBind.</p>
110    * Read database connection properties from specified properties file.
111    * @param sProfile Name of properties file without extension.<br>
112    * For example "hipergate" or "portal".<br>
113    * The properties file must be placed at the directory pointed by
114    * KNOWGATE_PROFILES environment variables.
115    */

116   public DBBind(String JavaDoc sProfile) {
117
118     oConnectXcpt = null;
119
120     try {
121       initialize(sProfile);
122     }
123     catch (AccessControlException JavaDoc e) {
124       oConnectXcpt=e;
125       if (DebugFile.trace) DebugFile.writeln("AccessControlException " + e.getMessage());
126     }
127     catch (ClassNotFoundException JavaDoc e) {
128       oConnectXcpt=e;
129       if (DebugFile.trace) DebugFile.writeln("ClassNotFoundException " + e.getMessage());
130     }
131     catch (SQLException JavaDoc e) {
132       oConnectXcpt=e;
133       if (DebugFile.trace) DebugFile.writeln("SQLException " + e.getMessage());
134     }
135     catch (NullPointerException JavaDoc e) {
136       oConnectXcpt=e;
137       if (DebugFile.trace) DebugFile.writeln("NullPointerException " + e.getMessage());
138     }
139     catch (UnsatisfiedLinkError JavaDoc e) {
140       oConnectXcpt = new Exception JavaDoc("UnsatisfiedLinkError " + e.getMessage(), e);
141       if (DebugFile.trace) DebugFile.writeln("UnsatisfiedLinkError " + e.getMessage());
142     }
143     catch (NumberFormatException JavaDoc e) {
144       oConnectXcpt = new Exception JavaDoc("NumberFormatException " + e.getMessage(), e);
145       if (DebugFile.trace) DebugFile.writeln("NumberFormatException " + e.getMessage());
146     }
147   }
148
149   /**
150    * <P>Close DBBind</P>
151    * Close connections from pool.<BR>
152    * Stop connection reaper.<BR>
153    */

154   public void close() {
155
156    if (DebugFile.trace) {
157      DebugFile.writeln("Begin DBBind.close()");
158      DebugFile.incIdent();
159    }
160
161    oConnectXcpt = null;
162
163    oGlobalTableMap = null;
164
165    oTableMap.clear();
166    oTableMap = null;
167
168    oConnPool.close();
169
170    oConnPool = null;
171
172    if (DebugFile.trace) {
173      DebugFile.decIdent();
174      DebugFile.writeln("End DBBind.close()");
175    }
176   }
177
178   // ----------------------------------------------------------
179

180   /**
181    * Close and reopen the connection pool and reload the table map cache
182    * @throws SQLException
183    * @throws ClassNotFoundException
184    */

185   public void restart()
186     throws SQLException JavaDoc, ClassNotFoundException JavaDoc {
187
188     if (DebugFile.trace) {
189       DebugFile.writeln("Begin DBBind.restart()");
190       DebugFile.incIdent();
191     }
192
193     oConnectXcpt = null;
194
195     oGlobalTableMap = null;
196
197     oTableMap.clear();
198     oTableMap = null;
199
200     try {
201       oConnPool.close();
202     }
203     catch (Exception JavaDoc e) {
204       if (DebugFile.trace) DebugFile.writeln(e.getClass().getName() + " " + e.getMessage());
205     }
206
207     oConnPool = null;
208
209     initialize (sProfileName);
210
211     if (DebugFile.trace) {
212       DebugFile.incIdent();
213       DebugFile.writeln("End DBBind.restart()");
214     }
215   } // restart
216

217   // ----------------------------------------------------------
218

219   /**
220    * Get connection pool used by this database binding
221    * @return Reference to JDCConnectionPool
222    */

223   public JDCConnectionPool connectionPool() {
224     return oConnPool;
225   }
226
227   // ----------------------------------------------------------
228

229   private void loadDriver(String JavaDoc sProfile)
230     throws ClassNotFoundException JavaDoc, NullPointerException JavaDoc {
231
232     Class JavaDoc oDriver;
233     String JavaDoc sDriver;
234
235     if (DebugFile.trace) DebugFile.writeln("Begin DBBind.loadDriver(" + sProfile + ")" );
236
237     sDriver = Environment.getProfileVar(sProfile, "driver");
238
239     if (DebugFile.trace) DebugFile.writeln(" driver=" + sDriver);
240
241     if (null==sDriver)
242       throw new NullPointerException JavaDoc("Could not find property driver at " + sProfile);
243
244     oDriver = Class.forName(sDriver);
245
246     if (DebugFile.trace) DebugFile.writeln("End DBBind.loadDriver()" );
247   } // loadDriver()
248

249   // ----------------------------------------------------------
250

251   private static boolean in (String JavaDoc sStr, String JavaDoc[] aSet) {
252
253     boolean bRetVal = false;
254
255     if (aSet!=null) {
256       final int iLen = aSet.length;
257
258       for (int i=0; i<iLen && !bRetVal; i++)
259         bRetVal = sStr.equalsIgnoreCase(aSet[i]);
260     } // fi
261

262     return bRetVal;
263   }
264
265   // ----------------------------------------------------------
266

267   protected void initialize(String JavaDoc sProfile)
268     throws ClassNotFoundException JavaDoc, SQLException JavaDoc, NullPointerException JavaDoc,
269            AccessControlException JavaDoc,UnsatisfiedLinkError JavaDoc,NumberFormatException JavaDoc {
270
271     int i;
272     Connection JavaDoc oConn;
273     DatabaseMetaData JavaDoc oMData;
274     ResultSet JavaDoc oRSet;
275     String JavaDoc TableTypes[] = new String JavaDoc[1];
276     DBTable oTable;
277     String JavaDoc sCatalog;
278     String JavaDoc sSchema;
279     String JavaDoc sTableName;
280     Iterator JavaDoc oTableIterator;
281     String JavaDoc[] aExclude;
282
283     oTableMap = new HashMap JavaDoc(255);
284     oGlobalTableMap = oTableMap ;
285
286     if (DebugFile.trace)
287       {
288       DebugFile.writeln("hipergate package build " + DBBind.VERSION);
289       DebugFile.envinfo();
290
291       DebugFile.writeln("Begin DBBind.initialize(" + sProfile+ ")" );
292       DebugFile.incIdent();
293       }
294
295       sProfileName = sProfile;
296
297       // ****************
298
// Load JDBC driver
299
loadDriver(sProfile);
300
301       if (DebugFile.trace) DebugFile.writeln("Load Driver " + Environment.getProfileVar(sProfile, "driver") + " : OK\n" );
302
303       if (DebugFile.trace) DebugFile.writeln("Trying to connect to " + Environment.getProfileVar(sProfile, "dburl") + " with user " + Environment.getProfileVar(sProfile, "dbuser"));
304
305       // **********************************************************
306
// Get database connection parameters from file hipergate.cnf
307

308       // New for v2.2 *
309
try {
310         DriverManager.setLoginTimeout(Integer.parseInt(Environment.getProfileVar(sProfile, "logintimeout", "20")));
311       } catch (Exception JavaDoc x) {
312         if (DebugFile.trace) DebugFile.writeln("DriverManager.setLoginTimeout() "+x.getClass().getName()+" "+x.getMessage());
313       }
314       // **************
315

316       try {
317         oConn = DriverManager.getConnection(Environment.getProfileVar(sProfile, "dburl"),
318                                             Environment.getProfileVar(sProfile, "dbuser"),
319                                             Environment.getProfileVar(sProfile, "dbpassword"));
320       }
321       catch (SQLException JavaDoc e) {
322         if (DebugFile.trace) DebugFile.writeln("SQLException [" + e.getSQLState() + "]:" + String.valueOf(e.getErrorCode()) + " " + e.getMessage());
323         oConnectXcpt = new SQLException JavaDoc(e.getMessage(), e.getSQLState(), e.getErrorCode());
324         throw (SQLException JavaDoc) oConnectXcpt;
325       }
326
327       if (DebugFile.trace) {
328         DebugFile.writeln("Database Connection to " + Environment.getProfileVar(sProfile, "dburl") + " : OK\n" );
329         DebugFile.writeln("Calling Connection.getMetaData()");
330       }
331
332       oMData = oConn.getMetaData();
333
334       if (DebugFile.trace) DebugFile.writeln("Calling DatabaseMetaData.getDatabaseProductName()");
335
336       sDatabaseProductName = oMData.getDatabaseProductName();
337
338       if (DebugFile.trace) {
339         DebugFile.writeln("Database is \"" + sDatabaseProductName + "\"");
340         DebugFile.writeln("Product version " + oMData.getDatabaseProductVersion());
341         DebugFile.writeln(oMData.getDriverName() + " " + oMData.getDriverVersion());
342         DebugFile.writeln("Max connections " + String.valueOf(oMData.getMaxConnections()));
343         DebugFile.writeln("Max statements " + String.valueOf(oMData.getMaxStatements()));
344       }
345
346       if (sDatabaseProductName.equals(DBMSNAME_POSTGRESQL))
347         iDatabaseProductId = DBMS_POSTGRESQL;
348       else if (sDatabaseProductName.equals(DBMSNAME_MSSQL))
349         iDatabaseProductId = DBMS_MSSQL;
350       else if (sDatabaseProductName.equals(DBMSNAME_ORACLE))
351         iDatabaseProductId = DBMS_ORACLE;
352       else if (sDatabaseProductName.equals(DBMSNAME_MYSQL))
353         iDatabaseProductId = DBMS_MYSQL;
354       else
355         iDatabaseProductId = DBMS_GENERIC;
356
357       Functions.setForDBMS(sDatabaseProductName);
358
359       // **********************
360
// Cache database catalog
361

362       sCatalog = oConn.getCatalog();
363
364       if (DebugFile.trace) DebugFile.writeln("Catalog is \"" + sCatalog + "\"");
365
366       if (DebugFile.trace) DebugFile.writeln("Gather metadata : OK" );
367
368       sSchema = Environment.getProfileVar(sProfile, "schema", "");
369
370       if (DebugFile.trace) DebugFile.writeln("Schema is \"" + sSchema + "\"");
371
372       i = 0;
373
374       TableTypes[0] = "TABLE";
375
376       if (DBMS_ORACLE==iDatabaseProductId) {
377         aExclude = new String JavaDoc[]{ "AUDIT_ACTIONS", "STMT_AUDIT_OPTION_MAP", "DUAL",
378         "PSTUBTBL", "USER_CS_SRS", "USER_TRANSFORM_MAP", "CS_SRS", "HELP",
379         "SDO_ANGLE_UNITS", "SDO_AREA_UNITS", "SDO_DIST_UNITS", "SDO_DATUMS",
380         "SDO_CMT_CBK_DML_TABLE", "SDO_CMT_CBK_FN_TABLE", "SDO_CMT_CBK_DML_TABLE",
381         "SDO_PROJECTIONS", "SDO_ELLIPSOIDS", "SDO_GEOR_XMLSCHEMA_TABLE",
382         "SDO_GR_MOSAIC_0", "SDO_GR_MOSAIC_1", "SDO_GR_MOSAIC_2", "SDO_GR_MOSAIC_3",
383         "SDO_TOPO_RELATION_DATA", "SDO_TOPO_TRANSACT_DATA", "SDO_TXN_IDX_DELETES",
384         "DO_TXN_IDX_EXP_UPD_RGN", "SDO_TXN_IDX_INSERTS", "SDO_CS_SRS", "IMPDP_STATS",
385         "OLAP_SESSION_CUBES", "OLAP_SESSION_DIMS", "OLAPI_HISTORY",
386         "OLAPI_IFACE_OBJECT_HISTORY", "OLAPI_IFACE_OP_HISTORY", "OLAPI_MEMORY_HEAP_HISTORY",
387         "OLAPI_MEMORY_OP_HISTORY", "OLAPI_SESSION_HISTORY", "OLAPTABLEVELS","OLAPTABLEVELTUPLES",
388         "OLAP_OLEDB_FUNCTIONS_PVT", "OLAP_OLEDB_KEYWORDS", "OLAP_OLEDB_MDPROPS","OLAP_OLEDB_MDPROPVALS",
389         "OGIS_SPATIAL_REFERENCE_SYSTEMS", "SYSTEM_PRIVILEGE_MAP", "TABLE_PRIVILEGE_MAP" };
390
391         if (DebugFile.trace) {
392           ResultSet JavaDoc oSchemas = null;
393           try {
394             int iSchemaCount = 0;
395             oSchemas = oMData.getSchemas();
396             while (oSchemas.next()) {
397               DebugFile.writeln("schema name = " + oSchemas.getString(1));
398               iSchemaCount++;
399             }
400             oSchemas.close();
401             oSchemas = null;
402             if (0==iSchemaCount) DebugFile.writeln("no schemas found");
403           }
404           catch (Exception JavaDoc sqle) {
405             try { if (null!=oSchemas) oSchemas.close();} catch (Exception JavaDoc ignore) {}
406             DebugFile.writeln("SQLException at DatabaseMetaData.getSchemas() " + sqle.getMessage());
407           }
408           DebugFile.writeln("DatabaseMetaData.getTables(" + sCatalog + ", null, %, {TABLE})");
409         }
410
411         oRSet = oMData.getTables(sCatalog, null, "%", TableTypes);
412
413         while (oRSet.next()) {
414
415           if (oRSet.getString(3).indexOf('$')<0 && !in(oRSet.getString(3).toUpperCase(), aExclude)) {
416             oTable = new DBTable(sCatalog, sSchema, oRSet.getString(3), ++i);
417
418             sTableName = oTable.getName().toLowerCase();
419
420             if (oTableMap.containsKey(sTableName))
421               oTableMap.remove(sTableName);
422
423             oTableMap.put(sTableName, oTable);
424
425             if (DebugFile.trace)
426               DebugFile.writeln("Reading table " + oTable.getName());
427           }
428           else if (DebugFile.trace)
429             DebugFile.writeln("Skipping table " + oRSet.getString(3));
430        } // wend
431

432       }
433       else {
434         if (DBMS_POSTGRESQL==iDatabaseProductId)
435           aExclude = new String JavaDoc[]{ "sql_languages", "sql_features",
436                                    "sql_implementation_info", "sql_packages",
437                                    "sql_sizing", "sql_sizing_profiles",
438                                    "pg_ts_cfg", "pg_logdir_ls",
439                                    "pg_ts_cfgmap", "pg_ts_dict", "pg_ts_parses",
440                                    "pg_ts_parser", "pg_reload_conf" };
441         else if (DBMS_MSSQL==iDatabaseProductId)
442           aExclude = new String JavaDoc[]{ "syscolumns", "syscomments", "sysdepends",
443                                    "sysfilegroups", "sysfiles" , "sysfiles1",
444                                    "sysforeignkeys", "sysfulltextcatalogs",
445                                    "sysfulltextnotify", "sysindexes",
446                                    "sysindexkeys", "sysmembers", "sysobjects",
447                                    "syspermissions", "sysproperties",
448                                    "sysprotects", "sysreferences", "systypes",
449                                    "sysusers" };
450         else
451           aExclude = null;
452
453         if (DebugFile.trace)
454           DebugFile.writeln("DatabaseMetaData.getTables(" + sCatalog + ", " + sSchema + ", %, {TABLE})");
455
456         oRSet = oMData.getTables(sCatalog, sSchema, "%", TableTypes);
457
458         // For each table, keep its name in a memory map
459

460         if (sSchema.length()>0) {
461
462           while (oRSet.next()) {
463
464             sTableName = oRSet.getString(3);
465
466             if (!oRSet.wasNull()) {
467               oTable = new DBTable (sCatalog, Environment.getProfileVar(sProfile, "schema", "dbo"), sTableName, ++i);
468
469               sTableName = oTable.getName().toLowerCase();
470
471               if (!in(sTableName, aExclude)) {
472                 if (oTableMap.containsKey(sTableName))
473                   oTableMap.remove(sTableName);
474
475                 oTableMap.put(sTableName, oTable);
476
477                 if (DebugFile.trace) DebugFile.writeln("Reading table " + sSchema + "." + oTable.getName());
478               } // fi (!in(sTableName, aExclude))
479
} // fi (!oRSet.wasNull())
480
} // wend
481
}
482         else { // sSchema == ""
483
while (oRSet.next()) {
484
485             sTableName = oRSet.getString(3);
486
487             if (!oRSet.wasNull()) {
488               oTable = new DBTable (sCatalog, "", sTableName, ++i);
489
490               sTableName = oTable.getName().toLowerCase();
491
492               if (!in(sTableName, aExclude)) {
493                 if (oTableMap.containsKey(sTableName))
494                   oTableMap.remove(sTableName);
495
496                 oTableMap.put(sTableName, oTable);
497
498                 if (DebugFile.trace) DebugFile.writeln("Reading table " + oTable.getName());
499               } // fi (!in(sTableName, aExclude))
500
} // fi (!oRSet.wasNull())
501
} // wend
502
} // fi (sSchema == "")
503
} // fi (DBMS_ORACLE!=iDatabaseProductId)
504

505       oRSet.close();
506
507       oTableIterator = oTableMap.values().iterator();
508
509       // For each table, read its column structure and keep it in memory
510

511       while (oTableIterator.hasNext()) {
512         oTable = (DBTable) oTableIterator.next();
513         oTable.readColumns(oConn,oMData);
514       } // wend
515

516       if (DebugFile.trace) DebugFile.writeln("Table scan : OK" );
517
518       oConn.close();
519       oConn=null;
520
521       // Create database connection pool
522

523       if (DebugFile.trace) DebugFile.writeln("new JDCConnectionPool("+Environment.getProfileVar(sProfile,"dburl")+","+Environment.getProfileVar(sProfile,"dbuser")+",...,"+Environment.getProfileVar(sProfile,"poolsize", "32")+","+Environment.getProfileVar(sProfile,"maxconnections", "100")+")");
524
525       // ***************************************************************
526
// New for v2.2
527
// Perform aditional checkings of hipergate.cnf integer values and
528
// add logintimeout and connectiontimeout property handling
529

530       int iPoolSize, iMaxConns, iLoginTimeout;
531       long iConnectionTimeout;
532
533       try {
534         iPoolSize=Integer.parseInt(Environment.getProfileVar(sProfile,"poolsize", "32"));
535         if (iPoolSize<0) throw new NumberFormatException JavaDoc();
536       }
537       catch (NumberFormatException JavaDoc nfe) {
538         if (DebugFile.trace) {
539           DebugFile.writeln("poolsize property at "+sProfile+".cnf must be a positive integer value");
540           DebugFile.decIdent();
541         }
542         throw new NumberFormatException JavaDoc("poolsize property at "+sProfile+".cnf must be a positive integer value");
543       }
544
545       try {
546         iMaxConns=Integer.parseInt(Environment.getProfileVar(sProfile,"maxconnections", "100"));
547         if (iMaxConns<0) throw new NumberFormatException JavaDoc();
548       }
549       catch (NumberFormatException JavaDoc nfe) {
550         if (DebugFile.trace) {
551           DebugFile.writeln("maxconnections property at "+sProfile+".cnf must be a positive integer value");
552           DebugFile.decIdent();
553         }
554         throw new NumberFormatException JavaDoc("maxconnections property at "+sProfile+".cnf must be a positive integer value");
555       }
556
557       try {
558         iLoginTimeout=Integer.parseInt(Environment.getProfileVar(sProfile,"logintimeout", "20"));
559       }
560       catch (NumberFormatException JavaDoc nfe) {
561         if (DebugFile.trace) {
562           DebugFile.writeln("logintimeout property at "+sProfile+".cnf must be an integer value");
563           DebugFile.decIdent();
564         }
565         throw new NumberFormatException JavaDoc("logintimeout property at "+sProfile+".cnf must be an integer value");
566       }
567       if (iLoginTimeout<=0) {
568         if (DebugFile.trace) {
569           DebugFile.writeln("logintimeout property at "+sProfile+".cnf must be greater than zero");
570           DebugFile.decIdent();
571         }
572         throw new NumberFormatException JavaDoc("logintimeout property at "+sProfile+".cnf must be greater than zero");
573       }
574
575       try {
576         iConnectionTimeout=Long.parseLong(Environment.getProfileVar(sProfile,"connectiontimeout", "60000"));
577       }
578       catch (NumberFormatException JavaDoc nfe) {
579         if (DebugFile.trace) {
580           DebugFile.writeln("connectiontimeout property at "+sProfile+".cnf must be an integer value");
581           DebugFile.decIdent();
582         }
583         throw new NumberFormatException JavaDoc("connectiontimeout property at "+sProfile+".cnf must be an integer value");
584       }
585       if (iConnectionTimeout<1000l) {
586         if (DebugFile.trace) {
587           DebugFile.writeln("connectiontimeout property at "+sProfile+".cnf must be greater than 1000 miliseconds");
588           DebugFile.decIdent();
589         }
590         throw new NumberFormatException JavaDoc("connectiontimeout property at "+sProfile+".cnf must be greater than 1000 miliseconds");
591       }
592
593       // ***************************************************************
594

595       oConnPool = new JDCConnectionPool(this,
596                                         Environment.getProfileVar(sProfile,"dburl"),
597                                         Environment.getProfileVar(sProfile,"dbuser"),
598                                         Environment.getProfileVar(sProfile,"dbpassword"),
599                                         iPoolSize,iMaxConns,iLoginTimeout,iConnectionTimeout);
600
601       if (null!=oConnPool) {
602         if (DebugFile.trace) DebugFile.writeln("Connection pool creation : OK" );
603
604         try {
605           oConnPool.setReaperDaemonDelay(Long.parseLong(Environment.getProfileVar(sProfile,"connectionreaperdelay", "30000")));
606         }
607         catch (NumberFormatException JavaDoc nfe) {
608           if (DebugFile.trace) {
609             DebugFile.writeln("connectionreaperdelay property at "+sProfile+".cnf must be an integer value");
610             DebugFile.decIdent();
611           }
612           throw new NumberFormatException JavaDoc("connectionreaperdelay property at "+sProfile+".cnf must be an integer value");
613         }
614         catch (IllegalArgumentException JavaDoc iae) {
615           if (DebugFile.trace) {
616             DebugFile.writeln("connectionreaperdelay property at " + sProfile + ".cnf must be greater than 1000");
617             DebugFile.decIdent();
618           }
619           throw new NumberFormatException JavaDoc("connectionreaperdelay property at "+sProfile+".cnf must must be greater than 1000");
620         }
621       } else {
622         if (DebugFile.trace) DebugFile.writeln("Connection pool creation failed!" );
623       }
624
625       if (DebugFile.trace)
626         {
627         DebugFile.decIdent();
628         DebugFile.writeln("End DBBind.initialize()");
629       }
630   } // initialize
631

632   // ----------------------------------------------------------
633

634   /**
635    * Get the name of Database Management System Connected
636    * @return one of { "Microsoft SQL Server", "Oracle", "PostgreSQL" }
637    * @throws SQLException
638    */

639
640   public String JavaDoc getDatabaseProductName()
641     throws SQLException JavaDoc {
642
643     if (null!=oConnectXcpt) throw (SQLException JavaDoc) oConnectXcpt;
644
645     return sDatabaseProductName;
646   }
647
648   // ----------------------------------------------------------
649

650   /**
651    * <p>Get Name of profile used for initializing DBBind</p>
652    * Profile Name is the properties file name ("hipergate.cnf") without extension.<br>
653    * For example "hipergate", "real", "demo", "test", "portal"
654    * @return Profile name
655    */

656   public String JavaDoc getProfileName() {
657     return sProfileName;
658   }
659
660   // ----------------------------------------------------------
661

662   /**
663    * Checks if an object exists at database
664    * Checking is done directly against database catalog tables,
665    * if current user does not have enought priviledges for reading
666    * database catalog tables methos may fail or return a wrong result.
667    * @param oConn Database connection
668    * @param sObjectName Objeto name
669    * @param sObjectType Objeto type
670    * C = CHECK constraint
671    * D = Default or DEFAULT constraint
672    * F = FOREIGN KEY constraint
673    * L = Log
674    * P = Stored procedure
675    * PK = PRIMARY KEY constraint (type is K)
676    * RF = Replication filter stored procedure
677    * S = System table
678    * TR = Trigger
679    * U = User table
680    * UQ = UNIQUE constraint (type is K)
681    * V = View
682    * X = Extended stored procedure
683    * @return <b>true</b> if object exists, <b>false</b> otherwise
684    * @throws SQLException
685    * @throws UnsupportedOperationException If current database management system is not supported for this method
686    */

687
688   public static boolean exists(JDCConnection oConn, String JavaDoc sObjectName, String JavaDoc sObjectType)
689       throws SQLException JavaDoc, UnsupportedOperationException JavaDoc {
690
691       return oConn.exists(sObjectName, sObjectType);
692
693   } // exists()
694

695   // ----------------------------------------------------------
696

697   /**
698    * Get datamodel version
699    * @param oConn JDCConnection object
700    * @return vs_stamp field from k_version table
701    * @throws SQLException
702    */

703   public static String JavaDoc getDataModelVersion(JDCConnection oConn) throws SQLException JavaDoc {
704     String JavaDoc sVersion = null;
705
706     if (DebugFile.trace) {
707       DebugFile.writeln("Begin DBBind.getDataModelVersion([Connection])");
708       DebugFile.incIdent();
709     }
710
711     if (DBBind.exists(oConn, DB.k_version, "U")) {
712       Statement JavaDoc oStmt = oConn.createStatement();
713       ResultSet JavaDoc oRSet = oStmt.executeQuery("SELECT vs_stamp FROM " + DB.k_version);
714       if (oRSet.next())
715         sVersion = oRSet.getString(1);
716       oRSet.close();
717       oStmt.close();
718     }
719
720     if (DebugFile.trace) {
721       DebugFile.decIdent();
722       DebugFile.writeln("End DBBind.getDataModelVersion() : " + sVersion);
723     }
724
725     return sVersion;
726   } // getDataModelVersion
727

728   // ----------------------------------------------------------
729

730   /**
731    * Get datamodel version number
732    * @param oConn JDCConnection object
733    * @return for 2.0.8-> 20008 , 2.1.0 -> 20100, etc.
734    * @throws SQLException
735    */

736   public static int getDataModelVersionNumber(JDCConnection oConn)
737     throws SQLException JavaDoc {
738
739     String JavaDoc sVersion = getDataModelVersion(oConn);
740
741     if (null==sVersion) return 0;
742
743     final int iLen = sVersion.length();
744     String JavaDoc sMajor = "", sMinor = "", sRevision = "";
745     int iDots = 0;
746
747     for (int i=0; i<iLen; i++) {
748       if (sVersion.charAt(i)>='0' && sVersion.charAt(i)<='9') {
749         switch (iDots) {
750           case 0:
751             sMajor += sVersion.charAt(i);
752             break;
753           case 1:
754             sMinor += sVersion.charAt(i);
755             break;
756           case 2:
757             sRevision += sVersion.charAt(i);
758         }
759       }
760       else if (sVersion.charAt(i)=='.')
761         iDots++;
762     } // next (i)
763

764     return Integer.parseInt(sMajor+Gadgets.leftPad(sMinor, '0', 2)+Gadgets.leftPad(sRevision, '0', 2));
765   } // getDataModelVersionNumber
766

767   // ----------------------------------------------------------
768

769   /**
770    * <p>Get current value for a sequence</p>
771    * @param oConn JDCConnection
772    * @param sSequenceName Sequence name.
773    * In MySQL and SQL Server sequences are implemented using row locks at k_sequences table.
774    * @return Current sequence value
775    * @throws SQLException
776    * @throws UnsupportedOperationException Not all databases support sequences.
777    * On Oracle and PostgreSQL, native SEQUENCE objects are used,
778    * on MySQL and Microsoft SQL Server the stored procedure k_sp_currval simulates sequences,
779    * this function is not supported on other DataBase Management Systems.
780    * @since 3.0
781    */

782
783   public static int currVal(JDCConnection oConn, String JavaDoc sSequenceName)
784       throws SQLException JavaDoc, UnsupportedOperationException JavaDoc {
785
786     Statement JavaDoc oStmt;
787     ResultSet JavaDoc oRSet;
788     CallableStatement JavaDoc oCall;
789     int iCurrVal;
790
791     if (DebugFile.trace)
792       {
793       DebugFile.writeln("Begin hipergate DBBind.currVal([JDCConnection], " + sSequenceName + ")" );
794       DebugFile.incIdent();
795       }
796
797     switch (oConn.getDataBaseProduct()) {
798
799       case JDCConnection.DBMS_MYSQL:
800       case JDCConnection.DBMS_MSSQL:
801
802         if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call k_sp_currval ('" + sSequenceName + "',?)})" );
803
804         oCall = oConn.prepareCall("{call k_sp_currval (?,?)}");
805         oCall.setString(1, sSequenceName);
806         oCall.registerOutParameter(2, java.sql.Types.INTEGER);
807         oCall.execute();
808         iCurrVal = oCall.getInt(2);
809         oCall.close();
810         oCall = null;
811         break;
812
813       case JDCConnection.DBMS_POSTGRESQL:
814         oStmt = oConn.createStatement();
815
816         if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(SELECT nextval('" + sSequenceName + "'))" );
817
818         oRSet = oStmt.executeQuery("SELECT nextval('" + sSequenceName + "')");
819         oRSet.next();
820         iCurrVal = oRSet.getInt(1)-1;
821         oRSet.close();
822
823         if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(SELECT setval('" + sSequenceName + "',"+String.valueOf(iCurrVal)+"))" );
824
825         oRSet = oStmt.executeQuery("SELECT setval('" + sSequenceName + "',"+String.valueOf(iCurrVal)+")");
826         oRSet.close();
827
828         oStmt.close();
829         break;
830
831       case JDCConnection.DBMS_ORACLE:
832         oStmt = oConn.createStatement();
833
834         if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(SELECT " + sSequenceName + ".CURRVAL))" );
835
836         oRSet = oStmt.executeQuery("SELECT " + sSequenceName + ".CURRVAL FROM dual");
837         oRSet.next();
838         iCurrVal = oRSet.getInt(1);
839         oRSet.close();
840         oStmt.close();
841         break;
842
843       default:
844         throw new UnsupportedOperationException JavaDoc("function currVal() not supported on current DBMS");
845     }
846
847     oConn = null;
848
849     if (DebugFile.trace)
850       {
851       DebugFile.decIdent();
852       DebugFile.writeln("End DBBind.currVal() : " + String.valueOf(iCurrVal));
853       }
854
855     return iCurrVal;
856   } // currVal
857

858   // ----------------------------------------------------------
859

860   /**
861    * <p>Get current value for a sequence</p>
862    * @param oSQLConn Database connection
863    * @param sSequenceName Sequence name.
864    * In MySQL and SQL Server sequences are implemented using row locks at k_sequences table.
865    * @return Current sequence value
866    * @throws SQLException
867    * @throws UnsupportedOperationException Not all databases support sequences.
868    * On Oracle and PostgreSQL, native SEQUENCE objects are used,
869    * on MySQL and Microsoft SQL Server the stored procedure k_sp_nextval simulates sequences,
870    * this function is not supported on other DataBase Management Systems.
871    * @since 3.0
872    */

873
874   public static int currVal(Connection JavaDoc oSQLConn, String JavaDoc sSequenceName)
875       throws SQLException JavaDoc, UnsupportedOperationException JavaDoc {
876     return currVal(new JDCConnection(oSQLConn, null), sSequenceName);
877   }
878
879   // ----------------------------------------------------------
880

881   /**
882    * <p>Get next value for a sequence</p>
883    * @param oConn JDCConnection
884    * @param sSequenceName Sequence name.
885    * In MySQL and SQL Server sequences are implemented using row locks at k_sequences table.
886    * @return int Next sequence value
887    * @throws SQLException
888    * @throws UnsupportedOperationException Not all databases support sequences.
889    * On Oracle and PostgreSQL, native SEQUENCE objects are used,
890    * on Microsoft SQL Server the stored procedure k_sp_nextval simulates sequences,
891    * this function is not supported on other DataBase Management Systems.
892    * @since 3.0
893    */

894   public static int nextVal(JDCConnection oConn, String JavaDoc sSequenceName)
895       throws SQLException JavaDoc, UnsupportedOperationException JavaDoc {
896
897     Statement JavaDoc oStmt;
898     ResultSet JavaDoc oRSet;
899     CallableStatement JavaDoc oCall;
900     int iNextVal;
901
902     if (DebugFile.trace)
903       {
904       DebugFile.writeln("Begin hipergate DBBind.nextVal([JDCConnection], " + sSequenceName + ")" );
905       DebugFile.incIdent();
906       }
907
908     switch (oConn.getDataBaseProduct()) {
909
910       case JDCConnection.DBMS_MYSQL:
911       case JDCConnection.DBMS_MSSQL:
912
913         if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call k_sp_nextval ('" + sSequenceName + "',?)})" );
914
915         oCall = oConn.prepareCall("{call k_sp_nextval (?,?)}");
916         oCall.setString(1, sSequenceName);
917         oCall.registerOutParameter(2, java.sql.Types.INTEGER);
918         oCall.execute();
919         iNextVal = oCall.getInt(2);
920         oCall.close();
921         oCall = null;
922         break;
923
924       case JDCConnection.DBMS_POSTGRESQL:
925         oStmt = oConn.createStatement();
926
927         if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(SELECT nextval('" + sSequenceName + "'))" );
928
929         oRSet = oStmt.executeQuery("SELECT nextval('" + sSequenceName + "')");
930         oRSet.next();
931         iNextVal = oRSet.getInt(1);
932         oRSet.close();
933         oStmt.close();
934         break;
935
936       case JDCConnection.DBMS_ORACLE:
937         oStmt = oConn.createStatement();
938
939         if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(SELECT nextval('" + sSequenceName + "'))" );
940
941         oRSet = oStmt.executeQuery("SELECT " + sSequenceName + ".NEXTVAL FROM dual");
942         oRSet.next();
943         iNextVal = oRSet.getInt(1);
944         oRSet.close();
945         oStmt.close();
946         break;
947
948       default:
949         throw new UnsupportedOperationException JavaDoc("function nextVal() not supported on current DBMS");
950     }
951
952     oConn = null;
953
954     if (DebugFile.trace)
955       {
956       DebugFile.decIdent();
957       DebugFile.writeln("End DBBind.nextVal() : " + String.valueOf(iNextVal));
958       }
959
960     return iNextVal;
961   } // nextVal
962

963   // ----------------------------------------------------------
964

965   /**
966    * <p>Get next value for a sequence</p>
967    * @param oSQLConn Database connection
968    * @param sSequenceName Sequence name.
969    * In MySQL and SQL Server sequences are implemented using row locks at k_sequences table.
970    * @return int Next sequence value
971    * @throws SQLException
972    * @throws UnsupportedOperationException Not all databases support sequences.
973    * On Oracle and PostgreSQL, native SEQUENCE objects are used,
974    * on Microsoft SQL Server the stored procedure k_sp_nextval simulates sequences,
975    * this function is not supported on other DataBase Management Systems.
976    */

977
978   public static int nextVal(Connection JavaDoc oSQLConn, String JavaDoc sSequenceName)
979       throws SQLException JavaDoc, UnsupportedOperationException JavaDoc {
980
981     return nextVal(new JDCConnection(oSQLConn, null), sSequenceName);
982   }
983
984   // ----------------------------------------------------------
985

986   /**
987    * Format Date in ODBC escape sequence style
988    * @param dt Date to be formated
989    * @param sFormat Format Type "d" or "ts" or "shortTime".
990    * Use d for { d 'yyyy-mm-dd' }, use ts for { ts 'ts=yyyy-mm-dd hh:nn:ss' }<br>
991    * use shortTime for hh:mm<br>
992    * use shortDate for yyyy-mm-dd<br>
993    * use dateTime for yyyy-mm-dd hh:mm:ss<br>
994    * @return Formated date
995    * @throws IllegalArgumentException if dt is of type java.sql.Date
996    */

997
998   public static String JavaDoc escape(java.util.Date JavaDoc dt, String JavaDoc sFormat)
999     throws IllegalArgumentException JavaDoc {
1000    String JavaDoc str = "";
1001    String JavaDoc sMonth, sDay, sHour, sMin, sSec;
1002
1003    if (sFormat.equalsIgnoreCase("ts") || sFormat.equalsIgnoreCase("d")) {
1004      str = DBBind.Functions.escape(dt, sFormat);
1005    }
1006    else if (sFormat.equalsIgnoreCase("shortTime")) {
1007      sHour = (dt.getHours()<10 ? "0" + String.valueOf(dt.getHours()) : String.valueOf(dt.getHours()));
1008      sMin = (dt.getMinutes()<10 ? "0" + String.valueOf(dt.getMinutes()) : String.valueOf(dt.getMinutes()));
1009      str += sHour + ":" + sMin;
1010    }
1011    else if (sFormat.equalsIgnoreCase("shortDate")) {
1012      sMonth = (dt.getMonth()+1<10 ? "0" + String.valueOf((dt.getMonth()+1)) : String.valueOf(dt.getMonth()+1));
1013      sDay = (dt.getDate()<10 ? "0" + String.valueOf(dt.getDate()) : String.valueOf(dt.getDate()));
1014
1015      str += String.valueOf(dt.getYear()+1900) + "-" + sMonth + "-" + sDay;
1016    } else {
1017      sMonth = (dt.getMonth()+1<10 ? "0" + String.valueOf((dt.getMonth()+1)) : String.valueOf(dt.getMonth()+1));
1018      sDay = (dt.getDate()<10 ? "0" + String.valueOf(dt.getDate()) : String.valueOf(dt.getDate()));
1019      sHour = (dt.getHours()<10 ? "0" + String.valueOf(dt.getHours()) : String.valueOf(dt.getHours()));
1020      sMin = (dt.getMinutes()<10 ? "0" + String.valueOf(dt.getMinutes()) : String.valueOf(dt.getMinutes()));
1021      sSec = (dt.getSeconds()<10 ? "0" + String.valueOf(dt.getSeconds()) : String.valueOf(dt.getSeconds()));
1022
1023      str += String.valueOf(dt.getYear()+1900)+"-"+sMonth+"-"+sDay+" "+sHour+":"+sMin+":"+sSec;
1024    }
1025
1026    return str;
1027  } // escape()
1028

1029  // ----------------------------------------------------------
1030

1031  /**
1032   * Format Timestamp in ODBC escape sequence style
1033   * @param ts Timestamp to be formated
1034   * @param sFormat Format Type "d" or "ts" or "shortTime".
1035   * Use d for { d 'yyyy-mm-dd' }, use ts for { ts 'ts=yyyy-mm-dd hh:nn:ss' }<br>
1036   * use shortTime for hh:mm<br>
1037   * use shortDate for yyyy-mm-dd<br>
1038   * use dateTime for yyyy-mm-dd hh:mm:ss<br>
1039   * @return Formated date
1040   * @since 3.0
1041   */

1042
1043  public static String JavaDoc escape(Timestamp JavaDoc ts, String JavaDoc sFormat) {
1044    return DBBind.escape(new java.util.Date JavaDoc(ts.getTime()), sFormat);
1045  }
1046
1047  // ----------------------------------------------------------
1048

1049  /**
1050   * <p>Get {@link DBTable} object by name</p>
1051   * @param sTable Table name
1052   * @return DBTable object or <b>null</b> if no table was found with given name.
1053   * @throws IllegalStateException DBTable objects are cached in a static HasMap,
1054   * the HashMap is loaded upon first call to a DBBind constructor. If getTable()
1055   * is called before creating any instance of DBBind an IllegalStateException
1056   * will be raised.
1057   * @deprecated Use {@link #getDBTable(String) getDBTable} instead
1058   */

1059
1060  public static DBTable getTable(String JavaDoc sTable) throws java.lang.IllegalStateException JavaDoc {
1061
1062    if (null==oGlobalTableMap)
1063      throw new IllegalStateException JavaDoc("DBBind global table map not initialized, call DBBind constructor first");
1064
1065    return (DBTable) oGlobalTableMap.get(sTable.toLowerCase());
1066  } // getTable
1067

1068  // ----------------------------------------------------------
1069

1070  /**
1071   * <p>Get {@link DBTable} object by name</p>
1072   * @param sTable Table name
1073   * @return DBTable object or <b>null</b> if no table was found with given name.
1074   * @throws IllegalStateException DBTable objects are cached in a static HasMap,
1075   * the HashMap is loaded upon first call to a DBBind constructor.
1076   * If getDBTable() is called before creating any instance of DBBind then an
1077   * IllegalStateException will be thrown.
1078   * @since 2.0
1079   */

1080
1081  public DBTable getDBTable(String JavaDoc sTable) throws IllegalStateException JavaDoc {
1082
1083    if (null==oTableMap)
1084      throw new IllegalStateException JavaDoc("DBBind internal table map not initialized, call DBBind constructor first");
1085
1086    return (DBTable) oTableMap.get(sTable.toLowerCase());
1087  } // getDBTable
1088

1089  /**
1090   * <p>Get map of {@link DBTable} objects</p>
1091   * @return HashMap
1092   * @throws IllegalStateException DBTable objects are cached in a static HasMap,
1093   * the HashMap is loaded upon first call to a DBBind constructor.
1094   * If getDBTablesMap() is called before creating any instance of DBBind
1095   * then an IllegalStateException will be thrown.
1096   * @since 3.0
1097   */

1098  public HashMap JavaDoc getDBTablesMap() throws IllegalStateException JavaDoc {
1099
1100    if (null==oTableMap)
1101      throw new IllegalStateException JavaDoc("DBBind internal table map not initialized, call DBBind constructor first");
1102
1103    return oTableMap;
1104  } // getDBTablesMap
1105

1106  // ----------------------------------------------------------
1107

1108  /**
1109   * <p>Get a {@link JDCConnection} instance from connection pool</p>
1110   * @param sCaller Symbolic name identifying the caller program or subroutine,
1111   * this field is used for statistical control of database accesses,
1112   * performance tunning and debugging open/close mismatch.
1113   * @return An open connection to the database.
1114   * @throws SQLException
1115   */

1116
1117  public synchronized JDCConnection getConnection(String JavaDoc sCaller) throws SQLException JavaDoc {
1118    JDCConnection oConn;
1119
1120    if (DebugFile.trace) {
1121      DebugFile.writeln("Begin DBBind.getConnection(" + sCaller + ")");
1122      DebugFile.incIdent();
1123
1124      if (null!=oConnectXcpt) {
1125        DebugFile.writeln("Previous exception " + oConnectXcpt.getMessage());
1126        DebugFile.decIdent();
1127      }
1128    }
1129
1130    if (null!=oConnectXcpt) {
1131      if (oConnectXcpt instanceof SQLException JavaDoc)
1132        throw (SQLException JavaDoc) oConnectXcpt;
1133      else
1134        throw new SQLException JavaDoc(oConnectXcpt.getClass().getName()+" "+oConnectXcpt.getMessage());
1135    }
1136
1137    if (null!=oConnPool) {
1138      oConn = oConnPool.getConnection(sCaller);
1139    }
1140    else {
1141      if (DebugFile.trace) DebugFile.writeln("ERROR: connection pool not set");
1142      oConn = null;
1143    }
1144
1145    if (DebugFile.trace) {
1146      DebugFile.decIdent();
1147      DebugFile.writeln("End DBBind.getConnection(" + sCaller + ") : " + (null==oConn ? "null" : "[Connection]") );
1148    }
1149
1150    return oConn;
1151  } // getConnection()
1152

1153  // ----------------------------------------------------------
1154
/**
1155   *
1156   * @return Get Current System Time
1157   */

1158
1159  public static long getTime() {
1160
1161    return System.currentTimeMillis();
1162  }
1163
1164  // ===========================================================================
1165

1166  /**
1167   * <p>Aliases for common SQL functions in different database dialects.</p>
1168   * @author Sergio Montoro Ten
1169   * @version 1.2
1170   */

1171
1172  public static class Functions {
1173
1174    /**
1175     * <p>ISNULL(value, default)</p>
1176     * Get value or default if value is null
1177     */

1178    public static String JavaDoc ISNULL;
1179
1180    /**
1181     * <p>String concatenation</p>
1182     * Str1 CONCAT Str2
1183     */

1184    public static String JavaDoc CONCAT;
1185
1186    /**
1187     * Get System Date
1188     */

1189    public static String JavaDoc GETDATE;
1190
1191    /**
1192     * <p>Transform String to lowercase</p>
1193     * LOWER(str)
1194     */

1195    public static String JavaDoc LOWER;
1196
1197
1198    /**
1199     * <p>Transform String to uppercase</p>
1200     * UPPER(str)
1201     */

1202    public static String JavaDoc UPPER;
1203
1204    /**
1205     * <p>Get string length</p>
1206     * LENGTH(str)
1207     */

1208    public static String JavaDoc LENGTH;
1209
1210
1211    /**
1212     * <p>Get character from ASCII code</p>
1213     * CHAR([0..255])
1214     */

1215    public static String JavaDoc CHR;
1216
1217    /**
1218     * <p>Case-insensitve LIKE operator (PostgreSQL only)</p>
1219     */

1220    public static String JavaDoc ILIKE;
1221
1222    public static int iDBMS;
1223
1224    // -------------------------------------------------------------------------
1225

1226    private static void setForDBMS(String JavaDoc sDBMSName) throws UnsupportedOperationException JavaDoc {
1227
1228      if (sDBMSName.equals("Microsoft SQL Server")) {
1229        iDBMS = JDCConnection.DBMS_MSSQL;
1230        ISNULL = "ISNULL";
1231        CONCAT = "+";
1232        GETDATE = "GETDATE()";
1233        LOWER = "LOWER";
1234        UPPER = "UPPER";
1235        LENGTH = "LEN";
1236        CHR = "CHAR";
1237        ILIKE = "LIKE";
1238
1239      } else if (sDBMSName.equals("Oracle")) {
1240        iDBMS = JDCConnection.DBMS_ORACLE;
1241        ISNULL = "NVL";
1242        CONCAT = "||";
1243        GETDATE = "SYSDATE";
1244        LOWER = "LOWER";
1245        UPPER = "UPPER";
1246        LENGTH = "LENGTH";
1247        CHR = "CHR";
1248        ILIKE = "LIKE";
1249
1250      } else if (sDBMSName.equals("PostgreSQL")) {
1251        iDBMS = JDCConnection.DBMS_POSTGRESQL;
1252        ISNULL = "COALESCE";
1253        CONCAT = "||";
1254        GETDATE = "current_timestamp";
1255        LOWER = "lower";
1256        UPPER = "upper";
1257        LENGTH = "char_length";
1258        CHR = "chr";
1259        ILIKE = "ILIKE";
1260
1261      } else if (sDBMSName.equals("MySQL")) {
1262        iDBMS = JDCConnection.DBMS_MYSQL;
1263        ISNULL = "COALESCE";
1264        CONCAT = null; // MySQL uses CONCAT() function instead of an operator
1265
GETDATE = "NOW()";
1266        LENGTH = "CHAR_LENGTH";
1267        CHR = "CHAR";
1268        LOWER = "LCASE";
1269        UPPER = "UCASE";
1270        ILIKE = "LIKE";
1271
1272      } else
1273        throw new UnsupportedOperationException JavaDoc("unsupported DBMS");
1274
1275    } // setForDBMS
1276

1277    // -------------------------------------------------------------------------
1278

1279    private static String JavaDoc escape(java.util.Date JavaDoc dt, String JavaDoc sFormat) throws UnsupportedOperationException JavaDoc {
1280      String JavaDoc str;
1281      String JavaDoc sMonth, sDay, sHour, sMin, sSec;
1282
1283      sMonth = (dt.getMonth()+1<10 ? "0" + String.valueOf((dt.getMonth()+1)) : String.valueOf(dt.getMonth()+1));
1284      sDay = (dt.getDate()<10 ? "0" + String.valueOf(dt.getDate()) : String.valueOf(dt.getDate()));
1285      sHour = (dt.getHours()<10 ? "0" + String.valueOf(dt.getHours()) : String.valueOf(dt.getHours()));
1286      sMin = (dt.getMinutes()<10 ? "0" + String.valueOf(dt.getMinutes()) : String.valueOf(dt.getMinutes()));
1287      sSec = (dt.getSeconds()<10 ? "0" + String.valueOf(dt.getSeconds()) : String.valueOf(dt.getSeconds()));
1288
1289      switch (iDBMS) {
1290
1291        case JDCConnection.DBMS_MSSQL:
1292          str = "{ " + sFormat.toLowerCase() + " '";
1293
1294          str += String.valueOf(dt.getYear()+1900) + "-" + sMonth + "-" + sDay + " ";
1295
1296          if (sFormat.equalsIgnoreCase("ts")) {
1297            str += sHour + ":" + sMin + ":" + sSec;
1298          }
1299
1300          str = str.trim() + "'}";
1301          break;
1302
1303        case JDCConnection.DBMS_ORACLE:
1304          if (sFormat.equalsIgnoreCase("ts"))
1305            str = "TO_DATE('" + String.valueOf(dt.getYear()+1900) + "-" + sMonth + "-" + sDay + " " + sHour + ":" + sMin + ":" + sSec + "','YYYY-MM-DD HH24-MI-SS')";
1306          else
1307            str = "TO_DATE('" + String.valueOf(dt.getYear()+1900) + "-" + sMonth + "-" + sDay + "','YYYY-MM-DD')";
1308          break;
1309
1310        case JDCConnection.DBMS_POSTGRESQL:
1311          if (sFormat.equalsIgnoreCase("ts"))
1312            str = "TIMESTAMP '" + String.valueOf(dt.getYear()+1900) + "-" + sMonth + "-" + sDay + " " + sHour + ":" + sMin + ":" + sSec + "'";
1313          else
1314            str = "DATE '" + String.valueOf(dt.getYear()+1900) + "-" + sMonth + "-" + sDay + "'";
1315          break;
1316
1317        case JDCConnection.DBMS_MYSQL:
1318          if (sFormat.equalsIgnoreCase("ts"))
1319            str = "CAST('" + String.valueOf(dt.getYear()+1900) + "-" + sMonth + "-" + sDay + " " + sHour + ":" + sMin + ":" + sSec + "' AS DATETIME)";
1320          else
1321            str = "TO_DATE('" + String.valueOf(dt.getYear()+1900) + "-" + sMonth + "-" + sDay + "' AS DATE)";
1322          break;
1323        default:
1324          throw new UnsupportedOperationException JavaDoc("DBBind.Functions.escape(Date,String) unsupported DBMS");
1325      } // end switch()
1326

1327      return str;
1328    } // escape()
1329

1330    // -------------------------------------------------------------------------
1331

1332    public static String JavaDoc toChar(Object JavaDoc oData, int iLength) throws UnsupportedOperationException JavaDoc {
1333      String JavaDoc sRetVal;
1334
1335      switch (iDBMS) {
1336        case JDCConnection.DBMS_ORACLE:
1337          sRetVal = "TO_CHAR(" + oData.toString() + ")";
1338          break;
1339        case JDCConnection.DBMS_MYSQL:
1340          sRetVal = "CAST(" + oData.toString() + " AS CHAR)";
1341          break;
1342        case JDCConnection.DBMS_POSTGRESQL:
1343        case JDCConnection.DBMS_MSSQL:
1344          sRetVal = "CAST(" + oData.toString() + " AS VARCHAR(" + String.valueOf(iLength) + "))";
1345          break;
1346        default:
1347          throw new UnsupportedOperationException JavaDoc("DBBind.Functions.toChar(Date,String) unsupported DBMS");
1348      }
1349
1350      return sRetVal;
1351    } // toChar()
1352

1353  } // Functions
1354

1355  // ===========================================================================
1356

1357  public static final int DBMS_GENERIC = 0;
1358  public static final int DBMS_MYSQL = 1;
1359  public static final int DBMS_POSTGRESQL = 2;
1360  public static final int DBMS_MSSQL = 3;
1361  public static final int DBMS_ORACLE = 5;
1362
1363  private static final int DBMS_UNKNOWN = -1;
1364  private static final int DBMS_SYBASE = 4;
1365  private static final int DBMS_B2 = 6;
1366  private static final int DBMS_INFORMIX = 7;
1367
1368  private static final String JavaDoc DBMSNAME_MSSQL = "Microsoft SQL Server";
1369  private static final String JavaDoc DBMSNAME_POSTGRESQL = "PostgreSQL";
1370  private static final String JavaDoc DBMSNAME_ORACLE = "Oracle";
1371  private static final String JavaDoc DBMSNAME_MYSQL = "MySQL";
1372
1373} // DBBind
1374
Popular Tags