Begin and commit not working with postgresql

[danielf]

Hi,
iam using ZSQLMonitor, when i execute the methods, StartTransaction, commit or rollback none of this commands are being sent to the database

begin;
commit;
rollback;

Anyone can help?

Thx in advanced

[Wild_Pointer]

Hello, danielf

would you provide more information on TZConnection component you are using:
- what is the value for AutoCommit
- what is TransactIsolationLevel
- what DB system are you connected to?

[danielf]

Sorry for the missing information,

AutoCommit = true
TransactIsolationLevel = ReadCommit
database is PostgresSQL

Thx Wild_Pointer for ur reply

[Wild_Pointer]

Hello,

To test I used simple code:

ZConnection1.Database := 'sport3_test';
ZConnection1.Connect;

ZConnection1.StartTransaction;
qZ.Open;
ZConnection1.Commit;

In Monitor file I got:
2009-12-18 15:21:15 cat: Connect, proto: postgresql-8, msg: CONNECT TO "sport3_test" AS USER "postgres"
2009-12-18 15:21:15 cat: Execute, proto: postgresql-8, msg: BEGIN
2009-12-18 15:21:15 cat: Execute, proto: postgresql-8, msg: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2009-12-18 15:21:17 cat: Execute, proto: postgresql-8, msg: select * from apribojimai

2009-12-18 15:21:17 cat: Execute, proto: postgresql-8, msg: SELECT version()
2009-12-18 15:21:17 cat: Execute, proto: postgresql-8, msg: SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_get_expr(def.adbin, def.adrelid) as adsrc,dsc.description FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname LIKE E'apribojimai' AND a.attname LIKE E'%' ORDER BY nspname,relname,attnum
2009-12-18 15:21:17 cat: Execute, proto: postgresql-8, msg: SELECT oid, typname, typbasetype FROM pg_type WHERE oid<10000 OR typbasetype<>0 ORDER BY oid
2009-12-18 15:21:17 cat: Execute, proto: postgresql-8, msg: COMMIT
2009-12-18 15:21:17 cat: Execute, proto: postgresql-8, msg: BEGIN
2009-12-18 15:21:17 cat: Execute, proto: postgresql-8, msg: SET TRANSACTION ISOLATION LEVEL READ COMMITTED

So as you see - Begin and Commit commands are used. What version of zeos lib do you use?

I would advice not to use autoCommit, because it should be understood as autoTransBegin. It starts the transaction automatically and there is no use of ZConnection1.StartTransaction - id actually does nothing, just fire event OnStartTransaction. This is the wors part of using zeos (IMHO). You should take dealing with transactions into your own hands.

[danielf]

Unfortunately i have uninstall 6.6.5 components and installed the old ones, i know i had tried both ways autocommit true and false, because i dont want zeos to automatically start any transaction.

In automatic mode, (i really cant remember if it was this property but i believe so) it worked until some point i had a Memo printing down the logs and when i used a zeos component to insert or update records by the use of insert() and edit() it started and committed the transactions without a problem.
when i tried to execute my own SQL code no COMMIT command appeared.

Anyways my described is when using the manual way to begin transactions.

I do recall i had some errors when installing the zeos 6.6.5 components on my c++builder6 but then again i get erros installing my current zeos components 6.5.1 so i didnt pay much attention to it.

If there is someone using zeos 6.6.5 components on c++builder with postgresql that works with none of this problems that i described, i can only assume that iam doing something wrong with the installation, so plz give the head's up.

Thx

[Wild_Pointer]

Hello, danielf.

As you describe it, the thing you should double check is TransactIsolationLevel. Maybe your program changes it dynamically?.. If TransactIsolationLevel = tiNone the Begin and Commit commands are not used.
If thats not the case - than this may be connected to CBuilder. I have almost no experience with it, so maybe other members of Zeos community will help you.

Good luck.