SQLite3 SB ext. module

[Support]

I have created a new board on the forum to make room for the new SQLite3 multi-platform extension module for ScriptBasic. Armando (AIR) has released a proof of concept as a sneak preview of what's to come.

This is the .00000001 version, there are a lot more functions in the SQLite3 package.  Note that the actual SQLite3 package is statically linked into the library, thus the somewhat larger library size but the net result is that it doesn't require that SQLite3 be installed, which is a plus. All other libraries it needs are dynamically linked.

Code: [Select]

INCLUDE sqlite.bas

hdb=sqlite::open("testsql")
sqlite::execute(hdb,"create table demo (someval integer, sometxt text);")
sqlite::execute(hdb, "INSERT INTO demo VALUES (123,'hello');")
sqlite::execute(hdb, "INSERT INTO demo VALUES (234, 'cruel');")
sqlite::execute(hdb, "INSERT INTO demo VALUES (345, 'world');")

stmt = sqlite::query(hdb,"SELECT * FROM demo;")

while (sqlite::row(stmt) = 100)
    pr = pr & sqlite::row_value(stmt,0) & " - " & sqlite::row_value(stmt,1) & "\n"
wend

sqlite::close(hdb)

print pr

print "SQLite Version: ",sqlite::version(),"\n"

jrs@ip-10-166-185-35:~/tmp$ scriba sqltest.sb
123 - hello
234 - cruel
345 - world
SQLite Version: 3.7.12.1
jrs@ip-10-166-185-35:~/tmp$

[kryton9]

Can't be any simpler than that. Great job!

[Support]

Armando (AIR) released a beta version of his SQLite3 extension module for ScriptBasic. This release follows the standard SB high level function calls that are used in the MySQL, ODBC and PostgreSQL extensions modules. In most cases, just changing open/connect statement is all that is needed to move between DB extension module libraries. You don't need to install any SQLite3 libraries or dependencies to use this extension module. Everything is included in the extension module shared object.

t_sqlite3.sb

Code: [Select]

import sqlite.bas

db = sqlite::open("testsql")

sqlite::execute(db,"CREATE TABLE demo (someval integer, sometxt text);")
sqlite::execute(db,"INSERT INTO demo VALUES (123,'hello');")
sqlite::execute(db, "INSERT INTO demo VALUES (234, 'cruel');")
sqlite::execute(db, "INSERT INTO demo VALUES (345, 'world');")

stmt = sqlite::query(db,"SELECT * FROM demo")

while (sqlite::row(stmt) = sqlite::SQLITE3_ROW)
  if sqlite::fetchhash(stmt,column) then
    print column{"someval"},"\t-\t",column{"sometxt"},"\n"
  end if
wend

sqlite::close(db)

jrs@ip-10-166-185-35:~/test$ scriba t_sqlite3.sb
123   -   hello
234   -   cruel
345   -   world
jrs@ip-10-166-185-35:~/test$

sqlite.bas

Code: [Select]

' """
FILE: sqlite.bas

This is the BASIC import file for the module sqlite.

