NWNX4/MySQL 5.0.x Stored procedure issue

[Grumalg]

I have been working with stored procedures with NWNX4 and MySQL 5.0 and have run into a snag. I have a known working proc that I can exercise in MySQL query browser and it does exactly what I intended. I can even create the proc in the DB via NWScript/NWNX4 and once created in this fashion it performs properly in the query browser. This is clearly not a security issue as I'm logging in to MySQL Query Browser with the same account NWNX4 is logging into the DB as.

However, when I 'CALL' the proc with NWNX4 SQLExecDirect() I get the message:

! Execute / mysql_query: PROCEDURE nwn2.nwnlogin can't return a result set in the given context.

After some research in the MySQL forums I found a reference that this error results from a lack of CLIENT_MULTI_STATEMENTS and/or CLIENT_MULTI_RESULTS flags being set when connecting to MySQL. Going back to the MySQL 5.0 doc pdf I found this (on page 1283):

<snip>
If your program executes stored procedures with the CALL SQL statement, you must set the CLIENT_MULTI_RESULTS flag, either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS when you call mysql_real_connect(). This is because each CALL returns a result to indicate the call status, in addition to any results sets that might be returned by statements executed within the procedure.

If you enable CLIENT_MULTI_STATEMENTS or CLIENT_MULTI_RESULTS, you should process the result for every call to mysql_query() or mysql_real_query() by using a loop that calls mysql_next_result() to determine whether there are more results. For an example, see Section
22.2.9, “C API Handling of Multiple Statement Execution”.
<\snip>

My read of this is that when calling a proc you *always* get at least two result sets back, one containing the status and one (or more depending upon the proc) containing the returned data. I'm guessing that xp_mysql.dll isn't handling this correctly since every reference in the MySQL forums always present the two CLIENT_MULTI_ flags as the issue for the above error message. The use of these flags is due to compatibility issues with earlier versions of MySQL as they didn't return multiple result sets.

I suspect it might break normal use of SQLExecDirect() to implement this. If this is the case, perhaps something like a SQLExecute() function might be required that handles the multiple result set case. Perhaps an additional seperate DB connection specifically for procs that handles the multi result set case might be required. Since stored procs would be extremely useful and valuable to minimize trips to the database for performance reasons NWNX4 should support the use of procs if at all possible.

<edit added> another snip that may shed light on the issue (page 1293 of mysql doc pdf)

Enabling multiple-statement support with MYSQL_OPTION_MULTI_STATEMENTS_ON does not have quite the same effect as enabling it by passing the CLIENT_MULTI_STATEMENTS flag to
mysql_real_connect(): CLIENT_MULTI_STATEMENTS also enables CLIENT_
MULTI_RESULTS. If you are using the CALL SQL statement in your programs, multiple-result support must be enabled; this means that MYSQL_OPTION_MULTI_STATEMENTS_ON by itself is insufficient to allow the use of CALL.

--- Grumalg ---

[Papillon]

Can you please send me a create statement for a simple example stored procedure that I can test with ?
_________________
Papillon

[Grumalg]

ok, here's a simple classic for you. Note that this form for the code is only executeable in the query browser. Creating such a proc in NWScript requires a somewhat different syntax which isn't documented and I had to find by trial and error. The below code must be placed in a proc by selecting the 'Script' menu in the query browser and selecting 'Create Stored Procedure/Function' then pasteing the below code over the template that will appear.

DELIMITER $$

DROP PROCEDURE IF EXISTS `nwn2`.`helloworld` $$
CREATE DEFINER=`root`@`%` PROCEDURE `helloworld`()
BEGIN
SELECT 'Hello World!';
END $$

DELIMITER ;

to run the proc paste the below in a normal query window and execute it or test by useing the below like a SQL string with SQLExecDirect() to see the error.

CALL nwn2.helloworld()

I suppose I should note that the account creating/calling any proc must have the CREATE ROUTINE privilege.

I sure you wouldn't have wanted the proc I was actually working with as it had 6 input params, operated on three tables conditionally doing an assortment of SELECT, INSERT, and UPDATE operations and returned a single row result set with 13 fields mixed between table data and internally set status flags. Took about 60 lines of proc scripting...

