First thanks for this great library. We use it in production
environnement instead of MS JDBC connector.
We use it also to generate code from meta-data informations.
With custom program using Velocity Apache Template engine.
But we have a problem. Meta informations from jTDS are very poor.
I want to submit a request.
Our program need a complete resultset get from : DatabaseMetaData.getColumns()
Actually jTDS get information from 'sp_columns' doesn't return
'IS_AUTOINCREMENT' information.
But a select like this one would be more usefull and is fully
compliant with JDBC standard (specialy with specific MS SQL Server
data type)
-- tested on SQL SERVER 8.0.2039
-- tested on SQL SERVER 9.0.4035
SELECT inf.TABLE_CATALOG AS TABLE_CAT,
inf.TABLE_SCHEMA AS TABLE_SCHEMA,
inf.TABLE_NAME AS TABLE_NAME,
inf.COLUMN_NAME AS COLUMN_NAME,
CASE inf.DATA_TYPE
WHEN 'bigint' THEN -5 -- BIGINT
WHEN 'timestamp' THEN -2 -- BINARY
WHEN 'binary' THEN -2 --
WHEN 'bit' THEN -7 -- BIT
WHEN 'char' THEN 1 -- CHAR
WHEN 'decimal' THEN 3 -- DECIMAL
WHEN 'money' THEN 3 --
WHEN 'smallmoney' THEN 3 --
WHEN 'float' THEN 8 -- DOUBLE
WHEN 'int' THEN 4 -- INTEGER
WHEN 'image' THEN -4 -- LONGVARBINARY
WHEN 'varbinary(max)' THEN -4 --
WHEN 'varchar(max)' THEN 12 -- LONGVARCHAR
WHEN 'text' THEN -1 --
WHEN 'nchar' THEN -15 -- NCHAR (Java SE 6.0)
WHEN 'nvarchar' THEN -9 -- NVARCHAR (Java SE 6.0)
WHEN 'ntext' THEN -16 -- LONGNVARCHAR (Java SE 6.0)
WHEN 'numeric' THEN 2 -- NUMERIC
WHEN 'real' THEN 7 -- REAL
WHEN 'smallint' THEN 5 -- SMALLINT
WHEN 'datetime' THEN 93 -- TIMESTAMP
WHEN 'smalldatetime' THEN 93 --
WHEN 'varbinary' THEN -3 -- VARBINARY
WHEN 'udt' THEN -3 --
WHEN 'varchar' THEN 12 -- VARCHAR
WHEN 'tinyint' THEN -6 -- TINYINT
WHEN 'uniqueidentifier' THEN 1 -- CHAR
WHEN 'xml' THEN 2009 -- SQLXML (Java SE 6.0)
ELSE 1111 -- 'OTHER'
END AS DATA_TYPE,
inf.DATA_TYPE AS TYPE_NAME,
-1 AS COLUMN_SIZE,
-1 AS BUFFER_LENGTH,
inf.NUMERIC_SCALE AS DECIMAL_DIGITS,
inf.NUMERIC_PRECISION_RADIX AS NUM_PREC_RADIX,
CASE inf.IS_NULLABLE
WHEN 'NO' THEN 0
WHEN 'YES' THEN 1
ELSE 2
END AS NULLABLE,
NULL AS REMARKS,
inf.COLUMN_DEFAULT AS COLUMN_DEF,
-1 AS SQL_DATA_TYPE,
-1 AS SQL_DATETIME_SUB,
inf.CHARACTER_OCTET_LENGTH AS CHAR_OCTET_LENGTH,
inf.ORDINAL_POSITION AS ORDINAL_POSITION,
CASE inf.IS_NULLABLE
WHEN 'No' THEN 'NO'
WHEN 'YES' THEN 'YES'
ELSE NULL
END as IS_NULLABLE,
NULL AS SCOPE_CATLOG,
NULL AS SCOPE_SCHEMA,
NULL AS SCOPE_TABLE,
NULL AS SOURCE_DATA_TYPE,
CASE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity')
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
ELSE ''
END AS IS_AUTOINCREMENT
FROM INFORMATION_SCHEMA.COLUMNS AS inf
IS_AUTOINCREMENT information is required for our program.
Can we modify DatabaseMetaDataImpl to use this one ?
Thanks for your feedback. There is a simple reason for returning an
"incomplete" resultset: jTDS is a JDBC 3 driver and the columns you expect
have not been defined in that version.
That being said, I do not see a good reason for not including metadata columns
of later JDBC versions at first glance. I'll take a closer look at your
proposal. For the time being I added feature request linking to this forum.
Cheers,
momo
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello world,
First thanks for this great library. We use it in production
environnement instead of MS JDBC connector.
We use it also to generate code from meta-data informations.
With custom program using Velocity Apache Template engine.
But we have a problem. Meta informations from jTDS are very poor.
I want to submit a request.
Our program need a complete resultset get from : DatabaseMetaData.getColumns()
Actually jTDS get information from 'sp_columns' doesn't return
'IS_AUTOINCREMENT' information.
But a select like this one would be more usefull and is fully
compliant with JDBC standard (specialy with specific MS SQL Server
data type)
IS_AUTOINCREMENT information is required for our program.
Can we modify DatabaseMetaDataImpl to use this one ?
: http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getCol
umns(java.lang.String,java.lang.String,java.lang.String,java.lang.String)
Thanks for your feedback. There is a simple reason for returning an
"incomplete" resultset: jTDS is a JDBC 3 driver and the columns you expect
have not been defined in that version.
That being said, I do not see a good reason for not including metadata columns
of later JDBC versions at first glance. I'll take a closer look at your
proposal. For the time being I added feature request linking to this forum.
Cheers,
momo