Revision 49 - Version 13 - Late bound SQLITE3 - EXAMPLE

 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