CODEWITHSUNDEEP
mysqlsqlitelualuasql
prix
prix

Reputation: 101

Communication to DB-File with Luasql and sqlite3 is not working

I'm trying to communicate to my database using luasql and sqlite3. I've read myself through many sites and found some examples. But there were many different ways that seem to work to use luasql. But for me it seems none of them is working... I'm really frustrated and don't know what to do. The following code snippet doesn't work for me as I'm not able to select anything from the table

local env = assert(luasql.sqlite()) -- Create a database environment object
local conn = assert(env:connect("cloud.db")) -- Open a database file
conn:execute(string.format([[INSERT INTO HEAVEN VALUES ('%s', '%s', '%s', '0', '0', '%s')]], id, name, tid, date))
cur = conn:execute(string.format([[SELECT TID FROM HEAVEN]]))
devtid = cur.fetch({}, "n")

When I use quotes (") instead of the 2 square-Brakets ([[ ]]) or add other quotes as shown in the following code snippet it doesn't work for me

conn:execute(string.format("\"INSERT INTO HEAVEN VALUES ('%s', '%s', '%s', '0', '0', '%s')\"", id, name, tid, date))

Or even when I'm using 

conn:execute("INSERT OR REPLACE INTO HEAVEN VALUES (0,0,0,0,0,0)")

without inserting some handed over values and formatting the string it also won't work...

I really don't know what to do. Is there someone who could help me?

Edit: I tried your variant @kingJulien and saved it in a variable to print the command before being executed:

local query = string.format([[INSERT INTO HEAVEN(ID, NAME, TID) VALUES (]] .. tonumber(id) .. [[,"]] .. name .. [[","]] .. tid .. [[");]])
trace(query)
conn:execute(query)

and the value of the variable is:

INSERT INTO HEAVEN(ID, NAME, TID) VALUES (1,"ESP-HEAVEN","1421856859");

so this command should be executed. When I use that command in putty with sqlite3 its working fine. For real I don't understand why this is not working....

Upvotes: 0

Views: 173

Answers (1)

kingJulian
kingJulian

Reputation: 6170

In luasql when you want to execute the query you need to use the brackets [[ ]] and the .. to specify values. So your code should be written as:

[[INSERT INTO HEAVEN (id, name, tid, date) ]] .. [[ VALUES (]] .. id_param ..[[,]] .. name_param .. [[,]] ..tid_param.. [[,]] ..date_param .. [[);]]

where the id_param, name_param, tid_param, date_param are the variables that hold the values that you want to insert into the HEAVEN table.

Also take into consideration that you may need to wrap your variables into tostring() or tonumber() functions depending on the type of your db fields.

Upvotes: 0

Related Questions