1 /************************************************************************* 2 * 3 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. 4 * 5 * Copyright 2000, 2010 Oracle and/or its affiliates. 6 * 7 * OpenOffice.org - a multi-platform office productivity suite 8 * 9 * This file is part of OpenOffice.org. 10 * 11 * OpenOffice.org is free software: you can redistribute it and/or modify 12 * it under the terms of the GNU Lesser General Public License version 3 13 * only, as published by the Free Software Foundation. 14 * 15 * OpenOffice.org is distributed in the hope that it will be useful, 16 * but WITHOUT ANY WARRANTY; without even the implied warranty of 17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18 * GNU Lesser General Public License version 3 for more details 19 * (a copy is included in the LICENSE file that accompanied this code). 20 * 21 * You should have received a copy of the GNU Lesser General Public License 22 * version 3 along with OpenOffice.org. If not, see 23 * <http://www.openoffice.org/license.html> 24 * for a copy of the LGPLv3 License. 25 * 26 ************************************************************************/ 27 28 package util; 29 30 import com.sun.star.uno.Exception; 31 import java.io.PrintWriter ; 32 33 // access the implementations via names 34 import com.sun.star.uno.XInterface; 35 import com.sun.star.lang.XMultiServiceFactory; 36 import com.sun.star.uno.UnoRuntime; 37 38 import com.sun.star.beans.PropertyValue; 39 import com.sun.star.beans.XPropertySet; 40 import com.sun.star.sdbc.XConnection ; 41 import com.sun.star.sdbc.XResultSet ; 42 import com.sun.star.sdbc.XResultSetUpdate ; 43 import com.sun.star.sdbc.XStatement ; 44 import com.sun.star.sdbc.XRowUpdate ; 45 import com.sun.star.util.Date ; 46 import com.sun.star.uno.XNamingService ; 47 import com.sun.star.task.XInteractionHandler ; 48 import com.sun.star.sdb.XCompletedConnection ; 49 import com.sun.star.container.XEnumeration ; 50 import com.sun.star.container.XEnumerationAccess ; 51 import com.sun.star.io.XInputStream ; 52 import com.sun.star.io.XTextInputStream ; 53 import com.sun.star.io.XDataInputStream ; 54 import com.sun.star.container.XNameAccess ; 55 import com.sun.star.frame.XStorable; 56 import com.sun.star.sdb.XDocumentDataSource; 57 import com.sun.star.sdbc.XCloseable ; 58 import java.sql.Statement; 59 import java.sql.Connection; 60 import java.sql.DriverManager; 61 62 /** 63 * Provides useful methods for working with SOffice databases. 64 * Database creation, data transfering, outputting infromation. 65 */ 66 public class DBTools { 67 68 private XMultiServiceFactory xMSF = null ; 69 private XNamingService dbContext = null ; 70 private PrintWriter m_log = null; 71 72 //JDBC driver 73 public final static String TST_JDBC_DRIVER = "org.gjt.mm.mysql.Driver"; 74 75 // constants for TestDB table column indexes 76 public final static int TST_STRING = 1 ; 77 public final static int TST_INT = 2 ; 78 public final static int TST_DOUBLE = 5 ; 79 public final static int TST_DATE = 6 ; 80 public final static int TST_BOOLEAN = 10 ; 81 public final static int TST_CHARACTER_STREAM = 11 ; 82 public final static int TST_BINARY_STREAM = 12 ; 83 84 // constants for TestDB columns names 85 public final static String TST_STRING_F = "_TEXT" ; 86 public final static String TST_INT_F = "_INT" ; 87 public final static String TST_DOUBLE_F = "_DOUBLE" ; 88 public final static String TST_DATE_F = "_DATE" ; 89 public final static String TST_BOOLEAN_F = "_BOOL" ; 90 public final static String TST_CHARACTER_STREAM_F = "_MEMO1" ; 91 public final static String TST_BINARY_STREAM_F = "_MEMO2" ; 92 93 /** 94 * Values for filling test table. 95 */ 96 public final static Object[][] TST_TABLE_VALUES = new Object[][] { 97 {"String1", new Integer(1), null, null, new Double(1.1), 98 new Date((short) 1,(short) 1, (short) 2001), null, null, null, 99 Boolean.TRUE, null, null}, 100 {"String2", new Integer(2), null, null, new Double(1.2), 101 new Date((short) 2, (short) 1,(short) 2001), null, null, null, 102 Boolean.FALSE, null, null}, 103 {null, null, null, null, null, 104 null, null, null, null, 105 null, null, null} 106 } ; 107 108 /** 109 * Array of lengths of streams for each row in of the 110 * <code>TST_TABLE_VALUES</code> constants. 111 */ 112 public final static int[] TST_STREAM_LENGTHS = {0, 0, 0} ; 113 114 /** 115 * It's just a structure with some useful methods for representing 116 * <code>com.sun.star.sdb.DataSource</code> service. All this 117 * service's properties are stored in appropriate class fields. 118 * Class also allows to construct its instances using service 119 * information, and create new service instance upon class 120 * fields. 121 * @see com.sun.star.sdb.DataSource 122 */ 123 public class DataSourceInfo { 124 /** 125 * Representation of <code>'Name'</code> property. 126 */ 127 public String Name = null ; 128 /** 129 * Representation of <code>'URL'</code> property. 130 */ 131 public String URL = null ; 132 /** 133 * Representation of <code>'Info'</code> property. 134 */ 135 public PropertyValue[] Info = null ; 136 /** 137 * Representation of <code>'User'</code> property. 138 */ 139 public String User = null ; 140 /** 141 * Representation of <code>'Password'</code> property. 142 */ 143 public String Password = null ; 144 /** 145 * Representation of <code>'IsPasswordRequired'</code> property. 146 */ 147 public Boolean IsPasswordRequired = null ; 148 /** 149 * Representation of <code>'SuppressVersionColumns'</code> property. 150 */ 151 public Boolean SuppressVersionColumns = null ; 152 /** 153 * Representation of <code>'IsReadOnly'</code> property. 154 */ 155 public Boolean IsReadOnly = null ; 156 /** 157 * Representation of <code>'TableFilter'</code> property. 158 */ 159 public String[] TableFilter = null ; 160 /** 161 * Representation of <code>'TableTypeFilter'</code> property. 162 */ 163 public String[] TableTypeFilter = null ; 164 165 /** 166 * Creates an empty instance. 167 */ 168 public DataSourceInfo() 169 { 170 } 171 172 /** 173 * Creates an instance laying upon specified DataSource. 174 * @param dataSource All source properties are copied into 175 * class fields. 176 */ 177 public DataSourceInfo(Object dataSource) { 178 XPropertySet xProps = (XPropertySet) 179 UnoRuntime.queryInterface(XPropertySet.class, dataSource) ; 180 181 try { 182 Name = (String)xProps.getPropertyValue("Name") ; 183 URL = (String)xProps.getPropertyValue("URL") ; 184 Info = (PropertyValue[])xProps.getPropertyValue("Info") ; 185 User = (String)xProps.getPropertyValue("User") ; 186 Password = (String)xProps.getPropertyValue("Password") ; 187 IsPasswordRequired = (Boolean)xProps.getPropertyValue("IsPasswordRequired") ; 188 SuppressVersionColumns = (Boolean) 189 xProps.getPropertyValue("SuppressVersionColumns") ; 190 IsReadOnly = (Boolean)xProps.getPropertyValue("IsReadOnly") ; 191 TableFilter = (String[])xProps.getPropertyValue("TableFilter") ; 192 TableTypeFilter = (String[])xProps.getPropertyValue("TableTypeFilter") ; 193 } catch (com.sun.star.beans.UnknownPropertyException e) { 194 System.err.println("util.DBTools.DataSourceInfo: Error retrieving property") ; 195 e.printStackTrace(System.err) ; 196 } catch (com.sun.star.lang.WrappedTargetException e) { 197 System.err.println("util.DBTools.DataSourceInfo: Error retrieving property") ; 198 e.printStackTrace(System.err) ; 199 } 200 } 201 202 /** 203 * Prints datasource info. 204 * @param out Stream to which information is printed. 205 */ 206 public void printInfo(PrintWriter out) { 207 out.println("Name = '" + Name + "'") ; 208 out.println(" URL = '" + URL + "'") ; 209 out.print(" Info = ") ; 210 if (Info == null) out.println("null") ; 211 else { 212 out.print("{") ; 213 for (int i = 0; i < Info.length; i++) { 214 out.print(Info[i].Name + " = '" + Info[i].Value + "'") ; 215 if (i + 1 < Info.length) out.print("; ") ; 216 } 217 out.println("}") ; 218 } 219 out.println(" User = '" + User + "'") ; 220 out.println(" Password = '" + Password + "'") ; 221 out.println(" IsPasswordRequired = '" + IsPasswordRequired + "'") ; 222 out.println(" SuppressVersionColumns = '" + SuppressVersionColumns + "'") ; 223 out.println(" IsReadOnly = '" + IsReadOnly + "'") ; 224 out.print(" TableFilter = ") ; 225 if (TableFilter == null) out.println("null") ; 226 else { 227 out.print("{") ; 228 for (int i = 0; i < TableFilter.length; i++) { 229 out.print("'" + TableFilter[i] + "'") ; 230 if (i+1 < TableFilter.length) out.print("; "); 231 } 232 out.println("}") ; 233 } 234 out.print(" TableTypeFilter = ") ; 235 if (TableTypeFilter == null) out.println("null") ; 236 else { 237 out.print("{") ; 238 for (int i = 0; i < TableTypeFilter.length; i++) { 239 out.print("'" + TableTypeFilter[i] + "'") ; 240 if (i+1 < TableTypeFilter.length) out.print("; "); 241 } 242 out.println("}") ; 243 } 244 } 245 246 /** 247 * Creates new <code>com.sun.star.sdb.DataSource</code> service 248 * instance and copies all fields (which are not null) to 249 * appropriate service properties. 250 * @return <code>com.sun.star.sdb.DataSource</code> service. 251 */ 252 public Object getDataSourceService() throws Exception 253 { 254 Object src = src = xMSF.createInstance("com.sun.star.sdb.DataSource") ; 255 256 XPropertySet props = (XPropertySet) UnoRuntime.queryInterface 257 (XPropertySet.class, src) ; 258 259 if (Name != null) props.setPropertyValue("Name", Name) ; 260 if (URL != null) props.setPropertyValue("URL", URL) ; 261 if (Info != null) props.setPropertyValue("Info", Info) ; 262 if (User != null) props.setPropertyValue("User", User) ; 263 if (Password != null) props.setPropertyValue("Password", Password) ; 264 if (IsPasswordRequired != null) props.setPropertyValue("IsPasswordRequired", IsPasswordRequired) ; 265 if (SuppressVersionColumns != null) props.setPropertyValue("SuppressVersionColumns", SuppressVersionColumns) ; 266 if (IsReadOnly != null) props.setPropertyValue("IsReadOnly", IsReadOnly) ; 267 if (TableFilter != null) props.setPropertyValue("TableFilter", TableFilter) ; 268 if (TableTypeFilter != null) props.setPropertyValue("TableTypeFilter", TableTypeFilter) ; 269 270 return src ; 271 } 272 } 273 274 /** 275 * Creates class instance. 276 * @param xMSF <code>XMultiServiceFactory</code>. 277 */ 278 public DBTools(XMultiServiceFactory xMSF, PrintWriter _logger ) 279 { 280 this.xMSF = xMSF ; 281 this.m_log = _logger; 282 283 try { 284 Object cont = xMSF.createInstance("com.sun.star.sdb.DatabaseContext") ; 285 286 dbContext = (XNamingService) UnoRuntime.queryInterface 287 (XNamingService.class, cont) ; 288 289 } catch (com.sun.star.uno.Exception e) {} 290 } 291 292 /** 293 * Returns new instance of <code>DataSourceInfo</code> class. 294 */ 295 public DataSourceInfo newDataSourceInfo() { return new DataSourceInfo() ;} 296 297 /** 298 * Returns new instance of <code>DataSourceInfo</code> class. 299 */ 300 public DataSourceInfo newDataSourceInfo(Object dataSource) { 301 return new DataSourceInfo(dataSource); 302 } 303 304 /** 305 * Registers the datasource on the specified name in 306 * <code>DatabaseContext</code> service. 307 * @param name Name which dataSource will have in global context. 308 * @param dataSource <code>DataSource</code> object which is to 309 * be registered. 310 */ 311 public void registerDB(String name, Object dataSource) 312 throws com.sun.star.uno.Exception { 313 314 dbContext.registerObject(name, dataSource) ; 315 } 316 317 318 /** 319 * First tries to revoke the datasource with the specified 320 * name and then registers a new one. 321 * @param name Name which dataSource will have in global context. 322 * @param dataSource <code>DataSource</code> object which is to 323 * be registered. 324 */ 325 public void reRegisterDB(String name, Object dataSource) 326 throws com.sun.star.uno.Exception { 327 328 try { 329 revokeDB(name) ; 330 } catch (com.sun.star.uno.Exception e) {} 331 332 XDocumentDataSource xDDS = (XDocumentDataSource) 333 UnoRuntime.queryInterface(XDocumentDataSource.class, dataSource); 334 XStorable store = (XStorable) UnoRuntime.queryInterface(XStorable.class, 335 xDDS.getDatabaseDocument()); 336 String aFile = utils.getOfficeTemp(xMSF) + name + ".odb"; 337 store.storeAsURL(aFile, new PropertyValue[] { }); 338 339 registerDB(name, dataSource) ; 340 } 341 342 /** 343 * RESERVED. Not used. 344 */ 345 public XConnection connectToTextDB(String contextName, 346 String dbDir, String fileExtension) 347 throws com.sun.star.uno.Exception { 348 349 try { 350 XInterface newSource = (XInterface) xMSF.createInstance 351 ("com.sun.star.sdb.DataSource") ; 352 353 XPropertySet xSrcProp = (XPropertySet) 354 UnoRuntime.queryInterface(XPropertySet.class, newSource); 355 356 xSrcProp.setPropertyValue("URL", "sdbc:text:" + dirToUrl(dbDir)); 357 358 PropertyValue extParam = new PropertyValue() ; 359 extParam.Name = "EXT" ; 360 extParam.Value = fileExtension ; 361 362 xSrcProp.setPropertyValue("Info", new PropertyValue[] {extParam}) ; 363 364 dbContext.registerObject(contextName, newSource) ; 365 366 Object handler = xMSF.createInstance("com.sun.star.sdb.InteractionHandler"); 367 XInteractionHandler xHandler = (XInteractionHandler) 368 UnoRuntime.queryInterface(XInteractionHandler.class, handler) ; 369 370 XCompletedConnection xSrcCon = (XCompletedConnection) 371 UnoRuntime.queryInterface(XCompletedConnection.class, newSource) ; 372 373 XConnection con = xSrcCon.connectWithCompletion(xHandler) ; 374 375 return con ; 376 } finally { 377 try { 378 dbContext.revokeObject(contextName) ; 379 } catch (Exception e) {} 380 } 381 } 382 383 /** 384 * Registers DBase database (directory with DBF files) in the 385 * global DB context, then connects to it. 386 * @param contextName Name under which DB will be registered. 387 * @param dbDir The directory with DBF tables. 388 * @return Connection to the DB. 389 */ 390 public XConnection connectToDBase(String contextName, 391 String dbDir) 392 throws com.sun.star.uno.Exception { 393 394 try { 395 XInterface newSource = (XInterface) xMSF.createInstance 396 ("com.sun.star.sdb.DataSource") ; 397 398 XPropertySet xSrcProp = (XPropertySet) 399 UnoRuntime.queryInterface(XPropertySet.class, newSource); 400 xSrcProp.setPropertyValue("URL", "sdbc:dbase:" + dirToUrl(dbDir)); 401 402 dbContext.registerObject(contextName, newSource) ; 403 404 XConnection con = connectToSource(newSource) ; 405 406 return con ; 407 } catch(com.sun.star.uno.Exception e) { 408 try { 409 dbContext.revokeObject(contextName) ; 410 } catch (Exception ex) {} 411 412 throw e ; 413 } 414 } 415 416 /** 417 * Performs connection to DataSource specified. 418 * @param dbSource <code>com.sun.star.sdb.DataSource</code> service 419 * specified data source which must be already registered in the 420 * <code>DatabaseContext</code> service. 421 * @param dbSource Data source to be connected to. 422 * @return Connection to the data source. 423 */ 424 public XConnection connectToSource(Object dbSource) 425 throws com.sun.star.uno.Exception { 426 427 Object handler = xMSF.createInstance("com.sun.star.sdb.InteractionHandler"); 428 XInteractionHandler xHandler = (XInteractionHandler) 429 UnoRuntime.queryInterface(XInteractionHandler.class, handler) ; 430 431 XCompletedConnection xSrcCon = (XCompletedConnection) 432 UnoRuntime.queryInterface(XCompletedConnection.class, dbSource) ; 433 434 return xSrcCon.connectWithCompletion(xHandler) ; 435 } 436 437 /** 438 * Registers Test data source in the <code>DatabaseContext</code> service. 439 * This source always has name <code>'APITestDatabase'</code> and it 440 * is registered in subdirectory <code>TestDB</code> of directory 441 * <code>docPath</code> which is supposed to be a directory with test 442 * documents, but can be any other (it must have subdirectory with DBF 443 * tables). If such data source doesn't exists or exists with 444 * different URL it is recreated and reregistered. 445 * @param docPath Path to database <code>TestDB</code> directory. 446 * @return <code>com.sun.star.sdb.DataSource</code> service 447 * implementation which represents TestDB. 448 */ 449 public Object registerTestDB(String docPath) 450 throws com.sun.star.uno.Exception { 451 452 String testURL = null ; 453 if (docPath.endsWith("/") || docPath.endsWith("\\")) 454 testURL = dirToUrl(docPath + "TestDB") ; 455 else 456 testURL = dirToUrl(docPath + "/" + "TestDB") ; 457 testURL = "sdbc:dbase:" + testURL ; 458 459 String existURL = null ; 460 461 XNameAccess na = (XNameAccess) UnoRuntime.queryInterface 462 (XNameAccess.class, dbContext) ; 463 464 Object src = null ; 465 if (na.hasByName("APITestDatabase")) { 466 src = dbContext.getRegisteredObject("APITestDatabase") ; 467 468 XPropertySet srcPs = (XPropertySet) UnoRuntime.queryInterface 469 (XPropertySet.class, src) ; 470 471 existURL = (String) srcPs.getPropertyValue("URL") ; 472 } 473 474 if (src == null || !testURL.equals(existURL)) { 475 // test data source must be reregistered. 476 DataSourceInfo info = new DataSourceInfo() ; 477 info.URL = testURL ; 478 src = info.getDataSourceService() ; 479 reRegisterDB("APITestDatabase", src) ; 480 src = dbContext.getRegisteredObject("APITestDatabase") ; 481 } 482 483 return src ; 484 } 485 486 /** 487 * Connects to <code>DataSource</code> specially created for testing. 488 * This source always has name <code>'APITestDatabase'</code> and it 489 * is registered in subdirectory <code>TestDB</code> of directory 490 * <code>docPath</code> which is supposed to be a directory with test 491 * documents, but can be any other (it must have subdirectory with DBF 492 * tables). If such data source doesn't exists or exists with 493 * different URL it is recreated and reregistered. Finally connection 494 * performed. 495 * @param docPath Path to database <code>TestDB</code> directory. 496 * @return Connection to test database. 497 */ 498 public XConnection connectToTestDB(String docPath) 499 throws com.sun.star.uno.Exception { 500 501 return connectToSource(registerTestDB(docPath)) ; 502 } 503 504 /** 505 * Empties the table in the specified source. 506 * @param con Connection to the DataSource where appropriate 507 * table exists. 508 * @param table The name of the table where all rows will be deleted. 509 * @return Number of rows deleted. 510 */ 511 512 // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 513 // Currently doesn't work because of bugs 85509, 85510 514 515 public int deleteAllRows(XConnection con, String table) 516 throws com.sun.star.sdbc.SQLException { 517 518 XStatement stat = con.createStatement() ; 519 520 XResultSet set = stat.executeQuery("SELECT * FROM " + table) ; 521 522 XResultSetUpdate updt = (XResultSetUpdate) UnoRuntime.queryInterface 523 (XResultSetUpdate.class, set) ; 524 525 int count = 0 ; 526 set.last() ; 527 int rowNum = set.getRow() ; 528 set.first() ; 529 530 for (int i = 0; i < rowNum; i++) { 531 updt.deleteRow() ; 532 set.next() ; 533 count ++ ; 534 } 535 536 XCloseable xClose = (XCloseable) UnoRuntime.queryInterface 537 (XCloseable.class, set) ; 538 xClose.close() ; 539 540 return count ; 541 } 542 543 /** 544 * Inserts row into test table of the specified connection. 545 * Test table has some predefined format which includes as much 546 * field types as possible. For every column type constants 547 * {@link #TST_STRING TST_STRING}, {@link #TST_INT TST_INT}, etc. 548 * are declared for column index fast find. 549 * @param con Connection to data source where test table exists. 550 * @param table Test table name. 551 * @param values Values to be inserted into test table. Values of 552 * this array inserted into appropriate fields depending on their 553 * types. So <code>String</code> value of the array is inserted 554 * into the field of <code>CHARACTER</code> type, etc. 555 * @param streamLength Is optional. It is used only if in values 556 * list <code>XCharacterInputStream</code> or <code>XBinaryInputStream 557 * </code> types specified. In this case the parameter specifies 558 * the length of the stream for inserting. 559 */ 560 public void addRowToTestTable(XConnection con, String table, Object[] values, 561 int streamLength) 562 throws com.sun.star.sdbc.SQLException { 563 564 XStatement stat = con.createStatement() ; 565 566 XResultSet set = stat.executeQuery("SELECT * FROM " + table) ; 567 568 XResultSetUpdate updt = (XResultSetUpdate) UnoRuntime.queryInterface 569 (XResultSetUpdate.class, set) ; 570 571 XRowUpdate rowUpdt = (XRowUpdate) UnoRuntime.queryInterface 572 (XRowUpdate.class, set) ; 573 574 updt.moveToInsertRow() ; 575 576 for (int i = 0; i < values.length; i++) { 577 if (values[i] instanceof String) { 578 rowUpdt.updateString(TST_STRING, (String) values[i]) ; 579 } else 580 if (values[i] instanceof Integer) { 581 rowUpdt.updateInt(TST_INT, ((Integer) values[i]).intValue()) ; 582 } else 583 if (values[i] instanceof Double) { 584 rowUpdt.updateDouble(TST_DOUBLE, ((Double) values[i]).doubleValue()) ; 585 } else 586 if (values[i] instanceof Date) { 587 rowUpdt.updateDate(TST_DATE, (Date) values[i]) ; 588 } else 589 if (values[i] instanceof Boolean) { 590 rowUpdt.updateBoolean(TST_BOOLEAN, ((Boolean) values[i]).booleanValue()) ; 591 } else 592 if (values[i] instanceof XTextInputStream) { 593 rowUpdt.updateCharacterStream(TST_CHARACTER_STREAM, (XInputStream) values[i], 594 streamLength) ; 595 } else 596 if (values[i] instanceof XDataInputStream) { 597 rowUpdt.updateBinaryStream(TST_BINARY_STREAM, (XInputStream) values[i], 598 streamLength) ; 599 } 600 } 601 602 updt.insertRow() ; 603 604 XCloseable xClose = (XCloseable) UnoRuntime.queryInterface 605 (XCloseable.class, set) ; 606 xClose.close() ; 607 } 608 609 /** 610 * Initializes test table specified of the connection specified. 611 * Deletes all record from table, and then inserts data from 612 * <code>TST_TABLE_VALUES</code> constant array. <p> 613 * Test table has some predefined format which includes as much 614 * field types as possible. For every column type constants 615 * {@link #TST_STRING TST_STRING}, {@link #TST_INT TST_INT}, etc. 616 * are declared for column index fast find. 617 * @param con Connection to data source where test table exists. 618 * @param table Test table name. 619 */ 620 public void initializeTestTable(XConnection con, String table) 621 throws com.sun.star.sdbc.SQLException { 622 623 deleteAllRows(con, table) ; 624 625 for (int i = 0; i < TST_TABLE_VALUES.length; i++) { 626 addRowToTestTable(con, table, TST_TABLE_VALUES[i], TST_STREAM_LENGTHS[i]) ; 627 } 628 } 629 630 /** 631 * Prints full info about currently registered DataSource's. 632 */ 633 public void printRegisteredDatabasesInfo(PrintWriter out) { 634 XEnumerationAccess dbContEA = (XEnumerationAccess) 635 UnoRuntime.queryInterface(XEnumerationAccess.class, dbContext) ; 636 637 XEnumeration xEnum = dbContEA.createEnumeration() ; 638 639 out.println("DatabaseContext registered DataSource's :") ; 640 while (xEnum.hasMoreElements()) { 641 try { 642 DataSourceInfo inf = new DataSourceInfo(xEnum.nextElement()) ; 643 inf.printInfo(out) ; 644 } catch (com.sun.star.container.NoSuchElementException e) {} 645 catch (com.sun.star.lang.WrappedTargetException e) {} 646 } 647 } 648 649 /** 650 * Convert system pathname to SOffice URL string 651 * (for example 'C:\Temp\DBDir\' -> 'file:///C|/Temp/DBDir/'). 652 * (for example '\\server\Temp\DBDir\' -> 'file://server/Temp/DBDir/'). 653 * Already converted string retured unchanged. 654 */ 655 public static String dirToUrl(String dir) { 656 String retVal = null; 657 if (dir.startsWith("file:/")) retVal = dir; 658 else { 659 retVal = dir.replace(':', '|').replace('\\', '/'); 660 661 if (dir.startsWith("\\\\")) { 662 retVal = "file:" + retVal; 663 } 664 665 else retVal = "file:///" + retVal ; 666 } 667 return retVal; 668 } 669 670 /** 671 * Revokes datasource from global DB context. 672 * @param name DataSource name to be revoked. 673 */ 674 public void revokeDB(String name) throws com.sun.star.uno.Exception 675 { 676 dbContext.revokeObject(name) ; 677 } 678 679 /** 680 * Initializes test table specified of the connection specified 681 * using JDBC driver. Drops table with the name <code>tbl_name</code>, 682 * creates new table with this name and then inserts data from 683 * <code>TST_TABLE_VALUES</code> constant array. <p> 684 * Test table has some predefined format which includes as much 685 * field types as possible. For every column type constants 686 * {@link #TST_STRING TST_STRING}, {@link #TST_INT TST_INT}, etc. 687 * are declared for column index fast find. 688 * @param tbl_name Test table name. 689 */ 690 public void initTestTableUsingJDBC(String tbl_name, DataSourceInfo dsi) 691 throws java.sql.SQLException, 692 ClassNotFoundException { 693 //register jdbc driver 694 if ( dsi.Info[0].Name.equals("JavaDriverClass") ) { 695 Class.forName((String)dsi.Info[0].Value); 696 } else { 697 Class.forName(TST_JDBC_DRIVER); 698 } 699 700 //getting connection 701 Connection connection = null; 702 703 connection = DriverManager.getConnection( 704 dsi.URL, dsi.User, dsi.Password); 705 Statement statement = connection.createStatement(); 706 707 //drop table 708 dropMySQLTable(statement, tbl_name); 709 710 //create table 711 createMySQLTable(statement, tbl_name); 712 713 //insert some content 714 insertContentMySQLTable(statement, tbl_name); 715 } 716 717 /** 718 * Inserts data from <code>TST_TABLE_VALUES</code> constant array 719 * to test table <code>tbl_name</code>. 720 * @param statement object used for executing a static SQL 721 * statement and obtaining the results produced by it. 722 * @param tbl_name Test table name. 723 */ 724 protected void insertContentMySQLTable(Statement statement, String tbl_name) 725 throws java.sql.SQLException { 726 727 728 for(int i = 0; i < DBTools.TST_TABLE_VALUES.length; i++) { 729 String query = "insert into " + tbl_name + " values ("; 730 int j = 0; 731 while(j < DBTools.TST_TABLE_VALUES[i].length) { 732 if (j > 0) { 733 query += ", "; 734 } 735 Object value = DBTools.TST_TABLE_VALUES[i][j]; 736 if (value instanceof String || 737 value instanceof Date) { 738 query += "'"; 739 } 740 if (value instanceof Date) { 741 Date date = (Date)value; 742 query += date.Year + "-" + date.Month + 743 "-" + date.Day; 744 } else if (value instanceof Boolean) { 745 query += (((Boolean)value).booleanValue()) 746 ? "1" : "0"; 747 } else { 748 query += value; 749 } 750 751 if (value instanceof String || 752 value instanceof Date) { 753 query += "'"; 754 } 755 j++; 756 } 757 query += ")"; 758 statement.executeUpdate(query); 759 } 760 } 761 762 /** 763 * Creates test table specified. 764 * Test table has some predefined format which includes as much 765 * field types as possible. For every column type constants 766 * {@link #TST_STRING TST_STRING}, {@link #TST_INT TST_INT}, etc. 767 * are declared for column index fast find. 768 * @param statement object used for executing a static SQL 769 * statement and obtaining the results produced by it. 770 * @param table Test table name. 771 */ 772 protected void createMySQLTable(Statement statement, String tbl_name) 773 throws java.sql.SQLException { 774 775 final String empty_col_name = "Column"; 776 int c = 0; 777 String query = "create table " + tbl_name + " ("; 778 for (int i = 0; i < TST_TABLE_VALUES[0].length; i++) { 779 if (i > 0) query += ","; 780 781 switch(i + 1) { 782 case TST_BINARY_STREAM: 783 query += TST_BINARY_STREAM_F + " BLOB"; 784 break; 785 case TST_BOOLEAN: 786 query += TST_BOOLEAN_F + " TINYINT"; 787 break; 788 case TST_CHARACTER_STREAM: 789 query += TST_CHARACTER_STREAM_F + " TEXT"; 790 break; 791 case TST_DATE: 792 query += TST_DATE_F + " DATE"; 793 break; 794 case TST_DOUBLE: 795 query += TST_DOUBLE_F + " DOUBLE"; 796 break; 797 case TST_INT: 798 query += TST_INT_F + " INT"; 799 break; 800 case TST_STRING: 801 query += TST_STRING_F + " TEXT"; 802 break; 803 default: query += empty_col_name + (c++) + " INT"; 804 if (c == 1) { 805 query += " NOT NULL AUTO_INCREMENT"; 806 } 807 } 808 } 809 query += ", PRIMARY KEY (" + empty_col_name + "0)"; 810 query += ")"; 811 statement.execute(query); 812 } 813 814 /** 815 * Drops table. 816 * @param statement object used for executing a static SQL 817 * statement and obtaining the results produced by it. 818 * @param table Test table name. 819 */ 820 protected void dropMySQLTable(Statement statement, String tbl_name) 821 throws java.sql.SQLException { 822 statement.executeUpdate("drop table if exists " + tbl_name); 823 } 824 } 825