< GeeXLab Reference Guide />

> Back to Reference Guide Index


gh_sqlite3 library

Description

gh_sqlite3 is the module that manages the embedded SQLite3 database engine.


Number of functions: 28

  1. gh_sqlite3.db_get_version (Lua - Python)
  2. gh_sqlite3.db_open (Lua - Python)
  3. gh_sqlite3.db_open_v2 (Lua - Python)
  4. gh_sqlite3.db_close (Lua - Python)
  5. gh_sqlite3.db_exec (Lua - Python)
  6. gh_sqlite3.db_prepare (Lua - Python)
  7. gh_sqlite3.db_finalize (Lua - Python)
  8. gh_sqlite3.db_get_column_count (Lua - Python)
  9. gh_sqlite3.db_get_column_name (Lua - Python)
  10. gh_sqlite3.db_get_column_type (Lua - Python)
  11. gh_sqlite3.db_step (Lua - Python)
  12. gh_sqlite3.db_get_last_insert_rowid (Lua - Python)
  13. gh_sqlite3.db_column_get_text1024 (Lua - Python)
  14. gh_sqlite3.db_column_get_text (Lua - Python)
  15. gh_sqlite3.db_column_get_int (Lua - Python)
  16. gh_sqlite3.db_column_get_double (Lua - Python)
  17. gh_sqlite3.db_column_get_blob (Lua - Python)
  18. gh_sqlite3.db_column_blob_to_file (Lua - Python)
  19. gh_sqlite3.db_column_blob_to_string (Lua - Python)
  20. gh_sqlite3.db_bind_text (Lua - Python)
  21. gh_sqlite3.db_bind_int (Lua - Python)
  22. gh_sqlite3.db_bind_double (Lua - Python)
  23. gh_sqlite3.db_bind_blob_from_file (Lua - Python)
  24. gh_sqlite3.db_bind_blob_from_string (Lua - Python)
  25. gh_sqlite3.db_bind_blob_from_buffer (Lua - Python)
  26. gh_sqlite3.db_enable_extended_result_codes (Lua - Python)
  27. gh_sqlite3.db_get_extended_errcode (Lua - Python)
  28. gh_sqlite3.db_get_errcode (Lua - Python)



db_get_version

Description

Get the SQLite3 engine version.


Syntax

ver, ver_str = gh_sqlite3.db_get_version()

Languages

Lua - Python


Parameters

This function has no input parameter(s).


Return Values


Code sample

    
ver, ver_str = gh_sqlite3.db_get_version()
            


db_open

Description

Initializes and opens a database.


Syntax

