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 
 
SQLServer
Goto page 1, 2, 3, 4, 5, 6  Next
 
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related
View previous topic :: View next topic  
Author Message
lostdreamz



Joined: 19 Nov 2006
Posts: 26

PostPosted: Thu Feb 01, 2007 1:35    Post subject: SQLServer Reply with quote

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)
Back to top
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Thu Feb 01, 2007 4:29    Post subject: Re: SQLServer Reply with quote

lostdreamz wrote:
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)


Working on an ODBC solution - which will include SQLServer access.
--conditional on my Vista installation allowing continued development.

Cheers
Gryphyn
Back to top
View user's profile Send private message
tayls25



Joined: 16 Dec 2006
Posts: 5

PostPosted: Tue Feb 06, 2007 14:34    Post subject: Re: SQLServer Reply with quote

Gryphyn wrote:
lostdreamz wrote:
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)


Working on an ODBC solution - which will include SQLServer access.
--conditional on my Vista installation allowing continued development.

Cheers
Gryphyn


For NWNX4? (crosses fingers)

I'm so looking forward to NWNX4 support for MSSQL. It was so powerful in NWN1, really gave us a lot of option and flexibility with design. Without the ODBC support, I haven't yet been able to use NWNX4 for anything more than watchdogging the NWN2 process yet. Thanks for all your hard work on this component, it's just such an innovative thing you guys do, and gives those of us running PW's so many options. We already run MSSQL for our forums and other stuff, so it doesn't make much sense to siphon off resources to a secondary DB engine, so when there's an ODBC solution, Iniquity will be coming on board with NWNX4. Very Happy
Back to top
View user's profile Send private message
Papillon
x-man


Joined: 28 Dec 2004
Posts: 1059
Location: Germany

PostPosted: Wed Feb 07, 2007 20:09    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Wed Feb 07, 2007 23:21    Post subject: Reply with quote

Papillon wrote:
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!

I was intending basic ODBC support, as provided with wxODBC. This should allow it to remain compatible with other plugins using wxWidgets.

Cheers
Gryphyn
Back to top
View user's profile Send private message
lostdreamz



Joined: 19 Nov 2006
Posts: 26

PostPosted: Sat Feb 10, 2007 1:06    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message
Grumalg



Joined: 04 Nov 2005
Posts: 70

PostPosted: Sat Feb 10, 2007 14:08    Post subject: Reply with quote

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 ---
Back to top
View user's profile Send private message
Grumalg



Joined: 04 Nov 2005
Posts: 70

PostPosted: Sat Feb 10, 2007 15:01    Post subject: Reply with quote

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 ---
Back to top
View user's profile Send private message
lostdreamz



Joined: 19 Nov 2006
Posts: 26

PostPosted: Sat Feb 10, 2007 15:14    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Grumalg



Joined: 04 Nov 2005
Posts: 70

PostPosted: Sat Feb 10, 2007 15:54    Post subject: Reply with quote

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

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 ---
Back to top
View user's profile Send private message
lostdreamz



Joined: 19 Nov 2006
Posts: 26

PostPosted: Sat Feb 10, 2007 15:58    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Grumalg



Joined: 04 Nov 2005
Posts: 70

PostPosted: Sat Feb 10, 2007 16:29    Post subject: Reply with quote

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 ---
Back to top
View user's profile Send private message
lostdreamz



Joined: 19 Nov 2006
Posts: 26

PostPosted: Sat Feb 10, 2007 16:38    Post subject: Reply with quote

Honestly I would still rather have infrequent poor performance than loss of data. I just hope the support for SQL Server comes soon
Back to top
View user's profile Send private message
Grumalg



Joined: 04 Nov 2005
Posts: 70

PostPosted: Sat Feb 10, 2007 18:02    Post subject: Reply with quote

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 ---
Back to top
View user's profile Send private message
tayls25



Joined: 16 Dec 2006
Posts: 5

PostPosted: Sat Feb 10, 2007 18:33    Post subject: Reply with quote

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


I gotta agree here. I also don't want to run a secondary database engine on my server, when I already have other applications running reliably and with stability under MSSQL. I sure hope SQL Server support comes soon too. From my own perspective, I trust it the most running on a windows platform, which I believe is the only OS NWN2 is available for, right?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related All times are GMT + 2 Hours
Goto page 1, 2, 3, 4, 5, 6  Next
Page 1 of 6

 
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