1.06 better but procs still bugged.

[Gryphyn]

[Grumalg]

"If SQLExecDirect is called, and there are still leftovers before I actually call the DB, I know they can safely be trashed."

While I'm not in your head, it sounds to me like the strategy your refering to is to read resultset(s) from the MySQL buffer, till you find a 'data' result at SQLFetch() time. Then, flushing the MySQL buffer if a new SQLExecDirect comes for safety's sake.

It's true the DB connection overhead is essentially the same whether you fully prebuffer or sum the cost of a bunch of individual fetches. The difference is where the DB connection overhead occurs. If SQLFetch() operates across the MySQL buffer you place the DB connection overhead inside the SQLFetch()/SQLGetData() loop across multiple data rows. Personally I think it's preferable to make the SQLFetch()/SQLGetData() loop faster useing a local buffer and take the DB connection hit upfront in SQLExecDirect() by prebuffering. If the user asked for a lot of rows they meant to use them. You only save time in the fetch by fetch approach if the user abandons the loop before processing all rows, which they are unlikely to want to do. With prebuffering you get faster SQLFetch() looping AND the MySQL buffer is already emptied so you don't need to flush it at new command time. You certainly can flush the buffer as insurance, but if prefetch is done right it isn't necessary.


"I haven't looked at it, but just trashing the leftovers should be cheaper than copying each result or resultset into a temporary buffer"

Assumeing the user not aborting a SQLFetch() loop, it's likely there would only be one status result following the last data result. In that case dumping the MySQL buffer as opposed to having prefetched that one status result is unlikely to offer much performance bump. Admittedly, a user could use multi-commands or procs that could leave a larger number of status results after the last data result. But even then a MySQL buffer dump only optimizes an infrequent occurance.


"You'd have a second loop for reading from the copied resultsets, I guess."

I see no reason SQLFetch() or SQLGetData() would need to loop at all. If your prefetch buffer has a decent structure to it, accessing the data should take nothing more than pointer manipulation.

Not saying you should use the exact approach below, just outlining how a good prefetch buffer design can make other things very simple. There's certainly a lot of classic data structure techniques to choose from.

Say you used a singlely linked list of structs to hold the data results. The struct might look like this:

struct DataResult
<actual data storage or pointer to data storage (your choice)>
<pointer to next struct or 'null' pointer if last element in list>

You'd build the linked list with a malloc of each list element while looping across the MySQL buffer to locate 'data' results and setting the <pointer to next struct> in the previous element to the new struct's pointer once it's malloced.

Then at SQLFetch() you'd only have to do:
<currstructpointer> = <currstructpointer>.<pointer to next struct>
If <pointer to next struct> is null, you're out of data. Of course, you'd have to handle the first SQLFetch() conditionally, but during successive fetches you'd just update the pointer.

SQLGetData() then uses <currstructpointer>.<actual data storage or pointer to data storage> to get to the data.


"Now it's my turn to not understand your comment"

<In my best Yosemite Sam cartoon voice> "It's a joke, son..." You made reference to my throwing stuff away once, and it looked like you were talking about throwing stuff away, thus the '<giggle>'.

--- Grumalg ---

[kungfoowiz]

Hello

I've tried to loop the resultset array from within an NWNX4 plugin (a modified version of Papillon's), but for some reason it crashes. I'm unsure if this is related to time or if it would be causing a memory fault for not returning whilst Libmysql is getting the next resultset.

It also appears that the row data for all resultsets only remains valid within the resultset loop. However, if we can't loop the resultsets in the first place, then there seems little point in using multiple statements. You're prolly better off using singular statements to do something.

---
kung

[Papillon]

I've changed the way resultsets are handled in the MySQL plugin. It now works like this:

1) In SQLExec, it tries to advance to the first valid resultset automatically. If there is none (e.g. if the command was INSERT), the old resultset remains unchanged.

2) If SQLFetch is called with the NEXT parameter, it tries to advance to the next valid resultset. If there is none, the old resultset gets invalidated anyway. If there is one, the first row is made the current row, just like it works without NEXT.

3) In SQLExec, if the plugin notices there are unfetched resultsets, it eats them up to avoid getting out of synch. This means that only in the very rare case where you have multi-resultsets from the previous SQLExec and issue another SQLExec that returns nothing (i.e. UPDATE), the previous resultset is destroyed. But this is only a problem in the most esoteric cases.

Since a lot has changed and I do not want to break backwards compability, here is a pre-release version of the MySQL 0.0.2 plugin:

http://www.nwnx.org/fileadmin/download/temp/xp_mysql-002-test.zip

The more positive feedback I get from you guys, the sooner I can include the change in the next prototype release.

For reference, here is my test function. The 002 version passes this test without any problem:

[Papillon]

What No reply

I'm itching whether this makes stored procedures useable in all their glory or not! You can not deny me that! Please!

Well, just try it, will ya?
_________________
Papillon

[Grumalg]

I dl'ed it last night, but have been busy getting server and workstation updated from 1.03 beta to 1.03. Along with all the defragging etc required. I will test it very soon

--- Grumalg ---

[Grumalg]

It works !!

Created the following quickie proc that returns 3 rows in query browser

[Papillon]

Yes, I was thinking about doing that, but then you would not know when one resultset stops, and the next one starts.
_________________
Papillon

[Grumalg]

Perhaps you could get the best of both worlds. Instead of 'NEXT' the param might be thought of as a mode. A 'manual' mode that behaves like 'NEXT' does now and an 'auto' mode that ignores result boundries and treats it all as one set of rows.

This idea is still only half-baked so far. Have to think a bit more about whether 'auto' can be the default or if it interferes with normal use of SQLFetch().

The only case I can think of where 'NEXT' would be the prefered mode is when returning multiple sets of row(s) from unrelated tables needing fully seperate handling. This is likely to be fairly rarely needed.

On another note, I've been thinking about the desireability of automatically converting non character fields to strings at SQLGetdata() time. Since you can examine the field type, it should be fairly easy to convert numeric fields to strings. That would eliminate the need to CAST such fields in the SQL making use of numeric fields in tables somewhat easier. Date/time fields could be tough because of internationalization issues though. Since MySQL does implicit casts when writing strings to numeric fields (like the handling of 'expire') this would provide the equivalent on reads. I touched on this in email before a bit.

--- Grumalg ---