Message6514

Sadly, although the patch fixes an obvious problem (the Type.NULL code is clearly wrong and should be fixed) it doesn't fix all the problems that occur when using this code with sqlite.

It looks like the column type reported through the metadata from sqlite changes based on the stored value, not based on the type name or type affinity of the column.

This means that that, although the patch fixes the real-world situation where sqlite returns a single row containing a NULL, it doesn't fix the case that there are multiple returned rows the first of which contains NULL.  This causes sqlite to return Types.NULL for the first row, and not unreasonably DataHandler.java assumes this type applies to all the values in the column.

A test along the following lines still fails under sqlite, even with the patch.

def testNullReturnQuery(self):
     """testing that a resultset containing a NULL value doesn't break."""
     c = self.cursor()
     try:
         c.execute("insert into zxtesting (id, name, state) values (100, NULL, 'xx')")
         self.db.commit()
         c.execute("select name from zxtesting where id = 100 or id = 1 order by id desc");
         f = c.fetchall()
         assert len(f) == 2, "expecting two rows"
         data = f[0]
         assert data[0] is None, "expecting None/NULL returned, was %r" % (data,)
         data = f[1]
         assert data[0] is not None, "expecting non-NULL value returned, was %r" % (data,)
     finally:
         c.close()

The only fix I can think of is to re-query the type for each value returned, i.e. rather than pass the type parameter into the getPyObject method, have it obtain it each time via something like:

int type = stmt.getMetaData().getColumnType(col)

This doesn't seem an ideal thing to have to do in generic JDBC driver code but it does make things work better for sqlite case.