Retrieving SQL records via Lua

[Smeg]

Hi,

I've created a new table within CivBE and now what I am trying to do is retrieve whatever records are in the newly created table via Lua.

I have looked at various tutorials but I cannot seem to get it to work.

Spoiler :

function Test ()

local db = sqlite("CivBEDebugDatabase.db", "r")

local query = [[
select name, email, phone from person
where name like :1;
]]

for name, email, phone in db:records(query, { 'a%' }) do
print(name, email, phone)
end
end

Now this is a simple tutorial code that I'm trying to get working so that I understand the basics. If I try and do a Test() - I get "Attempt to index a nil value"

I'm obviously missing something major here, can anyone assist or point me in the right direction? Thanks.

 

[Bosparan]

Hi Smeg,

try this:

local query = "select name, email, phone from person where name like ':1'";
for entry in DB.Query(query) do
    print((entry.name)..(entry.email)..(entry.phone));
end

DB is the static Database object you use to access the Civ Database (and since Query is a static method, it's only a single dot (".") between DB and Query).

Cheers,
Bosparan

 

[Smeg]

Hi Bosparan,

Thanks for this. The code you posted looks to execute without error (unlike previously), however it doesn't display any values. I was expecting to see this in the Live Tuner so that I know it's been executed.

I added a print "test"; within the function so I know that it's being called, but the actual query data isn't being displayed.

Any ideas?

Thanks,
Smeg

 

[Bosparan]

Hi Smeg,

my best guess is a rotten WHERE condition. If that one craps on you, you won't get any returns, but the syntax is correct, so it won't throw errors.
Try an equals (=) on a value you know is in there to verify this.

Cheers,
Bosparan

 

[Smeg]

Spot on Mr Bosparan!

Thank you very much for your assistance on this. Have a great day.