Not getting all rows back

[Morpheus]

Hi,
I have a complex query that I run in game and a while(SQLFetch()==SQL_SUCCESS) that prints out all the rows. it only prints the first row, but when I cut/paste the SQL from the log into mysql client, it returns the 4 rows I expect.

I am using 1.07. Here is the query, FWIW.

select rank,parent,thetext,state,next,pwms_quests_conv.conditions,actions,quest,pwms_quests_conv.guid,anycond,speaker,nwn1,pwms_quests.tag from pwms_quests_conv,pwms_quests where speaker='NPC_ORC' and pwms_quests.guid='71475446-25f1-4327-93dd-d9305361c764' and pwms_quests.guid=pwms_quests_conv.quest and parent<0

In NWNX it returns 1 row. In MySQL client on same data, it returns the correct amount.

Any ideas?

Morph

[Grinning Fool]

could you paste your script/loop in here?
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[Morpheus]

I think I found the reason, but not sure of a fix.

I have a while loop while(SQLFetch()==SQL_SUCCESS) { } and inside the while loop I do another SQLExecDirect, and I think it causes the outside check to fail.

Any suggested workarounds for this?

thanks!!
Morph

[Gryphyn]

[Papillon]

You can have a SQLExecDirect in the inner loop in NWNX4, but it may not return results, i.e. only INSERT and UPDATE. Otherwise, the "outer" result set will be overwritten.

If you need two resultsets, here's a quick workaround: Duplicate your plugin DLL and INI file, rename the function class of the second plugin, and then you can use two seperate connections to your database, which are completely independent of each other.
_________________
Papillon

[Morpheus]

Egads!

Papillon, would it be possible to allow multiple result sets by using a key like this?

SQLExecDirect("select...","key");
SQLFetch("key");

to separate the different sets and nwnx would just use an internal hash to maintain them?

EDIT:
The workaround I am doing is just reading the outer set into a tokenized string first, then iterating over that and doing my other selects then.


Morph

[Morpheus]

[Papillon]

@Morpheus: Multiple resultsets would be possible, but nobody has gotten around to provide a working implementation yet. It has often been said how easy a DB plugin supporting multiple resultsets would be, but nobody has actually presented one.

There are currently no plans to extend the plugins in that direction, as far as I am concerned. Sorry
_________________
Papillon

[Morpheus]

Hmmmm. In theory it could be done (although less efficient) on the script side. Like I was able to read my result set into a tokenized string that I later iterate over almost the same way as a sqlfetch(). So the resultset can be read in and associated with a key in yet another tokenized string.

SQLFetch would not be needed then, but it could work like this:

SQLExecDirect("select ....","key1");
SQLExecDirect("select ....","key2");

string value = SQLGetData(1,"key1");
string value2 = SQLGetData(1,"key2");

SQLClose(); // New function to flush out the key'd arrays.

This would be very doable on the script side of things. Maybe I will write a wrapper script to do it.

Morph

[virusman]

I had plans to implement multiple resultsets in NWNX-ODBC, but I found out that in most cases it's possible to get rid of queries in the cycle with JOIN in the main query.

[Morpheus]

Yeah, but those exceptions are the tricky parts!

[Grinning Fool]

[Morpheus]

In my case its not possible to combine, because data retrieved from the first query is used to issue a second query and depending on the second result the loop will bail and doesn't need to do any more lookups.

[Gryphyn]

[Grinning Fool]