SQLite Problems

Hi, so recently I’ve been trying to create a database that will store and load entity networking variables, however I’ve come to halt with it as I’m relativity new to lua and I’m completely stuck. My aim is to have a command that sets the network variables then saves straight after and when the map / server loads, it should take the data from the database then apply to the corresponding entity (door in this case). Since I’m new I’m not really too sure on how I would do this, if someone could elaborate on what I’m doing wrong that would be wonderful :smiley:

Just a note, if anyone plans on commenting “Why don’t you just copy DarkRP’s code” well, first off I don’t do things that way and furthermore I can’t make any sense of their code as it’s all split up into separate documents.
Also, excuse any sloppy / wrong code, I’m new to lua & it’s syntax :slight_smile:

Here’s my table setup:



function OnTablesExist()
	if sql.TableExists("player_info") && sql.TableExists("entity_info") then
		Msg("Tables already exist")
	else
		if (!sql.TableExists("player_info")) then
			query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
			result = sql.Query(query)
			if (sql.TableExists("player_info")) then
				Msg("Success! player_info created 
")
			else
				Msg("Something went wrong 
")
				Msg( sql.LastError( result ) .. "
" )
			end
		end
		if (!sql.TableExists("entity_info")) then
			query = "CREATE TABLE entity_info ( doorID int, doorKeys int, doorPrice int, doorOwner text, doorNotice text, doorIsOwned boolean, doorPolice boolean, doorOwnable boolean, map text  )"
			result = sql.Query(query)
			if (sql.TableExists("entity_info")) then
				Msg("Success! entity_info created 
")
			else
				Msg("Something went wrong 
")
				Msg( sql.LastError( result ) .. "
" )
			end
		end
	end
end


Here’s the load function:



function LoadEntities()
	doorID = sql.QueryValue("SELECT doorID FROM entity_info WHERE doorID = '"..doorID.."'")
	doorKeys = sql.QueryValue("SELECT doorKeys FROM entity_info WHERE doorID = '"..doorID.."'")
	doorPrice = sql.QueryValue("SELECT doorPrice FROM entity_info WHERE doorID = '"..doorID.."'")
	doorOwner = sql.QueryValue("SELECT doorOwner FROM entity_info WHERE doorID = '"..doorID.."'")
	doorNotice = sql.QueryValue("SELECT doorNotice FROM entity_info WHERE doorID = '"..doorID.."'")
	doorIsOwned = sql.QueryValue("SELECT doorIsOwned FROM entity_info WHERE doorID = '"..doorID.."'")
	doorPolice = sql.QueryValue("SELECT doorPolice FROM entity_info WHERE doorID = '"..doorID.."'")
	doorOwnable = sql.QueryValue("SELECT doorOwnable FROM entity_info WHERE doorID = '"..doorID.."'")

	for k, v in pairs(doorID) do
		v:SetNWInt("Keys", doorKeys)
		v:SetNWInt("doorPrice", doorPrice)
		v:SetNWString("Owner", doorOwner)
		v:SetNWString("doorNotice", doorNotice)
		v:SetNWBool("isOwned", doorIsOwned)
		v:SetNWBool("policeDoor", doorPolice)
		v:SetNWBool("Ownable", doorOwnable)
	end
end


Here’s the save function:



function SaveEntity( ent )
	doorID = ent:GetNWInt("doorID")
	doorKeys = ent:GetNWInt("Keys")
	doorPrice = ent:GetNWInt("doorPrice")
	doorOwner = ent:GetNWString("Owner")
	doorNotice = ent:GetNWString("doorNotice")
	doorIsOwned = ent:GetNWBool("isOwned")
	doorPolice = ent:GetNWBool("policeDoor")
	doorOwnable = ent:GetNWBool("Ownable")

	sql.Query("UPDATE entity_info SET doorID = "..doorID..", doorKeys = "..doorKeys..", doorPrice = "..doorPrice..", doorOwner = '"..doorOwner.."', doorNotice = '"..doorNotice.."', doorIsOwned = "..tostring(doorIsOwned)..", doorPolice = "..tostring(doorPolice)..", doorOwnable = "..tostring(doorOwnable).." WHERE map = "..string.lower(game.GetMap()).." AND doorID = "..doorID.."")
	ply:ChatPrint("[SproutRP] Entity Saved")
end


?

What exactly is wrong?

[editline]31st July 2015[/editline]

Heres an optimized “tableexists()” function, note: sql.LastError() doesnt have any arguments.



function OnTablesExist()
	if sql.TableExists("player_info") && sql.TableExists("entity_info") then
		return true, "Tables existing"
	else
		if !sql.TableExists("player_info") then
			local result = sql.Query("CREATE TABLE player_info ( unique_id varchar(255), money int )")
			if result == false then
				Msg("An error occoured: "..sql.LastError())
			elseif result == nil then
				Msg("An error occoured: Returned no data.")
			else
				Msg("Table created successfully.")
			end
		end

		if !sql.TableExists("entity_info") then
			local result = sql.Query("CREATE TABLE entity_info ( doorID int, doorKeys int, doorPrice int, doorOwner text, doorNotice text, doorIsOwned boolean, doorPolice boolean, doorOwnable boolean, map text  )")
			if result == false then
				Msg("An error occoured: "..sql.LastError())
			elseif result == nil then
				Msg("An error occoured: Returned no data.")
			else
				Msg("Table created successfully.")
			end
		end
	end
end


[editline]31st July 2015[/editline]

also, heres the edited LoadEntities() function:



function LoadEntities()
	local doorID = sql.Query("SELECT doorID FROM entity_info")
	local doorKeys = sql.Query("SELECT doorKeys FROM entity_info")
	local doorPrice = sql.Query("SELECT doorPrice FROM entity_info")
	local doorOwner = sql.Query("SELECT doorOwner FROM entity_info")
	local doorNotice = sql.Query("SELECT doorNotice FROM entity_info")
	local doorIsOwned = sql.Query("SELECT doorIsOwned FROM entity_info")
	local doorPolice = sql.Query("SELECT doorPolice FROM entity_info")
	local doorOwnable = sql.Query("SELECT doorOwnable FROM entity_info")

	for k, v in pairs(doorID) do
		v:SetNWInt("Keys", doorKeys)
		v:SetNWInt("doorPrice", doorPrice)
		v:SetNWString("Owner", doorOwner)
		v:SetNWString("doorNotice", doorNotice)
		v:SetNWBool("isOwned", doorIsOwned)
		v:SetNWBool("policeDoor", doorPolice)
		v:SetNWBool("Ownable", doorOwnable)
	end
end


Also, please note that you should all functions & vars locals as often as possible.

[editline]31st July 2015[/editline]

I dont know much about sql, but that should work with querying, and should cause less performance issues, its your way of setting stuff like doorID, doorKeys, etc.

the problem is the “map”, somehow my brain doesnt function atm, so I cant add a check for loadentities.

Basically the problem at the moment is it’s not saving the data to the database. When I look at a door (entity) I execute the command and it should get the network variables, then store them into the database based on the door ID but I’m not too sure how to do that.

EDIT: Well actually I don’t know if it’s not saving to the database or if it’s just not loading correctly? SQLite does still work on a local singleplayer game right?

I dont know which database type is used when playing singleplayer, cl or sv, you should download a SQLLite reader, and read both of these, also, generally coding inside a localy hosted server is better.

Both. Depending on where you you run sql.Query() function, on “server” or on “client”.

You sure its not sv? Since in Singleplay you are the server, there is no client, thats why coding in singleplayer is bad.

Err, no. In singleplayer there are still both server and client realms, they run on same machine but they’re different.