You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by hz...@apache.org on 2015/12/11 00:23:13 UTC
[3/7] incubator-trafodion git commit: [TRAFODION-1581] TMUDF for JDBC
queries
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());
+ }
+ }
+};