Commit in a stored procedure

Hi,

I'm new to firebird so this may be a pretty stupid question. Anyhow, I've got a simple stored procedure doing nothing more than a simple insert. (I call the procedure from php.)

I want to be sure that the insert is really committed, so I thought to just put a commit statement in my stored procedure. This doesn't seem to be possible with firebird. Or am I using the wrong syntax for the commit??

This is my procedure:
create procedure test
as
begin
insert into test_table(id) values (1);
commit;
end

Why does the procedure not compile with the commit line? And how can I make sure that the insert is really inserted in the database (and not only posted)?

Transactions cannot be started/committed/rolled back from inside stored procedure or trigger code.

The client (process) that calls the stored procedure should start and commit/rollback the transaction.



--
With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server.
Upscene Productions
http://www.upscene.com

Thanks for the reply, Martijn!

I have one more related question. When my client (which is a php-application) calls the simple test-procedure (with something like ibase_query("execute procedure test")) and I don't do a commit after the procedure-call, I can still see that the database is updated!
So does this imply that there's an automatic commit after the procedure closes? Wouldn't this mean that I don't need the client to explicitly commit the transaction?

Regards, Peter

Hi,

That implies that your connecting is doing some kind of auto-commit. This has nothing to do with the procedure itself, but more with the defaults for a PHP connection object, I think, and the driver that is used to connect to Firebird. My bet is that you can turn this behaviour OFF as well.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server.
Upscene Productions
http://www.upscene.com

That makes sense!!

I checked the documentation, but can't really find it. It could be that it's defined in the constant ibase_committed, which is set in a dll in my php-environment (php/extensions/php_interbase.dll).

I still can't figure out, how I can set this constant. I think it's better to put this 'auto commit' off and always use ibase_commit() instead.

Regards, Peter

Hi Peter,

I have zero PHP knowledge, so I guess you need to ask someone else on that subject


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server.
Upscene Productions
http://www.upscene.com

Hi,

Bit of a late reply sorry but I've just been faced with this issue too. From what I have gathered, PHP automatically creates a default transaction upon connecting to the db. This transaction is automatically commited when the script has finished executing.

If you want more control, you can create a new transaction (ibase_trans) and pass along the returned transaction id to any further queries etc. If you don't commit this transaction then your changes will be lost at the end of your script.

It's also not a bad idea to look at creating your own transactions anyway since you can pass along flags to tailor the transaction to your preferences. For example, the default transaction created for you is read/write. However, there is a small performance gain to be had from creating the transaction as read-only (if, of course, you only wish to perform SELECTs).

Russ

Russ,

Thanks for the reply! I'm not that familiar yet with the way php handles transactions, but I'll definitely have a closer look at it (might be useful for me to set a read-only flag now and then).

Regards, Peter