Getting wrong data for unsigned zero fill column

Databases
1

i am using Xojo 2019 r3
I am trying to fetch data from my mysql databse.
i faced a strange case where i am getting wrong value for one column.
When i checked column it is set as unsigned zero fill. i am getting same value for every row, other columns are getting fetched correctly.
Here is my code to fetch data.

sql = "SELECT * from "+ TableName + " WHERE COUpdate = ‘1’
rs =app.db.SQLSelect(sql)

I tried by specifying column names in query but no luck.

2

Did you try sql = "SELECT * from "+ TableName + " WHERE COUpdate=1 (Without the quotation marks around the 1) ?

3

yes, tried it just but same problem.
Other columns are fetching correctly just problem with this one.
Should i try by changing mysql plugging ?

4

No, i do not think so.

What do you mean exactly when you write you get wrong values from this column?
What values do you expect, what values do you get and how do you handle the RecordSet Field Values?

5

let take a example
i have following data in my table
CalEvent Title COUpdate
000071 Test 1

so now i am using MysqlCommunityServer object (app.db) to retrieve data.
sql = "SELECT * from "+ TableName + " WHERE COUpdate = 1
rs =app.db.SQLSelect(sql)

strData = rs.Field("CalEvent ).StringValue

but i am getting strData as ‘57’
Title is fetching correctly as ‘Test’

6

Am I getting you right? You have a Numeric Column with unsigned and zerofilled Numbers and try to read them as Strings with unknown encoding? :wink:

Try the following please:
strData = Str( rs.Field( "CalEvent" ).IntegerValue ) // Or DoubleValue in 32Bit Apps

Does it work?

7

Not worked.
i used following
strData1 = Str( rs.Field( “CalEvent” ).IntegerValue )
strData2 = Str( rs.Field( “CalEvent” ).DoubleValue )
strData3 = rs.Field( “CalEvent” ).StringValue

and got wrong data 57 instead 71 in all three conditions

Then i altered a table, Removed my column’s Unsigned and ZeroFill attribute then it returns correct data(71) for all the above 3 conditions even for .StringValue

so i think there should be a bug somewhere
Please let me know if anyone faced same issue and know how can i access Unsigned column. i need it unsigned as it is being used from long time.

8

How about the following?

Possible explanation of what’s happening: I assume the mySQLCommunityServer Plugin expects a SIGNED value (maybe that’s why there’s no UIntegerValue Type for DatabaseColumns?) and thus does a false convert to 57?

9

sql = "SELECT CAST(CalEvent as SIGNED), Title from "+ TableName + " WHERE COUpdate = 1
This solved the issue.
Thank you

1 Like