| View previous topic :: View next topic |
| Author |
Message |
tricia
Joined: 14 Jul 2005
Posts: 7
|
 Posted: Thu Jul 14, 2005 18:22 Post subject: creating/deleting tables. Noob question |
 |
|
Hi! I have just started with this, and I have a little problem
I am creating a persistant storage chest for players, that work for different characters across the same player account.
So what I have done is made so that when a player opens the chest,
1. it creates a table with the player name
2. it loads all objects from that table
and when they close the chest
1. Save all objects to database
2. Clear chest inventory.
The problem for me is, I figure everytime this table is created, it deletes all the contents in it. So that when it tries to load from the database its empy each time. Is it possible to make an 'if' statement to check if this table already exists in the database, so that it will not delete that table if it exists from before? In other words, ONLY new players visiting the chest for the first time will have the table created, while the others will just load from their existing tables.
Sorry if this is a totally stupid question, I do not have very much knowledge of scripting nor databases.
Thank you!
-Tricia
Last edited by tricia on Thu Jul 14, 2005 18:55; edited 2 times in total |
|
| Back to top |
|
 |
tricia
Joined: 14 Jul 2005
Posts: 7
|
| Posted: Thu Jul 14, 2005 18:53 Post subject: |
|
|
Actually, there might be another problem too. I tried removing the whole 'create table' part. I tried it out, and it saved the item i put in the container (The default script from the demo). However, when I open the chest again, it still answers with 'Retrieved 0 items'
I'll post the script so you can see
| Code: | #include "aps_include"
void main()
{
object chest = GetObjectByTag("perchest",0);
object PC = GetLastOpenedBy();
if (!GetIsPC(PC)) return;
string player = GetPCPlayerName(PC);
string announce = ("Loading Persistant Storage for: " + player);
ActionSpeakString(announce);;
//creates a table with the playername as index.
int iItem;
int bContinue = TRUE;
object oCreated;
/* MUST FIND IF STATEMENT OR TABLE WILL BE DELETED EACH TIME
}
// For SQLite..now THIS is the messy part...hehe. Most of this is copied from
//the demo for NWNX2
SQLExecDirect("DROP TABLE " + player);
SendMessageToPC(GetLastUsedBy(), "Creating Table '" + player +"' for SQLite...");
SQLExecDirect("CREATE TABLE "+player+" (" +
"player varchar(64) NOT NULL default '~'," +
"tag varchar(64) NOT NULL default '~'," +
"name varchar(64) NOT NULL default '~'," +
"val blob," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default current_timestamp," +
"PRIMARY KEY (player,tag,name)" +
")");
SendMessageToPC(GetLastUsedBy(), "Table '"+player+"' created.");
}
*/
//Now for the retrieving of the items
while (bContinue)
{
oCreated = GetPersistentObject(chest, "Item_" + IntToString(iItem), chest, player);
if (!GetIsObjectValid(oCreated))
bContinue = FALSE;
else
iItem++;
}
SendMessageToPC(GetFirstPC(), "Retrieved " + IntToString(iItem) + " objects from database.");
} |
Really sorry if the code is very messy. Its mostly copied from the demo scripts.
Again, thank you! |
|
| Back to top |
|
|
Grim
Joined: 04 Jan 2005
Posts: 12
|
| Posted: Mon Jul 18, 2005 19:54 Post subject: |
|
|
Tricia,
Not sure if this would help or not, but why not do a SELECT against the table you are going to create first. If it returns an empty result you know that the table if it exists is empty and can be dropped. If you get a result you know that it has content of some sort. I do something along these lines in one of my scripts:
| Code: |
serverFlagSQL = "SELECT * FROM " + PC_TABLE ;
SQLExecDirect(serverFlagSQL);
int rs = SQLFetch();
if(rs == SQL_SUCCESS)
{
// table found with content
}
else
{
// table not found or no content
}
|
I'm not sure this would work exactly in your case but works with what I do. I would hard code a couple of values for PC_TABLE, one you know exists and one you know doesn't and check the results. Anyway hope that helps.
Also, are you sure you want to drop and create tables for each player? Couldn't you just have one table and use unique player keys for the content?
_________________
Grim Havoc
Systems guy for Mists of the Mordri
(now private) |
|
| Back to top |
|
|
dguntner
Joined: 31 Dec 2004
Posts: 116
|
| Posted: Wed Jul 20, 2005 5:53 Post subject: |
|
|
| Grim wrote: | Tricia,
| Code: |
serverFlagSQL = "SELECT * FROM " + PC_TABLE ;
SQLExecDirect(serverFlagSQL);
int rs = SQLFetch();
if(rs == SQL_SUCCESS)
{
// table found with content
}
else
{
// table not found or no content
}
|
I'm not sure this would work exactly in your case but works with what I do. I would hard code a couple of values for PC_TABLE, one you know exists and one you know doesn't and check the results. Anyway hope that helps. |
One thing: If the table that you're trying to check has lots of data in it, doing a SELECT * FROM PC_TABLE will generate a lot of useless I/O in the database. I would suggest doing a DESCRIBE PC_TABLE instead. If it returns any information you'll get SQL_SUCCESS set, otherwise you get SQL_ERROR.
| Quote: |
Also, are you sure you want to drop and create tables for each player? Couldn't you just have one table and use unique player keys for the content? |
I agree that having a single table to track these things and using a unique key would probably be better.
--Dave |
|
| Back to top |
|
|
tricia
Joined: 14 Jul 2005
Posts: 7
|
| Posted: Wed Jul 20, 2005 19:51 Post subject: |
|
|
okay, thanks for the response guys, I see your point in the tables making alot of extra load. I actually got my script to work in the end... However, its still very buggy. I made a 'token' to spawn the chest and give it unique tag [player]_chest, and then operate from that. However, when 2 or more players spawn the chest in same area, it doesnt work right. I dont understand why, but the second chest then will have no items in, and report no items read from database.
Anyway, if I am to do as you say, about dropping the idea of creating a table for each player, and creating a key instead, how would i go about making that script?
here's the general idea:
*when the token is used, it spawns a persistent chest next to the player, and gives it unique tag [player]_chest, to not interfere with others doing the same.
*in the OnOpened on the chest, it will read all items stored from the database for that player, eventually create a new key if that player doesnt have one.
*the player can then take and give whatever he likes, and when he closes the chest, it stores his data
also, some other things i would like your view on:
-can i do so that other players cannot open a chest they have not spawned themselves?
-can I prevent them from even SEEING inside an open chest, one that the owner is currently using?
I really appreciate your help here, thanks alot guys:)
Tricia |
|
| 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
