| View previous topic :: View next topic |
| Author |
Message |
Frang
Joined: 22 May 2005
Posts: 32
|
 Posted: Mon Oct 30, 2006 12:30 Post subject: syntax error help if you can please. |
 |
|
Ok I been messing around with this for a few days and cant figure it out.
here is the bit of code thats giving the error.
| Code: |
// row exists
sSQL = "UPDATE players WHERE Player='" + sPlayer +
"' AND Name='" + sName +
"' Location='" + sLoc +
"' Spells='" + sSpells +
"' Feats='" + sFeats +
"' HitPoints='" + IntToString(nHP) +
"' Level='" + IntToString(nLevel) + "'";
SQLExecDirect(sSQL);
}
|
and here is the nwnx_odbc log
| Code: |
NWNX ODBC2 plugin V.0.9.2.4
(c) 2005 by Ingmar Stieger (Papillon) and Jeroen Broekhuizen
visit us at http://www.nwnx.org
o Logfile maximum size limit is: 524288 bytes
o Log level: Everything will be logged.
o Using ODBC connection.
o Hooking SCO....hooked at 5d3560
o Hooking RCO....hooked at 5d3440
o Connect successful.
o Got request: SELECT player FROM players WHERE Player='Frang' AND Name='Frang Lyonarlin'
o Sent response (5 bytes): Frang
o Got request: UPDATE players WHERE Player='Frang' AND Name='Frang Lyonarlin' Location='#AREA#Testing#POSITION_X# 13.285428047#POSITION_Y# 5.508528233#POSITION_Z# 0.000000000#ORIENTATION# 90.000000000#END#' Spells='S37=1;S100=1;S143=1;S144=1;S155=1;S371=1;' Feats='F51=1;F171=1;F258=1;F303=1;F399=1;' HitPoints='6' Level='1'
! SQL Error: [MySQL][ODBC 3.51 Driver][mysqld-4.1.21-community-nt]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 'WHERE Player='Frang' AND Name='Frang Lyonarlin' Location='#AREA#Testing#POSITION' at line 1
|
and here is my table setup
| Code: |
Player = varchar(64) has check for not null flaged as pri
Name = varchar(64) has check for not null flaged as pri
Location = text default null
Spells = text default null
Feats = text deafult null
HitPoints = int default 1
Level int default 1
|
Any hepl is greatly appreciated
Frang |
|
| Back to top |
|
 |
Mikel of Avalon
Joined: 29 Dec 2004
Posts: 72
Location: Germany
|
| Posted: Mon Oct 30, 2006 14:10 Post subject: |
|
|
I assume you would Set the values, but your SQL syntax is wrong. I try it from my workplace without nwn...
| Code: |
// row exists
sSQL = "UPDATE players " +
"SET Location='" + sLoc +
"', Spells='" + sSpells +
"', Feats='" + sFeats +
"', HitPoints='" + IntToString(nHP) +
"', Level='" + IntToString(nLevel) +
"' WHERE Player='" + sPlayer +
"' AND Name='" + sName + "'";
SQLExecDirect(sSQL);
}
|
This should do the work, but i cannot try this yet...
_________________
Mikel of Avalon
Kalandur - Die vergessene Welt |
|
| Back to top |
|
|
Frang
Joined: 22 May 2005
Posts: 32
|
| Posted: Mon Oct 30, 2006 14:58 Post subject: |
|
|
| awsome that worked thanks for the help..you rock!! |
|
| Back to top |
|
|
odenien
Joined: 26 Sep 2005
Posts: 37
|
| Posted: Mon Oct 30, 2006 15:23 Post subject: |
|
|
| One thing to note: I believe TEXT fields map to long varchar, which usually are not searchable, meaning you can not put them in a where clause. This query will also have problems if you try to create it mod_onexit. If you store the names on the player, that will work, but has a greater overhead. I would make all entries use a unique player ID and store that. An integer would only be four bytes of storage and you can use that value on exit to perform cleanup queries. |
|
| Back to top |
|
|
Frang
Joined: 22 May 2005
Posts: 32
|
| Posted: Mon Oct 30, 2006 21:29 Post subject: |
|
|
odenien thats some great info that I didnt know, problem is I have been working on this code for 7 straight days lol. I have used nwnx and mysql in the past but this is the first time I ever made code using it so its a lil slow going for me. But if you would be so kind as to post an example of this I would be happy to use it and give credit.
Thanks again for the help.
Frang |
|
| Back to top |
|
|
Frang
Joined: 22 May 2005
Posts: 32
|
| Posted: Tue Oct 31, 2006 0:38 Post subject: |
|
|
| Have another question. With the way Im storing the info what would be the best way to retrieve the stored info and apply it back to the pc? |
|
| Back to top |
|
|
odenien
Joined: 26 Sep 2005
Posts: 37
|
| Posted: Tue Oct 31, 2006 14:16 Post subject: |
|
|
First, your player table needs to have an auto increment (sequence) integer added to it. Something like
ALTER TABLE playerTable ADD COLUMN PlayerID integer auto_increment;
See database syntax for alter table and sequence field.
On mod_onEnter, you need to fetch the id.
| Code: | int playerID=0;
SQLExecDirect ("SELECT PlayerID, <anything else to fetch> from playerTable where playname = ? and charactername = ?");
If (SQLFetch ())
{
playerID = StringToInt (SQLGetData (1));
}
if (playerID == 0)
{
// New character, add info to player table
SQLExecDirect ("insert into player table (all info fields) values (all info values");
SQLExecDirect ("SELECT PlayerID from playerTable where playname = ? and charactername = ?");
If (SQLFetch ())
{
playerID = StringToInt (SQLGetData (1));
SetLocalInt (oPC, "PLAYERID", playerID);
}
}
else
{
// old character, do whatever
SetLocalInt (oPC, "PLAYERID", playerID);
} |
On exit or any other script just use the PLAYERID to reference all tables, it should be a primary key for the player table and at least a partial index for all others which will make it much faster to access.
As for the best way to store and apply information back to a PC, that is subjective to what you want to store. You should always use the native datatype on the database, and try to use what contrainers Bioware has already given you. Additional info, someone else might know better as to using a widget or just placing the info on the pc. |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
