1.06 better but procs still bugged.

[Grumalg]

Thank you for trying to make procs work Proc support is much closer, but still has a bug. I'm getting "Commands out of sync; you can't run this command now" messages in xp_mysql.txt. Some procs don't result in the message and others do. Since I have a proc that results in the message and one that doesn't and both are handling a SELECT followed by conditional UPDATE or INSERT. I note that the one that fails has 2 conditional UPDATE/INSERT sections for different tables and may be returning more results sets. After searching docs to make sure I wasn't doing something wrong all the references to this error point to useage order etc. of mysql api functions or mysql lib versions.


I'm grabbing these references from the MySQL 5.0 pdf. The one that says "This is the MySQL Reference Manual. It documents MySQL 5.0 through 5.0.28." on pdf page 2 (no page number on page). These snips are only what appears to be applicable paragraphs from the listed section.

under 2.3.16. Upgrading MySQL
snip on pdf page 177 (156 # on page)

If, after an upgrade, you experience problems with recompiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used old header or library files when compiling your programs. In this case, you should check the date for your mysql.h file and libmysqlclient.a library to verify that they are from the new MySQL distribution. If not, recompile your programs with the new headers and libraries.


under 22.2.3.70. mysql_use_result()
pdf page 1289 (1277 # on page)

When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows are returned as part of the result set for your next query. The C API gives the error Commands out of sync; you can't run this command now if you forget to do this!


pdf page 1731 (1710 # on page)
under B.2.13. Commands out of sync

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order. This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in
between.

--- Grumalg ---

[Papillon]

I think this might have something to do with this:

[Grumalg]

Ok, none of the procs I'm working with to date ever return more than one row of data from a single SELECT at the end. All other internal SELECT useage is all of the form SELECT INTO and stashing stuff in local variables inside the proc.

I don't believe the SELECT INTO to locals results in any returned data, at least nothing of the sort ever shows up when the proc is exercised in the query browser.

I am probably triggering a number of status results though, due to useing a number of SQL commands that don't normally return any data. I was expecting the intermediate status results to be 'eaten' inside the SQL plugin as I never see any indication of them in the query browser.

I'll try a fetch loop and log what it finds and look for clues to what's happening that way.

I've certainly never had any such problems working for MSSQL server in T-SQL despite doing things as complex as quadruply nested cursor loops though...

Since it's clear that no version of NWNX prior to 1.06 could handle procs at all I guess I'm just out at the 'bleeding edge'... Now where did I leave those band-aids?

<edit added> RE: multiple connections..

It's not the case that I have any personal desire for them. But I have read *many* requests by other's that they would solve.

(1) requests to connect to more than one DB engine instance/server
(2) requests to connect to more than one DB schema
(3) requests to work with one set of records while looping through another set of records

Since implementing multiple connections would solve *all* those requests it just looked to me like a lot of bang for the dev time of a single feature. That's the whole point I was trying to make...

--- Grumalg ---

[ratgutz]

[Grumalg]

Well I sat down to figure out what I should do to test this. But...

All my reading and thinking leads me back to the point that 'Commands out of sync' should never be an end user's problem. The SQL plugin should never allow the condition causing this error message to exist. If for no other reason than never letting itself get into an unuseable state no matter what the end user does.

Given use of current mysql lib's, the 'Commands out of sync' error has only one cause. It is issued when the DB engine's buffer for the connection still contains results from last command that the client (SQL plugin) hasn't fetched yet and a new command is sent by the client. The SQL plugin could prevent this from ever occuring by always looping fetching the results of a command till none are left in the DB engine's buffer. This means all the results must be buffered by the client after each command is issued to the DB engine so they are available for subsequent SQLFetch() or SQLGetData() operations.

I know MySQL is open source. If the query browser is too, you might be able to find some clues in their code to see how the multi-result case is properly handled. Depending on how complex a multi-command or proc may be there could be a lot of results to fetch, mixed between 'status' and 'data' results in any order.

The proc I am getting the 'Command out of sync' error with only returns one data result (a single row of data). The proc works perfectly in the MySQL query browser, returning only one row of data and one status message "1 row fetched in 0.0056s (0.0588s)". When I execute the proc in the MySQL command line client, what I get is one row of data with field names and returned data in the usual char boxed format and two messages "1 row in set (0.05 sec)" and "Query OK, 0 rows affected (0.05 sec)".

I don't see how I can improve anything by trying a SQLFetch() loop unless SQLFetch() works on status results, which I don't think it does. All the comments in your code sample refer to 'rows', I have no 'rows' I only have one row. If SQLFetch() is actually intended to iterate through both status and data results, then your code has a point. But forceing end user handling of status results is sub-optimal at best. Far better would be for SQLFetch() to fetch only data results in keeping with all historical use of SQLFetch().

I suppose I should also note that though my proc doesn't, other multi-commands or procs could return a number of data results mixed in with the status results that could each contain different number of fields. To truely support multi-commands and procs you'll need to able to handle all cases.
--- Grumalg ---

[Papillon]

So, did you try my code ?
_________________
Papillon

[Grumalg]

Ok, I pasted your code into a module. Fixed one missing semi in it, and wrapped it in a for loop set to run it 3 times. When it ran it caused 2 'Command out of sync' errors, which is exactly what I expected...

This is because the first time it runs the plugin leaves some leftover resultsets in MySQL's buffer for the connection, then the next two attempts to execute a command fail. You'll never see this error till the next attempt to run a command because there aren't any leftover resultsets in the buffer till it's been run once.

This is the code I ran....

[code]
#include "sql_include"

void main()
{ int nCount;

for (nCount = 0; nCount < 3; nCount++) {
SQLExecDirect("DROP TABLE IF EXISTS test_table; CREATE TABLE test_table(id INT); INSERT INTO test_table VALUES(10); UPDATE test_table SET id=20 WHERE id=10; SELECT * FROM test_table; DROP TABLE test_table");
while (SQLFetch() == SQL_SUCCESS) {
// do something with the rows from the first resultset, if there are any
WriteTimestampedLogEntry(SQLGetData(1));
}
while (SQLFetch("NEXT") == SQL_SUCCESS) {
do {
// do something with the rows from each individual resultset, if there are any
WriteTimestampedLogEntry(SQLGetData(1));
}
while (SQLFetch() == SQL_SUCCESS);
}
}
}
[/code]

Here's the contents of xp_mysql.txt

[code]
NWNX MySQL Plugin V.0.0.1
(c) 2006 by Ingmar Stieger (Papillon)
visit us at http://www.nwnx.org
(built using mysql-5.0.27 source)

* Connecting to server localhost
* Plugin initialized.
* Registering under function class SQL
! Error (mysql_query): Commands out of sync; you can't run this command now.
! Error (mysql_query): Commands out of sync; you can't run this command now.

[/code]

The reason this problem exists and how to fix it was described in my last post....

--- Grumalg ---

[Papillon]

[Papillon]

[Grumalg]

First off, you may always be assured I am never attempting to force any design decision on you. When I discuss things it's just much easier to present them in specific terms to clarify a point. I'm not bitching, complaining, or demanding. I'm reporting and commenting on what I see and how I interpet it, in the hope I have something to say you find useful.

While I did grab VS 2005 C++ express, Tortise, and your source, and poked around a bit in the source, C++'s object/class stuff is greek to me. I did work in C back in the 80's with unix running on mini's (PDP, VAX) talking over 9600 baud monochrome 'dumb' terminals via command line interface instead of a GUI. Since this was well before the ++ was added to C, it's hard for me to be code specific relative to your code in my comments. I am well used to object/class stuff in other languages though, just not C++. This means we may not interpet terminology in quite the same way leading to misunderstandings.

When I speak of MySQL's buffer I'm refering to the DB engine's buffer on the engine's side of the connection. 'Command out of sync' is MySQL's way of saying 'you didn't empty my buffer from the last command results before you issued a new command'. At which point any subsequent command issued to the engine will return this error without performing the command until the MySQL buffer is emptyied.

The fact that this can occur says to me that the right time to handle this is during SQLExecDirect() processing. If you handle it then it becomes impossible for the end user to ever get things in this state, no matter what functions they call in any order. In other words, handleing it at SQLExecDirect() time lets the plugin protect itself from user actions that could otherwise break it.

What it means internally is having your own buffered copy of all the resultset(s) returned by the DB engine to the MySQL buffer as a result of a command. It really doesn't matter what form you decide to buffer it in internally. You might chose to just have a mirror image of the MySQL buffer. You might want to seperately buffer 'status' resultset(s) and 'data' resultset(s) for easier processing later. You might want to preprocess them into a form easier to use later with SQLFetch() and SQLGetData(). However you decide to do the buffering on your side, looping fetching resultset(s) from MySQL's buffer into your own buffer until the MySQL buffer is empty is the key to avoiding the error. I never suggested 'throwing away' anything so the user couldn't have it. Though it would be necessary to empty or destroy/recreate the buffer the next time a SQLExecDirect() came in, so each command request starts fresh.

Your code sample appeared to me to be putting the responsibility for assuring the MySQL buffer was emptied on the end user via SQLFetch() looping. I got that impression because you suggested the code sample as a solution to the error. That is what I consider sub-optimal because if the end user didn't expect any returned resultset(s) (say they just issued a multi-statement command or proc that didn't return *any* data) they would have no reason to do any SQLFetch() operations and if they didn't loop to clear any 'status' results in MySQL buffer things would break the next time they issued a SQLExecDirect(). This is another part of why I believe MySQL buffer emptying should be handled in SQLExecDirect() processing.

Further since the proc I was attempting to use does only return one 'data' result in a single row, the suggestion to use SQLFetch() looping seemed to mean that I was expected to loop clearing 'status' results from the MySQL buffer and figureing out for myself whether the SQLFetch()ed row was actually my data result or a status result. Which I also don't think is a good way to handle it.

I think that SQLFetch() and SQLGetData() should operate solely on 'data' resultset(s). A multi statement command or a proc that returns multiple 'data' results should behave the same way a SELECT that returns multiple rows does as far as SQLFetch() looping around SQLGetData(). This does mean you need to able to handle rows of data in such a set of rows that may have differing numbers of fields in different rows.

Just out of curoisity, is the existance of 'NEXT' for SQLFetch() represent a workaround in the multi resultset handling or does it foretell future 'NEXT', 'PREV', 'FIRST', and 'LAST' support? Not sure I'd have much use for such, but it's an interesting idea

While I think it would be a rare case for anyone to desire to access the actual 'status' resultsets, but if you really wanted to support user handling of 'status' resultsets it might be better implemented as seperate SQLFetchStatus() and SQLGetStatus() functions that behave across 'status' resultset(s) the same way SQLFetch() and SQLGetData() behave on 'data' resultset(s). It's a pity the relationship between 'status' and 'data' resultsets isn't one to one though, as that makes interpeting which command resulted in which 'status' troublesome in the multi statement and proc cases. I did notice your partial support for status via 'get rows affected'.

Regarding multiple connections, I don't really understand your "Why do you think you can override the function class of the SQL plugins in their ini file ?" comment.

I'm sure the limitation doesn't come about due to the connection objects themselves as I frequently use multiple database connection objects individually or in object arrays. Since these objects are derived from underlying C++ classes/subclasses, I doubt you mean you can't handle multiple instanceing of connection objects. I use such connections to talk to multiple DB servers and/or instances including different DB engines (MS SQL Server, Oracle, Betrieve/Pervasive, InterBase, etc.) simultaneously to manipulate data in various 3rd party applications databases in a unified fashion for clients. Would adding a parameter to your exposed methods to specify 'which' connection to use with a default value if unspecified really break your SQL class? Or is it a limitation in your class design? All I suggested as far as ini support was multiple server/login account/password values for different connections so an end user could specify.

<giggle> "preaching to the choir"... I *almost* used that phrase in a post before, but I restrained myself.

--- Grumalg ---

[Papillon]

Your points are valid, and I think it will be either what you suggest (always fetching all result sets into temporary buffers), or what I was thinking (fetching only what is requested, but emptying the connection buffer before a new statement is executed). That depends on a couple of other things as well...

I have the additional responsibility to keep things similar with other database plugins, so the implementation may not become too MySQL specific. That is why I need to think (and experiment) more about this.

[Grumalg]

Hmmm... Well I can marshal several points against "fetching only what is requested, but emptying the connection buffer before a new statement is executed".

First, if you have any resultset(s) left in MySQL's buffer how do you know whether or not they contain part of the data that might have been requested? If you haven't looked at them you just don't know...

Second, why not just get the whole command sync issue out of the way at SQLExecDirect() so there's no need to worry about it in any other function once the MySQL buffer flush is done? Doesn't it make the rest of the function code concerns simpler that way?

Third, I don't see any performance advantage to defering a buffer flush when your just going to have to pay it anyway before sending the next command. Why not get it all in one whump and be done with it? It's argueable that doing it all in one tight loop *has* a performance advantage.

Fourth, C++ programmers usually pride themselves in codeing as lean'n'mean as the language allows. Isn't having a single While loop run to completion in one place more elegant and safer than having a partial looping and a seperate 'clean it out to make sure' in two seperate places?

Fifth, <giggle> who's talking "Arbitrarly throwing away results" now?

RE: Multiple connections a lot of comments come to mind, but I haven't the time now to marshal my thoughts to present it clearly. I'm very S-L-O-W at typing stuff up and thinking about it and editing till I'm happy with it.

Grummy gotta go get his 'ugly' sleep...

[Papillon]

[Gryphyn]

I'll throw in my 2 coppers...

Speaking as a professional DBA, You're trying to solve a 'bad practice' issue. --Changing Metadata and Data in the same context.

*Meta-data is all the bits of information that describe the contents of the database, Data is the information content of the database.

I know you are using it as an example, but in practice this sequence of events just doesn't happen, because the performance overhead would kill your server (especiality in a multi-user environment)

What follows in not a "You shall not ...", but rather a quick guide to making things go a little faster.

1. avoid meta-data changes. [exclusive access]
Things like setting up the database are unavoidable, you need to do this at least once. Lets just say, changing the layout of your database is not trivial. If in doubt get some professional advice - see the Database forums.
Don't rely on someone who seems to know, there's the axim 'a little knowledge is a dangerous thing' (Oh, how I know how true that is - "just wait while I restore your database")

2. meta-data changes. [exclusive access]
If you have to make them, get them done as quickly as possible!
As I said earlier, try not to make data changes at the same time.
-- This has to do with TRANSACTION management, I won't go into detail, but this is about the database state before things happen, and when 'activity' really takes place.

3. data changes
This is what we are here for.
Some key notes:
. INSERT is expensive - but has to be done to start with
. UPDATE is good - but be carefully to update only what you want to.
. DELETE - hope you wanted to do that
. REPLACE only some databases, = DELETE then INSERT

and finally
4. data - getting it back
. SELECT - only select what you need. This is where all the power lies.
Get the database to do your work, rather than returning everything to NWN(1/2) then selecting what you want.

>mind you I'm speculating here as I don't have everything set up yet<
But the following sequence would perform better, produce the correct results, and not have the 'buffer' issue

SQLExecDirect("DROP TABLE IF EXISTS test_table; CREATE TABLE test_table(id INT);");
for loop (n times)
{
SQLExecDirect("INSERT INTO test_table VALUES(10);");
SQLExecDirect("UPDATE test_table SET id=20 WHERE id=10;");
SQLExecDirect("SELECT * FROM test_table;");
while (SQLFetch()==SQL_SUCCESS)
{ ... }

SQLExecDirect("DELETE FROM test_table"); //*avoid meta-data change
}
SQLExecDirect("DROP TABLE test_table");

--If you required this behaviour it would be best done in a stored procedure. The procedure should only return the result-set for the SELECT part of the sequence.

Sure not everyone here is a professional DBA, and 'best practice' will mean nothing to them. People tend to follow examples, even bad ones.
Once again I know it is an example, and it does illustrate the 'technical issue'. I wouldn't put it on the priority fix list, a bit of doco should cover it.

Cheers
Gryphyn

[Grinning Fool]