Hi Иван, thank you very much for your help. My code works now.
I should read the Documentation mor carefully. On page 2 in sql-mkiv.pdf: In the next section we will see how %artist% can be replaced by a more meaningful value. You can get a percent sign by entering two in a row: %%. The function printf() in sqlite is the deprecated alias for the function format(). The round() function is needed because not all numbers end with .00. Enter 10.456 for the 10.0 and you see what I want to achieve. Thanks again Lutz Am 14. Mai 2026 14:58:49 MESZ schrieb "Иван Пешић" <[email protected]>: >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.
___________________________________________________________________________________ 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 ___________________________________________________________________________________
