OpenEuphoria

Re: LibCurl 4 and SQLite 3 wrappers

• Posted by petelomax Aug 30, 2019
• 1071 views

include sqlite3.e 
res = exec_sql_command(sql_db,`SELECT * FROM categories`) 

I can't say much about that particular wrapper, but anyway, I quickly translated your code to phix and my pSQLite.e:

include pSQLite.e 
constant db_name = "mydb.sqlite"  
object res = delete_file(db_name) 
sqlite3 sql_db = sqlite3_open(db_name) 
  
-- NB: not properly tested; you may need to call [eg] sqlite3_set_fatal_id() before this sort of error handling becomes meaningful. 
if sql_db <= 0 then  
        puts(1, "Couldn't open SQLite database\n")  
else  
        puts(1,"Opened " & db_name)  
          
        -- make sure 'categories' table exists  
        res = sqlite3_exec(sql_db, "CREATE TABLE IF NOT EXISTS categories ( id INTEGER PRIMARY KEY AUTOINCREMENT, parent INTEGER, name TEXT, data TEXT );")  
        puts(1,"\n\tcreated sql table categories")  
end if  
  
-- test the sql db  
string sql = `INSERT INTO categories (parent,name,data) VALUES (0,"departments","https://www.onlinesuperstore.com/browse/");`  
puts(1,"\n" & sql)  
res = sqlite3_exec(sql_db,sql)  
puts(1,"\n")  
?res  
res = sqlite3_get_table(sql_db,`SELECT * FROM categories`)  
pp(res) 
{} = wait_key()  

output:

Opened mydb.sqlite 
        created sql table categories 
INSERT INTO categories (parent,name,data) VALUES (0,"departments","https://www.onlinesuperstore.com/browse/"); 
0 
{{`id`, `parent`, `name`, `data`}, 
 {`1`, `0`, `departments`, `https://www.onlinesuperstore.com/browse/`}} 

I wonder if the delete_file() would help you, by removing an out-of-date table definition? HTH.

PS: To answer your earlier question, in pSQLite.e, the sqlite3_bind_blob() routine uses serialize(val), and sqlite3_column_blob() uses deserialize() to retrieve it, which are my versions of compress() and decompress(). I should say that probably means blobs saved with OE are not readable by phix, and vice versa.