|
Post by ppro06 on Apr 14, 2020 8:58:32 GMT -5
Good day, I am new to APIs, I have read a little bit on APIs from the LB encyclopedia, I have also acquainted myself with SQlite3. I have also looked at the interfaces that have been shared, from Consolesqlite.bas to ionSQL.bas. I would like to build an interface myself but I get stuck trying to adapt Consolesqlite.bas to run with SQlite3.dll (2020 version). When I run the code, it doesn't return a handle or it produces a 'runtime protection violation' error or an 'sqlite misuse error' in windows 10. Does anybody have any insight into how to overcome these errors?
|
|
|
Post by Chris Iverson on Apr 14, 2020 18:29:49 GMT -5
Sorry, do you have links to the code and DLL you're trying to use?
|
|
|
Post by metro on Apr 14, 2020 19:05:44 GMT -5
|
|
|
Post by Chris Iverson on Apr 14, 2020 19:35:46 GMT -5
I do recall those, and I do note that there was a wrapper library for SQLite in LB. I'll go over some of the SQLite3 API quickly, but that may have been for a reason.
|
|
|
Post by Brandon Parker on Apr 14, 2020 20:42:14 GMT -5
Here's some Liberty BASIC functions to wrap up the Wrapper DLL's function calls. I have used this pretty extensively in the past.
The capitalizeFirstLetter$() and sleep() functions are not absolutely necessary so you should be able to remove them if you would like. These are all of the functions that I use; I can't remember if the Wrapper DLL has more...
You can also uncomment the "On Error" traps within the functions if you would like to do that as well, but you would need to define the DebuggerDisplayErrors$() function (this was part of an internal debugging function I implemented in an application at my old job) as well as uncomment the "Exit Function" line above each [Error] label.
Global True : True = 1 Global False : False = 0
'Initialize the SQL Control Structure Struct SQLDB, SQLiteInitialized As boolean, _ SQLLiteOpen As boolean, _ DBHandle As long, _ LastMessage As ptr
Print initializeSQL() Print openSQLLiteDB(Your Database path goes here) Print GetLastMessage$() 'If you want, after any command ... Print Execute(Your SQL Command Goes Here in Quotes, SQLDB.DBHandle.struct) Print GetLastMessage$() Print closeSQL() Wait
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function initializeSQL() On Error GoTo [SQLOpenError] If Not(SQLDB.SQLiteInitialized.struct) Then Struct RecordSet, BOF As long,_ ' is True when CurrPos = 1 EOF As long,_ ' is True when CurrPos = Rows Handle As long,_ ' handle of recordset Rows As long,_ ' number of rows in recordset Cols As long,_ ' number of columns in recordset CurrPos As long,_ ' current row in recordset StrAdr As long ' address of data item
RecordSet.BOF.struct = _NULL RecordSet.EOF.struct = _NULL RecordSet.Handle.struct = _NULL RecordSet.Rows.struct = _NULL RecordSet.Cols.struct = _NULL RecordSet.CurrPos.struct = _NULL RecordSet.StrAdr.struct = "" + chr$(0)
SQLDB.SQLiteInitialized.struct = True SQLDB.LastMessage.struct = "SQLite Initialized" End If If Not(SQLDB.SQLLiteOpen.struct) Then Open "SQ3_4_LB.dll" For DLL As #SQLLite3 SQLDB.SQLLiteOpen.struct = True SQLDB.DBHandle.struct = _NULL SQLDB.LastMessage.struct = "SQLite Opened" initializeSQL = True Else initializeSQL = True End If Exit Function
[SQLOpenError] SQLDB.SQLLiteOpen.struct = False : initializeSQL = False ' result$ = DebuggerDisplayErrors$("SQLite Initialization Error") End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function closeSQL() On Error GoTo [SQLCloseError] result = Sleep(500) If SQLDB.DBHandle.struct <> _NULL Then result = ReleaseRecordset() result = closeSQLLiteDB(SQLDB.DBHandle.struct) SQLDB.DBHandle.struct = _NULL SQLDB.LastMessage.struct = "SQLite Database Closed" End If GoTo [CompleteClose] Exit Function [SQLCloseError] result = closeSQLLiteDB(SQLDB.DBHandle.struct) SQLDB.DBHandle.struct = _NULL SQLDB.LastMessage.struct = "SQLite Database Closed" [CompleteClose] If SQLDB.SQLLiteOpen.struct = True Then Close #SQLLite3 SQLDB.LastMessage.struct = "SQLite Closed" SQLDB.SQLLiteOpen.struct = False End If ' result$ = DebuggerDisplayErrors$("SQLite Exit Error") End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function createSQLLiteDB(DBPath$, DBName$, overwrite) ' On Error GoTo [Error] DataBase$ = DBPath$ + "\" + DBName$ + chr$(0) CallDLL #SQLLite3, "SQ3_4_LB_CreateDB", DataBase$ As ptr, _ overwrite As long, _ createSQLLiteDB As long SQLDB.DBHandle.struct = createSQLLiteDB ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function openSQLLiteDB(DBPath$, DBName$) ' On Error GoTo [Error] If Not(SQLDB.DBHandle.struct) Then DataBase$ = DBPath$ + "\" + DBName$ + chr$(0) CallDLL #SQLLite3, "SQ3_4_LB_OpenDB", DataBase$ As ptr, _ openSQLLiteDB As long SQLDB.DBHandle.struct = openSQLLiteDB Else openSQLLiteDB = SQLDB.DBHandle.struct End If ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function closeSQLLiteDB(DBHandle) ' On Error GoTo [Error] DataBase$ = DBPath$ + "\" + DBName$ + chr$(0) CallDLL #SQLLite3, "SQ3_4_LB_CloseDB", DBHandle As long, _ closeSQLLiteDB As long If closeSQLLiteDB Then SQLDB.DBHandle.struct = _NULL Else closeSQLLiteDB = False End If ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function Execute(SQLCommand$, DBHandle) 'execute an SQL command like INSERT UPDATE DELETE etc ' On Error GoTo [Error] SQLCommand$ = SQLCommand$ + chr$(0) CallDLL #SQLLite3, "SQ3_4_LB_Execute", SQLCommand$ As ptr, _ DBHandle As long, _ Execute As long ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function GetRecordset(SQLCommand$, DBHandle) 'execute an SQL 'SELECT' command ' On Error GoTo [Error] SQLCommand$ = SQLCommand$ + chr$(0) CallDLL #SQLLite3, "SQ3_4_LB_GetRecordset", SQLCommand$ As ptr, _ DBHandle As long, _ RecordSet As struct, _ GetRecordset As long ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function GetFieldIndexByName(fieldName$) ' On Error GoTo [Error] fieldName$ = fieldName$ + chr$(0) CallDLL #SQLLite3, "SQ3_4_LB_GetFieldIndexByName", fieldName$ As ptr, _ GetFieldIndexByName As long ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function GetFieldNameByIndex$(index) ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_GetFieldNameByIndex", index As long, _ fieldName As long GetFieldNameByIndex$ = Winstring(fieldName) + chr$(0) ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function GetRecordsetValueByName$(fieldName$) ' On Error GoTo [Error] fieldName$ = fieldName$ + chr$(0) CallDLL #SQLLite3, "SQ3_4_LB_GetRecordsetValueByName", fieldName$ As ptr, _ ret As long If ret Then GetRecordsetValueByName$ = Winstring(RecordSet.StrAdr.struct) + chr$(0) Else GetRecordsetValueByName$ = "SQL_ERROR_GETTING_VALUE_BY_NAME" SQLDB.LastMessage.struct = GetRecordsetValueByName$ End If ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function GetRecordsetValueByIndex$(index) ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_GetRecordsetValueByIndex", index As long, _ ret As long If ret Then GetRecordsetValueByIndex$ = Winstring(RecordSet.StrAdr.struct) + chr$(0) Else GetRecordsetValueByIndex$ = "SQL_ERROR_GETTING_VALUE_BY_INDEX" SQLDB.LastMessage.struct = GetRecordsetValueByIndex$ End If ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function GetRecordsetValueOfRow$(separator$) ' On Error GoTo [Error] separator$ = separator$ + chr$(0) CallDLL #SQLLite3, "SQ3_4_LB_GetRecordsetValueOfRow", separator$ As ptr, _ ret As long If ret Then GetRecordsetValueOfRow$ = Winstring(RecordSet.StrAdr.struct) + chr$(0) Else GetRecordsetValueOfRow$ = "SQL_ERROR_GETTING_VALUE_OF_ROW" SQLDB.LastMessage.struct = GetRecordsetValueOfRow$ End If ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function RecordsetMoveFirst() ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_RecordsetMoveFirst", ret As void ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function RecordsetMoveLast() ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_RecordsetMoveLast", ret As void ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function RecordsetMoveNext() ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_RecordsetMoveNext", ret As void ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$("RecordsetMoveNext") End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function RecordsetMovePrevious() ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_RecordsetMovePrevious", ret As void ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function RecordsetMoveToRow(row) ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_RecordsetMoveToRow", row As long, _ ret As void ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function GetLastSQLMessage$() ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_GetLastMessage", lastMessage As long SQLDB.LastMessage.struct = Trim$(capitalizeFirstLetter$(Winstring(lastMessage))) + chr$(0) GetLastSQLMessage$ = Winstring(SQLDB.LastMessage.struct)' + chr$(0) ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function SQL34Version$() ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_Version", version As long SQL34Version$ = Winstring(version) + chr$(0) ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function SQLLiteVersion$() ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_GetLibVersion", libVersion As long SQLLiteVersion$ = Winstring(libVersion) + chr$(0) ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function SQL34About$() ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_About", about As long SQL34About$ = Winstring(about) + chr$(0) ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function LastInsertRowID(DBHandle) ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_LastInsertRowID", DBHandle As long, _ LastInsertRowID As long ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function ReleaseRecordset() ' On Error GoTo [Error] CallDLL #SQLLite3, "SQ3_4_LB_ReleaseRecordset", ret As void ' Exit Function ' [Error] ' result$ = DebuggerDisplayErrors$() End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function capitalizeFirstLetter$(string$) string$ = Lower$(string$) Do Until Word$(" .(", (delim + 1), ".") = "" capitalizeFirstLetter$ = "" Do Until Word$(string$, (i + 1), Word$(" .(", (delim + 1), ".")) = "" If Instr("NOT AVAILABLE HSBY I/O SCAN TIMEOUT NG H/U C/D", Word$(Upper$(string$), (i + 1), Word$(" .(", (delim + 1), "."))) = False Then capitalizeFirstLetter$ = capitalizeFirstLetter$ + Word$(" .(", (delim + 1), ".") + _ Upper$(Left$(Word$(string$, (i + 1), Word$(" .(", (delim + 1), ".")), 1)) + _ Mid$(Word$(string$, (i + 1), Word$(" .(", (delim + 1), ".")), 2) Else capitalizeFirstLetter$ = capitalizeFirstLetter$ + " " + Word$(Upper$(string$), (i + 1), Word$(" .(", (delim + 1), ".")) End If i = (i + 1) Loop i = 0 : string$ = Mid$(capitalizeFirstLetter$, 2) delim = (delim + 1) Loop capitalizeFirstLetter$ = Trim$(Mid$(capitalizeFirstLetter$, 2)) End Function
'_________________________________________________________________________________________________________________________________________________________ '_________________________________________________________________________________________________________________________________________________________
Function Sleep(milliseconds) CallDLL #kernel32, "Sleep", milliseconds As ulong, _ ret As void End Function
{:0)
Brandon Parker
|
|
|
Post by Chris Iverson on Apr 15, 2020 0:43:00 GMT -5
I actually found it fairly easy to get the standard SQLite3 interface working in LB. There's a couple places that might be spotty, but overall, it works well.
That LB wrapper is better for quick and general queries, but I was even able to get bound parameters in prepared statements working(best way to prevent SQL injection is bound parameters).
All functions(that aren't my own custom ones) are closely named after their actual SQLite3 counterparts, with similar usage. I wrote this all just looking up the documentation on the SQLite3 website.
Here's the code I quickly whipped up tonight.
gosub [SQL3PrepGlobals]
print "SQL3Init() - ";SQL3Init()
hDB = 0 print "SQL3Open() - ";SQL3Open("test.db", hDB)
ret = SQL3ExecSingle(hDB, "CREATE TABLE test(a INT, b TEXT);") print "SQL3ExecSingle() - ";ret
if ret = SQLITE.DONE then print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(12,'blah');") print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(13,'stuff');") else print SQL3ErrStr$(ret) end if
'Attempting prepared statement with bound parameters SQL$ = "INSERT INTO test(a, b) VALUES(?, ?);" hStatement = 0 ret = SQL3Prepare(hDB, SQL$, hStatement) if ret <> SQLITE.OK then print "Bound prepared statement failed - ";ret;" - ";SQL3ErrStr$(ret) goto [skipBind] end if
ret = SQL3BindInt(hStatement, 1, 153) if ret <> SQLITE.OK then print "Bind int failed - ";ret;" - ";SQL3ErrStr$(ret) goto [finalSkipBind] end if
ret = SQL3BindText(hStatement, 2, "someoasidjrlkawergj") if ret <> SQLITE.OK then print "Bind text failed - ";ret;" - ";SQL3ErrStr$(ret) goto [finalSkipBind] end if
ret = SQL3Step(hStatement)
if ret <> SQLITE.DONE then Print "Executing bound statement failed - ";ret;" - ";SQL3ErrStr$(ret) end if
[finalSkipBind] ret = SQL3Finalize(hStatement)
[skipBind] SQL$ = "SELECT * FROM test;" hStatement = 0
ret = SQL3Prepare(hDB, SQL$, hStatement) if ret <> SQLLITE.OK then Print "SQL3Prepare() failed - ";ret;" - ";SQL3ErrStr$(ret) goto [skipSelect] end if
print print
do ret = SQL3Step(hStatement) if ret <> SQLITE.DONE and ret <> SQLITE.ROW then print "SQL3Step() error - ";ret;" - ";SQL3ErrStr$(ret) exit do end if
numCols = SQL3DataCount(hStatement)
if numCols <> 0 then for x = 0 to numCols - 1 print SQL3ColumnText$(hStatement, x), next x
print end if loop while ret <> SQLITE.DONE
print print
print "SQL3Finalize() - ";SQL3Finalize(hStatement)
[skipSelect]
print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "DROP TABLE test;")
print "SQL3Close() - ";SQL3Close(hDB)
print "SQL3Shutdown() - ";SQL3Shutdown()
end
'This function is provided as a shorthand for statements that can be executed 'in a single step, and don't return anything(CREATE, DROP, INSERT, etc) Function SQL3ExecSingle(hDB, SQL$) 'Prepare/compile the statement hStatement = 0 ret = SQL3Prepare(hDB, SQL$, hStatement) if ret <> SQLITE.OK then 'Statement preparation failed. Error code may have more info. goto [endFunc] end if
'Execute the statement. ret = SQL3Step(hStatement)
[endFunc] dummy = SQL3Finalize(hStatement) SQL3ExecSingle = ret End Function
Function SQL3Init() open "sqlite3" for DLL as #sqlite3
CallDLL #sqlite3, "sqlite3_initialize",_ SQL3Init as long End Function
Function SQL3Shutdown() CallDLL #sqlite3, "sqlite3_shutdown",_ SQL3Shutdown as long
close #sqlite3 End Function
Function SQL3Open(fileName$, byref hDB) struct a, b as ulong
CallDLL #sqlite3, "sqlite3_open",_ fileName$ as ptr,_ a as struct,_ SQL3Open as long
hDB = a.b.struct End Function
Function SQL3Close(hDB) CallDLL #sqlite3, "sqlite3_close",_ hDB as ulong,_ SQL3Close as long End Function
Function SQL3Prepare(hDB, SQL$, byref hStatement) sqlLen = len(SQL$)
struct a, b as ulong
CallDLL #sqlite3, "sqlite3_prepare_v2",_ hDB as ulong,_ SQL$ as ptr,_ sqlLen as long,_ a as struct,_ _NULL as long,_ SQL3Prepare as long
hStatement = a.b.struct End Function
Function SQL3Finalize(hStatement) CallDLL #sqlite3, "sqlite3_finalize",_ hStatement as ulong,_ SQL3Finalize as long End Function
Function SQL3Step(hStatement) CallDLL #sqlite3, "sqlite3_step",_ hStatement as ulong,_ SQL3Step as long End Function
Function SQL3Reset(hStatement) CallDLL #sqlite3, "sqlite3_reset",_ hStatement as ulong,_ SQL3Reset as long End Function
Function SQL3ClearBindings(hStatement) CallDLL #sqlite3, "sqlite3_clear_bindings",_ hStatement as ulong,_ SQL3ClearBindings as long End Function
Function SQL3ErrStr$(errorCode) CallDLL #sqlite3, "sqlite3_errstr",_ errorCode as long,_ ret as ulong
SQL3ErrStr$ = winstring(ret) End Function
Function SQL3ColumnCount(hStatement) CallDLL #sqlite3, "sqlite3_column_count",_ hStatement as ulong,_ SQL3ColumnCount as long End Function
Function SQL3DataCount(hStatement) CallDLL #sqlite3, "sqlite3_data_count",_ hStatement as ulong,_ SQL3DataCount as long End Function
Function SQL3ColumnText$(hStatement, column) CallDLL #sqlite3, "sqlite3_column_text",_ hStatement as ulong,_ column as long,_ ret as ulong
SQL3ColumnText$ = winstring(ret) End Function
Function SQL3ColumnBytes(hStatement, column) CallDLL #sqlite3, "sqlite3_column_bytes",_ hStatement as ulong,_ column as long,_ SQL3ColumnBytes as long End Function
Function SQL3BindInt(hStatement, param, value) CallDLL #sqlite3, "sqlite3_bind_int",_ hStatement as ulong,_ param as long,_ value as long,_ SQL3BindInt as long End Function
Function SQL3BindText(hStatement, param, text$) textLen = len(text$)
CallDLL #sqlite3, "sqlite3_bind_text",_ hStatement as ulong,_ param as long,_ text$ as ptr,_ textLen as long,_ SQLITE.STATIC as long,_ SQL3BindText as long End Function
[SQL3PrepGlobals] Global SQLITE.OK : SQLITE.OK = 0 Global SQLITE.ERROR : SQLITE.ERROR = 1 Global SQLITE.BUSY : SQLITE.BUSY = 5 Global SQLITE.LOCKED : SQLITE.LOCKED = 6 Global SQLITE.MISMATCH : SQLITE.MISMATCH = 20 Global SQLITE.MISUSE : SQLITE.MISUSE = 21 Global SQLITE.ROW : SQLITE.ROW = 100 Global SQLITE.DONE : SQLITE.DONE = 101 Global SQLITE.INTEGER : SQLITE.INTEGER = 1 Global SQLITE.FLOAT : SQLITE.FLOAT = 2 Global SQLITE.BLOB : SQLITE.BLOB = 3 Global SQLITE.NULL : SQLITE.NULL = 4 Global SQLITE.TEXT : SQLITE.TEXT = 5 Global SQLITE.STATIC : SQLITE.STATIC = 0 Global SQLITE.TRANSIENT : SQLITE.TRANSIENT = -1 return
|
|
|
Post by ppro06 on Apr 15, 2020 4:45:36 GMT -5
Thank you all for your responses, let me also wrap my head around the idea of a wrapper. I will report back soon.
|
|
|
Post by colinmcm on Apr 15, 2020 4:54:07 GMT -5
Hi all, just to confirm, Metro is right that the article in newsletter 106 is specifically for sqlite 2, and is really therefore for historical interest only. The SQlite documentation for sqlite 3 used to state specifically that backward compatability with sqlite 2 code was broken.
Should we have a policy for old resources to indicate when they are no longer functional? Should we remove them or mark them in some way?
Colin McMurchie
|
|
|
Post by Rod on Apr 15, 2020 8:27:55 GMT -5
Jeez, everything on the forum is old including most of the users! I think "buyer beware". I once set about updating all the API code posted in the LBPE to use ulong for handles and stop using Boolean as a return type. Gave up at some point. Then we have the Way Back Machine and other caches all round the world.
They are at least pointers in the right direction and we are always here to help.
|
|
|
Post by colinmcm on Apr 15, 2020 10:29:10 GMT -5
Ah yes, the great Ulong hunt. I remember it well.
I suppose we all need to learn to mistrust any code that is more than a few years old.
Colin
|
|
|
Post by pierre on Apr 16, 2020 9:07:47 GMT -5
Thank you, Chris. Nice code ! 1) I added a function SQL3ColumName$: Function SQL3ColumnName$(hStatement, column) CallDLL #sqlite3, "sqlite3_column_name",_ hStatement as ulong,_ column as long,_ ret as ulong
SQL3ColumnName$ = winstring(ret) End Function Then, insert just before the "do ....loop" section : numCols = SQL3ColumnCount(hStatement) for i = 0 to numCols - 1 print SQL3ColumnName$(hStatement, i), next i print within the "do.......loop" section: the SQL3DataCount function actually retrieves the number of records (the SQLite documentation seems somewhat ambiguous about this), so perhaps for more clarity, it would be better to rename the numCols in this section to numRecs ? 2) Thanks also for the prepared statements with bound parameters ! These functions are missing in our "standard" dll for LB 4.5.1. As far as I can see, they don't seem to be provided neither by the sqliteconnect method in RB nor in LB5 alpha, and that is in my opinion a real problem. They should be deeply desired by people who work with web applications, for - as you stated it
yourself -they are the best way to prevent us from SQL injection. Are they perhaps on Carl's to do list ?
3) A couple of questions: - Will the future LB5 always have the ability to read Windows API's and third party dll's ? (the LB5 alpha for the moment does not ? ) - LB 4.5.1 crashes while attempting to read 'null' values from SQLite (LB error:'system primitive failed'), while LB5 alpha apparently does not... - Also, accentuated characters like the french é, è, à etc. input via LB 4.5.1, appear as BLOB is SQLite. LB5 alpha doesn't seem to have this problem. - Something to do perhaps with character sets, SQLite and LB5 alpha using both UTF8 and LB 4.5.1 perhaps not? - Is there a workaround for LB 4.5.1 ? Many thanks in advance, pierre
|
|
|
Post by Chris Iverson on Apr 16, 2020 10:55:50 GMT -5
within the "do.......loop" section: the SQL3DataCount function actually retrieves the number of records (the SQLite documentation seems somewhat ambiguous about this), so perhaps for more clarity, it would be better to rename the numCols in this section to numRecs ? You can use whatever you wish, I called it numCols because of both the documentation and various supporting third-party sources(including this: www.oreilly.com/library/view/using-sqlite/9781449394592/re262.html ) saying that sqlite3_data_count() does the same thing as sqlite3_column_count(), with one difference: if there is no data returned in the call, data_count() will return zero, whereas column_count() will return how many columns there would have been in the result if data HAD been returned. (e.g. if you do a SELECT a,b FROM test when you have zero records in test, column_count() will return 2, data_count() will return 0.) - Will the future LB5 always have the ability to read Windows API's and third party dll's ? (the LB5 alpha for the moment does not ? ) No, and I suspect this will not be a part of the first official release of LB5. It may come in the future, but as a modified command(CallDLL is a rather confusing name if you're running on Linux or Mac OS X), and would be used to interface with dynamic libraries in whatever OS you're running on. This is simply a result of the fact that various bits of LB4 stuff that's explicitly built on windows-only stuff(CallDLL, stylebits, etc) aren't going to be cross-compatible, for obvious reasons. - LB 4.5.1 crashes while attempting to read 'null' values from SQLite (LB error:'system primitive failed'), while LB5 alpha apparently does not... Hmm, I can see that happening. Do you have a bit of example code to demonstrate it? And do you know if it happens right away upon the CallDLL call to the sqlite3 function? Or if it happens afterward? If it happens immediately while executing the CallDLL command, I think that would explain why the LB wrapper exists right there. I'm not sure that could be worked around without external assistance. - Also, accentuated characters like the french é, è, à etc. input via LB 4.5.1, appear as BLOB is SQLite. LB5 alpha doesn't seem to have this problem. - Something to do perhaps with character sets, SQLite and LB5 alpha using both UTF8 and LB 4.5.1 perhaps not? - Is there a workaround for LB 4.5.1 ? The workaround is to convert to UTF8. This specifically is what I was considering when I mentioned my existing code would be spotty. I can come up with an intervening function to convert all strings passed in/out to/from UTF8, but it would unfortunately be a necessary step. There's a couple API calls that can be used to do this.
|
|
|
Post by meerkat on Apr 16, 2020 11:29:27 GMT -5
If you suspect that there may be "null" values, you may want to try the "IFNULL" or the "coalesce". This should solve the problem before LB gets the data..
HTH Dan
|
|
|
Post by Chris Iverson on Apr 16, 2020 11:45:22 GMT -5
Here's some sample code(and functions that can be used elsewhere) to convert strings from LB's default code page to UTF8, and back again.
I can rewrite my wrapper functions to do the conversion to UTF8 by default, but I'm a bit hesitant to do the conversion back by default. It wouldn't pose a problem if running on the same system(or one running with the same codepage), but if you moved the db to another system and tried it there, it's possible you'd get data loss due to not being able to properly convert the UTF8 database back into the local code page.
utf8$ = ACPToUTF8$("testé") print utf8$
print UTF8ToACP$(utf8$)
Function ACPToUTF8$(acp$) CP.ACP = 0 CP.UTF8 = 65001
ACPToUTF8$ = DoUTF8Convert$(acp$, CP.ACP, CP.UTF8) End Function
Function UTF8ToACP$(utf8$) CP.ACP = 0 CP.UTF8 = 65001
UTF8ToACP$ = DoUTF8Convert$(utf8$, CP.UTF8, CP.ACP) End Function
Function DoUTF8Convert$(in$, fromCP, toCP) CallDLL #kernel32, "MultiByteToWideChar",_ fromCP as long, 0 as long, in$ as ptr,_ -1 as long, _NULL as ulong, 0 as long,_ numChars as long
numChars = numChars * 2 utf16$ = space$(numChars)
CallDLL #kernel32, "MultiByteToWideChar",_ fromCP as long, 0 as long, in$ as ptr,_ -1 as long, utf16$ as ptr, numChars as long,_ ret as long
CallDLL #kernel32, "WideCharToMultiByte",_ toCP as long, 0 as long, utf16$ as ptr,_ -1 as long, _NULL as ulong, 0 as long,_ _NULL as long, _NULL as long, numBytes as long
DoUTF8Convert$ = space$(numBytes)
CallDLL #kernel32, "WideCharToMultiByte",_ toCP as long, 0 as long, utf16$ as ptr,_ -1 as long, DoUTF8Convert$ as ptr, numBytes as long,_ _NULL as long, _NULL as long, ret as long End Function
|
|
|
Post by pierre on Apr 16, 2020 16:41:22 GMT -5
Wow ! Tremendous ! Thank you both, Chris and meerkat, for all your help ! I have now enough material to study during the coming days (and nights).
pierre
PS just for your info : I tried the 'pragma table_info(test)' statement.
This was the expected output:
cid name type notnull dflt_value pk 0 a INT 0 NULL 0 1 b TEXT 0 NULL 0
And here is what happened, not immediately after the dll call, but in the course of the transaction:
the first line appeared normally : cid name type notnull dflt_value pk the second line stopped by dflt_value: 0 a INT 0 | Here is where LB 4.5.1 crashed, while attempting to read the first NULL value.
|
|