Thoughts on multiple connections

[Grumalg]

Having comtemplated the multiple connection issue a bit more, I find some things I don't like about your proposed approach and I have some ideas for another approach. Now I may not be seeing your whole intended picture yet, so it's surely possible I'll wind up off base here...

< Danger Will Robinson... annoying lecture mode = ON >

[Papillon]

A short note beforehand: The current approach was not meant to be the most elegant way to support multiple DB connections. It just came almost for free and that's why I took it. I wanted to have an answer for this problem, which came up 2 or 3 times with NWNX2.

I did not think everything through, but your idea sounds reasonable. There are some open detail questions (like e.g. how the mapping between account data and connection string would work), but all-in-all, this would be the way to do it.

I wonder if it's worth it, though. There is probably only a very small percentage who would want multiple DB connections. It is not something the average user will need - if the queries get that complex, that you can not solve your problem with one DB connection (never happened to me, personally), your above "average" and could very well setup the current solution.

So, bottom line: I think the idea is good, and might implement it sometime. Right now, there are a lot of tasks that are a bit higher on my priority list, so please do not expect an implementation in the neat future. Of course, NWNX is open source, so...
_________________
Papillon

[Grumalg]

Well, to date I've never needed multiple connections myself. Nor did I expect them to be high on your priority list. I'm not pushing for you implement them soon, just discussing ways it could be done.

In my experience with various groups of programmers, bounceing around ideas in more than one head almost always leads to a better solution. My suggested approach wasn't intended to be a total solution. It was intended to show a framework I thought could work and that might stimulate you to an better final implementation.

I agree that the desire for multiple connections hasn't been a *massively* frequent request, but if they are simple enough to use that could change. They certainly *are* a powerful tool to have.

Beyond the ability to work with two recordsets while looping I have often seen asked for on this forum. I can think of a additional use where multiple connections could help a lot. I have seen discussion in the forum about handling large recordsets and avoiding TMI. Typically useing delay command recursion and things like LIMIT in SQL. Multiple connections could simplify this a lot as you could rely that the data initally requested was still around in a seperate connection AND still positioned on the current record each time you recurse a delay command routine. It would also avoid concerns in such use that some other event got between the delay commands and overwrote the recordset.

If I wanted to dust off my old C skills and get up to speed in C++, I *might* be able to do such things. But I'm *really* not interested in getting into a position with nwnx like the fast french stuff was. I'm content to wait for an official release from you, just like I'm waiting for a proc solution.

While waiting I have a lot of time to think about my wild haired ideas and toss them at you.

--- Grumalg ---

[Papillon]

Well, what FastFrench did was not very productive, since he basically took everything, modified some things here or there, and sticked his own label on NWNX. That did not help NWNX as much as it would have if he had contributed some code back.

So if you create something of general interest for NWNX, it could be integrated, rather than creating a fork that just doubles everybody's work. The source is available, and if the contributions come back in a form that allows them to be put easily into NWNX, I will be hapy to do so.
_________________
Papillon

[The Bus Driver]

I have run into situations where multiple connections would have been helpful, but I got around it by storing previous data as local variables. It would be much cleaner to have multiple connections, but certainly not mandatory. Most problems like this could probably be solved by using a more complicated SQL statement. Because your software is open source, I see no reason why another developer couldn't provide a plugin for it or some type of modification to support multiple connections.

PHP allows multiple connections by specifying an optional boolean in the connect function. Any request to close a connection must then specify the link identifier to close out that connection. I'm not sure when NWNX4 connects to the database, but I assume it connects when the program starts rather then each time a SQLExecDirect() statement is made (unlike PHP which can make new connections at will). I only state the way PHP handles multiple connections to possibly give some ideas into how it could be handled in NWNX4.

[Papillon]

The mother of all non-elegant solutions in NWNX is indeed a simple one: No pointers. NWNX does what it does by simply passing one string from NWScript to the rest of the world. There is no such concept as pointers or references (at this point of time).

All other solutions have the luxury of "designing" a solution to a known problem. NWNX can just work around the known quirks .

Before I start rambling further, let me just say that in theory, everything is possible with NWNX. If someone feels like it, a plugin which accesses 10 Oracle servers, with 25 databases, with 512 tables, a whole-lot-of stored procedures, and even more queries returning a absolutely amazing number of resultsets is - easy.

If someone sits down and codes it.
_________________
Papillon

[kungfoowiz]

Heh, it does sound a bit much to do it for a game plugin.

Though, I wouldn't mind doing a small-ish (say 4 or so) array of connections for the MySQL plugin, if there's a need for it?

You'd then specify in SQLExecDirect( STRING sQuery, INT nConnectionID = 1 );

Something like that.

---
kung

[Grinning Fool]

Similarly, an unlimited number could be supported by using a hashmap -- where the user supplies a name for each connection, and this is used as a key to retrieve the actual connection data.
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.