ActiveRecord and MySQL

Databases
1

Having a problem with Boolean fields used in MySQL tables. ActiveRecord ‘break’ at field type error on fields defined as Boolean or tinyint(1). Reading up on MySQL I find that Boolean is not supported, I find this hard to understand.

I guess I could change the field to text and use the words “true” or “false”, but then I would need to change any application that uses these table so they will work ok.

Has anyone else run into this problem? How have you handle this.

2

you could use bit as data type and populate 0 for false 1 for true as value.

3

[quote=137293:@Rich Hatfield]

you could use bit as data type and populate 0 for false 1 for true as value.[/quote]
Changing it to bit works for ActiveRecord.
However I not get a database error on this:rs.field(“roll_user”).BooleanValue = u.roll_user
Error message: DB Error: Data too long for column ‘roll_user’ at row 1

4

you would need to change this as the column type is no longer Boolean and is now bit.

rs.field("roll_user").BooleanValue = u.roll_user

to

rs.field("roll_user").IntegerValue = u.roll_user

also, you may need to look at u.roll_user to ensure value is either 0 or 1.

5

[quote=137307:@Jim Smith]Changing it to bit works for ActiveRecord.
However I not get a database error on this:rs.field(“roll_user”).BooleanValue = u.roll_user
Error message: DB Error: Data too long for column ‘roll_user’ at row 1[/quote]
I’m confused. If you’re using ActiveRecord how/why are you using a RecordSet? The whole point of AR is to move away from the traditional Recordset/Database Record approach.

6

Before getting ActiveRecord (and ARGEN) I have a MySQL database; with several tables, which contain data. I have one desktop application and two Web application that read and write to this database.
I was incorrect in stating this problem field are “Boolean”, they are “Tiny”. That is working for the 3 app mention.

So now I have ARGEN.

  1. Run Argen using MySQL database tables and input.
  2. Create test app per video adding “DataFile” created by Argen.
  3. Run Xojo and app stops on break in Method: MapFieldInfo (field type is “1” and that type is omitted from the select.)

Confusion: I have add that by mentioning the apps I am using with this database. So forget those for now and take care of the problem I’m having with MySQL’s types and ActiveRecord.

7

.

8

We used MySQL and ActiveRecord in one of our big Web projects. Our ‘boolean’ fields are defined in MySQL as TinyInt(1).

If FieldSchema is giving you a 1 that means the field is defined as Byte (ref: Database.FieldSchema). AR doesn’t handle bytes which is why it’s hitting the break.

What are doing with the Byte field? You can modify the select case statement in BKS_ActiveRecord.MapFieldInfo to attempt to map it to the datatype you want. I make no guarantee that it will be seamless at that point but it’s a starting point.

9

The TinyInt field is use as a Boolean, true/false.

I’ll work on it and let you know what I come up with.

Thanks, Bob.

10

Well, I suspect that you have Byte field somewhere and that’s what’s causing the problem. You should be able to see what the table and field name is in the MapFieldInfo method when it hits the break.