UIB (Unified Database) and Firebird 4

[Stéphanie Cortelli]

Hello,

anyone has used UIB (Delphi/Lazarus db tool) with Firebird 4?

At the moment I've no users with Firebird 4.0. But if a user upgrades to Firebird 4 I don't know if the UIB still works, even without using the new features of Firebird 4.

For example, with version 3 of Firebird, an upgrade of the UIB was necessary.

However there are no recent updates for the UIB (that is still supported from the developers), and Firebird 4 sorted at June 2021.

In the future could happen that a user has already Firebird 4 installed.

Sorry for my bad English.

Thank you and best regards,

Stephanie

[Mark Rotteveel]

On 05-10-2022 09:56, Stéphanie Cortelli wrote:
> anyone has used UIB (Delphi/Lazarus db tool) with Firebird 4?
>
> At the moment I've no users with Firebird 4.0. But if a user upgrades to
> Firebird 4 I don't know if the UIB still works, even without using the
> new features of Firebird 4.
>
> For example, with version 3 of Firebird, an upgrade of the UIB was
> necessary.
>
> However there are no recent updates for the UIB (that is still supported
> from the developers), and Firebird 4 sorted at June 2021.
>
> In the future could happen that a user has already Firebird 4 installed.

I have no experience with the library, but likely it will work just fine
as long as no types introduced in Firebird 4.0 are used (this probably
would have been the case with Firebird 3.0 as well, to be honest).

If any new types are used, this can probably be addressed by using the
DataTypeCompatibility setting (globally in firebird.conf, or for a
specific database in databases.conf), the isc_dpb_set_bind connection
property and/or the `SET BIND` management statement.

Mark
--
Mark Rotteveel

[Stéphanie Cortelli]

Thank you for your reply!

At the moment I don't use any of the new tipes of Firebird 4, the db must be still compatible with the previous versions.

The question is, sooner or later a user will have Firebird 4: with Firebird 3, the previous UIB didn't work at all (no connection possibile), even if the db didn't use anything new of the version 3, only with the upgrade of the UIB they worked.

I hope that with UIB and Firebird 4 it's different, I saw this message, that would seems to confirm the compatibility:

https://www.pilotlogic.com/sitejoom/index.php/forum/database-development/5886-pl-uib-and-firebird-4.html#17258

CodeTyphon Studio by PilotLogic is based on Lazarus/FPC (CodeTyphon it's substantially a Lazarus installation with many packages installed by default), so the UIB should be the same, I believe.

But if someone has had direct experience with the "official" UIB on Lazarus/FPC this would be very useful to know.

Best regards,

Stephanie

Il 05/10/22 10:02, Mark Rotteveel ha scritto:

[Mark Rotteveel]

On 05-10-2022 10:34, Stéphanie Cortelli wrote:
> At the moment I don't use any of the new tipes of Firebird 4, the db
> must be still compatible with the previous versions.
>
> The question is, sooner or later a user will have Firebird 4: with
> Firebird 3, the previous UIB didn't work at all (no connection
> possibile), even if the db didn't use anything new of the version 3,
> only with the upgrade of the UIB they worked.

As I said, I don't know UIB, but - as a driver developer - I don't
exactly see why it wouldn't be able to connect. As far as I can tell, it
uses fbclient, and the legacy API hasn't changed a lot in the past two
decades (basically only additive). If fbclient can connect, so should
libraries using that API. My guess is that it did something after
connect that returned a BOOLEAN datatype, and that broke things, Or
using an older fbclient.dll, with the default settings for AuthServer
and WireCrypt.

However, keep in mind, even if *your* code doesn't use new datatypes,
Firebird 4.0 metadata (RDB$ and MON$ tables) might. And if your code
currently uses CURRENT_TIMESTAMP or CURRENT_TIME, you should change to
LOCALTIMESTAMP or LOCALTIME (also backported to Firebird 2.5.9 and 3.0.4+).

Configuring the `DataTypeCompatibility` setting might be a prudent
course of action.

Mark
--
Mark Rotteveel

[Stéphanie Cortelli]

Hi Pierre,

first of all thank you very much for your work, I find UIB very good and rapid!

At the moment, without using new FB4 datatypes, does the current version of UIB work on FB4?

That is, if a user upgrades to FB4, but the db remains compabile with UIB 3, is it ok?

Thank you again, best regards,

Stephanie


---------------------------------------------------------
I "maintain" UIB here : https://github.com/zedalaye/uib

I use it with Firebird 3.0+ without problems. I started integrating new FB4 datatypes but as I don't plan to upgrade (way to complex) I let this project for later.

--
Pierre Yager

[Stéphanie Cortelli]

Thank you very much, your explanation is very clear and very helpful, I hadn't thought that metadata could use anyway the new datatypes.

