I have a sort of strange problem that I am encountering and I'm quickly
running out of hair to pull !
I have developed an application that essentially allows the user to select a
data source and enter various queries (Selects, Updates, Inserts etc). These
group queries are then executed depending upon commands that the application
receives from a TCP/IP socket. I am seeing some strange results which I
think are related to ODBC.
I have noticed that if a person enters multiple selects within a single
group after a while (Of many executions) I see locks appear on my database.
( As an example one user performed two selects. The first that joined table
1 and 2 followed by a second that joined 1 and 3)
Looking at the ODBC SDK. I learned that I needed to close the first select
cursor so that I could perform the second, since the first was holding a
lock on the first table. So in adding a Commit (To release the locks, I
guess I could have used an explicit close cursor)the nested grouped selects
works fine. So I thought that I was safe. Until another customer came to me
saying that I had broken their grouping.
What they had created in the application is a select that results in
multiple rows. They were then looping round on each record and then
performing another a select for each row found on a different table based on
a field from the row currently returned back from the first select.
Now this presents the following issue. If I commit after the first select I
blow away my record set that I have created. If I commit after the second
select again I will blow away my first record set since it is on the same
connection. I am also not using any transactions since I am only using
So the question is how can I over come the need to perform a commit (To
avoid the locking problem) while ensuring that the functionality is still
available for the user to loop round each record and still perform the
second commit. What are other people doing after a select ?
Any suggestions (or links or good books) on this would be very welcome. This
seems to be a very real world problem but is it a case that the user (or me)
needs to be eductated on what can be undertaken via ODBC ?
Just as some other information I am not using auto commit.
Wayne (Going bald quickly !)