SQLite in QB64 - Windows only (so far)

[SpriggsySpriggs]

Since I do so much programming with SQL, I decided to try my hand at SQLite. SQLite uses SQL syntax to interact with a file as a database. It is used in millions of applications and software across the world. It is quicker than standard file operations and quicker than algorithms you could write yourself to get data from a file. I've started using it and all the testing so far has been magnificent. SQLite could be used to replace random access files and possibly even INIs. If you already know SQL then you will feel right at home using SQLite. I've kept the function names the same as the ones I'm using in my ODBC project and my MySQL rewrite.

Here is the code I've been using in my tests:

Code: QB64 $NOPREFIX: [Select]

1. Option Explicit
2. $NoPrefix
3. $Console:Only
4. $ExeIcon:'databases.ico'
5. Icon
6.  
7. Const SQLITE_ABORT = 4
8. Const SQLITE_AUTH = 23
9. Const SQLITE_BUSY = 5
10. Const SQLITE_CANTOPEN = 14
11. Const SQLITE_CONSTRAINT = 19
12. Const SQLITE_CORRUPT = 11
13. Const SQLITE_DONE = 101
14. Const SQLITE_EMPTY = 16
15. Const SQLITE_ERROR = 1
16. Const SQLITE_FORMAT = 24
17. Const SQLITE_FULL = 13
18. Const SQLITE_INTERNAL = 2
19. Const SQLITE_INTERRUPT = 9
20. Const SQLITE_IOERR = 10
21. Const SQLITE_LOCKED = 6
22. Const SQLITE_MISMATCH = 20
23. Const SQLITE_MISUSE = 21
24. Const SQLITE_NOLFS = 22
25. Const SQLITE_NOMEM = 7
26. Const SQLITE_NOTADB = 26
27. Const SQLITE_NOTFOUND = 12
28. Const SQLITE_NOTICE = 27
29. Const SQLITE_OK = 0
30. Const SQLITE_PERM = 3
31. Const SQLITE_PROTOCOL = 15
32. Const SQLITE_RANGE = 25
33. Const SQLITE_READONLY = 8
34. Const SQLITE_ROW = 100
35. Const SQLITE_SCHEMA = 17
36. Const SQLITE_TOOBIG = 18
37. Const SQLITE_WARNING = 28
38.  
39. Const SQLITE_INTEGER = 1
40. Const SQLITE_FLOAT = 2
41. Const SQLITE_BLOB = 4
42. Const SQLITE_NULL = 5
43. Const SQLITE_TEXT = 3
44.  
45. Type SQLITE_FIELD
46.     As Long type
47.     As String columnName, value
48. End Type
49.  
50. ConsoleTitle "SQLite Test"
51.  
52. Declare Dynamic Library "sqlite3"
53.     Function sqlite3_open& (filename As String, Byval ppDb As Offset)
54.     Sub sqlite3_open (filename As String, Byval ppDb As Offset)
55.     Function sqlite3_prepare& (ByVal db As Offset, zSql As String, Byval nByte As Long, Byval ppStmt As Offset, Byval pzTail As Offset)
56.     Function sqlite3_step& (ByVal sqlite3_stmt As Offset)
57.     Function sqlite3_changes& (ByVal sqlite3_stmt As Offset)
58.     Function sqlite3_column_count& (ByVal sqlite3_stmt As Offset)
59.     Function sqlite3_column_type& (ByVal sqlite3_stmt As Offset, Byval iCol As Long)
60.     Function sqlite3_column_name$ (ByVal sqlite3_stmt As Offset, Byval N As Long)
61.     Function sqlite3_column_text$ (ByVal sqlite3_stmt As Offset, Byval iCol As Long)
62.     Function sqlite3_column_bytes& (ByVal sqlite3_stmt As Offset, Byval iCol As Long)
63.     Sub sqlite3_finalize (ByVal sqlite3_stmt As Offset)
64.     Sub sqlite3_close (ByVal db As Offset)
65. End Declare
66.  
67. Dim Shared As Offset hSqlite, hStmt
68. Dim As String sql
69. ReDim Shared As SQLITE_FIELD DB_Result(1 To 1, 1 To 1)
70.  
71. Dim As String db: db = "test.db"
72. If DB_Open(db) Then
73.     Dim As String conTitle: conTitle = "SQLite Test - " + db: ConsoleTitle conTitle
74.     If DB_QUERY("SELECT * FROM test") = SQLITE_OK Then
75.         Dim As Long column, row
76.         For row = 1 To UBound(DB_Result, 2)
77.             Print "Row"; row
78.             For column = 1 To UBound(DB_Result, 1)
79.                 Print , GetDataType(DB_Result(column, row).type), DB_Result(column, row).columnName, DB_Result(column, row).value
80.             Next
81.         Next
82.     End If
83.     'If DB_QUERY("INSERT INTO test(column2) VALUES ('And now, a fifth row!');") = SQLITE_OK Then
84.     '    Print DB_AffectedRows
85.     'End If
86.     'If DB_QUERY("UPDATE test SET column2 = 'And now, a fourth row!' WHERE column1 = '4';") = SQLITE_OK Then
87.     '    Print DB_AffectedRows
88.     'End If
89. End If
90.  
91. DB_Close
92.  
93. Function DB_Open%% (sqlitedb As String)
94.     If sqlite3_open(sqlitedb, Offset(hSqlite)) = SQLITE_OK Then DB_Open = -1 Else DB_Open = 0
95. End Function
96.  
97. Sub DB_Open (sqlitedb As String)
98.     sqlite3_open sqlitedb, Offset(hSqlite)
99. End Sub
100.  
101. Sub DB_QUERY (sql_command As String)
102.     If sqlite3_prepare(hSqlite, sql_command, Len(sql_command), Offset(hStmt), 0) = SQLITE_OK Then
103.         Dim As Long colCount: colCount = sqlite3_column_count(hStmt)
104.         Dim As Long column, row, ret
105.         ret = sqlite3_step(hStmt)
106.         If ret = SQLITE_ROW Then
107.             Do
108.                 row = row + 1
109.                 For column = 0 To colCount - 1
110.                     ReDim Preserve As SQLITE_FIELD DB_Result(colCount, row)
111.                     DB_Result(column + 1, row).type = sqlite3_column_type(hStmt, column)
112.                     DB_Result(column + 1, row).columnName = sqlite3_column_name(hStmt, column)
113.                     DB_Result(column + 1, row).value = sqlite3_column_text(hStmt, column)
114.                 Next
115.                 ret = sqlite3_step(hStmt)
116.             Loop While ret = SQLITE_ROW
117.         Else
118.             'do some error catching
119.         End If
120.         sqlite3_finalize hStmt
121.     End If
122. End Sub
123.  
124. Function DB_QUERY& (sql_command As String)
125.     If sqlite3_prepare(hSqlite, sql_command, Len(sql_command), Offset(hStmt), 0) = SQLITE_OK Then
126.         Dim As Long colCount: colCount = sqlite3_column_count(hStmt)
127.         Dim As Long column, row, ret
128.         ret = sqlite3_step(hStmt)
129.         If ret = SQLITE_ROW Then
130.             DB_QUERY = SQLITE_OK
131.             Do
132.                 row = row + 1
133.                 For column = 0 To colCount - 1
134.                     ReDim Preserve As SQLITE_FIELD DB_Result(colCount, row)
135.                     DB_Result(column + 1, row).type = sqlite3_column_type(hStmt, column)
136.                     DB_Result(column + 1, row).columnName = sqlite3_column_name(hStmt, column)
137.                     DB_Result(column + 1, row).value = sqlite3_column_text(hStmt, column)
138.                 Next
139.                 ret = sqlite3_step(hStmt)
140.             Loop While ret = SQLITE_ROW
141.         ElseIf ret = SQLITE_DONE Then DB_QUERY = SQLITE_OK
142.         Else DB_QUERY = SQLITE_ERROR
143.             'do some error catching
144.         End If
145.         sqlite3_finalize hStmt
146.     End If
147. End Function
148.  
149. Function DB_AffectedRows&
150.     DB_AffectedRows = sqlite3_changes(hSqlite)
151. End Function
152.  
153. Function GetDataType$ (dataType As Long)
154.     Select Case dataType
155.         Case SQLITE_INTEGER
156.             GetDataType = "INTEGER"
157.         Case SQLITE_FLOAT
158.             GetDataType = "FLOAT"
159.         Case SQLITE_BLOB
160.             GetDataType = "BLOB"
161.         Case SQLITE_NULL
162.             GetDataType = "NULL"
163.         Case SQLITE_TEXT
164.             GetDataType = "TEXT"
165.     End Select
166. End Function
167.  
168. Sub DB_Close
169.     sqlite3_close hSqlite
170. End Sub

A screenshot of the above code running:
 

I've attached the database file I'm working with. You can run regular SQL queries (INSERT, UPDATE, DELETE, SELECT, etc.) against it and test it out for yourself. Personally, SQLite will be something I end up incorporating in future projects for myself.