mySQL and decimal field type

Databases
1

Hello,

I have a mySQL table with one of the fields defined as Decimal(8,2). After doing my SQL select statement and retrieving records from the table I try to get the value from this field:
varX=TableRecordSet.field(“fieldX”).doublevalue
where varX is dimensioned as a double. My program crashes at this statement. I’ve tried different combinations of declaring varX as a currency and using (“fieldX”).currencyvalue but I get the same crash. If I use:
varX=TableRecordSet.field(“fieldX”).value
then my program doesn’t crash but the values I get is 0 which are incorrect.

Digging a little deeper ran database.fieldschema to see what the field was being seen as. This field did come back as field type 13 although the length of the field showed as 524290 ???

Any thoughts on whether I am using the wrong declaration for the variable or for the database field type, or if these types of fields cannot be used, would be appreciated.

Not sure if this is related but I had the same problem in another table when I had a field defined as MEDIUMINT which I resolved by redefining the field as INT.

Thank you.

2

Length of the field is a calculation:
524290 / 65536 = 8
524290 - (8 * 65536) = 2

(65536 = 256*256)

The crash you get is probably caused by the field being nil and does not contain a valid value for returning a value. Just test for the field being <> nil should help solve your problem.

3

Thanks for the reply Andre.

You are correct that the field is Nil. Added:
If TableRecordSet.field(“fieldX”).value=nil then
which proved true.

Now the question is why is it being seen as null. I can see in Sequel Pro that all the values in this column are not null and in fact the column is set to not allow null values. If I change the field type to float then everything is fine.
ALTER TABLE ‘TableX’ CHANGE ‘fieldX’ FLOAT NOT NULL;
Once I change the field type the field type back to decimal, then the value from this field is always being returned as null.
ALTER TABLE ‘TableX’ CHANGE ‘fieldX’ DECIMAL(8,2) NOT NULL;

I’m also unclear as to what database field type should be specified for a decimal field.

Thanks.

4

I haven’t tested this, but i think that just replacing the fieldtype doesn’t change the data in the table. Maybe you can try to insert new data when the type is set to decimal and try then again.
HTH,
Andre

5

OK tried this. SQL INSERT with a field that is defined as decimal(8,2) in mySQL works fine and value shows up find in Sequel Pro. However again when I access some records and check if the value of this field is Null (If TableRecordSet.Field(“account”).value=nil then) it is true. All other fields of char, int, tinyint, longtext are fine.

I am in the process of switching to using Prepared statements for all my SQL and I started to wonder whether this might be the cause. Tried the query using simply TableRecordSet=db.SQLSelect(my query) and not the Prepared statement. Now it works! Referencing the field as TableRecordSet.field(“fieldX”).doublevalue does not return a null but the correct value.

There is nothing in the prepared statements that reference the decimal field. So is this a bug in mySQL, or Xojo, or am I still missing something? Good news is that at least now I can work around it.

Thanks for your help so far.

6

Check your BindTypes in the Prepared Statement.

7

Hello Simon, this is and example of what I have:

ps=db.Prepare(“SELECT * FROM TableX WHERE Idfield=?”)
ps.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_STRING)
TableRecordSet =ps.SQLSelect(“06000”)

From what I see there is no reference to datatype and data for fields other than those that are included in the query. So the field that is defined as DECIMAL(8,2) I only reference after the query when I am looking for it’s value:
varX=TableRecordSet.field(“thedecimalfield”).doublevalue

Thanks.

8

Ok, it was just a thought that crossed my mind after you said that you had started to replace your queries with prepared statements.

I have fallen into that trap on a number of occasions!

9

Thanks for the suggestion anyways, never know. As I mentioned before I had this problem before when I had a different field declared as MEDIUMINT. At that time I got past the issue by changing the field type to INT. This time I would like to keep the field type as DECIMAL and not FLOAT because it is for currency. I was thinking I could have the field type as INT and then divide by 100 to get the actual value, and multiply by 100 before saving to the table. But if I can get it to work properly by not using a prepared statement in these cases, then I think I will do that.

Still not sure where the problem lies.

Thanks.