001: /*
002: * Copyright 2006 Pentaho Corporation. All rights reserved.
003: * This software was developed by Pentaho Corporation and is provided under the terms
004: * of the Mozilla Public License, Version 1.1, or any later version. You may not use
005: * this file except in compliance with the license. If you need a copy of the license,
006: * please go to http://www.mozilla.org/MPL/MPL-1.1.txt. The Original Code is the Pentaho
007: * BI Platform. The Initial Developer is Pentaho Corporation.
008: *
009: * Software distributed under the Mozilla Public License is distributed on an "AS IS"
010: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. Please refer to
011: * the license for the specific language governing your rights and limitations.
012: *
013: * Created Sep 8, 2005
014: * @author mbatchel
015: */
016: package org.pentaho.plugin.sql;
017:
018: import java.text.Format;
019: import java.util.ArrayList;
020: import java.util.List;
021: import java.util.Map;
022: import java.util.StringTokenizer;
023: import java.util.regex.Matcher;
024:
025: import org.apache.commons.logging.Log;
026: import org.pentaho.actionsequence.dom.ActionOutput;
027: import org.pentaho.actionsequence.dom.IActionInputValueProvider;
028: import org.pentaho.actionsequence.dom.actions.AbstractRelationalDbAction;
029: import org.pentaho.actionsequence.dom.actions.ActionDefinition;
030: import org.pentaho.actionsequence.dom.actions.SqlConnectionAction;
031: import org.pentaho.core.component.IDataComponent;
032: import org.pentaho.core.component.IPreparedComponent;
033: import org.pentaho.commons.connection.IPentahoConnection;
034: import org.pentaho.commons.connection.IPentahoMetaData;
035: import org.pentaho.commons.connection.IPentahoResultSet;
036: import org.pentaho.commons.connection.PentahoDataTransmuter;
037: import org.pentaho.commons.connection.memory.MemoryMetaData;
038: import org.pentaho.commons.connection.memory.MemoryResultSet;
039: import org.pentaho.core.util.IParameterResolver;
040: import org.pentaho.core.util.TemplateUtil;
041: import org.pentaho.data.PentahoConnectionFactory;
042: import org.pentaho.data.connection.sql.SQLConnection;
043: import org.pentaho.messages.Messages;
044: import org.pentaho.plugin.ComponentBase;
045:
046: /**
047: * SQLBaseComponent is the base class for SQLExecute and SQLLookupRule. it does the majority
048: * of work when interacting with Pentaho's BI Platform, including implementing the necessary
049: * component features. It also implements IDataComponent and IPreparedComponent.
050: *
051: * @see SQLExecute
052: * @see SQLLookupRule
053: */
054: public abstract class SQLBaseComponent extends ComponentBase implements
055: IDataComponent, IPreparedComponent, IParameterResolver {
056:
057: public static final String PREPARE_PARAMETER_PREFIX = "PREPARE"; //$NON-NLS-1$
058:
059: /** stores the prepared query for later use */
060: String preparedQuery = null;
061:
062: /** stores the prepared parameters for later use */
063: protected List preparedParameters = new ArrayList();
064:
065: /** is set to false if using another IPreparedComponents connection vs own */
066: protected boolean connectionOwner = true;
067:
068: /** reference to latest result set */
069: private IPentahoResultSet rSet;
070:
071: /** reference to connection object */
072: protected IPentahoConnection connection;
073:
074: public abstract boolean validateSystemSettings();
075:
076: public abstract String getResultOutputName();
077:
078: public abstract Log getLogger();
079:
080: /**
081: * returns the result set object
082: *
083: * @return pentaho result set
084: */
085: public IPentahoResultSet getResultSet() {
086: return rSet;
087: }
088:
089: /**
090: * validates the action. checks to verify inputs are available to execute
091: *
092: * - verify query is available
093: * - verify connection is available, via jndi, connection string, or prepared component
094: * - verify output is specified
095: *
096: *
097: */
098: public boolean validateAction() {
099: boolean result = true;
100:
101: ActionDefinition actionDefinition = getActionDefinition();
102: String actionName = getActionName();
103:
104: if (actionDefinition instanceof AbstractRelationalDbAction) {
105: AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) actionDefinition;
106: IActionInputValueProvider query = relationalDbAction
107: .getQuery();
108: IActionInputValueProvider dbUrl = relationalDbAction
109: .getDbUrl();
110: IActionInputValueProvider jndi = relationalDbAction
111: .getJndi();
112: IActionInputValueProvider sharedConnection = relationalDbAction
113: .getSharedConnection();
114: if (query == IActionInputValueProvider.NULL_INPUT) {
115:
116: error(Messages
117: .getErrorString(
118: "SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", actionName)); //$NON-NLS-1$
119: result = false;
120: }
121: if ((jndi == IActionInputValueProvider.NULL_INPUT)
122: && (dbUrl == IActionInputValueProvider.NULL_INPUT)
123: && (sharedConnection == IActionInputValueProvider.NULL_INPUT)) {
124: error(Messages
125: .getErrorString(
126: "SQLBaseComponent.ERROR_0002_CONNECTION_NOT_SPECIFIED", actionName)); //$NON-NLS-1$
127: result = false;
128: }
129: } else if (actionDefinition instanceof SqlConnectionAction) {
130: SqlConnectionAction sqlConnectionAction = (SqlConnectionAction) actionDefinition;
131: IActionInputValueProvider dbUrl = sqlConnectionAction
132: .getDbUrl();
133: IActionInputValueProvider jndi = sqlConnectionAction
134: .getJndi();
135: if ((jndi == IActionInputValueProvider.NULL_INPUT)
136: && (dbUrl == IActionInputValueProvider.NULL_INPUT)) {
137: error(Messages
138: .getErrorString(
139: "SQLBaseComponent.ERROR_0002_CONNECTION_NOT_SPECIFIED", actionName)); //$NON-NLS-1$
140: result = false;
141: }
142: } else {
143: error(Messages
144: .getErrorString(
145: "ComponentBase.ERROR_0001_UNKNOWN_ACTION_TYPE", actionDefinition.getElement().asXML())); //$NON-NLS-1$
146: result = false;
147: }
148: return result;
149: }
150:
151: /**
152: * nothing to do in done call from runtime context.
153: */
154: public void done() {
155: }
156:
157: /**
158: * determines state of component, and executes accordingly.
159: *
160: * various inputs that impact the state include:
161: *
162: * live - returns a live result set vs. an in memory copy
163: * transform - transform a result set based on additional inputs
164: * prepared_component - if available, use existing connection from prepared component
165: * max_rows - sets the number of rows that should be returned in result sets
166: *
167: * The specified output also impacts the state of the execution. If prepared_component is defined
168: * as an output, setup the query but delay execution.
169: *
170: */
171: protected boolean executeAction() {
172: ActionDefinition actionDefinition = getActionDefinition();
173: try {
174:
175: if (actionDefinition instanceof AbstractRelationalDbAction) {
176: AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) actionDefinition;
177: String baseQuery = getQuery();
178: if (baseQuery == null) {
179: error(Messages
180: .getErrorString(
181: "SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", actionDefinition.getDescription())); //$NON-NLS-1$
182: return false;
183: }
184:
185: IPreparedComponent sharedConnection = (IPreparedComponent) relationalDbAction
186: .getSharedConnection().getValue();
187: if (sharedConnection != null) {
188: connectionOwner = false;
189: IPentahoConnection conn = sharedConnection
190: .shareConnection();
191: if (conn == null) {
192: error(Messages
193: .getErrorString(
194: "IPreparedComponent.ERROR_0002_CONNECTION_NOT_AVAILABLE", getActionName())); //$NON-NLS-1$
195: return false;
196: } else if (conn.getDatasourceType() == IPentahoConnection.SQL_DATASOURCE) {
197: connection = conn;
198: } else {
199: error(Messages
200: .getErrorString(
201: "IPreparedComponent.ERROR_0001_INVALID_CONNECTION_TYPE", getActionName())); //$NON-NLS-1$
202: return false;
203: }
204: } else {
205: dispose();
206: connection = getDatasourceConnection();
207: }
208:
209: if (connection == null) {
210: return false;
211: }
212:
213: // Check if this is a prepared query that will be executed later. If so cache the
214: // query and set this component as the output. This query will be run later from a subreport.
215: if (relationalDbAction.getOutputPreparedStatement() != null) {
216: prepareQuery(baseQuery);
217: ActionOutput actionOutput = relationalDbAction
218: .getOutputPreparedStatement();
219: if (actionOutput != null) {
220: actionOutput.setValue(this );
221: }
222: return true;
223: }
224:
225: // TODO not sure if this should be allowed without connection ownership?
226: int maxRows = relationalDbAction.getMaxRows()
227: .getIntValue(-1);
228: if (maxRows >= 0) {
229: connection.setMaxRows(maxRows);
230: }
231:
232: if (relationalDbAction.getPerformTransform()
233: .getBooleanValue(false)) {
234: runQuery(baseQuery, false); // The side effect of
235: // transform rSet here
236:
237: rSet = PentahoDataTransmuter.crossTab(rSet,
238: relationalDbAction
239: .getTransformPivotColumn()
240: .getIntValue(-1) - 1,
241: relationalDbAction
242: .getTransformMeasuresColumn()
243: .getIntValue(-1) - 1,
244: relationalDbAction.getTransformSortColumn()
245: .getIntValue(0) - 1,
246: (Format) relationalDbAction
247: .getTransformPivotDataFormat()
248: .getValue(),
249: (Format) relationalDbAction
250: .getTransformSortDataFormat()
251: .getValue(), relationalDbAction
252: .getTransformOrderOutputColumns()
253: .getBooleanValue(false));
254:
255: ActionOutput actionOutput = relationalDbAction
256: .getOutputResultSet();
257: if (actionOutput != null) {
258: actionOutput.setValue(rSet);
259: }
260: return true;
261: } else {
262: return runQuery(baseQuery, relationalDbAction
263: .getLive().getBooleanValue(false));
264: }
265: } else if (actionDefinition instanceof SqlConnectionAction) {
266: SqlConnectionAction sqlConnectionAction = (SqlConnectionAction) actionDefinition;
267: dispose();
268: connection = getDatasourceConnection();
269: if (connection == null) {
270: return false;
271: } else {
272: ActionOutput actionOutput = sqlConnectionAction
273: .getOutputConnection();
274: if (actionOutput != null) {
275: actionOutput.setValue(this );
276: return true;
277: } else {
278: return false;
279: }
280: }
281: }
282: } catch (Exception e) {
283: error(
284: Messages
285: .getErrorString(
286: "SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$
287: }
288:
289: return false;
290: }
291:
292: /**
293: * returns metadata based on the result set. if not live, create an in memory version
294: *
295: * @param resultSet result set object to find metadata
296: * @param live if false, create an in memory version
297: *
298: * @return metadata object
299: */
300: protected IPentahoMetaData getMetadata(IPentahoResultSet resultSet,
301: boolean live) {
302: if (live) {
303: return resultSet.getMetaData();
304: } else {
305: Object columnHeaders[][] = resultSet.getMetaData()
306: .getColumnHeaders();
307: return new MemoryMetaData(columnHeaders, null);
308: }
309: }
310:
311: /**
312: * This inner class is used as a resolver for TemplateUtil.
313: */
314: private class ParamResolver implements IParameterResolver {
315: List paramList;
316:
317: Map lookupMap;
318:
319: public ParamResolver(List list, Map map) {
320: lookupMap = map;
321: paramList = list;
322: }
323:
324: /**
325: * This method is called when TemplateUtil.applyTemplate() encounters a parameter.
326: *
327: * @param template the source string
328: * @param parameter the parameter value
329: * @param parameterMatcher the regex parameter matcher
330: * @param copyStart the start of the copy
331: * @param results the output result
332: * @return the next copystart
333: */
334: public int resolveParameter(String template, String parameter,
335: Matcher parameterMatcher, int copyStart,
336: StringBuffer results) {
337:
338: StringTokenizer tokenizer = new StringTokenizer(parameter,
339: ":"); //$NON-NLS-1$
340: if (tokenizer.countTokens() == 2) { // Currently, the component only handles one bit of metadata
341: String parameterPrefix = tokenizer.nextToken();
342: String inputName = tokenizer.nextToken();
343:
344: if (parameterPrefix.equals(PREPARE_LATER_INTER_PREFIX)) {
345: // We know this parameter is for us.
346: // First, is this a special input
347: Object parameterValue = TemplateUtil
348: .getSystemInput(inputName,
349: getRuntimeContext());
350: if ((parameterValue == null) && (lookupMap != null)
351: && lookupMap.containsKey(inputName)) {
352: parameterValue = lookupMap.get(inputName);
353: }
354: if (parameterValue != null) {
355: // We have a parameter value - now, it's time to create a parameter and build up the
356: // parameter string
357: int start = parameterMatcher.start();
358: int end = parameterMatcher.end();
359: // First, find out if the parameter was quoted...
360: if ((start > 0) && (end < template.length())) {
361: if ((template.charAt(start - 1) == '\'')
362: && (template.charAt(end) == '\'')) {
363: // Ok, the parameter was quoted as near as we can tell. So, we need
364: // to increase the size of the amount we overwrite by one in each
365: // direction. This is for backward compatibility.
366: start--;
367: end++;
368: }
369: }
370: // We now have a valid start and end. It's time to see whether we're dealing
371: // with an array, a result set, or a scalar.
372: StringBuffer parameterBuffer = new StringBuffer();
373:
374: // find and remove the next placeholder, to be replaced by the new value
375: int index = paramList
376: .indexOf(PREPARE_LATER_PLACEHOLDER);
377: paramList.remove(index);
378: if (parameterValue instanceof String) {
379: paramList.add(index, parameterValue);
380: // preparedParameters.add(parameterValue);
381: parameterBuffer.append('?');
382: } else if (parameterValue instanceof Object[]) {
383: Object[] pObj = (Object[]) parameterValue;
384: for (int i = 0; i < pObj.length; i++) {
385: paramList.add(index++, pObj[i]);
386: parameterBuffer.append((parameterBuffer
387: .length() == 0) ? "?" : ",?"); //$NON-NLS-1$ //$NON-NLS-2$
388: }
389: } else if (parameterValue instanceof IPentahoResultSet) {
390: IPentahoResultSet rs = (IPentahoResultSet) parameterValue;
391: // See if we can find a column in the metadata with the same
392: // name as the input
393: IPentahoMetaData md = rs.getMetaData();
394: int columnIdx = -1;
395: if (md.getColumnCount() == 1) {
396: columnIdx = 0;
397: } else {
398: columnIdx = md
399: .getColumnIndex(new String[] { parameter });
400: }
401: if (columnIdx < 0) {
402: error(Messages
403: .getErrorString("Template.ERROR_0005_COULD_NOT_DETERMINE_COLUMN")); //$NON-NLS-1$
404: return -1;
405: }
406: int rowCount = rs.getRowCount();
407: Object valueCell = null;
408: // TODO support non-string columns
409: for (int i = 0; i < rowCount; i++) {
410: valueCell = rs.getValueAt(i, columnIdx);
411: paramList.add(index++, valueCell);
412: parameterBuffer.append((parameterBuffer
413: .length() == 0) ? "?" : ",?"); //$NON-NLS-1$ //$NON-NLS-2$
414: }
415: } else if (parameterValue instanceof List) {
416: List pObj = (List) parameterValue;
417: for (int i = 0; i < pObj.size(); i++) {
418: paramList.add(index++, pObj.get(i));
419: parameterBuffer.append((parameterBuffer
420: .length() == 0) ? "?" : ",?"); //$NON-NLS-1$ //$NON-NLS-2$
421: }
422: } else {
423: // If we're here, we know parameterValue is not null and not a string
424: paramList.add(index, parameterValue);
425: parameterBuffer.append('?');
426: }
427:
428: // OK - We have a parameterBuffer and have filled out the preparedParameters
429: // list. It's time to change the SQL to insert our parameter marker and tell
430: // the caller we've done our job.
431: results.append(template.substring(copyStart,
432: start));
433: copyStart = end;
434: results.append(parameterBuffer);
435: return copyStart;
436: }
437: }
438: }
439:
440: return -1; // Nothing here for us - let default behavior through
441: }
442: }
443:
444: /**
445: * executes a prepared method that returns a result set
446: * executePrepared looks up any "PREPARELATER" params
447: * in the preparedParams map.
448: *
449: * @param preparedParams a map of possible parameters.
450: * @return result set
451: */
452: public IPentahoResultSet executePrepared(Map preparedParams) {
453: try {
454: if (connection == null) {
455: error(Messages
456: .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
457: return null;
458: }
459: if (!connection.initialized()) {
460: error(Messages
461: .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
462: return null;
463: }
464:
465: if (preparedQuery == null) {
466: error(Messages
467: .getErrorString(
468: "SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", getActionName())); //$NON-NLS-1$
469: return null;
470: }
471:
472: // copy the preparedParams list, so it can be used multiple times.
473: ArrayList copyOfPreparedParameters = new ArrayList(
474: preparedParameters);
475:
476: // parse preparedQuery, replacing any {PREPARELATER:NAME} with appropriate values
477: String query = TemplateUtil.applyTemplate(preparedQuery,
478: getRuntimeContext(), new ParamResolver(
479: copyOfPreparedParameters, preparedParams));
480:
481: if (debug)
482: debug(Messages.getString(
483: "SQLBaseComponent.DEBUG_RUNNING_QUERY", query)); //$NON-NLS-1$
484:
485: // evaluate
486: IPentahoResultSet resultSet = null;
487: if (preparedParameters.size() > 0) {
488: resultSet = connection.prepareAndExecuteQuery(query,
489: copyOfPreparedParameters);
490: } else {
491: resultSet = connection.executeQuery(query);
492: }
493:
494: rSet = resultSet;
495: return resultSet;
496: } catch (Exception e) {
497: error(
498: Messages
499: .getErrorString(
500: "SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$
501: }
502: return null;
503: }
504:
505: /**
506: * called when in prepared-component mode, this method populates the preparedQuery string and
507: * preparedParameters object.
508: *
509: * @param rawQuery
510: * @return
511: */
512: protected boolean prepareQuery(String rawQuery) {
513:
514: try {
515: if (connection == null) {
516: error(Messages
517: .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
518: return false;
519: }
520: if (!connection.initialized()) {
521: error(Messages
522: .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
523: return false;
524: }
525:
526: preparedQuery = rawQuery;
527:
528: return true;
529: } catch (Exception e) {
530: error(
531: Messages
532: .getErrorString(
533: "SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$
534: }
535:
536: return false;
537: }
538:
539: /**
540: * executes the specified query template. The query template is first formatted and then
541: * executed. If live, the original result set is made available as an output. If not live,
542: * the result set is converted into memory and the connection and live result set are closed.
543: *
544: * @param rawQuery query template
545: * @param live returns original result set if true, memory result set if false
546: * @return true if successful
547: */
548: protected boolean runQuery(String rawQuery, boolean live) {
549: try {
550: if (connection == null) {
551: error(Messages
552: .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
553: return false;
554: }
555: if (!connection.initialized()) {
556: error(Messages
557: .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
558: return false;
559: }
560:
561: String query = applyInputsToFormat(rawQuery);
562:
563: if (debug)
564: debug(Messages.getString(
565: "SQLBaseComponent.DEBUG_RUNNING_QUERY", query)); //$NON-NLS-1$
566:
567: SQLConnection sqlConnection = null;
568: if ((connection instanceof SQLConnection)) {
569: sqlConnection = (SQLConnection) connection;
570: }
571:
572: AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) getActionDefinition();
573: if (live) {
574:
575: // set the result set as the ourput
576: IPentahoResultSet resultSet = doQuery(sqlConnection,
577: query, relationalDbAction
578: .getUseForwardOnlyResultSet()
579: .getBooleanValue(false));
580: rSet = resultSet;
581:
582: // After preparation and execution, we need to clear out the
583: // prepared parameters.
584: preparedParameters.clear();
585: if (resultSet != null) {
586: getMetadata(resultSet, true);
587: ActionOutput actionOutput = relationalDbAction
588: .getOutputResultSet();
589: if (actionOutput != null) {
590: actionOutput.setValue(resultSet);
591: }
592: return true;
593: } else {
594: // close the connection if owner
595: error(Messages
596: .getErrorString(
597: "SQLRule.ERROR_0006_EXECUTE_FAILED", getActionName())); //$NON-NLS-1$
598: if (connectionOwner) {
599: connection.close();
600: }
601: return false;
602: }
603:
604: } else {
605: // execute the query, read the results and cache them
606: try {
607: IPentahoResultSet resultSet = doQuery(
608: sqlConnection, query, relationalDbAction
609: .getUseForwardOnlyResultSet()
610: .getBooleanValue(false));
611: // After preparation and execution, we need to clear out the
612: // prepared parameters.
613: preparedParameters.clear();
614:
615: IPentahoMetaData metadata = getMetadata(resultSet,
616: false);
617: Object columnHeaders[][] = metadata
618: .getColumnHeaders();
619:
620: MemoryResultSet cachedResultSet = new MemoryResultSet(
621: metadata);
622:
623: int columnCount = columnHeaders[0].length;
624: String columnNames[] = new String[columnCount];
625: for (int columnNo = 0; columnNo < columnCount; columnNo++) {
626: columnNames[columnNo] = columnHeaders[0][columnNo]
627: .toString();
628: }
629:
630: Object[] rowObjects = resultSet.next();
631: while (rowObjects != null) {
632: cachedResultSet.addRow(rowObjects);
633: rowObjects = resultSet.next();
634: }
635: rSet = cachedResultSet;
636: ActionOutput actionOutput = relationalDbAction
637: .getOutputResultSet();
638: if (actionOutput != null) {
639: actionOutput.setValue(cachedResultSet);
640: }
641: } finally {
642: // close the connection if owner
643: if (connectionOwner) {
644: connection.close();
645: connection = null;
646: }
647: }
648: }
649: return true;
650: } catch (Exception e) {
651: error(
652: Messages
653: .getErrorString(
654: "SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$
655: }
656:
657: return false;
658: }
659:
660: public IPentahoResultSet doQuery(SQLConnection sqlConnection,
661: String query, boolean forwardOnlyResultset)
662: throws Exception {
663: IPentahoResultSet resultSet = null;
664: if (preparedParameters.size() > 0) {
665: if (!forwardOnlyResultset) {
666: resultSet = connection.prepareAndExecuteQuery(query,
667: preparedParameters);
668: } else {
669: if (sqlConnection != null) {
670: resultSet = sqlConnection.prepareAndExecuteQuery(
671: query, preparedParameters,
672: SQLConnection.RESULTSET_FORWARDONLY,
673: SQLConnection.CONCUR_READONLY);
674: }
675: }
676: } else {
677: if (!forwardOnlyResultset) {
678: resultSet = connection.executeQuery(query);
679: } else {
680: if (sqlConnection != null) {
681: resultSet = sqlConnection.executeQuery(query,
682: SQLConnection.RESULTSET_FORWARDONLY,
683: SQLConnection.CONCUR_READONLY);
684: }
685: }
686: }
687: return resultSet;
688: }
689:
690: /**
691: * dispose of the resultset, and if the owner, dispose of the connection.
692: */
693: public void dispose() {
694:
695: rSet = null;
696:
697: // close connection if owner
698: if (connectionOwner) {
699: if (connection != null) {
700: connection.close();
701: }
702: connection = null;
703: }
704:
705: }
706:
707: /**
708: * This method is called when TemplateUtil.applyTemplate() encounters a parameter.
709: * TemplateUtil.applyTemplate is called when someone makes a call to applyInputsToFormat()
710: * In this class it is called in the above "runQuery()" method.
711: *
712: * @param template the source string
713: * @param parameter the parameter value
714: * @param parameterMatcher the regex parameter matcher
715: * @param copyStart the start of the copy
716: * @param results the output result
717: * @return the next copystart
718: */
719: public int resolveParameter(String template, String parameter,
720: Matcher parameterMatcher, int copyStart,
721: StringBuffer results) {
722:
723: StringTokenizer tokenizer = new StringTokenizer(parameter, ":"); //$NON-NLS-1$
724: if (tokenizer.countTokens() == 2) { // Currently, the component only handles one bit of metadata
725: String parameterPrefix = tokenizer.nextToken();
726: String inputName = tokenizer.nextToken();
727:
728: // if the template contains a prepare later prefix,
729: // mark a spot in the preparedParameters list and move on.
730: if (parameterPrefix.equals(PREPARE_LATER_PREFIX)) {
731: if (!isDefinedOutput(PREPARED_COMPONENT_NAME)) {
732: error(Messages
733: .getErrorString("IPreparedComponent.ERROR_0003_INVALID_PARAMETER_STATE")); //$NON-NLS-1$
734: return -1;
735: }
736: preparedParameters.add(PREPARE_LATER_PLACEHOLDER);
737: int start = parameterMatcher.start();
738: int end = parameterMatcher.end();
739: results.append(template.substring(copyStart, start));
740: results
741: .append("{" + PREPARE_LATER_INTER_PREFIX + ":" + inputName + "}"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
742: return end;
743: }
744:
745: if (parameterPrefix.equals(PREPARE_PARAMETER_PREFIX)) {
746: // We know this parameter is for us.
747: // First, is this a special input
748: Object parameterValue = TemplateUtil.getSystemInput(
749: inputName, getRuntimeContext());
750: if ((parameterValue == null)
751: && isDefinedInput(inputName)) {
752: parameterValue = this .getInputValue(inputName);
753: }
754: if (parameterValue != null) {
755: // We have a parameter value - now, it's time to create a parameter and build up the
756: // parameter string
757: int start = parameterMatcher.start();
758: int end = parameterMatcher.end();
759: // First, find out if the parameter was quoted...
760: if ((start > 0) && (end < template.length())) {
761: if ((template.charAt(start - 1) == '\'')
762: && (template.charAt(end) == '\'')) {
763: // Ok, the parameter was quoted as near as we can tell. So, we need
764: // to increase the size of the amount we overwrite by one in each
765: // direction. This is for backward compatibility.
766: start--;
767: end++;
768: }
769: }
770: // We now have a valid start and end. It's time to see whether we're dealing
771: // with an array, a result set, or a scalar.
772: StringBuffer parameterBuffer = new StringBuffer();
773: if (parameterValue instanceof String) {
774: preparedParameters.add(parameterValue);
775: parameterBuffer.append('?');
776: } else if (parameterValue instanceof Object[]) {
777: Object[] pObj = (Object[]) parameterValue;
778: for (int i = 0; i < pObj.length; i++) {
779: preparedParameters.add(pObj[i]);
780: parameterBuffer.append((parameterBuffer
781: .length() == 0) ? "?" : ",?"); //$NON-NLS-1$ //$NON-NLS-2$
782: }
783: } else if (parameterValue instanceof IPentahoResultSet) {
784: IPentahoResultSet rs = (IPentahoResultSet) parameterValue;
785: // See if we can find a column in the metadata with the same
786: // name as the input
787: IPentahoMetaData md = rs.getMetaData();
788: int columnIdx = -1;
789: if (md.getColumnCount() == 1) {
790: columnIdx = 0;
791: } else {
792: columnIdx = md
793: .getColumnIndex(new String[] { parameter });
794: }
795: if (columnIdx < 0) {
796: error(Messages
797: .getErrorString("Template.ERROR_0005_COULD_NOT_DETERMINE_COLUMN")); //$NON-NLS-1$
798: return -1;
799: }
800: int rowCount = rs.getRowCount();
801: Object valueCell = null;
802: // TODO support non-string columns
803: for (int i = 0; i < rowCount; i++) {
804: valueCell = rs.getValueAt(i, columnIdx);
805: preparedParameters.add(valueCell);
806: parameterBuffer.append((parameterBuffer
807: .length() == 0) ? "?" : ",?"); //$NON-NLS-1$ //$NON-NLS-2$
808: }
809: } else if (parameterValue instanceof List) {
810: List pObj = (List) parameterValue;
811: for (int i = 0; i < pObj.size(); i++) {
812: preparedParameters.add(pObj.get(i));
813: parameterBuffer.append((parameterBuffer
814: .length() == 0) ? "?" : ",?"); //$NON-NLS-1$ //$NON-NLS-2$
815: }
816: } else {
817: // If we're here, we know parameterValue is not null and not a string
818: this .preparedParameters.add(parameterValue);
819: parameterBuffer.append('?');
820: }
821:
822: // OK - We have a parameterBuffer and have filled out the preparedParameters
823: // list. It's time to change the SQL to insert our parameter marker and tell
824: // the caller we've done our job.
825: results
826: .append(template
827: .substring(copyStart, start));
828: copyStart = end;
829: results.append(parameterBuffer);
830: return copyStart;
831: }
832: }
833: }
834:
835: return -1; // Nothing here for us - let default behavior through
836: }
837:
838: /**
839: * attempt to aquire a connection. if connection isn't available, wait a certain period of time
840: * before trying again.
841: *
842: * @return connection
843: */
844: public IPentahoConnection getDatasourceConnection() {
845: IPentahoConnection con;
846: int timeouts[] = { 200, 500, 2000 };
847: for (int i = 0; i < timeouts.length; i++) {
848: try {
849: con = getConnection();
850: try {
851: con.clearWarnings();
852: } catch (Exception ex) {
853: }
854: return con;
855: } catch (Exception ex) {
856: }
857: waitFor(timeouts[i]);
858: }
859: con = getConnection();
860: try {
861: con.clearWarnings();
862: } catch (Exception ex) {
863: }
864: return con;
865: }
866:
867: /**
868: * pause the thread a certain number of milliseconds
869: *
870: * @param millis time to sleep
871: */
872: protected void waitFor(int millis) {
873: try {
874: if (debug)
875: debug(Messages
876: .getString(
877: "SQLBaseComponent.DEBUG_WAITING_FOR_CONNECTION", Integer.toString(millis))); //$NON-NLS-1$
878: Thread.sleep(millis);
879: } catch (Exception ex) {
880: // ignore the interrupted exception, if it happens
881: }
882: }
883:
884: /**
885: * return this class's connection. This implements the IPreparedComponent
886: * interface, which may share its connection with others.
887: *
888: * @return connection object
889: */
890: public IPentahoConnection shareConnection() {
891: return connection;
892: }
893:
894: /**
895: * pass through to getConnection(defaultConnection)
896: *
897: * @return connection
898: */
899: protected IPentahoConnection getConnection() {
900: return getConnection(null);
901: }
902:
903: /**
904: * This method retrieves a connection based on the components
905: * inputs.
906: *
907: * @param defaultConnection a default connection to use if no other is available
908: * @return new connection object
909: */
910: protected IPentahoConnection getConnection(
911: IPentahoConnection defaultConnection) {
912: IPentahoConnection localConnection = null;
913: try {
914: String jndiName = null;
915: String driver = null;
916: String userId = null;
917: String password = null;
918: String connectionInfo = null;
919: if (getActionDefinition() instanceof SqlConnectionAction) {
920: SqlConnectionAction sqlConnectionAction = (SqlConnectionAction) getActionDefinition();
921: jndiName = sqlConnectionAction.getJndi()
922: .getStringValue();
923: driver = sqlConnectionAction.getDriver()
924: .getStringValue();
925: userId = sqlConnectionAction.getUserId()
926: .getStringValue();
927: password = sqlConnectionAction.getPassword()
928: .getStringValue();
929: connectionInfo = sqlConnectionAction.getDbUrl()
930: .getStringValue();
931: } else if (getActionDefinition() instanceof AbstractRelationalDbAction) {
932: AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) getActionDefinition();
933: jndiName = relationalDbAction.getJndi()
934: .getStringValue();
935: driver = relationalDbAction.getDriver()
936: .getStringValue();
937: userId = relationalDbAction.getUserId()
938: .getStringValue();
939: password = relationalDbAction.getPassword()
940: .getStringValue();
941: connectionInfo = relationalDbAction.getDbUrl()
942: .getStringValue();
943: }
944: if (jndiName != null) {
945: localConnection = PentahoConnectionFactory
946: .getConnection(
947: IPentahoConnection.SQL_DATASOURCE,
948: jndiName, this );
949: }
950: if (localConnection == null) {
951: if (driver == null && connectionInfo == null) {
952: // TODO raise an error
953: } else {
954: localConnection = PentahoConnectionFactory
955: .getConnection(
956: IPentahoConnection.SQL_DATASOURCE,
957: driver, connectionInfo, userId,
958: password, this );
959: }
960: }
961: if (localConnection == null) {
962: if (defaultConnection == null) {
963: error(Messages
964: .getErrorString("SQLBaseComponent.ERROR_0005_INVALID_CONNECTION")); //$NON-NLS-1$
965: return null;
966: } else {
967: localConnection = defaultConnection;
968: }
969: }
970: return localConnection;
971: } catch (Exception e) {
972: error(
973: Messages
974: .getErrorString(
975: "SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$
976: }
977: return null;
978: }
979:
980: /**
981: * nothing is done in the init function
982: *
983: * @return true always
984: */
985: public boolean init() {
986: return true;
987: }
988:
989: public String getQuery() {
990: preparedParameters.clear();
991: return ((AbstractRelationalDbAction) getActionDefinition())
992: .getQuery().getStringValue();
993: }
994: }
|