Error Mormot in Interbase : select first 1 ID from tablename

bagusprasojo · 2016-09-02 07:21:21

Hello, I have tried mormot in SQL Server and PostgreSQL. On those DBMS, mormot runs smoothly. But, when i change dbms to Interbase i got error. It's error is caused by execution of SQL

select first 1 ID from tablename

In interbase, if we want to get firstrow of a table the sql is :

select ID from tablename rows 1

How to fix this error ?

ab · 2016-09-02 07:24:32

Interbase is not supported yet.
I though that FireBird did have the same syntax, but it appears it does not.

Some modifications are needed in SynDB.pas to add explicit recognition and process of Interbase.
I do not have Interbase, nor want to use it.
So external input is welcome!

miab3 · 2016-09-02 07:53:17

From Firebird 2.0 both queries are equivalent
http://firebirdsql.org/refdocs/langrefu … elect-rows
http://firebirdsql.org/refdocs/langrefu … first-skip

Michal

bagusprasojo · 2016-09-02 08:31:42

hello AB, i have solved the above error. But i got another error. Here is the error log :

20160902 07582138  !  +    SynDBFireDAC.TSQLDBFireDACStatement(01BB5A00).007ED1A9 
20160902 07582138  ! SQL   	SynDBFireDAC.TSQLDBFireDACStatement(01BB5A00) CREATE UNIQUE DESC INDEX NDXMorAssetID ON MorAsset(ID)
20160902 07582447  ! EXC   	EIBNativeException ("[FireDAC][Phys][IB]unsuccessful metadata update\r\nSTORE RDB$INDICES failed\r\nattempt to store duplicate value (visible to active transactions) in unique index \"RDB$INDEX_5\"") at 00721C9F  stack trace API 00544E02 
20160902 07582447  ! EXC   	EIBNativeException ("[FireDAC][Phys][IB]unsuccessful metadata update\r\nSTORE RDB$INDICES failed\r\nattempt to store duplicate value (visible to active transactions) in unique index \"RDB$INDEX_5\"") at 00721C9F  stack trace API 00544E02 
20160902 07582447  ! EXC   	EIBNativeException ("[FireDAC][Phys][IB]unsuccessful metadata update\r\nSTORE RDB$INDICES failed\r\nattempt to store duplicate value (visible to active transactions) in unique index \"RDB$INDEX_5\"") at 00721C9F  stack trace API 00544E02 
20160902 07582447  ! EXC   	EIBNativeException ("[FireDAC][Phys][IB]unsuccessful metadata update\r\nSTORE RDB$INDICES failed\r\nattempt to store duplicate value (visible to active transactions) in unique index \"RDB$INDEX_5\"") at 00721C9F  stack trace API 00544E02 004071EC 77B7718B 77B77017 00721C9F 00AF9238 00B0B394 00B0B521 007BE47F 007BE5B7 007BF5AF 007BE00E 0084100A 00841460 007B915C 007BF89C 00828D9B 0082906D 0082F52D 007F8DEE 0084352D 00607884 00607F6F 005B3EBD 005B416A 0059D133 006C367E 00B0E168 004990F5 0049D44D 
20160902 07582448  ! EXC   	EIBNativeException ("[FireDAC][Phys][IB]unsuccessful metadata update\r\nSTORE RDB$INDICES failed\r\nattempt to store duplicate value (visible to active transactions) in unique index \"RDB$INDEX_5\"") at 00721C9F  stack trace API 00544E02 
20160902 07582449  ! EXC   	EIBNativeException ("[FireDAC][Phys][IB]unsuccessful metadata update\r\nSTORE RDB$INDICES failed\r\nattempt to store duplicate value (visible to active transactions) in unique index \"RDB$INDEX_5\"") at 00721C9F  stack trace API 00544E02 
20160902 07582449  ! EXC   	EIBNativeException ("[FireDAC][Phys][IB]unsuccessful metadata update\r\nSTORE RDB$INDICES failed\r\nattempt to store duplicate value (visible to active transactions) in unique index \"RDB$INDEX_5\"") at 00721C9F  stack trace API 00544E02

Could you point me which part of this framework to be changed ?

Last edited by bagusprasojo (2016-09-02 08:32:23)

bagusprasojo · 2016-09-02 09:15:58

I have solved the above error. I change unit unit mORMotDB.pas

function TSQLRestStorageExternal.CreateSQLMultiIndex(
  Table: TSQLRecordClass; const FieldNames: array of RawUTF8;
  Unique: boolean; IndexName: RawUTF8): boolean;
var SQL: RawUTF8;
    ExtFieldNames: TRawUTF8DynArray;
    IntFieldIndex: TIntegerDynArray;
    Descending: boolean;
    i,n,extfield: integer;
begin
  ..........................................
      dPostgreSQL,dMSSQL,dMySQL,dOracle,dNexusDB: begin // as most DB on primary key
        result := true;
        exit;
      end;
      dFirebird:  // see http://www.firebirdfaq.org/faq205
        Descending := true;
      end;
   .........................................

tobe

function TSQLRestStorageExternal.CreateSQLMultiIndex(
  Table: TSQLRecordClass; const FieldNames: array of RawUTF8;
  Unique: boolean; IndexName: RawUTF8): boolean;
var SQL: RawUTF8;
    ExtFieldNames: TRawUTF8DynArray;
    IntFieldIndex: TIntegerDynArray;
    Descending: boolean;
    i,n,extfield: integer;
begin
  .........................................
      dPostgreSQL,dMSSQL,dMySQL,dOracle,dNexusDB, dFirebird: begin // as most DB on primary key
        result := true;
        exit;
      end;
//      dFirebird:  // see http://www.firebirdfaq.org/faq205
//        Descending := true;
      end;
    .........................................

bagusprasojo · 2016-09-02 09:21:05

one more addition, before using mormot + Interbase, we must create domain BIGINT. Here is the code

create domain BIGINT as Integer

ab · 2016-09-02 11:41:36

AFAIK Interbase Integer is 32-bit, so it won't work as expected - for 64-bit values.
See http://docwiki.embarcadero.com/InterBas … Data_Types

Interbase is disapointing, in this aspect... no native 64-bit integer type? In year 2016?

miab3 · 2016-09-02 13:46:18

Interbase NUMERIC(18,0)
Fierbird NUMERIC(18,0) or BIGINT

Michal

mORMot Open Source

#1 2016-09-02 07:21:21

Error Mormot in Interbase : select first 1 ID from tablename

#2 2016-09-02 07:24:32

Re: Error Mormot in Interbase : select first 1 ID from tablename

#3 2016-09-02 07:53:17

Re: Error Mormot in Interbase : select first 1 ID from tablename

#4 2016-09-02 08:31:42

Re: Error Mormot in Interbase : select first 1 ID from tablename

#5 2016-09-02 09:15:58

Re: Error Mormot in Interbase : select first 1 ID from tablename

#6 2016-09-02 09:21:05

Re: Error Mormot in Interbase : select first 1 ID from tablename

#7 2016-09-02 11:41:36

Re: Error Mormot in Interbase : select first 1 ID from tablename

#8 2016-09-02 13:46:18

Re: Error Mormot in Interbase : select first 1 ID from tablename

Board footer