Question - Just fiddling around with MQ2SQLite and lua

[tervalas]

I was looking at the TLO for SQLite, and thinking about trying to use it in Lua.

local path = ("%s/testdb/MQ2LinkDB.db"):format(mq.luaDir)
mq.cmdf('/sqlite query "%s" testquery SELECT * FROM raw_item_data_315 where name = "Cloth Cap";', path)
testrows = mq.TLO.sqlite.rows(testquery)()

testrows is getting the value of 0, yet when I do this query in game and run /echo ${sqlite.rows[testquery]} I get 3.

What am I missing? Or is the sqlite TLO not as workable in Lua and I need to go through other methods of accessing the database?

 

[brainiac]

I don't have any examples in front of me, but you would want to use the sqlite luarock instead of the MQ2Sqlite plugin.

edit; you're missing quotes around testquery.

 

[tervalas]

I don't have any examples in front of me, but you would want to use the sqlite luarock instead of the MQ2Sqlite plugin.

edit; you're missing quotes around testquery.

Yeah, even that change didn't change the results. I was hoping not to have to worry about forcing the luarock to be loaded, but I'll run with it.

 

[brainiac]

Theres no reason why the sqlite TLO wouldn't work from Lua, but it it'd be a pretty poor experience. You might also need a delay after the command.

with the package manager it should be pretty simple to set up using the luarock.

 

[aquietone]

Yeah, even that change didn't change the results. I was hoping not to have to worry about forcing the luarock to be loaded, but I'll run with it.

you have packageman to automatically install the luarock for people, that 100% seems better than relying on the mq plugin by using something native to Lua

 

[Knightly]

local PackageMan = require('mq/PackageMan')
local sql = PackageMan.Require('lsqlite3')

local db, ec, em = sql.open(mq.TLO.Path("Resources")() .. "\\MQ2LinkDB.db") -- or "\\MQ2LinkDB_Live.db"
if db then
  local stmt, err = database:prepare('SELECT * FROM raw_item_data_315 where name = "Cloth Cap"')
  if stmt then
    local res = stmt:step()
      if res == sql.ROW then
        -- Do something with stmt:get_named_values()
      elseif res == sql.DONE then
        print("No rows found for query")
      else
        print("Step failed: %s", res)
      end
  else
    print("Something else went wrong: %s", err)
  end
else
  print("Something went wrong: %s", em)
end

 

[tervalas]

*sigh*

local stmt, err = db:prepare(string.format('SELECT classes FROM raw_item_data_315 where id = %s', ID))

I've tried multiple means of getting this to work, but it keeps rejecting the statement. Used, %d, used not string format code, used concatenation....

"Something else went wrong: 1"....which is just ERROR.

Yes, I made sure the number being passed for ID is actually the id given by mq.TLO.AdvLoot[PList](1).ID(). That shouldn't matter anyway, as this would give an internal fail on the stmt conditionals.

 

[Knightly]

Print the path you’re using for the database and confirm the database exists. You can also change the db line to open read and not create the database which will fail sooner if the path is wrong.

My guess is your path is wrong and the new database you’re opening doesn’t have that table.

After that, print your query and run it direct against the database using a DB app and make sure you didn’t get a column wrong or something.

 

[aquietone]

*sigh*

local stmt, err = db:prepare(string.format('SELECT classes FROM raw_item_data_315 where id = %s', ID))

I've tried multiple means of getting this to work, but it keeps rejecting the statement. Used, %d, used not string format code, used concatenation....

"Something else went wrong: 1"....which is just ERROR.

Yes, I made sure the number being passed for ID is actually the id given by mq.TLO.AdvLoot[PList](1).ID(). That shouldn't matter anyway, as this would give an internal fail on the stmt conditionals.

here's a random working example i stuck in a db:prepare call to, maybe it helps..

local mq = require 'mq'
local PackageMan = require('mq/PackageMan')
local sql = PackageMan.Require('lsqlite3')
local dbpath = string.format('%s\\entropy\\data\\project_lazarus_BRD_Mybard.sqlite', mq.TLO.MacroQuest.Path('macros')())
local db = sql.open(dbpath)
if db then
  local stmt, err = db:prepare("SELECT env_var,value FROM environment_song WHERE value != ':value1'")
  stmt:bind_names{ value1 = 'FALSE' }
  for row,_ in stmt:nrows() do
    for k,v in pairs(row) do
      printf('%s %s', k, v)
    end
  end
  stmt:reset()
  db:close()
else
  print('fatal error')
end

classes and id are both columns in your raw_item_data_315 table? have you tried the query without a prepared statement?

 

[tervalas]

Print the path you’re using for the database and confirm the database exists. You can also change the db line to open read and not create the database which will fail sooner if the path is wrong.

My guess is your path is wrong and the new database you’re opening doesn’t have that table.

After that, print your query and run it direct against the database using a DB app and make sure you didn’t get a column wrong or something.

grr....I feel so stupid. was sitting there thinking it had to open the database properly not realizing I was pointing to the wrong directory (had mq.luaDir for some reason lol)