logo logo

 Back to main page

The NWNX Community Forum

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
ATTACH command
Goto page 1, 2  Next
 
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules
View previous topic :: View next topic  
Author Message
Lady Abagail



Joined: 27 Jul 2006
Posts: 17

PostPosted: Fri Jul 28, 2006 17:55    Post subject: ATTACH command Reply with quote

Okay I have extra databases ready made for NWNX and SQLite to access and modify. The question I have now is about the ATTACH command. In SQL syntax "ATTACH database-filename AS database-name" should set-up a connection to the new database. When my module loads it attempts to "ATTACH client.db AS client" and "ATTACH player.db AS player" but the nwn_odbc.txt log reports:

o Got request: ATTACH 'client.db' AS client
! SQL Error: cannot ATTACH database within transaction
o Got request: ATTACH 'player.db' AS player
! SQL Error: cannot ATTACH database within transaction

What is this error really trying to tell me? Is my syntax wrong, do I need to add a character at the end of the statement to close it [ie. like NWScript uses a semi-colon ";"], or is attaching other databases simply not in the scope of the version of SQLite and the NWNX2 plug-in here?

I also attempt to access the attached databases in other transactions:

o Got request: CREATE TABLE client.QVR9JVCQ (login varchar(64) NOT NULL default '~',char text,PRIMARY KEY (login))
! SQL Error: unknown database client

The error that returns suggests to me the this version of SQLite can attach databases, but doesnt recognize the database name because I cant get it attached when the module loads (as exampled above).

Any thoughts?
_________________
"Pudding can't fill the emptiness inside me...but it will help!"
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Acrodania



Joined: 02 Jan 2005
Posts: 208

PostPosted: Fri Jul 28, 2006 18:18    Post subject: Reply with quote

I don't know anyone that is using Attach() with NWNX...

Any particular reason you aren't just using the tables in one database?
Back to top
View user's profile Send private message
Lady Abagail



Joined: 27 Jul 2006
Posts: 17

PostPosted: Fri Jul 28, 2006 18:31    Post subject: Reply with quote

Acrodania wrote:
I don't know anyone that is using Attach() with NWNX...

Any particular reason you aren't just using the tables in one database?


Even if I could implement my design in one database, that doesnt explain why the ATTACH command isnt working for me. I want to know if it can be done, and what Im doing wrong with my scripting. The fact that the SQLite site reports it as a useable function, and that the errors that Im recieving suggest it could read and write to other databases is what Im interested in here.
_________________
"Pudding can't fill the emptiness inside me...but it will help!"
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Tenkawa



Joined: 25 Aug 2005
Posts: 15

PostPosted: Fri Jul 28, 2006 20:14    Post subject: Reply with quote

It is because if you use the sqlite plugin straight from code in nwnx the entire session is one big transaction
if you look at the code it does a begin when it opens the database and ends the transaction when it closes. You would either need to script transaction control into your nwn scripts or rewrite the plugin to commit every call.
Since ATTACH is non transactional you would _have_ to make it work outside the BEGIN/COMMIT statements.

When I re-wrote the plugin to work on linux I took out the whole session transaction and made it work where I wanted to.
Mind you I rewrote the plugin a long time ago so someone correct me if that behaviour was changed.


Tenkawa


Last edited by Tenkawa on Fri Jul 28, 2006 20:23; edited 2 times in total
Back to top
View user's profile Send private message
Tenkawa



Joined: 25 Aug 2005
Posts: 15

PostPosted: Fri Jul 28, 2006 20:19    Post subject: Reply with quote

According to what I just read in the latest source the behaviour is still there.

in sqlite.cpp

in the Connect function

rc = sqlite3_prepare(sdb, "BEGIN", -1, &pStmt, NULL);

in the Disconnect function

rc = sqlite3_prepare(sdb, "COMMIT", -1, &pStmt, NULL);

Hope this helps.

Tenkawa
Back to top
View user's profile Send private message
Lady Abagail



Joined: 27 Jul 2006
Posts: 17

PostPosted: Fri Jul 28, 2006 20:19    Post subject: Reply with quote

Tenkawa wrote:
It is because if you use the sqlite plugin straight from code in nwnx the entire session is one big transaction
if you look at the code it does a begin when it opens the database and ends the transaction when it closes. You would either need to script transaction control into your nwnx scripts or commit every call into the plugin.
When I re-wrote the plugin to work on linux I took out the whole session transaction and made it work where I wanted to.
Mind you I rewrote the plugin a long time ago so someone correct me if that behaviour was changed.


So what you are saying is the the ATTACH statement has to occur before the beginning of a transaction?
_________________
"Pudding can't fill the emptiness inside me...but it will help!"
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Tenkawa



Joined: 25 Aug 2005
Posts: 15

