Hi Lutz,
It seems that there is something hapening with passing the query string to the
sqlite3 library.
I made it to work on Windows by doubling the % in format string.
You could try to use printf() function instead of format().
Also, this round(tgeld, 2) will be handled by printf('%.2f')
So:
template = "select name,replace(printf('%.2f',round(tgeld,2)),'.',',') AS
Taschengeld, replace(printf('%.2f',round(tgeld*52,2)),'.',',') AS Jahresgeld
from test ;",
should be
template = "SELECT name, replace(printf('%%.2f',tgeld),'.',',') AS Taschengeld,
replace(printf('%%.2f',tgeld*52),'.',',') AS Jahresgeld FROM test ;",
and then it works as expected (see the attached output).
Best regards,
Ivan
________________________________
Од: Lutz Haseloff
Послато: среда, мај 13, 2026 10:35
За: Context Mailing List
Тема: Re: [NTG-context] SQL, Sqlite3 and ConTeXt LMTX
Hi Hans, hi all,
I wish to use Sqlite from ConTeXt LMTX.
The demo-sql.tex in the sources works well.
I shortened it to schow my problem with queries.
(I hope, I didn't delete relevant parts.)
My minimal testfile:
________________________________
\registerctxluafile{libs-imp-sqlite}{autosuffix}
\enabletrackers[sql*]
\enabletrackers[*lib*]
\starttext
\startluacode
os.remove("test.db") -- start fresh
local sql = require("util-sql")
local method = "sqlite"
sql.setmethod(method)
document.sqlpresets = {
database = "test",
}
local sqlite_template = [[
CREATE TABLE IF NOT EXISTS test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
tgeld REAL NOT NULL
)
]]
local results = utilities.sql.execute {
presets = document.sqlpresets,
template = sql.getmethod() == "sqlite" and sqlite_template or
common_template,
}
\stopluacode
\startluacode
local template = [[
INSERT INTO test (
name,
tgeld
) VALUES (
'%[name]%',
'%[tgeld]%'
) ;
]]
local function add(t)
local results = utilities.sql.execute {
presets = document.sqlpresets,
template = template,
variables = {
name = t.name,
tgeld = t.tgeld,
},
}
if results then
-- inspect(results)
end
end
add { name = "Lutz Haseloff", tgeld = 10.0 }
\stopluacode
\setupbodyfont[dejavu]
\setuppapersize[S6]
\startluacode
context.starttitle { title = "Wie viel?" }
local results = utilities.sql.execute {
presets = document.sqlpresets,
template = "select name,
replace(format('%.2f',round(tgeld,2)),'.',',') AS Taschengeld from test ;",
}
context.starttabulate { "||r|" }
context.HL()
context.NC() context.bold("Name")
context.NC() context.bold("Taschengeld (€)")
context.NC() context.NR()
context.HL()
for i=1,#results do
local result = results[i]
context.NC() context(result.name)
context.NC() context(result.Taschengeld)
context.NC() context.NR()
end
context.HL()
context.stoptabulate()
context.stoptitle()
\stopluacode
\startluacode
local results = utilities.sql.execute {
presets = document.sqlpresets,
template = "select name,replace(format('%.2f',round(tgeld,2)),'.',',')
AS Taschengeld, replace(format('%.2f',round(tgeld*52,2)),'.',',') AS Jahresgeld
from test ;",
}
context.starttabulate { "||r|r|" }
context.HL()
context.NC() context.bold("Name")
context.NC() context.bold("Taschengeld (€)")
context.NC() context.bold("Taschengeld im Jahr (€)")
context.NC() context.NR()
context.HL()
for i=1,#results do
local result = results[i]
context.NC() context(result.name)
context.NC() context(result.Taschengeld)
context.NC() context(result.Jahresgeld)
context.NC() context.NR()
end
context.HL()
context.stoptabulate()
\stopluacode
\stoptext
________________________________
The resulting pdf is appended.
In the logs I find following two queries:
sqlite > executing sqlite
sql > query from template: select name,
replace(format('%.2f',round(tgeld,2)),'.',',') AS Taschengeld from test ;
sqlite > executing sqlite
sql > query from template: select
name,replace(format('.2f',round(tgeld*52,2)),'.',',') AS Jahresgeld from test ;
The first is correct and works fine.
The second is shortened and the format function is changed.
What is my error here?
Greetings Lutz
P.S. Unfortunately Hrabans excellent book doesn't help in this case.
sql.pdf
Description: sql.pdf
___________________________________________________________________________________ If your question is of interest to others as well, please add an entry to the Wiki! maillist : [email protected] / https://mailman.ntg.nl/mailman3/lists/ntg-context.ntg.nl webpage : https://www.pragma-ade.nl / https://context.aanhet.net (mirror) archive : https://github.com/contextgarden/context wiki : https://wiki.contextgarden.net ___________________________________________________________________________________