So on a FB4 I can set DataTypeCompatibility = 3.0 in both firebird.conf and databases.conf and all should work.

In the next time I will make sure that all users have at least Firebird 2.5.9 or Firebird 3.0.4+, in order to replace CURRENT_TIMESTAMP or CURRENT_TIME with LOCALTIMESTAMP or LOCALTIME, in this way when the UIB will support the new FB4 datatypes the DataTypeCompatibility will be no more necessary.

Thank you again and best regards,

Stephanie

Il 05/10/22 10:47, Mark Rotteveel ha scritto:

[Virgo Pärna]

On 05.10.2022 12:37, Stéphanie Cortelli wrote:
>
> So on a FB4 I can set DataTypeCompatibility = 3.0 in both firebird.conf
> and databases.conf and all should work.

There is also just issuing:
SET BIND OF TIMESTAMP WITH TIME ZONE TO LEGACY;
SET BIND OF TIME WITH TIME ZONE TO LEGACY;
SET BIND OF DECFLOAT TO LEGACY;
SET BIND OF NUMERIC(38) TO LEGACY;

after connecting to Firebird 4 database.



--
Virgo Pärna
Gaiasoft OÜ
vi...@gaiasoft.ee

[Stéphanie Cortelli]

Hello and thanks for your reply, SET BIND is alternative to DataTypeCompatibility settings?

If I have good understand:

1) If I set DataTypeCompatibility = 3.0 in the firebird.conf file this applies to all databases;

2) If I set DataTypeCompatibility = 3.0 in the databases.conf file this applies only to the specified database, for example:

  # Directives for MyDb1
  MyDb1 = opt/databases/mydb1.fdb
     {
        DataTypeCompatibility = 3.0
     }

  # Directives for MyDb2
  MyDb2 = opt/databases/mydb2.fdb
     {
        DataTypeCompatibility = 3.0
     }

3) if I use SET BIND via SQL this applies only to the current connection.

Is it right?

Thank you again, best regards,

Stephanie

Il 06/10/22 05:31, Virgo Pärna ha scritto:

[Virgo Pärna]

On 07.10.2022 00:16, Stéphanie Cortelli wrote:
> If I have good understand:
> 1) If I set DataTypeCompatibility = 3.0 in the firebird.conf file this
> applies to all databases;
> 2) If I set DataTypeCompatibility = 3.0 in the databases.conf file this
> applies only to the specified database, for example:

> 3) if I use SET BIND via SQL this applies only to the current connection.
>
> Is it right?

That is my understanding. Advantage of SET BIND is, that allows tools,
that support new data types work with those new data types, which might
be advantageous. Also, they work, when one can change application and
not server configuration. And they allow set compatibility more gradually.

Also, something to remember - new numeric types are used for
calculation results.

[Peter]

Our experience. We have tried to upgrade to FB4 using the DataTypeCompatibility = 2.5 setting.

Delphi XE and FIBplus components.

It was unsuccessful with problems arising deep in the FIBplus code. As I do not want to fiddle with that we eventually decided to only upgrade to FB 3.

That transitions was relatively painless, the only problem we encounter is occasionally with persistent fields which have the wrong type. This is quite trivial to fix.

Peter Reber

Technisoft

[Ertan Küçükoglu]

Did you find any documentation that says DataTypeCompatibility can be set to 2.5? My knowledge, it can be 3.0 only.

Peter <pre...@telkomsa.net>, 7 Eki 2022 Cum, 09:05 tarihinde şunu yazdı:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/e910686d-c57a-45cf-8546-a19370b748f2n%40googlegroups.com.

[Peter]

There are 2 possible settings according to the manual

DataTypeCompatibility= 2.5

DataTypeCompatibility= 3.0

I'd like to add that we tried setting it in firebird.conf as well as using the

SET BIND ... statements in the ON_CONNECT event

However at the time we did not try

SET BIND datatype to LEGACY

Issues arose with the TIMESTAMP type where an exception was raised in a code portion I do not want to touch especially as FB3 seems to solve a totally different problem we had on one particular system only. We still think that it was something else on that system that caused the original problems where FB2 was used. Maybe the phone management system, Webroot anti-virus, or something else.

Peter Reber

Technisoft

[Stéphanie Cortelli]

Il 07/10/22 08:14, Ertan Küçükoglu ha scritto:

> Did you find any documentation that says DataTypeCompatibility can be
> set to 2.5? My knowledge, it can be 3.0 only.

Hello Ertan,

there is this official documentation on Firebird that allows 2.5 or 3.0
als setting:

https://firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html

DataTypeCompatibility

