Column information from DatabaseMetaData and ResultSetMetaData is inconsistent

[GoogleCodeExporter]

The DatabaseMetaData and ReslutSetMetaData for a tables columns report 
different types and type names. This causes problems in some products for 
example Cognos tools, forcing the user to use CAST which is a major 
inconvenience.

What steps will reproduce the problem?
1. create a table similar to this 
CREATE TABLE order_details (ORDER_DETAIL_CODE INTEGER,ORDER_NUMBER 
INTEGER,SHIP_DATE TIMESTAMP,PRODUCT_NUMBER INTEGER,PROMOTION_CODE 
INTEGER,QUANTITY INTEGER,UNIT_COST DECIMAL(19, 2),UNIT_PRICE DECIMAL(19, 
2),UNIT_SALE_PRICE DECIMAL(19, 2))

2. insert some values

3. Write a small java program to both get DatabaseMetaData and 
ResultSetMetaData and compare the 2.

What is the expected output? What do you see instead?
I expect both should be the same, but I get this (data type is translated 
to string in my test program)

--- DATABASE METADATA ---
Column=ORDER_DETAIL_CODE, Data Type=INTEGER, Type Name=INTEGER
Column=ORDER_NUMBER, Data Type=INTEGER, Type Name=INTEGER
Column=SHIP_DATE, Data Type=VARCHAR, Type Name=TIMESTAMP
Column=PRODUCT_NUMBER, Data Type=INTEGER, Type Name=INTEGER
Column=PROMOTION_CODE, Data Type=INTEGER, Type Name=INTEGER
Column=QUANTITY, Data Type=INTEGER, Type Name=INTEGER
Column=UNIT_COST, Data Type=VARCHAR, Type Name=DECIMAL(19, 2)
Column=UNIT_PRICE, Data Type=VARCHAR, Type Name=DECIMAL(19, 2)
Column=UNIT_SALE_PRICE, Data Type=VARCHAR, Type Name=DECIMAL(19, 2)

--- RESULTSET METADATA ---
Column=ORDER_DETAIL_CODE, Data Type=INTEGER, Type Name=integer
Column=ORDER_NUMBER, Data Type=INTEGER, Type Name=integer
Column=SHIP_DATE, Data Type=VARCHAR, Type Name=text
Column=PRODUCT_NUMBER, Data Type=INTEGER, Type Name=integer
Column=PROMOTION_CODE, Data Type=INTEGER, Type Name=integer
Column=QUANTITY, Data Type=INTEGER, Type Name=integer
Column=UNIT_COST, Data Type=FLOAT, Type Name=float
Column=UNIT_PRICE, Data Type=FLOAT, Type Name=float
Column=UNIT_SALE_PRICE, Data Type=FLOAT, Type Name=float

The DatabaseMetaData reports the wrong Data Type, the ResultSetMetaData is 
better.

What version of the product are you using? On what operating system?
3.6.20.1
Windows XP Home SP3

Please provide any additional information below.

Original issue reported on code.google.com by thecatat...@gmail.com on 28 May 2010 at 6:23

[GoogleCodeExporter]

tested also with 3.6.23.1-SNAPSHOT, and result is the same

Original comment by thecatat...@gmail.com on 28 May 2010 at 6:32

[GoogleCodeExporter]

Here is my suggested modification to MetaData.getColumns()

            colType = colType == null ? "TEXT" : colType.toUpperCase();
            int colJavaType = -1;
//            if (colType.equals("INT") || colType.equals("INTEGER"))
//                colJavaType = Types.INTEGER;
//            else if (colType.equals("TEXT"))
//                colJavaType = Types.VARCHAR;
//            else if (colType.equals("FLOAT"))
//                colJavaType = Types.FLOAT;
//            else
//                colJavaType = Types.VARCHAR;

            /*
             * improved column types
             * ref http://www.sqlite.org/datatype3.html - 2.1 Determination Of Column
Affinity
             * plus some degree of artistic-license applied
             */
            // rule #1
            if (colType.matches(".*(INT).*")) 
                colJavaType=Types.INTEGER;
            // rule #2
            else if (colType.matches(".*(CHAR|CLOB|TEXT).*"))
                colJavaType = Types.VARCHAR;
            // rule #3 (should be NULL, but VARCHAR is better in practice)
            else if (colType.matches(".*(BLOB).*") || colType.equals("")) 
                colJavaType = Types.VARCHAR;
            // rule #4
            else if (colType.matches(".*(REAL|FLOA|DOUB).*"))
                colJavaType = Types.FLOAT;
            // rule #5 doesn't really work out too well, so we improvise a bit from
here on
            else if (colType.matches(".*(DEC|NUM).*"))
                colJavaType = Types.FLOAT;
            else if (colType.matches(".*(BOOL).*"))
                colJavaType = Types.INTEGER;
            else // catch-all               
                colJavaType = Types.VARCHAR;

Original comment by thecatat...@gmail.com on 3 Jun 2010 at 4:47

[GoogleCodeExporter]

Or to make it a bit neater...

            /*
             * improved column types
             * ref http://www.sqlite.org/datatype3.html - 2.1 Determination Of Column
Affinity
             * plus some degree of artistic-license applied
             */
            // rule #1 + boolean
            if (colType.matches(".*(INT|BOOL).*")) 
                colJavaType=Types.INTEGER;
            // rule #2 + blob
            else if (colType.matches(".*(CHAR|CLOB|TEXT|BLOB).*"))
                colJavaType = Types.VARCHAR;
            // rule #4 + decimal, numeric
            else if (colType.matches(".*(REAL|FLOA|DOUB|DEC|NUM).*"))
                colJavaType = Types.FLOAT;
            else // catch-all               
                colJavaType = Types.VARCHAR;

Original comment by thecatat...@gmail.com on 3 Jun 2010 at 4:55

[GoogleCodeExporter]

Original comment by taroleo on 26 Oct 2010 at 6:50

• Changed state: Accepted
• Added labels: Milestone-sqlite-jdbc-3.7.3
• Removed labels: Milestone-sqlite-jdbc-3.6.23

[GoogleCodeExporter]

This issue was closed by revision 3320be9e84.

Original comment by taroleo on 26 Oct 2010 at 7:00

• Changed state: Fixed