Add a text as variable in a SQL query

Add a text as variable in a SQL query - Printable Version

+- Logic Machine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: Add a text as variable in a SQL query (/showthread.php?tid=5469)

Add a text as variable in a SQL query - kike - 18.06.2024

Hi everyone,

I have problemas adding a string variable into a query.

This works fine;
---------------------------------------------------------------------------------------------------------
require('luasql.mysql')
require('json')
http = require('socket.http')
env = luasql.mysql()

dbcon, err = env:connect('knx', 'root','123123', '192.168.6.19', '3306')
log(dbcon, err)

dbcon:execute('INSERT INTO test (name, value) VALUES ("Name1", "11")')
dbcon:execute('INSERT INTO test (name, value) VALUES ("Name2", "22")')
---------------------------------------------------------------------------------------------------------

but i'm struggling with no success when I try to add both fields as local variables, for example

---------------------------------------------------------------------------------------------------------
require('luasql.mysql')
require('json')
http = require('socket.http')
env = luasql.mysql()

dbcon, err = env:connect('knx', 'root','123123', '192.168.6.19', '3306')
log(dbcon, err)

local nameparam = 'NameX'
local valueparam = 1000

¿¿¿???????
---------------------------------------------------------------------------------------------------------

I can't find the right string parametrization or contatenarion to create the right query

something is tricking me with the ' and " that I cant solve

Any help is wellcome!!

Thanks!!

Kike

RE: Add a text as variable in a SQL query - admin - 18.06.2024

Try this:

Code:
nameparam = dbcon:escape('NameX')

valueparam = 1000

dbcon:execute("INSERT INTO test (name, value) VALUES ('" .. nameparam .. "', " .. valueparam .. ")")

RE: Add a text as variable in a SQL query - kike - 18.06.2024

(18.06.2024, 12:16)admin Wrote: Try this:

Code:
nameparam = dbcon:escape('NameX') valueparam = 1000 dbcon:execute("INSERT INTO test (name, value) VALUES ('" .. nameparam .. "', " .. valueparam .. ")")

it Works!! Thanks!!

RE: Add a text as variable in a SQL query - kike - 19.06.2024

(18.06.2024, 12:16)admin Wrote: Try this:

Code:
nameparam = dbcon:escape('NameX') valueparam = 1000 dbcon:execute("INSERT INTO test (name, value) VALUES ('" .. nameparam .. "', " .. valueparam .. ")")

Hi again, I'm sorry for bothering again, but I dont understand how the " and single ' works formating the text.

I need to add more text fields, and I tried to copy or understand the format but the query responds with:

-----------------------------------
string: LuaSQL: error executing query. MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'kWh Calor', kike', 1000)
-----------------------------------

and the script:

-------------------------------------------------------------------------
require('luasql.mysql')
require('json')
http = require('socket.http')
env = luasql.mysql()

dbcon, err = env:connect('knx', 'root','123123', '192.168.6.19', '3306')
log(dbcon, err)

dbcon:execute('SET NAMES utf8')

instalacion_param = dbcon:escape('Building_1')
medida_param = dbcon:escape('P1_2A kWh Calor')
usuario_param = dbcon:escape('kike')
valor_param = 1000

log(dbcon:execute("INSERT INTO test2 (instalacion, medida, usuario, valor) VALUES ('" .. instalacion_param .. "', " .. medida_param .. "', " .. usuario_param .. "', " .. valor_param .. ")"))
-------------------------------------------------------------------------

What's the difference beetween " and ' dealing with test format in this case? Is there any manual or tip abouts this issue?

Thanks in advance!!

RE: Add a text as variable in a SQL query - admin - 19.06.2024

You are missing a single quote before " .. medida_param

You can use Lua multi-line string syntax to make it code more readable:

Code:
query = [[

  INSERT INTO test2 (instalacion, medida, usuario, valor)

  VALUES (

    ']] .. instalacion_param .. [[',

    ']] .. medida_param .. [[',

    ']] .. usuario_param .. [[', 

    ]] .. valor_param .. [[

  )

]]

log(dbcon:execute(query))

Lua strings can be wrapped in single or double quotes. But MySQL expects parameters to be wrapped in single quotes.

RE: Add a text as variable in a SQL query - kike - 19.06.2024

works perfect!! This way I cand ajust the number and type of variable to my needs!!

Thanks!! thats what I needed