更新了下Sqlite3库，增加了使用事例。

bycw2007 · 发表于 2014-2-26 14:15:29

不少朋友需要使用事例，其实如果熟悉标准sqlite的语句根本没必要事例的，
事例写的比较乱，凑活着看吧，基本功能都涵盖到了。

qzxym · 发表于 2014-2-27 15:40:36

请教最初始的问题：空的数据库如何创建？即范例中的 DB.sqlite 文件是如何创建出来的？

bycw2007 · 发表于 2014-2-27 17:22:27

qzxym 发表于 2014-2-27 15:40
请教最初始的问题：空的数据库如何创建？即范例中的 DB.sqlite 文件是如何创建出来的？

你可以使用一般的sqlite3管理工具建库，也先创建个空文件然后使用sql语句建库，详细的sql命令建议还是去看下sqlite3的教程，这里讲也讲不清

menglv · 发表于 2014-2-27 18:30:32

这个可以有，还真没用过这个数据库。

快手 · 发表于 2014-3-10 18:54:57

本帖最后由快手于 2014-3-10 18:56 编辑

给你搞了下函数命名，好理解点，哈~~~

为了方便记忆使用，又简单化了几个函数如下：

Option Explicit
Public Li As New clsSQLite
Public m_DB As Long
Public SqlStr As String
Public Sub DBOpen(DBPathFile$)
Li.DBOpen DBPathFile
End Sub
Public Sub DBClose()
Li.DBClose
End Sub
Public Sub RunSQL(SqlStr$)
On Error Resume Next
Li.Execute m_DB, SqlStr
End Sub
Public Function GetRs$(SqlStr$)
Dim sqlite3_stmt As Long
Dim hRet As Long
'Log.Cls
Dim RsString As String
Dim RsCount As Long
Dim i As Integer
hRet = Li.Prepare(m_DB, SqlStr, sqlite3_stmt)
If hRet = SQLITE_OK Then
Do While Li.Movenext(sqlite3_stmt)
RsCount = Li.ColumnCount(sqlite3_stmt)
For i = 0 To RsCount
RsString = RsString & Li.ColumnText(sqlite3_stmt, i) & "|-|-|"
Next i
RsString = RsString & "|#|#|"
Loop
End If
Li.Finalize sqlite3_stmt
GetRs = RsString
End Function
Public Function RsValue(ByRef SqlStr$) As Long
Dim sqlite3_stmt As Long
Dim hRet As Long
hRet = Li.Prepare(m_DB, SqlStr, sqlite3_stmt)
If hRet = SQLITE_OK Then
RsValue = sqlite3_stmt
End If
End Function
Public Sub Insert(biao$, ziduan$, Zhi$)
SqlStr = "insert into " & biao & "(" & ziduan & ")" & " values (" & Zhi & ")"
Li.Execute m_DB, SqlStr
End Sub
Public Sub Update(biao$, xiugai$, ziduan$, Zhi$)
SqlStr = "update " & biao & " set " & xiugai & " where " & ziduan & "=" & Zhi
Li.Execute m_DB, SqlStr
End Sub
Public Function UpdateValues(biao$, Set_ZhiDuan$, Set_Zhi$, FenGeFu$, Where_ziduan$, Where_Zhi$, ByVal Types As Integer)
Dim ArrStr1() As String
Dim ArrStr2() As String
Dim tmp_Gai As String
Dim i As Integer
ArrStr1 = Split(Set_ZhiDuan, ",")
ArrStr2 = Split(Set_Zhi, FenGeFu)
For i = 0 To UBound(ArrStr1)
tmp_Gai = tmp_Gai & Trim(ArrStr1(i)) & "=" & ArrStr2(i) & ","
Next i
tmp_Gai = Left(tmp_Gai, Len(tmp_Gai) - 1)
If Types = 1 Then
SqlStr = "Update " & biao & " Set " & tmp_Gai & " Where " & Where_ziduan & "='" & Where_Zhi & "'"
Else
SqlStr = "Update " & biao & " Set " & tmp_Gai & " Where " & Where_ziduan & "=" & Where_Zhi
End If
Li.Execute m_DB, SqlStr
End Function
Public Function Delete(biao$, Optional ByVal tiaojian$ = "")
If tiaojian = "" Then
SqlStr = "TRUNCATE " & biao
Else
If InStr(LCase(tiaojian), "where ") > 0 Then
SqlStr = "delete " & biao & tiaojian
Else
SqlStr = "delete " & biao & " where " & tiaojian
End If
End If
Li.Execute m_DB, SqlStr
End Function
Public Function GetValue$(biao$, gei$, huo$, Types$, str$)
Dim SqlStr As String
If Types = 0 Then
SqlStr = "select " & huo & " from " & biao & " where " & gei & "=" & str
Else
SqlStr = "select " & huo & " from " & biao & " where " & gei & "='" & str & "'"
End If
Dim sqlite3_stmt As Long
Dim hRet As Long
hRet = Li.Prepare(m_DB, SqlStr, sqlite3_stmt)
If hRet = SQLITE_OK Then
If Li.Movenext(sqlite3_stmt) Then
GetValue = Li.ColumnText(sqlite3_stmt, 0)
End If
End If
Li.Finalize sqlite3_stmt
End Function
Public Function GetCount&(biao$, Optional ByVal Zhiduan$ = "", Optional ByVal tiaojian$ = "")
GetCount = 0
If tiaojian = "" Then
SqlStr = "select count(" & Zhiduan & ") from " & biao
Else
If InStr(LCase(tiaojian), "where ") > 0 Then
SqlStr = "select count(" & Zhiduan & ") from " & biao & tiaojian
Else
SqlStr = "select count(" & Zhiduan & ") from " & biao & " where " & tiaojian
End If
End If
Dim sqlite3_stmt As Long
Dim hRet As Long
hRet = Li.Prepare(m_DB, SqlStr, sqlite3_stmt)
If hRet = SQLITE_OK Then
If Li.Movenext(sqlite3_stmt) Then
GetCount = Li.ColumnInt(sqlite3_stmt, 0)
End If
End If
Li.Finalize sqlite3_stmt
GetCount = GetCount
End Function

