1 package wrappers; 2 3 import java.math.BigDecimal; 4 import java.sql.Connection; 5 import java.sql.DatabaseMetaData; 6 import java.sql.Date; 7 import java.sql.Driver; 8 import java.sql.DriverManager; 9 import java.sql.PreparedStatement; 10 import java.sql.ResultSet; 11 import java.sql.ResultSetMetaData; 12 import java.sql.SQLException; 13 import java.sql.Statement; 14 import java.sql.Time; 15 import java.sql.Timestamp; 16 import java.util.Collections; 17 import java.util.Hashtable; 18 import java.util.List; 19 import java.util.UUID; 20 21 import javax.sql.DataSource; 22 23 public class JdbcInterface { 24 // This is used by DS classes 25 static Hashtable<String,DataSource> dst = null; 26 27 boolean DEBUG = false; 28 boolean CatisSchema = false; 29 String Errmsg = "No error"; 30 Connection conn = null; 31 DatabaseMetaData dbmd = null; 32 Statement stmt = null; 33 PreparedStatement pstmt = null; 34 ResultSet rs = null; 35 ResultSetMetaData rsmd = null; 36 37 // === Constructors/finalize ========================================= JdbcInterface()38 public JdbcInterface() { 39 this(false); 40 } // end of default constructor 41 JdbcInterface(boolean b)42 public JdbcInterface(boolean b) { 43 DEBUG = b; 44 } // end of constructor 45 SetErrmsg(Exception e)46 protected void SetErrmsg(Exception e) { 47 if (DEBUG) 48 System.out.println(e.getMessage()); 49 50 Errmsg = e.toString(); 51 } // end of SetErrmsg 52 GetErrmsg()53 public String GetErrmsg() { 54 String err = Errmsg; 55 56 Errmsg = "No error"; 57 return err; 58 } // end of GetErrmsg 59 CheckURL(String url, String vendor)60 protected void CheckURL(String url, String vendor) throws Exception { 61 if (url == null) 62 throw new Exception("URL cannot be null"); 63 64 String[] tk = url.split(":", 3); 65 66 if (!tk[0].equals("jdbc") || tk[1] == null) 67 throw new Exception("Invalid URL"); 68 69 if (vendor != null && !tk[1].equals(vendor)) 70 throw new Exception("Wrong URL for this wrapper"); 71 72 // Some drivers use Catalog as Schema 73 CatisSchema = tk[1].equals("mysql") || tk[1].equals("mariadb"); 74 } // end of CatalogIsSchema 75 JdbcConnect(String[] parms, int fsize, boolean scrollable)76 public int JdbcConnect(String[] parms, int fsize, boolean scrollable) { 77 int rc = 0; 78 79 if (DEBUG) 80 System.out.println("In JdbcInterface: driver=" + parms[0]); 81 82 try { 83 if (DEBUG) 84 System.out.println("In try block"); 85 86 if (parms[0] != null && !parms[0].isEmpty()) { 87 if (DEBUG) 88 System.out.println("Loading class" + parms[0]); 89 90 Class.forName(parms[0]); //loads the driver 91 } // endif driver 92 93 if (DEBUG) 94 System.out.println("URL=" + parms[1]); 95 96 CheckURL(parms[1], null); 97 98 // This is required for drivers using context class loaders 99 Thread.currentThread().setContextClassLoader(getClass().getClassLoader()); 100 101 if (parms[2] != null && !parms[2].isEmpty()) { 102 if (DEBUG) 103 System.out.println("user=" + parms[2] + " pwd=" + parms[3]); 104 105 conn = DriverManager.getConnection(parms[1], parms[2], parms[3]); 106 } else 107 conn = DriverManager.getConnection(parms[1]); 108 109 if (DEBUG) 110 System.out.println("Connection " + conn.toString() + " established"); 111 112 // Get the data base meta data object 113 dbmd = conn.getMetaData(); 114 115 // Get a statement from the connection 116 stmt = GetStmt(fsize, scrollable); 117 } catch(ClassNotFoundException e) { 118 SetErrmsg(e); 119 rc = -1; 120 } catch (SQLException se) { 121 SetErrmsg(se); 122 rc = -2; 123 } catch( Exception e ) { 124 SetErrmsg(e); 125 rc = -3; 126 } // end try/catch 127 128 return rc; 129 } // end of JdbcConnect 130 GetStmt(int fsize, boolean scrollable)131 protected Statement GetStmt(int fsize, boolean scrollable) throws SQLException, Exception { 132 Statement stmt = null; 133 134 if (scrollable) 135 stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY); 136 else 137 stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); 138 139 if (DEBUG) 140 System.out.println("Statement type = " + stmt.getResultSetType() 141 + " concurrency = " + stmt.getResultSetConcurrency()); 142 143 if (DEBUG) // Get the fetch size of a statement 144 System.out.println("Default fetch size = " + stmt.getFetchSize()); 145 146 if (fsize != 0) { 147 // Set the fetch size 148 stmt.setFetchSize(fsize); 149 150 if (DEBUG) 151 System.out.println("New fetch size = " + stmt.getFetchSize()); 152 153 } // endif fsize 154 155 return stmt; 156 } // end of GetStmt 157 158 CreatePrepStmt(String sql)159 public int CreatePrepStmt(String sql) { 160 int rc = 0; 161 162 try { 163 pstmt = conn.prepareStatement(sql); 164 } catch (SQLException se) { 165 SetErrmsg(se); 166 rc = -1; 167 } catch (Exception e) { 168 SetErrmsg(e); 169 rc = -2; 170 } // end try/catch 171 172 return rc; 173 } // end of CreatePrepStmt 174 SetStringParm(int i, String s)175 public void SetStringParm(int i, String s) { 176 try { 177 pstmt.setString(i, s); 178 } catch (Exception e) { 179 SetErrmsg(e); 180 } // end try/catch 181 182 } // end of SetStringParm 183 SetIntParm(int i, int n)184 public void SetIntParm(int i, int n) { 185 try { 186 pstmt.setInt(i, n); 187 } catch (Exception e) { 188 SetErrmsg(e); 189 } // end try/catch 190 191 } // end of SetIntParm 192 SetShortParm(int i, short n)193 public void SetShortParm(int i, short n) { 194 try { 195 pstmt.setShort(i, n); 196 } catch (Exception e) { 197 SetErrmsg(e); 198 } // end try/catch 199 200 } // end of SetShortParm 201 SetBigintParm(int i, long n)202 public void SetBigintParm(int i, long n) { 203 try { 204 pstmt.setLong(i, n); 205 } catch (Exception e) { 206 SetErrmsg(e); 207 } // end try/catch 208 209 } // end of SetBigintParm 210 SetFloatParm(int i, float f)211 public void SetFloatParm(int i, float f) { 212 try { 213 pstmt.setFloat(i, f); 214 } catch (Exception e) { 215 SetErrmsg(e); 216 } // end try/catch 217 218 } // end of SetFloatParm 219 SetDoubleParm(int i, double d)220 public void SetDoubleParm(int i, double d) { 221 try { 222 pstmt.setDouble(i, d); 223 } catch (Exception e) { 224 SetErrmsg(e); 225 } // end try/catch 226 227 } // end of SetDoubleParm 228 SetTimestampParm(int i, Timestamp t)229 public void SetTimestampParm(int i, Timestamp t) { 230 try { 231 pstmt.setTimestamp(i, t); 232 } catch (Exception e) { 233 SetErrmsg(e); 234 } // end try/catch 235 236 } // end of SetTimestampParm 237 SetUuidParm(int i, String s)238 public void SetUuidParm(int i, String s) { 239 try { 240 UUID uuid; 241 242 if (s == null) 243 uuid = null; 244 else if (s.isEmpty()) 245 uuid = UUID.randomUUID(); 246 else 247 uuid = UUID.fromString(s); 248 249 pstmt.setObject(i, uuid); 250 } catch (Exception e) { 251 SetErrmsg(e); 252 } // end try/catch 253 254 } // end of SetUuidParm 255 SetNullParm(int i, int typ)256 public int SetNullParm(int i, int typ) { 257 int rc = 0; 258 259 try { 260 pstmt.setNull(i, typ); 261 } catch (Exception e) { 262 SetErrmsg(e); 263 rc = -1; 264 } // end try/catch 265 266 return rc; 267 } // end of SetNullParm 268 ExecutePrep()269 public int ExecutePrep() { 270 int n = -3; 271 272 if (pstmt != null) try { 273 n = pstmt.executeUpdate(); 274 } catch (SQLException se) { 275 SetErrmsg(se); 276 n = -1; 277 } catch (Exception e) { 278 SetErrmsg(e); 279 n = -2; 280 } //end try/catch 281 282 return n; 283 } // end of ExecutePrep 284 ClosePrepStmt()285 public boolean ClosePrepStmt() { 286 boolean b = false; 287 288 if (pstmt != null) try { 289 pstmt.close(); 290 pstmt = null; 291 } catch (SQLException se) { 292 SetErrmsg(se); 293 b = true; 294 } catch (Exception e) { 295 SetErrmsg(e); 296 b = true; 297 } // end try/catch 298 299 return b; 300 } // end of ClosePrepStmt 301 JdbcDisconnect()302 public int JdbcDisconnect() { 303 int rc = 0; 304 305 // Cancel pending statement 306 if (stmt != null) 307 try { 308 if (DEBUG) 309 System.out.println("Cancelling statement"); 310 311 stmt.cancel(); 312 } catch(SQLException se) { 313 SetErrmsg(se); 314 rc += 1; 315 } // nothing more we can do 316 317 // Close the statement and the connection 318 if (rs != null) 319 try { 320 if (DEBUG) 321 System.out.println("Closing result set"); 322 323 rs.close(); 324 } catch(SQLException se) { 325 SetErrmsg(se); 326 rc = 2; 327 } // nothing more we can do 328 329 if (stmt != null) 330 try { 331 if (DEBUG) 332 System.out.println("Closing statement"); 333 334 stmt.close(); 335 } catch(SQLException se) { 336 SetErrmsg(se); 337 rc += 4; 338 } // nothing more we can do 339 340 ClosePrepStmt(); 341 342 if (conn != null) 343 try { 344 if (DEBUG) 345 System.out.println("Closing connection"); 346 347 conn.close(); 348 } catch (SQLException se) { 349 SetErrmsg(se); 350 rc += 8; 351 } //end try/catch 352 353 if (DEBUG) 354 System.out.println("All closed"); 355 356 return rc; 357 } // end of JdbcDisconnect 358 GetMaxValue(int n)359 public int GetMaxValue(int n) { 360 int m = 0; 361 362 try { 363 switch (n) { 364 case 1: // Max columns in table 365 m = dbmd.getMaxColumnsInTable(); 366 break; 367 case 2: // Max catalog name length 368 m = dbmd.getMaxCatalogNameLength(); 369 break; 370 case 3: // Max schema name length 371 m = dbmd.getMaxSchemaNameLength(); 372 break; 373 case 4: // Max table name length 374 m = dbmd.getMaxTableNameLength(); 375 break; 376 case 5: // Max column name length 377 m = dbmd.getMaxColumnNameLength(); 378 break; 379 } // endswitch n 380 381 } catch(Exception e) { 382 SetErrmsg(e); 383 m = -1; 384 } // end try/catch 385 386 return m; 387 } // end of GetMaxValue 388 GetQuoteString()389 public String GetQuoteString() { 390 String qs = null; 391 392 try { 393 qs = dbmd.getIdentifierQuoteString(); 394 } catch(SQLException se) { 395 SetErrmsg(se); 396 } // end try/catch 397 398 return qs; 399 } // end of GetQuoteString 400 GetColumns(String[] parms)401 public int GetColumns(String[] parms) { 402 int ncol = -1; 403 404 try { 405 if (rs != null) rs.close(); 406 407 if (CatisSchema) 408 rs = dbmd.getColumns(parms[1], null, parms[2], parms[3]); 409 else 410 rs = dbmd.getColumns(parms[0], parms[1], parms[2], parms[3]); 411 412 if (rs != null) { 413 rsmd = rs.getMetaData(); 414 ncol = rsmd.getColumnCount(); 415 } // endif rs 416 417 } catch(SQLException se) { 418 SetErrmsg(se); 419 } // end try/catch 420 421 return ncol; 422 } // end of GetColumns 423 GetTables(String[] parms)424 public int GetTables(String[] parms) { 425 int ncol = -1; 426 String[] typ = null; 427 428 if (parms[3] != null) { 429 typ = new String[1]; 430 typ[0] = parms[3]; 431 } // endif parms 432 433 try { 434 if (rs != null) rs.close(); 435 436 if (CatisSchema) 437 rs = dbmd.getTables(parms[1], null, parms[2], typ); 438 else 439 rs = dbmd.getTables(parms[0], parms[1], parms[2], typ); 440 441 if (rs != null) { 442 rsmd = rs.getMetaData(); 443 ncol = rsmd.getColumnCount(); 444 } // endif rs 445 446 } catch(SQLException se) { 447 SetErrmsg(se); 448 } // end try/catch 449 450 return ncol; 451 } // end of GetColumns 452 Execute(String query)453 public int Execute(String query) { 454 int n = 0; 455 456 if (DEBUG) 457 System.out.println("Executing '" + query + "'"); 458 459 try { 460 boolean b = stmt.execute(query); 461 462 if (b == false) { 463 n = stmt.getUpdateCount(); 464 if (rs != null) rs.close(); 465 } // endif b 466 467 if (DEBUG) 468 System.out.println("Query '" + query + "' executed: n = " + n); 469 470 } catch (SQLException se) { 471 SetErrmsg(se); 472 n = -1; 473 } catch (Exception e) { 474 SetErrmsg(e); 475 n = -2; 476 } //end try/catch 477 478 return n; 479 } // end of Execute 480 GetResult()481 public int GetResult() { 482 int ncol = 0; 483 484 try { 485 rs = stmt.getResultSet(); 486 487 if (rs != null) { 488 rsmd = rs.getMetaData(); 489 ncol = rsmd.getColumnCount(); 490 491 if (DEBUG) 492 System.out.println("Result set has " + rsmd.getColumnCount() + " column(s)"); 493 494 } // endif rs 495 496 } catch (SQLException se) { 497 SetErrmsg(se); 498 ncol = -1; 499 } catch (Exception e) { 500 SetErrmsg(e); 501 ncol = -2; 502 } //end try/catch 503 504 return ncol; 505 } // end of GetResult 506 ExecuteQuery(String query)507 public int ExecuteQuery(String query) { 508 int ncol = 0; 509 510 if (DEBUG) 511 System.out.println("Executing query '" + query + "'"); 512 513 try { 514 if (rs != null) 515 rs.close(); 516 rs = stmt.executeQuery(query); 517 rsmd = rs.getMetaData(); 518 ncol = rsmd.getColumnCount(); 519 520 if (DEBUG) { 521 System.out.println("Query '" + query + "' executed successfully"); 522 System.out.println("Result set has " + rsmd.getColumnCount() + " column(s)"); 523 } // endif DEBUG 524 525 } catch (SQLException se) { 526 SetErrmsg(se); 527 ncol = -1; 528 } catch (Exception e) { 529 SetErrmsg(e); 530 ncol = -2; 531 } //end try/catch 532 533 return ncol; 534 } // end of ExecuteQuery 535 ExecuteUpdate(String query)536 public int ExecuteUpdate(String query) { 537 int n = 0; 538 539 if (DEBUG) 540 System.out.println("Executing update query '" + query + "'"); 541 542 try { 543 n = stmt.executeUpdate(query); 544 545 if (DEBUG) 546 System.out.println("Update Query '" + query + "' executed: n = " + n); 547 548 } catch (SQLException se) { 549 SetErrmsg(se); 550 n = -1; 551 } catch (Exception e) { 552 SetErrmsg(e); 553 n = -2; 554 } //end try/catch 555 556 return n; 557 } // end of ExecuteUpdate 558 ReadNext()559 public int ReadNext() { 560 if (rs != null) { 561 try { 562 return rs.next() ? 1 : 0; 563 } catch (SQLException se) { 564 SetErrmsg(se); 565 return -1; 566 } //end try/catch 567 568 } else 569 return 0; 570 571 } // end of ReadNext 572 Fetch(int row)573 public boolean Fetch(int row) { 574 if (rs != null) { 575 try { 576 return rs.absolute(row); 577 } catch (SQLException se) { 578 SetErrmsg(se); 579 return false; 580 } //end try/catch 581 582 } else 583 return false; 584 585 } // end of Fetch 586 ColumnName(int n)587 public String ColumnName(int n) { 588 if (rsmd == null) { 589 System.out.println("No result metadata"); 590 } else try { 591 return rsmd.getColumnLabel(n); 592 } catch (SQLException se) { 593 SetErrmsg(se); 594 } //end try/catch 595 596 return null; 597 } // end of ColumnName 598 ColumnType(int n, String name)599 public int ColumnType(int n, String name) { 600 if (rsmd == null) { 601 System.out.println("No result metadata"); 602 } else try { 603 if (n == 0) 604 n = rs.findColumn(name); 605 606 return rsmd.getColumnType(n); 607 } catch (SQLException se) { 608 SetErrmsg(se); 609 } //end try/catch 610 611 return 666; // Not a type 612 } // end of ColumnType 613 ColumnDesc(int n, int[] val)614 public String ColumnDesc(int n, int[] val) { 615 if (rsmd == null) { 616 System.out.println("No result metadata"); 617 return null; 618 } else try { 619 val[0] = rsmd.getColumnType(n); 620 val[1] = rsmd.getPrecision(n); 621 val[2] = rsmd.getScale(n); 622 val[3] = rsmd.isNullable(n); 623 return rsmd.getColumnLabel(n); 624 } catch (SQLException se) { 625 SetErrmsg(se); 626 } //end try/catch 627 628 return null; 629 } // end of ColumnDesc 630 StringField(int n, String name)631 public String StringField(int n, String name) { 632 if (rs == null) { 633 System.out.println("No result set"); 634 } else try { 635 return (n > 0) ? rs.getString(n) : rs.getString(name); 636 } catch (SQLException se) { 637 SetErrmsg(se); 638 } //end try/catch 639 640 return null; 641 } // end of StringField 642 IntField(int n, String name)643 public int IntField(int n, String name) { 644 if (rs == null) { 645 System.out.println("No result set"); 646 } else try { 647 return (n > 0) ? rs.getInt(n) : rs.getInt(name); 648 } catch (SQLException se) { 649 SetErrmsg(se); 650 } //end try/catch 651 652 return 0; 653 } // end of IntField 654 BigintField(int n, String name)655 public long BigintField(int n, String name) { 656 if (rs == null) { 657 System.out.println("No result set"); 658 } else try { 659 BigDecimal bigDecimal = (n > 0) ? rs.getBigDecimal(n) : rs.getBigDecimal(name); 660 return bigDecimal != null ? bigDecimal.longValue() : 0; 661 } catch (SQLException se) { 662 SetErrmsg(se); 663 } //end try/catch 664 665 return 0; 666 } // end of BiginttField 667 DoubleField(int n, String name)668 public double DoubleField(int n, String name) { 669 if (rs == null) { 670 System.out.println("No result set"); 671 } else try { 672 return (n > 0) ? rs.getDouble(n) : rs.getDouble(name); 673 } catch (SQLException se) { 674 SetErrmsg(se); 675 } //end try/catch 676 677 return 0.; 678 } // end of DoubleField 679 FloatField(int n, String name)680 public float FloatField(int n, String name) { 681 if (rs == null) { 682 System.out.println("No result set"); 683 } else try { 684 return (n > 0) ? rs.getFloat(n) : rs.getFloat(name); 685 } catch (SQLException se) { 686 SetErrmsg(se); 687 } //end try/catch 688 689 return 0; 690 } // end of FloatField 691 BooleanField(int n, String name)692 public boolean BooleanField(int n, String name) { 693 if (rs == null) { 694 System.out.println("No result set"); 695 } else try { 696 return (n > 0) ? rs.getBoolean(n) : rs.getBoolean(name); 697 } catch (SQLException se) { 698 SetErrmsg(se); 699 } //end try/catch 700 701 return false; 702 } // end of BooleanField 703 DateField(int n, String name)704 public int DateField(int n, String name) { 705 if (rs == null) { 706 System.out.println("No result set"); 707 } else try { 708 Date d = (n > 0) ? rs.getDate(n) : rs.getDate(name); 709 return (d != null) ? (int)(d.getTime() / 1000) : 0; 710 } catch (SQLException se) { 711 SetErrmsg(se); 712 } //end try/catch 713 714 return 0; 715 } // end of DateField 716 TimeField(int n, String name)717 public int TimeField(int n, String name) { 718 if (rs == null) { 719 System.out.println("No result set"); 720 } else try { 721 Time t = (n > 0) ? rs.getTime(n) : rs.getTime(name); 722 return (t != null) ? (int)(t.getTime() / 1000) : 0; 723 } catch (SQLException se) { 724 SetErrmsg(se); 725 } //end try/catch 726 727 return 0; 728 } // end of TimeField 729 TimestampField(int n, String name)730 public int TimestampField(int n, String name) { 731 if (rs == null) { 732 System.out.println("No result set"); 733 } else try { 734 Timestamp ts = (n > 0) ? rs.getTimestamp(n) : rs.getTimestamp(name); 735 return (ts != null) ? (int)(ts.getTime() / 1000) : 0; 736 } catch (SQLException se) { 737 SetErrmsg(se); 738 } //end try/catch 739 740 return 0; 741 } // end of TimestampField 742 ObjectField(int n, String name)743 public Object ObjectField(int n, String name) { 744 if (rs == null) { 745 System.out.println("No result set"); 746 } else try { 747 return (n > 0) ? rs.getObject(n) : rs.getObject(name); 748 } catch (SQLException se) { 749 SetErrmsg(se); 750 } //end try/catch 751 752 return null; 753 } // end of ObjectField 754 UuidField(int n, String name)755 public String UuidField(int n, String name) { 756 Object job; 757 758 if (rs == null) { 759 System.out.println("No result set"); 760 } else 761 try { 762 job = (n > 0) ? rs.getObject(n) : rs.getObject(name); 763 return job.toString(); 764 } catch (SQLException se) { 765 SetErrmsg(se); 766 } // end try/catch 767 768 return null; 769 } // end of UuidField 770 GetDrivers(String[] s, int mxs)771 public int GetDrivers(String[] s, int mxs) { 772 int n = 0; 773 List<Driver> drivers = Collections.list(DriverManager.getDrivers()); 774 int size = Math.min(mxs, drivers.size()); 775 776 for (int i = 0; i < size; i++) { 777 Driver driver = (Driver)drivers.get(i); 778 779 // Get name of driver 780 s[n++] = driver.getClass().getName(); 781 782 // Get version info 783 s[n++] = driver.getMajorVersion() + "." + driver.getMinorVersion(); 784 s[n++] = driver.jdbcCompliant() ? "Yes" : "No"; 785 s[n++] = driver.toString(); 786 } // endfor i 787 788 return size; 789 } // end of GetDrivers 790 791 /** 792 * Adds the specified path to the java library path 793 * from Fahd Shariff blog 794 * 795 * @param pathToAdd the path to add 796 static public int addLibraryPath(String pathToAdd) { 797 System.out.println("jpath = " + pathToAdd); 798 799 try { 800 Field usrPathsField = ClassLoader.class.getDeclaredField("usr_paths"); 801 usrPathsField.setAccessible(true); 802 803 //get array of paths 804 String[] paths = (String[])usrPathsField.get(null); 805 806 //check if the path to add is already present 807 for (String path : paths) { 808 System.out.println("path = " + path); 809 810 if (path.equals(pathToAdd)) 811 return -5; 812 813 } // endfor path 814 815 //add the new path 816 String[] newPaths = Arrays.copyOf(paths, paths.length + 1); 817 newPaths[paths.length] = pathToAdd; 818 usrPathsField.set(null, newPaths); 819 System.setProperty("java.library.path", 820 System.getProperty("java.library.path") + File.pathSeparator + pathToAdd); 821 Field fieldSysPath = ClassLoader.class.getDeclaredField("sys_paths"); 822 fieldSysPath.setAccessible(true); 823 fieldSysPath.set(null, null); 824 } catch (Exception e) { 825 SetErrmsg(e); 826 return -1; 827 } // end try/catch 828 829 return 0; 830 } // end of addLibraryPath 831 */ 832 833 } // end of class JdbcInterface 834 835