SQL Help

[Gior]

Hi,
I'm trying to figure out how to use SQL.
I built this code to try understand the basics:
#include <sourcemod>

public Plugin:myinfo =
{
name = "New Plugin",
author = "Gior",
description = "<- Description ->",
version = "1.0",
url = "<- URL ->"
}

new Handle:DB = INVALID_HANDLE

public OnPluginStart()
{
RegConsoleCmd("sm_sqlmsg", SQLMessage)
RegConsoleCmd("sm_getsqlmsg", GetSQLMessage)

IntializeDB()
}

IntializeDB()
{
new String:Error[255]
new Handle:kv
kv = CreateKeyValues("")
KvSetString(kv, "driver", "sqlite")
KvSetString(kv, "database", "sql_test")

DB = SQL_ConnectCustom(kv, Error, sizeof(Error), true)

if(DB == INVALID_HANDLE)
{
SetFailState("The Error: %s", Error)
}

SQL_LockDatabase(DB)
SQL_Query(DB, "CREATE TABLE IF NOT EXISTS sql_test (steamid TEXT, msg TEXT)")
SQL_UnlockDatabase(DB)
}

public Action:SQLMessage(id, args)
{
if(args < 1)
{
ReplyToCommand(id, "Usage: sm_sqlmsg <msg>")
return Plugin_Handled
}

new String:msg[200];
new String:ArgPart[200]
new numArgs = GetCmdArgs()

for (new arg = 1; arg <= numArgs; arg++)
{
GetCmdArg(arg, ArgPart, sizeof(ArgPart));
Format(msg, sizeof(msg), "%s %s", msg, ArgPart);
}

new String:steamid[32]
new String:buffer[300]

GetClientAuthString(id, steamid, 32)

Format(buffer, sizeof(buffer), "SELECT msg FROM sql_test WHERE steamid = '%s'", steamid)

new Handle:QueryH = SQL_Query(DB, buffer)

new iRowCount = SQL_GetRowCount(QueryH)

if(iRowCount > 0)
{
Format(buffer, sizeof(buffer), "INSERT INTO sql_test VALUES ('%s', '%s')", steamid, msg )
SQL_Query(DB, buffer)
PrintToChat(id, "Message: %s", msg)
}
else
{
Format(buffer, sizeof(buffer), "UPDATE sql_test VALUES ('%s', '%s')", steamid, msg )
SQL_Query(DB, buffer)
PrintToChat(id, "Message: %s", msg)
}

return Plugin_Handled
}

public Action:GetSQLMessage(id, args)
{
new String:query[200]
new String:steamid[32]
new String:msg[100]

GetClientAuthString(id, steamid, sizeof(steamid), true)

Format(query, sizeof(query), "SELECT msg FROM sql_test WHERE steamid = '%s'", steamid)

new Handle:QueryH = SQL_Query(DB, query)

if(QueryH != INVALID_HANDLE)
{
if(SQL_FetchRow(QueryH))
{
SQL_FetchString(QueryH, 0, msg, sizeof(msg))
PrintToChat(id, "Your Message is: %s", msg)
}
}
else
{
new String:Error[250]
SQL_GetError(QueryH, Error, sizeof(Error))
PrintToChat(id, "SQL Error: %s", Error)
}

return Plugin_Handled
}

When I use the command "sm_sqlmsg" for the first time (since the SQL file was created), The message is saved, But when I am trying to change the message, It stays the same.
Can someone help me fix the problem?


Thanks in Advance. :)

[WildCard65]

I believe you need a where clause to find the old message.
EX(for mysql, should work for sql lite): Say you have a message "HI!" in already, but you want it as "Hello!", you do this: UPDATE <Table name here> SET `<column name here>` = '<new value here>' WHERE `<same column name here>` = '<old value here>';
So for my example, it'll be: UPDATE sql_test SET `msg` = 'Hello!' WHERE `msg` = 'HI!';

[LambdaLambda]

Your main problem is in here:

if(iRowCount > 0)

Basically, what you're saying in here is "if iRowCount is greater than 0", then insert new record. If it's not - update.

You want to make it other way-round.

And your second mistake is on your update query. Replace it with this:
Format(buffer, sizeof(buffer), "UPDATE sql_test SET msg = '%s' WHERE steamid = '%s'", msg, steamid);

[Gior]

I believe you need a where clause to find the old message.
EX(for mysql, should work for sql lite): Say you have a message "HI!" in already, but you want it as "Hello!", you do this: UPDATE <Table name here> SET `<column name here>` = '<new value here>' WHERE `<same column name here>` = '<old value here>';
So for my example, it'll be: UPDATE sql_test SET `msg` = 'Hello!' WHERE `msg` = 'HI!';

Your main problem is in here:

if(iRowCount > 0)

Basically, what you're saying in here is "if iRowCount is greater than 0", then insert new record. If it's not - update.

You want to make it other way-round.

And your second mistake is on your update query. Replace it with this:
Format(buffer, sizeof(buffer), "UPDATE sql_test SET msg = '%s' WHERE steamid = '%s'", msg, steamid);

Thank You, It Works! :)