Re: commit from powerbuilder to infromix 7.2

Oscar Goldes wrote: > > Hello, > reading powerbuilder 4 help, I find that autocommit is not supported for = > Informix databases, i.e., it is always set to false. Please be aware that PowerBuilder can only pass the "commit" command to Informix. PowerBuilder itself does not have the ability to commit SQL statements. In other words, Informix overules PowerBuilder in the case of commit/rollback. Even with autocommit = false in PowerBuilder, read statements in Informix (i.e., SELECT) will be autocommited by Informix in many cases, as long as you are not using ANSI standard database. I don't know all the possible cases here. Any taker? > If you use exclusively stored procedures in an application, i.e, no = > select in clients, which is the " right" way to deal with transactions = > and stored procedures then, from powerbuilder: > > a) control transactions entirely in the pb-client, no begin work, commit = > or rollback issued inside stored procedures? > > b) control entirely in procedures, no commit or rollback in = > powerbuilder? Rollback is simple. As long as something is wrong, issue a rollback. Commit is different. I have seen many "incorrect" commits in both powerbuilder and informix programs. The problem is due to the the neglected definition of transactions: "AN ATOMIC EXPRESSION." As long as it is the end of an atomic expression, you should issue a commit regardless it is in Powerbuilder, Informix, or any other programming languages. An atomic expression is the smallest group of program statements (SQL statements, in this case) that must be executed either all or none. Partial execution of an atomic expression will result into violdation of data integrity. I do find a practical way to measure excessive commits: If a user is going to abort the execution of a program (or reboot) in the middle of execution and then restart the program, will the program be able to handle? If not, the program has excessive commits. In case of too few commits, I have not find a practical way to identify them. Unfortunately, most of the errors I found in Powerbuilder/Informix programs are in this category. For example, a single commit at the end of a program usually indicates too few commits. > what happens then with the initially open transaction = > (connect)? I may be wrong, but I believe the connect does not start a transaction unless it is an ANSI compliant database. Even if it is an ANSI compliant database, there is not content in the connect transaction. Powerbuilder support or manuals may claim the transaction starts with the connect statement, but Informix overrules Powerbuilder in this case. > > c) both can be used? > > Another question: what happens to the open transaction if while = > executing a stroed procedure the client crashes? is it automatically = > rolled back or (I hope not!) commited? If there is no commit and the program is terminated, everything up to the previous commit will be rolled back. Chao Y. Din