P3
Need to change the methods and constants for getFunctions/getFunctionParameters including the name of getFunctionParameters to getFunctionColumns.
Reasons for the change are:
- The main functionality we’re looking for is the ability to get metadata on the result set returned by a function, the so called “table functions”.
Today we don’t have a way to retrieve this through the JDBC driver, basically this implies the user has to specify the information. Clearly the more complex the result sets are and the more table functions one uses, the more this becomes a usability issue.
# Why does getFunctions only allow to return “user functions”, and not the built-in ones of the database?
XQuery has a large set of built-in functions, and we are able to translate most into one of more SQL functions, sometimes using JDBC escapes or through database-specific functions. But of course, although pretty extensive, the XQuery built-in functions have their own limitations. Wouldn’t it be nice if one could use his favorite SQL function in XQuery?
DDXQ has the ability to call any function available in your database. Today you need to declare an “external function” in the xquery, we need this to determine the datatypes of the various parameters and function result.
Let’s say that one wants to use the SQL function RTRIM, the declaration specified in the query would be something as follows:
declare function ddtek-sql:rtrim($inp as xs:string) as xs:string external;
And subsequently used as follows
<result>{ddtek-sql:rtrim(“abc “)}</result>
So far so good, as a convenience for the user we would like to remove the requirement to declare the external function, and determine the needed information automatically.
At a first sight this seems to be possible using getFunctions and getFunctionParameters, but unfortunately these functions only seem to support “user functions”, not the built-in functions of the database?
# A detail…
getFunctions returns both a FUNCTION_NAME and a SPECIFIC_NAME
This feedback came from the folks working on a JSR225 implementation
The impact is extremely low as these methods are new for JDBC 4.0
The changes look like:
functionColumnUnknown
static final int functionColumnUnknown
Indicates that type of the parameter or column is unknown.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionColumnIn
static final int functionColumnIn
Indicates that the parameter or column is an IN parameter.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionColumnInOut
static final int functionColumnInOut
Indicates that the parameter or column is an INOUT parameter.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionColumnOut
static final int functionColumnOut
Indicates that the parameter or column is an OUT parameter.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionReturn
static final int functionReturn
Indicates that the parameter or column is a return value.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionColumnResult
static final int functionColumnResult
Indicates that the parameter or column is a column in a result set.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionNoNulls
static final int functionNoNulls
Indicates that NULL values are not allowed.
A possible value for the column NULLABLE in the ResultSet object returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionNullable
static final int functionNullable
Indicates that NULL values are allowed.
A possible value for the column NULLABLE in the ResultSet object returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionNullableUnknown
static final int functionNullableUnknown
Indicates that whether NULL values are allowed is unknown.
A possible value for the column NULLABLE in the ResultSet object returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionResultUnknown
static final int functionResultUnknown
Indicates that it is not known whether the function returns a result or a table.
A possible value for column FUNCTION_TYPE in the ResultSet object returned by the method getFunctions.
Since:
1.6
See Also:
Constant Field Values
functionNoTable
static final int functionNoTable
Indicates that the function does not return a table.
A possible value for column FUNCTION_TYPE in the ResultSet object returned by the method getFunctions.
Since:
1.6
See Also:
Constant Field Values
functionReturnsTable
static final int functionReturnsTable
Indicates that the function returns a table.
A possible value for column FUNCTION_TYPE in the ResultSet object returned by the method getFunctions.
Since:
1.6
See Also:
Constant Field Values
getFunctions
ResultSet getFunctions(java.lang.String catalog,
java.lang.String schemaPattern,
java.lang.String functionNamePattern)
throws SQLException
Retrieves a description of the system and user functions available in the given catalog.
Only system and user function descriptions matching the schema and function name criteria are returned. They are ordered by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME and SPECIFIC_ NAME.
Each function description has the the following columns:
1. FUNCTION_CAT String => function catalog (may be null)
2. FUNCTION_SCHEM String => function schema (may be null)
3. FUNCTION_NAME String => function name. This is the name used to invoke the function
4. REMARKS String => explanatory comment on the function
5. FUNCTION_TYPE short => kind of function:
* functionResultUnknown - Cannot determine if a return value or table will be returned
* functionNoTable- Does not return a table
* functionReturnsTable - Returns a table
6. SPECIFIC_NAME String => the name which uniquely identifies this function within its schema. This is a user specified, or DBMS generated, name that may be different then the FUNCTION_NAME for example with overload functions
A user may not have permission to execute any of the functions that are returned by getFunctions
Parameters:
catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
functionNamePattern - a function name pattern; must match the function name as it is stored in the database
Returns:
ResultSet - each row is a function description
Throws:
SQLException - if a database access error occurs
Since:
1.6
See Also:
getSearchStringEscape()
getFunctionColumns
ResultSet getFunctionColumns(java.lang.String catalog,
java.lang.String schemaPattern,
java.lang.String functionNamePattern,
java.lang.String parameterNamePattern)
throws SQLException
Retrieves a description of the given catalog's system or user function parameters and return type.
Only descriptions matching the schema, function and parameter name criteria are returned. They are ordered by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME and SPECIFIC_ NAME. Within this, the return value, if any, is first. Next are the parameter descriptions in call order. The column descriptions follow in column number order.
Each row in the ResultSet is a parameter description, column description or return type description with the following fields:
1. FUNCTION_CAT String => function catalog (may be null)
2. FUNCTION_SCHEM String => function schema (may be null)
3. FUNCTION_NAME String => function name.. This is the name used to invoke the function
4. COLUMN_NAME String => column/parameter name
5. COLUMN_TYPE Short => kind of column/parameter:
* functionColumnUnknown - nobody knows
* functionColumnIn - IN parameter
* functionColumnInOut - INOUT parameter
* functionColumnOut - OUT parameter
* functionColumnReturn - function return value
* functionColumnResult - Indicates that the parameter or column is a column in the ResultSet
6. DATA_TYPE int => SQL type from java.sql.Types
7. TYPE_NAME String => SQL type name, for a UDT type the type name is fully qualified
8. PRECISION int => precision
9. LENGTH int => length in bytes of data
10. SCALE short => scale - null is returned for data types where SCALE is not applicable.
11. RADIX short => radix
12. NULLABLE short => can it contain NULL.
* functionNoNulls - does not allow NULL values
* functionNullable - allows NULL values
* functionNullableUnknown - nullability unknown
13. REMARKS String => comment describing column/parameter
14. CHAR_OCTET_LENGTH int => the maximum length of binary and character based parameters or columns. For any other datatype the returned value is a NULL
15. ORDINAL_POSITION int => the ordinal position, starting from 1, for the input and output parameters. A value of 0 is returned if this row describes the function's return value. For result set columns, it is the ordinal position of the column in the result set starting from 1.
16. IS_NULLABLE String => ISO rules are used to determine the nullability for a parameter or column.
* YES --- if the parameter or column can include NULLs
* NO --- if the parameter or column cannot include NULLs
* empty string --- if the nullability for the parameter or column is unknown
17. SPECIFIC_NAME String => the name which uniquely identifies this function within its schema. This is a user specified, or DBMS generated, name that may be different then the FUNCTION_NAME for example with overload functions
The PRECISION column represents the specified column size for the given parameter or column. For numeric data, this is the maximum precision. For character data, this is the length in characters. For datetime datatypes, this is the length in characters of the String representation (assuming the maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype, this is the length in bytes. Null is returned for data types where the column size is not applicable.
Parameters:
catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
functionNamePattern - a procedure name pattern; must match the function name as it is stored in the database
parameterNamePattern - a parameter name pattern; must match the parameter or column name as it is stored in the database
Returns:
ResultSet - each row describes a user function parameter, column or return type
Throws:
SQLException - if a database access error occurs
Since:
1.6
See Also:
getSearchStringEscape()
Reasons for the change are:
- The main functionality we’re looking for is the ability to get metadata on the result set returned by a function, the so called “table functions”.
Today we don’t have a way to retrieve this through the JDBC driver, basically this implies the user has to specify the information. Clearly the more complex the result sets are and the more table functions one uses, the more this becomes a usability issue.
# Why does getFunctions only allow to return “user functions”, and not the built-in ones of the database?
XQuery has a large set of built-in functions, and we are able to translate most into one of more SQL functions, sometimes using JDBC escapes or through database-specific functions. But of course, although pretty extensive, the XQuery built-in functions have their own limitations. Wouldn’t it be nice if one could use his favorite SQL function in XQuery?
DDXQ has the ability to call any function available in your database. Today you need to declare an “external function” in the xquery, we need this to determine the datatypes of the various parameters and function result.
Let’s say that one wants to use the SQL function RTRIM, the declaration specified in the query would be something as follows:
declare function ddtek-sql:rtrim($inp as xs:string) as xs:string external;
And subsequently used as follows
<result>{ddtek-sql:rtrim(“abc “)}</result>
So far so good, as a convenience for the user we would like to remove the requirement to declare the external function, and determine the needed information automatically.
At a first sight this seems to be possible using getFunctions and getFunctionParameters, but unfortunately these functions only seem to support “user functions”, not the built-in functions of the database?
# A detail…
getFunctions returns both a FUNCTION_NAME and a SPECIFIC_NAME
This feedback came from the folks working on a JSR225 implementation
The impact is extremely low as these methods are new for JDBC 4.0
The changes look like:
functionColumnUnknown
static final int functionColumnUnknown
Indicates that type of the parameter or column is unknown.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionColumnIn
static final int functionColumnIn
Indicates that the parameter or column is an IN parameter.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionColumnInOut
static final int functionColumnInOut
Indicates that the parameter or column is an INOUT parameter.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionColumnOut
static final int functionColumnOut
Indicates that the parameter or column is an OUT parameter.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionReturn
static final int functionReturn
Indicates that the parameter or column is a return value.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionColumnResult
static final int functionColumnResult
Indicates that the parameter or column is a column in a result set.
A possible value for the column COLUMN_TYPE in the ResultSet returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionNoNulls
static final int functionNoNulls
Indicates that NULL values are not allowed.
A possible value for the column NULLABLE in the ResultSet object returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionNullable
static final int functionNullable
Indicates that NULL values are allowed.
A possible value for the column NULLABLE in the ResultSet object returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionNullableUnknown
static final int functionNullableUnknown
Indicates that whether NULL values are allowed is unknown.
A possible value for the column NULLABLE in the ResultSet object returned by the method getFunctionColumns.
Since:
1.6
See Also:
Constant Field Values
functionResultUnknown
static final int functionResultUnknown
Indicates that it is not known whether the function returns a result or a table.
A possible value for column FUNCTION_TYPE in the ResultSet object returned by the method getFunctions.
Since:
1.6
See Also:
Constant Field Values
functionNoTable
static final int functionNoTable
Indicates that the function does not return a table.
A possible value for column FUNCTION_TYPE in the ResultSet object returned by the method getFunctions.
Since:
1.6
See Also:
Constant Field Values
functionReturnsTable
static final int functionReturnsTable
Indicates that the function returns a table.
A possible value for column FUNCTION_TYPE in the ResultSet object returned by the method getFunctions.
Since:
1.6
See Also:
Constant Field Values
getFunctions
ResultSet getFunctions(java.lang.String catalog,
java.lang.String schemaPattern,
java.lang.String functionNamePattern)
throws SQLException
Retrieves a description of the system and user functions available in the given catalog.
Only system and user function descriptions matching the schema and function name criteria are returned. They are ordered by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME and SPECIFIC_ NAME.
Each function description has the the following columns:
1. FUNCTION_CAT String => function catalog (may be null)
2. FUNCTION_SCHEM String => function schema (may be null)
3. FUNCTION_NAME String => function name. This is the name used to invoke the function
4. REMARKS String => explanatory comment on the function
5. FUNCTION_TYPE short => kind of function:
* functionResultUnknown - Cannot determine if a return value or table will be returned
* functionNoTable- Does not return a table
* functionReturnsTable - Returns a table
6. SPECIFIC_NAME String => the name which uniquely identifies this function within its schema. This is a user specified, or DBMS generated, name that may be different then the FUNCTION_NAME for example with overload functions
A user may not have permission to execute any of the functions that are returned by getFunctions
Parameters:
catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
functionNamePattern - a function name pattern; must match the function name as it is stored in the database
Returns:
ResultSet - each row is a function description
Throws:
SQLException - if a database access error occurs
Since:
1.6
See Also:
getSearchStringEscape()
getFunctionColumns
ResultSet getFunctionColumns(java.lang.String catalog,
java.lang.String schemaPattern,
java.lang.String functionNamePattern,
java.lang.String parameterNamePattern)
throws SQLException
Retrieves a description of the given catalog's system or user function parameters and return type.
Only descriptions matching the schema, function and parameter name criteria are returned. They are ordered by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME and SPECIFIC_ NAME. Within this, the return value, if any, is first. Next are the parameter descriptions in call order. The column descriptions follow in column number order.
Each row in the ResultSet is a parameter description, column description or return type description with the following fields:
1. FUNCTION_CAT String => function catalog (may be null)
2. FUNCTION_SCHEM String => function schema (may be null)
3. FUNCTION_NAME String => function name.. This is the name used to invoke the function
4. COLUMN_NAME String => column/parameter name
5. COLUMN_TYPE Short => kind of column/parameter:
* functionColumnUnknown - nobody knows
* functionColumnIn - IN parameter
* functionColumnInOut - INOUT parameter
* functionColumnOut - OUT parameter
* functionColumnReturn - function return value
* functionColumnResult - Indicates that the parameter or column is a column in the ResultSet
6. DATA_TYPE int => SQL type from java.sql.Types
7. TYPE_NAME String => SQL type name, for a UDT type the type name is fully qualified
8. PRECISION int => precision
9. LENGTH int => length in bytes of data
10. SCALE short => scale - null is returned for data types where SCALE is not applicable.
11. RADIX short => radix
12. NULLABLE short => can it contain NULL.
* functionNoNulls - does not allow NULL values
* functionNullable - allows NULL values
* functionNullableUnknown - nullability unknown
13. REMARKS String => comment describing column/parameter
14. CHAR_OCTET_LENGTH int => the maximum length of binary and character based parameters or columns. For any other datatype the returned value is a NULL
15. ORDINAL_POSITION int => the ordinal position, starting from 1, for the input and output parameters. A value of 0 is returned if this row describes the function's return value. For result set columns, it is the ordinal position of the column in the result set starting from 1.
16. IS_NULLABLE String => ISO rules are used to determine the nullability for a parameter or column.
* YES --- if the parameter or column can include NULLs
* NO --- if the parameter or column cannot include NULLs
* empty string --- if the nullability for the parameter or column is unknown
17. SPECIFIC_NAME String => the name which uniquely identifies this function within its schema. This is a user specified, or DBMS generated, name that may be different then the FUNCTION_NAME for example with overload functions
The PRECISION column represents the specified column size for the given parameter or column. For numeric data, this is the maximum precision. For character data, this is the length in characters. For datetime datatypes, this is the length in characters of the String representation (assuming the maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype, this is the length in bytes. Null is returned for data types where the column size is not applicable.
Parameters:
catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
functionNamePattern - a procedure name pattern; must match the function name as it is stored in the database
parameterNamePattern - a parameter name pattern; must match the parameter or column name as it is stored in the database
Returns:
ResultSet - each row describes a user function parameter, column or return type
Throws:
SQLException - if a database access error occurs
Since:
1.6
See Also:
getSearchStringEscape()