| View previous topic :: View next topic |
| Author |
Message |
Squatting Monk
Joined: 28 Jun 2007
Posts: 76
|
 Posted: Thu Oct 25, 2007 23:10 Post subject: Help with a custom persistent object script |
 |
|
I put together a script to work specifically with my HCR2 persistence system (which identifies all PCs by a unique ID number). Here's the function...
| Code: | // Stores oPC's persistent object in the database.
// - oPC: the player character with which to associate the object.
// - sVarName: the variable name to identify the item.
// - oItem: the object to store in the database.
void h2_SetExternalPCObject(object oPC, string sVarName, object oItem);
void h2_SetExternalPCObject(object oPC, string sVarName, object oItem)
{
if (!GetIsPC(oPC))
{
string sError = "ERROR: Could not not store " + GetTag(oPC) + "'s persistent objects - object oPC is not a player character.";
WriteTimestampedLogEntry(sError);
return;
}
string sPCID = h2_GetPCID(oPC);
string sSQL = "SELECT Value FROM pcobjdata WHERE PCID='" + sPCID + "' AND VarName='" + sVarName + "'";
SQLExecDirect(sSQL);
if(SQLFetch() == SQL_SUCCESS)
{
sSQL = "UPDATE pcobjdata SET Value= %s WHERE PCID='" + sPCID + "' AND VarName='" + sVarName + "'";
SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
StoreCampaignObject ("NWNX", "-", oItem);
}
else
{
sSQL = "INSERT INTO pcobjdata (PCID, VarName, Value) VALUES ('" + sPCID + "', '" + sVarName + "',%s";
SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
StoreCampaignObject ("NWNX", "-", oItem);
}
} |
And here's it in use in an OnOpen script on a chest...
| Code: | void main()
{
int nItem;
int bContinue= TRUE;
object oItem;
object oOwner = OBJECT_SELF;
object oPC = GetLastOpenedBy();
string sTag = GetTag(oOwner);
string sVarName;
while(bContinue)
{
sVarName = sTag + "_" + IntToString(nItem);
oItem = h2_GetExternalPCObject(oPC, sVarName, oOwner);
if(!GetIsObjectValid(oItem))
bContinue = FALSE;
else
nItem++;
}
SendMessageToPC(oPC, IntToString(nItem) + " objects retrieved from database.");
} |
I'm getting the following error, though:
| Code: | o Got request (scorco): INSERT INTO pcobjdata (PCID, VarName, Value) VALUES ('0x00000000', 'Chest1_0',~s
! 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 '' at line 1 |
And just in case someone wants to know, here's my table creation SQL...
| Code: | sSQL = "CREATE TABLE pcobjdata (" +
"PCID varchar(10) NOT NULL default '0'," +
"VarName varchar(64) NOT NULL default ''," +
"Value blob," +
"TimeStamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP," +
"PRIMARY KEY (VarName, PCID)" +
") ENGINE=MyISAM DEFAULT CHARSET=latin1;"; |
Any ideas on what the problem is? The error message isn't very descriptive or helpful. Thanks in advance.  |
|
| Back to top |
|
 |
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Fri Oct 26, 2007 0:21 Post subject: Re: Help with a custom persistent object script |
|
|
| Squatting Monk wrote: | | Code: |
<snip>
sSQL = "INSERT INTO pcobjdata (PCID, VarName, Value) VALUES ('" + sPCID + "', '" + sVarName + "',%s";
SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
StoreCampaignObject ("NWNX", "-", oItem);
}
} |
I'm getting the following error, though:
| Code: | o Got request (scorco): INSERT INTO pcobjdata (PCID, VarName, Value) VALUES ('0x00000000', 'Chest1_0',~s
! 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 '' at line 1 |
Any ideas on what the problem is? The error message isn't very descriptive or helpful. Thanks in advance. |
You need to terminate the the brackets on your INSERT statement.
| Code: | | sSQL = "INSERT INTO pcobjdata (PCID, VarName, Value) VALUES ('" + sPCID + "', '" + sVarName + "',%s)"; |
Cheers
Gryphyn |
|
| Back to top |
|
|
Squatting Monk
Joined: 28 Jun 2007
Posts: 76
|
| Posted: Fri Oct 26, 2007 8:45 Post subject: |
|
|
D'oh! How could I have missed that? Thanks for the help, Gryphyn.
I've got another problem now, though. My script stores objects fine the first time, but when I try to change the contents of the chest, it's not letting me overwrite what's in there. The error I'm getting is...
| Code: | o Got request: SELECT Value FROM pcobjdata WHERE PCID='0x00000000' AND VarName='Chest1_0'
o Sent response (0 bytes):
o Got request (scorco): INSERT INTO pcobjdata (PCID, VarName, Value) VALUES ('0x00000000', 'Chest1_0', ~s)
! SQL Error: Duplicate entry 'Chest1_0-0x00000000' for key 1 |
That suggests to me that this section of code...
| Code: | string sSQL = "SELECT Value FROM pcobjdata WHERE PCID='" + sPCID + "' AND VarName='" + sVarName + "'";
SQLExecDirect(sSQL); |
... isn't working correctly. I don't see why it's not returning anything. What am I doing wrong here?
Another thought, too. Since the objects are stored in the database, there's nothing to stop the character from opening the chest repeatedly and taking the stuff out over and over again to amass tons of items to sell. How can I delete from the database all the objects that have been removed from the chest after it has been opened? In effect, I want the PC to put three items in the container, close it (which stores them in the database and destroys them), open it (which restores the items), take two items out, close it, and open it again to find only those two items. Any suggestions from those of you in the know? |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Fri Oct 26, 2007 11:17 Post subject: |
|
|
Part 1.
You need to go through the "IF NOT EXISTS INSERT ELSE UPDATE" hoops
If you can not find the row (via the PrimaryKey) insert it, otherwise update it. <Force SQLFetch() to work the way you want it to>
Part 2.
Does any of your string contain a single quote character?
Part 3.
You really need both an OnAcquire and OnUnacquire script. and do the appropriate insert/delete when either of these two things happen.
You can do it on Open/Close (of the chest) but that's a lag monster, you need to track what went in, what went out, was it disturbed, etc.
You could have a look at my Vendor scripts. even though they deal with 'stores' the same techniques can be applied to 'containers'. They can give you pointers to getting the OnAcquire/OnUnaquire scripts. - with containers though you need to 'OnOpen' - load the chest, 'OnClose' - destroy the contents. (assuming you're sharing the container, somehow)
Cheers
Gryphyn |
|
| Back to top |
|
|
Squatting Monk
Joined: 28 Jun 2007
Posts: 76
|
| Posted: Fri Oct 26, 2007 22:43 Post subject: |
|
|
| Gryphyn wrote: | Part 1.
You need to go through the "IF NOT EXISTS INSERT ELSE UPDATE" hoops
If you can not find the row (via the PrimaryKey) insert it, otherwise update it. <Force SQLFetch() to work the way you want it to> |
I attempted to do this and also used the REPLACE INTO command, but both of them went wild with the %s. Changing SELECT Value FROM to SELECT * FROM seemed to make the SQL statement fire. Still no idea why, though. Perhaps it was because of the BLOB data type.
| Quote: | | Does any of your string contain a single quote character? |
Nope, and curiously, when I used SQLEncodeSpecialChars(), sVarName was changed to an empty string which caused all kinds of errors.
| Quote: | You really need both an OnAcquire and OnUnacquire script. and do the appropriate insert/delete when either of these two things happen.
You can do it on Open/Close (of the chest) but that's a lag monster, you need to track what went in, what went out, was it disturbed, etc.
You could have a look at my Vendor scripts. even though they deal with 'stores' the same techniques can be applied to 'containers'. They can give you pointers to getting the OnAcquire/OnUnaquire scripts. - with containers though you need to 'OnOpen' - load the chest, 'OnClose' - destroy the contents. (assuming you're sharing the container, somehow) |
Thanks for the pointers. I'm checking out your scripts and trying to piece together what exactly they're doing. Pretty complex system, it looks like, so there's a lot to unravel.
I appreciate the help. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
