NWN server crashes after writing to the DB

[PaithanQuindiniar]

We're running NWNX2 with a MySQL database and implemented KPB's banking system (for NWNX of course).
I added a bit of code to the banking script, that writes a string in the DB containing all items stored in the vault of a player (just for info, to see what players have stored in their vaults --> big brother ). The DB field is a TEXT type one. Now everything has been working okay, until suddenly the server starts crashing when using the vault. After some testing, I noticed, that it crashes when the string containing the items becomes rather long - 27 items (including name, stack size and tag) was my limit during testing. I runned the query in the DB control program with a text of over 1000 characters and that works. So it seems, that NWN (or NWNX?) has a problem with string of bigger size ...

Does anybody have any ideas?? I'd really appreciate some help here (it has cost me already about 5 hours, just to figure this bit out, so I thought, I'd ask the experts! )

[Lanthar D'Alton]

NWNX was coded for 8*128 characters... 1024. That means you'll need to limit yourself to that many letters. I'd suggest you make it run in a loop that sends the data in each time the string hits just less than 1024... Just always call a DELETE FROM big_brother_table WHERE player='name' or whatever beforehand, then put all your entries in as a set of strings instead of one big one.

-Lanthar

[PaithanQuindiniar]

Okay ... I'll give that a try ...

One more question though. I tried the query

UPDATE big_brother_table SET val=CONCAT(val, 'new data') WHERE playerID=1 AND name='stored items'

for each item. I figured, that way, the DB access is always done with only one item information string (name, tag, stack size) and the "attaching" handled by the DB itself. Why isn't this approach successful?

[Lanthar D'Alton]

I'd say it failed before because your string was too long.

Now however, there will be multiple rows for a given player, and you will need to delete them all, then add new ones. Updates for that sort of thing would be pointless. You'd only repeatedly update the first row.

Egads. Actually, that's a TERRIBLE method (just reread it). You really should consider that you are doing one call per item for what may be hundreds of items... I'd advise that you do the string concatenation in the server and send a string in then instead... but then, I don't know how slow the server might be about concatenation. It depends on how the reallocation of the string is done when you add two together....

anyway, I'd try it the way I suggested. concatenate the string into something just less then 1024, then send to the db... concatenate the next string, send to db... but use inserts.

[PaithanQuindiniar]

Hm okay ... still, I'm wondering why the next thing doesn't work (sorry for being such a pain in the *** about this! ). I'm gonna try with the exact example:

In the KPB system (as you might or might not know), all items you put in a chest are stored on an invisible creature when you walk away from the chest. This creature object then is stored in a NWN campaign database. So my problem is, that there's no way to see, what players got stored in the chest (i.e. the creature object in the NWN database).

To manage this, I wrote the following bit of code, which builds a string sItems. While the original banking code cycles through the player's items in his inventory, my code attaches a string with name, tag and stack size of the item, every time the original code finds an item and copies it to the invisible creature's.

The original code:

[Papillon]

You can increase the amount of a data a single row can hold by using a bigger ODBC SPACER in aps_include. For Windows, the maximum should be 64KByte per Row.

Anyway, it should never crash, even if you go over the limit. Do you think you could create a small package that helps me reproducing the problem ?

Which OS btw ?
_________________
Papillon

[PaithanQuindiniar]

Hi there! (Thanks for the replies by the way!)

I can arrange a sample mod alright - going to need until saturday, since next week I have a skiing trip planned I'll send it as soon as I can!

We're running Win XP (or Win2K on my computer, which results in the same problem) ...

[Papillon]

Oh yes, forgot to ask which version and which plugins you are running.
_________________
Papillon