1. sqlite interface ?
- Posted by begin Jun 30, 2018
- 1555 views
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.
2. Re: sqlite interface ?
- Posted by ChrisB (moderator) Jul 01, 2018
- 1474 views
Hi
I would like to take a look at that - could I get a download link?
Cheers
Chris
3. Re: sqlite interface ?
- Posted by begin Jul 02, 2018
- 1459 views
- Last edited Jul 03, 2018
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() */
4. Re: sqlite interface ?
- Posted by ChrisB (moderator) Jul 02, 2018
- 1399 views
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
Cheers
Chris
5. Re: sqlite interface ?
- Posted by ghaberek (admin) Jul 02, 2018
- 1429 views
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
6. Re: sqlite interface ?
- Posted by begin Jul 02, 2018
- 1392 views
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
7. Re: sqlite interface ?
- Posted by begin Jul 03, 2018
- 1336 views
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)