*
The moose likes JDBC and the fly likes OracleDatabaseMetaData Typeinfo query getting fired Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "OracleDatabaseMetaData Typeinfo query getting fired" Watch "OracleDatabaseMetaData Typeinfo query getting fired" New topic
Author

OracleDatabaseMetaData Typeinfo query getting fired

Shail Rathi
Greenhorn

Joined: Dec 03, 2008
Posts: 2
I am using com.ibm.db.beans.DBSelect.

following is code snippet.

public SQLUtilitiesAnswerSelect() {
super();

initializer();
}

protected void initializer() {
select = new DBSelect();
try {
select.setConnectionSpec(SurveyPortlet.connectionObj);
select.setCommand("SELECT ANSWER_ID, ANSWER_DESC, ANSWER_ORDER, IS_DEFAULT, PERCENT_WEIGHT FROM ANSWERMASTER WHERE QUESTION_ID =:questionId ORDER BY ANSWER_ORDER asc");

DBParameterMetaData parmMetaData = select.getParameterMetaData();
parmMetaData.setParameter(1,"questionId",java.sql.DatabaseMetaData.procedureColumnIn,java.sql.Types.INTEGER,Integer.class);
} catch (SQLException ex) {
LoggerUtils.error("################### SQLException in method SQLUtilitiesAnswerSelect.initializer : ",ex);

}
catch (Exception e) {
LoggerUtils.error("################### Exception in method SQLUtilitiesAnswerSelect.initializer : ",e);

}
}