This file was generated by headerer.pl from the file interface.c
Do not edit this file, rather edit the file interface.c and use
headerer.pl to regenerate this file.
"""

module sqlite

SQLITE3_OK          =   0
SQLITE3_ERROR       =   1
SQLITE3_INTERNAL    =   2
SQLITE3_PERM        =   3
SQLITE3_ABORT       =   4
SQLITE3_BUSY        =   5
SQLITE3_LOCKED      =   6
SQLITE3_NOMEM       =   7
SQLITE3_READONLY    =   8
SQLITE3_INTERRUPT   =   9
SQLITE3_IOERR       =  10
SQLITE3_CORRUPT     =  11
SQLITE3_NOTFOUND    =  12  
SQLITE3_FULL        =  13
SQLITE3_CANTOPEN    =  14  
SQLITE3_PROTOCOL    =  15  
SQLITE3_EMPTY       =  16  
SQLITE3_SCHEMA      =  17
SQLITE3_TOOBIG      =  18
SQLITE3_CONStraint  =  19
SQLITE3_MISMATCH    =  20
SQLITE3_MISUSE      =  21
SQLITE3_NOLFS       =  22
SQLITE3_AUTH        =  23
SQLITE3_ROW         = 100
SQLITE3_DONE        = 101

SQLITE3_STATIC      =   0
SQLITE_TRANSIENT    =  -1

SQLITE_INTEGER      =   1
SQLITE_FLOAT        =   2
SQLITE_TEXT         =   3
SQLITE_BLOB         =   4
SQLITE_NULL         =   5
      
' FUNCTION DECLARATIONS
declare sub     ::OPEN         alias "sql3_open"         lib "sqlite"
declare sub     ::CLOSE        alias "sql3_close"        lib "sqlite"
declare sub     ::EXECUTE      alias "sql3_execute"      lib "sqlite"
declare sub     ::QUERY        alias "sql3_query"        lib "sqlite"
declare sub     ::ROW          alias "sql3_row"          lib "sqlite"
declare sub     ::ROW_VALUE    alias "sql3_row_value"    lib "sqlite"
declare sub     ::COLUMN_COUNT alias "sql3_column_count" lib "sqlite"
declare sub     ::COLUMN_NAME  alias "sql3_column_name"  lib "sqlite"
declare sub     ::FINALIZE     alias "sql3_finalize"     lib "sqlite"
declare sub     ::VERSION      alias "sql3_version"      lib "sqlite"
declare sub     ::ErrorCode    alias "sql3_errorcode"    lib "sqlite"
declare sub     ::ErrorMsg     alias "sql3_errormsg"     lib "sqlite"
declare sub     ::FETCHHASH    alias "sql3_fetchhash"    lib "sqlite"
declare sub     ::FETCHARRAY   alias "sql3_fetcharray"   lib "sqlite"

end module

interface.c

Code: [Select]

/*
  FILE   : interface.c
  HEADER : interface.h
  BAS    : sqlite.bas
  AUTHOR : Armando I. Rivera

  DATE:

  CONTENT:
  This is the interface.c file for the ScriptBasic module sqlite3

NTLIBS:
UXLIBS: -lc -ldl -lpthread
DWLIBS: -lsqlite3 -lpthread
ADLIBS:
*/

/*
TO_BAS:
SQLITE3_OK          =   0
SQLITE3_ERROR       =   1
SQLITE3_INTERNAL    =   2
SQLITE3_PERM        =   3
SQLITE3_ABORT       =   4
SQLITE3_BUSY        =   5
SQLITE3_LOCKED      =   6
SQLITE3_NOMEM       =   7
SQLITE3_READONLY    =   8
SQLITE3_INTERRUPT   =   9
SQLITE3_IOERR       =  10
SQLITE3_CORRUPT     =  11
SQLITE3_NOTFOUND    =  12  
SQLITE3_FULL        =  13
SQLITE3_CANTOPEN    =  14  
SQLITE3_PROTOCOL    =  15  
SQLITE3_EMPTY       =  16  
SQLITE3_SCHEMA      =  17
SQLITE3_TOOBIG      =  18
SQLITE3_CONStraint  =  19
SQLITE3_MISMATCH    =  20
SQLITE3_MISUSE      =  21
SQLITE3_NOLFS       =  22
SQLITE3_AUTH        =  23
SQLITE3_ROW         = 100
SQLITE3_DONE        = 101

SQLITE3_STATIC      =   0
SQLITE_TRANSIENT    =  -1

SQLITE_INTEGER      =   1
SQLITE_FLOAT        =   2
SQLITE_TEXT         =   3
SQLITE_BLOB         =   4
SQLITE_NULL         =   5
      
*/

#include <stdio.h>
#include <string.h>

#include "../../basext.h"
#include "sqlite3.h"

besVERSION_NEGOTIATE
  return (int)INTERFACE_VERSION;
besEND

besSUB_START
  long *p;

  besMODULEPOINTER = besALLOC(sizeof(long));
  if( besMODULEPOINTER == NULL )return 0;

  p = (long*)besMODULEPOINTER;
  return 0;
besEND

besSUB_FINISH
  long *p;

  p = (long*)besMODULEPOINTER;
  if( p == NULL )return 0;
  return 0;
besEND

/**
=section OPEN
=H Open/Create an sqlite3 database file
*/

besFUNCTION(sql3_open)
     sqlite3 *db;
     const char *fileName;
     int i;

     besARGUMENTS("s")
          &fileName
     besARGEND

     i = sqlite3_open(fileName, &db);
     besRETURN_POINTER(db)
besEND

/**
=section CLOSE
=H Close an sqlite3 database file
*/
besFUNCTION(sql3_close)
     sqlite3 *db;
     int i;

     besARGUMENTS("p")
          &db
     besARGEND

     i = sqlite3_close(db);
     besRETURN_LONG(i)
besEND

/**
=section EXECUTE
=H Execute an SQL statement/command
*/
besFUNCTION(sql3_execute)
    sqlite3 *db;
    char *sqlcmd;
    int ret;

    besARGUMENTS("ps")
        &db,&sqlcmd
    besARGEND
    ret = sqlite3_exec(db,sqlcmd,NULL,NULL,NULL);
    besRETURN_LONG(ret)
besEND

/**
=section QUERY
=H Pass a Query to an sqlite3 database file
*/
besFUNCTION(sql3_query)
    sqlite3 *db;
    sqlite3_stmt *stmt;
    char *sqlcmd;
    int ret;

    besARGUMENTS("ps")
        &db,&sqlcmd
    besARGEND
    ret = sqlite3_prepare_v2(db,sqlcmd,strlen(sqlcmd)+1,&stmt,NULL);
    besRETURN_POINTER(stmt)
besEND

/**
=section ROW
=H Retrieve the next Row from a database file
*/
besFUNCTION(sql3_row)
     sqlite3_stmt *stmt;
     int i;

     besARGUMENTS("p")
          &stmt
     besARGEND

     i = sqlite3_step(stmt);
     besRETURN_LONG(i)

besEND

/**
=section ROW_VALUE
=H Retrieve the value at position y in database row
*/
besFUNCTION(sql3_row_value)
     sqlite3_stmt *stmt;
     const char* cur_column_text;
     int i;

     besARGUMENTS("pi")
          &stmt,&i
     besARGEND

     cur_column_text = sqlite3_column_text(stmt,i);
     besRETURN_STRING(cur_column_text)

besEND

/**
=section COLUMN_COUNT
=H Retrieve number of columns in table
*/
besFUNCTION(sql3_column_count)
     sqlite3_stmt *stmt;
     int i;

     besARGUMENTS("p")
          &stmt
     besARGEND

     i = sqlite3_column_count(stmt);
     besRETURN_LONG(i)

besEND

/**
=section COLUMN_NAME
=H Retrieve column name at pos i in table
*/
besFUNCTION(sql3_column_name)
     sqlite3_stmt *stmt;
     const char* cur_column_name;
     int i;

     besARGUMENTS("pi")
          &stmt,&i
     besARGEND

     cur_column_name = sqlite3_column_name(stmt,i);
     besRETURN_STRING(cur_column_name)

besEND

/**
=section FINALIZE
=H Finalize an sqlite3 database file
*/
besFUNCTION(sql3_finalize)
     sqlite3_stmt *stmt;
     int i;

     besARGUMENTS("p")
          &stmt
     besARGEND

     i = sqlite3_finalize(stmt);
     besRETURN_LONG(i)
besEND

/**
=section VERSION
=H Retrieve sqlite3 version string
*/
besFUNCTION(sql3_version)
    const char *ver = sqlite3_libversion();
    besRETURN_STRING(ver)
besEND

/**
=section ErrorCode
=H Returns status code for sqlite function call
*/
besFUNCTION(sql3_errorcode)
     sqlite3 *db;
     int i;

     besARGUMENTS("p")
          &db
     besARGEND

     i = sqlite3_errcode(db);
     besRETURN_LONG(i)
besEND

/**
=section ErrorMsg
=H Returns text that describes the error
*/
besFUNCTION(sql3_errormsg)
     sqlite3 *db;
     const char *errString;

     besARGUMENTS("p")
          &db
     besARGEND

     errString = sqlite3_errmsg(db);
     besRETURN_STRING(errString)
besEND

/**
=section FETCHHASH
=H Returns Hash containing row contents in Key/Value pair
*/
besFUNCTION(sql3_fetchhash)
    VARIABLE Argument;
    unsigned long __refcount_;
    sqlite3_stmt *stmt;
    LEFTVALUE Lval;
    unsigned int numfields;
    int i;
    const char* cur_column_name;
    const char* cur_column_text;
    
     besARGUMENTS("p")
          &stmt
     besARGEND
    
     Argument = besARGUMENT(2);

     besLEFTVALUE(Argument,Lval);
     if( ! Lval )return 0x00081001;
    
     besRELEASE(*Lval);
     *Lval = NULL;
    
    
     numfields = sqlite3_column_count(stmt);
     if( numfields == 0 ){
        besRETURNVALUE = NULL;
        return COMMAND_ERROR_SUCCESS;
     }
    
     *Lval = besNEWARRAY(0,2*numfields-1);
     if( *Lval == NULL )return COMMAND_ERROR_MEMORY_LOW;
    
     for( i= 0 ; ((unsigned)i) < numfields ; i++ ) {
        cur_column_name = sqlite3_column_name(stmt,i);
        cur_column_text = sqlite3_column_text(stmt,i);
        ARRAYVALUE(*Lval,2*i) = besNEWSTRING(strlen(cur_column_name));
        if( ARRAYVALUE(*Lval,2*i) == NULL )return COMMAND_ERROR_MEMORY_LOW;
        memcpy(STRINGVALUE(ARRAYVALUE(*Lval,2*i)),cur_column_name, strlen(cur_column_name));
        
        ARRAYVALUE(*Lval,2*i+1) = besNEWSTRING(strlen(cur_column_text));
        if( ARRAYVALUE(*Lval,2*i+1) == NULL )return COMMAND_ERROR_MEMORY_LOW;
        memcpy(STRINGVALUE(ARRAYVALUE(*Lval,2*i+1)),cur_column_text,strlen(cur_column_text));
     }
    
     besALLOC_RETURN_LONG;
     LONGVALUE(besRETURNVALUE) = -1;
besEND

/**
=section FETCHARRAY
=H Returns Array containing row contents
*/
besFUNCTION(sql3_fetcharray)
    VARIABLE Argument;
    unsigned long __refcount_;
    sqlite3_stmt *stmt;
    LEFTVALUE Lval;
    unsigned int numfields;
    int i;
    const char* cur_column_text;
    
     besARGUMENTS("p")
          &stmt
     besARGEND

     Argument = besARGUMENT(2);

     besLEFTVALUE(Argument,Lval);
     if( ! Lval )return 0x00081001;
    
     besRELEASE(*Lval);
     *Lval = NULL;

     numfields = sqlite3_column_count(stmt);
     if( numfields == 0 ){
        besRETURNVALUE = NULL;
        return COMMAND_ERROR_SUCCESS;
     }
    
     *Lval = besNEWARRAY(0,numfields-1);
     if( *Lval == NULL )return COMMAND_ERROR_MEMORY_LOW;
    
     for( i= 0 ; ((unsigned)i) < numfields ; i++ ) {
        cur_column_text = sqlite3_column_text(stmt,i);
        
        ARRAYVALUE(*Lval,i) = besNEWSTRING(strlen(cur_column_text));
        if( ARRAYVALUE(*Lval,i) == NULL )return COMMAND_ERROR_MEMORY_LOW;
        memcpy(STRINGVALUE(ARRAYVALUE(*Lval,i)),cur_column_text,strlen(cur_column_text));
     }
    
     besALLOC_RETURN_LONG;
     LONGVALUE(besRETURNVALUE) = -1;
besEND

START_FUNCTION_TABLE(SQLITE_SLFST)
// Ext. module
  EXPORT_MODULE_FUNCTION(versmodu)
  EXPORT_MODULE_FUNCTION(bootmodu)
  EXPORT_MODULE_FUNCTION(finimodu)

// MOUDLE FUNCTIONS
  EXPORT_MODULE_FUNCTION(sql3_open)
  EXPORT_MODULE_FUNCTION(sql3_close)
  EXPORT_MODULE_FUNCTION(sql3_execute)
  EXPORT_MODULE_FUNCTION(sql3_query)
  EXPORT_MODULE_FUNCTION(sql3_row)
  EXPORT_MODULE_FUNCTION(sql3_row_value)
  EXPORT_MODULE_FUNCTION(sql3_column_count)
  EXPORT_MODULE_FUNCTION(sql3_column_name)
  EXPORT_MODULE_FUNCTION(sql3_version)
  EXPORT_MODULE_FUNCTION(sql3_finalize)
  EXPORT_MODULE_FUNCTION(sql3_version)
  EXPORT_MODULE_FUNCTION(sql3_errorcode)
  EXPORT_MODULE_FUNCTION(sql3_errormsg)
  EXPORT_MODULE_FUNCTION(sql3_fetchhash)
  EXPORT_MODULE_FUNCTION(sql3_fetcharray)
END_FUNCTION_TABLE

I would like to thank Armando for all his contributions to the ScriptBasic open source project.

[Support]

Attached is Armando's SQLite3 ScriptBasic extension module for Android Linux. SQLite3 is statically linked into the SB ext. module so there is no external SQLite3 dependencies required. (my SGT2 10.1 doesn't seem to have a /system/bin/sqlite3 command line utility and I haven't found where there may be a share object version of SQLite3 installed) This Android Developer SQLite3 reference doesn't hold true on my SGT2_10.1 at least.

[Support]

While testing the new SQLite3 extension module, I discovered a problem with the FetchHash() and FetchArray() functions. Armando sent me a fix which seems to work based on my limited testing. The following is an example of reading the raw_contacts table from the Android system DB.

Code: [Select]

import sqlite.bas

db = sqlite::open("contacts2.db")

stmt = sqlite::query(db,"select * from raw_contacts")

while (sqlite::row(stmt) = sqlite::SQLITE3_ROW)
  if sqlite::fetchhash(stmt,column) then
    for x = 0 to ubound(column) step 2
      PRINT column[x]," - ",column[x+1],"\n"
    next x
  end if
wend

sqlite::close(db)

jrs@laptop:~/sbdev$ scriba t6_sqlite.sb
_id - 1
account_name - 1
account_type - 1
data_set - 1
sourceid - 1
raw_contact_is_read_only - 0
version - 2
dirty - 1
deleted - 0
contact_id - 1
aggregation_mode - 0
aggregation_needed - 0
custom_ringtone - 0
send_to_voicemail - 0
times_contacted - 0
last_time_contacted - 0
starred - 0
display_name - John Spikowski
display_name_alt - Spikowski, John
display_name_source - 40
phonetic_name - 40
phonetic_name_style - 3
sort_key - John Spikowski
sort_key_alt - Spikowski, John
name_verified - 0
sync1 - 0
sync2 - 0
sync3 - 0
sync4 - 0
jrs@laptop:~/sbdev$

[Script_test]

Hello!
First of all, thank you for your work.
I have Strange random problem running under windows 7 and windows 10.
When the file is created, it adds additional ascii characters to the file name.
thanks for your help.

update
This happens when you use ...

Code: [Select]

directo="c:\\scriptbasic\\database\\"
hdb=sqlite::open(directo & "registro_fechas.db")

[AlyssonR]

Try this:

Code: [Select]

directo="c:\\scriptbasic\\database\\"
fileno="registro_fechas.db"
hdb=sqlite::open(directo & fileno)

or even

Code: [Select]

directo="c:\\scriptbasic\\database\\"
fileno="registro_fechas.db"
ofile = directo & fileno
hdb=sqlite::open(ofile)

Sometimes, it pays to keep arguments free of literal strings.

[Script_test]

 
Thank you!!