Trouble inserting data into an sqlite3 database
#11
(11-23-2015, 01:34 AM)PureTryOut Wrote:
(11-22-2015, 09:31 AM)DrMasik Wrote: Any questions?

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().

Replace
Code:
stmt:get_values()

to
Code:
local dataCol1;
local dataCol2;

for col1, col2 in stmt:urows() do
  dataCol1 = col1;
  dataCol2 = col2;
end

-- If no cycle dataCol2 == nil and dataCol2 == nil
I'm sorry for bad English.
Reply
Thanks given by:
#12
Sorry for the really, really late response. I just gave up on it basically.

Current 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)
			stmt:step();
		end
		--stmt:bind_names(parameters);
		--stmt:step();
	end

	local result;
	if (sql:match("SELECT")) then
		local x = 1;
		for row in stmt:urows() do
			result = row;
			x = x + 1;
			LOG("TEST");
		end
		--LOG(stmt);
	elseif (sql:match("INSERT")) then
		--result = stmt:last_insert_rowid();
		LOG("INSERT");
	else
		LOG("NOTHING");
	end

	stmt:finalize();

	db:close();

	if not (result == nil) then
		return result;
	else
		return 0;
	end
end

I'm 100% sure that the SELECT query I'm running should return at least 1 row. However, it doesn't go into the loop even once. It doesn't break, it just doesn't run properly.

I've tried urows, nrows and rows, but none of them seem to do anything.
Reply
Thanks given by:
#13
I think the problem may be with stepping the statement while binding values. You should first bind all values and then do a single step - move line 8 to line 10.
Here's an "ExecuteStatement()" function that has been tested to work on Windows x64, Linux x86 and RasPi (and is likely to work anywhere):
https://github.com/madmaxoft/GalExport/b...e.lua#L376
Reply
Thanks given by:
#14
Actually I finally fixed the problem using the guide here.
I had to use stmt:step() in the loop instead of stmt:rows. It now worsk perfectly!
Reply
Thanks given by:




Users browsing this thread: 1 Guest(s)