Trouble inserting data into an sqlite3 database
#1
Hey all.

I'm new to Lua, but I'm trying to create and access a sqlite3 database.
I managed to create a database using the following:
db = sqlite3.open(PLUGIN:GetLocalFolder() .. "/database.sqlite3")

-- Create tables and insert default records
stmt = db:prepare("CREATE TABLE test (name, address)")
stmt:step()
stmt:finalize()

stmt = db:prepare("INSERT INTO test (name, address) VALUES (\'John Doe\', \'Test street 3\')")
stmt:step()
stmt:finalize()

db:close()

Now this seems to work fine, a database is created in the plugin folder. However according to the sqlite tool on Linux, it's empty.

Could anybody write a quick tutorial on how to do this? Preferably with prepared statements. Simply inserting data in a database, and afterwards selecting it and putting it into a variable and/or array.
Thanks in advance!
Reply
Thanks given by:
#2
Don't you have to say the type of column's? For example "name STRING, address STRING"
Reply
Thanks given by:
#3
Woops, totally forgot.! I just forgot when I read the Lua Sqlite documentation. For example here, they don't show it's needed. I should just stop using phpMyAdmin for everything and write more queries myself Wink

Now I changed it around a bit:
stmt = db:prepare("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, name STRING, address STRING)")

According to the sqlite tool it seems to work... I get the following dump:
Code:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (id INTEGER PRIMARY KEY, name STRING, address STRING);
COMMIT;

I'm still having trouble actually inserting something with prepared statements though.
Reply
Thanks given by:
#4
SQLite doesn't need the column types, it can store any data in any column, it's just the later interpretation that is affected by saying types.

Could you try making a few more insert statements, see if any of those get through? I have a feeling that either the last statement doesn't get through, or only the first statement gets through. Also, can you print the results of each stmt:step() and stmt:finalize()?
print("stepping...")
print(stmt:step())
print("finalizing...")
print(stmt:finalize())
(Note that the stmt:step() must be the only thing in the print statement, so that multiple return values are reported properly)
Reply
Thanks given by:
#5
I'm not sure what really changed, but I can succesfully create tables and insert data now. My code however, is practically the same. I really don't know, but I guess fixedBig Grin

Anyways I managed to retrieve some data from the database, however it gives an error if the result is empty. So for example I have:
Code:
SELECT * FROM test WHERE test_id = ?

If the ID I'm asking for does not exist, then I get the following error:
Code:
[17:13:55] LUA: Plugins/Townvalds/database.lua:43: misuse of function
[17:13:55] Stack trace:
[17:13:55]   [C](-1): get_values
[17:13:55]   Plugins/Townvalds/database.lua(43): ExecuteStatement
[17:13:55]   Plugins/Townvalds/functions.lua(48): (no name)
[17:13:55] Stack trace end
[17:13:55] Error in plugin Townvalds calling function <callback>()

If the ID i'm asking for however does exist, then I have no problems whatsoever.
I'm using "stmt:get_values()" to retrieve the data from the query.

It's more of a general Lua question, but I'm guessing you guys will have a clueBig Grin
Reply
Thanks given by:
#6
It seems you're writing some sort of a wrapper around the DB? You may consider reusing one of the existing wrappers:
https://github.com/madmaxoft/GalExport/b...#L324-L381

The error you're running into is, by my very limited guess, caused by calling the get_values function at such a moment - the function could for example expect that it is called only if there are any results returned from the query (so there must be another function indicating whether there are any results or not). Difficult to say anything more without seeing your exact code.
Reply
Thanks given by:
#7
(11-22-2015, 02:21 AM)PureTryOut Wrote: I'm not sure what really changed, but I can succesfully create tables and insert data now. My code however, is practically the same. I really don't know, but I guess fixedBig Grin

Anyways I managed to retrieve some data from the database, however it gives an error if the result is empty. So for example I have:
Code:
SELECT * FROM test WHERE test_id = ?

If the ID I'm asking for does not exist, then I get the following error:
Code:
[17:13:55] LUA: Plugins/Townvalds/database.lua:43: misuse of function
[17:13:55] Stack trace:
[17:13:55]   [C](-1): get_values
[17:13:55]   Plugins/Townvalds/database.lua(43): ExecuteStatement
[17:13:55]   Plugins/Townvalds/functions.lua(48): (no name)
[17:13:55] Stack trace end
[17:13:55] Error in plugin Townvalds calling function <callback>()

If the ID i'm asking for however does exist, then I have no problems whatsoever.
I'm using "stmt:get_values()" to retrieve the data from the query.

It's more of a general Lua question, but I'm guessing you guys will have a clueBig Grin

Try to use bind_names (http://lua.sqlite.org/index.cgi/doc/tip/...bind_names).
I'm sorry for bad English.
Reply
Thanks given by:
#8
I'm not sure how that would help me? I'm binding every value in my query, and changing it to bind_names did not work, I still get the same error.
Reply
Thanks given by:
#9
(11-22-2015, 08:50 AM)PureTryOut Wrote: I'm not sure how that would help me? I'm binding every value in my query, and changing it to bind_names did not work, I still get the same error.

Code:
function someFunc(playerName, someName, aData)
local sql = [=[
    INSERT INTO exports(plName, name, data)
    VALUES(:plName, :name, :data)
    ;
  ]=];

  -- Open DB
  local db = sqlite3.open('data.sqlite3');
  if not db then
    return false;
  end

  -- Execute statement
  local stmt = db:prepare(sql);

  -- Is it allright?
  if not stmt then
    db:close();
    return false;
  end

  -- Bind names
  stmt:bind_names({
    plName = playerName,
    name = someName,
    data = aData
  });

  -- Insert record
  local ret = stmt:step();

  if ret ~= sqlite3.OK and ret ~= sqlite3.DONE then
    stmt:finalize();
    db:close();
    return false;
  end

  -- Clean handler
  stmt:finalize();

  -- Close DB
  db:close();

return true;
end

Any questions?
I'm sorry for bad English.
Reply
Thanks given by:
#10
(11-22-2015, 09:31 AM)DrMasik Wrote: Any questions?

Sorry, I think you may have missed a part of the conversation. I managed to insert data, using prepared statements.
The problem I have now is using a SELECT query: if the result contains data from the database it runs fine, if not, it errors out instead.
Like I said, it breaks on stmt:get_values().

@xoft, I just noticed I completely missed your response, sorry for that. I'm indeed trying to write a wrapper around the database, so I can just run "ExecuteStatement" with my query and an array of parameters. It works fine for now, except when using SELECT with no result.

My current complete code:
function ExecuteStatement(sql, parameters)
	local db = sqlite3.open(PLUGIN:GetLocalFolder() .. "/database.sqlite3")
	local stmt = db:prepare(sql)

	if not (parameters == nil) then
		for key, value in pairs(parameters) do
			stmt:bind(key, value)
		end
	end

	stmt:step()

	if (sql:match("SELECT")) then
		result = stmt:get_values()
	elseif (sql:match("INSERT")) then
		result = stmt:last_insert_rowid()
	end

	stmt:finalize()

	if not (result == nil) then
		return result
	end

	db:close()
end

last_insert_rowid() breaks too but I have to look a little bit more into that before I ask any questions about it Wink
Reply
Thanks given by:




Users browsing this thread: 1 Guest(s)