http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/regress/udr/TEST002 ---------------------------------------------------------------------- diff --git a/core/sql/regress/udr/TEST002 b/core/sql/regress/udr/TEST002 index 97fb254..79d8250 100644 --- a/core/sql/regress/udr/TEST002 +++ b/core/sql/regress/udr/TEST002 @@ -230,6 +230,24 @@ from udf(timeseries(table(select * from t002_Timeseries 'VAL2', 'FLi', 'VAL2', 'LLi')); +-- JDBC queries through a TMUDF. Note that this requires the Trafodion T4 +-- driver jar to exist in $MY_SQROOT/udr/external_libs. +sh mkdir -p $$MY_SQROOT$$/udr/external_libs; +sh cp $$MY_SQROOT$$/export/lib/jdbcT2.jar $$MY_SQROOT$$/udr/external_libs; + +-- with default ports, $$QUOTE$$$$JDBC_T4_URL$$$$QUOTE$$ should be +-- 'jdbc:t4jdbc://localhost:23400/:' +prepare s_traf from +select * +from udf(jdbc('jdbcT4.jar', + 'org.trafodion.jdbc.t4.T4Driver', + $$QUOTE$$$$JDBC_T4_URL$$$$QUOTE$$, + 'any', -- no user id + 'any', -- no password + 'source', + 'select * from (values (''Hello'', ''World''), (''Hallo'', ''Welt'')) T(a,b)')); +execute s_traf; + -- negative tests select * from udf(timeseries());
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/sqlci/SqlCmd.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlci/SqlCmd.cpp b/core/sql/sqlci/SqlCmd.cpp index 855652b..6c09c0d 100644 --- a/core/sql/sqlci/SqlCmd.cpp +++ b/core/sql/sqlci/SqlCmd.cpp @@ -642,6 +642,7 @@ char * SqlCmd::replacePattern(SqlciEnv * sqlci_env, char * str) } j = 0; + skipChar = FALSE; state = CONSUME_CHAR; break; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/sqludr/doxygen_tmudr.1.6.config ---------------------------------------------------------------------- diff --git a/core/sql/sqludr/doxygen_tmudr.1.6.config b/core/sql/sqludr/doxygen_tmudr.1.6.config index b795389..158b1f6 100644 --- a/core/sql/sqludr/doxygen_tmudr.1.6.config +++ b/core/sql/sqludr/doxygen_tmudr.1.6.config @@ -31,14 +31,14 @@ PROJECT_NAME = tmudr # This could be handy for archiving the generated documentation or # if some version control system is used. -PROJECT_NUMBER = 2.0 +PROJECT_NUMBER = 1.3.0 # The OUTPUT_DIRECTORY tag is used to specify the (relative or absolute) # base path where the generated documentation will be put. # If a relative path is entered, it will be relative to the location # where doxygen was started. If left blank the current directory will be used. -OUTPUT_DIRECTORY = tmudr_2.0 +OUTPUT_DIRECTORY = tmudr_1.3.0 # If the CREATE_SUBDIRS tag is set to YES, then doxygen will create # 4096 sub-directories (in 2 levels) under the output directory of each output http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/sqludr/sqludr.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqludr/sqludr.cpp b/core/sql/sqludr/sqludr.cpp index 85e4259..f1741b9 100644 --- a/core/sql/sqludr/sqludr.cpp +++ b/core/sql/sqludr/sqludr.cpp @@ -7761,7 +7761,7 @@ void UDR::processData(UDRInvocationInfo &info, * * This method is called in debug Trafodion builds when certain * flags are set in the UDR_DEBUG_FLAGS CQD (CONTROL QUERY DEFAULT). - * See https://wiki.trafodion.org/wiki/index.php/Tutorial:_The_object-oriented_UDF_interface#Debugging_UDF_code + * See https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface#Tutorial:Theobject-orientedUDFinterface-DebuggingUDFcode * for details. * * The default implementation prints out the process id and then http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/sqludr/sqludr.h ---------------------------------------------------------------------- diff --git a/core/sql/sqludr/sqludr.h b/core/sql/sqludr/sqludr.h index 6e094a7..ba90eab 100644 --- a/core/sql/sqludr/sqludr.h +++ b/core/sql/sqludr/sqludr.h @@ -464,7 +464,7 @@ typedef void (*SQLUDR_EmitRow) (char *rowData, /*IN*/ * values of parameters, UDR name, etc. * * For an introduction, see - * https://wiki.trafodion.org/wiki/index.php/Tutorial:_The_object-oriented_UDF_interface + * https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface * */ @@ -1489,14 +1489,16 @@ namespace tmudr ///< carry any state between rows it reads from its ///< table-valued inputs. It produces zero or more output ///< rows per input row. Because no state is kept between - ///< rows, the Trafodion compiler can automatically push - ///< predicates down to the table-valued inputs. + ///< rows, the Trafodion compiler can automatically + ///< parallelize execution and push predicates down to + ///< the table-valued inputs. REDUCER ///< A reducer requires the data to be partitioned on ///< a set of columns. The UDF does not carry any state ///< between groups of rows with the same partition column ///< values, but it may carry state within such groups. - ///< This allows the compiler to push predicates on the - ///< partitioning column(s) down to table-valued inputs. + ///< This allows the compiler to parallelize execution and + ///< to push predicates on the partitioning column(s) down + ///< to table-valued inputs. }; /** @@ -1571,7 +1573,7 @@ namespace tmudr * * use cqd UDR_DEBUG_FLAGS 'num' in SQL to set these, add up * the flags (in decimal) that you want to set. See - * https://wiki.trafodion.org/wiki/index.php/Tutorial:_The_object-oriented_UDF_interface#Debugging_UDF_code + * https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface#Tutorial:Theobject-orientedUDFinterface-DebuggingUDFcode * for details. */ enum DebugFlags @@ -1802,7 +1804,7 @@ namespace tmudr * UDR writers can create a derived class and implement these methods * for their specific UDR. The base class also has default methods * for all but the runtime call. See - * https://wiki.trafodion.org/wiki/index.php/Tutorial:_The_object-oriented_UDF_interface + * https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface * for examples. * * To use this interface, the UDR writer must provide a function http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java ---------------------------------------------------------------------- diff --git a/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java b/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java index 0c16ad1..478a993 100644 --- a/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java +++ b/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java @@ -51,15 +51,17 @@ public class UDRInvocationInfo extends TMUDRSerializableObject * carry any state between rows it reads from its * table-valued inputs. It produces zero or more output * rows per input row. Because no state is kept between - * rows, the Trafodion compiler can automatically push - * predicates down to the table-valued inputs. */ + * rows, the Trafodion compiler can automatically + * parallelize execution and push predicates down to + * the table-valued inputs. */ MAPPER, /** A reducer requires the data to be partitioned on * a set of columns. The UDF does not carry any state * between groups of rows with the same partition column * values, but it may carry state within such groups. - * This allows the compiler to push predicates on the - * partitioning column(s) down to table-valued inputs. */ + * This allows the compiler to parallelize execution and + * to push predicates on the partitioning column(s) down + * to table-valued inputs. */ REDUCER; private static FuncType[] allValues = values(); @@ -950,12 +952,7 @@ public class UDRInvocationInfo extends TMUDRSerializableObject CallPhase.COMPILER_PLAN_CALL, "UDRInvocationInfo::setUDRWriterCompileTimeData()"); - // for now we can't allow this - throw new UDRException( - 38912, - "UDRInvocationInfo::setUDRWriterCompileTimeData() not yet supported"); - - // udrWriterCompileTimeData_ = compileTimeData; + udrWriterCompileTimeData_ = compileTimeData; } /** http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/src/main/java/org/trafodion/sql/udr/predef/JDBCUDR.java ---------------------------------------------------------------------- diff --git a/core/sql/src/main/java/org/trafodion/sql/udr/predef/JDBCUDR.java b/core/sql/src/main/java/org/trafodion/sql/udr/predef/JDBCUDR.java new file mode 100644 index 0000000..31fb72a --- /dev/null +++ b/core/sql/src/main/java/org/trafodion/sql/udr/predef/JDBCUDR.java @@ -0,0 +1,690 @@ +/********************************************************************** +Licensed to the Apache Software Foundation (ASF) under one +or more contributor license agreements. See the NOTICE file +distributed with this work for additional information +regarding copyright ownership. The ASF licenses this file +to you under the Apache License, Version 2.0 (the +"License"); you may not use this file except in compliance +with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, +software distributed under the License is distributed on an +"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +KIND, either express or implied. See the License for the +specific language governing permissions and limitations +under the License. +**********************************************************************/ + +/*************************************************** + * A TMUDF that executes a generic JDBC query + * and returns the result of the one SQL statement + * in the list that produces results as a table-valued + * output + * + * Invocation (all arguments are strings): + * + * select ... from udf(JDBC( + * <name of JDBC driver jar>, + * <name of JDBC driver class in the jar>, + * <connection string>, + * <user name>, + * <password>, + * <statement_type>, + * <sql statement 1> + * [ , <sql statements 2 ...n> ] )) ... + * + * The first 7 arguments are required and must be + * string literals that are available at compile + * time. + * Statement type: + * 'source': This statement produces a result + * (only type allowed at this time) + * (may support "target" to insert + * into a table via JDBC later) + * + * Note that only one of the SQL statements can be + * a select or other result-producing statements. + * The others can perform setup and cleanup + * operations, if necessary (e.g. create table, + * insert, select, drop table). + * + * For an example, see file + * core/sql/regress/udr/TEST002. + ***************************************************/ + +package org.trafodion.sql.udr.predef; + +import org.trafodion.sql.udr.*; +import java.sql.*; +import java.util.Vector; +import java.lang.Math; +import java.net.URL; +import java.net.URLClassLoader; +import java.nio.file.Path; +import java.nio.file.Paths; +import java.io.PrintStream; +import java.util.Properties; +import java.util.logging.Logger; + +class JDBCUDR extends UDR +{ + // class used to connect, both at compile and at runtime + static class JdbcConnectionInfo + { + String driverJar_; + String driverClassName_; + String connectionString_; + String username_; + String password_; + boolean debug_; + + Connection conn_; + + public void setJar(String jar) + { driverJar_ = jar; } + public void setClass(String className) + { driverClassName_ = className; } + public void setConnString(String connString) + { connectionString_ = connString; } + public void setUsername(String userName) + { username_ = userName; } + public void setPassword(String password) + { password_ = password; } + public void setDebug(boolean debug) { debug_ = debug; } + + public Connection connect() throws UDRException + { + try { + Path driverJarPath = Paths.get(driverJar_); + Path sandBoxPath = Paths.get(System.getenv("MY_SQROOT"), "udr", "external_libs"); + URLClassLoader jdbcJarLoader = null; + URL jarClassPath[] = new URL[1]; + + // for security reasons, we sandbox the allowed driver jars + // into $MY_SQROOT/export/lib/udr/external_libs + driverJarPath = driverJarPath.normalize(); + if (driverJarPath.isAbsolute()) + { + if (! driverJarPath.startsWith(sandBoxPath)) + throw new UDRException( + 38010, + "The jar name of the JDBC driver must be a name relative to %s, got %s", + System.getenv("MY_SQROOT")+"/udr/external_libs", + driverJar_); + } + else + driverJarPath = sandBoxPath.resolve(driverJarPath); + + // Create a class loader that can access the + // jar file specified by the caller. + jarClassPath[0] = driverJarPath.toUri().toURL(); + jdbcJarLoader = new URLClassLoader( + jarClassPath, + this.getClass().getClassLoader()); + + // go through an intermediary driver, since the DriverManager + // will not accept classes that are not loaded by the default + // class loader + Driver d = (Driver) Class.forName(driverClassName_, true, jdbcJarLoader).newInstance(); + DriverManager.registerDriver(new URLDriver(d)); + conn_ = DriverManager.getConnection(connectionString_, + username_, + password_); + return conn_; + } + catch(Exception e) { + if (debug_) + { + System.out.println("Debug: Exception during connect:"); + try { e.printStackTrace(System.out); } + catch (Exception e2) {} + } + throw new UDRException( + 38020, + "Exception during connect: %s", + e.getMessage()); + } + } + + public Connection getConnection() { return conn_; } + + public void disconnect() throws SQLException + { + conn_.close(); + conn_ = null; + } + }; + + // list of SQL statements to execute + static class SQLStatementInfo + { + // list of SQL statements to execute + Vector<String> sqlStrings_; + + // which of the above is the one that + // produces the table-valued result? + int resultStatementIndex_; + + // prepared result-producing statement + PreparedStatement resultStatement_; + + SQLStatementInfo() + { + sqlStrings_ = new Vector<String>(); + resultStatementIndex_ = -1; + } + + void addStatementText(String sqlText) + { + sqlStrings_.add(sqlText); + } + + void addResultProducingStatement(PreparedStatement preparedStmt, + int resultStatementIndex) + { + resultStatement_ = preparedStmt; + resultStatementIndex_ = resultStatementIndex; + } + + String getStatementText(int ix) { return sqlStrings_.get(ix); } + PreparedStatement getResultStatement(){ return resultStatement_; } + int getNumStatements() { return sqlStrings_.size(); } + int getResultStatementIndex() { return resultStatementIndex_; } + }; + + // Define data that gets passed between compiler phases + static class JdbcCompileTimeData extends UDRWriterCompileTimeData + { + JdbcConnectionInfo jci_; + SQLStatementInfo sqi_; + + JdbcCompileTimeData() + { + jci_ = new JdbcConnectionInfo(); + sqi_ = new SQLStatementInfo(); + } + }; + + static class URLDriver implements Driver { + private Driver driver_; + URLDriver(Driver d) { driver_ = d; } + public boolean acceptsURL(String u) throws SQLException { + return driver_.acceptsURL(u); + } + public Connection connect(String u, Properties p) throws SQLException { + return driver_.connect(u, p); + } + public int getMajorVersion() { + return driver_.getMajorVersion(); + } + public int getMinorVersion() { + return driver_.getMinorVersion(); + } + public DriverPropertyInfo[] getPropertyInfo(String u, Properties p) throws SQLException { + return driver_.getPropertyInfo(u, p); + } + public boolean jdbcCompliant() { + return driver_.jdbcCompliant(); + } + public Logger getParentLogger() throws SQLFeatureNotSupportedException { + return driver_.getParentLogger(); + } + } + + JdbcConnectionInfo getConnectionInfo(UDRInvocationInfo info) throws UDRException + { + return ((JdbcCompileTimeData) info.getUDRWriterCompileTimeData()).jci_; + } + + SQLStatementInfo getSQLStatementInfo(UDRInvocationInfo info) throws UDRException + { + return ((JdbcCompileTimeData) info.getUDRWriterCompileTimeData()).sqi_; + } + + + // default constructor + public JDBCUDR() + {} + + // a method to process the input parameters, this is + // used both at compile time and at runtime + private void handleInputParams(UDRInvocationInfo info, + JdbcConnectionInfo jci, + SQLStatementInfo sqi, + boolean isCompileTime) + throws UDRException + { + int numInParams = info.par().getNumColumns(); + + // Right now we don't support table inputs + if (isCompileTime && info.getNumTableInputs() != 0) + throw new UDRException( + 38300, + "%s must be called with no table-valued inputs", + info.getUDRName()); + + if (numInParams < 7) + throw new UDRException( + 38310, + "Expecting at least 7 parameters for %s UDR", + info.getUDRName()); + + // loop over scalar input parameters + for (int p=0; p<numInParams; p++) + { + if (isCompileTime && + ! info.par().isAvailable(p)) + throw new UDRException( + 38320, + "Parameter %d of %s must be a compile time constant", + p+1, + info.getUDRName()); + + String paramValue = info.par().getString(p); + + switch (p) + { + case 0: + jci.setJar(paramValue); + break; + + case 1: + jci.setClass(paramValue); + break; + + case 2: + jci.setConnString(paramValue); + break; + + case 3: + jci.setUsername(paramValue); + break; + + case 4: + jci.setPassword(paramValue); + break; + + case 5: + // Only statement type supported + // so far is select, we may support insert later + if (paramValue.compareToIgnoreCase("source") != 0) + throw new UDRException( + 38330, + "The only statement type supported so far is 'source' in parameter 6 of %s", + info.getUDRName()); + break; + + default: + // SQL statement (there could be multiple) + sqi.addStatementText(paramValue); + break; + + } + + if (isCompileTime) + // add the actual parameter as a formal parameter + // (the formal parameter list is initially empty) + info.addFormalParameter(info.par().getColumn(p)); + } + + jci.setDebug(info.getDebugFlags() != 0); + + // Prepare each provided statement. We will verify that + // only one of these statements produces result rows, + // which will become our table-valued output. + int numSQLStatements = sqi.getNumStatements(); + + // sanity check + if (numSQLStatements != numInParams-6) + throw new UDRException(383400, "internal error"); + + if (numSQLStatements < 1) + throw new UDRException(383500, "At least one SQL statement must be given in parameters 6 and following"); + + if (isCompileTime) + { + // walk through all statements, check whether they are + // valid by preparing them, and determine which one is + // the one that generates a result set + String currentStmtText = ""; + try + { + jci.connect(); + + for (int s=0; s<numSQLStatements; s++) + { + currentStmtText = sqi.getStatementText(s); + // System.out.printf("Statement to prepare: %s\n", currentStmtText); + PreparedStatement preparedStmt = + jci.getConnection().prepareStatement(currentStmtText); + // if (preparedStmt != null) + // System.out.printf("Prepare was successful\n"); + ParameterMetaData pmd = preparedStmt.getParameterMetaData(); + if (pmd != null && pmd.getParameterCount() != 0) + throw new UDRException( + 38360, + "Statement %s requires %d input parameters, which is not supported", + currentStmtText, pmd.getParameterCount()); + ResultSetMetaData desc = preparedStmt.getMetaData(); + + int numResultCols = desc.getColumnCount(); + // System.out.printf("Number of output columns: %d", numResultCols); + + if (numResultCols > 0) + { + if (sqi.getResultStatementIndex() >= 0) + throw new UDRException( + 38370, + "More than one of the statements provided produce output, this is not supported (%d and %d)", + sqi.getResultStatementIndex()+1, + s+1); + + // we found the statement that is producing the result + sqi.addResultProducingStatement(preparedStmt, s); + + // now add the output columns + for (int c=0; c<numResultCols; c++) + { + String colName = desc.getColumnLabel(c+1); + TypeInfo udrType = getUDRTypeFromJDBCType(desc, c+1); + info.out().addColumn(new ColumnInfo(colName, udrType)); + } + } + } + jci.disconnect(); + } + catch (SQLException e) + { + throw new UDRException( + 38380, + "SQL Exception when preparing SQL statement %s. Exception text: %s", + currentStmtText, e.getMessage()); + } + } + } + + TypeInfo getUDRTypeFromJDBCType(ResultSetMetaData desc, + int colNumOneBased) throws UDRException + { + TypeInfo result; + + final int maxLength = 100000; + + int colJDBCType; + + // the ingredients to make a UDR type and their default values + TypeInfo.SQLTypeCode sqlType = TypeInfo.SQLTypeCode.UNDEFINED_SQL_TYPE; + int length = 0; + boolean nullable = false; + int scale = 0; + TypeInfo.SQLCharsetCode charset = TypeInfo.SQLCharsetCode.CHARSET_UCS2; + TypeInfo.SQLIntervalCode intervalCode = TypeInfo.SQLIntervalCode.UNDEFINED_INTERVAL_CODE; + int precision = 0; + TypeInfo.SQLCollationCode collation = TypeInfo.SQLCollationCode.SYSTEM_COLLATION; + + try { + colJDBCType = desc.getColumnType(colNumOneBased); + nullable = (desc.isNullable(colNumOneBased) != ResultSetMetaData.columnNoNulls); + + // map the JDBC type to a Trafodion UDR parameter type + switch (colJDBCType) + { + case java.sql.Types.SMALLINT: + case java.sql.Types.TINYINT: + case java.sql.Types.BOOLEAN: + if (desc.isSigned(colNumOneBased)) + sqlType = TypeInfo.SQLTypeCode.SMALLINT; + else + sqlType = TypeInfo.SQLTypeCode.SMALLINT_UNSIGNED; + break; + + case java.sql.Types.INTEGER: + if (desc.isSigned(colNumOneBased)) + sqlType = TypeInfo.SQLTypeCode.INT; + else + sqlType = TypeInfo.SQLTypeCode.INT_UNSIGNED; + break; + + case java.sql.Types.BIGINT: + sqlType = TypeInfo.SQLTypeCode.LARGEINT; + break; + + case java.sql.Types.DECIMAL: + case java.sql.Types.NUMERIC: + if (desc.isSigned(colNumOneBased)) + sqlType = TypeInfo.SQLTypeCode.NUMERIC; + else + sqlType = TypeInfo.SQLTypeCode.NUMERIC_UNSIGNED; + precision = desc.getPrecision(colNumOneBased); + scale = desc.getScale(colNumOneBased); + break; + + case java.sql.Types.REAL: + sqlType = TypeInfo.SQLTypeCode.REAL; + break; + + case java.sql.Types.DOUBLE: + case java.sql.Types.FLOAT: + sqlType = TypeInfo.SQLTypeCode.DOUBLE_PRECISION; + break; + + case java.sql.Types.CHAR: + case java.sql.Types.NCHAR: + sqlType = TypeInfo.SQLTypeCode.CHAR; + length = Math.min(desc.getPrecision(colNumOneBased), maxLength); + charset = TypeInfo.SQLCharsetCode.CHARSET_UCS2; + break; + + case java.sql.Types.VARCHAR: + case java.sql.Types.NVARCHAR: + sqlType = TypeInfo.SQLTypeCode.VARCHAR; + length = Math.min(desc.getPrecision(colNumOneBased), maxLength); + charset = TypeInfo.SQLCharsetCode.CHARSET_UCS2; + break; + + case java.sql.Types.DATE: + sqlType = TypeInfo.SQLTypeCode.DATE; + break; + + case java.sql.Types.TIME: + sqlType = TypeInfo.SQLTypeCode.TIME; + break; + + case java.sql.Types.TIMESTAMP: + sqlType = TypeInfo.SQLTypeCode.TIMESTAMP; + scale = 3; + break; + + // BLOB - not supported yet, map to varchar + // case java.sql.Types.BLOB: + // sqlType = TypeInfo.SQLTypeCode.BLOB; + // break; + + // CLOB - not supported yet, map to varchar + // case java.sql.Types.CLOB: + // sqlType = TypeInfo.SQLTypeCode.CLOB; + // break; + + case java.sql.Types.ARRAY: + case java.sql.Types.BINARY: + case java.sql.Types.BIT: + case java.sql.Types.BLOB: + case java.sql.Types.DATALINK: + case java.sql.Types.DISTINCT: + case java.sql.Types.JAVA_OBJECT: + case java.sql.Types.LONGVARBINARY: + case java.sql.Types.NULL: + case java.sql.Types.OTHER: + case java.sql.Types.REF: + case java.sql.Types.STRUCT: + case java.sql.Types.VARBINARY: + // these types produce a binary result, represented + // as varchar(n) character set iso88591 + sqlType = TypeInfo.SQLTypeCode.VARCHAR; + length = Math.min(desc.getPrecision(colNumOneBased), maxLength); + charset = TypeInfo.SQLCharsetCode.CHARSET_ISO88591; + break; + + case java.sql.Types.LONGVARCHAR: + case java.sql.Types.LONGNVARCHAR: + case java.sql.Types.CLOB: + case java.sql.Types.NCLOB: + case java.sql.Types.ROWID: + case java.sql.Types.SQLXML: + // these types produce a varchar(n) character set utf8 result + sqlType = TypeInfo.SQLTypeCode.VARCHAR; + length = Math.min(desc.getPrecision(colNumOneBased), maxLength); + charset = TypeInfo.SQLCharsetCode.CHARSET_UCS2; + break; + } + } catch (SQLException e) { + throw new UDRException( + 38500, + "Error determinging the type of output column %d: ", + colNumOneBased, + e.getMessage()); + } + + result = new TypeInfo( + sqlType, + length, + nullable, + scale, + charset, + intervalCode, + precision, + collation); + + return result; + } + + // determine output columns dynamically at compile time + @Override + public void describeParamsAndColumns(UDRInvocationInfo info) + throws UDRException + { + // create an object with common info for this + // UDF invocation that we will carry through the + // compilation phases + info.setUDRWriterCompileTimeData(new JdbcCompileTimeData()); + + // retrieve the compile time data, we will do this for + // every compile phase + JdbcConnectionInfo jci = getConnectionInfo(info); + SQLStatementInfo sqi = getSQLStatementInfo(info); + + // process input parameters + handleInputParams(info, jci, sqi, true); + } + + // override the runtime method + @Override + public void processData(UDRInvocationInfo info, + UDRPlanInfo plan) + throws UDRException + { + // retrieve the compile time data, we will do this for + // every compile phase + JdbcConnectionInfo jci = new JdbcConnectionInfo(); + SQLStatementInfo sqi = new SQLStatementInfo(); + int numCols = info.out().getNumColumns(); + + // process input parameters (again, now at runtime) + handleInputParams(info, jci, sqi, false); + + int numSQLStatements = sqi.getNumStatements(); + int numSQLResultSets = 0; + String stmtText = null; + + try { + Connection conn = jci.connect(); + Statement stmt = conn.createStatement(); + + for (int s=0; s<numSQLStatements; s++) + { + stmtText = sqi.getStatementText(s); + + boolean hasResultSet = stmt.execute(stmtText); + + if (hasResultSet) + { + ResultSet rs = stmt.getResultSet(); + numSQLResultSets++; + + if (numSQLResultSets > 1) + throw new UDRException( + 38700, + "More than one result set returned by UDF %s", + info.getUDRName()); + + if (rs.getMetaData().getColumnCount() != numCols) + throw new UDRException( + 38702, + "Number of columns returned by UDF %s (%d) differs from the number determined at compile time (%d)", + info.getUDRName(), + rs.getMetaData().getColumnCount(), + numCols); + + while (rs.next()) + { + for (int c=0; c<numCols; c++) + { + TypeInfo typ = info.out().getColumn(c).getType(); + + switch (typ.getSQLTypeSubClass()) + { + case FIXED_CHAR_TYPE: + case VAR_CHAR_TYPE: + info.out().setString(c, rs.getString(c+1)); + break; + + case EXACT_NUMERIC_TYPE: + info.out().setLong(c, rs.getLong(c+1)); + break; + + case APPROXIMATE_NUMERIC_TYPE: + info.out().setDouble(c, rs.getDouble(c+1)); + break; + + case DATE_TYPE: + info.out().setTime(c, rs.getDate(c+1)); + break; + + case TIME_TYPE: + info.out().setTime(c, rs.getTime(c+1)); + break; + + case TIMESTAMP_TYPE: + info.out().setTime(c, rs.getTimestamp(c+1)); + break; + + case LOB_SUB_CLASS: + throw new UDRException(38710, "LOB parameters not yet supported"); + + default: + throw new UDRException(38720, "Unexpected data type encountered"); + + } // switch + + if (rs.wasNull()) + info.out().setNull(c); + } // loop over columns + + // produce a result row + emitRow(info); + + } // loop over result rows + } // statement produces a result set + } // loop over statements + jci.disconnect(); + } catch (SQLException e) { + throw new UDRException( + 38730, + "Error preparing statement %s at runtime: %s", + stmtText, + e.getMessage()); + } + } +};
