Edit .fbk file?

[sirtet]

Background first, as i feel i may be asking the wrong question:
I want to automate the process of copying a DB from live- to test/training-DB.
As part of the copying, i  want to change some settings stored in the DB (change visual appearance, change SMTP Server to dummy, etc.).

The DB is part of my ERP system, so i don't have direct SQL write access. I can create .fdb backups, restore these in the test-system and manually alter the settings there.
But what i need is a one/few-click solution for regular use.

Looking into an .fdb file, i was surprised to see plain text strings in it.

So i thought, maybe it's possible to alter the backup via a script before restoring it.
Unsurprisingly, a plain edit of a string in the backup led to an error on restore.
I guess there's at least some checksums that would have to be updated too.

So, my questions:
Is editing a Backup possible at all?  

If so, how? 

Might it be reasonable in my Case? (i need to change a few dozen string- and integer- fields)

Suggestions for other approaches?

If editing .fdb is possible but dangerous, i think i could take that risk because it's only for restore in a test system, where errors/crashes won't hurt much (unless it crashes more than the ERP and Firebird).

The AI's out there said: "Direct edit is unusual, not recommended, dangerous... either do a restore>edit>backup, or use gbackup to manipulate the .fbk directly. The answers felt not much more informed than i feel myself.

[Mark Rotteveel]

On 11/02/2024 10:41, sirtet wrote:
[..]

> Looking into an .fdb file, i was surprised to see plain text strings in it.

Out of curiosity, why are you surprised by that? That is the data that
is being backed up.

> So i thought, maybe it's possible to alter the backup via a script
> before restoring it.
> Unsurprisingly, a plain edit of a string in the backup led to an error
> on restore.
> I guess there's at least some checksums that would have to be updated too.

No you can't do that (or at least, not without intimate knowledge of the
binary layout of the backup file).

> So, my questions:
> Is editing a Backup possible at all?

In theory, yes, in practice you really shouldn't do that, because it is
hard to do correctly, and making the modifications in an actual database
is generally a lot easier because you have the full power of SQL and
PSQL to do the work for you.

> If so, how?
> Might it be reasonable in my Case? (i need to change a few dozen string-
> and integer- fields)
> Suggestions for other approaches?

Restore the backup and apply a SQL script to modify those values.

> If editing .fdb is possible but dangerous, i think i could take that
> risk because it's only for restore in a test system, where
> errors/crashes won't hurt much (unless it crashes more than the ERP and
> Firebird).
>
> The AI's out there said: "Direct edit is unusual, not recommended,
> dangerous... either do a restore>edit>backup, or use gbackup to
> manipulate the .fbk directly. The answers felt not much more informed
> than i feel myself.

Although I'm not a fan of ChatGPT and similar AI, because it can be
wrong, very wrong, or subtly wrong a lot of the time, in this case it is
at least partially correct: you need to restore and then edit (using
SQL!). Backing up again is probably not necessary, unless for security
reasons the original data may not leave the original server, in that
case you need to restore and edit on the original server, and then back
up again, and restore on the destination server.

However, the "use gbackup to manipulate the .fbk directly" it suggested
makes no sense, because gbak doesn't have such a feature.

Mark
--
Mark Rotteveel

[sirtet]

Thanks a lot.

Out of curiosity, why are you surprised by that? That is the data that
is being backed up.

Lack of experience... I expected a binary format to be all unreadable, strings to be stored/compressed in a custom binary format too.

 

> Suggestions for other approaches?
Restore the backup and apply a SQL script to modify those values.

As stated, my problem with that (normally obvious) approach is, that i don't have direct SQL write access, for writes, there's only the Data manipulation UI and Import UI which only can target one table per import.

What i thought of but did not try yet is to import / (restore?) the fbk into a DB outside the ERP and check if i can execute UPDATE's there. I suspect i can't as the data in the .fbk is tied to a user? I guess the system-User, of which i don't have credentials...
I don't have much experience working with RDBMS besides running some SELECT's, so i expect a steep learning curve and think it's ok to ask here, without having invested hours of research and trial/error.

[Mark Rotteveel]

On 11/02/2024 11:23, sirtet wrote:
> Thanks a lot.
>
> Out of curiosity, why are you surprised by that? That is the data that
> is being backed up.
>
> Lack of experience... I expected a binary format to be all unreadable,
> strings to be stored/compressed in a custom binary format too.

Prior to Firebird 4.0, gbak only applies a very simple compression
(RLE), which will only compress repetitions of the same byte. Firebird
4.0 added the `-zip` option to gbak to compress the resulting backup
using zlib.