--- Grumalg ---

[Papillon]

No, the original procedure would probably have been too much.

Thanks for the example, which spares me the effort to learn the MySQL syntax.
_________________
Papillon

[Papillon]

The same does not only apply to stored procedures, but also to any statement like in their example:

[Gryphyn]

[Grumalg]

Der Phobos: I haven't played with exposeing a server to gamespy with nwn2 yet, I have no help to offer.

Papillon: When I posted the sample proc, I had gotten out of bed to 'walk my lizard' and happened to check for a response before returning to bed. The time difference between us is fierce as I'm in Los Angeles. I was a tad groggy, and didn't mention everything you might run into to create/exercise a proc in the above post. Your "spares me the effort to learn the MySQL sxntax" comment leads me to believe I should explain a bit more as both you and other readers might find it useful.

In the sample "helloworld" proc you'll notice a DELIMITER wrapper around things. This wrapper is needed because MySQL normally uses ';' by default as a command delimiter. Since procs also use ';' as a statement delimiter this would conflict. So the DELIMITER wrapper temporarly changes MySQL's delimiter (in the example to $$) and then changes it back to avoid conflicts.

In the CREATE you will see a DEFINER = clause. This is the MySQL login account owner of the proc. In my sample the query browser defaulted to the account I was logged in as at the time. Since I was logged in as a remote root user 'root'@'%' was used as the definer as I was running the query browser from a different machine than MySQL was running on. If you were logged in locally the definer should have been 'root'@'localhost' if logging in as administrator. You can also specify a specific user as the definer or use DEFINER = CURRENT_USER. In any case whatever account is used as the definer *must* have the CREATE_ROUTINE privilege to avoid security errors. By default MySQL only gives the 'root'@'localhost' account the CREATE_ROUTINE rights. Any other account used must have been specifically granted the right. The creator of a proc automatically inherits the rights to delete, modify, or run the procedure. You can also set specific rights per account so an account might be able to run a proc, but not create, delete or modify it.

In the PROCEDURE clause of the CREATE statement it's not a bad idea to specify the DB to create the proc in as in PROCEDURE <dbname>.'<procname>'. Without specifing the DB name I have had the query browser create a proc in the mysql DB at times, and in the nwn2 DB at times, so specifing the DB name lets you make sure where it goes.

I neglected to mention before that once you have the proc code in a query browser window you have to 'execute' it with the same button used to execute normal commands in order to actually create the proc. If successfully created (no error occured) the proc will then appear in the normal table list of the DB it was created in with a different icon than tables use and is ready to be exercised. A double-click on the proc in this list will put a CALL template into to command window of the query browser.


I was aware that the issue wasn't limited to procs and also applied to multiple statements, it was just that procs were my current obstacle. While thinking about this stuff a number of ideas occured to me. The following long discussion of these is not intended to try to tell you what to do, but to present some thoughts that may inspire you to your final solution. While I do have a lot of experience with a number of database engines my experience with MySQL is entirely due to NWN and NWNX. Thus, I'm very aware I don't know everything.

One big unknown to me is whether enableing the MULTI flags on the DB connection will cause compatability issues with the normal NWNX functions, especially when talking to MySQL 4.x. The MULTI stuff was added in one of the earlier 5.0.x versions. If there is a compatability problem from useing MULTI stuff on the connection to normal nwnx functions there are a couple of possible approaches. You could have two DB connections one without and one with MULTI enabled and use the one without for normal nwnx functions. If MULTI doesn't conflict, then perhaps a ini option could specify whether the user wanted MULTI on the connection(s) or not, something like a DBType= mysql 4 or 5.

If you do need to use multiple connections this should not be a problem at all. A single app useing many connections is common in the DB world and configureing MySQL to have a big connection limit is trivial. Nor would multiple nwserver instances in the case of a PW split across servers as NWN2 is going to require for a large world be an issue. MySQL can easily handle far more connections and traffic than would be used in such a situation. However, since creating a connection is very time expensive compared to just querying the engine you probably shouldn't allow users to dynamically create and destroy connections in NWScript. Perhaps you should initially create a pool of connections (something like 3-5 connections) as some interesting and useful things would fall out of having such a connection pool.