复制代码

以下引用兄弟你的原作，名字把：ite_ 扔掉了~~~~~~~~~~~~~

Private Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)
'ite
Private Declare Sub ite__free_table Lib "iteSql" (ByVal ResultPtr As Long)
Private Declare Function ite__get_table Lib "iteSql" (ByVal db As Long, ByVal SQLStatement As String, ByRef ResultPtr As Long, ByRef rRowCount As Long, ByRef rColCount As Long) As Long
Private Declare Function ite__get_data Lib "iteSql" (ByVal ResultPtr As Long, ByVal RowIdx As Long, ByVal ColIdx As Long, ByVal ColCount As Long) As Long
Private Declare Function ite__open Lib "iteSql" (ByVal dbname As String, ByRef db As Long) As Long
Private Declare Function ite__close Lib "iteSql" (ByVal db As Long) As Long
Private Declare Function ite__exec Lib "iteSql" (ByVal db As Long, ByVal SQLStatement As String) As Long
Private Declare Function ite__libversion Lib "iteSql" () As Long 'T
Private Declare Function ite__errmsg Lib "iteSql" (ByVal db As Long) As Long 'T
Private Declare Function ite__errcode Lib "iteSql" (ByVal db As Long) As Long
Private Declare Function ite__last_insert_rowid Lib "iteSql" (ByVal db As Long) As Long
Private Declare Function ite__changes Lib "iteSql" (ByVal db As Long) As Long
Private Declare Function ite__total_changes Lib "iteSql" (ByVal db As Long) As Long
Private Declare Function ite__prepare Lib "iteSql" (ByVal db As Long, ByVal SQLStatement As String, ByRef hStmt As Long) As Long
Private Declare Function ite__finalize Lib "iteSql" (ByVal hStmt As Long) As Long
Private Declare Function ite__reset Lib "iteSql" (ByVal hStmt As Long) As Long
Private Declare Function ite__step Lib "iteSql" (ByVal hStmt As Long) As Long
Private Declare Function ite__data_count Lib "iteSql" (ByVal hStmt As Long) As Long
Private Declare Function ite__column_count Lib "iteSql" (ByVal hStmt As Long) As Long
Private Declare Function ite__column_name Lib "iteSql" (ByVal hStmt As Long, ByVal ColNum As Long) As Long 'T
Private Declare Function ite__column_decltype Lib "iteSql" (ByVal hStmt As Long, ByVal ColNum As Long) As Long 'T
Private Declare Function ite__column_type Lib "iteSql" (ByVal hStmt As Long, ByVal ColNum As Long) As Long
Private Declare Function ite__column_blob Lib "iteSql" (ByVal hStmt As Long, ByVal ColNum As Long) As Long
Private Declare Function ite__column_bytes Lib "iteSql" (ByVal hStmt As Long, ByVal ColNum As Long) As Long
Private Declare Function ite__column_double Lib "iteSql" (ByVal hStmt As Long, ByVal ColNum As Long) As Double
Private Declare Function ite__column_int Lib "iteSql" (ByVal hStmt As Long, ByVal ColNum As Long) As Long
Private Declare Function ite__column_text Lib "iteSql" (ByVal hStmt As Long, ByVal ColNum As Long) As Long 'T
Private Declare Function ite__bind_blob Lib "iteSql" (ByVal hStmt As Long, ByVal ParamNum As Long, ByVal ptrData As Long, ByVal numBytes As Long) As Long
Private Declare Function ite__bind_int Lib "iteSql" (ByVal hStmt As Long, ByVal ParamNum As Long, ByVal nValue As Long) As Long
Private Declare Function ite__bind_double Lib "iteSql" (ByVal hStmt As Long, ByVal ParamNum As Long, ByVal nValue As Double) As Long
Private Declare Function ite__bind_text Lib "iteSql" (ByVal hStmt As Long, ByVal ParamNum As Long, ByVal szValue As String) As Long
Private Const SQLITE_OK = 0
Private Const SQLITE_ERROR = 1
Private Const SQLITE_INTERNAL = 2
Private Const SQLITE_PERM = 3
Private Const SQLITE_ABORT = 4
Private Const SQLITE_BUSY = 5
Private Const SQLITE_LOCKED = 6
Private Const SQLITE_NOMEM = 7
Private Const SQLITE_READONLY = 8 ' Attempt to write a readonly database
Private Const SQLITE_INTERRUPT = 9 ' Operation terminated by sqlite3_interrupt()
Private Const SQLITE_IOERR = 10 ' Some kind of disk I/O error occurred
Private Const SQLITE_CORRUPT = 11 ' The database disk image is malformed
Private Const SQLITE_NOTFOUND = 12 ' (Internal Only) Table or record not found
Private Const SQLITE_FULL = 13 ' Insertion failed because database is full
Private Const SQLITE_CANTOPEN = 14 ' Unable to open the database file
Private Const SQLITE_PROTOCOL = 15 ' Database lock protocol error
Private Const SQLITE_EMPTY = 16 ' Database is empty
Private Const SQLITE_SCHEMA = 17 ' The database schema changed
Private Const SQLITE_TOOBIG = 18 ' Too much data for one row of a table
Private Const SQLITE_CONSTRAINT = 19 ' Abort due to contraint violation
Private Const SQLITE_MISMATCH = 20 ' Data type mismatch
Private Const SQLITE_MISUSE = 21 ' Library used incorrectly
Private Const SQLITE_NOLFS = 22 ' Uses OS features not supported on host
Private Const SQLITE_AUTH = 23 ' Authorization denied
Private Const SQLITE_FORMAT = 24 ' Auxiliary database format error
Private Const SQLITE_RANGE = 25 ' 2nd parameter to sqlite3_bind out of range
Private Const SQLITE_NOTADB = 26 ' File opened that is not a database file
Private Const SQLITE_ROW = 100 ' sqlite3_step() has another row ready
Private Const SQLITE_DONE = 101 ' sqlite3_step() has finished executing
Public Enum SQLITETYPE
SQLITE_INTEGER = 1
SQLITE_FLOAT = 2
SQLITE_TEXT = 3
SQLITE_BLOB = 4
SQLITE_NULL = 5
End Enum
Private Const JULIANDAY_OFFSET As Double = 2415018.5
Private Function UTF8StringFromPtr(ByVal pUtf8String As Long) As String
Dim cSize As Long
UTF8StringFromPtr = ""
cSize = MultiByteToWideChar(65001, 0, pUtf8String, -1, 0, 0)
If cSize > 1 Then
UTF8StringFromPtr = String(cSize - 1, " ")
MultiByteToWideChar 65001, 0, pUtf8String, -1, StrPtr(UTF8StringFromPtr), cSize
End If
End Function
Private Function BytesFromPtr(ByVal lAddr As Long, ByVal lSize As Long) As Byte()
ReDim bvData(lSize - 1) As Byte
CopyMemory bvData(0), ByVal lAddr, lSize
BytesFromPtr = bvData
End Function
Private Function ToJulianDay(oleDate As Date) As Double
ToJulianDay = CDbl(oleDate) + JULIANDAY_OFFSET
End Function
Private Function FromJulianDay(julianDay As Double) As Date
FromJulianDay = CDate(julianDay - JULIANDAY_OFFSET)
End Function
Public Function Errmsg(ByVal db As Long) As Long
Errmsg = UTF8StringFromPtr(ite__errmsg(db))
End Function
Public Function Errcode(ByVal db As Long) As Long
Errcode = ite__errcode(db)
End Function
Public Function Libversion() As String
Libversion = UTF8StringFromPtr(ite__libversion)
End Function
Private Function DB_Open(ByVal DBFile As String, ByRef db As Long) As Long
DB_Open = ite__open((DBFile), db)
End Function
Public Function DBOpen(dbname&)
'打开数据库
DB_Open dbname, m_DB
End Function
Private Function DB_Close(ByVal db As Long) As Long
DB_Close = ite__close(db)
End Function
Public Function DBClose()
'关闭数据库
DB_Close m_DB
End Function
Public Function Execute(ByVal db As Long, ByVal zSQL As String) As Long
Execute = ite__exec(db, zSQL)
End Function
Public Function GetTable(ByVal db As Long, ByVal zSQL As String, ByRef ResultPtr As Long, ByRef rRowCount As Long, ByRef rColCount As Long) As Long
GetTable = ite__get_table(db, zSQL, ResultPtr, rRowCount, rColCount)
End Function
Public Sub FreeTable(ByVal ResultPtr As Long)
Call ite__free_table(ResultPtr)
End Sub
Public Function GetData(ByVal ResultPtr As Long, ByVal RowIdx As Long, ByVal ColIdx As Long, ByVal ColCount As Long) As String
GetData = UTF8StringFromPtr(ite__get_data(ResultPtr, RowIdx, ColIdx, ColCount))
End Function
Public Function Changes(ByVal db As Long) As Long
Changes = ite__changes(db)
End Function
Public Function TotalChanges(ByVal db As Long) As Long
TotalChanges = ite__total_changes(db)
End Function
Public Function LastRowID(ByVal db As Long) As Long
LastRowID = ite__last_insert_rowid(db)
End Function
Public Function Prepare(ByVal db As Long, ByVal zSQL As String, ByRef hStmt As Long) As Long
Prepare = ite__prepare(db, zSQL, hStmt)
End Function
Public Function Finalize(ByVal hStmt As Long) As Long
Finalize = ite__finalize(hStmt)
End Function
Public Function Reset(ByVal hStmt As Long) As Long
Reset = ite__reset(hStmt)
End Function
Public Function Step(ByVal hStmt As Long) As Long
Step = ite__step(hStmt)
End Function
Public Function Movenext(ByVal hStmt As Long) As Boolean
Movenext = (ite__step(hStmt) = 100)
End Function
Public Function DataCount(ByVal hStmt As Long) As Long
DataCount = ite__data_count(hStmt)
End Function
Public Function ColumnName(ByVal hStmt As Long, ByVal ColNum As Long) As String
ColumnName = UTF8StringFromPtr(ite__column_name(hStmt, ColNum))
End Function
Public Function ColumnDecltype(ByVal hStmt As Long, ByVal ColNum As Long) As String
ColumnDecltype = UTF8StringFromPtr(ite__column_decltype(hStmt, ColNum))
End Function
Public Function ColumnType(ByVal hStmt As Long, ByVal ColNum As Long) As Long
ColumnType = ite__column_type(hStmt, ColNum)
End Function
Public Function ColumnCount(ByVal hStmt As Long) As Long
ColumnCount = ite__column_count(hStmt)
End Function
Public Function ColumnDouble(ByVal hStmt As Long, ByVal ColNum As Long) As Double
ColumnDouble = ite__column_double(hStmt, ColNum)
End Function
Public Function ColumnInt(ByVal hStmt As Long, ByVal ColNum As Long) As Double
ColumnInt = ite__column_int(hStmt, ColNum)
End Function
Public Function ColumnText(ByVal hStmt As Long, ByVal ColNum As Long) As String
ColumnText = UTF8StringFromPtr(ite__column_text(hStmt, ColNum))
End Function
Public Function ColumnBlob(ByVal hStmt As Long, ByVal ColNum As Long) As Byte()
Dim lAddr As Long, lSize As Long
lAddr = ite__column_blob(hStmt, ColNum)
lSize = ite__column_bytes(hStmt, ColNum)
ColumnBlob = BytesFromPtr(lAddr, lSize)
End Function
Public Function ColumnDate(ByVal hStmt As Long, ByVal ColNum As Long) As Date
ColumnDate = FromJulianDay(ite__column_double(hStmt, ColNum))
End Function
Public Function BindDate(ByVal hStmt As Long, ByVal ParamNum As Long, ByVal nValue As Date) As Long
BindDate = ite__bind_double(hStmt, ParamNum, ToJulianDay(nValue))
End Function
Public Function BindBlob(ByVal hStmt As Long, ByVal ParamNum As Long, pData() As Byte) As Long
BindBlob = ite__bind_blob(hStmt, ParamNum, VarPtr(pData(0)), UBound(pData) + 1)
End Function
Public Function BindInt(ByVal hStmt As Long, ByVal ParamNum As Long, ByVal nValue As Long) As Long
BindInt = ite__bind_int(hStmt, ParamNum, nValue)
End Function
Public Function BindDouble(ByVal hStmt As Long, ByVal ParamNum As Long, ByVal nValue As Double) As Long
BindDouble = ite__bind_double(hStmt, ParamNum, nValue)
End Function
Public Function BindText(ByVal hStmt As Long, ByVal ParamNum As Long, ByVal szValue As String) As Long
BindText = ite__bind_text(hStmt, ParamNum, szValue)
End Function
Public Function UpdateBlob(ByVal db As Long, ByVal zSQL As String, pData() As Byte) As Long
Dim sqlite3_stmt As Long
If Prepare(db, zSQL, sqlite3_stmt) = 0 Then
UpdateBlob = BindBlob(sqlite3_stmt, 1, pData)
Step sqlite3_stmt
Finalize sqlite3_stmt
End If
End Function
Public Function Compact(ByVal db As Long) As Long
Compact = Execute(db, "VACUUM")
End Function
Public Function RowNumber(ByVal db As Long, ByVal zTable As String, Optional ByVal zFilter As String) As Long
Dim sqlite3_stmt As Long, zSQL As String
zSQL = "select count(*) from " & zTable
If Len(zFilter) > 0 Then zSQL = zSQL & " where " & zFilter
If Prepare(db, zSQL, sqlite3_stmt) = 0 Then
Step sqlite3_stmt
RowNumber = ColumnInt(sqlite3_stmt, 0)
Finalize sqlite3_stmt
End If
End Function