> > Suggestions for other approaches?
> Restore the backup and apply a SQL script to modify those values.
>
> As stated, my problem with that (normally obvious) approach is, that i
> don't have direct SQL write access, for writes, there's only the Data
> manipulation UI and Import UI which only can target one table per import.

If you can backup and restore the database, then should also be able to
connect to the database with a query tool (e.g. ISQL. FlameRobin, or
DBeaver, or IB Expert, etc.). If necessary, on a separate Firebird
installation.

> What i thought of but did not try yet is to import / (restore?) the fbk
> into a DB outside the ERP and check if i can execute UPDATE's there. I
> suspect i can't as the data in the .fbk is tied to a user? I guess the
> system-User, of which i don't have credentials...

That is only relevant if you restore to the same Firebird installation,
if you restore to your own Firebird installation, then you control the
credentials. Potentially you may have to create a user with the same
name, and maybe grant it database creation permissions, but then you'll
know the password.

So, if you don't have sufficient access on either the original or
destination systems, then:

1) Install Firebird to another, intermediate system
2) Backup the database on the original system
3) Restore the backup to the intermediate system using gbak
4) Modify the database as needed (using ISQL or another query tool)
5) Backup the database on the intermediate system using gbak
6) Restore on the destination system

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

sirtet wrote 11.02.2024 11:23:
> As stated, my problem with that (normally obvious) approach is, that i don't
> have direct SQL write access, for writes, there's only the Data manipulation UI
> and Import UI which only can target one table per import.

You stated that you don't have direct SQL access to production server, but
test-system is usually located on different server and is under full control of
developer.

> I suspect i can't as the data in the .fbk is tied to a user?

No. Users belongs to server. Different server - different users. Backup file
is just a bunch of data exported from database.

> I don't have much experience working with RDBMS besides running some SELECT's, so i expect a steep learning curve and think it's ok to ask here, without having invested hours of research and trial/error.

"Getting Started" manual is quite short and hardly can take hours to read.

--
WBR, SD.

[liviuslivius]

Hi

You overcomplicste things. If you have database at hand, you can connect to it with embedded acces which does not reqire the password at all. So connect to restored database and modify what you want.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: sirtet <thomas.r...@gmail.com>

Data: 11.02.2024 10:41 (GMT+01:00)

Do: firebird-support <firebird...@googlegroups.com>

Temat: [firebird-support] Edit .fbk file?

--
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/18a3ac8e-5f60-46f4-9557-02d7e5a41a68n%40googlegroups.com.

[sirtet]

Thanks again to all of you.

You stated that you don't have direct SQL access to production server, but
test-system is usually located on different server and is under full control of
developer.

I was unclear there. It's all on one server, a single installation of the ERP Software, which installed FB as part of itself. It's started with different parameters to use the live- or test- DB.
So the same access restrictions apply to both of them, even if they were on different Servers, as far as i understand.

 

"Getting Started" manual is quite short and hardly can take hours to read.

Yes, i did read in that, but to also understand, let alone test/try implement my  ideas, hours quickly pile up. 

I am already multiple levels off of "just installing and then using" my ERP, and therefore also far out of my comfort zone, yet too stubborn and curious to give up when the vendor is not offering the solution i need.

 

So i guess next i have to try restore>modify>backup locally, and then see if i can do a 2nd FB install on the Server... Quickstart says "Setting this up is not a beginner’s task"...
unless...

If you have database at hand, you can connect to it with embedded acces which does not reqire the password at all.

You are referring to this?
https://firebirdsql.org/rlsnotesh/install2-win32-embed.html
Sounds promising, but after a first read i don't understand what " your application " is referencing to.
Will have to search for more info on that. 

Using the embedded server, would i be able to create a batch- powershell- python- whaterver- script that runs a few UPDATE's on a copy of my DB? Would that be ISQL, that acts through the embedded server?
I guess then i won't even need a .fbk file, but just copy the original .fdb, update it via the embedded server, and done.

[Karol Bieniaszewski]

>> You are referring to this?

 

Simply connect to DB without the server name/ip address. If you connect succesfully, then embeded access 😊

 

Regards,

Karol Bieniaszewski

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/e9272a92-95b5-4595-969b-4e1955ebc941n%40googlegroups.com.

 

[Elmar Haneke]

https://firebirdsql.org/rlsnotesh/install2-win32-embed.html
Sounds promising, but after a first read i don't understand what " your application " is referencing to.

That simply means the application you intend to use to access database.

In your case it is simply isql.exe or perhaps flamerobin.

Using the embedded server, would i be able to create a batch- powershell- python- whaterver- script that runs a few UPDATE's on a copy of my DB? Would that be ISQL, that acts through the embedded server?

ISQL can do the job.

I guess then i won't even need a .fbk file, but just copy the original .fdb, update it via the embedded server, and done.

