Got the persistent script working but now have a mysql error

[Frang]

Here is the code. But for some reason it does the fetch to see if player has info but when it finds the info it skips the update process and tried to just dump the info to the database.

[Grumalg]

Your nwnx_odbc log text is showing you that the record you are attempting to insert already exists, thus the duplicate entry error. This means you nwscript code isn't detecting that the record already exists and switching between insert and update properly.

Your 'if' test isn't checking the returned field to see if 'Player' actually matches sPlayer. Remember that SQL_SUCCESS only indicates you got a result from the DB and no results from the query is a valid value.

Another approach would be to use 'SELECT COUNT(*)' with your WHERE clause which will return the number of records matching. Since NWNX only handles string data you'll have to CAST the COUNT(*) value as CHAR so it can pass through NWNX. If you don't already have it you should DL the MySQL documentation pdf which whill show you the CAST syntax.

However in either case you need to examine the contents of the first returned field in your 'if' decision to decide between INSERT and UPDATE.

[Frang]

could I just change my check to something like this?
[code]

if(SQLFetch() == sPlayer || sName) // if you retrieved an entry
{
{/code}

[Grumalg]

I think you don't understand SQLFetch() fully. SQLFetch() takes the DB returned result and places it in a buffer. When SQLFetch() returns SQL_SUCCESS it means there was no error, and a return of SQL_ERROR means there was an error executing the SQL statement. Neither of these have anything to do with what the returned data contains.

A SQL statement that does not return any data (i.e. there was no matching record), does not result in a SQL_ERROR return from SQLFetch(). You have to look at the data itself in the buffer via SQLGetData(n), where 'n' is the 'one' based field number being returned, to know what was actually returned. In the case where your SQL doesn't result in any returned data SQLGetData(1) will contain a null string. However a null string return isn't *always* conclusive because it's possible for a DB field to actually contain a null string as legitimate data.

In cases where you *know* there will never be a field containing a null string in your table, testing SQLGetData(1) for null string is enough to tell there was no matching record. If your data allows a null string in a field then fetching SELECT CAST(COUNT(*) AS CHAR) becomes useful as SQLGetData(1) will then contain a string of '0' for no matching records or a value of say '1' if there was one matching record (or some other number as string if more than one record matched).

[odenien]

My code snips I showed you had SQLGetData being called only if SQLFetch was successful. That is why you have to initalize the playername and tag and check for empty or PlayID for zero. If the fetch fails or if the get data fails then, it assumes the player you are trying to find is not in the player table.

[Grumalg]

A SQL statement that doesn't match any records is not an error.

You have to examine the fetched data with SQLGetData to determine if any data was actually returned. I see no SQLGetData calls at all in the above code. Your 'if' that switches between INSERT and UPDATE should be based on examining the contents of the 'Player' field.

Most of the time I ignore the return value of SQLFetch. Once you have known working SQL statements an error from SQLFetch only tells you the DB engine has failed. Since there isn't really anything you can do about handleing that in nw script other than writeing it to the log, as you can't code to fix the problem. Thus I *usually* ignore return values from SQLFetch except for special cases.

Your 'if' should look more like this:

SQLExecDirect(sSQL);
SQLFetch();
sFound = SQLGetData(1);
if (sFound == sPlayer) // sPlayer is found to exist
{
--update--
}
else // sPlayer didn't exist
{
---insert---
}

This works because if the record doesn't exist SQLGetData(1) will be a null string.

[odenien]

I don't think NWNX2 handles it the same as normal query engines. I always remembered the data coming back as an empty string.

[tayls25]

I'm experiencing the same problem in nwnx4 with a SetPersistentCampaignString call..... based on this argument, are you saying the code in the sql_include is wrong?

Are you saying that the following is wrong? Because I've pulled this right out of the sql_include:

void SetPersistentString(object oObject, string sVarName, string sValue, int iExpiration =
0, string sTable = "pwdata")
{
string sPlayer;
string sTag;

if (GetIsPC(oObject))
{
sPlayer = SQLEncodeSpecialChars(GetPCPlayerName(oObject));
sTag = SQLEncodeSpecialChars(GetName(oObject));
}
else
{
sPlayer = "~";
sTag = GetTag(oObject);
}

sVarName = SQLEncodeSpecialChars(sVarName);
sValue = SQLEncodeSpecialChars(sValue);

string sSQL = "SELECT player FROM " + sTable + " WHERE player='" + sPlayer +
"' AND tag='" + sTag + "' AND name='" + sVarName + "'";
SQLExecDirect(sSQL);

if (SQLFetch() == SQL_SUCCESS)
{
// row exists
sSQL = "UPDATE " + sTable + " SET val='" + sValue +
"',expire=" + IntToString(iExpiration) + " WHERE player='" + sPlayer +
"' AND tag='" + sTag + "' AND name='" + sVarName + "'";
SQLExecDirect(sSQL);
}
else
{
// row doesn't exist
sSQL = "INSERT INTO " + sTable + " (player,tag,name,val,expire) VALUES" +
"('" + sPlayer + "','" + sTag + "','" + sVarName + "','" +
sValue + "'," + IntToString(iExpiration) + ")";
SQLExecDirect(sSQL);
}
}



-------------------------------

should it be something like:

if (!(SQLGetData(1) == ""))

[tayls25]

Well that didn't work either. I ended up getting it to work by rearranging the insert and update order, and removing the conditional.

so now the script inserts, if it gets a key violation, it just fails on that statement, then it updates. If the insert happened, it just updates the record anyhow.

It's not ideal, but I suppose it works.

If anyone has a better suggestion to make this work, I'd appreciate hearing it.

[Grumalg]