Hi. This is my problem. Can someone help me?
I made an app on Xojo 2015r1 (compiled for windows+mac) that connects to mysql community server to get data and make some calculations.
Mysql server 5.6 runs on windows 2003 server 64bit.
Now I was asked to migrate that app to Linux (ubuntu 16.04), and this is the BIG problem running the executable on Linux: when the app makes a query (SELECT * FROM tab), and reads the float field data from the recordset (my_variable=rs.field(“xxx”).doublevalue, let’s say the value in the table is 1.1234), it seems it gets an integer value truncated to 1. When I compile and run in windows and mac everything is perfectly ok.
Some years ago, with different versions of xojo and mysql server and linux, I got the same issue.
what is the data type of the column?
Do you see right value in another app, e.g. Workshell?
the type is double. The same query from mysql workbench gives correct values
What does rs.field("xxx").stringvalue.cdbl return?
It returns the truncated value, 1. I also checked in the debugger what’s inside the recordset’s field: 1.
Could it be a bug in the mysql plugin? Or something wrong in mysql configuration? But why it works on windows and mac?
is the string without the cdbl ok or not ?
it could be that there are spaces between the numbers and the cdbl or val is truncated to the first num before the first space…
I use such method to filter them :
[code]Public Function RemoveNonNumericChars(extends ch as string) as string
dim ch2 as string = “”
dim c as string
for i as Integer=1 to Len(ch)
c = Mid(ch,i,1)
if IsNumeric© or c="," or c="." or c="-" then
ch2=ch2+c
end if
next
Return ch2
End Function
[/code]
could certainly be done in a more elegant way with a regex … Kem it’s your turn !
cdbl is localized. Don’t use it here.
if you get the number as text, you probably want to use val() instead.
rs.field(“xxx”).string returns 1 (wrong) on linux. the same code on windows and mac returns 1.1234
Have you tried MBS SQL Plugin as an alternative?
or try an access with ODBC ?
I copied the thread with the query to a new desktop app. It works. Maybe the compiler go mad with mysql on big apps, while it works on basic app? I’ll try mbs plugin and odbc… but the app is very big, I should rewrite a lot of code.
I’ll also try new version of xojo.
I think you have a locale-problem there… Decimal mark could be handles as , instead of .
Try to debug your code on Linux and have a look at the debugger what’s there in the recordset directly after doing the select.
You could also try, what you get if you use something like that:
SELECT CAST (`column` as DECIMAL(6,4)) from `table`
the server does not run with US/English locale?
Maybe that could be changed.
Marius, in the recordset I get ‘1’ with my query, while I get ‘1.1234’ with your ‘select cast’ suggestion! So what’s the problem in your opinion? I don’t want to add the slower cast function to every select query?
Also, I can’t explain why the same query (no cast) on the same linux machine connected to the same mysql server gives 2 different results in 2 apps, as I told before…
Hmm, crazy…
Do a “SHOW VARIABLES;” in both apps and compare the results.
Marco, is exactly the same connection (user/server/setup)?
Is the same Xojo Release?
If all is the same, then there is some other reason like some other command you sent to the server in the big app and that is not in the small app
Have you tried to add a button to test in the “big app” a new connection and get from there the value?
Yes Antonio, I did it. And I got the wrong value. And yes, it’s exactly the same. I will check the variables, as Marius suggest…