Devart Forums

The following code fails on PostgreSQL, if "myTable1" does not exist, but "myTable2" exists.

The error is "current transaction is aborted, commands ignored until end of transaction block".

var
ds : TUniQuery;

begin
try
ds.Sql.Text := 'select * from myTable1';
ds.Open;
except
on E: Exception do begin
ds.Sql.Text := 'select * from myTable2';
ds.Open;
end;
end;
end;

Do I have to fix / workaround this myself or is this something, that can be fixed in UniDac?

cu Christian

Trying your code here it works as expected i.e. It opens table2 without displaying any exception regarding the missing table1.

D2010, Pg 9, UniDAC 3.0.0.10

Regards, Paul.

[quote="DepSoft"]Trying your code here it works as expected i.e. It opens table2 without displaying any exception regarding the missing table1.

But try with:

ds.UniDirectional := True;

then it will fail.

D2010, Pg 8.4, UniDAC 3.0.0.8

cu Christian

You're right, you should have said

Would it not be better to properly check if the table exists first if it may not always be present?

With your exception handler, there may well be other exceptions that could also be raised that might also stop you opening table2.

I use a TableExists function that queries PG_TABLES or you could always do something with the metadata component.

e.g. Regards, Paul.

DepSoft wrote:Would it not be better to properly check if the table exists first if it may not always be present?

Well yes and know. I have my own db wrapper, UniDac is just one component set and I use exceptions in cases like this in different places.

So I prefer a solution where I don't have to go through all my code.

cu Christian

Hello

Thank you for the information. We have reproduced this problem and fixed it. This fix will be included in the next PgDAC and UniDAC builds.

bork wrote:This fix will be included in the next PgDAC and UniDAC builds.

When can we expect a new build for UniDAC?

cu Christian

Hello

We are planning to release the next version of UniDAC this week.

It's funny to find my own post from 9 years ago when I search for a solution for a problem :-).

I have a similar case:
- Postgresql 11
- using a TUniQuery
Again, the second call of "Open" fails with error "current transaction is aborted, commands ignored until end of transaction block".

Could you please look into this?

Regards
Christian

We've tested UniDAC according to your description of the issue, no issues have been found. Please create and send us a sample project with source code where the issue can be reproduced. Also we need the DDL script that creates the tables that you use in the sample project. For your convenience, please use the e-support form https://www.devart.com/company/contactform.html

We've received your sample code and investigated it. When accessing the non-existing object "gen_not_there", you get the error: relation "gen_not_there" does not exist, which you then handle in your application code. However, handling this error doesn't solve the root cause -- the error returned by the PostgreSQL server remains unhandled. Lack of handling for this error on the server side leads to the error: "current transaction is aborted, commands ignored until end of transaction"

But how should I handle this? In case I opened the transaction explicitely: Did the server rollback all? Should I call conn.Commit or conn.Rollback?

Christian

You need to implement the handling of the PostgreSQL server error in the except.. end block. Operator invocations for the current transaction will be completely defined by the business logic of your application in this case.