SQLFetch - Possible bug

[OVLD_NZ]

SQLFetch doesn't appear to be stepping through the dataset rows.

[caloup]

aren't you supposed to use :

[Papillon]

It should, and it should return all rows that are matched by the query.

First of all, which DB plugin are you using ? Can you please give some more detailed examples (including data in the table, log file output, and so on) ?
_________________
Papillon

[caloup]

and have you rename the database dll and ini that you don't need ?

for example, if you are using xp_mysql.dll : rename xp_sqlite.dll to off_xp_sqlite.dll and do the same for xp_sqlite.ini...

(in case of...)

[OVLD_NZ]

The plugin I'm using is MySQL and yes sqlite is not being loaded. There's nothing in the log files, so not much point posting them. But here's the other information.

SQL to create table

[Grumalg]

While I haven't tested your specific code I can make some observations just from looking at it.

Your table definition uses a lot of numeric fields. Since NWNX is limited to passing string data by the nature of the hooking mechanism this may be your problem. It's true that MySQL will do a certain amount of implicit type casting under the right conditions, just don't think it will do it correctly as you are useing it. I normally use VARCHAR(n) or CHAR(n) fields to store n digit numbers and do IntToString on writes and StringToInt on reads for numeric data.

Your 'reward' field is TEXT, do you really want to reserve 64kb per field? This too would benefit for useing VARCHAR or CHAR datatype.

Your primary key index is pretty useless, and you have no index at all on the 'taskid' field. You'd get faster performance with an index on 'taskid' since you use it in WHERE clause matching. Note that you cannot make 'taskid' a primary key as it contains non-unique values, but you can make it an ordinary index. There is no requirement that a table must have a primary key at all.

--- Grumalg ---

[OVLD_NZ]

Well, I know I can optimize the table better. But I don't see that as the problem here.

The code will read the first row fine, but fails to goto the next row when I call SQL_Fetch(). Which returns SQL_ERROR.

I can try converting everything to VARCHAR and see what happens, but I don't think it'll solve the problem.
_________________
The World of Judur - The Shadow has Awoken

[Grumalg]

My TEXT field and indexing comments didn't apply to your problem. They were just meant to be helpful in general.

I'm not saying you haven't encountered a bug. But attempting to SELECT a numeric field through nwnx could be causeing your problems. Nwnx has never supported returning numbers as binary. By definition everything must be passed back and forth in string form.

Getting a bit more detailed, when you SELECT a numeric field in a table MySQL returns the binary form of the number. When you call SQLGetData(), nwnx grabs the returned bytes and stuffs them into a local string on the module. SQLGetData() then returns the contents of that local string. Your code then takes those bytes and passes them to StringToInt. Since these bytes are binary instead of characters, it's entirely possible that either StringToInt or GetLocalString is chokeing on them. It may be that only certain byte values cause a problem. In that case, it would appear to work on some values and not on others. It's also possible that nwnx itself has some trouble with the multiple binary returns though it's intended to be transparent. However, it's not meant to be used that way, so you can't really say thats a bug.

If you really need the data in the table to be an actual number instead of a number stored as a string, you'd need to CAST such fields into CHAR within the SELECT statement to get them through nwnx. Since that approach entails additional overhead in conversions general practice is to store numbers as strings except for special cases.

It's a bit different when you are writing to the database. You still do need to pass the number down in string form, but MySQL recognizes that you are trying to put a string into a number field and does the conversion automatically if the string can be converted. This is an example of 'implicit' type casting

If you try passing everything through as string and still it doesn't work, then you may have found a bug.

-- Grumalg ---

[caloup]

I think you could try to order your table by taskid before reading the data (but it's just an idea to try if that do anything...)

[OVLD_NZ]

Problem solved

Within the while loop I was performing another database query and of course it resets the dataset. After removing the inner query it works fine. Even with INTEGER fields in the database.

I just needed you guys to make me look harder.
_________________
The World of Judur - The Shadow has Awoken

[Papillon]

I tried your code and table definition and it works for me.

[Grinning Fool]