ATTACH command

[Lady Abagail]

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!"

[Acrodania]

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

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

[Lady Abagail]

[Tenkawa]

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

[Tenkawa]

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

[Lady Abagail]

[Tenkawa]

[Tenkawa]

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;

[Lady Abagail]

[Tenkawa]

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

[Lady Abagail]

[Papillon]

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

[Tenkawa]

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.

[Acrodania]

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

And nice work

[Lady Abagail]