[sqlitejdbc] PreparedStatement says "COLUMN may not be null" after schema change

[[EMAIL PROTECTED]]

Hi there, everyone!

I would just like to report a problem we found.  The issue is
executing prepared statements that created -before- a schema change
but execute -after- the schema change.

Here's a JUnit test to show what I mean:

/**
 *
 */
package com.thomson.care.utils;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

/**
 * We found some strange behavior where executing a preparedstatement
after a
 * schema change caused a SQLException("KEY may not be null") to be
thrown
 *
 * This test tries to reproduce it
 *
 * @author u0066990
 *
 */
public class SqliteJdbcTest
{
    private static Connection conn;

    @Before
    public void beforeClass() throws ClassNotFoundException,
SQLException
    {
        new File( "test.db" ).delete();
        Class.forName( "org.sqlite.JDBC" );
        conn = getConnection();
    }

    private static Connection getConnection() throws SQLException
    {
        return DriverManager.getConnection( "jdbc:sqlite:test.db" );
    }

    @After
    public void afterClass() throws SQLException
    {
        if ( conn != null )
        {
            conn.close();
        }
        new File( "test.db" ).delete();
    }

    @Test
    public void doesWork() throws Exception
    {
        boolean changeSchema = false;
        testPreparedStatement( changeSchema );
    }

    private void verifyRowIsPresent() throws SQLException
    {
        final ResultSet rs =
            conn.createStatement().executeQuery(
                "SELECT * from wpfeature_universe_wpfuniv" );
        rs.next();
        Assert.assertEquals( 1000, rs.getInt( 1 ) );
    }

    @Test
    public void doesNotWork() throws Exception
    {
        boolean changeSchema = true;
        testPreparedStatement( changeSchema );

    }

    private void testPreparedStatement( boolean changeSchema )
        throws SQLException
    {
        Statement stat = conn.createStatement();
        // create a new table
        final String sql =
            "CREATE TABLE  wpfeature_universe_wpfuniv ("
                + "id_wpfuniv INTEGER NOT NULL" + ")";
        stat.executeUpdate( sql );

        // create a preparedstatement
        PreparedStatement prepSt =
            conn
                .prepareStatement( "INSERT INTO
wpfeature_universe_wpfuniv (id_wpfuniv) VALUES(?)" );

        // change the schema
        if ( changeSchema )
        {
            conn.createStatement().executeUpdate(
                "create table cats(name, napTime )" );
        }
        prepSt.setInt( 1, 1000 );
        prepSt.execute();

        // insert new row
        prepSt.executeUpdate();
        verifyRowIsPresent();
    }

}

Anyone have any thoughts?  Thanks!
--~--~---------~--~----~------------~-------~--~----~
Mailing List: http://groups.google.com/group/sqlitejdbc?hl=en
To unsubscribe, send email to [EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---