001/* 002 * Copyright (c) 1998-2010 The Regents of the University of California. 003 * All rights reserved. 004 * 005 * '$Author: crawl $' 006 * '$Date: 2016-10-14 06:25:45 +0000 (Fri, 14 Oct 2016) $' 007 * '$Revision: 34531 $' 008 * 009 * Permission is hereby granted, without written agreement and without 010 * license or royalty fees, to use, copy, modify, and distribute this 011 * software and its documentation for any purpose, provided that the above 012 * copyright notice and the following two paragraphs appear in all copies 013 * of this software. 014 * 015 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY 016 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES 017 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF 018 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF 019 * SUCH DAMAGE. 020 * 021 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, 022 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 023 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE 024 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF 025 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, 026 * ENHANCEMENTS, OR MODIFICATIONS. 027 * 028 */ 029 030package org.geon; 031 032import java.sql.Array; 033import java.sql.PreparedStatement; 034import java.sql.ResultSet; 035import java.sql.ResultSetMetaData; 036import java.sql.SQLException; 037import java.sql.Statement; 038import java.sql.Types; 039import java.util.HashMap; 040import java.util.Iterator; 041import java.util.LinkedList; 042import java.util.List; 043import java.util.Map; 044import java.util.Vector; 045 046import org.apache.commons.lang3.StringUtils; 047import org.ecoinformatics.seek.querybuilder.DBQueryDef; 048import org.ecoinformatics.seek.querybuilder.DBQueryDefParserEmitter; 049import org.ecoinformatics.seek.querybuilder.DBSchemaParserEmitter; 050import org.kepler.objectmanager.data.db.DSSchemaIFace; 051import org.kepler.objectmanager.data.db.QBTableauFactory; 052 053import ptolemy.actor.IOPort; 054import ptolemy.actor.TypedIOPort; 055import ptolemy.actor.parameters.PortParameter; 056import ptolemy.data.ArrayToken; 057import ptolemy.data.BooleanToken; 058import ptolemy.data.DateToken; 059import ptolemy.data.DoubleToken; 060import ptolemy.data.IntToken; 061import ptolemy.data.ObjectToken; 062import ptolemy.data.RecordToken; 063import ptolemy.data.StringToken; 064import ptolemy.data.Token; 065import ptolemy.data.expr.Parameter; 066import ptolemy.data.expr.StringParameter; 067import ptolemy.data.type.ArrayType; 068import ptolemy.data.type.BaseType; 069import ptolemy.data.type.RecordType; 070import ptolemy.data.type.Type; 071import ptolemy.kernel.CompositeEntity; 072import ptolemy.kernel.Port; 073import ptolemy.kernel.util.Attribute; 074import ptolemy.kernel.util.IllegalActionException; 075import ptolemy.kernel.util.InternalErrorException; 076import ptolemy.kernel.util.NameDuplicationException; 077import ptolemy.kernel.util.NamedObj; 078import ptolemy.kernel.util.Settable; 079import ptolemy.kernel.util.StringAttribute; 080import ptolemy.kernel.util.Workspace; 081import ptolemy.util.StringUtilities; 082 083////////////////////////////////////////////////////////////////////////// 084//// DatabaseQuery 085/** 086 * This actor performs database queries against a specific database. It accepts 087 * a string query and a database connection reference as inputs. The actor 088 * produces the output of the query in the user's selected output format, 089 * specified by the outputType parameter, either as an XML, Record, string or a 090 * in a relational form with no metadata. The user can also specify whether to 091 * broadcast each row at a time or the whole result at once. 092 * 093 * @author Efrat Jaeger 094 * @version $Id: DatabaseQuery.java 34531 2016-10-14 06:25:45Z crawl $ 095 * @since Ptolemy II 3.0.2 096 */ 097public class DatabaseQuery extends DatabaseAccessor { 098 099 /** 100 * Construct an actor with the given container and name. 101 * 102 * @param container 103 * The container. 104 * @param name 105 * The name of this actor. 106 * @exception IllegalActionException 107 * If the actor cannot be contained by the proposed 108 * container. 109 * @exception NameDuplicationException 110 * If the container already has an actor with this name. 111 */ 112 public DatabaseQuery(CompositeEntity container, String name) 113 throws NameDuplicationException, IllegalActionException { 114 super(container, name); 115 116 // Parameters 117 outputType = new StringParameter(this, "outputType"); 118 outputType.setExpression("XML"); 119 _outputType = _XML; 120 outputType.addChoice("XML"); 121 outputType.addChoice("record"); 122 outputType.addChoice("array"); 123 outputType.addChoice("string"); 124 outputType.addChoice("no metadata"); 125 outputType.addChoice("result set"); // send result set as is. 126 127 // Ports 128 query = new PortParameter(this, "query"); 129 query.setStringMode(true); 130 131 result = new TypedIOPort(this, "result", false, true); 132 133 _schemaAttr = new StringAttribute(this, "schemaDef"); 134 //TextStyle schemaDefTS = new TextStyle(_schemaAttr, "schemaDef"); 135 136 _sqlAttr = new StringAttribute(this, "sqlDef"); 137 _sqlAttr.setVisibility(Settable.NONE); 138 //TextStyle sqlDefTS = new TextStyle(_sqlAttr, "sqlDef"); 139 140 outputEachRowSeparately = new Parameter(this, 141 "outputEachRowSeparately", new BooleanToken(false)); 142 outputEachRowSeparately.setTypeEquals(BaseType.BOOLEAN); 143 attributeChanged(outputEachRowSeparately); 144 145 lowerColumnNames = new Parameter(this, "lowerColumnNames", 146 new BooleanToken(false)); 147 lowerColumnNames.setTypeEquals(BaseType.BOOLEAN); 148 149 // create tableau for editting the SQL String 150 _qbTableauFactory = new QBTableauFactory(this, "_tableauFactory"); 151 152 queryValues = new TypedIOPort(this, "queryValues", true, false); 153 queryValues.setTypeEquals(BaseType.GENERAL); 154 155 _attachText("_iconDescription", "<svg>\n" 156 + "<ellipse cx=\"0\" cy=\"-30\" " + "rx=\"20\" ry=\"10\"/>\n" 157 + "<line x1=\"20\" y1=\"0\" " + "x2=\"20\" y2=\"-30\"/>\n" 158 + "<line x1=\"-20\" y1=\"0\" " + "x2=\"-20\" y2=\"-30\"/>\n" 159 + "<line x1=\"-20\" y1=\"0\" " + "x2=\"20\" y2=\"0\"/>\n" 160 + "</svg>\n"); 161 } 162 163 // ///////////////////////////////////////////////////////////////// 164 // // ports and parameters //// 165 166 /** 167 * The output format: XML, Record or String or a relational string with no 168 * metadata information. 169 */ 170 public StringParameter outputType; 171 172 /** 173 * Specify whether to display the complete result at once or each row 174 * separately. 175 */ 176 public Parameter outputEachRowSeparately; 177 178 /** 179 * An input query string. Can use ? to denote parameters in the query, 180 * and specify the values <i>queryValues</i>. 181 */ 182 public PortParameter query; 183 184 /** 185 * The query result. 186 */ 187 public TypedIOPort result; 188 189 /** 190 * Hidden variable containing the xml representation of the query as 191 * returned by the query builder. 192 */ 193 public StringAttribute _sqlAttr = null; 194 195 /** 196 * The schema of the database. 197 */ 198 public StringAttribute _schemaAttr = null; 199 200 /** If true, column names are converted to lower-case. */ 201 public Parameter lowerColumnNames; 202 203 /** Value(s) to use in a prepared statement query. Must be either a 204 * single token, or an array. If an array, either the size 205 * of the array must be the same as the number of ? in the 206 * query, or there is only one ? in the query. In the latter 207 * case, the ? in the query is repeated the same number of times 208 * as the size of the array. 209 */ 210 public TypedIOPort queryValues; 211 212 // ///////////////////////////////////////////////////////////////// 213 // // public methods //// 214 215 /** 216 * Determine the output format 217 * 218 * @param attribute 219 * The attribute that changed. 220 * @exception IllegalActionException 221 * If the output type is not recognized. 222 */ 223 @Override 224 public void attributeChanged(Attribute attribute) 225 throws IllegalActionException { 226 try { 227 if (attribute == outputType) { 228 String strOutputType = outputType.getExpression(); 229 if (strOutputType.equals("XML")) { 230 _outputType = _XML; 231 } else if (strOutputType.equals("record")) { 232 _outputType = _RECORD; 233 } else if (strOutputType.equals("array")) { 234 _outputType = _ARR; 235 } else if (strOutputType.equals("string")) { 236 _outputType = _STR; 237 } else if (strOutputType.startsWith("no")) { 238 _outputType = _NOMD; 239 } else if (strOutputType.startsWith("result")) { 240 _outputType = _RS; 241 } else { 242 throw new IllegalActionException(this, 243 "Unrecognized math function: " + strOutputType); 244 } 245 } else if (attribute == outputEachRowSeparately) { 246 _separate = ((BooleanToken) outputEachRowSeparately.getToken()) 247 .booleanValue(); 248 } else if (attribute == _sqlAttr) { 249 if (_sqlAttr != null && !_sqlAttr.equals("")) { 250 String sqlXMLStr = ((Settable) _sqlAttr).getExpression(); 251 if (sqlXMLStr != null && !sqlXMLStr.equals("")){ 252 DBQueryDef queryDef = DBQueryDefParserEmitter 253 .parseQueryDef(_schemaDef, sqlXMLStr); 254 String sqlStr = DBQueryDefParserEmitter.createSQL( 255 _schemaDef, queryDef); 256 if (sqlStr != null) { 257 query.setToken(new StringToken(sqlStr)); 258 } 259 } 260 } 261 } else if (attribute == _schemaAttr) { 262 String schemaDef = ((Settable) _schemaAttr).getExpression(); 263 if (schemaDef.length() > 0) { 264 _schemaDef = DBSchemaParserEmitter 265 .parseSchemaDef(schemaDef); 266 } 267 } else if (attribute == lowerColumnNames) { 268 _lowerColumnNamesVal = ((BooleanToken) lowerColumnNames 269 .getToken()).booleanValue(); 270 } else { 271 super.attributeChanged(attribute); 272 } 273 } catch (Exception nameDuplication) { 274 /* 275 * throw new InternalErrorException(this, nameDuplication, 276 * "Unexpected name duplication"); 277 */ 278 } 279 } 280 281 @Override 282 public Object clone(Workspace workspace) throws CloneNotSupportedException { 283 DatabaseQuery newObject = (DatabaseQuery) super.clone(workspace); 284 newObject._qbTableauFactory = (QBTableauFactory) newObject.getAttribute("_tableauFactory"); 285 return newObject; 286 } 287 288 /** 289 * Try to set the database schema once the database connection port has been 290 * connected. 291 */ 292 @Override 293 public void connectionsChanged(Port port) { 294 super.connectionsChanged(port); 295 if (port == dbcon) { 296 List<?> conPortsList = dbcon.connectedPortList(); 297 Iterator<?> conPorts = conPortsList.iterator(); 298 while (conPorts.hasNext()) { 299 IOPort p = (IOPort) conPorts.next(); 300 if (p.isOutput() && p.getName().equals("dbcon")) { 301 NamedObj container = p.getContainer(); 302 if (container instanceof OpenDBConnection) { 303 String schema = ""; 304 try { 305 schema = ((OpenDBConnection) container) 306 .sendSchemaToConnected(); 307 } catch (IllegalActionException ex) { 308 schema = ""; 309 System.out.println("IllegalActionException: " 310 + ex.getMessage()); 311 } 312 if (!schema.equals("")) { 313 try { 314 _schemaAttr.setExpression(schema); 315 } catch (IllegalActionException ex) { 316 // unable to set schema attribute.. 317 System.out.println("IllegalActionException: " 318 + ex.getMessage()); 319 } 320 } 321 } 322 } 323 } 324 } 325 } 326 327 /** 328 * Consume a query and a database connection reference. Compute the query 329 * result according to the specified output format. 330 * 331 * @exception IllegalActionException 332 * If there is no director. 333 */ 334 @Override 335 public void fire() throws IllegalActionException { 336 337 super.fire(); 338 339 query.update(); 340 _query = ((StringToken) query.getToken()).stringValue(); 341 if (!_query.equals(_prevQuery) || query.getPort().getWidth() > 0) { // if 342 // this 343 // is 344 // a 345 // different 346 // query. 347 _prevQuery = _query; 348 349 Statement st = null; 350 ResultSet rs = null; 351 try { 352 try { 353 if(queryValues.numberOfSources() > 0) { 354 Token values = queryValues.get(0); 355 356 if(values instanceof ArrayToken) { 357 String queryStr; 358 ArrayToken array = (ArrayToken)values; 359 int paramsCount = StringUtils.countMatches(_query, "?"); 360 if(paramsCount == 1) { 361 // duplicate the ? 362 String newParams = StringUtils.repeat(",?", array.length()).substring(1); 363 queryStr = _query.replace("?", newParams); 364 } else if(paramsCount != array.length()) { 365 throw new IllegalActionException(this, 366 "Number of parameters in query (" + 367 paramsCount + ") does not match array size (" + 368 array.length() + ")."); 369 } else { 370 queryStr = _query; 371 } 372 373 //System.out.println(queryStr); 374 st = _db.prepareStatement(queryStr); 375 376 for(int i = 0; i < array.length(); i++) { 377 _setPreparedStatementValue((PreparedStatement)st, i + 1, array.getElement(i)); 378 } 379 380 } else { 381 st = _db.prepareStatement(_query); 382 _setPreparedStatementValue((PreparedStatement)st, 1, values); 383 } 384 385 rs = ((PreparedStatement)st).executeQuery(); 386 387 } else { 388 st = _db.createStatement(); 389 rs = st.executeQuery(_query); 390 } 391 392 393 switch (_outputType) { 394 case _XML: 395 _createXML(rs); 396 break; 397 case _RECORD: 398 _createRecord(rs); 399 break; 400 case _ARR: 401 _createArr(rs); 402 break; 403 case _STR: 404 _createString(rs); 405 break; 406 case _NOMD: 407 _createNoMetadata(rs); 408 break; 409 case _RS: 410 _sendResultSet(rs); 411 break; 412 default: 413 throw new InternalErrorException( 414 "Invalid value for _outputType private variable. " 415 + "DatabaseQuery actor (" + getFullName() 416 + ")" + " on output type " + _outputType); 417 } 418 } finally { 419 if(rs != null) { 420 rs.close(); 421 } 422 if(st != null) { 423 st.close(); 424 } 425 } 426 } catch (SQLException e) { 427 throw new IllegalActionException(this, e, 428 "SQL executeQuery exception for query:" + _query); 429 } 430 } else { 431 // if the query comes only from the parameter and hasn't changed 432 // don't refire. 433 if (query.getPort().getWidth() == 0) { 434 _refire = false; 435 } 436 } 437 } 438 439 /** 440 * Takes care of halting the execution in case the query is not updated from 441 * a port and hasn't changed. 442 */ 443 @Override 444 public boolean postfire() throws IllegalActionException { 445 if (!_refire) 446 return false; 447 448 return super.postfire(); 449 } 450 451 /** 452 * Read the outputType parameter and set output type accordingly. 453 * 454 * @exception IllegalActionException 455 * If the file or URL cannot be opened, or if the first line 456 * cannot be read. 457 */ 458 @Override 459 public void preinitialize() throws IllegalActionException { 460 super.preinitialize(); 461 462 _prevQuery = ""; 463 _refire = true; 464 465 // clear any existing constraints. 466 result.typeConstraints().clear(); 467 result.setTypeEquals(BaseType.UNKNOWN); 468 469 // Set the output type. 470 switch (_outputType) { 471 case _XML: 472 result.setTypeEquals(BaseType.STRING); 473 break; 474 case _RECORD: 475 // set the type to GENERAL; downstream actors will need to cast 476 // into the appropriate record type 477 result.setTypeEquals(BaseType.GENERAL); 478 break; 479 case _ARR: 480 result.setTypeEquals(new ArrayType(BaseType.STRING)); 481 break; 482 case _STR: 483 result.setTypeEquals(BaseType.STRING); 484 break; 485 case _NOMD: 486 result.setTypeEquals(BaseType.STRING); 487 break; 488 case _RS: 489 result.setTypeEquals(BaseType.GENERAL); 490 break; 491 default: 492 throw new InternalErrorException( 493 "Invalid value for _outputType private variable. " 494 + "DatabaseQuery actor (" + getFullName() + ")" 495 + " on output type " + _outputType); 496 } 497 } 498 499 @Override 500 public void wrapup() throws IllegalActionException { 501 super.wrapup(); 502 _prevQuery = ""; 503 _refire = true; 504 } 505 506 // ///////////////////////////////////////////////////////////////// 507 // // private methods //// 508 509 /** 510 * Send result set as is (separate is not applicable in this case). 511 */ 512 513 private void _sendResultSet(ResultSet rs) throws IllegalActionException { 514 result.broadcast(new ObjectToken(rs)); 515 } 516 517 /** 518 * Create a string result. 519 */ 520 private void _createString(ResultSet rs) throws IllegalActionException { 521 try { 522 ResultSetMetaData md = rs.getMetaData(); 523 String res = ""; 524 while (rs.next()) { 525 for (int i = 1; i <= md.getColumnCount(); i++) { 526 if (_lowerColumnNamesVal) { 527 res += md.getColumnName(i).toLowerCase() + ": "; 528 } else { 529 res += md.getColumnName(i) + ": "; 530 } 531 String val = rs.getString(i); 532 if (val == null) 533 res += ""; 534 else 535 res += val; 536 res += " ; "; 537 } 538 if (_separate) { 539 result.broadcast(new StringToken(res)); 540 res = ""; 541 } else { 542 res += "\n"; 543 } 544 } 545 if (!_separate) { 546 result.broadcast(new StringToken(res)); 547 } 548 rs.close(); 549 } catch (Exception ex) { 550 throw new IllegalActionException(this, ex, 551 "exception in create String result"); 552 } 553 } 554 555 /** 556 * Create result as an array of string. (due to problems with record array) 557 */ 558 private void _createArr(ResultSet rs) throws IllegalActionException { 559 try { 560 boolean hasResult = false; 561 Vector<StringToken> results = new Vector<StringToken>(); 562 Token resultTokens[] = null; 563 ResultSetMetaData md = rs.getMetaData(); 564 while (rs.next()) { 565 hasResult = true; 566 String res = ""; 567 for (int i = 1; i <= md.getColumnCount(); i++) { 568 String val = rs.getString(i); 569 if (val == null) 570 res += ","; 571 else { 572 val = val.replace(',', '%'); 573 res += val + ","; 574 } 575 576 } 577 578 // remove last comma. 579 int lstCmaInd = res.lastIndexOf(","); 580 if (lstCmaInd > -1) { 581 res = res.substring(0, lstCmaInd); 582 } 583 if (_separate) { 584 resultTokens = new Token[1]; 585 resultTokens[0] = new StringToken(res); 586 result.broadcast(new ArrayToken(resultTokens)); 587 } else { 588 results.add(new StringToken(res)); 589 } 590 } 591 592 if(!hasResult){ 593 //sent an empty array token if there is no result 594 result.broadcast(new ArrayToken(BaseType.STRING)); 595 return; 596 } 597 598 if (!_separate) { 599 600 resultTokens = new Token[results.size()]; 601 results.toArray(resultTokens); 602 result.broadcast(new ArrayToken(resultTokens)); 603 604 } 605 rs.close(); 606 } catch (Exception ex) { 607 throw new IllegalActionException(this, ex, 608 "exception in create String result"); 609 } 610 } 611 612 /** 613 * Create an XML stream result. 614 */ 615 private void _createXML(ResultSet rs) throws IllegalActionException { 616 try { 617 String tab = " "; 618 String finalResult = "<?xml version=\"1.0\"?> \n"; 619 finalResult += "<result> \n"; 620 ResultSetMetaData md = rs.getMetaData(); 621 622 int colNum = md.getColumnCount(); 623 String tag[] = new String[colNum]; // holds all the result tags. 624 for (int i = 0; i < colNum; i++) { 625 if (_lowerColumnNamesVal) { 626 tag[i] = md.getColumnName(i + 1).toLowerCase(); 627 } else { 628 tag[i] = md.getColumnName(i + 1); 629 } 630 tag[i] = tag[i].replace(' ', '_'); 631 if (tag[i].startsWith("#")) { 632 tag[i] = tag[i].substring(1); 633 } 634 635 // when joining two or more tables that have the same columns 636 // we'd like to distinguish between them. 637 int count = 1; 638 int j; 639 while (true) { // if the same tag appears more then once add an 640 // incremental index to it. 641 for (j = 0; j < i; j++) { 642 if (tag[i].equals(tag[j])) { // the new tag already 643 // exist 644 if (count == 1) { // first duplicate 645 tag[i] = tag[i] + count; 646 } else { 647 int tmp = count - 1; 648 String strCnt = "" + tmp; 649 int index = tag[i].lastIndexOf(strCnt); 650 tag[i] = tag[i].substring(0, index); // remove 651 // the 652 // prev 653 // index. 654 tag[i] = tag[i] + count; 655 } 656 count++; 657 break; 658 } 659 } 660 if (j == i) {// the tag was not found in existing tags. 661 count = 1; 662 break; 663 } 664 } 665 } 666 667 while (rs.next()) { 668 String res = tab + "<row> \n"; 669 670 for (int i = 0; i < colNum; i++) { 671 String val = rs.getString(i + 1); 672 res += tab + tab; 673 if (val == null) { 674 // res += "<" + tag[i] + "/>\n"; 675 res += "<" + StringUtilities.escapeForXML(tag[i]) 676 + "/>\n"; 677 } else { 678 res += "<" + StringUtilities.escapeForXML(tag[i]) + ">" 679 + StringUtilities.escapeForXML(val) + "</" 680 + StringUtilities.escapeForXML(tag[i]) + ">\n"; 681 } 682 } 683 res += tab + "</row> \n"; 684 685 if (_separate) { 686 finalResult += res + "</result>"; 687 result.broadcast(new StringToken(finalResult)); 688 finalResult = "<?xml version=\"1.0\"?> \n"; 689 finalResult += "<result>\n"; 690 } else { 691 finalResult += res; 692 } 693 } 694 if (!_separate) { 695 finalResult += "</result>"; 696 result.broadcast(new StringToken(finalResult)); 697 } 698 rs.close(); 699 } catch (Exception ex) { 700 throw new IllegalActionException(this, ex, 701 "exception in create XML stream"); 702 } 703 } 704 705 /** Create a record result. */ 706 private void _createRecord(ResultSet rs) throws IllegalActionException, 707 SQLException { 708 709 LinkedList<Token> outList = null; 710 711 if (!_separate) { 712 outList = new LinkedList<Token>(); 713 } 714 715 ResultSetMetaData md = rs.getMetaData(); 716 int colNum = md.getColumnCount(); 717 String labels[] = new String[colNum]; 718 Type types[] = new Type[colNum]; 719 for (int i = 1; i <= colNum; i++) { 720 if (_lowerColumnNamesVal) { 721 labels[i - 1] = md.getColumnName(i).toLowerCase(); 722 } else { 723 labels[i - 1] = md.getColumnName(i); 724 } 725 types[i - 1] = _convertTypeFromSQLType(md.getColumnType(i)); 726 } 727 728 Token values[] = new Token[colNum]; 729 while (rs.next()) { 730 for (int i = 1; i <= colNum; i++) { 731 values[i - 1] = _makeTokenFromResultSet(i, rs, md 732 .getColumnType(i)); 733 } 734 735 Token token = new RecordToken(labels, values); 736 if (_separate) { 737 result.broadcast(token); 738 } else { 739 outList.add(token); 740 } 741 } 742 rs.close(); 743 744 if (!_separate) { 745 ArrayToken arrayToken = null; 746 747 Token[] array = outList.toArray(new Token[0]); 748 if (array.length == 0) { 749 arrayToken = new ArrayToken(new RecordType(labels, types)); 750 } else { 751 arrayToken = new ArrayToken(array); 752 } 753 result.broadcast(arrayToken); 754 } 755 } 756 757 private static Type _convertTypeFromSQLType(int sqlType) { 758 Type retval = null; 759 if (sqlType == Types.ARRAY) { 760 761 } else { 762 retval = _sqlTypeMap.get(sqlType); 763 } 764 765 if (retval == null) { 766 System.out.println("WARNING: unhandled sql type: " + sqlType); 767 } 768 return retval; 769 } 770 771 /** Create a token from a single SQL row. */ 772 private static Token _makeTokenFromResultSet(int i, ResultSet rs, 773 int sqlType) throws IllegalActionException, SQLException { 774 Token retval = null; 775 776 switch (sqlType) { 777 case Types.INTEGER: 778 retval = new IntToken(rs.getInt(i)); 779 break; 780 case Types.DOUBLE: 781 retval = new DoubleToken(rs.getDouble(i)); 782 break; 783 case Types.TIMESTAMP: 784 retval = new DateToken(rs.getTimestamp(i).getTime()); 785 break; 786 case Types.VARCHAR: 787 retval = new StringToken(rs.getString(i)); 788 break; 789 case Types.ARRAY: 790 retval = _makeArrayTokenFromSQLArray(rs.getArray(i)); 791 break; 792 case Types.REAL: 793 retval = new DoubleToken(rs.getDouble(i)); 794 break; 795 case Types.FLOAT: 796 retval = new DoubleToken(rs.getDouble(i)); 797 break; 798 case Types.BIT: 799 case Types.BOOLEAN: 800 retval = new BooleanToken(rs.getBoolean(i)); 801 break; 802 default: 803 System.out.println("WARNING: unhandled sql type: " + sqlType); 804 retval = new StringToken(rs.getString(i)); 805 break; 806 } 807 return retval; 808 } 809 810 /** Create an array token from an SQL array. */ 811 private static ArrayToken _makeArrayTokenFromSQLArray(Array array) 812 throws IllegalActionException, SQLException { 813 814 try(ResultSet rsArray = array.getResultSet();) { 815 816 // count the number of elements 817 int len = 0; 818 if (rsArray.first()) { 819 len = 1; 820 while (rsArray.next()) { 821 len++; 822 } 823 } 824 825 // XXX check for len = 0 826 827 rsArray.first(); 828 Token[] tokens = new Token[len]; 829 int i = 0; 830 831 switch (array.getBaseType()) { 832 case Types.INTEGER: 833 do { 834 tokens[i++] = new IntToken(rsArray.getInt(2)); 835 } while (rsArray.next()); 836 break; 837 case Types.DOUBLE: 838 do { 839 tokens[i++] = new DoubleToken(rsArray.getDouble(2)); 840 } while (rsArray.next()); 841 break; 842 default: 843 System.out.println("WARNING: unhandle sql array type: " 844 + array.getBaseTypeName()); 845 do { 846 tokens[i++] = new StringToken(rsArray.getString(2)); 847 } while (rsArray.next()); 848 break; 849 } 850 return new ArrayToken(tokens); 851 } 852 } 853 854 /** Create a tabular form result string with no metadata information. */ 855 private void _createNoMetadata(ResultSet rs) throws IllegalActionException { 856 try { 857 ResultSetMetaData md = rs.getMetaData(); 858 int colNum = md.getColumnCount(); 859 String res = ""; 860 while (rs.next()) { 861 String currRow = ""; 862 for (int i = 1; i <= colNum; i++) { 863 String currVal = rs.getString(i); 864 if (currVal == null || currVal.equals("")) { 865 int type = md.getColumnType(i); 866 if (type == Types.CHAR || type == Types.VARCHAR) { 867 currVal = "-"; 868 } else 869 currVal = "-1"; 870 } 871 currVal = currVal.replace(' ', '_'); 872 currRow += currVal; 873 874 // for display purposes. 875 int colWidth = md.getColumnDisplaySize(i); 876 int numSpace = colWidth - currVal.length(); 877 for (int j = 0; j < numSpace; j++) { 878 currRow += " "; 879 } 880 } 881 if (_separate) { 882 result.broadcast(new StringToken(currRow)); 883 } else { 884 res += currRow + "\n"; 885 } 886 } 887 if (!_separate) { 888 // remove the last carriage return. 889 int lastCRInd = res.lastIndexOf("\n"); 890 if (lastCRInd > -1) { 891 res = res.substring(0, lastCRInd); 892 } 893 result.broadcast(new StringToken(res)); 894 } 895 rs.close(); 896 } catch (Exception ex) { 897 throw new IllegalActionException(this, ex, 898 "exception in create custom result"); 899 } 900 } 901 902 private static final Map<Integer, Type> _sqlTypeMap = new HashMap<Integer, Type>(); 903 904 static { 905 906 _sqlTypeMap.put(Types.DOUBLE, BaseType.DOUBLE); 907 _sqlTypeMap.put(Types.INTEGER, BaseType.INT); 908 _sqlTypeMap.put(Types.VARCHAR, BaseType.STRING); 909 _sqlTypeMap.put(Types.TIMESTAMP, BaseType.DATE); 910 _sqlTypeMap.put(Types.REAL, BaseType.DOUBLE); 911 _sqlTypeMap.put(Types.FLOAT, BaseType.FLOAT); 912 _sqlTypeMap.put(Types.BOOLEAN, BaseType.BOOLEAN); 913 _sqlTypeMap.put(Types.BIT, BaseType.BOOLEAN); 914 } 915 916 // ///////////////////////////////////////////////////////////////// 917 // // private variables //// 918 919 /** Output indicator parameter. */ 920 private int _outputType; 921 922 /** Output indicator parameter. */ 923 private boolean _separate; 924 925 /** Query string. */ 926 private String _query; 927 928 /** Previously queried query.. */ 929 private String _prevQuery = ""; 930 931 /** Refire flag. */ 932 private boolean _refire = true; 933 934 /** Query builder tableau factory. */ 935 protected QBTableauFactory _qbTableauFactory = null; 936 937 /** Schema definition interface, used by the query builder */ 938 protected DSSchemaIFace _schemaDef = null; 939 940 // Constants used for more efficient execution. 941 private static final int _XML = 0; 942 private static final int _RECORD = 1; 943 private static final int _STR = 2; 944 private static final int _NOMD = 3; 945 private static final int _ARR = 4; 946 private static final int _RS = 5; 947 948 /** If true, use lower-case column names. */ 949 private boolean _lowerColumnNamesVal = false; 950}