[SOLVED] ODBC and committing a transaction? Help!

[leesiulung]

I'm on a Windows hosting environment with GoDaddy. I normally prefer MySQL, but am stuck with MS Access.

 

Anyhow, how do you one start and committ a transaction? I use odbc_connect and odbc_exec to run my queries, but do not understand the documentation for odbc_commit.

 

Anyone have any clues? Kind of lost here....

[btherl]

Does it work without using odbc_commit()?  If yes, then you don't need to worry about it

[leesiulung]

It will work most of the time, but it could potentially mix up peoples' account. Thus, it is a good idea to fix it.

 

Basically, I need to insert a new account and keep track of the primary key which is numeric.

 

Since, I do not want autoincrement on in Access I do the following:

 

1. start commit

 

2. find new unused primark key (userid) by number of records + 1

 

3. insert new record with userid

 

4. commit

 

5. insert additional records into other tables with using primary key as foreign key

 

Any other suggestions?

[btherl]

The key operation there is #3.  If #3 succeeds, then you've got your new key and no-one else can have it.  So you may be able to get away without transactions.

 

From what I understand of ODBC (ref: http://msdn2.microsoft.com/en-us/library/aa198023(SQL.80).aspx ), you must switch autocommit off to start a transaction.  Then, when your work is finished, you call the odbc_commit() function.  So.

 

odbc_autocommit($conn, false);
# do your stuff
odbc_commit($conn);

 

But this is all pure speculation, I am by no means an odbc expert.  Test it first!

[leesiulung]

What is the best way to test this?

 

Test it with a sequences of inserts, but have one of them fail and check the tables?

[btherl]

I would test it with:

 

odbc_autocommit($conn, false);

# do an insert

odbc_rollback($conn);

 

If the insert is visible, then something is wrong.  Then try the same with odbc_commit().  The insert should be visible, but only after the commit.  You might want to add a sleep(10) before the commit (and check the db in another window), so you can verify that the insert only becomes visible after committed.

[leesiulung]

The key operation there is #3.  If #3 succeeds, then you've got your new key and no-one else can have it.  So you may be able to get away without transactions.

 

From what I understand of ODBC (ref: http://msdn2.microsoft.com/en-us/library/aa198023(SQL.80).aspx ), you must switch autocommit off to start a transaction.  Then, when your work is finished, you call the odbc_commit() function.  So.

 

odbc_autocommit($conn, false);
# do your stuff
odbc_commit($conn);

 

But this is all pure speculation, I am by no means an odbc expert.  Test it first!

 

This worked. Thanks! For those wondering this was with GoDaddy's Windows Deluxe hosting. I highy recommend AVOIDING GoDaddy if you need PHP on Windows hosting. They do not officially support PHP on Windows.

 

I would test it with:

 

odbc_autocommit($conn, false);

# do an insert

odbc_rollback($conn);

 

If the insert is visible, then something is wrong.  Then try the same with odbc_commit().  The insert should be visible, but only after the commit.  You might want to add a sleep(10) before the commit (and check the db in another window), so you can verify that the insert only becomes visible after committed.

 

While testing the code I found a bug and indeed the transaction was rolled back. Thanks!