lua-users home
lua-l archive

[Date Prev][Date Next][Thread Prev][Thread Next] [Date Index] [Thread Index]

Tomas Guisasola Gorham wrote:
	But the above code could be done in Lua don't you think?
If you are only creating SQL statements...

My original library was actually a layer of Lua sitting on top of LuaSQL. However, I did have to patch LuaSQL to make it support sending query parameters separately from the query string.

	I don't like to work with cursors either, neither checking every
statement if there was an error.  And of course assembling SQL statements
is boring.  But I've done all these jobs in Lua.  The result is something

local dado = require"dado" -- my database abstraction
local db = dado.connect("database", "user", "pass", "postgres")

local cond = string.format ("d > %s and e = '%s'", 1234, "abcd")
for a, b, c in db:select("a,b,c", "t", cond) do
	-- do anything you want with the results

	In this example I have to be careful when assembling the SQL
statement, but I have some facilities for the common cases like:

print (table.fullconcat ({a = 1, b = 2, c = "a'bc"}, "=", " AND ", nil,
a='1' AND c='a\'bc' AND b='2'

	What I'm really looking for is a uniform way to do Prepared
Statements in LuaSQL, which I think would really be a step further.
Have you thought about that?

Well, every database is slightly different when it comes to prepared statements... As far as I know, anyway.

As far as Postgres is concerned, prepared statements would be a fairly simple addition to my library. I have not fully thought out the semantics but I sort of imagined something like this:

local stmt = conn:statement"SELECT ..."
local prep = stmt:prepare"name"

Then you can start using the prepared statement directly, or create a new object latter by referencing the name:

local prep = conn:prepare"name"
prep:setInteger(1, 100)
prep:setString(2, "woenwe")
local res, count = prep:selectResult()

I am only really familiar with the Postgres client API, but I will just go ahead and guess that this probably won't work for most other databases. That is the main reason why I left this out, for now.