KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > pentaho > plugin > sql > SQLExecute


1 /*
2  * Copyright 2006 Pentaho Corporation. All rights reserved.
3  * This software was developed by Pentaho Corporation and is provided under the terms
4  * of the Mozilla Public License, Version 1.1, or any later version. You may not use
5  * this file except in compliance with the license. If you need a copy of the license,
6  * please go to http://www.mozilla.org/MPL/MPL-1.1.txt. The Original Code is the Pentaho
7  * BI Platform. The Initial Developer is Pentaho Corporation.
8  *
9  * Software distributed under the Mozilla Public License is distributed on an "AS IS"
10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. Please refer to
11  * the license for the specific language governing your rights and limitations.
12  *
13  * Contributed May 22, 2006
14  * @author Radek Maciaszek, M3 Media Service Limited
15  *
16  */
17
18 package org.pentaho.plugin.sql;
19
20 import java.sql.SQLException JavaDoc;
21 import java.util.StringTokenizer JavaDoc;
22
23 import org.apache.commons.logging.Log;
24 import org.apache.commons.logging.LogFactory;
25 import org.pentaho.core.connection.memory.MemoryMetaData;
26 import org.pentaho.core.connection.memory.MemoryResultSet;
27 import org.pentaho.data.connection.sql.SQLConnection;
28 import org.pentaho.messages.Messages;
29 public class SQLExecute extends SQLLookupRule {
30
31   private static final long serialVersionUID = 2480019361917802106L;
32
33   private String JavaDoc multiStatementSeparatorToken = ";"; //$NON-NLS-1$
34
35     public Log getLogger() {
36         return LogFactory.getLog(SQLExecute.class);
37     }
38
39     protected boolean runQuery(String JavaDoc rawQuery, boolean live) {
40         SQLConnection conn = (SQLConnection)connection;
41         return runSqlQuery(conn, rawQuery, live);
42     }
43     
44     protected boolean runSqlQuery(SQLConnection conn, String JavaDoc rawQuery, boolean live) {
45         boolean executed=false;
46         boolean continueOnException = false;
47         String JavaDoc[] columnHeaders = new String JavaDoc[] {
48             Messages.getString("SQLExecute.USER_AFFECTED_ROWS_COLUMN_NAME"), //$NON-NLS-1$
49 Messages.getString("SQLExecute.USER_AFFECTED_ROW_STATUS") //$NON-NLS-1$
50 };
51         MemoryMetaData metaData = new MemoryMetaData(new String JavaDoc[][]{ columnHeaders }, null);
52         metaData.setColumnTypes(new String JavaDoc[] {"int", "string"} ); //$NON-NLS-1$ //$NON-NLS-2$
53 MemoryResultSet affectedRowsResultSet = new MemoryResultSet(metaData);
54         String JavaDoc successMsg = Messages.getString("SQLExecute.USER_SUCCESS"); //$NON-NLS-1$
55 String JavaDoc failMsg = Messages.getString("SQLExecute.USER_FAILED"); //$NON-NLS-1$
56 try {
57             if (conn == null) {
58                 error(Messages.getErrorString( "SQLBaseComponent.ERROR_0007_NO_CONNECTION" )); //$NON-NLS-1$
59 return false;
60             }
61             if (!conn.initialized()) {
62                 error(Messages.getErrorString( "SQLBaseComponent.ERROR_0007_NO_CONNECTION" )); //$NON-NLS-1$
63 return false;
64             }
65
66             // Even if we get an exception, it's OK. This could happen with some drivers
67 // on a drop table statement when the table doesn't exist. So, in that case, we'd want
68 // to continue on exception.
69 if (isDefinedInput("continue_on_exception")) { //$NON-NLS-1$
70 continueOnException = "true".equals(getInputStringValue("continue_on_exception")); //$NON-NLS-1$ //$NON-NLS-2$
71 }
72             
73             // If our statement has semicolons in the statement itself, then
74 // we need to make sure not to tokenize on semicolon. This setting
75 // will force it into the original execution path.
76 //
77 boolean forceSingleStatement = false;
78             if (isDefinedInput("force_single_statement")) { //$NON-NLS-1$
79 forceSingleStatement = "true".equals(getInputStringValue("force_single_statement")); //$NON-NLS-1$ //$NON-NLS-2$
80 }
81             
82             // If we have multiple statements that include semicolons in the data,
83 // then we need to specify a separator for multi-line input.
84 if (isDefinedInput("multi_statement_separator")) { //$NON-NLS-1$
85 multiStatementSeparatorToken = getInputStringValue("multi_statement_separator"); //$NON-NLS-1$
86 }
87             
88             try {
89               if (forceSingleStatement) {
90                 // Forces original execution path.
91 //
92 // This execution path should be used if the query
93 // has a semi-colon in the text of the SQL statement.
94 //
95 // This is a legitimate condition if there is (for example)
96 // a statement with a where-clause that has a semi-colon.
97 //
98 // e.g.: UPDATE sometable SET somecolumn='val1;val2' WHERE somecolumn='val3;val4'
99 //
100 // In this case, using StringTokenizer on semi-colon will result in multiple un-executable
101 // statements - the whole thing will fail.
102 //
103 // This is (arguably) unlikely, but it is possible. That's why I've chosen to make sure
104 // that there is a mechanism for instating the old behavior.
105 //
106 String JavaDoc query = applyInputsToFormat(rawQuery);
107                 if (debug) {
108                     debug(Messages.getString("SQLBaseComponent.DEBUG_RUNNING_QUERY", query)); //$NON-NLS-1$
109 }
110                 int affectedRows = conn.execute(query);
111                 executed = true;
112                 affectedRowsResultSet.addRow(new Object JavaDoc[] { new Integer JavaDoc(affectedRows), successMsg} );
113               } else {
114                 //
115 // Multiple statement execute support provided by contribution from Melanie Crouch
116 //
117 rawQuery = removeLineTerminators(rawQuery.trim()).toString();
118                
119                // tokenize the rawQuery passed into method to find if there are multiple updates to be executed.
120 StringTokenizer JavaDoc st = new StringTokenizer JavaDoc(rawQuery, multiStatementSeparatorToken);
121                 
122                 while (st.hasMoreTokens() ) {
123                     //set rawQuery equal to the nextToken.
124 rawQuery = st.nextToken();
125                     String JavaDoc query = applyInputsToFormat(rawQuery.trim());
126                     if (debug)
127                       debug(Messages.getString("SQLBaseComponent.DEBUG_RUNNING_QUERY", query)); //$NON-NLS-1$
128 try{
129                       int affectedRows = conn.execute(query);
130                       // Normally, we'd check to see if the execution resulted in
131 // some updated rows.
132 affectedRowsResultSet.addRow(new Object JavaDoc[] { new Integer JavaDoc(affectedRows), successMsg} );
133                       executed = true;
134                       debug(Messages.getString("SQLBaseComponent.DEBUG_UPDATED_QUERY", query)); //$NON-NLS-1$
135 } catch (SQLException JavaDoc e) {
136                       error(Messages.getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName() + " : " + e.getLocalizedMessage())); //$NON-NLS-1$ //$NON-NLS-2$
137 executed = continueOnException;
138                       if (!continueOnException) {
139                         break;
140                       }
141                       addErrorCode(affectedRowsResultSet, e, failMsg);
142                     }
143                 } //end while tokenizer
144 }
145               if (getResultOutputName() != null) {
146                 setOutputValue(this.getResultOutputName(), affectedRowsResultSet);
147               }
148             } finally {
149               //
150 // No matter what, make sure the connection
151 // gets closed. Otherwise, the connection can
152 // (ok, will) get stranded eating up resources
153 // on the server. This is important.
154 //
155 conn.close();
156             }
157         } catch (SQLException JavaDoc e) {
158           error(Messages.getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName() + " : " + e.getLocalizedMessage())); //$NON-NLS-1$ //$NON-NLS-2$
159 executed = continueOnException;
160           addErrorCode(affectedRowsResultSet, e, e.getLocalizedMessage());
161         }
162         //moved finally after last catch so one connection could be used to execute multiple updates.
163 finally {
164           conn.close();
165         }
166         return executed;
167     }
168
169     public void addErrorCode(MemoryResultSet affectedRowsResultSet, SQLException JavaDoc e, String JavaDoc failMsg) {
170       int eCode = e.getErrorCode();
171       if (eCode > 0) {
172         eCode *= -1; // Make sure that error code results are negative.
173 }
174       affectedRowsResultSet.addRow(new Object JavaDoc[] { new Integer JavaDoc(eCode), e.getLocalizedMessage()} );
175     }
176     
177     public static String JavaDoc removeLineTerminators(String JavaDoc inputStr) {
178       char[] rtn = new char[inputStr.length()];
179       char ch;
180       for (int i=0; i<inputStr.length(); i++) {
181         ch = inputStr.charAt(i);
182         switch (ch) {
183           case '\r':
184           case '\n':
185             rtn[i] = ' ';
186             break;
187           default:
188             rtn[i] = (ch);
189         }
190       }
191       return new String JavaDoc(rtn);
192     }
193
194 }
Popular Tags