Re: SQL, Sqlite3 and ConTeXt LMTX

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
___________________________________________________________________________________