Code:
'SQLiteClass1.inc Declare Function sqlite3_open CDecl Lib "sqlite3.dll" Alias "sqlite3_open" (zFilename As Asciiz, mHDB As Long) As Long Declare Sub sqlite3_close CDecl Lib "sqlite3.dll" Alias "sqlite3_close" (ByVal mHDB As Long) Declare Function sqlite_get_table CDecl Lib "sqlite3.dll" Alias "sqlite3_get_table" (ByVal mHDB As Long, szSql As Asciiz, lpTable As Long, nRow As Long, nColumn As Long, lpErrMsg As Long) As Long Declare Function sqlite_free_table CDecl Lib "sqlite3.dll" Alias "sqlite3_free_table" (ByVal lpTable As Long Ptr) As Long Declare Function sqlite3_errmsg CDecl Lib "sqlite3.dll" Alias "sqlite3_errmsg" (ByVal mHDB As Long) As Long 'simple little class for working with local SQLite databases ' need: latest DLL from SQLite.org ' ' check [URL]http://planetsquires.com/[/URL] for heavy duty SQLite work ' 'Open("database"); ' opens existing database ' creates database and opens it if file doesn't exist ' 'Execute(); execute SQL statement that doesn't return a result: "CREATE TABLE ..." 'Quickie(); execute statement that only returns one result: "SELECT COUNT(*)" ' 'Queries: ' SelectBegin("select * from ...") : Queries must be closed; SelectEnd() ' ' RowCount() = number of rows in query results ' ColumnCount() = number of columns in query results ' ' First(), Next(), Last(), Previous(); move around query results ' Get() or GetAt() to get column's value in query results ' ' 'query results indexes are ONE based ' first row = 1; first column in result = 1 ' 'you can use Column Name or the order of the Column in the statement to get a column's value ' Get("name"); GetAt(1) Class SQLiteDatabaseC Instance mHDB As Long Instance mPTable As Long Ptr Instance mColumns() As String Instance mRowCount As Long Instance mColCount As Long Instance mRowNo As Long Instance mFirstCol As Long Interface SQLiteDatabaseI Inherit IUnknown Method Open(ByVal file As String) As Long 'Create or Open a database file 'True/False success If Len(file) Then If sqlite3_open(ByVal StrPtr(file), mHDB) = 0 Then Method = 1 Else mHDB = 0 End If End If End Method Method Close() 'close database file sqlite3_close(mHDB) mHDB = 0 End Method Method ErrorMessage() As String 'return last error message 'Thanks to Don Dickinson Local pzErr As Asciiz Ptr pzErr = sqlite3_errmsg(mHDB) If pzErr Then Method = @pzErr End Method Method Fix(ByVal s As String) As String 'escape single quotes in string Replace "'" With "''" In s Method = s End Method Method Execute(ByVal sql As String) As Long 'execute an SQL statement with NO return 'True/False success ' "CREATE TABLE ...", "BEGIN TRANSACTION", "END TRANSACTION", ...etc Local lpTable As Long Ptr Local lpErrorSz As Long Local RowCount&, ColCount& Method = IIf&( sqlite_get_table(mHDB, ByVal StrPtr(sql), lpTable, RowCount&, ColCount&, lpErrorSz)=0, 1, 0 ) sqlite_free_table lpTable End Method Method Quickie(ByVal sql As String) As String 'return a value from an SQL statement that will have only one return ' SELECT COUNT(*) ... If me.SelectBegin(sql) And me.First() Then Method = me.GetAt(1) me.SelectEnd() End Method Method SelectBegin(ByVal sql As String) As Long 'execute a query and return a record set ' MUST be freed: SelectEnd() 'True/False success ' thanks to Terence McDonnell Local i As Long Local lpErrorSz As Long Local pzCol As Asciiz Ptr mPTable = 0 Erase mColumns() mRowCount = 0 mColCount = 0 mRowNo = 0 mFirstCol = 0 If Len(sql) Then If sqlite_get_table(mHDB, ByVal StrPtr(sql), mPTable, mRowCount, mColCount, lpErrorSz) = 0 Then If mRowCount = 0 Then Method = 1 'command succeed - no return sqlite_free_table mPTable Else ReDim mColumns(1 To mColCount) For i = 1 To mColCount pzCol = @mPTable[i - 1] mColumns(i) = @pzCol Next i Method = 1 End If End If End If End Method Method SelectEnd() 'close query ' MUST close queries to free sqlite internal table If mRowCount Then sqlite_free_table mPTable mPTable = 0 Erase mColumns() mRowCount = 0 mColCount = 0 mRowNo = 0 mFirstCol = 0 End Method Method RowCount() As Long 'get query result row count Method = mRowCount End Method Method ColumnCount() As Long 'get query result column count Method = mColCount End Method Method First() As Long 'move to first row in query results 'True/False success If mRowCount Then mRowNo = 1 mFirstCol = mRowNo * mColCount Method = 1 End If End Method Method Last() As Long 'move to last row in query results 'True/False success If mRowCount Then mRowNo = mRowCount mFirstCol = mRowNo * mColCount Method = 1 End If End Method Method Next() As Long 'move to next row in query results 'True/False success If mRowCount Then Incr mRowNo If mRowNo > 0 And mRowNo <= mRowCount Then mFirstCol = mRowNo * mColCount Method = 1 End If End If End Method Method Previous() As Long 'move to previous row in query results 'True/False success If mRowCount Then Decr mRowNo If mRowNo > 0 And mRowNo <= mRowCount Then mFirstCol = mRowNo * mColCount Method = 1 End If End If End Method Method GoTo(ByVal row As Long) As Long 'move to row in query results 'True/False success If mRowCount And row > 0 And row <= mRowCount Then mRowNo = row mFirstCol = mRowNo * mColCount Method = 1 End If End Method Method Get(ByVal Column As String) As String 'get value for column on current row of query results If mRowNo > 0 And mRowNo <= mRowCount Then Local x, ndx As Long x = me.ColumnNo(Column) ndx = mFirstCol + x - 1 Local pzCol As Asciiz Ptr pzCol = @mPTable[ndx] Method = @pzCol End If End Method Method pszGet(ByVal Column As String) As Long 'get value for column on current row of query results 'returns ASCIIZ ptr to value ' lot faster way to load ListView which is expecting an ASCIIZ ptr If mRowNo > 0 And mRowNo <= mRowCount Then Local x, ndx As Long x = me.ColumnNo(Column) ndx = mFirstCol + x - 1 Method = @mPTable[ndx] End If End Method Method GetAt(ByVal columnNo As Long) As String 'get value for columnNo on current row of query results If mRowNo > 0 And mRowNo <= mRowCount Then If columnNo > 0 And columnNo <= UBound(mColumns) Then Local ndx As Long ndx = mFirstCol + columnNo - 1 Local pzCol As Asciiz Ptr pzCol = @mPTable[ndx] Method = @pzCol End If End If End Method Method pszGetAt(ByVal columnNo As Long) As Long 'get value for columnNo on current row of query results 'returns ASCIIZ ptr to value ' lot faster way to load ListView which is expecting an ASCIIZ ptr If mRowNo > 0 And mRowNo <= mRowCount Then If columnNo > 0 And columnNo <= UBound(mColumns) Then Local ndx As Long ndx = mFirstCol + columnNo - 1 Method = @mPTable[ndx] End If End If End Method Method ColumnNo(ByVal Column As String) As Long 'return position of column in query results 'zero if not found 'ToDo: replace with hash or trie Local x As Long Array Scan mColumns(), Collate UCase, = Column, To x Method = x End Method Method Column(ByVal columnNo As Long) As String 'get column name for columnNo in query results 'ONE based index If columnNo > 0 And columnNo <= UBound(mColumns) Then Method = mColumns(columnNo) End Method End Interface End Class
Comment