Boolean and varchar Data type

Hmdsadeghian · 2018-08-01 08:41:16

Hi ab,

I want to declare boolean and varchar data types in my tables. And here is my model :

  TFinancialYears=class(TSQLRecord)
  private
    fEndDate: TDateTime;
    fClosed: Boolean;
    fClosedByUserId: Integer;
    fStartDate: TDateTime;
    fCloseDate: TDateTime;
    fCompanyId: integer;
  published
    property  CompanyId: integer read fCompanyId write fCompanyId;
    property  StartDate :TDateTime read fStartDate write fStartDate;
    property EndDate :TDateTime read fEndDate write fEndDate;
    property Closed:Boolean read fClosed write fClosed;
    property ClosedByUserId:Integer read fClosedByUserId write fClosedByUserId;
    property CloseDate :TDateTime read fCloseDate write fCloseDate;
  end;

But when the table is created in SQL Server, Integer types will map to int64, rawutf8 will map to nvarchar and boolean types will map to integer.
How can I declare exactly the same type as equal type in SQL Server in Delphi?

Thanks.

ab · 2018-08-01 09:43:40

The Boolean type is not handled by our SynDB layer.
So it is not possible to let the ORM create such columns.
You can try to create the table manually by SQL - I guess boolean columns may work with the ORM, but it will depend on the DB client itself....

Note that using an integer field could help your DB to evolve, if later on you switch from a boolean to an enumerate, to have more than 2 states.
If you really need a boolean column... then you are focusing on SQL, not objects, so perhaps you have to consider what an ORM is about.

Hmdsadeghian · 2018-08-01 16:53:08

Thanks a lot.
Is it possible to create varchar or integer field type? not nvarchar or int64.

Why mORMot doesn't support boolean type?

Last edited by Hmdsadeghian (2018-08-01 17:07:47)

mpv · 2018-08-01 21:37:03

There is a good answer on stackoverflow for this question https://stackoverflow.com/questions/114 … ase-design

Integer column with 0/1 value is the best choice for storing boolean to RDBMS imho

Hmdsadeghian · 2018-08-02 04:29:19

Thanks for your answer. from Performance perspective , the length of a record is smaller is better. because more rows can be saved on a page that is 8kb.
In the systems with high transactions , data types must be very precise.

ab · 2018-08-02 07:40:20

Your assumptions about DB was correct in 1980's but DB storage engine did change a lot since!
A BIGINT doesn't take more than a byte, in some DBs.

Even CLOB (nvarchar(max)) are faster and uses less space than size-specific fields in modern DB today.
See e.g. https://www.postgresql.org/docs/current … acter.html and the performance note.

EgonHugeist · 2018-10-20 05:48:44

ab wrote:

Your assumptions about DB was correct in 1980's but DB storage engine did change a lot since!
A BIGINT doesn't take more than a byte, in some DBs.
Even CLOB (nvarchar(max)) are faster and uses less space than size-specific fields in modern DB today.
See e.g. https://www.postgresql.org/docs/current … acter.html and the performance note.

?? thats a PG thing only and just an advantage for updates iirc. This has nothing todo with "modern". An index scan is getting slower here as a side effect.

mORMot Open Source

#1 2018-08-01 08:41:16

Boolean and varchar Data type

#2 2018-08-01 09:43:40

Re: Boolean and varchar Data type

#3 2018-08-01 16:53:08

Re: Boolean and varchar Data type

#4 2018-08-01 21:37:03

Re: Boolean and varchar Data type

#5 2018-08-02 04:29:19

Re: Boolean and varchar Data type

#6 2018-08-02 07:40:20

Re: Boolean and varchar Data type

#7 2018-10-20 05:48:44

Re: Boolean and varchar Data type

Board footer