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

  1. 7 days ago

    Duane M

    Aug 9 Pre-Release Testers, Xojo Pro Boston, MA

    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.

    -image-

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

    -image-

    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.

    -image-

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

        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)
  2. Jeff T

    Aug 9 Midlands of England, Europe

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

  3. Julian M

    Aug 9 Pre-Release Testers, Xojo Pro San Francisco Bay Area

    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?

  4. Duane M

    Aug 9 Pre-Release Testers, Xojo Pro Boston, MA

    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.

  5. Dave S

    Aug 9 San Diego, California USA

    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

  6. brian f

    Aug 9 Pre-Release Testers, Xojo Pro Chilly California

    @Dave S .... so where did the "A" come from is the question

    Arthur Fonzarelli "The Fonz"

    Sorry could not resist :P

  7. Duane M

    Aug 9 Pre-Release Testers, Xojo Pro Boston, MA

    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?

    what you say should be a "1" but is showing as 42949672961

    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.

  8. Julian M

    Aug 9 Pre-Release Testers, Xojo Pro San Francisco Bay Area

    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.

  9. Dave S

    Aug 9 San Diego, California USA

    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

  10. Duane M

    Aug 9 Pre-Release Testers, Xojo Pro Boston, MA

    @Julian M 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.

    I thought I was doing that with this line:

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

    @Dave S OH! I think I figured it out! (maybe)

    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.

  11. Paul M

    Aug 9 Pre-Release Testers, Xojo Pro

    Possibly related to: Feedback Case #51943

  12. 6 days ago

    Tanner L

    is not verified Aug 9 Pre-Release Testers Toronto, Canada

    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?

  13. Tim H

    Aug 9 Pre-Release Testers Portland, OR USA
    Edited 6 days ago

    @Duane M I thought I was doing that with this line:

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

    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.

  14. Sascha S

    Aug 9 Pre-Release Testers, Xojo Pro Germany/W'haven
    Edited 6 days ago

    I am in a hurry, so please excuse my short reply... :)

    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

  15. Tim S

    Aug 10 Canterbury, UK

    I always do this:

    rs.Field("id_clients").Integer.totext
  16. 5 days ago

    Duane M

    Aug 10 Pre-Release Testers, Xojo Pro Boston, MA

    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.

  17. Tim S

    Aug 11 Canterbury, UK

    @Tim S I always do this:

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

    rs.Field("id_clients").Integervalue.totext
  18. Jeff T

    Aug 11 Midlands of England, Europe

    You know, I posted this answer two days ago..

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

or Sign Up to reply!