dbid, rc = gh_sqlite3.db_open(
 db_filename
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local db_filename = demo_dir .. "database01.db"

local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 

dbid, rc = gh_sqlite3.db_open(db_filename)
            


db_open_v2

Description

Initializes and opens a database.


Syntax

dbid, rc = gh_sqlite3.db_open_v2(
 db_filename,
 flags
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local db_filename = demo_dir .. "database01.db"

local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 

local SQLITE_OPEN_READONLY = 1
local SQLITE_OPEN_READWRITE = 2
local SQLITE_OPEN_CREATE = 4
local SQLITE_OPEN_URI = 64
local SQLITE_OPEN_MEMORY = 128
local SQLITE_OPEN_NOMUTEX = 32768
local SQLITE_OPEN_FULLMUTEX = 65536
local SQLITE_OPEN_SHAREDCACHE = 131072
local SQLITE_OPEN_PRIVATECACHE = 262144

local flags = SQLITE_OPEN_READWRITE

dbid, rc = gh_sqlite3.db_open_v2(db_filename, flags)
            


db_close

Description

Closes a database.


Syntax

rc = gh_sqlite3.db_close(
 dbid
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
rc = gh_sqlite3.db_close(dbid)    
            


db_exec

Description

Executes an SQL request.


Syntax

rc = gh_sqlite3.db_exec(
 dbid,
 sql
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local sql = "CREATE TABLE users (name varchar(64), age int not null)"
local rc = gh_sqlite3.db_exec(dbid, sql)
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
if (rc == SQLITE_ERROR) then
  print("SQL error 1")
end  
            


db_prepare

Description

Prepares an SQL request for later use with db_step().


Syntax

rc = gh_sqlite3.db_prepare(
 dbid,
 sql
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local sql = "SELECT * FROM users"
local rc = gh_sqlite3.db_prepare(dbid, sql)
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
if (rc == SQLITE_OK) then
  local num_columns = gh_sqlite3.db_get_column_count(dbid)
  ...
  
end  
            


db_finalize

Description

Frees resources allocated by db_prepare().


Syntax

rc = gh_sqlite3.db_finalize(
 dbid
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
rc = gh_sqlite3.db_finalize(dbid)
            


db_get_column_count

Description

Gets the number of columns in a result set.


Syntax

num_columns, rc = gh_sqlite3.db_get_column_count(
 dbid
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local num_columns, rc = gh_sqlite3.db_get_column_count(dbid)
            


db_get_column_name

Description

Gets the name of a column.


Syntax

name = gh_sqlite3.db_get_column_name(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
name = gh_sqlite3.db_get_column_name(dbid, column)
            


db_get_column_type

Description

Gets the type of a column in a result set.


Syntax

type = gh_sqlite3.db_get_column_type(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local SQLITE_INTEGER = 1
local SQLITE_FLOAT = 2
local SQLITE_TEXT = 3
local SQLITE_BLOB = 4
local c = 0 -- first column
local type = gh_sqlite3.db_get_column_type(dbid, c)
if (type == SQLITE_TEXT) then
  local v = gh_sqlite3.db_column_get_text1024(dbid, c)
  print("Column " .. c .. " - type: TEXT - value: " .. v)
elseif  (type == SQLITE_INTEGER) then
  local v = gh_sqlite3.db_column_get_int(dbid, c)
  print("Column " .. c .. " - type: INTEGER - value: " .. v)
elseif  (type == SQLITE_FLOAT) then
  local v = gh_sqlite3.db_column_get_double(dbid, c)
  print("Column " .. c .. " - type: FLOAT - value: " .. v)
elseif  (type == SQLITE_BLOB) then
  local size, dataptr = gh_sqlite3.db_column_get_blob(dbid, c)
  print("Column " .. c .. " - type: BLOB - size: " .. size)
end
            


db_step

Description

Evaluates a request prepared with db_prepare().


Syntax

rc = gh_sqlite3.db_step(
 dbid
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 

rc = gh_sqlite3.db_step(dbid)
while (rc ~= SQLITE_DONE) do
  ...
end
            


db_get_last_insert_rowid

Description

Gets the last insert ID.


Syntax

gh_sqlite3.db_get_last_insert_rowid(
 dbid
)

Languages

Lua - Python


Parameters


Return Values

This function has no return value(s).


Code sample

    
lastid = gh_sqlite3.db_get_last_insert_rowid(dbid)
            


db_column_get_text1024

Description

Gets the text value of a column.


Syntax

text, rc = gh_sqlite3.db_column_get_text1024(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
-- return codes:
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 
v, rc = gh_sqlite3.db_column_get_text1024(dbid, c)
            


db_column_get_text

Description

Gets the text value of a column.


Syntax

text, rc = gh_sqlite3.db_column_get_text(
 dbid,
 column,
 num_chars
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
-- return codes:
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 
v, rc = gh_sqlite3.db_column_get_text(dbid, c, 1024)
            


db_column_get_int

Description

Gets the integer value of a column.


Syntax

v, rc = gh_sqlite3.db_column_get_int(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
-- return codes:
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 
v, rc = gh_sqlite3.db_column_get_int(dbid, c)
            


db_column_get_double

Description

Gets the real value (a double) of a column.


Syntax

v, rc = gh_sqlite3.db_column_get_double(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
-- return codes:
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 
v, rc = gh_sqlite3.db_column_get_double(dbid, c)
            


db_column_get_blob

Description

Gets the size and data pointer of a blob field.


Syntax

dataptr, size = gh_sqlite3.db_column_get_blob(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
-- return codes:
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 
dataptr, size = gh_sqlite3.db_column_get_blob(dbid, c)
            


db_column_blob_to_file

Description

Stores the content of a blob in a file.


Syntax

rc = gh_sqlite3.db_column_blob_to_file(
 dbid,
 column,
 filename
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
-- return codes:
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 
rc = gh_sqlite3.db_column_blob_to_file(dbid, c, filename)
            


db_column_blob_to_string

Description

Stores the content of a blob in a string.


Syntax

str = gh_sqlite3.db_column_blob_to_string(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
str = gh_sqlite3.db_column_blob_to_string(dbid, c)
            


db_bind_text

Description

Binds a text value for a prepared statement.


Syntax

rc, text = gh_sqlite3.db_bind_text(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    

-- return codes:
local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 

local sql = "CREATE TABLE images (id INTEGER PRIMARY KEY, name varchar(64), data BLOB)"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 1")
end  

sql = "INSERT INTO images (name, data) VALUES (?, ?);"
if (gh_sqlite3.db_prepare(dbid, sql) == SQLITE_OK) then

  local column = 1
  gh_sqlite3.db_bind_text(dbid, column, "toto.jpg")
  
  column = 2
  gh_sqlite3.db_bind_blob_from_file(dbid, column, demo_dir .. "assets/toto.jpg")

  rc = gh_sqlite3.db_step(dbid)

  gh_sqlite3.db_finalize(dbid)
end
            


db_bind_int

Description

Binds an integer value for a prepared statement.


Syntax

rc, x = gh_sqlite3.db_bind_int(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local column = 1
x= 12
gh_sqlite3.db_bind_int(dbid, column, x)


db_bind_double

Description

Binds a double value for a prepared statement.


Syntax

rc, x = gh_sqlite3.db_bind_double(
 dbid,
 column
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local column = 1
x = 3.14159265
gh_sqlite3.db_bind_double(dbid, column, x)


db_bind_blob_from_file

Description

Binds a blob value for a prepared statement. The content that will be stored in the blob comes from a file.


Syntax

rc = gh_sqlite3.db_bind_blob_from_file(
 dbid,
 column,
 filename
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local column = 1
filename = demo_dir .. "assets/image.jpg"
gh_sqlite3.db_bind_blob_from_file(dbid, column, filename)


db_bind_blob_from_string

Description

Binds a blob value for a prepared statement. The content that will be stored in the blob comes from a string.


Syntax

rc = gh_sqlite3.db_bind_blob_from_string(
 dbid,
 column,
 str
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local column = 1
str = "......."
gh_sqlite3.db_bind_blob_from_string(dbid, column, str)


db_bind_blob_from_buffer

Description

Binds a blob value for a prepared statement. The content that will be stored in the blob comes from a memory buffer.


Syntax

rc = gh_sqlite3.db_bind_blob_from_buffer(
 dbid,
 column,
 buffer_ptr,
 buffer_size
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
local demo_dir = gh_utils.get_demo_dir()
filename = demo_dir .. "assets/data.txt"
buffer, buffer_size = gh_utils.file_buffer_create(filename)

local column = 1
gh_sqlite3.db_bind_blob_from_buffer(dbid, column, buffer, buffer_size)

gh_sqlite3.db_step(dbid)

-- After step()...
gh_utils.file_buffer_kill(buffer)


db_enable_extended_result_codes

Description

Enables or disables the extented result codes.


Syntax

gh_sqlite3.db_enable_extended_result_codes(
 dbid,
 onoff
)

Languages

Lua - Python


Parameters


Return Values

This function has no return value(s).


Code sample

    
onoff = 1
gh_sqlite3.db_enable_extended_result_codes(dbid, onoff)
            


db_get_extended_errcode

Description

Gets the last extended error code. Extended error codes must be enabled with db_enable_extended_result_codes().


Syntax

errcode = gh_sqlite3.db_get_extended_errcode(
 dbid
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
errcode = gh_sqlite3.db_get_extended_errcode(dbid)
            


db_get_errcode

Description

Gets the last error code.


Syntax

errcode = gh_sqlite3.db_get_errcode(
 dbid
)

Languages

Lua - Python


Parameters


Return Values


Code sample

    
errcode = gh_sqlite3.db_get_errcode(dbid)
            






GeeXLab Rootard Guide | Downloads | Contact | Newsletter