>From the article you referenced, I believe that the
ResultSetMetaData.getColumnType method should provide the column
affinity, if the table was created using a type:

"The type affinity of a column is determined by the declared type of
the column, according to the following rules:

   1. If the datatype contains the string "INT" then it is assigned
INTEGER affinity."


This example (from the documentation) seems to speak to what I mean:

2.2 Column Affinity Example

    CREATE TABLE t1(
        t  TEXT,
        nu NUMERIC,
        i  INTEGER,
        no BLOB
    );

    -- Storage classes for the following row:
    -- TEXT, REAL, INTEGER, TEXT
    INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');

    -- Storage classes for the following row:
    -- TEXT, REAL, INTEGER, REAL
    INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);


Notice how in the first insert statement, the data value for the
INTEGER column (i) is '500.0', but the storage class is INTEGER
because the column was created using the INTEGER type.  The column
affinity doesn't appear to be affected by the number of rows in the
table - which is the behavior that the driver seems to have currently
- but only for the type code.  The type column name appears to be
correct (my test output erroneously refers to this value as
getColumnName, when it was actually the getColumnTypeName value)

In the test case I provided, also notice how the column type name
(getColumnName) is "integer" in both cases, whereas the type code
switched from 0 to 4 when records were added.  I don't see how this
driver behavior is consistent with the documentation.

Rob


On Jun 17, 1:51 am, "David Crawshaw" <[EMAIL PROTECTED]> wrote:
> Robert. M. Manning wrote:
> > I'm trying to add support for SQLiteJDBC to SQuirreL SQL Client and I
> > came across some odd behavior.  If I declare a table with a single
> > integer column, select from it (no records - just an emtpy ResultSet)
> > and look at the ResultSet's MetaData, the column type is returned as
> > zero (java.sql.Types.NULL).  However when I insert a record into it,
> > and do another select, the column type is reported to be integer
> > (java.sql.Types.INTEGER).  Shouldn't the column type be consistent
> > regardless of whether or not the table has data?  My test below yields
> > the following output:
>
> > getColumnType: 0
> > getColumnName: integer
> > getColumnType: 4
> > getColumnName: integer
>
> This is the nature of SQLite, it is not strictly typed. To quote:
>
>     In SQLite version 3, the type of a value is associated with the
>     value itself, not with the column or variable in which the value
>     is stored. (This is sometimes called manifest typing.)
>
> http://sqlite.org/datatype3.html
>
> d.
--~--~---------~--~----~------------~-------~--~----~
Mailing List: http://groups.google.com/group/sqlitejdbc?hl=en
To unsubscribe, send email to [EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---

Reply via email to