SQLServer

[lostdreamz]

Is there any update on when the plugin for SQLServer is going to be ready. Its so much more powerful than SQLite and doesnt lock the database which is a big problem when the module is running. I cant change anything.

Thank you

Rebecca Casidy (owner World of Desire 2:Ryhiron)

[Gryphyn]

[tayls25]

[Papillon]

I do hope more developers start working on support for other databases (PostgreSQL, Firebird, ...). The requirements are not too high and I think the interface is quite easy to understand.

Myself, I do not have enough time to support all kinds of DBs, but together with some dedicated individuals, it should be no problem to support all major (and even minor) SQL servers out there.

Keep up the good work!
_________________
Papillon

[Gryphyn]

[lostdreamz]

There seems to be a bug in SQLite. I am losing things in it when the server crashes. These appear to be only written back correctly when the server is shut down. Also as I previously mentioned the database is locked while the server is up.

Thanks

Rebecca Casidy (owner World of Desire 2: Ryhiron)

[Grumalg]

What you are seeing is not a bug. For performance reasons SQLite is normally used in a transaction wrapper and NWNX starts a transaction when SQLite initializes. Unfortunately, useing transactions has two side effects.

First, data written to the DB isn't actually written to the DB file until the transaction is committed. Instead, it caches such data in a ram buffer. This means any crash will lose any data not yet written to the DB file. SQLite will return (read) data it has cached in a transaction making it *appear* to be in the DB file, but such data will be lost when SQLite shuts down if the transaction hasn't been committed. To flush the ram cache and actually write the data to the DB file you need to issue two commands. First COMMIT the current transaction, then BEGIN a fresh one so you keep the performance transactions give. Doing this everytime you write to the DB is costly in DB calls, so the usual approach is to handle this in a heartbeat script that does the COMMIT/BEGIN every n heartbeats to keep the DB load down. Basically you are tradeing DB load vs data loss from a crash with a uncommitted transaction. You might also choose to do COMMIT/BEGIN at certain critical times that are important to you. In any case to do this you always use two SQL commands:

SQLExecDirect("COMMIT");
SQLExecDirect("BEGIN");

Second, while a transaction is in progress any tables involved in the transaction are locked, and no other process can use them. You can choose not to use transactions with SQLite avoiding the table lock issue, but SQLite's performance will suffer greatly if you do. To not use transactions, simply issue a SQLExecDirect("COMMIT") at module load to cancel the transaction nwnx automatically starts and never start another transaction.

If your after doing live DB content changes from another app while the module is running, you should use MySQL instead of SQLite. Yeah I know it *is* harder to setup and use, but MySQL is a vastly more capable DB engine and would solve both the above issues. Taking the time to learn to use MySQL will be well worth the time it takes you.

--- Grumalg ---

[Grumalg]

I have worked more with MSSQL server than with any of the many DB engines I've worked with. I like MSSQL a *lot* and can make it roll over or sit up and beg, but MySQL is a generally better choice for most people working with NWN/NWNX.

Since MSSQL is rather expensive (yeah I know about the cheap/free limited versions...) most people use SQLite or MySQL. That means if being able to use community created stuff is valuable to you, or you want to provide code to the community you need to work with SQLite or MySQL.

To me SQLite feels very crippled and brain dead, it's only saving grace is lack of an install and thus ease of use. But as your needs grow and you want to do more sophisticated things you'll start hitting the wall with SQLite.

As far as ODBC support, it's generally a nice thing to have. Having it in NWNX4 will open the doors to a lot of DB engines that might otherwise remain unuseable. But ODBC is *always* slower than a native connection to the DB engine. ODBC is a 'second language' many DB engines support after their native tongue, but always requires translation into the native tongue before it can be used in the engine.

Also, all ODBC support isn't equal and there are many incompatabilities that can rear their ugly heads at either end of an ODBC connection. If you hit a problem like this with ODBC there may or may not be a workaround that will let you get the job done.

All of the above led me to decide to use MySQL and avoid all the possible problems, have extensive capabilities, and have nwn community code available to me or be able release code to the community.

--- Grumalg ---

[lostdreamz]

I am a professional developer working with SqlServer. We use transactions and they do lock the database. However the real reason for use of transaction is to bind SQL statements together and provide the ability to rollback if there is an error in one of those statements. I cant see a reason for NWNX4 to use a single transaction. I doubt speed is the issue as data integrity is far more important and by using a single transaction there is risk of massive data loss.

If the solution is to put a commit statement when the module loads then I will do this. Thanks for your advice.

Rebecca Casidy

[Grumalg]

Ok, we both know what transactions are *usually* good for

But, SQLite is different. This is not to say SQLite transactions don't do what you usually think of them as being used for. However, SQLite recomends *always* useing transactions as the engine runs *vastly* slower if you don't use them.

That is why NWNX always starts a transaction.... performance....

--- Grumalg ---

[lostdreamz]

Well maybe if you are writing or reading thousands of rows back to the database but NWNX4 works with single rows do you really think perfomance is a bigger issue than data loss here. Anyway no point arguing I will try without the transactions and see if we notice any difference.

Rebecca

[Grumalg]

Internally SQLite is very different from other engines you've used. Take a look at the docs on www.sqlite.org. Among other things you'll see there that the author(s) specifically say client/server situations is not a good use for SQLite. SQLite locks the entire DB file every time any read/write is in progress. This makes having several clients at the same time problematic. You'll also find the section on version 3 datatypes in the docs a bit shocking compared to other DB engines. For example declared datatype is treated only as a suggestion for what the data is, and SQLite will cheerfully insert alpha text into a field declared as integer.

The performace issue is serious without transactions. While the benchmarks found in the NWNX2 ODBC2 section of the docs accessable from this main site page are old they will give you some idea. At least for that older version they show 30 ms vs 2800 ms for transaction vs no transaction.

--- Grumalg ---

[lostdreamz]

Honestly I would still rather have infrequent poor performance than loss of data. I just hope the support for SQL Server comes soon

[Grumalg]

When I first played with NWN1/NWNX2 I *did* use MS SQL Server and it worked fine. However over time it became more and more apparent the MySQL was a better choice for use with NWNX.

Now, if I was writing a custom client/server/DB app, MS SQL Server is my engine of choice. I've been known to write procs containing quadruply nested cursor loops populating temp tables in T-SQL. But for NWN/NWNX life will be much easier with MySQL. Somewhere along the way MS changed the ODBC stuff in MS SQL Server and NWNX2 stopped working with it according to posts around this forum, but that was well after I had already stopped useing MS SQL Server with NWNX.

I have both MS SQL Server and MySQL running on my 2003 enterprise dev server here at home and use whichever one is appropriate to the task at hand. MySQL provides all the GUI equivalent of Enterprise Manager you'll ever need. You can even install the MySQL ODBC connector and use Access as an interface to the DB if ya want. As of NWNX4 1.07 you can even use MySQL stored procs that return multiple recordsets.

There's no telling when/if MS SQL Server support will be available/stable for NWNX4.

I'm not arguing with ya, just trying to tell ya that the MySQL water is fine and you'll have an easy time learning to use it since you already know a DB engine as complex as MS SQL Server and you won't have to wait or face any of the problems you've been having with SQLite...

--- Grumalg ---

[tayls25]