sqlite setBytes() not working for BLOB data type on Windows

I am using sqlite (version 3.7.5) with sqlite jdbc driver provided at http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC

I asked an almost identical question few months back at xerial forums (http://groups.google.com/group/xerial/browse_thread/thread/ee19bd855e282f9c), but never got any response.

When I build the shared library sqlite.dll/libsqlite.so on my own, my example works correctly on Linux (opensuse 64 bit), but does not work correctly on Windows XP professional 32 bit.

But, if I use the shared library provided at the sqlite.org website (http://sqlite.org/sqlite-dll-win32-x86-3070500.zip) or the one that comes bundled with the jdbc driver (http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.7.2/sqlite-jdbc-3.7.2.jar), it works fine on Linux as well as Windows.

I am therefore guessing that I am not building the sqlite library correctly. I am using cygwin environment with Microsoft Platform SDK to build sqlite. I am using the following set of commands to create the sqlite library on Windows XP 32 bit.

mycl -32 -O2 /D "NDEBUG" /MD /D "_WIN32" /D "_WINDOWS" /D "_MBCS" /D "_USRDLL" /D "SQLITE_ENABLE_COLUMN_METADATA" /D "SQLITE_ENABLE_FTS3" /D "SQLITE_THREADSAFE=1" -c NativeDB.c -o NativeDB.o

mycl -32 -O2 /D "NDEBUG" /MD /D "_WIN32" /D "_WINDOWS" /D "_MBCS" /D "_USRDLL" /D "SQLITE_ENABLE_COLUMN_METADATA" /D "SQLITE_ENABLE_FTS3" /D "SQLITE_THREADSAFE=1" -c sqlite3.c -o sqlite3.o

mylink -32 /DLL /libpath:../lib/Win32 /out:sqlite.dll NativeDB.o sqlite3.o gdi32.lib vfw32.lib user32.lib comdlg32.lib comctl32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib wbemuuid.lib netapi32.lib ws2_32.lib kernel32.lib

mymt -32 /manifest sqlite.dll.manifest /outputresource:sqlite.dll';#2'

(mycl, mylink, and mymt are wrappers around original cl.exe, link.exe, and mt.exe that convert command line arguments. They work fine with many other projects that I build with them).

I also created a SSCE to demonstrate the problem.

package org.sqlite;

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

public class MainDriver {


public static void main(String[] args) {
    new MainDriver();
}


public MainDriver() {

    //Buffers to read and write
    byte[] writeBuffer = new byte[10];
    byte[] readBuffer = null;
    for (int i = 1; i < 10; i++) {
        writeBuffer[i] = (byte)i;
    }

    //Database objects
    Connection conn = null;
    Statement stat = null;
    PreparedStatement prep = null;

    //Load the database driver
    try {
        System.loadLibrary("sqlite");
        Class.forName("org.sqlite.JDBC");
    } catch (Exception e) {
        System.err.println("Could not load sqlite library or instantiate the database driver.");
        System.err.println(e);
        e.printStackTrace();
        return;
    }

    //Open a connection to the database
    try {
        conn = DriverManager.getConnection("jdbc:sqlite:" + "file.db");
    } catch (SQLException e) {
        System.err.println("Could not open a connection to the database with name file.db");
        System.err.println(e);
        e.printStackTrace();
        return;
    }

    //Create a table
    try {
        stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST (model BLOB NOT NULL)");
        stat.close();
    } catch (SQLException e) {
        System.err.println("The table could not be created.");
        System.err.println(e);
        e.printStackTrace();
        return;
    }

    //Write buffer into the database
    try {
        conn.setAutoCommit(false);
        prep = conn.prepareStatement("INSERT INTO TEST (model) VALUES(?)");
        prep.setBytes(1, writeBuffer);
        prep.addBatch();
        prep.executeBatch();
        conn.setAutoCommit(true);
        prep.close();
    } catch (SQLException e) {
        System.err.println("The buffer could not be written to the database.");
        System.err.println(e);
        e.printStackTrace();
        return;
    }

    //Read buffer from the database
    try {
        stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
        readBuffer = rs.getBytes(1);
        rs.close();
        stat.close();
    } catch (SQLException e) {
        System.err.println("The buffer could not be read");
        System.err.println(e);
        e.printStackTrace();
    }

    //Close the database
    try {
        conn.close();
    } catch (SQLException e) {
        System.err.println("Database could not be closed");
        System.err.println(e);
        e.printStackTrace();
    }

    //Print the buffers
    System.out.print("Write buffer = ");
    for (int i = 0; i < writeBuffer.length; i++) {
        System.out.print(writeBuffer[i]);
    }
    System.out.println();
    System.out.print("Read  buffer = ");
    for (int i = 0; i < readBuffer.length; i++) {
        System.out.print(readBuffer[i]);
    }
    System.out.println();

    //Check the md5sum开发者_开发知识库
    try {
        java.security.MessageDigest digest = java.security.MessageDigest.getInstance("MD5");
        byte[] md5sum = null;
        java.math.BigInteger bigInt = null;

        //Write buffer
        digest.reset();
        digest.update(writeBuffer);
        md5sum = digest.digest();
        bigInt = new java.math.BigInteger(1, md5sum);
        System.out.println("MD5 checksum of write buffer = " + bigInt.toString(16));

        //Read buffer
        digest.reset();
        digest.update(readBuffer);
        md5sum = digest.digest();
        bigInt = new java.math.BigInteger(1, md5sum);
        System.out.println("MD5 checksum of read  buffer = " + bigInt.toString(16));
    } catch (Exception e) {
        System.err.println("MD5 checksum not available");
        return;
    }
}

}

I have also attempted building sqlite with ICU unicode library (version 4.4.2). I am using the following commands to build sqlite with unicode support.

cl.exe -32 -O2 /D "NDEBUG" /MD /D "_WIN32" /D "_WINDOWS" /D "_MBCS" /D "_USRDLL" /D "SQLITE_ENABLE_COLUMN_METADATA" /D "SQLITE_ENABLE_FTS3" /D "SQLITE_THREADSAFE=1" /D "SQLITE_ENABLE_ICU" -I../external/icu/win32/include -I../include -c NativeDB.c -o NativeDB.o

cl.exe -32 -O2 /D "NDEBUG" /MD /D "_WIN32" /D "_WINDOWS" /D "_MBCS" /D "_USRDLL" /D "SQLITE_ENABLE_COLUMN_METADATA" /D "SQLITE_ENABLE_FTS3" /D "SQLITE_THREADSAFE=1" /D "SQLITE_ENABLE_ICU" -I../external/icu/win32/include -I../include -c sqlite3.c -o sqlite3.o

link.exe -32 /DLL /libpath:../external/icu/win32/lib /out:sqlite.dll NativeDB.o sqlite3.o icuuc.lib icuin.lib gdi32.lib vfw32.lib user32.lib comdlg32.lib comctl32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib wbemuuid.lib netapi32.lib ws2_32.lib kernel32.lib

mt.exe -32 /manifest sqlite.dll.manifest /outputresource:sqlite.dll';#2'

Building with/without unicode has no effect. I am still unable to resolve the issue. I will very much appreciate any help or pointers to a possible solution/workaround.

I finally resolved the problem. The culprit was the "NDEBUG" flag that is automatically added by Visual Studio 6. The following set of commands correctly build the sqlite library (build environment is Microsoft Windows SDK v6.1 - release year 2008).

cl.exe /O2 /GL /D "WIN32" /D "_WINDLL" /D "_UNICODE" /D "UNICODE" /MD /W3 /c /Wp64 /TC /D "SQLITE_ENABLE_COLUMN_METADATA" /D "SQLITE_ENABLE_FTS3" /D "SQLITE_THREADSAFE=1" /D "SQLITE_ENABLE_ICU" /I "../external/icu/Win32/include" sqlite3.c

cl.exe /O2 /GL /D "WIN32" /D "_DLL" /D "_WINDLL" /D "_UNICODE" /D "UNICODE" /MD /W3 /c /Wp64 /TC /D "SQLITE_ENABLE_COLUMN_METADATA" /D "SQLITE_ENABLE_FTS3" /D "SQLITE_THREADSAFE=1" /D "SQLITE_ENABLE_ICU" NativeDB.c

link.exe /INCREMENTAL:NO /DLL /SUBSYSTEM:CONSOLE /OPT:REF /OPT:ICF /LTCG /MACHINE:X86 /LIBPATH:"../external/icu/Win32/lib" "/out:sqlite.dll" NativeDB.obj sqlite3.obj icuuc.lib icuin.lib kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib uuid.lib

mt.exe /manifest sqlite.dll.manifest /outputresource:sqlite.dll';#2'

I am posting the commands in case someone else might be stuck with the same problem.