View previous topic :: View next topic |
Author |
Message |
scarface
Joined: 12 Jun 2005 Posts: 94 Location: Sweden
|
Posted: Sat Apr 19, 2008 16:58 Post subject: SQLFetch Problems (I Think!) |
|
|
Hello, I am trying to modify some APS functions to work with my banking system, but from what I can tell, whenever I use SQLFetch() it always returns SQL_ERROR regardless of whether the row exists or not, here are a couple of the modified functions:
Code: | void SF_SetPersistentPlayerString(object oPC, string sVarName, string sValue, string sTable);
void SF_SetPersistentPlayerString(object oPC, string sVarName, string sValue, string sTable)
{
string sCDKey = SQLEncodeSpecialChars(GetPCPublicCDKey(oPC));
sVarName = SQLEncodeSpecialChars(sVarName);
sValue = SQLEncodeSpecialChars(sValue);
string sSQL = "SELECT " + sVarName + " FROM " + sTable + " WHERE cd_key = '" + sCDKey + "'";
SQLExecDirect(sSQL);
if (SQLFetch() == SQL_SUCCESS)
{
// row exists
sSQL = "UPDATE " + sTable + " SET " + sVarName + " = '" + sValue +
"' WHERE cd_key = '" + sCDKey + "'";
SQLExecDirect(sSQL);
}
else
{
// row doesn't exist
sSQL = "INSERT INTO " + sTable + " (cd_key, " + sVarName + ") VALUES" +
"('" + sCDKey + "','" + sValue + "')";
SQLExecDirect(sSQL);
}
}
string SF_GetPersistentPlayerString(object oPC, string sVarName, string sTable);
string SF_GetPersistentPlayerString(object oPC, string sVarName, string sTable)
{
string sCDKey = SQLEncodeSpecialChars(GetPCPublicCDKey(oPC));
sVarName = SQLEncodeSpecialChars(sVarName);
string sSQL = "SELECT " + sVarName + " FROM " + sTable + " WHERE cd_key = '" + sCDKey + "'";
SQLExecDirect(sSQL);
if (SQLFetch() == SQL_SUCCESS)
{ return SQLDecodeSpecialChars(SQLGetData(1)); }
else
{ return ""; }
}
void SF_SetPersistentPlayerInt(object oPC, string sVarName, int nValue, string sTable);
void SF_SetPersistentPlayerInt(object oPC, string sVarName, int nValue, string sTable)
{
SF_SetPersistentPlayerString(oPC, sVarName, IntToString(nValue), sTable);
}
int SF_GetPersistentPlayerInt(object oPC, string sVarName, string sTable);
int SF_GetPersistentPlayerInt(object oPC, string sVarName, string sTable)
{
string sCDKey = SQLEncodeSpecialChars(GetPCPublicCDKey(oPC));
object oModule = GetModule();
sVarName = SQLEncodeSpecialChars(sVarName);
string sSQL = "SELECT " + sVarName + " FROM " + sTable + " WHERE cd_key = '" + sCDKey + "'";
SQLExecDirect(sSQL);
SetLocalString(oModule, "NWNX!ODBC!FETCH", "-2147483647");
return StringToInt(GetLocalString(oModule, "NWNX!ODBC!FETCH"));
} |
And here is the necessary part of the system:
Code: | void main()
{
object oPC = GetPCSpeaker();
int nBanked = SF_GetPersistentPlayerInt(oPC, "gold", "banking");
nBanked += nDeposit;
TakeGoldFromCreature(nDeposit, oPC, TRUE);
SF_SetPersistentPlayerInt(oPC, "gold", nBanked, "banking");
} |
Basically, when the row exists i.e. the cd key, I know this for sure using a database viewer, and the amount of gold first stored is valid, but it's inserting a new row of the same cd key with new values instead of updating the old row with new values, so it seems SQLFetch is always returning FALSE, why?
On another note, is it possible to create an arrayed database, like a 2da file? |
|
Back to top |
|
|
Disco
Joined: 06 Dec 2006 Posts: 152
|
Posted: Sun Apr 20, 2008 10:01 Post subject: |
|
|
Take a look at your SQL log and post the SQL query that fails? |
|
Back to top |
|
|
scarface
Joined: 12 Jun 2005 Posts: 94 Location: Sweden
|
Posted: Sun Apr 20, 2008 11:30 Post subject: |
|
|
Hmmm, it seems to work now, but I have another issue, I have an xp bank also, the table should be the same to store xp and gold, example:
Empty Table 'banking':
Code: | -------------------------------------
| CD Key | Gold | Exp |
-------------------------------------
| | | |
-------------------------------------
| | | |
------------------------------------- |
If I use the gold bank to deposit 555 gold, first with the following commands:
SELECT gold FROM banking WHERE cd_key = 'XXXXXXXX';
if row exists use:
UPDATE banking SET gold = '555' WHERE cd_key = 'XXXXXXXX';
if row doesn't exist use:
INSERT INTO banking (cd_key, gold) VALUES ('XXXXXXXX','555')";
This works! the table will then look like:
Code: | -------------------------------------
| CD Key | Gold | Exp |
-------------------------------------
| XXXXXXXX | 555 | |
-------------------------------------
| | | |
------------------------------------- |
Now if I use the xp bank "after" I used the gold bank to deposit 555 exp
(Or any amount, 555 just for test), using the following commands:
SELECT exp FROM banking WHERE cd_key = 'XXXXXXXX';
if row exists use:
UPDATE banking SET exp = '555' WHERE cd_key = 'XXXXXXXX';
if row doesn't exist use:
INSERT INTO banking (cd_key, exp) VALUES ('XXXXXXXX','555')";
This doesn't work if the gold bank had been updated first, and the same if exp bank was used
first and gold bank used second, then the gold bank doesn't store data, what am I doing wrong?
[Edit]
Well I have to confirm that this DOES work if typed directly into MySQL, but through NWScript it fails, I'm confused :s |
|
Back to top |
|
|
scarface
Joined: 12 Jun 2005 Posts: 94 Location: Sweden
|
Posted: Mon Apr 21, 2008 6:19 Post subject: |
|
|
Ok the problem is solved, Funkyswerve helped me with it, he didn't have time for a request though, I wanted to add item banking to the table too. With my banking system, a creature holds all of the player's items, and then the creature is stored to the database, I would like to know how object's can be stored into this table like so:
Code: |
--------------------------------------------------
| CD Key | Gold | Exp | Items |
--------------------------------------------------
| XXXXXXXX | 555 | 1000 | npc_object |
--------------------------------------------------
| | | | |
-------------------------------------------------- |
Any help would be great, thanks. |
|
Back to top |
|
|
Disco
Joined: 06 Dec 2006 Posts: 152
|
Posted: Mon Apr 21, 2008 10:35 Post subject: |
|
|
You realise that this way of storing is an open invitation to muling items, right? |
|
Back to top |
|
|
scarface
Joined: 12 Jun 2005 Posts: 94 Location: Sweden
|
Posted: Mon Apr 21, 2008 18:28 Post subject: |
|
|
Disco wrote: | You realise that this way of storing is an open invitation to muling items, right? |
Right, so... any help with storing an object in that table? |
|
Back to top |
|
|
Disco
Joined: 06 Dec 2006 Posts: 152
|
Posted: Mon Apr 21, 2008 19:16 Post subject: |
|
|
There's a function for it in aps_include. Check that and take out all stuff you don't need. |
|
Back to top |
|
|
scarface
Joined: 12 Jun 2005 Posts: 94 Location: Sweden
|
Posted: Mon Apr 21, 2008 19:37 Post subject: |
|
|
Disco wrote: | There's a function for it in aps_include. Check that and take out all stuff you don't need. |
I already tried that, I couldn't get it to work so I came here, let me at least post what I attempted to do so you can get an idea of what I need.
First off, the datatype for the item storage NPC in the MySQL table is 'text', not sure if that's right or not but it seems to work that way for the default object database, and this is how I have tried to work it, but failed:
Code: | void SF_SetPersistentPlayerObject(object oOwner, string sVarName, object oObject, string sTable);
void SF_SetPersistentPlayerObject(object oOwner, string sVarName, object oObject, string sTable)
{
string sCDKey = GetPCPublicCDKey(oOwner);
string sSQL = "SELECT " + sVarName + " FROM " + sTable + " WHERE cd_key = '" + sCDKey + "'";
SQLExecDirect(sSQL);
if (SQLFetch() == SQL_SUCCESS)
{
// row exists
sSQL = "UPDATE " + sTable + " SET " + sVarName + "= %s WHERE cd_key = '" + sCDKey + "'";
SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
StoreCampaignObject ("NWNX", "-", oObject);
}
else
{
// row doesn't exist
sSQL = "INSERT INTO " + sTable + " (cd_key, " + sVarName + ") VALUES" +
"('" + sCDKey + "', %s)";
SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
StoreCampaignObject ("NWNX", "-", oObject);
}
}
object SF_GetPersistentPlayerObject(object oPC, string sVarName, string sTable, object oOwner = OBJECT_INVALID);
object SF_GetPersistentPlayerObject(object oPC, string sVarName, string sTable, object oOwner = OBJECT_INVALID)
{
string sCDKey = GetPCPublicCDKey(oPC);
string sSQL = "SELECT " + sVarName + " FROM " + sTable + " WHERE cd_key = '" + sCDKey + "'";
SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
if (!GetIsObjectValid(oOwner))
oOwner = oPC;
return RetrieveCampaignObject ("NWNX", "-", GetLocation(oOwner), oOwner);
} |
|
|
Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Mon Apr 21, 2008 23:35 Post subject: |
|
|
scarface wrote: | First off, the datatype for the item storage NPC in the MySQL table is 'text', not sure if that's right or not but it seems to work that way for the default object database, and this is how I have tried to work it, but failed: |
You're using SCORCO
SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
so you need to be using the object based table structure.
'BLOB' instead of 'TEXT' (it's another table, just for Objects)
Cheers
Gryphyn |
|
Back to top |
|
|
scarface
Joined: 12 Jun 2005 Posts: 94 Location: Sweden
|
Posted: Tue Apr 22, 2008 4:30 Post subject: |
|
|
Gryphyn wrote: | scarface wrote: | First off, the datatype for the item storage NPC in the MySQL table is 'text', not sure if that's right or not but it seems to work that way for the default object database, and this is how I have tried to work it, but failed: |
You're using SCORCO
SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
so you need to be using the object based table structure.
'BLOB' instead of 'TEXT' (it's another table, just for Objects)
Cheers
Gryphyn |
You're wrong about that fella, I don't know what I was doing wrong before when I was testing it, but it seems to work great now storing my storage npc persistently with the functions I posted using datatype text, I think I was inputing the player var in the wrong parameter when calling the function(s), anyways... it works |
|
Back to top |
|
|
Disco
Joined: 06 Dec 2006 Posts: 152
|
Posted: Tue Apr 22, 2008 11:22 Post subject: |
|
|
Make sure you strip any quotes from the varname with SQLEncodeEtc().
I alse use the Blob data type in the table, but I guess Text will work as well. The function is meant to store an object in the BioWare db and I bet that's been done as a string.
The method you follow gives a big overhead. Try this:
Table:
id (type=int, length=11, primary key, auto increment)
cdkey (type=string, length=8, index key)
item_name (type=string, length=128, index key)
item_data (type=blob, length=65535 )
You store items you put in a box following the previous post.
Now, you retrieve a list of what's in store by
"SELECT id, item_name FROM MyObjects WHERE cdkey='XYZ12345' "
Loop that into a convo and retrieve individual items with
"SELECT item_data FROM MyObjects WHERE id=134"
That way you don't have to store a whole NPC every time. |
|
Back to top |
|
|
scarface
Joined: 12 Jun 2005 Posts: 94 Location: Sweden
|
Posted: Tue Apr 22, 2008 16:58 Post subject: |
|
|
Disco wrote: | Make sure you strip any quotes from the varname with SQLEncodeEtc().
I alse use the Blob data type in the table, but I guess Text will work as well. The function is meant to store an object in the BioWare db and I bet that's been done as a string.
The method you follow gives a big overhead. Try this:
Table:
id (type=int, length=11, primary key, auto increment)
cdkey (type=string, length=8, index key)
item_name (type=string, length=128, index key)
item_data (type=blob, length=65535 )
You store items you put in a box following the previous post.
Now, you retrieve a list of what's in store by
"SELECT id, item_name FROM MyObjects WHERE cdkey='XYZ12345' "
Loop that into a convo and retrieve individual items with
"SELECT item_data FROM MyObjects WHERE id=134"
That way you don't have to store a whole NPC every time. |
Wouldn't it be more efficient storing a single object holding multiple items in one call rather than multiple items and calls to the database? |
|
Back to top |
|
|
Disco
Joined: 06 Dec 2006 Posts: 152
|
Posted: Wed Apr 23, 2008 10:26 Post subject: |
|
|
Depends on what you call efficient. If you want to minimise calls whole object storing is indeed better, but you need to move the whole block of data just to look at what's in it, so it isn't efficient if you look at the amount of data transfered between SQL and NWN. With a bit of effort you can get PC objects of about 1 MB, and moving that repeatedly isn't a trivial load. |
|
Back to top |
|
|
scarface
Joined: 12 Jun 2005 Posts: 94 Location: Sweden
|
Posted: Wed Apr 23, 2008 21:38 Post subject: |
|
|
Disco wrote: | Depends on what you call efficient. If you want to minimise calls whole object storing is indeed better, but you need to move the whole block of data just to look at what's in it, so it isn't efficient if you look at the amount of data transfered between SQL and NWN. With a bit of effort you can get PC objects of about 1 MB, and moving that repeatedly isn't a trivial load. |
Efficient "performance" wise, performance is the most important to me, that is what I meant |
|
Back to top |
|
|
FunkySwerve
Joined: 02 Jun 2005 Posts: 377
|
Posted: Thu Apr 24, 2008 0:40 Post subject: |
|
|
When using NWNX-MySQL, the difference in performance is negligible, since the biggest hit is the ingame object creation - do enough and it'll down your server. This is NOT ameliorated by spawning in the creature, since all its items are also spawned in and added as objects. This is why you see additional spawn lag on creatures wearing lots of gear, incidentally. In any event, because MySQL calls are blazing fast compared to Campaign ints, there's absolutely no reason to do it this way, and every reason not to, since as a previous poster pointed out, it prevents you from viewing or manipulating the items individually, one of the prime advantages of MySQL to begin with. Basically, the way you're doing makes perfect sense when using the Bioware database, and none at all when using MySQL. Which is of course why I suggested you use the provided functions, to begin with. You are reinventing a square wheel.
Funky |
|
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
|