网上下载了一个VB使用SQLite数据库的实例,测试中发现以下问题:
1.不支持BLOB型数据读写;
2.中文读取乱码。实例提供了Unicode到UTF-8的编码函数,但没有UTF-8到Unicode的解码函数,所以存入中文经测试没有问题,但读取时出现乱码(全英文不出现问题)。我把资源放到这个地址http://download.csdn.net/source/2881540希望高手们帮忙解决一下。谢谢了!
1.不支持BLOB型数据读写;
2.中文读取乱码。实例提供了Unicode到UTF-8的编码函数,但没有UTF-8到Unicode的解码函数,所以存入中文经测试没有问题,但读取时出现乱码(全英文不出现问题)。我把资源放到这个地址http://download.csdn.net/source/2881540希望高手们帮忙解决一下。谢谢了!
// mSqlite.sqlite3_exec(f_lSqlite, "INSERT INTO data (Age , Name) VALUES (23, '中國人12') ") = SQLITE_OK修改如下程序:Public Function sqlite3_exec(ByVal sqlite3 As Long, ByVal zSql As String) As Long
'int sqlite3_exec(
' sqlite3*, /* An open database */
' const char *sql, /* SQL to be evaluated */
' int (*callback)(void*,int,char**,char**), /* Callback function */
' void *, /* 1st argument to callback */
' char **errmsg /* Error msg written here */
');
Dim bvData() As Byte
Dim lSize As Long
Dim lRet As Long
If LenB(zSql) Then
lSize = Len(zSql) * 4
ReDim bvData(lSize)
lRet = WideCharToMultiByte(CP_UTF8, 0, StrPtr(zSql), Len(zSql), bvData(0), lSize + 1, vbNullString, 0)
If lRet Then
ReDim Preserve bvData(lRet - 1)
End If
End If If m_bLoaded Then
With m_cCDECL
sqlite3_exec = .CallFunc("sqlite3_exec", sqlite3, VarPtr(bvData(0)), 0, 0, 0)
End With
Else
sqlite3_exec = SQLITE_ERROR
End If
End Function
If mSqlite.sqlite3_prepare_v2(f_lSqlite, "SELECT Age , Name FROM data", 0, f_lStatement, 0) = SQLITE_OK Then
'// Print Values
Do While mSqlite.sqlite3_step(f_lStatement) = SQLITE_ROW
Debug.Print mSqlite.sqlite3_column_int(f_lStatement, 0)
Debug.Print mSqlite.sqlite3_column_text(f_lStatement, 1)
' Debug.Print mSqlite.HexToStr(mSqlite.sqlite3_column_text(f_lStatement, 1))
Loop
Else
Debug.Print mSqlite.sqlite3_errmsg(f_lSqlite)
End If
修改程序如下:
Public Function sqlite3_column_text(ByVal sqlite3_stmt As Long, ByVal iCol As Long) As String
'const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
Dim tBLOB As DATA_BLOB
If m_bLoaded Then
With m_cCDECL
tBLOB.pbData = .CallFunc("sqlite3_column_text", sqlite3_stmt, iCol)
tBLOB.cbData = sqlite3_column_bytes(sqlite3_stmt, iCol)
sqlite3_column_text = CStringPointerToVbString(tBLOB.pbData)
End With
End If
End FunctionPublic Function CStringPointerToVbString(ByVal lpString As Long) As String
Dim b() As Byte
Dim i As Long
Dim sVal As String
Dim tmplng As Long
'check for null pointer
If lpString = 0 Then Exit Function
sVal = ""
i = 0
Do
ReDim Preserve b(i)
'copy string 1 byte at a time until we get null terminator
CopyMemory b(i), ByVal lpString, 1
If b(i) = 0 Then Exit Do
'sVal = sVal & Chr(b(i))
lpString = lpString + 1
i = i + 1
Loop
ReDim Preserve b(UBound(b) - 1)
sVal = Utf8ToUnicode(b)
'return the string
CStringPointerToVbString = sVal
End FunctionPublic Function Utf8ToUnicode(ByRef Utf() As Byte) As String
Dim utfLen As Long
utfLen = -1
On Error Resume Next
utfLen = UBound(Utf)
If utfLen = -1 Then Exit Function
On Error GoTo 0
Dim i As Long, j As Long, k As Long, n As Long
Dim b As Byte, cnt As Byte
Dim Buf() As String
ReDim Buf(utfLen)
i = 0
j = 0
Do While i <= utfLen
b = Utf(i)
If (b And &HFC) = &HFC Then
cnt = 6
ElseIf (b And &HF8) = &HF8 Then
cnt = 5
ElseIf (b And &HF0) = &HF0 Then
cnt = 4
ElseIf (b And &HE0) = &HE0 Then
cnt = 3
ElseIf (b And &HC0) = &HC0 Then
cnt = 2
Else
cnt = 1
End If
If i + cnt - 1 > utfLen Then
Buf(j) = "?"
Exit Do
End If
Select Case cnt
Case 2
n = b And &H1F
Case 3
n = b And &HF
Case 4
n = b And &H7
Case 5
n = b And &H3
Case 6
n = b And &H1
Case Else
Buf(j) = Chr(b)
GoTo Continued:
End Select
For k = 1 To cnt - 1
b = Utf(i + k)
n = n * &H40 + (b And &H3F)
Next
Buf(j) = ChrW(n)
Continued:
i = i + cnt
j = j + 1
Loop
Utf8ToUnicode = Join(Buf, "")
End Function
ByVal sqlite3 As Long, _
ByVal zSql As String, _
ByVal nByte As Long, _
ByRef ppStmt As Long, _
ByRef pzTail As Long) As Long
'int sqlite3_prepare_v2(
' sqlite3 *db, /* Database handle */
' const char *zSql, /* SQL statement, UTF-8 encoded */
' int nByte, /* Maximum length of zSql in bytes. */
' sqlite3_stmt **ppStmt, /* OUT: Statement handle */
' const char **pzTail /* OUT: Pointer to unused portion of zSql */
');
Dim bvStr() As Byte
zSql = UnicodeToUTF8(zSql)
bvStr = StrConv(zSql & vbNullChar, vbFromUnicode)
If nByte = 0 Then
nByte = Len(zSql)
End If
If m_bLoaded Then
With m_cCDECL
sqlite3_prepare_v2 = .CallFunc( _
"sqlite3_prepare_v2", _
sqlite3, _
VarPtr(bvStr(0)), _
nByte, _
VarPtr(ppStmt), _
VarPtr(pzTail))
End With
Else
sqlite3_prepare_v2 = SQLITE_ERROR
End If
End FunctionPublic Function UnicodeToUTF8(ByVal sData As String) As String
Dim bvData() As Byte
Dim lSize As Long
Dim lRet As Long
If LenB(sData) Then
lSize = Len(sData) * 4
ReDim bvData(lSize)
lRet = WideCharToMultiByte(CP_UTF8, 0, StrPtr(sData), Len(sData), bvData(0), lSize + 1, vbNullString, 0)
If lRet Then
ReDim Preserve bvData(lRet - 1)
UnicodeToUTF8 = StrConv(bvData, vbUnicode)
End If
End IfEnd Function
(1)sqlite3_prepare()或sqlite3_prepare_v2()-预编译函数;
(2)sqlite3_bind_blob()-数据绑定函数,将BLOB型数据绑定并解析到sqlite3内部特殊的数据结构中去;
(3)sqlite3_step()-将数据写到数据库中。
ByVal sqlite3 As Long, _
ByVal zSql As String, _
ByVal nByte As Long, _
ByRef ppStmt As Long, _
ByRef pzTail As Long) As Long
'int sqlite3_prepare_v2(
' sqlite3 *db, /* Database handle */
' const char *zSql, /* SQL statement, UTF-8 encoded */
' int nByte, /* Maximum length of zSql in bytes. */
' sqlite3_stmt **ppStmt, /* OUT: Statement handle */
' const char **pzTail /* OUT: Pointer to unused portion of zSql */
');
'TEST OK
Dim bvData() As Byte
Dim lSize As Long
Dim lRet As Long
If LenB(zSql) Then
lSize = Len(zSql) * 4
ReDim bvData(lSize)
lRet = WideCharToMultiByte(CP_UTF8, 0, StrPtr(zSql), Len(zSql), bvData(0), lSize + 1, vbNullString, 0)
If lRet Then
ReDim Preserve bvData(lRet - 1)
End If
End If If m_bLoaded Then
With m_cCDECL
sqlite3_prepare_v2 = .CallFunc( _
"sqlite3_prepare_v2", _
sqlite3, _
VarPtr(bvData(0)), _
nByte, _
VarPtr(ppStmt), _
VarPtr(pzTail))
End With
Else
sqlite3_prepare_v2 = SQLITE_ERROR
End If
End Function
'const char *sqlite3_bind_parameter_name(sqlite3_stmt*, int)
'sqlite3_bind_blob( stat, 1, pdata, (int)(length_of_data_in_bytes), NULL ); // pdata为数据缓冲区,length_of_data_in_bytes为数据大小,以字节为单位'这个函数一共有5个参数?'第1个参数: 是前面prepare得到的 sqlite3_stmt * 类型变量?'第2个参数:?号的索引。前面prepare的sql语句里有一个?号,假如有多个?号怎么插入?方法就是改变 bind_blob 函数第2个参数。这个参数我写1,表示这里插入的值要替换 stat 的第一个?号(这里的索引从1开始计数,而非从0开始)。如果你有多个?号,就写多个 bind_blob 语句,并改变它们的第2个参数就替换到不同的?号。如果有?号没有替换,sqlite为它取值null。'第3个参数: 二进制数据起始指针?'第4个参数:二进制数据的长度,以字节为单位。'第5个参数:是个析够回调函数,告诉sqlite当把数据处理完后调用此函数来析够你的数据。这个参数我还没有使用过,因此理解也不深刻。但是一般都填NULL,需要释放的内存自己用代码来释放。 If m_bLoaded Then
With m_cCDECL
sqlite3_bind_blob = .CallFunc("sqlite3_bind_blob", sqlite3_stmt, iCol, ppDb, length_of_data_in_bytes)
End With
Else
sqlite3_bind_blob = SQLITE_ERROR
End If
End Function
'int sqlite3_step(sqlite3_stmt*);
'TEST OK
If m_bLoaded Then
With m_cCDECL
sqlite3_step = .CallFunc("sqlite3_step", sqlite3_stmt)
End With
Else
sqlite3_step = SQLITE_ERROR
End If
End Function
测试了一下插入10万条数据的效率,SQLITE用事务的方式比ACCESS效率要高20多倍。如果上述模糊检索语句能测试成功,整个例子还是很有应用价值的。
我就怕他的索引技术比access差很多,这样导致检索数据慢。因为建库毕竟是一次性的,慢点关系不大,而检索速度才是关键。
如果索引好用,速度也快,我以后就改用sqlite了。
目前待解决的问题:
1.中文模糊查询问题
SELECT * FROM DATA 语句可以检索到记录,显示正常;
SELECT * FROM DATA WHERE NAME LIKE '%中国%'检索不到记录,但该语句在第三方工具的查询栏能够检索到数据;
2.尚未解决读写BLOB型数据的方法。
这样试试,sqlite 数据库的纠错能力很弱~~
但毕竟sqlite还是很弱的,相比sql server、 Oracle等不在一个级别上
sqlite内部默认是UTF-8编码方式,检索语句已按UTF-8编码,况且SELECT * FROM DATA 可以查询成功。你所说的“sqlite 数据库驱动的缘故”是指编码方式还是指其他什么?能详细的说一下吗?
由于它的驱动写的相对没有别的数据库那么健壮,所以,它的驱动在翻译SQl语句为检索命令时,往往特别严格。稍有不甚,就不能按要求返回记录。这是我用这东西最难受的地方。
您也是在vb中使用sqlite的吗?类似的问题是如何解决的?我从网上下载的实例(1楼有网址),最初在存入中文时乱码,后来用2楼朋友的代码解决了乱码的问题,但读取时检索不到结果。
曾经在枕善居下载过封装好的DLL,使用前需要注册,不知道内部是如何实现的。我想把1楼的实例改造成直接调用、不需要注册DLL的例子,方便部署。
请您指教。
SELECT * FROM DATA WHERE NAME LIKE '%中国%'检索不到记录,但该语句在第三方工具的查询栏能够检索到数据。
用您23楼的方法仍然不行。
//问题越来越多了:发现类似SELECT * FROM DATA WHERE NAME LIKE '%中国%'这样的语句检索不到结果Public Function sqlite3_prepare( _
ByVal sqlite3 As Long, _
ByVal zSql As String, _
ByVal nByte As Long, _
ByRef ppStmt As Long, _
ByRef pzTail As Long) As Long
'int sqlite3_prepare(
' sqlite3 *db, /* Database handle */
' const char *zSql, /* SQL statement, UTF-8 encoded */
' int nByte, /* Maximum length of zSql in bytes. */
' sqlite3_stmt **ppStmt, /* OUT: Statement handle */
' const char **pzTail /* OUT: Pointer to unused portion of zSql */
');
Dim bvData() As Byte
Dim lSize As Long
Dim lRet As Long
If LenB(zSql) Then
lSize = Len(zSql) * 4
ReDim bvData(lSize)
lRet = WideCharToMultiByte(CP_UTF8, 0, StrPtr(zSql), Len(zSql), bvData(0), lSize + 1, vbNullString, 0)
If lRet Then
ReDim Preserve bvData(lRet - 1)
End If
End If
If m_bLoaded Then
With m_cCDECL
sqlite3_prepare = .CallFunc( _
"sqlite3_prepare", _
sqlite3, _
VarPtr(bvData(0)), _
lRet, _
VarPtr(ppStmt), _
VarPtr(pzTail))
End With
Else
sqlite3_prepare = SQLITE_ERROR
End If
End Function