For example if you had ini options to specify server and login values for each possible connection, one could actually connect to more than one MySQL server engine or multiple databases in a single engine. This could have some interesting uses for PW's such as running a local MySQL on each server for better performance or carrying data for web site status display in a seperate MySQL server or instance. If could also be useful in cases where replication between databases was desired. I imagine the community would find a lot of interesting ways to use this capability.

Another fallout would be the issue I have seen requested to have more than one query result with multiple records available for doing things like looping through one recordset in an outer loop and another recordset inside the loop. I did see an earlier post you made about this in NWNX4, but didn't examine it in detail at the time as MySQL in NWNX4 wasn't available at the time. You may already be doing something like this, but having multiple connections would surely make it possible to do things like this easily.

End user manipulation of multiple connections could be handled by adding a parameter to the low level NWNX functions such as:

SQLExecDirect(<connection>, <command>)
SQLFetch(<connection>)
SQLGetData(<connection>, <fieldnumber>)

Admittedly adding a parameter *will* break legacy code useing the low level functions, but I argue that those already useing the low level nwnx functions are already knowledgeable enough to find the changeover trivial (search and replace is your friend!). To those who only use the Get/Set persistant stuff and accept its performance limits such a change would be invisible as you would have already incorporated it inside the Get/Set functions. Since I suspect most nwnx users use only the Get/Set functions the impact of such parameter changes would be less than you might think. Overall I believe there is great potiential and flexibility in such an approach.

Now comes the crux, i.e. how do you squeeze all those connections into the single hooked string var and keep each one independently positionable within it's own recordset? While I haven't dug into the innards to see exactly how you are handling it now, I did see the posts about delimiter char problems before. It's very likely you are already useing something very similar to the following description. For the purposes of discussion I will use single quote as a field delimiter, comma as a field seperator, and vertical bar as a connection seperator. In actual use you'd select some chars for delimiters that didn't impact the free passage of strings of printable chars. Since this scheme would only require 3 delimiter chars, you should be able to find that many. A likely place to look is in the first 32 char codes (the 'control' set). Ok, picture the hooked string containing something like this:

"C1_F1","C1_F2","C1_F3"|"C2_F1","C2_F2","C2_F3"|"C3_F1","C3_F2","C3_F3"

A call to SQLExecDirect(<connection#>, <command>) would use it's appropriate connection and buffer the resultset inside the dll. Each connection in the dll would have it's own buffered 'current row' string or track which part of the resultset was current. When SQLFetch(<connection#>) was called it would reassemble the whole string concatenation useing the previous contents of other connections and it's own new string section. This would allow each connection to be independently positionable within it's own recordset data without disturbing the other connections current row data state. SQLGetData(<Connection#>, <field#>) would know to only use the "|" delimited section of the string appropriate for it's connection. The larger size of the total string shouldn't be as issue as previous object handling passed quite large strings around, In fact, you *could* implement object handling as just one more connection section in the string without overwriting the whole string contents. That would allow always being able to rely on whatever you left in the big string before still being available for use in another script after the current script had finished and exited since the big string is a module var.

The fact that SQLGetData() was '1' based before for field indexes makes me suspect you may have been useing the equivalent of the '0' field for internal state information. If you haven't there's some nice stuff that could be done if you did use a header section in the big string. For example, the dll code could build a header indicating start char position and length for each connection section in the string. This would allow directly pulling out a individual connection's string section without needing to examine every char. Similar techniques could be used within connection sections for field start/length. This would allow moving some of the string parseing load out of the nwn engine into the dll where it would be a different process/thread. SQLGetData could then use these start/length values to directly GetSubstring(start, length) instead of searching the strings to find delimiters. In fact, with good headers use of delimiters could be eliminated entirely.

Well I imagine I've bent your ear enough by now. Hope some of the above is useful or thought provoking for you

--- Grumalg ---

[Lamancha]

It's resurecting an old post, I know, but I'm not clear as to the solution.

I'm using NWNX2 but am getting the same issue with Stored Procedures containing Select Statements.

Is there a way forward?

[Skywing]

One possible solution might be to write a CLR-based script and use one of the fully-featured MySQL clients for CLR-based code (like MySQL Connector/.NET).