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
___________________________________________________________________________________