SQL Error: Incorrect integer value: 'Y' for column `users`.`ACTIVE` at row 1.

BIZ TMS Aurelius
1

Hello!

I have a problem saving boolean property to the MySQL DB. It always wants to save "Y" instead of "1" for boolean = true.

The manager configured like this:

constructor TBaseManager.Create(Owner: TComponent; Connection: TZConnection);
var Generator: TMySQLSQLGenerator;
begin
  inherited Create(Owner);
  FConnection := Connection;

// Doesn't work. Disabled, because tried directly on FManager
//  Generator := (TSQLGeneratorRegister.GetInstance.GetGenerator('MySQL') as TMySQLSQLGenerator);
//  Generator.UseBoolean := true;

  FDbConnection := TZeosLibConnectionAdapter.Create(FConnection, false);

  FDbManager := TDatabaseManager.Create(FDbConnection);
  FManager := TObjectManager.Create(FDbConnection);
  (FManager.SQLGenerator as TMySQLSQLGenerator).UseBoolean := true;

  TMappingExplorer.Default.Events.OnSqlExecuting.Subscribe(
    procedure(Args: TSQLExecutingArgs)
    begin
      Log(llVerbose, 'Aurelius: '+Args.SQL);
    end
  );
end;

DB is configured like this

CREATE TABLE `users` (
	`LOGIN` VARCHAR(20) NOT NULL,
	`PASS` VARCHAR(20) NOT NULL,
	`REAL_NAME` VARCHAR(255) NULL DEFAULT NULL ,
	`ACTIVE` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
	`LAST_LOGIN` DATETIME NULL DEFAULT NULL,
	`POS_PASS` VARCHAR(50) NULL DEFAULT '123' ,
	`TAX_ID` VARCHAR(50) NULL DEFAULT NULL ,
	`READ_ONLY` CHAR(1) NULL DEFAULT '0' ,
	PRIMARY KEY (`LOGIN`) USING BTREE
)
COLLATE='cp1250_croatian_ci'
ENGINE=MyISAM
;

Object

  [Entity]
  [Table('users')]
  [Id('FUserName', TIdGenerator.None)]
  TUser = class
  private
    [Column('LOGIN', [TColumnProp.Unique])]
    FUserName: string;
    [Column('REAL_NAME')]
    FRealName: string;
    [Column('ACTIVE')]
    FActive: boolean;
    [Column('PASS')]
    FPassword: string;
    [Column('POS_PASS')]
    FPasswordPOS: string;
    [Column('LAST_LOGIN')]
    FLastLogin: TDateTime;
    [Column('TAX_ID')]
    FTaxId: string;
    [Column('READ_ONLY')]
    FIsReadOnly: boolean;

    FRoles: Proxy<TList<TUserRole>>;
  public
    constructor Create; reintroduce;
    destructor Destroy; override;

    property Active: boolean read FActive write FActive; 
    property Password: string read FPassword write FPassword;
    property PasswordPOS: string read FPasswordPOS write FPasswordPOS;
    property Username: string read FUsername write FUsername;
    property RealName: string read FRealName write FRealName;
    property LastLogin: TDateTime read FLastLogin write FLastLogin;
    property Level: integer read FLevel write FLevel;
    property TaxId: string read FTaxId write FTaxId;
    property IsReadOnly: boolean read FIsReadOnly write FIsReadOnly;

    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAllRemoveOrphan, 'FUser')]
    property Roles: Proxy<TList<TUserRole>> read FRoles write FRoles;
  end;

I tried BIT and TINYINT, but no way. I know that I'm doing something wrong, I just can't find what :slight_smile:

2

The UseBoolean property should be set globally at the beginning of your application, not during manager creation.

But there is something very strange here, as Aurelius doesn't save "Y" anywhere at all in the database. When using chars to save booleans, it uses T and F. So unless you just got confused and it's indeed trying to save T, then the problem is somewhere else outside Aurelius scope.

3

I'm also confused by this.

I moved the generator in the MainForm.OnCreate method, so it's called only once in the beginning, but the error remains the same.

4

Again, Aurelius doesn't save "Y". You should investigate this better, maybe trying to create a smaller project reproducing the issue.

5

How can I send you a test program? I have done a small program and added SQL code for the DB.

6

You can send a test program just attaching a zip file here.
Have you tried to use a different component than ZeosLib? Have you fiddled with ZeosLib configuration? I'm suspicious that it must be something with ZeosLib configuration.

Ref: ZeosLib / Wiki / Things to know about the Zeos 8.0 release

MySQL / MariaDB:

  • the parameter MySQL_FieldType_Bit_1_IsBoolean is enabled by default now if your server version is MySQL >= 5.0.3. This means that fields declared as BIT(1) will be treated as boolean fields. The old assumption that an enum('Y','N') is a boolean field is disabled if this parameter is enabled. If this parameter is enabled, enum('Y','N') will be a string field. Other enums behave as before, they will be mapped to a sting filed in any case.
  • The batch import API introduced in Zeos 7.2 is now supported for MariaDB
  • some parameter names changed. See [#395] / ZeosLib / Bugs / #395 Create the doxygen documentation fails
7

uuuhh... ok, this is something I didn't tought about the DB layer - thank you! I will check this immediately and let you know.

1 Like
8

Solved. Your hint was correct - Zeos uses Y/N for booleans. If you want to disable it, then add the parameter

dbMain.Properties.Values['MySQL_FieldType_Bit_1_IsBoolean'] := '1';

before connecting and it will work correctly.

Thank you for the assistance, I didn't tought about the DB components, because I use them for a veeery long time and never had any issues. So I almost forgot about them :slight_smile:

1 Like
9

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.