Register forum user name Search FAQ

Gammon Forum

Notice: Any messages purporting to come from this site telling you that your password has expired, or that you need to verify your details, confirm your email, resolve issues, making threats, or asking for money, are spam. We do not email users with any such messages. If you have lost your password you can obtain a new one by using the password reset link.

Due to spam on this forum, all posts now need moderator approval.

 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  (23,133 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.


7,865 views.

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

Go to topic:           Search the forum


[Go to top] top

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