Post by metro on Mar 30, 2018 19:04:06 GMT -5
It's possible to create an in memory DB with SQLite
Super fast sorting and extraction of queried data
code isn't flash but you get the idea
Super fast sorting and extraction of queried data
code isn't flash but you get the idea
gosub [setStructs]
gosub [openDll]
hDB = openDB("trial.db") ' CHANGE TO DB you HAVE on DISK
hDBM=createDB(":memory:")
gosub [getTblInfo] ' routine to get info and works if you connect after using another DB command
print: print: print "HALT @ WAIT STATEMENT"
close #sq3 :close #sqm
WAIT
[getTblInfo]
SQL$ = "Select * From ASXList Limit 1" ' REPLACE TABLE NAME 2 YOURS
calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,hDB as long,RS as struct, ret as long
FOR n = 0 TO RS.Cols.struct -1
calldll #sq3, "SQ3_4_LB_GetFieldNameByIndex",n as long, ret as long
print Winstring(ret);" ";
NEXT n
' calldll #sq3, "SQ3_4_LB_ReleaseRecordset", result as void
'******** NEXT DB is MEMORY DB************************ 'changed hDBM & RSM as struct
SQL$ = "Select * From ASXmem Limit 1"
calldll #sqm , "SQ3_4_LB_GetRecordset", SQL$ as ptr,hDBM as long,RSM as struct, ret as long
calldll #sqm, "SQ3_4_LB_GetLastMessage", result as long
print Winstring(result);" ";" Message to display MEMORY FIELDS"
FOR n = 0 TO RSM.Cols.struct -1
calldll #sqm, "SQ3_4_LB_GetFieldNameByIndex",n as long, ret as long
print Winstring(ret);" ";
NEXT n
' calldll #sqm, "SQ3_4_LB_ReleaseRecordset", result as void
RETURN
[setStructs]
Struct RS,_
BOF as long,_ ' is True when CurrPos = 1
EOF as long,_ ' is True when CurrPos = Rows
Handle as long,_ ' address of recordset data returned by sqlite3.dll (dont't use)
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 (pointer to a string)
' -------------------------------
' structure for memory DB
' -------------------------------
Struct RSM,_
BOF as long,_ ' is True when CurrPos = 1
EOF as long,_ ' is True when CurrPos = Rows
Handle as long,_ ' address of recordset data returned by sqlite3.dll (dont't use)
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 (pointer to a string)
RETURN
[openDll]
' --------------------------
' DLL for #sq3
' --------------------------
Open "SQ3_4_LB.dll" for DLL As #sq3 ' open SQ3_4_LB.dll
calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long ' was sqlite3.dll loaded by SQ3_4_LB.dll
msg$ = Winstring(result) ' LastMessage would be "General Error - Couldn't open 'sqlite3.dll'"
if instr(msg$,"Error") then ' If we have an error
notice msg$ : close #sq3 ' close sqlite and end program
end
end if
'*************************************************
' --------------------------
' DLL for #sqm :memory:
' --------------------------
Open "SQ3_4_LB.dll" for DLL As #sqm ' open SQ3_4_LB.dll'calldll #sqm, "SQ3_4_LB_GetLastMessage", result as long ' was sqlite3.dll loaded by SQ3_4_LB.dll
msg$ = Winstring(result) ' LastMessage would be "General Error - Couldn't open 'sqlite3.dll'"
if instr(msg$,"Error") then ' If we have an error
notice msg$ : close #sqm ' close sqlite and end program
end
end if
RETURN
'************************************************
'
'END FUNCTION
FUNCTION openDB(DB$)
overwrite = 0
calldll #sq3, "SQ3_4_LB_OpenDB",DB$ as ptr, DBhnd as long
calldll #sqm, "SQ3_4_LB_GetLastMessage", result as long
print: print Winstring(result);" ";"disk DB opened"
If DBhnd = 0 then
calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long
print Winstring(result) ': close #sq3 : end
end if
openDB = DBhnd
END FUNCTION
FUNCTION createDB(DBM$) ' should be broken down to smaller functions (steps)
' DBM$ = ':memory:'
calldll #sqm, "SQ3_4_LB_CreateDB",DBM$ as ptr, overwrite as long, hDBM as ulong
calldll #sqm, "SQ3_4_LB_GetLastMessage", result as long 'message for testing
print: print Winstring(result);" " ;"after MEMORY DB CREATED"
If hDBM = 0 then 'message on error
calldll #sqm, "SQ3_4_LB_GetLastMessage", result as long
print Winstring(result)
end if
SQL$ ="Create Table ASXmem (BusDate,TheMarket,ASXCode,Underlying,OptType,ExpDate,Strike,Style,ContractSize,DerivativeProduct,ProductType,Category)"
calldll #sqm, "SQ3_4_LB_Execute",SQL$ as ptr, hDBM as ulong,result as long
calldll #sqm, "SQ3_4_LB_GetLastMessage", result as long
print: print Winstring(result);" ";"after MEM Table Create"
IF result = 0 THEN
calldll #sqm, "SQ3_4_LB_GetLastMessage", result as long
print Winstring(result);" "; "Table Does not exist"
END IF
SQL$="INSERT InTO ASXmem (BusDate,TheMarket,ASXCode,Underlying,OptType,ExpDate,Strike,Style,ContractSize,DerivativeProduct,ProductType,Category) Values ('02/12/2015','ASX','AGLT87','AGL','C','17/12/2015',10.56,'A',104.00,'AGL','OS','')"
calldll #sqm, "SQ3_4_LB_Execute",SQL$ as ptr, hDBM as ulong,result as long
calldll #sqm, "SQ3_4_LB_GetLastMessage", result as long
print Winstring(result); " "; "after MEM Table Insert"
print: print "Completed memory DB setup":print
createDB=hDBM
END FUNCTION