Variables and SQLITE3 plugin

[kernal]

Hi,

I am trying to find the proper syntax for inserting data into a sqlite3 database, from within Hollywood, where the data I want to insert is contained in variables. I can't seem to find the syntax that will work anywhere.

i.e.
db:exec([[ INSERT INTO profiles (name, address, phone) VALUES(n, a, p); ]])

n, a, & p are variables. Obviously the above doesn't work, but what is the syntax that will?

[airsoftsoftwair]

Something like this?

Code: Select all

n = "Happy"
a = "New"
p = "Year"
cmd$="INSERT INTO profiles (name, address, phone) VALUES('"..n.."', '"..a.."', '"..p.."');"
db:exec(cmd$)

[kernal]

Thanks for reply... looked promising, but it just treats them as strings, so the literal values n a and p are now inserted....

[kernal]

I take that back... it works, I had some quotes mixed up the first time.

Thanks a bunch.....

[lazi]

You need something like this:

Code: Select all

stmt=db:prepare([[ INSERT INTO dokumentumok VALUES (NULL,:dok_type, :dok_sp, :dok_cs, :dok_tr, :dok_es, :dok_info, :dok_file)]])
stmt:bind_names({dok_type=0, dok_sp=0, dok_cs=0, dok_tr=0, dok_es=data.es_id, dok_info=FilePart(data.filename), dok_file=data.filename})

stmt:dostep()
stmt:reset()     

new_row =db:last_insert_rowid   

db:prepare(sql)
This function compiles the SQL statement in string sql into an internal representation and returns this as userdata. The returned object should be used for all further method calls in connection with this specific SQL statement (see Methods for prepared statements).

stmt:bind_names(nametable)
Binds the values in nametable to statement parameters. If the statement parameters are named (i.e., of the form ":AAA" or "$AAA") then this function looks for appropriately named fields in nametable; if the statement parameters are not named, it looks for numerical fields 1 to the number of statement parameters. The function returns sqlite3.OK on success or else a numerical error code (see Numerical error and result codes).

stmt:step() (stmt:dostep() here)
This function must be called to evaluate the (next iteration of the) prepared statement stmt. It will return one of the following values:

sqlite3.BUSY: the engine was unable to acquire the locks needed. If the statement is a COMMIT or occurs outside of an explicit transaction, then you can retry the statement. If the statement is not a COMMIT and occurs within a explicit transaction then you should rollback the transaction before continuing.

sqlite3.DONE: the statement has finished executing successfully. stmt:step() should not be called again on this statement without first calling stmt:reset() to reset the virtual machine back to the initial state.

sqlite3.ROW: this is returned each time a new row of data is ready for processing by the caller. The values may be accessed using the column access functions. stmt:step() can be called again to retrieve the next row of data.

sqlite3.ERROR: a run-time error (such as a constraint violation) has occurred. stmt:step() should not be called again. More information may be found by calling db:errmsg(). A more specific error code (can be obtained by calling stmt:reset().

sqlite3.MISUSE: the function was called inappropriately, perhaps because the statement has already been finalized or a previous call to stmt:step() has returned sqlite3.ERROR or sqlite3.DONE.

stmt:reset()
This function resets SQL statement stmt, so that it is ready to be re-executed. Any statement variables that had values bound to them using the stmt:bind*() functions retain their values.


More is here: http://lua.sqlite.org/index.cgi/doc/tip ... lite3.wiki