Preface
1. Final fix for fonts which have break character number 13 (like Seqoe UI). Now we can give full justification (left and right) using a left margin and a length (so this is like a right margin).
2. Expand DECLARE for using C and return values like LONG LONG and DOUBLE (8bytes). There is a new module SQLITE3, a demo for the use of SQLITE3, download from internet, unzip it, and use of c functions directly from M2000. We make a database with one table, we insert rows and we read the table and display it. Last we unload the library (sqlite3.dll).
This is what we do with this program (first time the program load the SQLITE3.DLL from internet from the official repository)
The program is self explained;
At the declarations the & means by reference, and the Long means the value converted to LONG (but this isn't the case when we use by reference, we have to use the specific type). Another way to use by reference is to use just Long anyname and before the call stage we make a Long thisVar and then we pass the Varptr(thisVar) (VarPtr works with array items also, so if A(3) is a long the VarPtr(A(3)) pass the address of A(3), the &A(3) not work for this type of call - modules use &A(3) making a hidden variable which actually passed by reference and at the return we get the value back to A(3) if A(3) exist - because we can reduce the array to zero items)
For downloading we use the URLDownloadToFileW() function from urlmon. The file is less than 2Mbyte, and we get with no wait. We download it in a tmp file (M2000 erase all tmp files which we get name using TEMPNAME$ - all of these files are in TEMP folder). Then we use an object of M2000 which has this name and link: https://github.com/M2000Interpreter/Environment/blob/main/ZipTool.cls
Just two methods, openZip and ExtractToPath and we have two files (the dll is the one we want).
Although DECLARE look for dll first to APPDIR$ we did something else: We place the dll to user directory (for M2000), So for every DECLARE we provide the exact path (see DIR$ + ).
All the jobs with SQLITE3 after opening the db file comes with the need if preparing the SQL query, compiling it, then binding data, then execute step by step, getting results when rows are available, then for each row we get each value from each column from row, and at the end we release the db file.
We have three strings with Sql statements. One for the creation of the table. No binding used. Another for inserting a row, and we have values to bind, then if we have another row we reset the compiled statement and again bind new data, and so on until no data exist on stack of values. Last string for statement is one to read the table, according the value of ORDER BY, here we sort on name. No bindings here, but we have many steps, the number of the returning rows.
I didn't use Blobs, so if you like find the missing functions from SQLITE documentation, as an exercise.
We bind strings using TRANSIENT (SQLITE3 take a copy of string). Also we read strings getting a copy from the provided pointer, and the provided length in bytes. Firs we ask about the length, we make a buffer, then we get the pointer and do the copy in the buffer and we return a string as a copy from buffer (buffer removed - is local) the string from function are not copied again, M2000 swap string pointers. So we have exactly two copies, one to buffer (which use CopyBytes internally). And then we get a BSTR string from buffer (as a copy), using Eval$(Buffer_pointer) which get all bytes and place them in a string without missing/altering a bit.
Add the reading of fields, and I use :memory: to make the table in memory (we can change it for a file name with path and extension db).
IF VERSION<13 THEN EXIT
IF VERSION=13 THEN IF REVISION<49 THEN EXIT
WINDOW 6, WINDOW
FORM 60
LOCALE 1033
DIR USER
IF NOT EXIST("sqlite3.dll") THEN
PRINT "FILE sqlite3.dll NOT FOUND - START DOWNLOAD FROM INTERNET"
CONST BINDF_GETNEWESTVERSION = 0x10&
STRING ZIPFILE="https://www.sqlite.org/2025/sqlite-dll-win-x86-3490200.zip"
DECLARE URLDownloadToFile LIB "urlmon.URLDownloadToFileW" {
LONG p, szUrl$, sxFname$, LONG dwRrvd, LONG cback
}
STRING tempZIP=TEMPNAME$ // this file erased automatic when we exit M2000.
IF URLDownloadToFile(0,ZIPFILE, tempZIP, BINDF_GETNEWESTVERSION, 0)=0 THEN
PRINT "ZIP FILE LOADED"
DECLARE zip COMPRESSOR
METHOD zip, "openZip", tempZIP
METHOD zip, "ExtractToPath", DIR$
PRINT "ZIP FILE EXTRACTED TO:";DIR$
END IF
REMOVE "urlmon" ' unload library
END IF
IF NOT EXIST("sqlite3.dll") THEN EXIT
CONST SQLITE_OK AS LONG = 0
CONST SQLITE_STATIC AS LONG = 0, SQLITE_TRANSIENT AS LONG =-1
CONST SQLITE_ROW AS LONG = 100, SQLITE_DONE AS LONG = 101
CONST RET_STATUS=3
ENUM Types {
dbl=1,
int,
int64,
txt
}
DECLARE sqlite3_libversion_number LIB c DIR$+"sqlite3.sqlite3_libversion_number" {} AS LONG
DECLARE sqlite3_libversion LIB c DIR$+"sqlite3.sqlite3_libversion" {} AS LONG
DECLARE sqlite3_open16 LIB c DIR$+"sqlite3.sqlite3_open16" {
Filename$, LONG &handler
} AS RET_STATUS
DECLARE sqlite3_close_v2 LIB c DIR$+"sqlite3.sqlite3_close_v2" { LONG handler}
DECLARE sqlite3_prepare16_v2 LIB c DIR$+"sqlite3.sqlite3_prepare16_v2" {
LONG handler, LONG bufferPtr, LONG Byte_size,
LONG &handlerStatement, LONG &pNotUsed
} AS RET_STATUS
DECLARE sqlite3_step LIB c DIR$+"sqlite3.sqlite3_step" {
LONG handlerStatement
} AS RET_STATUS
DECLARE sqlite3_reset LIB c DIR$+"sqlite3.sqlite3_reset" {
LONG handlerStatement
} AS RET_STATUS
DECLARE sqlite3_finalize LIB c DIR$+"sqlite3.sqlite3_finalize" {
LONG handlerStatement
} AS RET_STATUS
DECLARE sqlite3_bind_value LIB c DIR$+"sqlite3.sqlite3_bind_value" {
LONG handlerStatement, LONG Nth, LONG ValueHandler
} AS RET_STATUS
DECLARE sqlite3_value_double LIB c DIR$+"sqlite3.sqlite3_value_double" {
LONG ValueHandler
} AS DOUBLE
DECLARE sqlite3_column_double LIB c DIR$+"sqlite3.sqlite3_column_double" {
LONG handlerStatement, LONG Nth
} AS DOUBLE
DECLARE sqlite3_column_int64 LIB c DIR$+"sqlite3.sqlite3_column_int64" {
LONG handlerStatement, LONG Nth
} AS LONG LONG
DECLARE sqlite3_column_bytes16 LIB c DIR$+"sqlite3.sqlite3_column_bytes16" {
LONG handlerStatement, LONG Nth
} AS LONG
DECLARE sqlite3_column_text16 LIB c DIR$+"sqlite3.sqlite3_column_text16" {
LONG handlerStatement, LONG Nth
} AS LONG
DECLARE sqlite3_column_int LIB c DIR$+"sqlite3.sqlite3_column_int" {
LONG handlerStatement, LONG Nth
} AS LONG
DECLARE sqlite3_bind_double LIB c DIR$+"sqlite3.sqlite3_bind_double" {
LONG handlerStatement, LONG Nth, Dbl
} AS RET_STATUS
DECLARE sqlite3_bind_text16 LIB c DIR$+"sqlite3.sqlite3_bind_text16" {
LONG handlerStatement, LONG Nth, a$, LONG len_bytes, SQLITE_TRANSIENT
} AS RET_STATUS
DECLARE sqlite3_bind_text LIB c DIR$+"sqlite3.sqlite3_bind_text" {
LONG handlerStatement, LONG Nth, a$, LONG len_bytes, SQLITE_TRANSIENT
} AS RET_STATUS
DECLARE sqlite3_bind_int64 LIB c DIR$+"sqlite3.sqlite3_bind_int64" {
LONG handlerStatement, LONG Nth, int64
} AS RET_STATUS
DECLARE sqlite3_bind_int LIB c DIR$+"sqlite3.sqlite3_bind_int" {
LONG handlerStatement, LONG Nth, LONG int
} AS RET_STATUS
ret= sqlite3_libversion_number()
PRINT "SQLITE VERSION = ";(ret div 1000000)+"."+((ret mod 1000000) div 1000)+"."+(ret mod 1000)
rem {// press enter key just after rem to use these lines
BUFFER readthis AS BYTE*80
copyfromthere=sqlite3_libversion()
METHOD readthis, "FillDataFromMem", copyfromthere
PRINT "Version=";LEFTPART$(CHR$(EVALl$(readthis)), CHR$(0))
}
LONG db, where, compiled_statement, compiled_statement
create$={
CREATE TABLE COMPANY(
ID INT64 PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
}
insertRow$={
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (?1, ?2, ?3, ?4, ?5);
}
ReadFieldNamesAndTypes$={
PRAGMA TABLE_INFO(COMPANY)
}
ReadRows$={
SELECT * FROM COMPANY ORDER BY NAME
}
rem {ret=sqlite3_open16(DIR$+"check.db", &db)}
ret=sqlite3_open16(":memory:", &db)
BUFFER preparemem AS INTEGER*10000 ' for 10000 characters
compiled_statement=@process(db, preparemem, create$)
IF compiled_statement<>0 THEN
ret=sqlite3_step(compiled_statement)
IF ret=SQLITE_DONE THEN PRINT "TABLE CREATED"
ret=sqlite3_finalize(compiled_statement)
END IF
compiled_statement=@process(db, preparemem,insertRow$)
IF compiled_statement<>0 THEN
FLUSH
Data 1,"Paul", 32, "California", 20000.45
Data 2, "Allen", 25, "Texas", 15000.00
Data 3, "Teddy", 23, "Norway", 20000.00
Data 4, "Mark", 25, "Rich-Mond ", 65000.50
Data 5, "David", 27, "Texas", 85000.00
Data 6, "Kim", 22, "South-Hall", 45000.00
rowTypes=(int64, txt, int, txt, dbl)
WHILE @bind_stack(compiled_statement, rowTypes)
ret=sqlite3_step(compiled_statement)
IF ret=SQLITE_DONE ELSE CANCEL=TRUE: EXIT
IF EMPTY THEN EXIT
ret=sqlite3_reset(compiled_statement)
END WHILE
ret=sqlite3_finalize(compiled_statement)
END IF
IF VALID(CANCEL) THEN GOTO 100
PRINT "DATA INSERTED TO DATABASE AT TABLE COMPANY"
VARIANT var0, var1, var2, var3, var4
compiled_statement=@process(db, preparemem, ReadFieldNamesAndTypes$)
IF compiled_statement<>0 THEN
ret=SQLITE_ROW
rows_counter=0
FLUSH
WHILE ret=SQLITE_ROW
ret=sqlite3_step(compiled_statement)
IF ret=SQLITE_ROW THEN
rows_counter++
var0=@sqlite_column_string(compiled_statement, 0&)
var1=@sqlite_column_string(compiled_statement, 1&)
var2=@sqlite_column_string(compiled_statement, 2&)
' 1 not null 0 can be null
var3=@sqlite_column_string(compiled_statement, 3&)
? var0, var1, var2, var3
END IF
END WHILE
PRINT "FOUND NUMBER OF FIELDS = ";rows_counter
ret=sqlite3_finalize(compiled_statement)
END IF
compiled_statement=@process(db, preparemem, ReadRows$)
IF compiled_statement<>0 THEN
ret=SQLITE_ROW
rows_counter=0
WHILE ret=SQLITE_ROW
ret=sqlite3_step(compiled_statement)
IF ret=SQLITE_ROW THEN rows_counter++
END WHILE
PRINT "FOUND NUMBER OF ROWS = ";rows_counter
ret=sqlite3_reset(compiled_statement)
ret=SQLITE_ROW
// WORKS WITH VARIANTSOR WITH SPECIFIC TYPES
// THESE ARE THE VARIABLES FILLING FROM DATABASE
STRING headers="{0:8}|{1:10}|{2:3}|{3:20}|{4:10}"
STRING rowformat="{0:-8}|{1:10}|{2:-3}|{3:20}|{4:2:-10}"
STRING bar="{0:8}+{1:10}+{2:3}+{3:20}+{4:10}"
// FROM HERE WE RENDER THE TABLE COMPANY
PRINT FORMAT$(headers, "ID", "NAMES","AGE", "ADDRESS", "SALARY")
S=STRING$("-",WIDTH)
PRINT PART $(0), FORMAT$(bar, S, S, S, S, S )
PRINT
WHILE ret=SQLITE_ROW
ret=sqlite3_step(compiled_statement)
IF ret=SQLITE_ROW THEN
// THIS IS ZERO BASED INDEX FOR COLUMNS
var0=sqlite3_column_int64(compiled_statement, 0&)
var1=@sqlite_column_string(compiled_statement, 1&)
var2=sqlite3_column_int(compiled_statement, 2&)
var3=@sqlite_column_string(compiled_statement, 3&)
var4=sqlite3_column_double(compiled_statement, 4&)
PRINT FORMAT$(rowformat, var0, var1, var2, var3, var4)
END IF
END WHILE
PRINT PART $(0), FORMAT$(bar, S, S, S, S, S )
ret=sqlite3_finalize(compiled_statement)
END IF
100 ret=sqlite3_close_v2(db)
REMOVE "sqlite3" ' unload library
PRINT ret=SQLITE_OK
FUNCTION sqlite_column_string(stmt AS LONG, icol AS LONG)
LOCAL buffer_size=sqlite3_column_bytes16(stmt, icol)
LOCAL buf : BUFFER buf AS BYTE*buffer_size
METHOD buf, "FillDataFromMem", sqlite3_column_text16(stmt, icol)
=EVAL$(buf)
END FUNCTION
FUNCTION process(this_db, prep, a$)
prep[0]=a$
LOCAL LONG z=LEN(a$)*2, ret, compiled_statement
LOCAL LONG where=prep(0), where0, last=z+where
DO where0=where
z=last-where
ret=sqlite3_prepare16_v2(this_db, where0, z, &compiled_statement, &where)
UNTIL ret=0 or z=last-where
IF ret=0 THEN =compiled_statement
END FUNCTION
FUNCTION bind_stack(that_stmt, Rtypes)
IF STACK.SIZE<LEN(Rtypes) THEN FLUSH : =FALSE: EXIT FUNCTION
LOCAL int64val AS LONG LONG, dbl1 AS DOUBLE, int1 AS LONG
LOCAL m=EACH(Rtypes), ret AS LONG
// BINDING USE BASE ONE FOR INDEX
WHILE m
SELECT CASE array(m)
CASE txt
READ a$
ret+=sqlite3_bind_text16(that_stmt, m^+1, a$, LEN(a$)*2, SQLITE_TRANSIENT)
CASE dbl
READ dbl1
ret+=sqlite3_bind_double(that_stmt, m^+1, dbl1)
CASE int64
READ int64val
ret+=sqlite3_bind_int64(that_stmt, m^+1, int64val)
CASE int
READ int1
ret+=sqlite3_bind_int(that_stmt, m^+1, int1)
END SELECT
END WHILE
=ret=SQLITE_OK
END FUNCTION
