| View previous topic :: View next topic |
| Author |
Message |
RamaDeveloper
Joined: 19 Mar 2009
Posts: 13
Location: UK
|
 Posted: Thu Mar 19, 2009 10:58 Post subject: |
 |
|
| Grumalg wrote: |
SQLExecDirect("COMMIT");
SQLExecDirect("BEGIN");
|
Gosh, I'd imagine that would cause all sorts of problems, a SQL transaction really needs to be started and completed or rolled back within a single stored proceudure, there could be a fair gap between those calls especially if the NWN process is hard at work. I'm not even sure that will produce a proper transaction, it's not supposed to work like that for sure - like reaching the front of the queue at a shop, plonking your shopping on the counter and then wandering off to get some sugar, if every customer doers that, the people in the queue behind will get increasingly unhappy and the queue will grow !
If your transaction needs to be that wide-ranging there's probably something awry with the DB or query design, surround chunks of stored procs with transactions and avoid cursors like the plague, use temporary tables or CTE expressions or something else that allows you to operate on the data as a set rather than via a cursor.
_________________
Chris |
|
| Back to top |
|
 |
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Thu Mar 19, 2009 11:50 Post subject: |
|
|
| RamaDeveloper wrote: | | Grumalg wrote: |
SQLExecDirect("COMMIT");
SQLExecDirect("BEGIN");
|
Gosh, I'd imagine that would cause all sorts of problems, a SQL transaction really needs to be started and completed or rolled back within a single stored proceudure, there could be a fair gap between those calls especially if the NWN process is hard at work. I'm not even sure that will produce a proper transaction, it's not supposed to work like that for sure - like reaching the front of the queue at a shop, plonking your shopping on the counter and then wandering off to get some sugar, if every customer doers that, the people in the queue behind will get increasingly unhappy and the queue will grow !
If your transaction needs to be that wide-ranging there's probably something awry with the DB or query design, surround chunks of stored procs with transactions and avoid cursors like the plague, use temporary tables or CTE expressions or something else that allows you to operate on the data as a set rather than via a cursor. |
This is one of the main problems with the NWNX 'transaction model' it relies on 'committing' the last transaction just before starting the next. It has to do with how the MySQL plug-in was developed. On a crash the very last transaction is lost.
One of the things I did with the SQLServer plug-in was to remove this vulnerability. A COMMIT is fault-tolerant, it won't fail if there is no active transaction, and if you've forgotten to add it - it will kick in when needed.
While I've made SQLServer APS compatible (so you don't have to rewrite all the 'persistent' systems out there) the re-coded interface offers the ability to simplify NWScripting, while at the same time providing the complete ODBC API (almost).
Cheers
Gryphyn |
|
| Back to top |
|
|
RamaDeveloper
Joined: 19 Mar 2009
Posts: 13
Location: UK
|
| Posted: Thu Mar 19, 2009 13:58 Post subject: |
|
|
| Gryphyn wrote: | | One of the things I did with the SQLServer plug-in was to remove this vulnerability. |
I must check out your SQL plug-in, I've been using a tweaked version of the aps_include code - in fact, I must upload my performance tweaks for the original SQLFetch etc - I get another 50% throughput out of it, especially for large result sets though maybe it's been done before, I have some backreading to do !
On the whole transaction business, I'd be very wary of starting a transaction when the commit isn't in the same stored proc and coming up any millisecond now, starting a transaction on the basis that it'll be committed by the next bit of code to saunter along at some time in the future is going to cause lots of headaches surely.
(forgive me pouncing in, new member and pretty new to the NWNX interface and all that but I do high performance SQL professionally and am keen to share and glean :^)
_________________
Chris |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Thu Mar 19, 2009 21:43 Post subject: |
|
|
| RamaDeveloper wrote: | | (forgive me pouncing in, new member and pretty new to the NWNX interface and all that but I do high performance SQL professionally and am keen to share and glean :^) |
You ain't seen a database dance, until you've seen one I've been dealing with  Express @4Gb is nothing on the Pb databases I work with.
Pounce away! there is always more to learn. |
|
| Back to top |
|
|
RamaDeveloper
Joined: 19 Mar 2009
Posts: 13
Location: UK
|
| Posted: Thu Mar 19, 2009 23:57 Post subject: |
|
|
| Gryphyn wrote: | | there is always more to learn. |
Absolutely - that's the 'glean' bit and why I'm here to pick your brains :^) - I'll do some backreading and download your splendid looking addon. I like the ideas I read in the document particularly about multiple channels and spotted a mention of supporting mutiple returned recordsets - always after shaving a few more cycles off my code so sounds handy.
Having said that, now I can write c# assemblies and call via the marvellous .Net plugin... arggh, can't recall name of author - anyway, I'll be doing a lot more loading of static data into in-memory structures for quick retrieval with no call to SQL.
Great to find a place to natter about this stuff - my players certainly aren't interested !!! (scuse off topic glee)
What's 'Pb' by the way ?
What I was on about with transactions though - to my understanding, the point of the transaction model is to allow a series of atomic processes to be treated as a single atomic process, either they all happen or none of them happen.
It's not about securing data to the database, that happens anyway, arguably sooner and more efficiently without a transaction but if there is a slight performance hit then we take it gladly in return for the assured data integrity so for example, it's impossible to add a debit to one bank account without also adding the associated credit to the other account as that would be very bad for ones contract renewal chances ;^)
_________________
Chris |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Fri Mar 20, 2009 7:07 Post subject: |
|
|
| RamaDeveloper wrote: | | What's 'Pb' by the way ? |
The next step up from Tb - Giga, Tera, Petra
As to the .NetPlugin (last time I looked) it leaked memory and foobared the Garbage Collector. Not right away of course...but the longer you left it the worse it got.
I was looking into C++ mixed assemblies (.NET with a native entry point)
getting all the delegates to work was easy - getting rid of the double thunking was another matter. My attempts were to rework the core so that the 'simple' plugin architecture of .NET could be used, rather than a plugin 'bridge' to .NET
Then along came detours...which handles 'managed' code as well, didn't get my head around it before moving on to other things. |
|
| Back to top |
|
|
RamaDeveloper
Joined: 19 Mar 2009
Posts: 13
Location: UK
|
| Posted: Fri Mar 20, 2009 10:32 Post subject: |
|
|
| Gryphyn wrote: | | The next step up from Tb ...Petra |
Ahhh, one step down from 'Rex-a-byte'
| Quote: | | "As to the .NetPlugin (last time I looked) it leaked memory and foobared the Garbage Collector. Not right away of course...but the longer you left it the worse it got. |
Thanks for the tip, that *would* be inconvenient, I'll have to set up a test of some kind and see if that's still the case
| Quote: | | I was looking into C++ mixed assemblies..... |
Looks like you've been nibbling at this problem for a while, perhaps a tiny pipes/tcpip client as a plug-in calling a local windows service that provides all the interfaces required - decoupling like that could help, though if it's too much like hard work I'll probably revert to my original plan to call assemblies from SQL - one way or another if I have to code much more in the NWN development environment I'll start killing and possibly never ever stop !!!
_________________
Chris |
|
| Back to top |
|
|
metamud
Joined: 30 Sep 2008
Posts: 16
|
| Posted: Sat Mar 21, 2009 10:02 Post subject: |
|
|
| Gryphyn wrote: | | RamaDeveloper wrote: | | What's 'Pb' by the way ? |
The next step up from Tb - Giga, Tera, Petra |
Actually, that would be Peta (no r). |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Sat Mar 21, 2009 12:32 Post subject: |
|
|
| metamud wrote: | | Gryphyn wrote: | | RamaDeveloper wrote: | | What's 'Pb' by the way ? |
The next step up from Tb - Giga, Tera, Petra |
Actually, that would be Peta (no r). |
Teach me for trying to type with more than two fingers |
|
| Back to top |
|
|
Sable
Joined: 24 Dec 2008
Posts: 10
|
| Posted: Mon Apr 13, 2009 15:03 Post subject: |
|
|
| RamaDeveloper wrote: |
| Quote: | | I was looking into C++ mixed assemblies..... |
Looks like you've been nibbling at this problem for a while, perhaps a tiny pipes/tcpip client as a plug-in calling a local windows service that provides all the interfaces required - decoupling like that could help, though if it's too much like hard work I'll probably revert to my original plan to call assemblies from SQL - one way or another if I have to code much more in the NWN development environment I'll start killing and possibly never ever stop !!! |
Can you really not just do it all in a stored proc and invoke the proc from within NWN? There really are few if any data manipulation functions you need to step outside of SQL to perform |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Mon Apr 13, 2009 17:04 Post subject: |
|
|
| Sable wrote: | | Can you really not just do it all in a stored proc and invoke the proc from within NWN? There really are few if any data manipulation functions you need to step outside of SQL to perform |
The recent discussions here are about the 'plugin' itself. (ie the DLL)
I set up the proof of concept for NWN2, however I never got into backend stuff with all the 'game-play' issues with the sequel.
I re-engineered the NWNX^2 plugin code to enable greater functionality when using NWScripting to utilise a SQLServer database. Getting this to a NWNX^4 version still needs the 'interface' bits to be re-worked.
Cheers
Gryphyn |
|
| Back to top |
|
|
Dayhawk
Joined: 21 Nov 2009
Posts: 2
|
| Posted: Sat Nov 21, 2009 11:47 Post subject: |
|
|
First off I want to say nice work on the plugin!
I have a test module working with the MS 2008 SQL server, but when I use the function SQLFetchString, it only returns the character from the value column. In the log you can see that the entire value was retrieved.
#include "nwnx_sqlmode"
void main()
{
object oPC = GetEnteringObject();
string sResult = SQLFetchString( oPC, "MyKey");
SpeakString( "Value Returned " + sResult, 0 );
}
any idea's why? |
|
| Back to top |
|
|
Fireboar
Joined: 17 Feb 2008
Posts: 323
|
| Posted: Sat Nov 21, 2009 14:12 Post subject: |
|
|
| That would be the way SQLFetchString works. I'd avoid that personally and just work with SQLExecDirect calls, with your own database structure. Far more efficient, just requires a working knowledge of SQL. |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Sat Nov 21, 2009 23:44 Post subject: |
|
|
| Dayhawk wrote: | First off I want to say nice work on the plugin!
I have a test module working with the MS 2008 SQL server, but when I use the function SQLFetchString, it only returns the character from the value column. In the log you can see that the entire value was retrieved.
#include "nwnx_sqlmode"
void main()
{
object oPC = GetEnteringObject();
string sResult = SQLFetchString( oPC, "MyKey");
SpeakString( "Value Returned " + sResult, 0 );
}
any idea's why? |
There should be no truncation. The only thing I can think of at the moment is a #0 character embedded in the string (as can happen with UCS-2).
I'll need to dust off my old dev box (RIP) and reclaim the HD to have a look (I was in the process to adopting the new VS code-branch, - which doesn't (didn't for me) work with a direct export & build)
It'll be a week or two before I can have a decent look.
--I'll upload the latest build we are using on nailaeon. (it's had a few tweaks) but this is a core function (and won't have changed)
Cheers
Gryphyn |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Sat Nov 21, 2009 23:59 Post subject: |
|
|
| Fireboar wrote: | | That would be the way SQLFetchString works. I'd avoid that personally and just work with SQLExecDirect calls, with your own database structure. Far more efficient, just requires a working knowledge of SQL. |
This is for SQLServer. The underlying functionality of the the 'plugin-interface' has been re-engineered. aspmode & sqlmode are both wrappers around this new functionality. (this includes SQLExecDirect)
The guts of it exposes most of the ODBC API (read ODBC API doco).
It all but eliminates that messy 'string building' of the original source as it fully utilizes 'parameters' when talking to the database (better DB response/caching)
Oh, and objects can be stored/fetched in position. Get both the item data and the item object in the same row of a result set (select).
Cheers
Gryphyn |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
