Read Only connection to DB

[Arun J]

Is there a way to access/read the DB (file) when there is another process writing to it?

In our use case the process writes to one DB file per day (telemetry data) and we wanted read on a periodic basis the data being written.  One way is to clone/copy/snapshot the file to another location and read it - not sure whether the DB file will be in a consistent state if we do that - considering this as the file size would not exceed beyond few MBs.

Any good options for doing this one?

TIA,

JAK

[Jiří Činčura]

> Any good options for doing this one?

If you're using server connection, you can have as many processes accessing the database as you want. Server will handle that for you.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Arun J]

Thanks.

While accessing/connecting I am getting this error - looks like the process has taken exclusive write rights to this DB file

Statement failed, SQLSTATE = 08001
I/O error during "CreateFile (open)" operation for file "XXXXX.FDB"
-Error while trying to open file
-The process cannot access the file because it is being used by another process.

--
You received this message because you are subscribed to the Google Groups "firebird-java" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-jav...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/d0ff4996-36cf-4319-a90d-b58d7daff9b1%40www.fastmail.com.

[Adam McMahon]

Is the dB being opened up in server mode?  Are you using the correct port and ip?

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/CA%2B7f3%3DdAGQyzkZ6nE_NQLehCthwn54pgHqjUapKzdhcvV-aqwQ%40mail.gmail.com.

[Arun J]

Adam,

I don't know the details of the writing process - whether it is opened up in the server mode - is there a way to check it?  ( I have tried through command line/isql and still get the same error and it works fine with other FDB files which are not being accessed hence ruling out those kind of issues)

It is a 3rd party process that writes to it hence I am not sure what mode they open and write?

~JAK

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/CANa5PgK_EGFo2fo-gE4SUMWQCjcALOYHucX30tQbfn3SnXrOLw%40mail.gmail.com.

[Jiří Činčura]

> I don't know the details of the writing process - whether it is opened
> up in the server mode - is there a way to check it? ( I have tried

One way would be stopping the server and observing whether the process fails to access the DB.

[Arun J]

Stopped the server that I used to connect - it still writes the DB file - i.e it uses different instance to write - could be embedded DB.

~JAK

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

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/b59084b1-e642-4388-a043-ec2fb8d80211%40www.fastmail.com.

[Arioch The]

While accessing/connecting I am getting this error - looks like the process has taken exclusive write rights to this DB file

The server should enforce exclusive file access indeed. 

Statement failed, SQLSTATE = 08001
I/O error during "CreateFile (open)" operation for file "XXXXX.FDB"

Sounds Windowsesque. 

Run SysInternals Process Monitor, set filters for File I/O and for the DB file and see which processes do reads and writes. 

-Error while trying to open file
-The process cannot access the file because it is being used by another process.

It sounds like one application is accessing the DB using embedded (in-process DLL/SO) server while another application does so using stand-alone (separate process) server. 

What are connection strings with all you apps? 

It became complicated in Firebird 3, there is no way to explicitly request standalone or embedded mode, but you may start including TCP/IP addresses in all your connection strings. This as a side effect should implicitly make stand-alone servers used at a price of some overhead, usually small, comparing with no-network (XNET) connection mode. 

[Arun J]

Thanks Arioch.

Found the process that is writing to the FDB file.  Looks like it used DLL to do so directly.  I am running a separate instance of Firebird (2.5) to access this file.

As mentioned, I am trying first using command line (isql) to access the data - while connecting I am getting file share violation error.

What are my options here to access this file - I have zero control over the writing process as it is a black box.

~JAK

--

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

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/CAB_oP36mLMpXSq6HN0Gfs4Ht7MNvPXzJ%3DzN1%2BP6OpjHSi%3DQ%3DTQ%40mail.gmail.com.

[Arioch The]

with FB 2 on Windows it is easier

there is "lean" fbclient.dll (about 800 KB) which only contains "wire
protocol" parser/router (and utilities, like error messages, etc).

there is "fat" fbembed.dll which contains both protocol routing AND
engines for several formats (ODS versions) of database files, starting
with ODS 9 aka Interbase 5 of 1990-s

fbembed.dll can be renamed into fbclient.dll, API-wise they are the same.

the connection string application passes to the FB client can include
TCP/IP marker, or Windows network marker (obsolete, almost
non-supported), or can be local.
the connection string might also have "alias" - an abstract name for a
database enlisted in aliases.conf, but i am not sure where
aliases.conf file should reside with fb-embedded.

fbembed engine, when given a local connection string, should try to
open the DB file on their own, then if failed - should find a
stand-alone FB engine and proxy the request to it.

in your case another application already made an embedded connection it seems

I heard that FB 2.5 embedded (based on "classic server") can, if
proper bells and whistles were used, make shared DB file access. Apart
from FB 2.0/2.1 embedded, bases upon "SuperServer"
http://www.firebird.1100200.n4.nabble.com/Shared-database-access-with-FB-2-5-embedded-with-Windows-service-app-td4304872.html
https://stackoverflow.com/questions/47748342/how-to-setup-embedded-firebird-and-access-from-java-application

I would not consider that reliable or safe, IMHO one better have a
specifically installed and running Firebird Server process, so if the
user application crashes it would not damage the file by writing
memory garbage or by failing to write memory cache, as it might do
with embedded servers.

However, FB-embedded does not use ayny user authentication and SQL
rights checking, standalone FB does.
So mere "external surgery" swapping "lean" fbclient instead of "fat"
fbembed dll might not work.

I would first consult with the first app maker if you can reconfigure
it properly to use a standalone FB server by design.

If that is not feasible, then reverse-engineering that black box
application and breaking out incompatibilities with s-a FB might be
required.

As a last resort you always can make a drop-in replacement for
fbclient/fbembed dll which would log all the application requests and
then proxy them to the renamed real DLL.
Then you would parse the log and extract the data you need.
People were doing that before TraceAPI was implemented in FB 2.5 to
have a tool to peep inside real SQL exchanges underneath layers of
application libraries.

> I have zero control over the writing process as it is a black box

Sounds like some story i somewhere (stackoverflow?) already heard few
months ago.

вт, 8 сент. 2020 г. в 14:41, Arun J <mail...@gmail.com>:

> To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/CA%2B7f3%3DfAzppK6%2BEbQudOx4BsGxAfQwKYio%2BpzauLEa%3D5nKAjUA%40mail.gmail.com.

[Arioch The]

> Found the process that is writing to the FDB file. Looks like it used DLL to do so directly. I am running a separate instance of Firebird (2.5) to access this file.

Maybe i would had tried to do the following. No warranties.

I assume that you checked the exact version of FB embed used by that
app and you installed accurately matching FB stand-alone version

1) temporary remove fb embedded DLL from the app, and implant "lean"
fbclient instead. Run something like http://FBProfiler.sf.net against
the stand-alone FB 2.5 server.
Try to launch the blackbox app, it will probably crash, but the tracer
would show the exact connection string and connection flags (AFAIR
those are DPB, database parameters block).
1.a) *maybe* the app will not crash and CAN work with a stand-alone
server, only the vendor knows for sure (and may be or not be inclined
to share with you)
1.b) *maybe* the app will use different connection settings and
string for different requests. I think that would have no any
influence on the embedded FB, but not sure. I do not like embedded
modes and know little about FBEmb file sharing.

