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



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(
 db_id,
 col_index,
 buff_ptr,
 buff_size
)

Languages


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 col_index = 1
gh_sqlite3.db_bind_blob_from_buffer(db_id, col_index, buffer, buffer_size)

gh_sqlite3.db_step(db_id)

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


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(
 db_id,
 column,
 filename
)

Languages


Parameters


Return Values


Code sample


local col_index = 1
filename = demo_dir .. "assets/image.jpg"

gh_sqlite3.db_bind_blob_from_file(db_id, col_index, 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(
 db_id,
 col_index,
 str
)

Languages


Parameters


Return Values


Code sample


local col_index = 1
str = "......."

gh_sqlite3.db_bind_blob_from_string(db_id, col_index, str)
			


db_bind_double

Description

Binds a double value for a prepared statement.


Syntax

rc, x = gh_sqlite3.db_bind_double(
 db_id,
 col_index
)

Languages


Parameters


Return Values


Code sample


local col_index = 1
x = 3.14159265

gh_sqlite3.db_bind_double(db_id, col_index, x)
			


db_bind_int

Description

Binds an integer value for a prepared statement.


Syntax

rc, x = gh_sqlite3.db_bind_int(
 db_id,
 col_index
)

Languages


Parameters


Return Values


Code sample


local col_index = 1
x = 12

gh_sqlite3.db_bind_int(db_id, col_index, x)
			


db_bind_text

Description

Binds a text value for a prepared statement.


Syntax

rc, text = gh_sqlite3.db_bind_text(
 db_id,
 col_index
)

Languages


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(db_id, sql) == SQLITE_ERROR) then
    print("SQL error 1")
end

sql = "INSERT INTO images (name, data) VALUES (?, ?);"
if (gh_sqlite3.db_prepare(db_id, sql) == SQLITE_OK) then
    local col_index = 1
    gh_sqlite3.db_bind_text(db_id, col_index, "toto.jpg")

    col_index = 2
    gh_sqlite3.db_bind_blob_from_file(db_id, col_index, demo_dir .. "assets/toto.jpg")

    rc = gh_sqlite3.db_step(db_id)

    gh_sqlite3.db_finalize(db_id)
end
			


db_close

Description

Closes a database.


Syntax

rc = gh_sqlite3.db_close(
 db_id
)

Languages


Parameters


Return Values


Code sample


rc = gh_sqlite3.db_close(db_id)
			


db_column_blob_to_file

Description

Stores the content of a blob in a file.


Syntax

rc = gh_sqlite3.db_column_blob_to_file(
 db_id,
 col_index,
 filename
)

