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.
demo.pdf
Description: Adobe PDF document
___________________________________________________________________________________ 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 ___________________________________________________________________________________
