Trouble inserting data into an sqlite3 database

[PureTryOut]

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!

[NiLSPACE]

Don't you have to say the type of column's? For example "name STRING, address STRING"

[PureTryOut]

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

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.

[xoft]

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)

[PureTryOut]

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 fixed

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 clue

[xoft]

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.

[DrMasik]

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 fixed

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 clue

Try to use bind_names (http://lua.sqlite.org/index.cgi/doc/tip/...bind_names).

[PureTryOut]

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.

[DrMasik]

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?

[PureTryOut]

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