1. sqlite interface ?

hi,

i took "Ray Smith - email: smithr@ix.net.au" interface and extended it a lot to the latest sqlite, - well almost nothing other then some comments are left. it can all of the original release, metatdata and phix sequences in blobs and char fields. works with 64 and 32 bit and is easy to adept to openeuphoria.

must download "sqlite-dll-win32-x86-3240000.zip" and/or "sqlite-dll-win32-x64-3240000.zip" from the sqlite webpage.

if there is a need for anyone i'll post the code.

richard

p.s. hope the new phix version is comming (some previous post) of pete.

new topic     » topic index » view message » categorize

2. Re: sqlite interface ?

Hi

I would like to take a look at that - could I get a download link?

Cheers

Chris

new topic     » goto parent     » topic index » view message » categorize

3. Re: sqlite interface ?

unfortunately i have no possibility to do that - and don't how to do that. so i post it here and when you copied it, please tell me, i delete it.

namespace SQLITE 
 
include builtins\serialize.e 
include builtins\timedate.e  
 
 
public type DbHandle(atom x) 
  return x>0 or x=-1 
end type 
 
public type DbStatement(atom x) 
  return x>0 or x=-1 
end type 
 
 
public constant 
 SQLITE_OK           = 0,   -- Successful result 
 SQLITE_ERROR        = 1,   -- SQL error or missing database 
 SQLITE_INTERNAL     = 2,   -- An internal logic error in SQLite 
 SQLITE_PERM         = 3,   -- Access permission denied 
 SQLITE_ABORT        = 4,   -- Callback routine requested an abort 
 SQLITE_BUSY         = 5,   -- The database file is locked 
 SQLITE_LOCKED       = 6,   -- A table in the database is locked 
 SQLITE_NOMEM        = 7,   -- A malloc() failed 
 SQLITE_READONLY     = 8,   -- Attempt to write a readonly database 
 SQLITE_INTERRUPT    = 9,   -- Operation terminated by sqlite_interrupt() 
 SQLITE_IOERR        = 10,  -- Some kind of disk I/O error occurred 
 SQLITE_CORRUPT      = 11,  -- The database disk image is malformed 
 SQLITE_NOTFOUND     = 12,  -- (Internal Only) Table or record not found 
 SQLITE_FULL         = 13,  -- Insertion failed because database is full 
 SQLITE_CANTOPEN     = 14,  -- Unable to open the database file 
 SQLITE_PROTOCOL     = 15,  -- Database lock protocol error 
 SQLITE_EMPTY        = 16,  -- (Internal Only) Database table is empty 
 SQLITE_SCHEMA       = 17,  -- The database schema changed 
 SQLITE_TOOBIG       = 18,  -- Too much data for one row of a table 
 SQLITE_CONSTRAINT   = 19,  -- Abort due to constraint violation 
 SQLITE_MISMATCH     = 20,  -- Data type mismatch 
 SQLITE_MISUSE       = 21,  -- Library used incorrectly 
 SQLITE_NOLFS        = 22,  -- Uses OS features not supported on host 
 SQLITE_AUTH         = 23,  -- Authorization denied 
 SQLITE_FORMAT       = 24,  -- Not used 
 SQLITE_RANGE        = 25,  -- 2nd parameter to sqlite3_bind out of range 
 SQLITE_NOTADB       = 26,  -- File opened that is not a database file 
 SQLITE_NOTICE       = 27,  -- Notifications from sqlite3_log() 
 SQLITE_WARNING      = 28,  -- Warnings from sqlite3_log() 
 SQLITE_ROW          = 100, -- SQStep() has another row ready 
 SQLITE_DONE         = 101, -- SQStep() has finished executing 
 SQLITE_STATIC       = 0, 
 SQLITE_TRANSIENT    = -1, 
 FTS5_TOKENIZE_QUERY    = #0001, 
 FTS5_TOKENIZE_PREFIX   = #0002, 
 FTS5_TOKENIZE_DOCUMENT = #0004, 
 FTS5_TOKENIZE_AUX      = #0008, 
 FTS5_TOKEN_COLOCATED   = #0001, 
