Encode Function truncating long strings?

[Kallistor]

When I store and retrieve a location I noticed that the float after orientation was missing or truncated. I have printed the location strings to log before and after the EncodeSpecialCharacter functions and here is the result:

[Sat Dec 16 07:41:42] sValue before Encode : #AREA#SometestDocks#POSITION_X# 100.886230469#POSITION_Y# 118.891502380#POSITION_Z# 3.000000000#ORIENTATION# 104.106178284#END#
[Sat Dec 16 07:41:42] sValue after Encode : #AREA#SometestDocks#POSITION_X# 100.886230469#POSITION_Y# 118.891502380#POSITION_Z# 3.000000000#ORIENTATION#

As you can see the Orientation and #END# has been dropped. Anyone else noticed this effect?

[Grumalg]

That happens when the field they are stored in is to small and the data gets truncated to fit. Make the field larger...

Note that since area name is included, the size of the string can grow signifigantly when the name is long.

--- Grumalg ---

[Grinning Fool]

I think it's just the area tag that's included; max size of that is 32 (IIRC). Based on that and the example, I'd say the max length is 146. However smallColSpacer is only 129 bytes -- so increasing that to 146+ will probably fix this.
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[Kallistor]

@Grumalg: Nothing to do with the field as its before it is stored in the DB. But the field is TEXT so it can handle 65000 bytes, ample for this string.

@Grinning Fool: Good point. I will give that a go.

EDIT: Adjusted the length of smallColSpacer to 159 (just for kicks) and the full location string was returned intact. Thankyou again for your help Grinning Fool. I am hoping smallColSpacer will be extended in v1.07

[Grumalg]

Guess I still have too much nwnx2 stuck in my head. Saw the smaller spacer before and the use of bigspacer but it just didn't click when I first saw your problem. Having seen the truncation occur before when field was too small, it was the top of the head answer

--- Grumalg ---

[Grumalg]

@Kaliistor:

I note your use of TEXT. I'm not a big fan of TEXT or BLOB fields unless there's no other way to do it. They are expensive in performance.

Most datatypes are stored as a contiguous series of column bytes for a row in the DB file. TEXT and BLOB are stored outside this normal storage and represented in the row's contiguous bytes as an indicator to where they are actually stored. This means when reading a row from the DB file the HD must seek to the actual location of the TEXT or BLOB data and possibly then seek back for any remaining column data. Now MySQL may try to optimize this internally and read the row, then interpet the TEXT or BLOB and go get it after the fact. But either way, you get extra HD loading unless you're lucky enough that the data was already in MySQL's data cache.

Since VARCHAR can hold up to 65,535 bytes (MySQL 5.0.3 or later) and varchar's bytes are contiguous in the row data they should perform better for larger blocks of text.

--- Grumalg ---

[Kallistor]

Yeah I hear ya. I was also thinking why the hell am I usong 65000 bytes I will be changing it down to VARCHAR(160) or even TINYTEXT for 256 bytes, though I think a bit of trimming on the position floats might be a suggestion, I dont think most people would care if 2 decimal places was all that was kept instead of the default 9 places.

Hey since you are using MySQL 5, what are the benefits compared to MySQL 4.1 (what I am using) and are there any major outstanding reasons to upgrade?

[Grumalg]

All forms of TEXT still do the indirection to data storage. In MySQL 4.x VARCHAR can hold up to 255 chars.

The biggest advantage to me of 5.x is stored procedures (though they don't work yet as og 1.06). Stored procedures will allow you to collapse multiple DB calls into one call and can perform logical decisions as well.

Think of a proc as a script that executes in the DB engine.

[Kallistor]

I saw the stored procedures thing in v5 but I didnt see an application for it. Enlighten me so I can see the worth of a stored procedure. To me I can write a script in NWN or I can write a stored procedure outside in MySQL but they both do the same thing???

[Grumalg]

ok...

A proc can take a bunch of input params, declare local vars, perform conditional decisions (if/else if/else), do loops, etc. And then return a row(s) of data built out of actual table data or vars in any combination. All that is in addition to performing any sequence of SQL commands.

So if you had several things to do with the DB, say you would otherwise do several DB calls to change things in a number of tables. With a proc you can pass down all the params and do a number of otherwise unrelated things in a single proc call, i.e. one DB round trip instead of several.

--- Grumalg ---