Ok, perhaps it needs to wait until the C api matures a bit. One other
observation though, if I may. DatabaseMetaData.getColumns() returns
yet another data type code:
My revised test driver produces the following output:
*** Before insert (empty table) ***
Column type from DatabaseMetaData: 12
Column type name from DatabaseMetaData: INTEGER
Column type from ResultSetMetaData: 0
Column type name from ResultSetMetaData: integer
*** After insert (one record table) ***
Column type from DatabaseMetaData: 12
Column type name from DatabaseMetaData: INTEGER
Column type from ResultSetMetaData: 4
Column type name from ResultSetMetaData: integer
Perhaps the DatabaseMetaData implementation could be used to implement
ResultSetMetaData.getColumnType/getColumnTypeName implementation to
make the values returned consistent? At least that way the type
information is consistent (albeit, misleading as in this case it is
identified as a TEXT / VARCHAR type field). Unless of course the
implementation for DatabaseMetaData.getColumns is hard-coded to always
return TEXT / VARCHAR. In that case then it's not really helpful.
I'm not sure how the value of a column could ever be returned as it's
type. I'm not seeing that in my test - at lease I don't think I am.
The new test case is below.
Rob
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class TestSQLite
{
public static void main(String[] args) throws Exception
{
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:/tmp/
test.dbf");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists test");
stat.executeUpdate("create table test (myid integer)");
stat.close();
System.out.println("\t *** Before insert (empty table) *** \n");
getDatabaseMetaDataType(conn, "test", "myid");
printColumnTypeAndName(conn);
PreparedStatement prep = conn.prepareStatement("insert into test
values (?)");
prep.setInt(1, 1);
prep.executeUpdate();
System.out.println("\t *** After insert (one record table) ***
\n");
getDatabaseMetaDataType(conn, "test", "myid");
printColumnTypeAndName(conn);
conn.close();
}
private static void printColumnTypeAndName(Connection conn) throws
Exception
{
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from test");
ResultSetMetaData md = rs.getMetaData();
System.out.println("Column type from ResultSetMetaData: " +
md.getColumnType(1));
System.out.println("Column type name from ResultSetMetaData: " +
md.getColumnTypeName(1) + "\n");
rs.close();
}
private static int getDatabaseMetaDataType(Connection conn, String
tableName, String columnName) throws Exception {
int result = -1;
ResultSet rs = conn.getMetaData().getColumns(null, null,
tableName,
columnName);
while (rs.next()) {
int columnType = rs.getInt(5);
String columnTypeName = rs.getString(6);
System.out.println("Column type from DatabaseMetaData:
"+columnType);
System.out.println("Column type name from
DatabaseMetaData:
"+columnTypeName+"\n");
}
rs.close();
return result;
}
}
On Jun 17, 7:58 pm, "David Crawshaw" <[EMAIL PROTECTED]> wrote:
> 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]
-~----------~----~----~----~------~----~------~--~---