SQLITE_TRACE_STMT       = #01, 
SQLITE_TRACE_PROFILE    = #02, 
SQLITE_TRACE_ROW        = #04, 
SQLITE_TRACE_CLOSE      = #08, 
SQLITE_CREATE_INDEX         =  1,  -- Index Name      Table Name 
SQLITE_CREATE_TABLE         =  2,  -- Table Name      NULL 
SQLITE_CREATE_TEMP_INDEX    =  3,  -- Index Name      Table Name 
SQLITE_CREATE_TEMP_TABLE    =  4,  -- Table Name      NULL 
SQLITE_CREATE_TEMP_TRIGGER  =  5,  -- Trigger Name    Table Name 
SQLITE_CREATE_TEMP_VIEW     =  6,  -- View Name       NULL 
SQLITE_CREATE_TRIGGER       =  7,  -- Trigger Name    Table Name 
SQLITE_CREATE_VIEW          =  8,  -- View Name       NULL 
SQLITE_DELETE               =  9,  -- Table Name      NULL 
SQLITE_DROP_INDEX           = 10,  -- Index Name      Table Name 
SQLITE_DROP_TABLE           = 11,  -- Table Name      NULL 
SQLITE_DROP_TEMP_INDEX      = 12,  -- Index Name      Table Name 
SQLITE_DROP_TEMP_TABLE      = 13,  -- Table Name      NULL 
SQLITE_DROP_TEMP_TRIGGER    = 14,  -- Trigger Name    Table Name 
SQLITE_DROP_TEMP_VIEW       = 15,  -- View Name       NULL 
SQLITE_DROP_TRIGGER         = 16,  -- Trigger Name    Table Name 
SQLITE_DROP_VIEW            = 17,  -- View Name       NULL 
SQLITE_INSERT               = 18,  -- Table Name      NULL 
SQLITE_PRAGMA               = 19,  -- Pragma Name     1st arg or NULL 
SQLITE_READ                 = 20,  -- Table Name      Column Name 
SQLITE_SELECT               = 21,  -- NULL            NULL 
SQLITE_TRANSACTION          = 22,  -- Operation       NULL 
SQLITE_UPDATE               = 23,  -- Table Name      Column Name 
SQLITE_ATTACH               = 24,  -- Filename        NULL 
SQLITE_DETACH               = 25,  -- Database Name   NULL 
SQLITE_ALTER_TABLE          = 26,  -- Database Name   Table Name 
SQLITE_REINDEX              = 27,  -- Index Name      NULL 
SQLITE_ANALYZE              = 28,  -- Table Name      NULL 
SQLITE_CREATE_VTABLE        = 29,  -- Table Name      Module Name 
SQLITE_DROP_VTABLE          = 30,  -- Table Name      Module Name 
SQLITE_FUNCTION             = 31,  -- NULL            Function Name 
SQLITE_SAVEPOINT            = 32,  -- Operation       Savepoint Name 
SQLITE_COPY                 =  0,  -- No longer used */ 
SQLITE_RECURSIVE            = 33,  -- NULL            NULL 
SQLITE_DENY                 = 1,   -- Abort the SQL statement with an error 
SQLITE_OPEN_READONLY        = #00000001, -- Ok for sqlite3_open_v2() 
SQLITE_OPEN_READWRITE       = #00000002, -- Ok for sqlite3_open_v2() 
SQLITE_OPEN_CREATE          = #00000004,  -- Ok for sqlite3_open_v2() 
SQLITE_INTEGER              = 1, 
SQLITE_FLOAT                = 2, 
SQLITE_TEXT                 = 3, 
SQLITE_BLOB                 = 4, 
SQLITE_NULL                 = 5, 
SQLITE3_TEXT                = 3 
 
 
 
atom SQLiteDll = 0 
ifdef BITS32 then 
  SQLiteDll = open_dll("sqlite32.dll") 
elsedef 
  SQLiteDll = open_dll("sqlite64.dll") 
end ifdef 
 
if SQLiteDll = 0 then 
  puts(1, "Install sqlite3.dll, from www.sqlite.org, amalgamated for 32 and 64 bit") 
  abort(0) 
end if 
 
 
 
public constant SQLITE_VERSION  =   "3.24.0" 
public constant SQLITE_VERSION_NUMBER = 3024000 
public constant SQLITE_SOURCE_ID = "2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca" 
 
 
 