Specifies the compatibility level that defines what SQL data types can
be exposed to the client API. Currently two options are available: "3.0"
and "2.5". The "3.0" emulation mode hides data types introduced after
Firebird 3.0 release, in particular DECIMAL/NUMERIC with precision 19 or
higher, DECFLOAT, TIME/TIMESTAMP WITH TIME ZONE. The corresponding
values are returned via data types already supported by Firebird 3.0.
The "2.5" emulation mode also converts the BOOLEAN data type. See the
Native to Legacy Coercion Rules table for details. This setting allows
legacy client applications to work with Firebird 4.0 without recompiling
and adjusting them to understand the new data types.

Best regards,

Stephanie

[Stéphanie Cortelli]

Hello Virgo,

thank your for your reply.

I've a very complex project with many forms and many db components.

Of course I could modify the UIB component in order to automatically make a SET BIND after every connection.

However if I use DataTypeCompatibility = 3.0 in the databases.conf I should resolve easier, as UIB now don't support the new data types, so I believe that I've not other benefit using SET BIND.

When UIB will support FB4, I've only to upgrade UIB and delete the DataTypeCompatibility cause, without changing my code.

Or would I have other benefits using SET BIND, that justify to modify the actual UIB?

Thank you again and best regards,

Stephanie

Il 07/10/22 07:53, Virgo Pärna ha scritto:

[Dimitry Sibiryakov]

Stéphanie Cortelli wrote 07.10.2022 10:04:
> Of course I could modify the UIB component in order to automatically make a SET
> BIND after every connection.

"SET BIND" can be issued in ON CONNECT trigger as well.

--
WBR, SD.

[Stéphanie Cortelli]

Thank you very much, I didn't know, as I used triggers only for tables.

So I could do:

SET TERM !! ;

CREATE OR ALTER TRIGGER DATABASE_ON_CONNECT
ACTIVE ON CONNECT POSITION 0
AS
BEGIN

  SET BIND OF TIMESTAMP WITH TIME ZONE TO LEGACY;
  SET BIND OF TIME WITH TIME ZONE TO LEGACY;
  SET BIND OF DECFLOAT TO LEGACY;
  SET BIND OF NUMERIC(38) TO LEGACY;

END!!

SET TERM ; !!

With Firebird 3 I become an error:

Token unknown - line 7, column 3
SET

but I suppose that with FB 4 it should work.

In this case it's not necessary to use DataTypeCompatibility = 3.0 in databases.conf , isn't it?

Thank you again, best regards,

Stephanie

Il 07/10/22 11:29, 'Dimitry Sibiryakov' via firebird-support ha scritto:

[Mark Rotteveel]

Personally, I wouldn't choose to use SET BIND in an ON CONNECT trigger.
Then why not just add DataTypeCompatibility = 3.0 in databases.conf for
that database?

Also instead of SET BIND, a client can also specify the isc_dpb_set_bind
connection property (though of course, that does require using a
connection library which is aware of this DPB item).

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

Mark Rotteveel wrote 07.10.2022 14:07:
> Personally, I wouldn't choose to use SET BIND in an ON CONNECT trigger. Then why
> not just add DataTypeCompatibility = 3.0 in databases.conf for that database?

In the trigger it is possible to recognize attached application and set the
binds conditionally for those you cannot fix.

--
WBR, SD.

[Ismael L. Donis Garcia]

I use

set bind of timestamp with time zone to legacy;
set bind of time with time zone to legacy;
set bind of int128 to legacy;
set bind of decfloat to legacy;

Are there differences between
set bind of int128 to legacy;
and
set bind of numeric(38) to legacy;

or is it the same?

Best Regards
--
Ismael
Devuan User: http://distrowatch.com/table.php?distribution=devuan
Web Site:
https://gitlab.uic.cu/ismael.donis/sistema-de-contabilidad-general/

--
You received this message because you are subscribed to the Google Groups
"firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit

https://groups.google.com/d/msgid/firebird-support/6c6f1744-c88d-b0ef-42b8-60d8b554c943%40ibphoenix.com.

[Mark Rotteveel]

On 07-10-2022 15:08, Ismael L. Donis Garcia wrote:
> I use
>
>  set bind of timestamp with time zone to legacy;
>  set bind of time with time zone to legacy;
>  set bind of int128 to legacy;
>  set bind of decfloat to legacy;
>
> Are there differences between
>  set bind of int128 to legacy;
> and
>  set bind of numeric(38) to legacy;
>
> or is it the same?

No, it is not the same. As described in the docs[1]:

"""
Changing the binding of any NUMERIC or DECIMAL data type does not affect
the underlying integer type. In contrast, changing the binding of an
integer data type also affects appropriate NUMERICs/DECIMALs (e.g. SET
BIND OF INT128 TO DOUBLE PRECISION will also map NUMERIC and DECIMAL
with precision greater than 19 as these types use INT128 as their
underlying type).
"""

In other words, `set bind of int128 to legacy` will also change
numeric(38) and decimal(38) as both are a sub-types of int128, but `set
bind of numeric(38) to legacy` will only change numeric(38), and not
change int128 nor decimal(38). However, not that it will change **all**
numeric types with a precision of 19 or greater, irrespective of scale.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-management-setbind

