How in firebird to pick up changes made in background thread?

[Delphic]

Hi

I have a job that runs a background thread adding new tables. When it is finished I have to close the app and reopen it to see the new tables.

What is the right way to pick them up immediately?

TIA
Mark

[Delphic]

Here is some extra info on this:

If I use the TZConnection objects used in the main thread, from the main datamodule, it seems to work. I only get this issue: either the tables I am creating not being seen, or when trying on the main thread to diconnect then reconnect I get:
I/O error during "open of file ... Error code -902

I thought we were supposed to use TZConnection objects in only one thread?

I want to do things correctly. At present, I am not using threads at all.

Does anyone have extra knowledge on this?

TIA
Mark

[Delphic]

More explanation:

It could be that those problems are arising because I have a little utility I wrote connected to the same database open while running Delphi and the main application.

---
Mark

[Delphic]

More explanation:

It could be that those problems are arising because I have a little utility I wrote connected to the same database open while running Delphi and the main application. That happens in some cases. But I am also getting error -902 when trying to disconnect the database in the main thread after the background thread has run.

---
Mark

[marsupilami]

Hello Mark,

I have some basic questions about this. Do you keep to the rule that one connection should be used in one thread only? What settings do you use for transaction isolation? Do you commit the transaction of the main connection after your thread finishes its work?

One thing that comes to mind in that use case: I am not sure wether Zeos does a "commit" or a "commit retain". Possibly the second one also can lead to situations where new tables are not seen by the transaction.

You might want to close the connection of the main thread and reopen it again.

With best regards,

Jan

[Delphic]

Thank for the answer, Jan

I have been trying it for a while. Even disconnecting didn't help. I have modified it so that creating the data now happens in the main thread, and I am still getting errors.

And now when I got to read tables after getting back in, I am getting:
SQL Error: no permission for read/select access to table user. Error Code -551

We have a table called "user", with the double quotes needed by firebird.

This was not happening before.

Regards
Mark

[marsupilami]

Hello Mark,

I have been trying it for a while. Even disconnecting didn't help. I have modified it so that creating the data now happens in the main thread, and I am still getting errors.

Which errors do you get? Do you have an example program on what you try to do?

And now when I got to read tables after getting back in, I am getting:
SQL Error: no permission for read/select access to table user. Error Code -551

That seems to be a permission problem. To me that ususally happens when I create a table as user SYSDBA and then other users try to access it. It should happen when the user who creates the tables doesn't give permissions to read and update it to other users.

We have a table called "user", with the double quotes needed by firebird.

According to http://www.firebirdsql.org/en/iso-9075- ... ved-words/ user is a reserved word in the sql standard. Because of this you can only use it as a table name in firebird if you quote it. Most other RDBMS should behave the same, I think.

With best regards,

Jan

[Delphic]

Hello Mark,

I have been trying it for a while. Even disconnecting didn't help. I have modified it so that creating the data now happens in the main thread, and I am still getting errors.

Which errors do you get? Do you have an example program on what you try to do?

One of the confusing things is that I have written a small utility to run queries against or databases (Firebird embedded) and these problems are happening in that utility.

And now when I got to read tables after getting back in, I am getting:
SQL Error: no permission for read/select access to table user. Error Code -551

That seems to be a permission problem. To me that ususally happens when I create a table as user SYSDBA and then other users try to access it. It should happen when the user who creates the tables doesn't give permissions to read and update it to other users.

I came back into work this morning, and the problem was not happening. All reads and writes are happening through the same TZConnection objects. There are no users, just the default SYSDBA.

We have a table called "user", with the double quotes needed by firebird.

According to http://www.firebirdsql.org/en/iso-9075- ... ved-words/ user is a reserved word in the sql standard. Because of this you can only use it as a table name in firebird if you quote it. Most other RDBMS should behave the same, I think.

I think we are doing the right thing there. My test queries run with "user" and "date".

But the current situation does not inspire confidence. I lean toward blaming the combination: Zeos + firebird embedded. That may be a rare pairing. Does anyone know about this as an issue? If so, does 7.2 have a chance of fixing it?

Regards
Mark

[marsupilami]

Hello Mark,

One of the confusing things is that I have written a small utility to run queries against or databases (Firebird embedded) and these problems are happening in that utility.

Again - which problems happen. Can you provide the source code or can you create a small test database and test program that demonstrate your problems? Having source cde will help a great deal.

I think we are doing the right thing there. My test queries run with "user" and "date"

Doing so is perfectly correct. But you will always have to rmember that you have to use the quotes and the correct character case.

But the current situation does not inspire confidence. I lean toward blaming the combination: Zeos + firebird embedded. That may be a rare pairing. Does anyone know about this as an issue? If so, does 7.2 have a chance of fixing it?

I use Firebird Embedded a lot in commercial applications. So far it doesn't behave any different than using Firebird as a server. Also Zeos behaves as I expect it to do. Honestly the problem most probably is in your source code. If you provide a test case, we can work on this.

regards,

Jan