constant _sqlite3_open =define_c_func(SQLiteDll, "sqlite3_open",{C_POINTER, C_POINTER}, C_HANDLE) 
constant _sqlite3_open16 =define_c_func(SQLiteDll, "sqlite3_open16",{C_POINTER, C_POINTER}, C_HANDLE) 
constant _sqlite3_open_v2=define_c_func(SQLiteDll, "sqlite3_open_v2",{C_POINTER,C_POINTER,C_INT,C_POINTER},C_INT) 
constant _sqlite3_close=define_c_func(SQLiteDll, "sqlite3_close", {C_HANDLE}, C_INT) 
constant _sqlite3_errmsg =define_c_func(SQLiteDll, "sqlite3_errmsg",{C_HANDLE}, C_POINTER) 
constant _sqlite3_errmsg16 =define_c_func(SQLiteDll, "sqlite3_errmsg16",{C_HANDLE}, C_POINTER) 
constant _sqlite3_free_table=define_c_proc(SQLiteDll, "sqlite3_free_table", {C_POINTER}) 
constant _sqlite3_free =define_c_proc(SQLiteDll, "sqlite3_free",{C_HANDLE}) 
constant _sqlite3_exec = define_c_func(SQLiteDll, "sqlite3_exec",{C_HANDLE,C_POINTER,C_HANDLE,C_HANDLE,C_POINTER}, C_INT) 
constant _sqlite3_changes=define_c_func(SQLiteDll, "sqlite3_changes", {C_HANDLE}, C_INT) 
constant _sqlite3_get_table =define_c_func(SQLiteDll, "sqlite3_get_table",{C_HANDLE, C_POINTER, C_POINTER, C_POINTER, C_POINTER, C_POINTER}, C_INT) 
constant _sqlite3_libversion_number = define_c_func(SQLiteDll, "sqlite3_libversion_number",{}, C_HANDLE) 
constant _sqlite3_busy_timeout =define_c_func(SQLiteDll, "sqlite3_busy_timeout",{C_HANDLE}, C_INT) 
constant _sqlite3_prepare =define_c_func(SQLiteDll, "sqlite3_prepare", {C_HANDLE,C_POINTER,C_INT,C_HANDLE,C_POINTER}, C_INT) 
constant _sqlite3_step =define_c_func(SQLiteDll, "sqlite3_step",{C_HANDLE}, C_INT) 
constant _sqlite3_reset=define_c_func(SQLiteDll, "sqlite3_reset", {C_HANDLE}, C_INT) 
constant _sqlite3_finalize=define_c_func(SQLiteDll, "sqlite3_finalize", {C_HANDLE}, C_INT) 
constant _sqlite3_bind_blob =define_c_func(SQLiteDll, "sqlite3_bind_blob",{C_HANDLE, C_INT, C_HANDLE, C_INT, C_HANDLE}, C_INT) 
constant _sqlite3_bind_blob64 =define_c_func(SQLiteDll, "sqlite3_bind_blob64", {C_HANDLE, C_INT, C_HANDLE, C_INT64 , C_HANDLE}, C_INT) 
constant _sqlite3_bind_double =define_c_func(SQLiteDll, "sqlite3_bind_double", {C_HANDLE, C_INT, C_DOUBLE}, C_INT) 
constant _sqlite3_bind_int=define_c_func(SQLiteDll, "sqlite3_bind_int", {C_HANDLE, C_INT, C_INT}, C_INT) 
constant _sqlite3_bind_int64=define_c_func(SQLiteDll, "sqlite3_bind_int64", {C_HANDLE, C_INT, C_INT64}, C_INT) 
constant _sqlite3_bind_null =define_c_func(SQLiteDll, "sqlite3_bind_null",{C_HANDLE, C_INT}, C_INT) 
constant _sqlite3_bind_text =define_c_func(SQLiteDll, "sqlite3_bind_text",{C_HANDLE,C_INT,C_POINTER,C_INT,C_HANDLE}, C_INT) 
constant _sqlite3_bind_value=define_c_func(SQLiteDll, "sqlite3_bind_value", {C_HANDLE, C_INT, C_HANDLE}, C_INT) 
constant _sqlite3_bind_pointer =define_c_func(SQLiteDll, "sqlite3_bind_pointer",{C_HANDLE, C_INT, C_HANDLE, C_POINTER,C_HANDLE}, C_INT) 
constant _sqlite3_bind_zeroblob=define_c_func(SQLiteDll, "sqlite3_bind_zeroblob", {C_HANDLE, C_INT, C_INT}, C_INT) 
constant _sqlite3_bind_zeroblob64=define_c_func(SQLiteDll, "sqlite3_bind_zeroblob64", {C_HANDLE, C_INT, C_INT64}, C_INT) 
constant _sqlite3_bind_parameter_count=define_c_func(SQLiteDll, "sqlite3_bind_parameter_count", {C_HANDLE}, C_INT) 
constant _sqlite3_bind_parameter_name =define_c_func(SQLiteDll, "sqlite3_bind_parameter_name",{C_HANDLE, C_INT}, C_POINTER) 
constant _sqlite3_bind_parameter_index=define_c_func(SQLiteDll, "sqlite3_bind_parameter_index", {C_HANDLE, C_POINTER}, C_INT) 
constant _sqlite3_clear_bindings =define_c_func(SQLiteDll, "sqlite3_clear_bindings",{C_HANDLE}, C_INT) 
constant _sqlite3_table_column_metadata =define_c_func(SQLiteDll, "sqlite3_table_column_metadata",{C_HANDLE, C_POINTER, C_POINTER, C_POINTER, C_POINTER, C_POINTER, C_POINTER, C_POINTER, C_POINTER}, C_INT) 
constant _sqlite3_column_count =define_c_func(SQLiteDll, "sqlite3_column_count",{C_HANDLE}, C_INT) 
constant _sqlite3_column_name =define_c_func(SQLiteDll, "sqlite3_column_name", {C_HANDLE, C_INT}, C_POINTER) 
constant _sqlite3_column_database_name=define_c_func(SQLiteDll, "sqlite3_column_database_name", {C_HANDLE,C_INT}, C_POINTER) 
constant _sqlite3_column_table_name =define_c_func(SQLiteDll, "sqlite3_column_table_name", {C_HANDLE,C_INT}, C_POINTER) 
constant _sqlite3_column_origin_name=define_c_func(SQLiteDll, "sqlite3_column_origin_name", {C_HANDLE,C_INT}, C_POINTER) 
constant _sqlite3_column_decltype=define_c_func(SQLiteDll, "sqlite3_column_decltype", {C_HANDLE,C_INT}, C_POINTER) 
constant _sqlite3_data_count=define_c_func(SQLiteDll, "sqlite3_data_count", {C_HANDLE}, C_INT) 
constant _sqlite3_column_blob =define_c_func(SQLiteDll, "sqlite3_column_blob", {C_HANDLE, C_INT}, C_POINTER) 
constant _sqlite3_column_double=define_c_func(SQLiteDll, "sqlite3_column_double", {C_HANDLE, C_INT}, C_DOUBLE) 
constant _sqlite3_column_int=define_c_func(SQLiteDll, "sqlite3_column_int", {C_HANDLE, C_INT}, C_INT) 
constant _sqlite3_column_int64 =define_c_func(SQLiteDll, "sqlite3_column_int64",{C_HANDLE, C_INT}, C_INT64) 
constant _sqlite3_column_text =define_c_func(SQLiteDll, "sqlite3_column_text", {C_HANDLE, C_INT}, C_POINTER) 
constant _sqlite3_column_value =define_c_func(SQLiteDll, "sqlite3_column_value",{C_HANDLE, C_INT}, C_POINTER) 
constant _sqlite3_column_bytes =define_c_func(SQLiteDll, "sqlite3_column_bytes",{C_HANDLE, C_INT}, C_INT) 
constant _sqlite3_column_type =define_c_func(SQLiteDll, "sqlite3_column_type", {C_HANDLE, C_INT}, C_INT) 
constant _sqlite3_last_insert_rowid =define_c_func(SQLiteDll, "sqlite3_last_insert_rowid", {C_HANDLE}, C_INT) 
constant _sqlite3_result_null =define_c_proc(SQLiteDll, "sqlite3_result_null", {C_HANDLE}) 
constant _sqlite3_get_autocommit =define_c_func(SQLiteDll, "sqlite3_get_autocommit",{C_HANDLE}, C_INT) 
constant _sqlite3_errstr =define_c_func(SQLiteDll, "sqlite3_errstr",{C_INT}, C_POINTER) 
constant _sqlite3_value_bytes =define_c_func(SQLiteDll, "sqlite3_value_bytes",{C_HANDLE}, C_INT) 
constant _sqlite3_create_function =define_c_func(SQLiteDll, "sqlite3_create_function",{C_HANDLE, C_POINTER, C_INT, C_INT, C_POINTER, C_POINTER, C_POINTER, C_POINTER}, C_INT) 
 
 
 