PostPosted: Fri Jul 28, 2006 20:24    Post subject: Reply with quote

Lady Abagail wrote:
Tenkawa wrote:
It is because if you use the sqlite plugin straight from code in nwnx the entire session is one big transaction
if you look at the code it does a begin when it opens the database and ends the transaction when it closes. You would either need to script transaction control into your nwnx scripts or commit every call into the plugin.
When I re-wrote the plugin to work on linux I took out the whole session transaction and made it work where I wanted to.
Mind you I rewrote the plugin a long time ago so someone correct me if that behaviour was changed.


So what you are saying is the the ATTACH statement has to occur before the beginning of a transaction?


That is correct. It cannot occur inside a transaction.

Tenkawa
Back to top
View user's profile Send private message
Tenkawa



Joined: 25 Aug 2005
Posts: 15

PostPosted: Fri Jul 28, 2006 20:27    Post subject: Reply with quote

This can even be recreated in the command line sqlite interface
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> begin;
sqlite> attach ftr as ftr;
SQL error: cannot ATTACH database within transaction
sqlite> commit;
sqlite> attach ftr as ftr;
Back to top
View user's profile Send private message
Lady Abagail



Joined: 27 Jul 2006
Posts: 17

PostPosted: Fri Jul 28, 2006 20:52    Post subject: Reply with quote

Tenkawa wrote:
According to what I just read in the latest source the behaviour is still there.

in sqlite.cpp

in the Connect function

rc = sqlite3_prepare(sdb, "BEGIN", -1, &pStmt, NULL);

in the Disconnect function

rc = sqlite3_prepare(sdb, "COMMIT", -1, &pStmt, NULL);

Hope this helps.

Tenkawa


Is there a workaround in NWScript without rewriting the NWNX script?
_________________
"Pudding can't fill the emptiness inside me...but it will help!"
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Tenkawa



Joined: 25 Aug 2005
Posts: 15

PostPosted: Fri Jul 28, 2006 21:01    Post subject: Reply with quote

you could send a commit as the first query to the database then wrap the rest in transaction(s).
Back to top
View user's profile Send private message
Lady Abagail



Joined: 27 Jul 2006
Posts: 17

PostPosted: Fri Jul 28, 2006 21:55    Post subject: Reply with quote

Tenkawa wrote:
you could send a commit as the first query to the database then wrap the rest in transaction(s).


Holy crackers Tenkawa, its not only working, but its doing more then I thought it would. I call a COMMIT TRANSACTION on module load and attach my two databases right after. Not only will it attach them, it will create them with teh filename you specify in script if they dont exist!

Syntax:

  • ATTACH DATABASE 'yourDBname.db' AS dbname;


Where 'yourDBname.db' is the filename created (you must include the file extension or it will make you database a generic file).
And dbname is the variable you call to effect that database in script.

Im going to start breaking out my code now and Ill be back to nwnx.org to share my finding with a load of examples and tutorials!
_________________
"Pudding can't fill the emptiness inside me...but it will help!"
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Papillon
x-man


Joined: 28 Dec 2004
Posts: 1060
Location: Germany

PostPosted: Sat Jul 29, 2006 10:09    Post subject: Reply with quote

Great! Please keep in mind that SQLite gets relatively slow if it has to work without an open transaction. If you do not BEGIN somewhere later on, every write operation is immediately written to the disk, which is slow.
_________________
Papillon
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Tenkawa



Joined: 25 Aug 2005
Posts: 15

PostPosted: Sat Jul 29, 2006 13:19    Post subject: Reply with quote

At least on our server the performance of auto committing every query has not even been noticeable due to the fact that the nwserver process itself I think is slower and serial in nature. Other apps I would definitely agree have the potential for timing issues. The other thing to watch out for is if nwserver crashes the transactions will roll back so running one transaction per whole process itself isnt always safe.
Back to top
View user's profile Send private message
Acrodania



Joined: 02 Jan 2005
Posts: 208

PostPosted: Sat Jul 29, 2006 17:43    Post subject: Reply with quote

Common practice with servers using SQLite is to manually close the transaction every few minutes then start another one.

And nice work Smile
Back to top
View user's profile Send private message
Lady Abagail



Joined: 27 Jul 2006
Posts: 17

PostPosted: Sat Jul 29, 2006 19:20    Post subject: Reply with quote

Acrodania wrote:
Common practice with servers using SQLite is to manually close the transaction every few minutes then start another one.

And nice work Smile


I couldnt have even touched this functionality without your support! Who knows what possible applications this might have for Persistent Worlds. I just felt that the single 'pwdata' table was a bit confining for large scale operations, and wouldnt be very readable in a database interface such as SQLiteCC if there were a lot of different module systems accessing and storing data there.
_________________
"Pudding can't fill the emptiness inside me...but it will help!"
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules All times are GMT + 2 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
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