--
Mark Rotteveel

[Marsupilami79]

Hello Stephanie,

how many TUIBDataBase components do you have in your application? Usually it should be possible to execute the "set bind" statements in the AfterConnect event?

With best regards,

Jan

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/b39c7856-4f16-12bd-21c0-945c6ef9e4ec%40gmail.com.

[Stéphanie Cortelli]

Thank you all for your replies.

I've many, many TUIBDataBase components in my project. It requires time to upgrade the code on every AfterConnect event, so if with DataTypeCompatability I can solve, it is easier for me.

In the future I hope that UIB will support new types of FB4, in the meantime I need to be able to connect to FB4 if a server has already FB4 installed.

Thank you again for your help, now I know that I can somehow connect to FB4 database.

Best regards,

Stephanie

Il 07/10/22 15:30, Marsupilami79 ha scritto:

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/00709f6d-5e1a-24b8-0bbc-b2ce580baaca%40gmx.de.

[Pierre Y.]

I have a question : why would you have to deal with new firebird datatypes if you don't use/need them since you are migrating an existing database from a previous version ?

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/6066db02-6ce4-5b44-a3f8-52c1c7d7a5a3%40gmail.com.

[Peter]

One change I am aware of is that COUNT() returns INTEGER in FB2 but BIGINT in FB3.

This is not a problem until one has persistent fields in a query component where the query datatype does not match the result data type.

Peter

Technisoft

[Virgo Pärna]

On 10.10.2022 10:21, Peter wrote:
> One change I am aware of is that COUNT() returns INTEGER in FB2 but
> BIGINT in FB3.
> This is not a problem until one has persistent fields in a query
> component where the query datatype does not match the result data type.
>

From the 4.0 release notes:
The result of various mathematical operations, such as LOG(), EXP() and
so on, and aggregate functions using a high precision numeric argument,
will be DECFLOAT(34).
Which means, that aggregate functions can return unexpected new data
types. And int128 support in programming languages is much less common,
then int64.
There are also metadata tables, that changed fields from timestamp to
timestamp with timezone.
Also, CURRENT_TIME now returns timestamp with timezone.

[Stéphanie Cortelli]

Hello Pierre,

the reason is that I would need that my software, which uses UIBs (which I find excellent!!) is compatible with an installation of Firebird 4.

Now that FB4 is released, I might find situations where FB4 is already installed, for example because there is other software that uses FB4.

Sometimes I have the opposite problem, I have situations in which there is other software that still needs FB2.5, or in which FB2.5 still runs on the server, even if these situations are now quite rare.

Furthermore it is also likely that in the future new Linux distros have FB4 in their repositories, so to have FB3 it is necessary to install it from the tar.gz file (although this isn't a great problem, but often these aspects are managed by an user's technician, and not by me).

For this it would be very important that I can be sure that, even without using the new datatypes of FB4, the UIBs are compatible with a FB4 installation.

Then when the UIB will also manage the new datatypes of FB4 even better, but this can happen at a later time, also because in any case if I use the new datatypes I would lose compatibility with the previous versions of Firebird and now it is still too early to do so.

Of course, when I can choose, I choose FB3 to install, but this doesn't always depend on me.

Thank your very much for your work with the UIB, I use them since 2006 and I'm very glad with them.

Best regards,

Stephanie

Il 10/10/22 09:10, Pierre Y. ha scritto:

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CABZLKTWT_08hQ2TN3%2Bxa%2B2JkaTnK_QbJOWi%3Du-OiLkx%2BjRnZWQ%40mail.gmail.com.

[Stéphanie Cortelli]

Hello Brahim,

I USE UIB with Lazarus. With FB3 all works great for me (I don't use UIB for backup/restore db, but gbak via TProcess).

Best regards,

Stephanie

Il 25/10/22 19:52, DjaSoft softdz ha scritto:

Hello Stephanie,

Can you tell please if you use UIB for delphi or lazarus, I am using it with delphi  and FB3 it works fine except for restoring db

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/88f06fc2-9ac4-d376-a59a-fc0823e32ba6%40gmail.com.

--

Cordialement,

Brahim Mohamed

Support

mobile

+(213) 06 62 37 64 94

+(213) 05 52 39 25 65

e-mail

sup...@softdz.com

con...@softdz.com

site internet

djasoft.com
softdz.com

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/rG6A8N3OrIs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAGTJwZKBSfD6kksfH1BoY%3Dr79StrH28-XgQkS_2oUHiv_Su0ww%40mail.gmail.com.

[DjaSoft softdz]

Thanks for the answer Stephanie

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/d068183b-dd9e-8d5b-53e4-1cd5f0f16952%40gmail.com.