Two integers, one converts to a string, one does not

Hello,

Having an odd and crazy problem that occurs “most” of the time. I’m creating a table of data. I get the recordset, parse it, put the fields into a record class which becomes a row tag. There’s 2 integers. One is the primary key and the other a foreign key. I convert them to strings. The primary key is never a problem, the foreign key often is. The integer fields are id_projects and id_clients.

The data from the database looks like this.

The data in the record class looks like this. Not so good.

The id_clients field “almost always” comes through as this crazy number.

Of course, while writing this post I try it again and it comes through fine.

The code does not seem to be complicated. Not sure what I’m missing?

[code] cRecord.id_projects=rs.Field(“id_projects”).StringValue.DefineEncoding(Encodings.UTF8)
cRecord.creation_date=rs.Field(“creation_date”).StringValue.DefineEncoding(Encodings.UTF8)
cRecord.modification_date=rs.Field(“modification_date”).StringValue.DefineEncoding(Encodings.UTF8)
cRecord.client_project_num=rs.Field(“client_project_num”).StringValue.DefineEncoding(Encodings.UTF8)
cRecord.client_proj_manager=rs.Field(“client_proj_manager”).StringValue.DefineEncoding(Encodings.UTF8)

cRecord.id_clients=rs.Field("id_clients").StringValue.DefineEncoding(Encodings.UTF8)
cRecord.project_client=rs.Field("project_client").StringValue.DefineEncoding(Encodings.UTF8)
cRecord.project_name=rs.Field("project_name").StringValue.DefineEncoding(Encodings.UTF8)
cRecord.project_date=rs.Field("project_date").StringValue.DefineEncoding(Encodings.UTF8)
cRecord.project_description=rs.Field("project_description").StringValue.DefineEncoding(Encodings.UTF8)
cRecord.project_notes=rs.Field("project_notes").StringValue.DefineEncoding(Encodings.UTF8)[/code]

Have you tried taking the integervalue of the field and using format or totext on it instead?

Not sure what Database this is SQLite? Have you tried pulling the value out from the db as an Int e.g. rs.Field().IntegerValue and then casting it to a string. This way you can at least check to ensure that the db is returning the correct value?

It’s a MySQL database, v5.6.38.

I did “start” to look at using a different way to work with the integer but I got diverged into a related sink hole. I just narrowed the focus in the process of creating the post.

I’m going to try these suggestions. Thanks.

since it is mySQL it has datatypes (unlike SQLite)
so first off. .what is the actual datatype the fields is define as? (database table)
and what is the datatype of the location you are reading it into (ie. Xojo variable)

what you say should be a “1” but is showing as 42949672961
the interesing thing is … in Hex that is “0xA00000001” … so where did the “A” come from is the question

Arthur Fonzarelli “The Fonz”

Sorry could not resist :stuck_out_tongue:

I made this change and it works so far. I changed the top line to the bottom line.

cRecord.id_clients=rs.Field("id_clients").StringValue.DefineEncoding(Encodings.UTF8) cRecord.id_clients=rs.Field("id_clients").IntegerValue.ToText

@Dave S both integer fields are defined as Int(11) unsigned. The datatype of the Xojo class property is string which is why I was using this line to convert the integer to string.

cRecord.id_clients=rs.Field("id_clients").StringValue.DefineEncoding(Encodings.UTF8)

Not sure why it always works with the id_projects field and mostly not with the id_clients field?

That number is repeatable, mostly. I haven’t watched it enough specifically but that particular number does come up most often when the problem occurs.

I’m going to go with the IntegerValue.ToText for now and see if it’s a solution.

With databases that are typed (mostly anything other than SQLite) it’s best to pull the value out as the native type and then cast or you can get weird behavior. Some DB’s are more tolerant of this than others. Behavior can sometimes change across platforms so sticking to this approach will hopefully provide a consistent result.

OH! I think I figured it out! (maybe)

You said it was INT(11)… so I’m betting that internal it is 1byte for length, and 10 bytes for value
or more likely 1/2 byte for length 1/2 byte for sign and 10 bytes for value

“A” is the length = 10, thus the value is 1

I thought I was doing that with this line:

cRecord.id_clients=rs.Field("id_clients").StringValue.DefineEncoding(Encodings.UTF8)

That makes some sense. Cool! There were a couple other numbers that seemed to come up. One was a long negative number. Perhaps I’ll never know if the solution I implemented holds.

Thanks everybody for the suggestions. I hope I got it.

Possibly related to: feedback://showreport?report_id=51943

The number 42949672961 has a value of 1 when cast as a Uint32 (it wraps around at 4,294,967,295). When cast as a Uint64 it retains its’ value. Have you recently changed to compiling as 64 bit?

[quote=400083:@Duane Mitchell]I thought I was doing that with this line:

cRecord.id_clients=rs.Field("id_clients").StringValue.DefineEncoding(Encodings.UTF8)

[/quote]
No. The native value is IntegerValue. By pulling StringValue instead, you’re asking the database class to convert it from integer to string for you, and that’s where things go south due to some internal representation issues. By asking for IntegerValue, you get a clean result, which you can then apply your own conversion to string and get the correct representation.

I am in a hurry, so please excuse my short reply… :slight_smile:

Please try the following, it should bring the correct results:

cRecord.id_clients = Str( rs.Field("id_clients").Integer )

Maybe related to: https://forum.xojo.com/conversation/post/383135

I always do this:

rs.Field("id_clients").Integer.totext

I appreciate the excellent suggestions, tips, and insights. I will look into them and circle back to the forum here…after my vacation. About 10 days.

[quote=400161:@Tim Streater]I always do this:

rs.Field("id_clients").Integer.totext
rs.Field("id_clients").Integervalue.totext

You know, I posted this answer two days ago…

[quote]@Sascha S Please try the following, it should bring the correct results:

cRecord.id_clients = Str( rs.Field(“id_clients”).Integer )[/quote]

Just a quick update on this, you need to have it “.Integervalue” as you correctly do in the related topic you linked to.