用luasql.postgres编写的报表

Question

我试图遵循在luasql中使用准备好的语句的示例：http://lists.luaforge.net/pipermail/kepler-project/2008-January/002207.html

我已经安装了luasql版本2.2.0。

当我尝试示例代码时，我会得到以下错误消息：

lua: postgrestest.lua:63:尝试调用方法‘gettype’(一个零值)堆栈回溯: postgrestest.lua:63:在主块C：？

下面是代码：

require "luasql.postgres"
local databasename = "test"
local databaseUser = "user"
local databasepassword = "password"
local databaseserver="10.10.10.10" -- only rqd for remote database servers.
--local databaseserver="127.0.0.1"
local databaseport = 5432 -- only rqd for remote database servers.

env = assert (luasql.postgres())
con = assert (env:connect(databasename, databaseUser, databasepassword, databaseserver))

-- DROP ANY EXISTING PEOPLE TABLE
-- note:  con:execute() Returns: a cursor object if there are results, or the number of rows affected by the command otherwise.
res = con:execute("DROP TABLE people")

-- RECREATE PEOPLE TABLE
res = assert (con:execute[[
    CREATE TABLE people(
        id integer,
        fname text,
        lname text,
        job text
    )
]])
print ('result from create table is:'..res)
-- ADD SOME PEOPLE TO THE PEOPLE TABLE
res = assert(con:execute("INSERT INTO people " ..
    "VALUES (1, 'Roberto', 'Ierusalimschy', 'Programmer')"), "record inserted")
print ("result from insert"..res)

res = assert(con:execute("INSERT INTO people " ..
    "VALUES (3, 'Taylor', 'Swift', 'Singer')"))

-- RETRIEVE THE PEOPLE TABLE SORTED BY LAST NAME INTO CURSOR
cur = assert (con:execute"SELECT * from people order by lname")

-- LOOP THROUGH THE CURSOR AND PRINT
print()
print(string.format("%15s  %-15s %-15s %-15s",
    "#", "FNAME", "LNAME", "JOB"))
print(string.format("%15s  %-15s %-15s %-15s",
    "-", "-----", "-----", "---"))
row = cur:fetch ({}, "a")
while row do
    print(string.format("%15d  %-15s %-15s %-15s",
        row.id, row.fname, row.lname, row.job))
    row = cur:fetch (row, "a")
end
print()

-- prepared statements 
types = con:gettypes()
stmt, err = con:prepare("select * from people where lname=$1", types["text"])
if err then
    print("problems creating prepared statement"..err)
else
    cur, err = stmt:execute("Swift")
  if err then
        print("Couldn't execute the statement: "..err);
    else
        results = cur:fetch({}, "a")
        while results do
            print(results.id)
        end
    end
end 
-- CLOSE EVERYTHING
cur:close()
con:close()
env:close()

我还尝试将准备语句改为如下所示：

--types = con:gettypes()
--stmt, err = con:prepare("select * from people where lname=$1", types["text"])
stmt, err = con:prepare("select * from people where lname=$1", "Swift")
if err then
    print("problems creating prepared statement"..err)
else
    --cur, err = stmt:execute("Swift")
    cur, err = stmt:execute()
  if err then
        print("Couldn't execute the statement: "..err);

但是，除了准备方法之外，我还得到了相同的错误消息：

lua: postgrestest.lua:65: attempt to call method 'prepare' (a nil value)
stack traceback:
        postgrestest.lua:65: in main chunk
        [C]: ?

你能告诉我我做错了什么吗?如何解决这个问题？谢谢!

编辑1

到数据库和所有其他代码的连接可以工作。循环遍历表并打印数据的代码可以工作。

谢谢。

Answer 1

正如注释中所指出的，您需要应用一个显然没有的修补程序。但是，如果需要在支持它们的语言框架之外使用已准备好的语句，则可以手动使用PostgreSQL的服务器端准备语句。

基本上，您在SQL中声明存储过程，然后像函数一样在SQL中调用它。准备好的语句有点像临时的SQL语言函数。因此，对于任何SQL接口，仍然可以执行如下操作：

PREPARE my_insert(int, int, text, text) AS
INSERT INTO foo(id, bar, baz, test)
values ($1, $2, $3, $4);

然后您可以重复调用它(使用相同的计划)：

EXECUTE my_insert(1, 3, 'foo', 'bar');

这允许您执行这样的准备语句，即使您的客户端框架不支持它们。