I do not think it is possible to access the column type affinity
through the SQLite C interface. So I cannot have getColumnType() work
the way you would like.
Right now, getCoulmnTypeName() is doing something silly, it is
returning the name given to the type by the user. This means if
something does this:
create table t1 (col1 blahtype);
Then getColumnTypeName() will return "blahtype". Which is not helpful.
Worse, someone can do this:
create table t1 (col1 integer);
insert into t1 values ('hello');
And then any poor program that relied on the meta data gets "hello"
instead of an integer. I think the only thing to do here is to make
getColumnTypeName() return a string version of getColumnType(), which
returns the affinity type of the current row. I know this is even
further from what you want, but such is life when interfacing manifest
typing with strict typing.
d.
2008/6/17 [EMAIL PROTECTED] <[EMAIL PROTECTED]>:
>
> 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]
-~----------~----~----~----~------~----~------~--~---