public void execute(int questionId) throws SQLException {
try {
select.setParameter("questionId",new Integer(questionId));

select.execute();
}
catch (SQLException ex) {
LoggerUtils.error("################### SQLException in method SQLUtilitiesAnswerSelect.execute(questionId) : ",ex);
}
catch (Exception e) {
LoggerUtils.error("################### Exception in method SQLUtilitiesAnswerSelect.execute(questionId) : ",e);
}
// Free resources of select object.
finally {
select.close();
}


In my code there are many such utilities files.

When every I user execute statement, it fire following ugly query.

-----------------------------------------------------------------------------

SELECT 'NUMBER' AS type_name, 2 AS data_type, 38 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 1 AS fixed_prec_scale,
0 AS auto_increment, 'NUMBER' AS local_type_name,
-84 AS minimum_scale, 127 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'CHAR' AS type_name, 1 AS data_type, 2000 AS PRECISION,
'''' AS literal_prefix, '''' AS literal_suffix,
NULL AS create_params, 1 AS nullable, 1 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'CHAR' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'VARCHAR2' AS type_name, 12 AS data_type, 4000 AS PRECISION,
'''' AS literal_prefix, '''' AS literal_suffix,
NULL AS create_params, 1 AS nullable, 1 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'VARCHAR2' AS local_type_name,
0 AS minimum_scale, 0 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'DATE' AS type_name, 91 AS data_type, 7 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'DATE' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'DATE' AS type_name, 92 AS data_type, 7 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'DATE' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'TIMESTAMP' AS type_name, 93 AS data_type, 11 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'TIMESTAMP' AS local_type_name,
0 AS minimum_scale, 0 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'TIMESTAMP WITH TIME ZONE' AS type_name, -101 AS data_type,
13 AS PRECISION, NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'TIMESTAMP WITH TIME ZONE' AS local_type_name,
0 AS minimum_scale, 0 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'TIMESTAMP WITH LOCAL TIME ZONE' AS type_name, -102 AS data_type,
11 AS PRECISION, NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment,
'TIMESTAMP WITH LOCAL TIME ZONE' AS local_type_name,
0 AS minimum_scale, 0 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'INTERVALYM' AS type_name, -103 AS data_type, 5 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'INTERVALYM' AS local_type_name,
0 AS minimum_scale, 0 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'INTERVALDS' AS type_name, -104 AS data_type, 4 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'INTERVALDS' AS local_type_name,
0 AS minimum_scale, 0 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'RAW' AS type_name, -3 AS data_type, 2000 AS PRECISION,
'''' AS literal_prefix, '''' AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'RAW' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'LONG' AS type_name, -1 AS data_type, 2147483647 AS PRECISION,
'''' AS literal_prefix, '''' AS literal_suffix,
NULL AS create_params, 1 AS nullable, 1 AS case_sensitive,
0 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'LONG' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'LONG RAW' AS type_name, -4 AS data_type, 2147483647 AS PRECISION,
'''' AS literal_prefix, '''' AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
0 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'LONG RAW' AS local_type_name,
0 AS minimum_scale, 0 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'NUMBER' AS type_name, -7 AS data_type, 1 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
'(1)' AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 1 AS fixed_prec_scale,
0 AS auto_increment, 'NUMBER' AS local_type_name,
-84 AS minimum_scale, 127 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'NUMBER' AS type_name, -6 AS data_type, 3 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
'(3)' AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 1 AS fixed_prec_scale,
0 AS auto_increment, 'NUMBER' AS local_type_name,
-84 AS minimum_scale, 127 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'NUMBER' AS type_name, 5 AS data_type, 5 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
'(5)' AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 1 AS fixed_prec_scale,
0 AS auto_increment, 'NUMBER' AS local_type_name,
-84 AS minimum_scale, 127 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'NUMBER' AS type_name, 4 AS data_type, 10 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
'(10)' AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 1 AS fixed_prec_scale,
0 AS auto_increment, 'NUMBER' AS local_type_name,
-84 AS minimum_scale, 127 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'NUMBER' AS type_name, -5 AS data_type, 38 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 1 AS fixed_prec_scale,
0 AS auto_increment, 'NUMBER' AS local_type_name,
-84 AS minimum_scale, 127 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'FLOAT' AS type_name, 6 AS data_type, 63 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 1 AS fixed_prec_scale,
0 AS auto_increment, 'FLOAT' AS local_type_name,
-84 AS minimum_scale, 127 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'REAL' AS type_name, 7 AS data_type, 63 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
3 AS searchable, 0 AS unsigned_attribute, 1 AS fixed_prec_scale,
0 AS auto_increment, 'REAL' AS local_type_name, -84 AS minimum_scale,
127 AS maximum_scale, NULL AS sql_data_type,
NULL AS sql_datetime_sub, 10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'BLOB' AS type_name, 2004 AS data_type, 4294967295 AS PRECISION,
NULL AS literal_prefix, NULL AS literal_suffix,
NULL AS create_params, 1 AS nullable, 0 AS case_sensitive,
0 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'BLOB' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'CLOB' AS type_name, 2005 AS data_type, 4294967295 AS PRECISION,
'''' AS literal_prefix, '''' AS literal_suffix,
NULL AS create_params, 1 AS nullable, 1 AS case_sensitive,
0 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'CLOB' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'REF' AS type_name, 2006 AS data_type, 0 AS PRECISION,
'''' AS literal_prefix, '''' AS literal_suffix,
NULL AS create_params, 1 AS nullable, 1 AS case_sensitive,
0 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'REF' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'ARRAY' AS type_name, 2003 AS data_type, 0 AS PRECISION,
'''' AS literal_prefix, '''' AS literal_suffix,
NULL AS create_params, 1 AS nullable, 1 AS case_sensitive,
0 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'ARRAY' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
UNION
SELECT 'STRUCT' AS type_name, 2002 AS data_type, 0 AS PRECISION,
'''' AS literal_prefix, '''' AS literal_suffix,
NULL AS create_params, 1 AS nullable, 1 AS case_sensitive,
0 AS searchable, 0 AS unsigned_attribute, 0 AS fixed_prec_scale,
0 AS auto_increment, 'STRUCT' AS local_type_name, 0 AS minimum_scale,
0 AS maximum_scale, NULL AS sql_data_type, NULL AS sql_datetime_sub,
10 AS num_prec_radix
FROM DUAL
ORDER BY data_type;
-----------------------------------------------------------------------------------
Output of this query is all available datatypes in oracle with its parameters.

My question is does implementation of DBSelect makes this call or getParameterMetadata is culprit?

Or there is some problem with implementation of JDBC driver.

I am using WebSphere Portal server v6.0 and Oracle 10g as backend.

Any help would be greatly appreciated.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: OracleDatabaseMetaData Typeinfo query getting fired