# Can't use SQL to format date in LuaLaTeX with LuaSQL & passing parameters to compiler

I am working on a LuaLaTeX example where date has to be transferred from my database using Lua and then compiled using LuaLaTeX. I am using LuaSQL to do all this and this is the minimal working example:

predracun.lua:

function skupina_pricetek ()
package.cpath = package.cpath .. ";/usr/lib/lua/5.3/luasql/mysql.so"
luasql = require('luasql.mysql')
env = assert (luasql.mysql())
con = assert (env:connect("my_database","root","my_password"))

cur = assert (con:execute("SELECT pricetek FROM skupine WHERE id_skupine = (SELECT id_skupine FROM predracuni WHERE id_interesa = 1);"))

vnos = cur:fetch ({}, "a")

tex.print(
string.format([[%s]], vnos.pricetek)
)

end


predracun.tex:

\documentclass[12pt]{article}

%package for Lua support
\usepackage{luacode}
\directlua{dofile('predracun.lua')}

\begin{document}

\end{document}


Now this works flawlessly:

It works until I try to format date by replacing SELECT pricetek in predracun.lua with any of the lines below:

SELECT DATE_FORMAT(pricetek,'%d. %m. %Y')
SELECT Concat(DAY(pricetek),'. ',MONTH(pricetek),'. ',YEAR(pricetek))


If I do this, I get:

It looks like LuaSQL doesn't fully support SQL... Or am I missing some escape characters etc.? Note that described modifications work flawlessly if executed directly in database.

Does anyone know, how I can pass parameters 1, 2, 3... in lualatex predracun.tex command. I need this to change id_interesa = 1 to id_interesa = 2 or id_interesa = 3... for each custommer...

• When you select an expression you lose the column name. Try select ... as pricetek .... – Sergei Golovan Sep 11 '17 at 18:25
• So you mean I should use SQL labels to assign "column" name which should be the same as name of the date - that's pricetek in both cases. – 71GA Sep 11 '17 at 18:30
• Or some other name and use it instead of vnos.pricetek. – Sergei Golovan Sep 11 '17 at 18:32
• @SergeiGolovan this SELECT DATE_FORMAT(pricetek,'%d. %m. %Y') AS pricetek works! And so does this SELECT Concat(DAY(pricetek),'. ',MONTH(pricetek),'. ',YEAR(pricetek)) AS pricetek. I have to point out that this isn't the default SQL behaviour which works also without labels. Does anyone know the answer how to pass the parameters to Lua using LuaLaTeX (last part of the question)? – 71GA Sep 11 '17 at 18:34
• @SergeiGolovan Do you want to write up an answer? – TeXnician Sep 26 '18 at 17:44