OpenEuphoria

1. LibCurl 4 and SQLite 3 wrappers

• Posted by jmduro Nov 26, 2018
• 1534 views
• Last edited Aug 29, 2019

LibCurl 4 and SQLite 3 wrappers for OpenEuphoria 4 as standalone libraries (all dependencies to other EU4 Standard libraries removed).

• LibCurl 4: http://jean-marc.duro.pagesperso-orange.fr/libcurl4.zip

• SQLite 3: http://jean-marc.duro.pagesperso-orange.fr/sqlite3.zip

Jean-Marc

3. Re: LibCurl 4 and SQLite 3 wrappers

• Posted by ghaberek (admin) Aug 29, 2019
• 1277 views

It looks like sqlite_bind_blob() will store objects using compress() and sqlite_column_blob() will fetch them with decompress().

public procedure sqlite_bind_blob(atom db, atom stmt, integer param_index, object val) 
  integer ret 
  sequence val_string 
  atom val_addr    
     
  val_string = compress(val) 
  val_addr = allocate (length (val_string)) 
  poke (val_addr, val_string) 
       
  ret = c_func(xsqlite3_bind_blob,{stmt, param_index, val_addr, 
                  length (val_string), SQLITE_TRANSIENT}) 
   
  sqlite_last_err_no = ret 
  sqlite_last_err_desc = "" 
  if ret != SQLITE_OK then 
    sqlite_last_err_desc = sqlite_errmsg (db) 
    fatal (db, "sqlite_bind_blob()") 
  end if    
end procedure 

public function sqlite_column_blob(atom db, atom stmt, integer column_num) 
  atom addr, nBytes 
  sequence val_string 
       
  addr = c_func(xsqlite3_column_blob, {stmt, column_num - 1}) 
  if addr then 
    nBytes = c_func(xsqlite3_column_bytes, {stmt, column_num - 1}) 
    val_string = peek ({addr, nBytes}) 
    return decompress (val_string) 
  else 
    fatal(db, "sqlite_column_blob(): can't get data") 
    return -1 
  end if 
end function 

Curious, what benefit is there to storing serialized objects in the database? Wouldn't you be better served using text and number fields so you can query off them?

-Greg

5. Re: LibCurl 4 and SQLite 3 wrappers

• Posted by petelomax Aug 30, 2019
• 1251 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.

7. Re: LibCurl 4 and SQLite 3 wrappers

• Posted by jimcbrown (admin) Aug 30, 2019
• 1207 views

I don't get it. Why can't you do this with the appropriate number field in SQL? MySQL for example supports FLOAT and DOUBLE columns.

9. Re: LibCurl 4 and SQLite 3 wrappers

• Posted by petelomax Aug 30, 2019
• 1177 views

You're not wrong, my bad! How about this: while bind_double() will do only one atom, bind_blob() can do several hundred or more in one go?

I have also just remembered a case where there were 72 meter readings per day, and needing 72 unique column names was quite the pita (a1..24, b1..24, and c1..24, or somesuch).