Incompatible FireBird and InterBase??

Hi.

We are using the free version of InterBase for 3 years. It is v601 on both windows and linux. There are some serious problem so we think that it would be better to change to FireBird. The main problems are:
1., The gdb file (database file) size is 2 GB. The InterBase runing on windows can use the file but the InterBase on linux has the next error message "File size too large." while we try toto connect to the gdb.
2., There are many times the next message on the client application: "violation of primary key....". This is very exciting because the application execute a command to insert a row into a table and this table's primary key is an autoincrement field with a trigger and a generator. So how the hell can be this kind of error message?

The database has 240 tables, 204 stored procs, 362 triggers...

Do U think that is this a good idea to change the database server from interbase to firebird?

If we change the database server the client application will be able to work correctly? Is there anything to modify in the gdb?

I mean I tried FireBird of course! That is why ask U.
I have some querys which can run on interbase but not on firebird.
That means InterBase and FireBird are not compatible 100%!

Some examples of problems querys:

1.,
SELECT e.EGYSEG_HK,
(select max(ceg) from params where egyseg_azon = e.egyseg_azon)
from egyseg e
where egyseg_tip = 'N'
group by e.EGYSEG_HK

This can run on InterBase, but on FireBird there is an error msg:
"Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)."
In FireBird I have to use the SUM agregate function for the second field of the select.

2.,
SELECT *
FROM egyseg e
join dolgozo d
on d.egyseg_azon = e.egyseg_azon
WHERE EGYSEG_AZON = 'LV'

In FireBird I have to use the alias for field egyseg_azon after the where.
The FireBird's error msg is:
"Ambiguous field name between table EGYSEG and table DOLGOZO .
EGYSEG_AZON."

3.,
SELECT MT_AZON,
sum(RM_BRUTTO)
FROM RM
GROUP BY MT_AZON
ORDER BY RM_BRUTTO

This can run on InterBase, but on FireBird there is an error msg:
"Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause)."
In FireBird I have to use the order by like this: "ORDER BY 2"

After all that is my main questioin:
Where can I found a list of incompatibility InterBase and FireBird?

Thanks.

I don't know of an Interbase-Firebird incompatibility list, but, from my (Oracle - MsSQL mainly) point of view Firebird's syntax is more strict but much more accurate!

I have noticed the same problem with firebird that you post under problem with group by clause ... the only way that I have found to work around it is to do:

SELECT COALESCE(MAX(SD.uniid), 0), DT.jobno, LT.unid
FROM detlentr DT
LEFT JOIN ledgertrans LT
ON ( DT.unumber = LT.sourceid )
LEFT JOIN shipdetl SD
ON ( DT.jobdetno = SD.jobdetno )
GROUP BY DT.jobno, LT.unid ")

if I leave LT.unid out of group by clause then I get the same error you were getting but if I include LT.unid in group by clause it works ... Hope this helps and maybe someone has an explanation for it or better work around.

""violation of primary key....". This is very exciting because the application execute a command to insert a row into a table and this table's primary key is an autoincrement field with a trigger and a generator"

well - I know that sometimes Interbase generators can change their values aribtrarily (I don't know whether it is an bug, but it certainly occures when the Interbase datapage is too small for containing all the generators in database in one page) - so check the current values of generators and - possibly the second problem will end.

Regarding the first problem - db can be organized in multiple fiels, I have heard about 35 GB large Interbase databases

Selecting ALL the columns of the 2 tables gives you that ambiguous field error ... you SHOULD only select the required fields (that is a major query design error unless you actually need all the records) and also try this:
you can also use inner join / left join and right join ...


Here you can avoid the error by selecting the RM_BRUTTO ... example:


Anyway ... you should expect some language improvements in FireBird and in time you will see their advantage too

After all that is my main questioin:
Where can I found a list of incompatibility InterBase and FireBird?

Thanks.[/QUOTE]


We recently converted our InterBase 6.01 application to Firebird 2.0 Beta and kept a record of differences and their effect on our conversion process.

Before we started, we searched for any migration guidance document on the web and did not find any one summarizing document, so we decided to keep a record and make it available to others who might want to tread the same path.

If you are considering InterBase to Firebird conversion you can find our notes at
http://www.topicscape.com/InterBase_Firebird_migration/IB2FB.php

Roytsganda