Connection

Lib.SQLight.Connection.new(file)	It will create SQLite connection object	file - SQLight database file location. For complete list of file options check SQLite documentation
blobOpen(table_name, blob_field_name, row_id)	open SQLite blob field in DB for read and write bytes operations by specified table name, blob column name in this table and unique row id. Returns SqliteBlob object.	table_name - table with blob field blob_field_name - blob field column name row_id - unique record id
interrupt()	This function causes any pending database operation to abort and return at its earliest opportunity. This routine is typically called in response to a user action such as pressing "Cancel" or Ctrl-C where the user wants a long query operation to halt immediately.
request(query, query_parameters)	perform query request to DB with parameters. Use nil for empty parameters table. Can return ResultSet object with DB rows access.	query - project path to DB query .sql file or query text with '?' as parameter placeholder query_parameters - table of query parameters corresponding to each '?' placeholder in order of appearance. Parameters types can be: - nil (NULL in DB) - integer - number/float - string - BytesData - special object with property: zeroBlobSize. {zeroBlobSize = requested_blob_field_size_int} This value will initialize BLOB field with zeros values and requested size.
close()	close DB connection and any opened associated with this connection BLOB field connections (SqliteBlobs). From this moment requests to DB not available.

Examples:

Example 1

local connection = Lib.SQLight.Connection.new(":memory:")

--also request can load query from .sql file: connection.request("/folder/file.sql", nil)

connection.request([[CREATE TABLE IF NOT EXISTS Test (

field1 INTEGER PRIMARY KEY NOT NULL,

field2 TEXT,

field3 BLOB

)]], nil);

connection.request ("INSERT INTO Test (field1, field2) VALUES (?, ?)", {1, "test1"})

connection.request ("INSERT INTO Test (field1, field2) VALUES (?, ?)", {2, "test2"})

resultSet = connection.request ("SELECT field1, field2 FROM Test", nil)

while resultSet.hasNext() do

row = resultSet.next()

print(row.field1.." "..row.field2)

end

--direct blob update/select (use for small size data)

imageBytes = Lib.Project.getBytes("/Bunnymark/assets/wabbit_alpha.png")

connection.request ("UPDATE Test SET field3 = ? WHERE field1=?", {imageBytes.getData(), 1})

resultSet = connection.request ("SELECT field3 FROM Test WHERE field1=?", {1})

while resultSet.hasNext() do

row = resultSet.next()

imageBlobBytes2 = Lib.Sys.IO.Bytes.ofData(row.field3)

end

--BLOB field should be not NULL or it will crash with error "cannot open value of type null"

--also we need to pre allocate blob field size before we can write image in chunks

imageBytes = Lib.Project.getBytes("/Bunnymark/assets/pirate.png")

print("Expected image size: "..imageBytes.length)

blobInfo = {zeroBlobSize = imageBytes.length} --zeroBlobSize - reserved object field name for blob field size initialization

connection.request ("UPDATE Test SET field3 = ? WHERE field1=?", {blobInfo, 2}) --pre allocate size

blob = connection.blobOpen("Test", "field3", 2)

print("BLOB size: "..blob.size())

--write

pos = 0

chunkSize = 100 --100 bytes limit

while pos < imageBytes.length do

if pos + chunkSize > imageBytes.length then chunkSize = imageBytes.length - pos end

blob.write(imageBytes, chunkSize, pos, pos)

pos = pos + chunkSize

end

--read

pos = 0

chunkSize = 100 --100 bytes limit

imageBlobBytes = Lib.Sys.IO.Bytes.alloc(imageBytes.length)

while pos < imageBytes.length do

if pos + chunkSize > imageBytes.length then chunkSize = imageBytes.length - pos end

blob.read(imageBlobBytes, chunkSize, pos, pos)

pos = pos + chunkSize

end

blob.close()

connection.close()

--Display

Display = Lib.Media.Display

stage = Display.stage

BitmapData = Display.BitmapData

Bitmap = Display.Bitmap

data = BitmapData.loadFromBytes(Lib.Media.Utils.ByteArray.fromBytes(imageBlobBytes), nil)

bmp = Bitmap.new(data, Display.PixelSnapping.AUTO, false)

bmp.x = 50

bmp.y = 50

stage.addChild(bmp)

data = BitmapData.loadFromBytes(Lib.Media.Utils.ByteArray.fromBytes(imageBlobBytes2), nil)

bmp = Bitmap.new(data, Display.PixelSnapping.AUTO, false)

bmp.x = 10

bmp.y = 10

stage.addChild(bmp)

Example 2

-- https://sqlite.org/c3ref/interrupt.html

Thread = Lib.Sys.VM.Thread

Text = Lib.Media.Text

Capabilities = Lib.Media.Capabilities

stage = Lib.Media.Display.stage

connection = Lib.SQLight.Connection.new(":memory:")

stage.scaleX = Capabilities.screenDPI > 120 and Capabilities.screenDPI / 120 or 1

stage.scaleY = stage.scaleX

local fmt = Text.TextFormat.new('_sans', 16, 0x000000, nil, nil, nil)

fmt.align = Text.TextFormatAlign.CENTER

fmt.leftMargin = 4

fmt.rightMargin = 4

fmt.leading = 15

local text = Text.TextField.new()

text.defaultTextFormat = fmt

text.autoSize = Text.TextFieldAutoSize.LEFT

text.x = 10

text.y = 10

text.border = true

text.selectable = false

text.multiline = true

text.wordWrap = false

text.htmlText = [[Click on text link to interrupt query:

<u><font color='#0000FF'><a href='event:myEvent'>INTERRUPT</a></font></u>]]

text.addEventListener(Lib.Media.Events.TextEvent.LINK, function(e)

print("Interrupting ...")

connection.interrupt()

end, false, 0, false)

stage.addChild(text)

t = Thread.create([[--thread

Thread = Lib.Sys.VM.Thread

local connection = Thread.readMessage(true)

--endless query

local status, message = pcall(connection.request, "WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c) SELECT x FROM c WHERE x<0", nil)

connection.close()

print("thread done! query status: ["..(status and "ok" or "error").."] message: ["..message.."]")

]])

t.sendMessage(connection)

Created with the Personal Edition of HelpNDoc: Easily create EPub books