----------------------------- 
--sqlite3 error messages 
----------------------------- 
public function SQErrmsg(DbHandle handle) 
  atom message_addr = c_func(_sqlite3_errmsg, {handle} ) 
  return peek_string(message_addr) 
end function 
 
public function SQErrmsg16(DbHandle handle) 
  atom message_addr = c_func(_sqlite3_errmsg, {handle} ) 
  return peek_wstring(message_addr) 
end function 
 
 
----------------------------- 
-- Routine : sqlite3_free 
-- Syntax : sqlite3_free(atom addr) 
-- Description : Frees memory allocated from mprintf() or vmprintf(). 
-- Used internally by sqlite 
----------------------------- 
public procedure SQFree(atom addr) 
   atom t=c_func(_sqlite3_free, {addr}) 
end procedure 
 
 
----------------------------- 
-- SQOpen 
----------------------------- 
 
public function SQOpen(string dbname) 
  DbHandle dbhandle=-1, db_addr 
  ifdef BITS32 then 
    db_addr = allocate(4, true) 
  elsedef 
    db_addr = allocate(8, true) 
  end ifdef 
  atom str = allocate_string(dbname, true) 
  atom ret = c_func(_sqlite3_open, {str, db_addr}) 
  if ret != SQLITE_OK then 
    return dbhandle 
  end if 
  ifdef BITS32 then 
    dbhandle=peek4u(db_addr) 
  elsedef 
    dbhandle=peek8u(db_addr) 
  end ifdef 
  if ret != SQLITE_OK then 
    integer tmp = c_func(_sqlite3_close, {dbhandle}) 
  end if 
  return dbhandle 
end function 
 
----------------------------- 
-- SQClose 
----------------------------- 
public function SQClose(DbHandle handle) 
  return c_func(_sqlite3_close, {handle}) 
end function 
 
----------------------------- 
-- sqlite_libversion 
----------------------------- 
public function SQVersNum() 
    return c_func(_sqlite3_libversion_number,{}) 
end function 
 
public function SQBusyTimeout(DbHandle handle, integer aBusyTimeoutMs) 
    return c_func(_sqlite3_busy_timeout, {handle, aBusyTimeoutMs}) 
end function 
 
----------------------------- 
-- sqlite_prepare 
----------------------------- 
-- it returns statement address, or 0 if failure 
public function SQPrepare(DbHandle handle, string zSql) 
  atom str = allocate_string(zSql, true), ppStmt 
  DbStatement stmt=-1 
  ifdef BITS32 then 
    ppStmt = allocate(4, true) 
  elsedef 
    ppStmt = allocate(8, true) 
  end ifdef 
  integer ret = c_func(_sqlite3_prepare, {handle, str, -1, ppStmt, 0}) 
  if ret != SQLITE_OK then 
    return stmt 
  end if 
  ifdef BITS32 then 
    stmt = peek4u(ppStmt) 
  elsedef 
    stmt = peek8u(ppStmt) 
  end ifdef 
  return stmt 
end function 
 
 
----------------------------- 
-- SQStep 
----------------------------- 
-- Returns SQLITE_DONE, SQLITE_ROW, SQLITE_BUSY, 
-- SQLITE_ERROR or SQLITE_MISUSE, see http://sqlite.org/capi3ref.html#sqlite3_step 
-- SQLITE_ROW is returned if there were any row(s) found 
-- and are ready to be read with sqlite_column_* functions. 
-- SQLITE_DONE is returned if there was no row found. 
public function SQStep(DbStatement stmt) 
  return c_func(_sqlite3_step,{stmt}) 
end function 
 
----------------------------- 
-- sqlite_reset 
----------------------------- 
public function SQReset(DbStatement stmt) 
  return c_func(_sqlite3_reset,{stmt}) 
end function 
 
 
 
