sqlite setBytes() 不适用于 Windows 上的 BLOB 数据类型

Question

我正在使用 sqlite （版本 3.7.5）以及 http://www 提供的 sqlite jdbc 驱动程序.xerial.org/trac/Xerial/wiki/SQLiteJDBC

几个月前，我在 xerial 论坛上问了一个几乎相同的问题（http://groups.google.com/group/xerial/browse_thread/thread/ee19bd855e282f9c），但从未得到任何回应。

当我自己构建共享库 sqlite.dll/libsqlite.so 时，我的示例在 Linux（opensuse 64 位）上正常工作，但在 Windows XP professional 32 位上无法正常工作。

但是，如果我使用 sqlite.org 网站 (http://sqlite.org/sqlite-dll-win32-x86-3070500.zip) 提供的共享库或与 jdbc 驱动程序捆绑在一起的共享库 (http://sqlite.org/sqlite-dll-win32-x86-3070500.zip) /www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.7.2/sqlite-jdbc-3.7.2.jar)，它在 Linux 和 Windows 上运行良好。

因此我猜测我没有正确构建 sqlite 库。我正在使用 cygwin 环境和 Microsoft Platform SDK 来构建 sqlite。我正在使用以下命令集在 Windows XP 32 位上创建 sqlite 库。

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 和 mymt 是原始 cl.exe、link.exe 和 mt.exe 的包装器，用于转换命令行参数。它们与我用它们构建的许多其他项目配合得很好）。

我还创建了一个 SSCE 来演示该问题。

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;
    }
}

。

我还尝试使用 ICU unicode 库（版本 4.4.2）构建 sqlite 我正在使用以下命令来构建支持 unicode 的 sqlite。

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'

使用/不使用 unicode 进行构建均无效。我仍然无法解决这个问题。我将非常感谢任何帮助或指向可能的解决方案/解决方法的指示。

Answer 1

我终于解决了这个问题。罪魁祸首是 Visual Studio 6 自动添加的“NDEBUG”标志。以下命令集正确构建 sqlite 库（构建环境是 Microsoft Windows SDK v6.1 - 发布年份 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 "_D​​LL" /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 /INCRMENTAL: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'

我发布命令以防其他人可能遇到同样的问题。