SQLite3: How to Create a Simple SQL Database with GeeXLab

The support of the SQLite3 has been added in GeeXLab 0.22+. The SQLite3 database engine is embedded in the plugin_gxc_sqlite3_{x32|x64}.{dll|so|dylib} plugin. The SQLite3 scripting API is available using the gh_sqlite3 library (Lua and Python). At the time of writing, the SQLite3 plugin is only available for GeeXLab win64 (plugin_gxc_sqlite3_x64.dll). The plugin will be available for all platforms later.

Let’s see how to use these new functions in Lua to create a very simple database. This database is stored in a file (database01.db) and has a single table (users) with two columns (name and age). We will use SQL queries to send commands to the database engine.

First, let’s define some constants:

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

local SQLITE_INTEGER = 1
local SQLITE_FLOAT = 2
local SQLITE_TEXT = 3

 
Now let’s create the database:

local demo_dir = gh_utils.get_demo_dir()
local db_filename = demo_dir .. "database01.db"
local rc = SQLITE_OK
dbid, rc = gh_sqlite3.db_open(db_filename)

 
dbid is the database identifier we will use in the other SQLite3 functions.

Now we have a database. Let’s create the users table. Before, we drop the table if it already exists in the database:

local sql = "DROP TABLE users"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 1")
end  

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

 
Now that the users table is created, we can populate it with some data:

sql = "INSERT INTO users (name, age) VALUES ('toto', 12);"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 2")
end

sql = "INSERT INTO users (name, age) VALUES ('John', 53);"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 2")
end

sql = "INSERT INTO users (name, age) VALUES ('Viktor', 48);"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 2")
end

sql = "INSERT INTO users (name, age) VALUES ('Duke', 85);"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 2")
end

 
Now we have a database with a table that contains some data. Let’s read the data with a SELECT query:

local sql = "SELECT * FROM users"
if (gh_sqlite3.db_prepare(dbid, sql) == SQLITE_OK) then
  local num_columns = gh_sqlite3.db_get_column_count(dbid)
  print("# columns: " .. num_columns)
  
  local row = 1
  rc = gh_sqlite3.db_step(dbid)
  while (rc ~= SQLITE_DONE) do
  
    if (rc == SQLITE_ROW) then
      for c=0, num_columns-1 do

        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("Col. " .. c .. " | TEXT | " .. v)
        elseif  (type == SQLITE_INTEGER) then
          local v = gh_sqlite3.db_column_get_int(dbid, c)
          print("Col. " .. c .. " | INT | " .. v)
        elseif  (type == SQLITE_FLOAT) then
          local v = gh_sqlite3.db_column_get_double(dbid, c)
          print("Col. " .. c .. " | REAL | " .. v)
        end
      end
    end
    
    -- next result.
    rc = gh_sqlite3.db_step(dbid)
    
    if ((rc == SQLITE_ERROR) or (rc == SQLITE_DONE)) then
      break
    end
  end
  
  gh_sqlite3.db_finalize(dbid)
end

When you no longer need the database, you can close it with:

gh_sqlite3.db_close(dbid)    

 
The complete demo is available HERE as well as in the full code sample pack in the gl-32/sqlite3/ folder.

A thread is available on GeeXLab forum HERE.