ExecuteSQLUpdate: Difference between revisions

Latest revision as of 14:19, 9 October 2016

	This function is deprecated. This means that its use is discouraged and that it might not exist in future versions.
	Please use executeSQLQuery instead. See the examples at executeSQLQuery for equivalent UPDATE usage.

This function updates one or more rows in the database, by using the set parameter and conditions to change field values in specific rows.

The SQLite database contains globally stored data and can be used by scripts to store and retrieve data in a structured manner.

The executed SQL query is the following:

UPDATE <table> SET <set> WHERE <conditions>

Syntax

bool executeSQLUpdate ( string tableName, string set, [ string conditions ] )

Required Arguments

tableName: The name of the table you want to modify.
set: The query you want to execute on that table. The default SQL syntax is used, which means that the = is used to set a value and the ' is used to specify a value (e.g. test = 'hi' ). The , is used as a delimiter for multiple queries.

Optional Arguments

conditions: The conditions that need to be met before a specific row is changed.

Returns

The function returns true on success, and false on failure.

Example

This example creates a SQL table when a map loads, and stores info about a player to that database when he spawns.

function onMapLoad ()
	-- create our table, if it doesn't already exist
	executeSQLCreateTable ( "players", "clothes_head_texture TEXT, clothes_head_model TEXT, player TEXT" )
end
addEventHandler ( "onGamemodeMapStart", getRootElement(), onMapLoad )

function addInfoToSQL( theSpawnpoint, theTeam )	
	sourcename = getPlayerName ( source )	-- get the player's name
	
	-- try to retrieve the player data from the db
	result = executeSQLSelect ( "players", "player", "player = '" .. sourcename .. "'" )
	if ( result == false ) then	-- see if any data was found at all
		outputChatBox ( "This is your first time here! Welcome " .. sourcename .. "!", source )
		executeSQLInsert ( "players", "'none', 'none', '" .. sourcename .. "'" )
	else
		outputChatBox ( "Welcome back " .. sourcename .. "!", source )
		executeSQLUpdate ( "players", "clothes_head_texture = 'hairgreen', clothes_head_model = 'somehead'",
		"player = '" .. sourcename .. "'" )
	end	
	
	-- get the clothes data for the player
	result = executeSQLSelect ( "players", "clothes_head_texture, clothes_head_model", "player = '" .. sourcename .. "'" )
	outputChatBox ( "Your head texture is " .. result[1][1] )
	outputChatBox ( "Your head model is " .. result[1][2] )	
end
addEventHandler ( "onPlayerSpawn", getRootElement(), addInfoToSQL )

@@ Line 1: / Line 1: @@
 __NOTOC__
-{{Note_box|It is strongly recommended that you use the standard ''module.key'' naming for your keys, as shown in the example below. This prevents collisions between different scripts.}}
+{{Server function}}
-This function sets a value in the server's registry. This is data that is stored across sessions and can be read by any script. This is useful if you want to easily store a setting for your script.
+{{Deprecated|executeSQLQuery|See the examples at executeSQLQuery for equivalent UPDATE usage.}}
+This function updates one or more rows in the database, by using the set parameter and conditions to change field values in specific rows.
+The SQLite database contains globally stored data and can be used by scripts to store and retrieve data in a structured manner.
+The executed SQL query is the following:
+<syntaxhighlight lang="lua">UPDATE <table> SET <set> WHERE <conditions></syntaxhighlight>
 ==Syntax==
 <syntaxhighlight lang="lua">
-string setRegistryValue ( string key, [ string value ] )
+bool executeSQLUpdate ( string tableName, string set, [ string conditions ] )
 </syntaxhighlight>
 ===Required Arguments===
-*'''key:''' The key under which the data you wish to retrieve was stored
+*'''tableName:''' The name of the table you want to modify.
+*'''set:''' The query you want to execute on that table. The default SQL syntax is used, which means that the = is used to set a value and the ' is used to specify a value (e.g. ''test = 'hi' ''). The , is used as a delimiter for multiple queries.
 ===Optional Arguments===
-*'''value:''' A [[string]] containing the value you wish to store in the registry. If this value is not passed, the registry key specified will be removed.
+*'''conditions:''' The conditions that need to be met before a specific row is changed.
 ===Returns===
-If a ''value'' is specified, returns ''true'' in all cases. If a ''value'' is not specified, returns ''true'' if the ''key'' specified existed (and has been removed), ''false'' if it did not exist.
+The function returns ''true'' on success, and ''false'' on failure.
 ==Example==
-This example keeps track of the largest number of players playing at once on the server and announces each time the record has been broken.
+This example creates a SQL table when a map loads, and stores info about a player to that database when he spawns.
 <syntaxhighlight lang="lua">
--- Trigger our function every time a player joins
+function onMapLoad ()
-addEventHandler ( "onPlayerJoin", getRootElement(), "maxPlayerCounter" )
+	-- create our table, if it doesn't already exist
-function maxPlayerCounter ( )
+	executeSQLCreateTable ( "players", "clothes_head_texture TEXT, clothes_head_model TEXT, player TEXT" )
-    mostPlayers = getRegistryValue ( "maxPlayerCounter.mostPlayers" ) -- get the previous record from the registry
-    playerCount = getPlayerCount() -- get the number of players now
-    if ( mostPlayers == false or playerCount > mostPlayers ) -- see if there was a previous record and if there was, see if we've broken it
-        outputChatBox ( "New player count record: " .. playerCount .. " players!" ) -- display a message in the chat box
-        setRegistryValue ( "maxPlayerCounter.mostPlayers", playerCount ) -- store our new record
-    end
 end
+addEventHandler ( "onGamemodeMapStart", getRootElement(), onMapLoad )
--- Add a command handler called reset_max. This should in practice have some checks for a user's access level.
+function addInfoToSQL( theSpawnpoint, theTeam )
-addCommandHandler ( "reset_max", "resetMaxPlayerCounter" )
+	sourcename = getPlayerName ( source )	-- get the player's name
-function resetMaxPlayerCounter ( )
-    setRegistryValue ( "maxPlayerCounter.mostPlayers" ) -- remove the registry key containing the max players record
+	-- try to retrieve the player data from the db
+	result = executeSQLSelect ( "players", "player", "player = '" .. sourcename .. "'" )
+	if ( result == false ) then	-- see if any data was found at all
+		outputChatBox ( "This is your first time here! Welcome " .. sourcename .. "!", source )
+		executeSQLInsert ( "players", "'none', 'none', '" .. sourcename .. "'" )
+	else
+		outputChatBox ( "Welcome back " .. sourcename .. "!", source )
+		executeSQLUpdate ( "players", "clothes_head_texture = 'hairgreen', clothes_head_model = 'somehead'",
+		"player = '" .. sourcename .. "'" )
+	end
+	-- get the clothes data for the player
+	result = executeSQLSelect ( "players", "clothes_head_texture, clothes_head_model", "player = '" .. sourcename .. "'" )
+	outputChatBox ( "Your head texture is " .. result[1][1] )
+	outputChatBox ( "Your head model is " .. result[1][2] )
 end
+addEventHandler ( "onPlayerSpawn", getRootElement(), addInfoToSQL )
 </syntaxhighlight>
 ==See Also==
 {{Registry_functions}}