Presumably yes - at least if it is not blocked by database encryption.

You should use the same version as your application in this case.

[sirtet]

My DB is V4, but the link above is from 2.5 and did not help in my case. I googled this
https://www.ibphoenix.com/files/Embedded_fb3.pdf
and it seems to work for 4.0 too.
(Are there any official infos for embedded on FB4?)

I have to use isql with the -nod parameter, else a trigger prevents logging in as SYSDBA: "no login for sysdba allowed".
With it, i was able to do an UPDATE.
I guess i'll have to find out if this will work in the end, or if i miss the execution of some some triggers that normally run after updating those fields that i intend to alter.

[Elmar Haneke]

I have to use isql with the -nod parameter, else a trigger prevents logging in as SYSDBA: "no login for sysdba allowed".

Presumably it is an good idea to find out which username the application uses and open database specifying that user instead of SYSDBA. This soult enable you to run your updates with triggers active.

[Mark Rotteveel]

-NOD doesn't disable all triggers, it only disables database triggers
(e.g. on connect, on disconnect, etc).

Mark
--
Mark Rotteveel

[sirtet]

resurrecting this old thread...
Thanks to all the answers i was able to do what i needed, using embedded firebird and isql.exe.
With C:\embedded-firebird\isql.exe mydbalias -user sysdba -nod i can connect to my db with write access.
As explained earlyer, the -nod lets me get around a trigger that prevents sysdba from login.
What i did not achieve so far:
How (if at all) can i use a tool like flamerobin or DBeaver to connect through the embedded firebird? My issue is, i don't see how i would use the -nod argument from flamerobin

[Elmar Haneke]

As explained earlyer, the -nod lets me get around a trigger that prevents sysdba from login.
What i did not achieve so far:
How (if at all) can i use a tool like flamerobin or DBeaver to connect through the embedded firebird? My issue is, i don't see how i would use the -nod argument from flamerobin

It should be much easier to connect with another username that is not blocked out by triggers.

Either embedded does ignore password or you can reset it when logged in as sysdba.

Elmar

[Mark Rotteveel]

On 20/02/2025 23:39, sirtet wrote:
> resurrecting this old thread...
> Thanks to all the answers i was able to do what i needed, using embedded
> firebird and isql.exe.

> With *C:\embedded-firebird\isql.exe mydbalias -user sysdba -nod* i can

> connect to my db with write access.

> As explained earlyer, the *-nod* lets me get around a trigger that

> prevents sysdba from login.

> What i did not achieve so far:
> How (if at all) can i use a tool like flamerobin or DBeaver to connect
> through the embedded firebird? My issue is, i don't see how i would use
> the -nod argument from flamerobin

For FlameRobin, it needs to use a fbclient.dll of a Firebird Embedded,
but AFAIK, you cannot specify the equivalent DPB item
(isc_dpb_no_db_triggers).

However, have you considered using a different user (one with sufficient
privileges defined in the database)?

For DBeaver, there are several ways, but the "easiest" is the following:

Ensure Jaybird 6.0.0 or higher is used. Go to Database, Driver Manager,
select Firebird, click Edit, go to tab Libraries, check if the version
of org.firebirdsql.jdbc:jaybird is 6.0.0 (or higher). If not, select it
and click Download/Update, in the new dialog, click on the version
number, and select 6.0.0 (or a higher version, once available), and
click Download.

You'll also need to add the jaybird-native dependency. In the Libraries
list, click "Add artifact", go to tab "Declare Artifact Manually", and
enter:

- Group Id: org.firebirdsql.jdbc
- Artifact Id: jaybird-native
- Version: 6.0.0 (or the same version as your main jaybird artifact)

In the connection properties of your connection, use Connect by: URL,
specify jdbc:firebird:embedded:<database-path-or-alias>, e.g.
jdbc:firebird:embedded:C:\DB\somedatabase.fdb

On tab "Driver properties", add the following properties (use right
click, Add new property): nativeLibraryPath, no_db_triggers

Give the properties the following values:

- nativeLibraryPath: directory of your Firebird Embedded installation
(e.g. C:\Firebird\Firebird-5.0.2.1613-0-windows-x64)
- no_db_triggers: 1

Be aware that the nativeLibraryPath property is only used once in the
run time of an application, so you cannot use it to connect to different
databases with different versions of Firebird Embedded: the first
version of the library loaded will be used for *all* embedded
connections in the same run time of a program.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 21/02/2025 10:02, Elmar Haneke wrote:
>
>> As explained earlyer, the *-nod* lets me get around a trigger that

Embedded ignores password (on Windows always, on Linux since Firebird 3.0).

Mark
--
Mark Rotteveel