Hi all,
I’ve been fooling about with sql some more and I have it mostly working well now, excepting that the game is giving me a lot of instances where it doesn’t find rows for data that has yet to be saved. While this is good to know on my test server, it’s spammy on my production one. How would I go about suppressing SQL errors for no rows found (or just SQL errors in general) for said production server?
Here is an example invocation:
// ----------------------------------------------------------------------------- LoadPlayerLocation()
// Procedure: LoadPlayerLocation
// Retrieve the player location from the database and port the player to it. If
// no location exists, save the module start location as that location.
void LoadPlayerLocation(object oPC) {
// Get player location in game
string sName = GetPCPlayerName(oPC);
object oArea = GetArea(oPC);
string sAreaTag = GetTag(oArea);
vector vPosition = GetPosition(oPC);
float fFacing = GetFacing(oPC);
object oNewArea;
string sNewAreaTag;
vector vNewPosition;
float fNewFacing;
location lNewLocation;
// Don't want to save locations for non-PCs
if (GetIsPC(oPC)==FALSE) { return; }
// Determine if this character already has a saved location
string sLocation = "SELECT * FROM Location LIMIT 1;";
sqlquery sql = SqlPrepareQueryObject(oPC, sLocation);
while (SqlStep(sql)) {
sNewAreaTag = SqlGetString(sql,0);
oNewArea = GetObjectByTag(sNewAreaTag);
vNewPosition = SqlGetVector(sql,1);
fNewFacing = SqlGetFloat(sql,2);
}
if (sNewAreaTag=="") { return; } // no saved location or malformed save
if (sNewAreaTag!=sAreaTag) {
if (vNewPosition!=vPosition) {
// Compute new lcoation
lNewLocation = Location(oNewArea, vNewPosition, fNewFacing);
AssignCommand(oPC, ClearAllActions());
AssignCommand(oPC, ActionJumpToLocation(lNewLocation));
// Log retrieving the player
string sLog = "Loaded character " + sName + " location in " + sNewAreaTag;
WriteTimestampedLogEntry(sLog);
}
}
}
Yeah I do try that, for some things I use it to initialize the data if no data is returned; but this results in a bunch of spam of yellowtext to the player, which is what I’d like to suppress or avoid. You get a bunch of “sqlite error: no data returned”
That I understood. The “Count(*)” should always return with a result, 0 or any number of datarows. Of course under the precondition that the table exists.
The other unusual thing (IMO) is that you select “*” into a single variable without structure.
Well, my skills are on Oracle. I can only point out things that spring in my eye. No guarantee that I’m right.
Add an order clause so that if multiple locations are in the table, the sql query planner knows which one you want.
List the columns to query explicitly, do not select *. If you say *, you also get the schema order (I think?), not the one you might have expected. Explicit naming is better to read and understand as well.
Look, I’m as vexed as you are trying to determine it, but at time of writing, that is literally the only code that interacts with that, in whole cloth, so I don’t know what to tell you. There is no “something else”. Maybe there should be, but there was not.
Making sure we don’t have out-of-order exceptions by initializing things properly cures most ails, as does making sure the table actually has entries.
An error for no data returned isn’t exactly unexpected behaviour, SQL queries where there is no content in the table should return “no rows returned.” It seems the game is just pushing all the sql errors to the game console for individual players which is not really a behaviour I’d expect or consider desirable, especially on a PW where it is possible players may be able to divine exploits from seeing the database errors such as that.
This is why I’m so confused here: SqlFetch() will return FALSE to indicate no rows. It should definitely not emit a error, as that’s correct use of the API. I understand the WAR in place is what allows to move on, and that’s fine. Just saying, there’s something else going on and I couldn’t say what it is right now.
Same as with printing script errors in general to the player log, it’s a convenience feature: The alternative is to only print them to the server log, which “no one” ever reads. Security implications are not really a concern when variable binding is used (like everyone should); the worst that happens is that the unbound query text and failing script name leaks.
A point could be made to weaken this to only emit errors of any kind to SP games and DMs in MP, or maybe to hide the query text from non-DMs. The important part here is to make the DMs/developers aware that a script error has happened in the first place. I’ve started a discussion with the dev team on that topic.
If you run NWNX, there’s a toggle to do this already, I think. But if you run NWNX, you’re much more likely to actually watch the server log (edit: It should be NWNX_Feedback_SetFeedbackMessageHidden(.., NWNX_FEEDBACK_SCRIPT_ERROR, TRUE, ..))
