/* * Copyright (C) 2000 - 2013 TagServlet Ltd * * This file is part of Open BlueDragon (OpenBD) CFML Server Engine. * * OpenBD is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * Free Software Foundation,version 3. * * OpenBD is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with OpenBD. If not, see http://www.gnu.org/licenses/ * * Additional permission under GNU GPL version 3 section 7 * * If you modify this Program, or any covered work, by linking or combining * it with any of the JARS listed in the README.txt (or a modified version of * (that library), containing parts covered by the terms of that JAR, the * licensors of this Program grant you additional permission to convey the * resulting work. * README.txt @ http://www.openbluedragon.org/license/README.txt * * http://openbd.org/ * $Id: cfSTOREDPROC.java 2374 2013-06-10 22:14:24Z alan $ */ package com.naryx.tagfusion.cfm.sql; import java.io.Serializable; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Iterator; import com.naryx.tagfusion.cfm.application.cfApplicationData; import com.naryx.tagfusion.cfm.engine.catchDataFactory; import com.naryx.tagfusion.cfm.engine.cfCatchData; import com.naryx.tagfusion.cfm.engine.cfNumberData; import com.naryx.tagfusion.cfm.engine.cfSession; import com.naryx.tagfusion.cfm.engine.cfStructData; import com.naryx.tagfusion.cfm.engine.cfmBadFileException; import com.naryx.tagfusion.cfm.engine.cfmRunTimeException; import com.naryx.tagfusion.cfm.tag.cfTag; import com.naryx.tagfusion.cfm.tag.cfTagReturnType; public class cfSTOREDPROC extends cfTag implements Serializable { static final long serialVersionUID = 1; public static final String DATA_BIN_KEY = "CFSTOREDPROC_DATA"; protected void defaultParameters(String _tag) throws cfmBadFileException { defaultAttribute("RETURNCODE", "NO"); parseTagHeader(_tag); if (!containsAttribute("PROCEDURE")) throw newBadFileException("Missing Attribute", "You need to provide a PROCEDURE for the query"); if (containsAttribute("DBTYPE")) { if (!getConstant("DBTYPE").equalsIgnoreCase("dynamic")) throw newBadFileException("Invalid Attribute", "Only a DBTYPE of dynamic is supported"); if (!containsAttribute("CONNECTSTRING")) throw newBadFileException("Missing Attribute", "When a DBTYPE of dynamic is specified, you must provide a CONNECTSTRING"); } else { if (containsAttribute("CONNECTSTRING")) throw newBadFileException("Invalid Attribute", "CONNECTSTRING is only valid when DBTYPE is dynamic"); } } public String getEndMarker() { return "</CFSTOREDPROC>"; } /** * Gets the datasource value from first the TAG attribute, and if that does not exist, then from the application data * @param tag * @param _Session * @return * @throws cfmRunTimeException */ private String getDataSourceValue( cfTag tag, cfSession _Session ) throws cfmRunTimeException { if ( tag.containsAttribute("DATASOURCE") ) return tag.getDynamic(_Session, "DATASOURCE").getString(); else{ cfApplicationData appData = _Session.getApplicationData(); if ( appData != null ) return appData.getDataSource(); } return null; } public cfTagReturnType render(cfSession _Session) throws cfmRunTimeException { // Create the cfDataSource cfDataSource dataSource = null; String procName = getDynamic(_Session, "PROCEDURE").getString(); String datasourceName = getDataSourceValue( this, _Session ); if ( datasourceName != null ) { dataSource = new cfDataSource(datasourceName, _Session); } else { datasourceName = "DYNAMIC"; dataSource = new cfDynamicDataSource(datasourceName, _Session, getDynamic(_Session, "CONNECTSTRING").getString()); } if (!dataSource.isSql_storedprocedures()) { throw new cfmRunTimeException(catchDataFactory.generalException(cfCatchData.TYPE_DATABASE, "errorCode.sqlError", "sql.storedProcedure", new String[] { datasourceName })); } if (containsAttribute("USERNAME")) dataSource.setUsername(getDynamic(_Session, "USERNAME").getString()); if (containsAttribute("PASSWORD")) dataSource.setPassword(getDynamic(_Session, "PASSWORD").getString()); // Get the java.sql.Connection object Connection con = null; CallableStatement call = null; String callString = null; try { con = setupDataConnection(dataSource); boolean bSupportStored = false; try { bSupportStored = con.getMetaData().supportsStoredProcedures(); } catch (Exception e) {} if (!bSupportStored) { throw newRunTimeException("The datasource, " + datasourceName + ", does not support stored procedures"); } // Set the internal data structure and render all the internal tags cfStoredProcData storedProcData = new cfStoredProcData(con); _Session.setDataBin(DATA_BIN_KEY, storedProcData); renderToString(_Session); // Determine if we should use named parameters for this stored procedure // call NOTE: this method will set a flag in each preparedData object too. boolean useNamedParameters = storedProcData.setUseNamedParameters(); boolean returnCode = getDynamic(_Session, "RETURNCODE").getBoolean(); // Get the java.sql.CallableStatement object callString = "{ " + (returnCode ? "? = " : "") + "call " + procName + storedProcData.getQueryString() + " }"; call = setUpStoredProcedure(datasourceName, callString, procName, con, returnCode, useNamedParameters); setParams(datasourceName, callString, call, returnCode, storedProcData.iterator(), con); long start = System.currentTimeMillis(); call.execute(); long execTime = System.currentTimeMillis() - start; _Session.getDebugRecorder().execStoredProc( datasourceName, callString, procName, execTime ); _Session.metricQueryTimeAdd(execTime); // get the results; must be done BEFORE return code and out variables // are read otherwise we won't be able to read them with certain drivers (ie. Oracle) storedProcData.retrieveAndStoreResultSets(_Session, datasourceName, call); // set cfstoredproc.executionTime cfStructData returnData = new cfStructData(); returnData.setData("executiontime", new cfNumberData(execTime)); // get the return code if (returnCode) { try { returnData.setData("statuscode", new cfNumberData(call.getInt(1))); } catch (SQLException ignored) { returnData.setData("statuscode", new cfNumberData(0)); } } if (containsAttribute("RESULT")) { _Session.setData(getDynamic(_Session, "RESULT").getString(), returnData); } else { _Session.setData("cfstoredproc", returnData); } // get out variables retrieveOutVariables(_Session, callString, datasourceName, call, returnCode, storedProcData.iterator()); // Tracer details querySlowLog.record( this, procName, execTime ); // Debug recording if (_Session.getShowDBActivity()) { if (!containsAttribute("DEBUG") || getDynamic(_Session, "DEBUG").getString().equals("") || getDynamic(_Session, "DEBUG").getBoolean()) { _Session.storedProcRan(getFile(), datasourceName, procName, execTime, storedProcData.getParams(), storedProcData.getResults()); } } else if (containsAttribute("DEBUG") && (getDynamic(_Session, "DEBUG").getString().equals("") || getDynamic(_Session, "DEBUG").getBoolean())) { _Session.storedProcRan(getFile(), datasourceName, procName, execTime, storedProcData.getParams(), storedProcData.getResults()); } } catch (SQLException e) { throw new cfmRunTimeException(catchDataFactory.databaseException(datasourceName, "sql.storedProcedureExecute", null, callString, e)); } finally { // Close off the connections _Session.deleteDataBin(DATA_BIN_KEY); closeConnections(con, call, dataSource); } return cfTagReturnType.NORMAL; } private static Connection setupDataConnection(cfDataSource thisDataSource) throws cfmRunTimeException { try { return thisDataSource.takeConnection(); } catch (SQLException e) { throw new cfmRunTimeException(catchDataFactory.databaseException(thisDataSource.getDataSourceName(), "sql.connecting", new String[] { com.naryx.tagfusion.cfm.tag.tagUtils.trimError(e.getMessage()) }, "", e)); } } private static void closeConnections(Connection dataConnection, PreparedStatement Statmt, cfDataSource thisDataSource) { if (dataConnection != null) { try { Statmt.close(); } catch (Exception ignoreException) {} thisDataSource.returnConnection(dataConnection); } } private CallableStatement setUpStoredProcedure(String _datasourceName, String callString, String _procName, Connection _con, boolean returnCode, boolean useNamedParameters) throws cfmRunTimeException { try { CallableStatement stmt = _con.prepareCall(callString); if (returnCode) { if (useNamedParameters) { // Using "@RETURN_VALUE" for the return value is specific to SQL Server. // For other databases we may need to use a different value. stmt.registerOutParameter("RETURN_VALUE", java.sql.Types.INTEGER); } else { stmt.registerOutParameter(1, java.sql.Types.INTEGER); } } return stmt; } catch (SQLException e) { throw new cfmRunTimeException(catchDataFactory.databaseException(_datasourceName, "sql.storedProcedureSetup", new String[] { _procName }, callString, e)); } } private boolean retrieveOutVariables(cfSession _Session, String callString, String _datasourceName, CallableStatement _stmt, boolean returnCode, Iterator<preparedData> iter)throws cfmRunTimeException { try { int counter = (returnCode ? 2 : 1); while (iter.hasNext()) iter.next().retrieveOutVariables(counter++, _Session, _stmt); return true; } catch (SQLException e) { throw new cfmRunTimeException(catchDataFactory.databaseException(_datasourceName, "sql.storedProcedureOUT", null, callString, e)); } } private void setParams(String _datasourceName, String callString, CallableStatement _call, boolean returnCode, Iterator<preparedData> iter, Connection _con) throws cfmRunTimeException { try { int counter = (returnCode ? 2 : 1); while (iter.hasNext()) counter = iter.next().prepareStatement(counter, _call, _con); } catch (SQLException e) { throw new cfmRunTimeException(catchDataFactory.databaseException(_datasourceName, "sql.storedProcedureParams", null, callString, e)); } } }