Languages


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(db_id, col_index, 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(
 db_id,
 col_index
)

Languages


Parameters


Return Values


Code sample


str = gh_sqlite3.db_column_blob_to_string(db_id, col_index)
			


db_column_get_blob

Description

Gets the size and data pointer of a blob field.


Syntax

dataptr, size = gh_sqlite3.db_column_get_blob(
 db_id,
 col_index
)

Languages


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(db_id, col_index)
			


db_column_get_double

Description

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


Syntax

rc, v = gh_sqlite3.db_column_get_double(
 db_id,
 col_index
)

Languages


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(db_id, col_index)
			


db_column_get_int

Description

Gets the integer value of a column.


Syntax

rc, v = gh_sqlite3.db_column_get_int(
 db_id,
 col_index
)

Languages


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(db_id, col_index)
			


db_column_get_text

Description

Gets the text value of a column.


Syntax

rc, text = gh_sqlite3.db_column_get_text(
 db_id,
 col_index,
 max_chars
)

Languages


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(db_id, col_index, 1024)
			


db_column_get_text1024

Description

Gets the text value of a column.


Syntax

rc, text = gh_sqlite3.db_column_get_text1024(
 db_id,
 col_index
)

Languages


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(db_id, col_index)
			


db_enable_extended_result_codes

Description

Enables or disables the extented result codes.


Syntax

gh_sqlite3.db_enable_extended_result_codes(
 db_id,
 state
)

Languages


Parameters


Return Values

This function has no return value(s).


Code sample


state = 1
gh_sqlite3.db_enable_extended_result_codes(db_id, state)
			


db_exec

Description

Executes an SQL request.


Syntax

rc = gh_sqlite3.db_exec(
 db_id,
 sql
)

Languages


Parameters


Return Values


Code sample


local SQLITE_OK = 0
local SQLITE_ERROR = 1

local sql = "CREATE TABLE users (name varchar(64), age int not null)"
local rc = gh_sqlite3.db_exec(db_id, sql)

if (rc == SQLITE_ERROR) then
    print("SQL error 1")
end
			


db_finalize

Description

Frees resources allocated by db_prepare().


Syntax

rc = gh_sqlite3.db_finalize(
 db_id
)

Languages


Parameters


Return Values


Code sample


rc = gh_sqlite3.db_finalize(db_id)
			


db_get_column_count

Description

Gets the number of columns in a result set.


Syntax

num_columns, rc = gh_sqlite3.db_get_column_count(
 db_id
)

Languages


Parameters


Return Values


Code sample


local num_columns, rc = gh_sqlite3.db_get_column_count(db_id)
			


db_get_column_name

Description

Gets the name of a column.


Syntax

name = gh_sqlite3.db_get_column_name(
 db_id,
 col_index
)

Languages


Parameters


Return Values


Code sample


name = gh_sqlite3.db_get_column_name(db_id, col_index)
			


db_get_column_type

Description

Gets the type of a column in a result set.


Syntax

type = gh_sqlite3.db_get_column_type(
 db_id,
 col_index
)

Languages


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(db_id, c)

if (type == SQLITE_TEXT) then
    local v = gh_sqlite3.db_column_get_text1024(db_id, c)
    print("Column " .. c .. " - type: TEXT - value: " .. v)
elseif (type == SQLITE_INTEGER) then
    local v = gh_sqlite3.db_column_get_int(db_id, c)
    print("Column " .. c .. " - type: INTEGER - value: " .. v)
elseif (type == SQLITE_FLOAT) then
    local v = gh_sqlite3.db_column_get_double(db_id, c)
    print("Column " .. c .. " - type: FLOAT - value: " .. v)
elseif (type == SQLITE_BLOB) then
    local size, dataptr = gh_sqlite3.db_column_get_blob(db_id, c)
    print("Column " .. c .. " - type: BLOB - size: " .. size)
end
			


db_get_errcode

Description

Gets the last error code.


Syntax

res_code = gh_sqlite3.db_get_errcode(
 db_id
)

Languages


Parameters


Return Values


Code sample


errcode = gh_sqlite3.db_get_errcode(db_id)
			


db_get_extended_errcode

Description

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


Syntax

ext_res_code = gh_sqlite3.db_get_extended_errcode(
 db_id
)

Languages


Parameters


Return Values


Code sample


errcode = gh_sqlite3.db_get_extended_errcode(db_id)
			


db_get_last_insert_rowid

Description

Gets the last insert ID.


Syntax

gh_sqlite3.db_get_last_insert_rowid(
 db_id
)

Languages


Parameters


Return Values

This function has no return value(s).


Code sample


lastrowid = gh_sqlite3.db_get_last_insert_rowid(db_id)
			


db_get_version

Description

Get the SQLite3 engine version.


Syntax

int_ver, str_ver = gh_sqlite3.db_get_version()

Languages


Parameters

This function has no input parameter(s).


Return Values


Code sample


int_ver, str_ver = gh_sqlite3.db_get_version()
			


db_open

Description

Initializes and opens a database.


Syntax

db_id, rc = gh_sqlite3.db_open(
 db_filename
)

Languages


Parameters


Return Values


Code sample


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

local db_filename = demo_dir .. "database01.db"

db_id, rc = gh_sqlite3.db_open(db_filename)
			


db_open_v2

Description

Initializes and opens a database.


Syntax

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

Languages


Parameters


Return Values


Code sample


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

local db_filename = demo_dir .. "database01.db"

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


db_prepare

Description

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


Syntax

rc = gh_sqlite3.db_prepare(
 db_id,
 sql
)

Languages


Parameters


Return Values


Code sample


local SQLITE_OK = 0
local SQLITE_ERROR = 1

local sql = "SELECT * FROM users"
local rc = gh_sqlite3.db_prepare(db_id, sql)

if (rc == SQLITE_OK) then
    local num_columns = gh_sqlite3.db_get_column_count(db_id)
    ...
end
			


db_step

Description

Evaluates a request prepared with db_prepare().


Syntax

rc = gh_sqlite3.db_step(
 db_id
)

Languages


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(db_id)
while (rc ~= SQLITE_DONE) do
    ...
end
			






GeeXLab Rootard Guide | Downloads | Contact | Newsletter