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