复制代码

lemondian · 发表于 2014-3-10 19:30:52

好东东：
近来我也在看这东西，只可惜与ADO相差很远。
看起来有点困难！

楼上的兄弟：为何不上个附件呢？

votasee · 发表于 2014-4-27 14:57:18

本帖最后由 votasee 于 2014-4-27 23:45 编辑

湿兄总算更新你的stdcall的封装DLL了...另外还加上了实例...不错..

不过楼上快手又是作了些什么劳动...写写清楚吗.
你改写的一些东西.似乎没法成功运行...比如RUNSQL

dhbin · 发表于 2014-8-31 17:25:34

提示: 作者被禁止或删除内容自动屏蔽

dhbin · 发表于 2014-9-3 21:29:29

提示: 作者被禁止或删除内容自动屏蔽

dhbin · 发表于 2014-9-8 08:22:11

提示: 作者被禁止或删除内容自动屏蔽

		自动登录	找回密码
密码			立即注册

dhbin dhbin 当前离线积分 17076 IP卡狗仔卡头像被屏蔽	发表于 2014-8-31 17:25:34 \| 显示全部楼层提示: 作者被禁止或删除内容自动屏蔽
dhbin dhbin 当前离线积分 17076 IP卡狗仔卡头像被屏蔽
	回复支持反对使用道具举报

dhbin dhbin 当前离线积分 17076 IP卡狗仔卡头像被屏蔽	发表于 2014-9-3 21:29:29 \| 显示全部楼层提示: 作者被禁止或删除内容自动屏蔽
dhbin dhbin 当前离线积分 17076 IP卡狗仔卡头像被屏蔽
	回复支持反对使用道具举报

dhbin dhbin 当前离线积分 17076 IP卡狗仔卡头像被屏蔽	发表于 2014-9-8 08:22:11 \| 显示全部楼层提示: 作者被禁止或删除内容自动屏蔽
dhbin dhbin 当前离线积分 17076 IP卡狗仔卡头像被屏蔽
	回复支持反对使用道具举报

VBGood网站全文搜索

[原创] 更新了下Sqlite3库，增加了使用事例。

本帖子中包含更多资源

点评

评分

本帖被以下淘专辑推荐:

点评