----------------------------- 
-- SQFinalize 
----------------------------- 
-- can return SQLITE_ABORT, see http://sqlite.org/capi3ref.html#sqlite3_finalize 
public function SQFinalize(DbStatement stmt) 
   return c_func(_sqlite3_finalize,{stmt}) 
end function 
 
 
-- Clears away all the bindings of a prepared statement (can be associated with #reset() above). 
public function SQClearBindings(DbStatement stmt) 
  return c_func(_sqlite3_clear_bindings,{stmt}) 
end function 
 
-- Bind an int value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement 
public function SQBindInteger(DbStatement stmt, integer index, atom  value) 
    return c_func(_sqlite3_bind_int, {stmt, index, value}) 
end function 
 
-- Bind a 64bits int value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement 
public function SQBindInteger64(DbStatement stmt, integer index, atom  value) 
  return c_func(_sqlite3_bind_int64, {stmt, index, value}) 
end function 
 
-- Bind a double (64bits float) value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement 
public function SQBindDouble(DbStatement stmt, integer index, atom value) 
  return c_func(_sqlite3_bind_double, {stmt, index, value}) 
end function 
 
-- Bind a string value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement 
public function SQBindText(DbStatement stmt, integer index, sequence value) 
  atom s = allocate_string(value, true) 
  return c_func(_sqlite3_bind_text, {stmt, index, s, -1, SQLITE_TRANSIENT}) 
end function 
 
-- Bind a string value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement 
public function SQBindPhixText(DbStatement stmt, integer index, sequence value) 
  sequence s = serialize(value) 
  atom len = length(s) 
  atom ss = allocate(len, true) 
  poke(ss, s) 
  return c_func(_sqlite3_bind_text, {stmt, index, ss, -1, SQLITE_TRANSIENT}) 
end function 
 
-- Bind a string value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement 
public function SQBindTextNoCopy(DbStatement stmt, integer index, sequence value) 
  atom s = allocate_string(value, true) 
  return c_func(_sqlite3_bind_text, {stmt, index, s, length(value), SQLITE_STATIC}) 
end function 
 
 
 
-- Bind a binary blob value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement 
public function SQBindBlob(DbStatement stmt, atom index, sequence value) 
  atom len = length(value) 
  atom ss = allocate(len, true) 
  poke(ss, {value}) 
  return c_func(_sqlite3_bind_blob, {stmt, index, ss, len, SQLITE_TRANSIENT}) 
end function 
 
-- Bind a binary blob value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement 
public function SQBindPhixblob(DbStatement stmt, atom index, object  value) 
  sequence s = serialize(value) 
  atom len = length(s) 
  atom ss = allocate(len, true) 
  poke(ss, s) 
  return c_func(_sqlite3_bind_blob, {stmt, index, ss, len, SQLITE_TRANSIENT}) 
end function 
 
public function SQBindBlobNoCopy(DbStatement stmt, atom index, sequence value, integer size) 
  atom s = allocate(size, true) 
  poke(s,value) 
  return c_func(_sqlite3_bind_blob, {stmt, index, s, size, SQLITE_STATIC}) 
end function 
 
public function SQBindPhixBlobNoCopy(DbStatement stmt, atom index, sequence value) 
  sequence s = serialize(value) 
  atom len = length(s) 
  atom ss = allocate(len, true) 
  poke(ss, s) 
  return c_func(_sqlite3_bind_blob, {stmt, index, s, len, SQLITE_STATIC}) 
end function 
 
----------------------------------------------------------------------------------------------------------- 
----------------------------------------------column------------------------------------------------------- 
----------------------------------------------------------------------------------------------------------- 
 
-- Return the named assigned to this result column (potentially aliased) 
public function SQGetName(DbStatement stmt, integer index) 
    atom message_addr = c_func(_sqlite3_column_name, {stmt, index}) 
    return peek_string(message_addr) 
end function 
 
 
-- Return the name of the table column that is the origin of this result column 
public function SQGetOrigName(DbStatement stmt, integer index) 
  atom message_addr = c_func(_sqlite3_column_origin_name, {stmt, index} ) 
  return peek_string(message_addr) 
end function 
 
 
 
-- Return the integer value of the column specified by its index starting at 0 
public function SQGetInteger(DbStatement stmt, integer index) 
  ifdef BITS32 then 
    return c_func(_sqlite3_column_int, {stmt, index} ) 
  elsedef 
    return c_func(_sqlite3_column_int64, {stmt, index} ) 
  end ifdef 
end function 
 
-- Return the 64bits integer value of the column specified by its index starting at 0 
public function SQGetInteger64(DbStatement stmt, integer index) 
    return c_func(_sqlite3_column_int64, {stmt, index} ) 
end function 
 
-- Return the double value of the column specified by its index starting at 0 
public function SQGetDouble(DbStatement stmt, integer index) 
    return c_func(_sqlite3_column_double, {stmt, index} ) 
end function 
 
-- Return a pointer to the text value (NULL terminated string) of the column specified by its index starting at 0 
public function SQGetText(DbStatement stmt, integer index) 
    atom message_addr = c_func(_sqlite3_column_text, {stmt, index} ) 
    if message_addr then 
      atom len = c_func(_sqlite3_column_bytes, {stmt, index} ) 
      if len= 0 then 
        return "" 
      end if 
      return peek({message_addr, len}) 
    else 
      return "" 
    end if 
end function 
 
-- Return a pointer to the blob value (*not* NULL terminated) of the column specified by its index starting at 0 
public function SQGetBlob(DbStatement stmt, integer index) 
    atom len = c_func(_sqlite3_column_bytes, {stmt, index}) 
    if len= 0 then 
        return {} 
    end if 
    atom blobdata = c_func(_sqlite3_column_blob, {stmt, index}) 
    return peek({blobdata, len}) 
end function 
 
-- Return a pointer to the blob value (*not* NULL terminated) of the column specified by its index starting at 0 
public function SQGetPhixBlob(DbStatement stmt, integer index) 
    sequence s0 = {} 
    atom len = c_func(_sqlite3_column_bytes, {stmt, index}) 
    if len= 0 then 
        return {} 
    end if 
    atom blobdata = c_func(_sqlite3_column_blob, {stmt, index}) 
    if blobdata then 
       sequence s = peek({blobdata, len}) 
       s0 = deserialize(s) 
    end if 
    return s0 --deserialize(s) 
end function 
 
-- Return a std::string to a TEXT or BLOB column 
-- Note: using sqlite3_column_blob and not sqlite3_column_text 
-- - no need for sqlite3_column_text to add a \0 on the end, as we're getting the bytes length directly 
public function SQGetString(DbStatement stmt, integer index) 
    atom len = c_func(_sqlite3_column_bytes, {stmt, index} ) 
    if len= 0 then 
        return "" 
    end if 
    atom blobdata = c_func(_sqlite3_column_blob, {stmt, index} ) 
    return peek({blobdata, len}) 
end function 
 
public function SQGetPhixString(DbStatement stmt, integer index) 
    sequence s0 = {} 
    atom len = c_func(_sqlite3_column_bytes, {stmt, index}) 
    if len= 0 then 
        return {} 
    end if 
    atom blobdata = c_func(_sqlite3_column_blob, {stmt, index}) 
    if blobdata then 
       sequence s = peek({blobdata, len}) 
       s0 = deserialize(s) 
    end if 
    return s0 --deserialize(s) 
end function 
 
 
 
 
 
public function SQGetColCount(DbStatement stmt) 
  return c_func(_sqlite3_column_count,{stmt}) 
end function 
 
 
-- Return the type of the value of the column 
public function SQGetColType(DbStatement stmt, integer index) 
    return c_func(_sqlite3_column_type, {stmt, index} ) 
end function 
 
-- Return the number of bytes used by the text value of the column 
public function SQGetColBytes(DbStatement stmt, integer index) 
    return c_func(_sqlite3_column_bytes, {stmt, index} ) 
end function 
 
 
------------------------------------------------------------------------------------- 
-- sqlite3_last_insert_rowid(sqlite3*); 
--gets the last inserted row_id from open database db 
--remember, to get the last inserted row, need to  work on an open database, where the data has just been inserted 
------------------------------------------------------------------------------------- 
public function SQGetLastRowId(DbHandle handle) 
  return c_func(_sqlite3_last_insert_rowid, {handle}) 
end function 
 
----------------------------- 
-- SQFreeTable 
----------------------------- 
public procedure SQFreeTable(atom data_addr) 
  c_proc(_sqlite3_free_table,{data_addr}) 
end procedure 
 
public function SQGetErrStr(atom num) 
   return peek_string(c_func(_sqlite3_errstr,{num})) 
end function 
 
public function SQGetErrStr16(atom num) 
   return peek_wstring(c_func(_sqlite3_errstr,{num})) 
end function 
 
 
public function SQNowToTimestampString()  
  return format_timedate(date(),"YYYY-MM-DD hh:mm:ss") 
end function 
 
public function SQTimestampString(string td)  
  return parse_date_string(td,{"YYYY-MM-DD hh:mm:ss"}) 
end function 
 
public function SQSetDateTimesStr(integer Y, integer M, integer D, integer h=0, integer m=0, integer s=0)  
  sequence res = {Y, M, D, h, m, s, 0, day_of_year(Y,M,D)} 
  return format_timedate(res,"YYYY-MM-DD hh:mm:ss") 
end function 
 
public function SQSetDateTimesTD(integer Y, integer M, integer D, integer h=0, integer m=0, integer s=0)  
  sequence res = {Y, M, D, h, m, s, 0, day_of_year(Y,M,D)} 
  string tz = format_timedate(res,"YYYY-MM-DD hh:mm:ss") 
  return parse_date_string(tz,{"YYYY-MM-DD hh:mm:ss"})   
end function 
 
 
 
 
 
public function SQTableExists(DbHandle handle, sequence TableName) 
    sequence zSql = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name=?" 
    DbStatement stmt = SQPrepare(handle, zSql) 
    if stmt = -1 then 
      return false 
    end if 
    atom ret = SQBindText(stmt, 1, TableName) 
    ret = SQStep(stmt) 
    if (SQLITE_ROW != ret) and (SQLITE_DONE != ret) then -- on row or no (more) row ready, else it's a problem 
      return false 
    end if 
    ret = SQGetInteger(stmt, 0) 
    integer ret1 = SQFinalize(stmt) 
    return ret 
end function 
 
----------------------------- 
-- SQExec2 
----------------------------- 
-- Shortcut to execute one or multiple SQL statements without results (UPDATE, INSERT, ALTER, COMMIT, CREATE...). 
public function SQExec(DbHandle handle, string query) 
    atom str = allocate_string(query, true) 
    integer ret = c_func(_sqlite3_exec, {handle, str, null, null, null}) 
    -- Return the number of rows modified by those SQL statements (INSERT, UPDATE or DELETE only) 
    return c_func(_sqlite3_changes, {handle}) 
end function 
 
----------------------------- 
-- SQExec2 
----------------------------- 
-- returns SQLITE_OK, SQLITE_ABORT or SQLITE_BUSY 
public function SQExec2(DbHandle handle, sequence cmd) 
  integer ret 
  atom err_ptr_addr, err_addr 
  ifdef BITS32 then 
    err_ptr_addr = allocate(4, true) 
    poke4(err_ptr_addr, 0) 
  elsedef 
    err_ptr_addr = allocate(8, true) 
    poke8(err_ptr_addr, 0) 
  end ifdef 
  atom cmd_addr = allocate_string(cmd, true) 
  ret = c_func(_sqlite3_exec,{handle, cmd_addr, NULL, NULL, err_ptr_addr}) 
  err_addr = peek4u(err_ptr_addr) 
  if err_addr > 0 then 
      SQFree(err_addr) 
  end if 
  if ret != SQLITE_OK then 
     return false 
  end if 
  return ret 
end function 
 
public function SQStartTransaction(DbHandle handle) 
  return SQExec(handle, "BEGIN TRANSACTION") 
end function 
 
public function SQEndTransaction(DbHandle handle) 
  return SQExec(handle, "COMMIT TRANSACTION") 
end function 
 
public function DatabaseUpdate(DbHandle handle, string prag) 
  return SQExec(handle, prag) 
end function 
 
public function SQGetColumn(DbHandle db, DbStatement stmt, integer col) 
  atom columnType = SQGetColType(stmt, col) 
  switch columnType 
    case SQLITE_INTEGER: 
       return SQGetInteger(stmt, col) 
    case SQLITE_FLOAT: 
       return SQGetDouble(stmt, col) 
    case SQLITE_TEXT: 
       return SQGetText(stmt, col) 
    case SQLITE_BLOB: 
       return SQGetBlob(stmt, col) 
    case SQLITE_NULL: 
       return {} 
    default:     
       return SQErrmsg(db) 
  end switch 
end function 
 
 
----------------------------- 
-- SQGetTable 
----------------------------- 
public function SQGetTable(DbHandle db, sequence cmd) 
  DbStatement stmt = SQPrepare(db, cmd) 
  if stmt=-1 then 
    return {} 
  end if 
  atom cols = SQGetColCount(stmt) 
  atom row_num = 0 
  sequence data = {}, tmpcol = {} 
  while SQStep(stmt) != SQLITE_DONE do -- read all rows which SELECT query finds 
    tmpcol = {} 
    for i=0 to cols do 
      tmpcol &= {SQGetColumn(db, stmt, i)} 
    end for 
    data &= {tmpcol} 
  end while 
  return data 
end function 
 
 
 
public function getMetaInformation(DbHandle handle) 
  sequence metatables = {}, tmp = {}, seqs = {} 
  sequence tables = SQGetTable(handle, "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;") 
  ?length(tables) 
  for i = 1 to length(tables) do 
    string tablename = tables[i][1] 
    sequence columns = SQGetTable(handle, "PRAGMA table_info('table_name')") 
    for j=1 to length(columns) do 
      tmp &= {columns[j]} 
    end for 
    metatables &= {{tablename} & {tmp}} 
    tmp = {} 
  end for 
  sequence sequences = SQGetTable(handle, "SELECT name, seq FROM sqlite_sequence;") 
  for i = 1 to length(sequences) do 
    tmp &= {{sequences[i][1]} & {sequences[i][2]}} 
    seqs &= tmp 
  end for 
  return metatables & seqs 
end function 
 
 
 
/* 

procedure BlobTest() 
  object Void 
  DbHandle db 
  DbStatement stmt 
  sequence read_text_data 
  sequence blob_data 
  object read_blob_data 
  integer row_num, read_int_data 
  sequence dbfile = "testxx.db" 
  -- create database, create table 
  db = SQOpen(dbfile) 
  Void = SQExec(db, "PRAGMA synchronous = OFF;") 
  Void = SQExec(db, "BEGIN TRANSACTION") 
  string sqstr = "create table table_name(field_1 INTEGER PRIMARY KEY,field_2 TEXT,field_3 BLOB,field_4 REAL);" 
  stmt = SQPrepare(db, sqstr) 
  Void = SQStep(stmt) 
  Void = SQFinalize(stmt) 
 
  -- write Euphoria objects into database 
  sqstr = "insert into table_name values(?,?,?,?)" 
  stmt = SQPrepare(db, sqstr) 
  for i = 1 to 10 do -- write 10 rows 
    integer rets = SQBindInteger(stmt, 1, i) 
    rets = SQBindText(stmt, 2, sprintf ("text in row %d", {i})) 
    blob_data = {i, 0, time () * rand (10000), 2.45,  {"this is blob data"}, {8, {9, 10}}} 
    rets = SQBindPhixblob(stmt, 3, blob_data) 
    puts(1, "inserted blob data=\n") 
    -- pretty_print (1, blob_data, {}) 
    -- puts(1, "\n\n") 
    atom retsd = SQBindDouble(stmt, 4, PI) 
    Void = SQStep(stmt) 
    rets = SQReset(stmt) 
  end for 
  Void = SQFinalize(stmt) 
  Void = SQExec(db, "COMMIT TRANSACTION") 
  -- read Euphoria objects from database 
  -- data = SQGetTable (db, "select * from table_name where field_1=1") 
  -- ?data 
  -- Void = wait_key () 
  --sqstr = "select ?,?,?,? from table_name where field_1 > 0" 
  sqstr = "select field_1,field_2,field_3,field_4 from table_name where field_1 > 0" 
  --stmt = SQPrepare(db, "select * from table_name where field_1 between 1 and 10", length("select * from table_name where field_1 between 1 and 10")) 
  --stmt = sqlite_prepare(db, "select * from table_name where field_1 = 1") 
  stmt = SQPrepare(db, sqstr) 
  ?{stmt, "2. read"} 
  row_num = 0 
  while SQStep(stmt) != SQLITE_DONE do -- read all rows which SELECT query finds 
    row_num += 1 
    read_int_data = SQGetInteger(stmt, 0) 
    read_text_data = SQGetText(stmt, 1) 
    read_blob_data = SQGetPhixBlob(stmt, 2) 
    atom xread_int_data2 = SQGetDouble(stmt, 3) 
    ?SQErrmsg(db) 
    ?xread_int_data2 
    printf (1, "row %d:\n", row_num) 
    printf (1, "read_int_data=%d\n", read_int_data) 
    printf (1, "read_text_data=%s\n", {read_text_data}) 
    puts(1, "read_blob_data=")  ?read_blob_data 
    printf (1, "read_int_data2 = %g \n", xread_int_data2) 
    puts(1, "\n") 
    Void = wait_key () 
  end while 
  Void = SQFinalize(stmt) 
  -- if not equal (read_text_data, text_data) then 
  --     puts (1, "Program works wrong: text data is not the same\n") 
  -- end if 
  -- if not equal (read_blob_data, blob_data) then 
  --     puts (1, "Program works wrong: blob data is not the same\n") 
  -- end if 
  -- close database 
  integer rets = SQClose(db) 
  puts (1, "done. press a key to close...\n") 
  Void = wait_key () 
end procedure 
 
 
 
 
 
 
DbHandle h = SQOpen("testxx.db") 
--?SQTableExists(h, "table_name2") 
--?getMetaInformation(h) 
 
--?SQGetTable(h, "select * from table_name") --SELECT name, sql FROM sqlite_master WHERE type='table_name' ORDER BY name; 
--?SQGetTable(h, "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;") 
--?SGetTable(h, "SELECT name, seq FROM sqlite_sequence;") 
--?SQGetTable(h, "PRAGMA table_info('table_name')") 
 
--BlobTest() 
--?getMetaInformation(h) 
--?SQTableExists16(h, "books") 
 
 
/* 

?SQTableExists(h, "select * from books") 
?"-----------------------" 
--?SQGetTable(h, "select * from books where BOOKID=624") 
?SQGetTable(h, "select * from books;") 
*/ 
 
maybe_any_key() 
*/ 
new topic     » goto parent     » topic index » view message » categorize

4. Re: sqlite interface ?

Hi

Thanks. Looks good, especially if it deals seemlesly with 32 and 64 bit dlls.

I can see that you've added a couple of tests at the bottom of the include, if you wanted to release this as a package then might I suggest including the include file with some documentation in the include, the actual dll files so people can find them easily, the help file from the dropbox repositry, and some example programs. There is still some confusion as to where the new archive is going to live, but Pete's Pecan would be a good start.

That being said, this is a more comprehensive include than the one I have been maintaining for years, and am now rarely using, so I am more than happy to hand the mantle of maintainance over to you smile

Cheers

Chris

new topic     » goto parent     » topic index » view message » categorize

5. Re: sqlite interface ?

begin said...

unfortunately i have no possibility to do that - and don't how to do that. so i post it here and when you copied it, please tell me, i delete it.

You should use Pastey whenever you have a large amount of content to share. This helps keep the forum from being too cluttered with large walls of code or text.

I'm working on a new database abstraction library to replace EDBI, which I plan to use for a rewrite of this website. I'd like to use this wrapper to implement the SQLite support, if that's okay.

-Greg

new topic     » goto parent     » topic index » view message » categorize

6. Re: sqlite interface ?

I'd like anyone to use this wrapper, if they have any use for it.

that's why i post code and - i hope that, if there are corrections or additions, they will be posted too. no strings attached, free as in beer.

richard

new topic     » goto parent     » topic index » view message » categorize

7. Re: sqlite interface ?

EDIT:

please recopy code in first post. changed - inserted if's for null in the following functions:

-- Return a pointer to the text value (NULL terminated string) of the column specified by its index starting at 0 
public function SQGetText(DbStatement stmt, integer index) 
-- Return a pointer to the blob value (*not* NULL terminated) of the column specified by its index starting at 0 
public function SQGetBlob(DbStatement stmt, integer index) 
-- Return a pointer to the blob value (*not* NULL terminated) of the column specified by its index starting at 0 
public function SQGetPhixBlob(DbStatement stmt, integer index) 
public function SQGetString(DbStatement stmt, integer index) 
public function SQGetPhixString(DbStatement stmt, integer index) 
new topic     » goto parent     » topic index » view message » categorize

Search



Quick Links

User menu

Not signed in.

Misc Menu