[SOLVED] SQLite - SQL Logic error or missing database

[cheez3d]

  
dbExec(getElementData(resourceRoot,"dayz:resource.settings.database"),"INSERT INTO `players` (`serial`,`password`,`data`) VALUES (?,?,?);","serial","password","3,4") 
  

This is a test query that should insert some data into the database but it is not working. I have already tested and the connection element exists, so there should be a problem inside the query that I cannot figure out. Can anyone help please? I've already searched on Google and didn't find anything.

ERROR: dbExec failed (1); SQL logic error or missing database

Edited March 28, 2014 by Guest

[Spajk]

Did you try without the ";" on the end?

[WhoAmI]

dbExec ( getElementData ( resourceRoot, "dayz:resource.settings.database" ), "INSERT INTO `players` (`serial`,`password`,`data`) VALUES (?,?,?)", "serial", "password", "3,4" ) 

For checking what is in database, I prefer:

https://addons.mozilla.org/pl/firefox/a ... e-manager/

[cheez3d]

I also tried without ; before posting the topic. Still no result.

[Castillo]

The problem is with your database handler, because I just tried it and works fine.

[cheez3d]

I still need help with this. Also tried using dbConnect inside, instead of getElementData and it is the same thing. I also use outputChatBox and it returns an userdata. Here are some screenshots from SQLite Browser. I think the problem is the id column, but I'm not sure.

As you can see the table is empty.

[xXMADEXx]

Why don't you just define a variable with the database connection?

local databaseConnection = dbConnect ( args... ) 
  
function getDbConnection ( ) 
    return databaseConnection 
end 

[Castillo]

Post the dbConnect script line.

[cheez3d]

  
local connection = dbConnect("sqlite","resources/storage/dayz.db") 
setElementData(resourceRoot,"dayz:resource.settings.database",connection,false) 
  

I also execute a SELECT query before that INSERT and it works fine.

[Castillo]

And the other code is in the same resource as that?

[cheez3d]

Yep. It's actually in the same script.

[Castillo]

Tried executing it using "connection" instead of getting the element data?

[cheez3d]

Yeah. And I also tried using dbConnect inside the dbExec function.

[cheez3d]

  
dbExec (dbConnect(getElementData(resourceRoot,"dayz:resource.settings.database"),"INSERT INTO `players` VALUES (?,?,?,?);",23,"serial","password","3,4") 
  

This query works just fine (if i do not supply the columns to insert into) but the problem is that id is an auto increment column and I don't want to insert the values manually.

EDIT: Got the problem! But I still have a dilemma. The creation query will create the id column that has the NOT NULL property. When I insert new values into the table I don't specify the id and that is why it's giving me the error. So, I remove NOT NULL from id and now this query works but there is no id value automatically assigned (it remains empy).

  
-- CREATION QUERY 
dbExec(getElementData(resourceRoot,"dayz:resource.settings.database"),"CREATE TABLE IF NOT EXISTS `players` (`id` INT PRIMARY KEY,`serial` TEXT NOT NULL,`password` TEXT NOT NULL,`data` TEXT NOT NULL);") 
-- INSERT QUERY 
dbExec(getElementData(resourceRoot,"dayz:resource.settings.database"),"INSERT INTO `players` (`serial`,`password`,`data`) VALUES (?,?,?);","serial","password","3,4") 
  

EDIT 2: Well, after I documented myself a little bit I found out that you must use a query like this:

  
dbExec(getElementData(resourceRoot,"dayz:resource.settings.database"),"CREATE TABLE IF NOT EXISTS `players` (`id` INTEGER PRIMARY KEY AUTOINCREMENT,`serial` TEXT NOT NULL,`password` TEXT NOT NULL,`data` TEXT NOT NULL);") 
  

There are some weird differences between MySQL queries and SQLite queries. Problem finally solved!

[WhoAmI]

I got same problem and I was using database manager to create column with auto increment property. Anyway, good that you found the problem, now I know how to build tables inside function.