[IceCucumber]

I'm trying to insert a row into a table, and then increment an integer value ("count"), using prepared statements, but I'm running into some issues with my syntax.
I've removed the variables for readability sake, but this reproduces the same error.

PHP Code:

...

new String:sqlString[256];
Format(sqlString, sizeof(sqlString), "INSERT INTO test2 (steamid, name) VALUES (\"foo\", \"bar\"); UPDATE test2 SET count = Coalesce(count, 0) + 1 WHERE steamid = \"foo\";");

new Handle:sqlQuery = SQL_PrepareQuery(sqlConnection, sqlString, sqlError, sizeof(sqlError));

if (sqlQuery == INVALID_HANDLE)
{
    PrintToServer("SQL error: %s", sqlError);
    return Plugin_Stop;
}

... 


This will stop at the first error check:

SQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE test2 SET count = Coalesce(count, 0) + 1 WHERE steamid = "foo"' at line 1

The first part (INSERT INTO [...];) works fine. I've also tried running this query in my local Apache setup, and it's working just fine:

INSERT INTO test2 (steamid, name) VALUES ("foo", "bar"); UPDATE test2 SET count = Coalesce(count, 0) + 1 WHERE steamid = "foo";

Any idea?

[WildCard65]

Does your table have a column named "count"? If so, reference it like this: `count` also reference "steamid" as `steamid` in your update command.

[IceCucumber]

Ah yes, pardon the lack of clarity. The column is called "count" indeed. Maybe not the most descriptive name.

I tried

PHP Code:

Format(sqlString, sizeof(sqlString), "INSERT INTO test2 (steamid, name) VALUES (\"foo\", \"bar\"); UPDATE test2 SET `count` = Coalesce(`count`, 0) + 1 WHERE `steamid` = \"foo\";"); 


but still get

SQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE test2 SET `count` = Coalesce(`count`, 0) + 1 WHERE `steamid` = "foo"' at line 1

The query

INSERT INTO test2 (steamid, name) VALUES ("foo", "bar"); UPDATE test2 SET `count` = Coalesce(`count`, 0) + 1 WHERE `steamid` = "foo";

works on my local SQL server.

[asherkin]

SM doesn't let you run multiple queries in one go.

[Bacardi]

What above guy have said.

I didn't find for ALTER TABLE a quick check if row already exist in table, then ADD it.
So I query first count field, if result is invalid, then add row in table.

Then there is prepared statement

*This is however example, maybe have some faults.

Spoiler

new Handle:g_hDB;
new Handle:g_hStatement;

public OnPluginStart()
{
	RegConsoleCmd("sm_test", test);
	SQL_TConnect(TConnect);
}

public Action:test(client, args)
{
	DoThing(client);
	return Plugin_Handled;
}

public TConnect(Handle:owner, Handle:hndl, const String:error[], any:data)
{
	if(hndl == INVALID_HANDLE)
	{
		SetFailState("error: %s", error);
	}
	PrintToServer("DATABSE FOUND!")
	g_hDB = hndl;

	// What if we have already this table...
	//SQL_FastQuery(g_hDB, "CREATE  TABLE  IF NOT EXISTS test2 (steamid VARCHAR(65) PRIMARY KEY , name VARCHAR(65))");

	new Handle:result = SQL_Query(g_hDB, "SELECT count FROM test2"); // Look row called count

	if(result == INVALID_HANDLE)
	{
		SQL_FastQuery(g_hDB, "ALTER TABLE test2 ADD count INT(11) NULL DEFAULT 0"); // add row in table
		PrintToServer("ALTER TABLE test2 ADD count INT(11) NULL DEFAULT 0");
	}
	else
	{
		CloseHandle(result);
	}

	new String:buffer[256];
	g_hStatement = SQL_PrepareQuery(g_hDB, "INSERT INTO test2 (steamid,name) VALUES (? , ?) ON DUPLICATE KEY UPDATE count=count+1, name=?", buffer, sizeof(buffer));

	if(g_hStatement == INVALID_HANDLE)
	{
		PrintToServer("g_hStatement error: %s", buffer);
	}
}

DoThing(client)
{
	if(g_hDB == INVALID_HANDLE || g_hStatement == INVALID_HANDLE)
	{
		PrintToConsole(client, "Error database or statement!");
		return;
	}

	new String:query[256];
	GetClientAuthString(client, query, sizeof(query));
	SQL_BindParamString(g_hStatement, 0, query, true);

	Format(query, sizeof(query), "%N", client);
	SQL_BindParamString(g_hStatement, 1, query, true);
	SQL_BindParamString(g_hStatement, 2, query, true);

	SQL_Execute(g_hStatement);

	PrintToServer("insert %s", query);
}

[IceCucumber]

Ah, I see. Thanks!