< 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

gh_sqlite3.db_bind_blob_from_buffer ()
gh_sqlite3.db_bind_blob_from_file ()
gh_sqlite3.db_bind_blob_from_string ()
gh_sqlite3.db_bind_double ()
gh_sqlite3.db_bind_int ()
gh_sqlite3.db_bind_text ()
gh_sqlite3.db_close ()
gh_sqlite3.db_column_blob_to_file ()
gh_sqlite3.db_column_blob_to_string ()
gh_sqlite3.db_column_get_blob ()
gh_sqlite3.db_column_get_double ()
gh_sqlite3.db_column_get_int ()
gh_sqlite3.db_column_get_text ()
gh_sqlite3.db_column_get_text1024 ()
gh_sqlite3.db_enable_extended_result_codes ()
gh_sqlite3.db_exec ()
gh_sqlite3.db_finalize ()
gh_sqlite3.db_get_column_count ()
gh_sqlite3.db_get_column_name ()
gh_sqlite3.db_get_column_type ()
gh_sqlite3.db_get_errcode ()
gh_sqlite3.db_get_extended_errcode ()
gh_sqlite3.db_get_last_insert_rowid ()
gh_sqlite3.db_get_version ()
gh_sqlite3.db_open ()
gh_sqlite3.db_open_v2 ()
gh_sqlite3.db_prepare ()
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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column
buff_ptr [POINTER]: pointer to the buffer
buff_size [INTEGER]: size of the buffer in bytes

Return Values

rc [ENUM]: return code

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

db_id [ID]: database identifier
column [INTEGER]: index of the column
filename [STRING]: absolute path of the file

Return Values

rc [ENUM]: return code

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column
str [STRING]: string to be stored in the blob

Return Values

rc [ENUM]: return code

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

rc [ENUM]: return code
x [REAL]: value

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

rc [ENUM]: return code
x [INTEGER]: value

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

rc [ENUM]: return code
text [STRING]: text

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

db_id [ID]: database identifier

Return Values

rc [ENUM]: return code

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column
filename [STRING]: absolute path of the destination file

Return Values

rc [ENUM]: return code

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

str [STRING]: output string

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

dataptr [INTEGER]: light user data representing the blob data
size [INTEGER]: size of the blob data

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

rc [ENUM]: return code
v [INTEGER]: column value

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

rc [ENUM]: return code
v [INTEGER]: column value

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column
max_chars [INTEGER]: max number of chars your want to retrieve

Return Values

rc [ENUM]: return code
text [STRING]: column value

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

rc [ENUM]: return code
text [STRING]: column value

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

db_id [ID]: database identifier
state [BOOLEAN]: extended result codes: 1 (enabled) or 0 (disabled)

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

db_id [ID]: database identifier
sql [STRING]: SQL query

Return Values

rc [ENUM]: return code

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

db_id [ID]: database identifier

Return Values

rc [ENUM]: return code

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

db_id [ID]: database identifier

Return Values

num_columns [INTEGER]: number of columns
rc [ENUM]: return code

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

name [STRING]: column name

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

db_id [ID]: database identifier
col_index [INTEGER]: index of the column

Return Values

type [ENUM]: type of a column

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

db_id [ID]: database identifier

Return Values

res_code [ENUM]: primary result code

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

db_id [ID]: database identifier

Return Values

ext_res_code [ENUM]: extended result code

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

db_id [ID]: database identifier

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

int_ver [INTEGER]: numeric format of the version
str_ver [STRING]: alphanumeric format of the version

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

db_filename [STRING]: absolute path of the database file

Return Values

db_id [ID]: database identifier
rc [ENUM]: return code

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

db_filename [STRING]: absolute path of the database file
flags [ENUM]: additional flags that control the way the database is opened

Return Values

db_id [ID]: database identifier
rc [ENUM]: return code

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

db_id [ID]: database identifier
sql [STRING]: SQL query

Return Values

rc [ENUM]: return code

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

db_id [ID]: database identifier

Return Values

rc [ENUM]: return code

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