-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathsqlite.bas
298 lines (202 loc) · 7.91 KB
/
sqlite.bas
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
'======================================================================================
' QB64 SQLite routines - May of 2022
'
' (c)sadLogic 2022 (c)All of humankind Written in occupied Kherson, Ukraine
'======================================================================================
'--- UNREM when editing
'''''$Include: 'sqlite.bi'
'$Include: 'sqlite_helpers.bas'
DECLARE DYNAMIC LIBRARY "sqlite3"
FUNCTION sqlite3_errmsg$ (BYVAL DbhANLDE AS _OFFSET)
FUNCTION sqlite3_open& (filename AS STRING, BYVAL ppDb AS _OFFSET)
SUB sqlite3_open (filename AS STRING, BYVAL ppDb AS _OFFSET)
FUNCTION sqlite3_prepare& (BYVAL DbhANLDE AS _OFFSET, zSql AS STRING, BYVAL nByte AS LONG, BYVAL ppStmt AS _OFFSET, BYVAL pzTail AS _OFFSET)
FUNCTION sqlite3_step& (BYVAL sqlite3_stmt AS _OFFSET)
FUNCTION sqlite3_changes& (BYVAL sqlite3_stmt AS _OFFSET)
FUNCTION sqlite3_column_count& (BYVAL sqlite3_stmt AS _OFFSET)
FUNCTION sqlite3_column_type& (BYVAL sqlite3_stmt AS _OFFSET, BYVAL iCol AS LONG)
FUNCTION sqlite3_column_name$ (BYVAL sqlite3_stmt AS _OFFSET, BYVAL N AS LONG)
FUNCTION sqlite3_column_text$ (BYVAL sqlite3_stmt AS _OFFSET, BYVAL iCol AS LONG)
FUNCTION sqlite3_column_bytes& (BYVAL sqlite3_stmt AS _OFFSET, BYVAL iCol AS LONG)
SUB sqlite3_finalize (BYVAL sqlite3_stmt AS _OFFSET)
SUB sqlite3_close (BYVAL DbhANLDE AS _OFFSET)
FUNCTION sqlite3_last_insert_rowid& (BYVAL DbhANLDE AS _OFFSET)
END DECLARE
SUB DB_Open (db AS STRING, CreateIfMissing AS _BYTE)
'--- just call the function and dump the return value
'--- db.ErrMsg will be set if there is an error
DIM junk AS _BYTE
junk = DB_Open(db, CreateIfMissing)
END SUB
FUNCTION DB_Open%% (db AS STRING, CreateIfMissing AS _BYTE)
dbOBJ.ErrMsg = "" '--- clear err message
dbOBJ.dbName = CreateCstrX$(db) '--- set db name
IF NOT _FILEEXISTS(dbOBJ.dbName) AND CreateIfMissing = 0 THEN
dbOBJ.ErrMsg = "SQLite DB file not found"
DB_Open = 0 '--- return FALSE
EXIT FUNCTION
END IF
'--- try and open db file,if missing it will be created
DIM ret AS INTEGER
ret = sqlite3_open(dbOBJ.dbName, _OFFSET(dbOBJ.hSqliteDB))
IF ret = SQLITE_OK THEN
DB_Open = -1
ELSE
DB_Open = 0 '--- failed, get an error code
dbOBJ.ErrMsg = "Error opening DB, code: " + STR$(ret)
END IF
END FUNCTION
FUNCTION DB_LastInsertedRowID& ()
dbOBJ.ErrMsg = "" '--- clear last error message
DB_LastInsertedRowID = sqlite3_last_insert_rowid(dbOBJ.hSqliteDB)
END FUNCTION
FUNCTION DB_AffectedRows& ()
dbOBJ.ErrMsg = "" '--- clear last error message
DB_AffectedRows = sqlite3_changes(dbOBJ.hSqliteDB)
END FUNCTION
FUNCTION DB_GetDataType$ (dataType AS LONG)
SELECT CASE dataType
CASE SQLITE_INTEGER
DB_GetDataType = "INTEGER"
CASE SQLITE_FLOAT
DB_GetDataType = "FLOAT"
CASE SQLITE_BLOB
DB_GetDataType = "BLOB"
CASE SQLITE_NULL
DB_GetDataType = "NULL"
CASE SQLITE_TEXT
DB_GetDataType = "TEXT"
END SELECT
END FUNCTION
SUB DB_Close ()
sqlite3_close dbOBJ.hSqliteDB
END SUB
FUNCTION DB_GetErrMsg$ ()
DIM em$: em$ = dbOBJ.ErrMsg
'--- if there is no error then
'--- ErrMsg can contain "not an error"
IF INSTR(em$, "not an e") OR em$ = "" THEN
DB_GetErrMsg = ""
ELSE
DB_GetErrMsg = em$
END IF
END FUNCTION
FUNCTION DB_RowCount& (RS() AS SQLITE_RESULTSET)
DB_RowCount& = UBOUND(RS, 2)
END FUNCTION
FUNCTION DB_ColCount& (RS() AS SQLITE_RESULTSET)
DB_ColCount& = UBOUND(RS, 1)
END FUNCTION
FUNCTION DB_GetField$ (RS() AS SQLITE_RESULTSET, Row AS LONG, FieldName AS STRING)
'--- get a field value from a row
DIM ndx AS LONG
DIM tmpFName$: tmpFName$ = LCASE$(FieldName)
FOR ndx = 1 TO UBOUND(RS, 1)
IF LCASE$(RS(ndx, Row).columnName) = tmpFName$ THEN
DB_GetField$ = RS(ndx, Row).value
EXIT FUNCTION
END IF
NEXT
'--- blow up!
dbOBJ.ErrMsg = "Field not found"
PRINT dbOBJ.ErrMsg
ERROR 100
END FUNCTION
FUNCTION DB_ExecQuerySingleResult$ (sql_command AS STRING)
REDIM RS(1 TO 1, 1 TO 1) AS SQLITE_RESULTSET
IF DB_ExecQuery(sql_command, RS()) THEN
DB_ExecQuerySingleResult$ = RS(1, 1).value
EXIT FUNCTION
END IF
PRINT dbOBJ.ErrMsg
ERROR 100
END FUNCTION
SUB DB_ExecQuery (sql_command AS STRING, RS() AS SQLITE_RESULTSET)
'--- just call the function and dump the return value
'--- db.ErrMsg will be set if there is an error
DIM junk AS LONG
junk = DB_ExecQuery(sql_command, RS())
END SUB
FUNCTION DB_ExecQuery& (sql_command AS STRING, RS() AS SQLITE_RESULTSET)
dbOBJ.ErrMsg = "" '--- clear last error message
DIM retVal AS INTEGER
retVal = sqlite3_prepare(dbOBJ.hSqliteDB, sql_command, LEN(sql_command), _OFFSET(dbOBJ.hSqliteStmt), 0)
IF retVal = SQLITE_OK THEN
DIM AS LONG colCount: colCount = _
sqlite3_column_count(dbOBJ.hSqliteStmt)
DIM AS LONG column, row, ret, tmpCol
ret = sqlite3_step(dbOBJ.hSqliteStmt)
IF ret = SQLITE_ROW THEN
DB_ExecQuery = -1 '--- return TRUE, we are good
'---build cursor / resultset
DO
row = row + 1
FOR column = 0 TO colCount - 1
REDIM _PRESERVE RS(colCount, row) AS SQLITE_RESULTSET
tmpCol = column + 1
RS(tmpCol, row).ColumnTYPE = sqlite3_column_type(dbOBJ.hSqliteStmt, column)
RS(tmpCol, row).columnName = sqlite3_column_name(dbOBJ.hSqliteStmt, column)
RS(tmpCol, row).value = sqlite3_column_text(dbOBJ.hSqliteStmt, column)
NEXT
ret = sqlite3_step(dbOBJ.hSqliteStmt)
LOOP WHILE ret = SQLITE_ROW
ELSE
'--- return FALSE
DB_ExecQuery = 0
'--- do some error catching
dbOBJ.ErrMsg = sqlite3_errmsg$(dbOBJ.hSqliteDB)
END IF
ELSE
'--- return FALSE
DB_ExecQuery = 0
'--- do some error catching
dbOBJ.ErrMsg = sqlite3_errmsg$(dbOBJ.hSqliteDB)
END IF
sqlite3_finalize dbOBJ.hSqliteStmt
END FUNCTION
SUB DB_ExecNonQuery (sql_command AS STRING)
'--- just call the function and dump the return value
'--- db.ErrMsg will be set if there is an error
DIM junk AS _BYTE
junk = DB_ExecNonQuery(sql_command)
END SUB
FUNCTION DB_ExecNonQuery& (sql_command AS STRING)
dbOBJ.ErrMsg = "" '--- clear last error message
DB_ExecNonQuery = 0 '--- assume bad things - set return to FALSE
DIM retOK AS LONG
retOK = sqlite3_prepare(dbOBJ.hSqliteDB, sql_command, _
LEN(sql_command), _OFFSET(dbOBJ.hSqliteStmt), 0)
IF retOK = SQLITE_OK THEN
DIM AS LONG ret: ret = sqlite3_step(dbOBJ.hSqliteStmt)
IF ret = SQLITE_DONE THEN
DIM AffectedRows AS LONG: AffectedRows = DB_AffectedRows
IF AffectedRows = 0 THEN
'--- return TRUE, all is good
DB_ExecNonQuery = -1
ELSE
'--- return number of rows affected, this will be a TRUE
DB_ExecNonQuery = AffectedRows
END IF
ELSE
'--- populate the err message var
dbOBJ.ErrMsg = sqlite3_errmsg$(dbOBJ.hSqliteDB)
END IF
ELSE retOK = SQLITE_ERROR
'---populate the err message var
dbOBJ.ErrMsg = sqlite3_errmsg$(dbOBJ.hSqliteDB)
END IF
'--- cleanup mem in the statement
sqlite3_finalize dbOBJ.hSqliteStmt
END FUNCTION
FUNCTION DB_SqlParse$ (sql_str$, values_str$)
CONST ParseChar = "?#"
DIM ndx%: ndx% = 0
DIM tmp$: tmp$ = sql_str$
REDIM arrValues$(0)
strSplitX values_str$, ParseChar, arrValues$()
DO WHILE INSTR(tmp$, ParseChar)
tmp$ = strReplaceOneX$(tmp$, ParseChar, arrValues$(ndx%))
ndx% = ndx% + 1
LOOP
DB_SqlParse = tmp$
END FUNCTION