Gammon Forum : MUSHclient : Lua : Unexpected LuaSQLite3 behaviour

Log on

Gammon Forum

Entire forum

MUSHclient

Lua

Unexpected LuaSQLite3 behaviour

Unexpected LuaSQLite3 behaviour

It is now over 60 days since the last post. This thread is closed. Refresh page

Posted by Rollanz (26 posts) bio

Date

Tue 01 Jun 2021 04:02 AM (UTC)

Amended on Tue 01 Jun 2021 04:15 AM (UTC) by Rollanz

Message

I was playing around with ways to get column names from a database table and ran unto an unexpected error when I used the stmt:get_name(n) method. Strangely, the stmt:get_names() method worked as expected. I would really appreciate it if someone could look this over and tell me if I misunderstood something important.

Alias for replication:


<aliases>
  <alias
   match="^ndb_test$"
   enabled="y"
   group="test"
   regexp="y"
   send_to="12"
   sequence="100"
  >
  <send>nndb = {}
nndb.namedb = sqlite3.open("nndb.sqlite3")
nndb.loaded = {}

function nndb:init()
  self.namedb:execute[[
    PRAGMA journal_mode = WAL;
    CREATE TABLE IF NOT EXISTS adventurers (
      name TEXT PRIMARY KEY,
      city TEXT,
      enemy INTEGER,
      pirate INTEGER,
      note TEXT,
      date_added DATE
    );
  ]]
end

nndb:init()

function nndb:add(name,city)
  if not name or not city then
    return
  end
  self.namedb:execute(string.format(
    [[INSERT OR REPLACE INTO adventurers(name, city)
    VALUES ('%s', '%s');]], name, city))
end --ndb:add

nndb:add("Rangor", "Eleusis")

--check the table looks right
print("table check:")
for a in nndb.namedb:nrows("SELECT * FROM ADVENTURERS") do
  tprint(a)
end

local command = "SELECT * FROM ADVENTURERS"
local statement = nndb.namedb:prepare(command)

print("\\nColumn names:")
tprint(statement:get_names())

print("Number of columns:")
print(statement:get_name(0))
</send>
  </alias>
</aliases>

Output:

Quote:

table check:
"city"="Eleusis"
"name"="Rangor"

Column names:
1="name"
2="city"
3="enemy"
4="pirate"
5="note"
6="date_added"
Number of columns:
Run-time error
World: Achaea
Immediate execution
[string "Alias: "]:45: index out of range [0..-1]
stack traceback:
[C]: in function 'get_name'
[string "Alias: "]:45: in main chunk

From what I can tell by skimming the lsqlite3.c source code, the stmt object is internally a (pointer to a) C struct. The get_names() method does an explicit count of the number of columns using the sqlite3_column_count(vm) function.

On the other hand, the get_name(n) method assumes the svm->columns member of the struct is accurate and uses that to check the index is in bounds. That seems to be an issue because the columns member is only updated by a limited number of functions - in particular, it's not adjusted by db_prepare.

Did I stumble upon a bug, or am I misunderstanding how SQL works?

Thanks in advance.

top

Posted by

Nick Gammon Australia (22,928 posts)

bio Forum Administrator

Date

Reply #1 on Tue 01 Jun 2021 05:25 AM (UTC)

Message

It looks like you've stumbled across a bug. The lsqlite3.c file is quite old (version 0.7-devel) and this may have been fixed in future versions. I tend to not fiddle with interfacing code like that unless it fails in some way.

You obviously have a work-around, by calling get_names.

- Nick Gammon

www.gammon.com.au, www.mushclient.com

top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

6,032 views.

It is now over 60 days since the last post. This thread is closed. Refresh page

Go to topic: Search the forum

top

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.