1 32 33 package com.knowgate.dataobjs; 34 35 import java.security.AccessControlException ; 36 37 import java.lang.System ; 38 import java.util.HashMap ; 39 import java.util.Iterator ; 40 41 import java.sql.DriverManager ; 42 import java.sql.Connection ; 43 import java.sql.ResultSet ; 44 import java.sql.DatabaseMetaData ; 45 import java.sql.CallableStatement ; 46 import java.sql.Statement ; 47 import java.sql.SQLException ; 48 import java.sql.Timestamp ; 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 ; 57 58 63 64 public class DBBind extends Beans { 65 66 69 private JDCConnectionPool oConnPool; 70 private String sProfileName; 71 private String sDatabaseProductName; 72 private int iDatabaseProductId; 73 private Exception oConnectXcpt; 74 75 private static HashMap oGlobalTableMap; 76 77 private HashMap oTableMap; 78 79 private static final String VERSION = "3.0.0"; 80 81 84 93 public DBBind() { 94 95 oConnectXcpt = null; 99 100 try { 101 initialize("hipergate"); 102 } catch (Exception e) { 103 oConnectXcpt=e; 104 if (DebugFile.trace) DebugFile.writeln(e.getClass().getName() + " " + e.getMessage()); 105 } 106 } 107 108 116 public DBBind(String sProfile) { 117 118 oConnectXcpt = null; 119 120 try { 121 initialize(sProfile); 122 } 123 catch (AccessControlException e) { 124 oConnectXcpt=e; 125 if (DebugFile.trace) DebugFile.writeln("AccessControlException " + e.getMessage()); 126 } 127 catch (ClassNotFoundException e) { 128 oConnectXcpt=e; 129 if (DebugFile.trace) DebugFile.writeln("ClassNotFoundException " + e.getMessage()); 130 } 131 catch (SQLException e) { 132 oConnectXcpt=e; 133 if (DebugFile.trace) DebugFile.writeln("SQLException " + e.getMessage()); 134 } 135 catch (NullPointerException e) { 136 oConnectXcpt=e; 137 if (DebugFile.trace) DebugFile.writeln("NullPointerException " + e.getMessage()); 138 } 139 catch (UnsatisfiedLinkError e) { 140 oConnectXcpt = new Exception ("UnsatisfiedLinkError " + e.getMessage(), e); 141 if (DebugFile.trace) DebugFile.writeln("UnsatisfiedLinkError " + e.getMessage()); 142 } 143 catch (NumberFormatException e) { 144 oConnectXcpt = new Exception ("NumberFormatException " + e.getMessage(), e); 145 if (DebugFile.trace) DebugFile.writeln("NumberFormatException " + e.getMessage()); 146 } 147 } 148 149 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 180 185 public void restart() 186 throws SQLException , ClassNotFoundException { 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 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 } 217 219 223 public JDCConnectionPool connectionPool() { 224 return oConnPool; 225 } 226 227 229 private void loadDriver(String sProfile) 230 throws ClassNotFoundException , NullPointerException { 231 232 Class oDriver; 233 String 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 ("Could not find property driver at " + sProfile); 243 244 oDriver = Class.forName(sDriver); 245 246 if (DebugFile.trace) DebugFile.writeln("End DBBind.loadDriver()" ); 247 } 249 251 private static boolean in (String sStr, String [] 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 } 262 return bRetVal; 263 } 264 265 267 protected void initialize(String sProfile) 268 throws ClassNotFoundException , SQLException , NullPointerException , 269 AccessControlException ,UnsatisfiedLinkError ,NumberFormatException { 270 271 int i; 272 Connection oConn; 273 DatabaseMetaData oMData; 274 ResultSet oRSet; 275 String TableTypes[] = new String [1]; 276 DBTable oTable; 277 String sCatalog; 278 String sSchema; 279 String sTableName; 280 Iterator oTableIterator; 281 String [] aExclude; 282 283 oTableMap = new HashMap (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 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 308 try { 310 DriverManager.setLoginTimeout(Integer.parseInt(Environment.getProfileVar(sProfile, "logintimeout", "20"))); 311 } catch (Exception x) { 312 if (DebugFile.trace) DebugFile.writeln("DriverManager.setLoginTimeout() "+x.getClass().getName()+" "+x.getMessage()); 313 } 314 316 try { 317 oConn = DriverManager.getConnection(Environment.getProfileVar(sProfile, "dburl"), 318 Environment.getProfileVar(sProfile, "dbuser"), 319 Environment.getProfileVar(sProfile, "dbpassword")); 320 } 321 catch (SQLException e) { 322 if (DebugFile.trace) DebugFile.writeln("SQLException [" + e.getSQLState() + "]:" + String.valueOf(e.getErrorCode()) + " " + e.getMessage()); 323 oConnectXcpt = new SQLException (e.getMessage(), e.getSQLState(), e.getErrorCode()); 324 throw (SQLException ) 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 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 []{ "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 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 sqle) { 405 try { if (null!=oSchemas) oSchemas.close();} catch (Exception 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 } 432 } 433 else { 434 if (DBMS_POSTGRESQL==iDatabaseProductId) 435 aExclude = new String []{ "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 []{ "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 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 } } } } 482 else { 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 } } } } } 505 oRSet.close(); 506 507 oTableIterator = oTableMap.values().iterator(); 508 509 511 while (oTableIterator.hasNext()) { 512 oTable = (DBTable) oTableIterator.next(); 513 oTable.readColumns(oConn,oMData); 514 } 516 if (DebugFile.trace) DebugFile.writeln("Table scan : OK" ); 517 518 oConn.close(); 519 oConn=null; 520 521 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 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 (); 536 } 537 catch (NumberFormatException 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 ("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 (); 548 } 549 catch (NumberFormatException 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 ("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 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 ("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 ("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 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 ("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 ("connectiontimeout property at "+sProfile+".cnf must be greater than 1000 miliseconds"); 591 } 592 593 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 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 ("connectionreaperdelay property at "+sProfile+".cnf must be an integer value"); 613 } 614 catch (IllegalArgumentException 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 ("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 } 632 634 639 640 public String getDatabaseProductName() 641 throws SQLException { 642 643 if (null!=oConnectXcpt) throw (SQLException ) oConnectXcpt; 644 645 return sDatabaseProductName; 646 } 647 648 650 656 public String getProfileName() { 657 return sProfileName; 658 } 659 660 662 687 688 public static boolean exists(JDCConnection oConn, String sObjectName, String sObjectType) 689 throws SQLException , UnsupportedOperationException { 690 691 return oConn.exists(sObjectName, sObjectType); 692 693 } 695 697 703 public static String getDataModelVersion(JDCConnection oConn) throws SQLException { 704 String 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 oStmt = oConn.createStatement(); 713 ResultSet 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 } 728 730 736 public static int getDataModelVersionNumber(JDCConnection oConn) 737 throws SQLException { 738 739 String sVersion = getDataModelVersion(oConn); 740 741 if (null==sVersion) return 0; 742 743 final int iLen = sVersion.length(); 744 String 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 } 764 return Integer.parseInt(sMajor+Gadgets.leftPad(sMinor, '0', 2)+Gadgets.leftPad(sRevision, '0', 2)); 765 } 767 769 782 783 public static int currVal(JDCConnection oConn, String sSequenceName) 784 throws SQLException , UnsupportedOperationException { 785 786 Statement oStmt; 787 ResultSet oRSet; 788 CallableStatement 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 ("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 } 858 860 873 874 public static int currVal(Connection oSQLConn, String sSequenceName) 875 throws SQLException , UnsupportedOperationException { 876 return currVal(new JDCConnection(oSQLConn, null), sSequenceName); 877 } 878 879 881 894 public static int nextVal(JDCConnection oConn, String sSequenceName) 895 throws SQLException , UnsupportedOperationException { 896 897 Statement oStmt; 898 ResultSet oRSet; 899 CallableStatement 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 ("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 } 963 965 977 978 public static int nextVal(Connection oSQLConn, String sSequenceName) 979 throws SQLException , UnsupportedOperationException { 980 981 return nextVal(new JDCConnection(oSQLConn, null), sSequenceName); 982 } 983 984 986 997 998 public static String escape(java.util.Date dt, String sFormat) 999 throws IllegalArgumentException { 1000 String str = ""; 1001 String 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 } 1029 1031 1042 1043 public static String escape(Timestamp ts, String sFormat) { 1044 return DBBind.escape(new java.util.Date (ts.getTime()), sFormat); 1045 } 1046 1047 1049 1059 1060 public static DBTable getTable(String sTable) throws java.lang.IllegalStateException { 1061 1062 if (null==oGlobalTableMap) 1063 throw new IllegalStateException ("DBBind global table map not initialized, call DBBind constructor first"); 1064 1065 return (DBTable) oGlobalTableMap.get(sTable.toLowerCase()); 1066 } 1068 1070 1080 1081 public DBTable getDBTable(String sTable) throws IllegalStateException { 1082 1083 if (null==oTableMap) 1084 throw new IllegalStateException ("DBBind internal table map not initialized, call DBBind constructor first"); 1085 1086 return (DBTable) oTableMap.get(sTable.toLowerCase()); 1087 } 1089 1098 public HashMap getDBTablesMap() throws IllegalStateException { 1099 1100 if (null==oTableMap) 1101 throw new IllegalStateException ("DBBind internal table map not initialized, call DBBind constructor first"); 1102 1103 return oTableMap; 1104 } 1106 1108 1116 1117 public synchronized JDCConnection getConnection(String sCaller) throws SQLException { 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 ) 1132 throw (SQLException ) oConnectXcpt; 1133 else 1134 throw new SQLException (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 } 1153 1158 1159 public static long getTime() { 1160 1161 return System.currentTimeMillis(); 1162 } 1163 1164 1166 1171 1172 public static class Functions { 1173 1174 1178 public static String ISNULL; 1179 1180 1184 public static String CONCAT; 1185 1186 1189 public static String GETDATE; 1190 1191 1195 public static String LOWER; 1196 1197 1198 1202 public static String UPPER; 1203 1204 1208 public static String LENGTH; 1209 1210 1211 1215 public static String CHR; 1216 1217 1220 public static String ILIKE; 1221 1222 public static int iDBMS; 1223 1224 1226 private static void setForDBMS(String sDBMSName) throws UnsupportedOperationException { 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; 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 ("unsupported DBMS"); 1274 1275 } 1277 1279 private static String escape(java.util.Date dt, String sFormat) throws UnsupportedOperationException { 1280 String str; 1281 String 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 ("DBBind.Functions.escape(Date,String) unsupported DBMS"); 1325 } 1327 return str; 1328 } 1330 1332 public static String toChar(Object oData, int iLength) throws UnsupportedOperationException { 1333 String 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 ("DBBind.Functions.toChar(Date,String) unsupported DBMS"); 1348 } 1349 1350 return sRetVal; 1351 } 1353 } 1355 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 DBMSNAME_MSSQL = "Microsoft SQL Server"; 1369 private static final String DBMSNAME_POSTGRESQL = "PostgreSQL"; 1370 private static final String DBMSNAME_ORACLE = "Oracle"; 1371 private static final String DBMSNAME_MYSQL = "MySQL"; 1372 1373} | Popular Tags |