Add a text as variable in a SQL query

[kike]

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

[admin]

Try this:

Code:

nameparam = dbcon:escape('NameX')
valueparam = 1000

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

[kike]

Try this:

Code:

nameparam = dbcon:escape('NameX')
valueparam = 1000

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

it Works!! Thanks!!

[kike]

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!!

[admin]

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.

[kike]

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

Thanks!! thats what I needed