2) restore the black box app.
Try to make your new app use EXACTLY THE SAME client DLL (jaybird has
some special :native: mode for that AFAIR, or use JNA/JNI) as the
black-box app uses, with exactly the same connection string and DPB
options (meaning your Java runtime should use exactly the same CPU
arch as the application). And run your new app using the same Windows
user (whether elevated or not, the same SID not merely the same
username) as the old app.
Whenever possible explicitly only user read-only transactions in your new app

[Adam McMahon]

Maybe I am missing something but if the DB is able to run in server mode, as many clients has you want can connect to the database, as only the server needs to have access to the file.

Have you tried that?

-Adam

[Arun J]

Adam,

As I have mentioned earlier there is another process which is using an embedded DB(DLL) that writes to the DB files. I uses another server to read from those files.

I am trying few things that Arioch had suggested below so far I have not reached there. Will let you know if I am successful or it is possible in the environment that we would be running.

JAK

--

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

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/41fa56fe-6f72-4f10-afbc-f843a316e28cn%40googlegroups.com.

[Vlad Khorsun]

On Wednesday, 9 September 2020 04:53:14 UTC+3, Arun J wrote:

As I have mentioned earlier there is another process which is using an embedded DB(DLL) that writes to the DB files. I uses another server to read from those files.

  If I understand you correctly - there is some app that used Firebird embedded v2.5 and you want to access

same database using Firebird Server. It is possible with no additional setup if you use Classic or SuperClassic

architecture, not SuperServer. FB embedded in v2.5 uses Classic architecture wich allows many [Super]Classic

processes to access same database simultaneously.

  Note, embedded v2.1 was build using SuperServer architecture and not allows to share database with another

process. I.e. make sure both you and "other" app uses Firebird v2.5.

e

Hope it helps,

Vlad

[Arun J]

Thanks Vlad.  Your understanding is correct and your solution should help.  

I will try your recommendation and let you know it works - I found this for using embedded in Java - surprised to see even the linux version for this embedded Superclassic one.

~JAK

--

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

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/8f780c92-ac3a-43b4-9089-f4628d40399eo%40googlegroups.com.