Multiple Selects

Multiple Selects

Post by Wayn » Fri, 26 Jan 2001 03:45:12


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 !)


1. Need help with multiple SELECT using multiple COUNTs

I am creating a page which gives a report of user activity for a call
center. I want to return the number of "comments" that user opened &
closed during a time period and the total amount they currently have
opened. So far I have created a SQL statement that returns the total
open during a time period, but I have not been able to include the
other 2 columns in the result. I want a user to be returned if s/he
opened or closed any comments during the time period, or currently has
any open. I'm not sure how to do all of this. Thanks!

sql =   "SELECT "&_
                  "  tblUsers.strFamily, "&_
                  "  tblUsers.strGiven, "&_
                  "  COUNT (tblComments.intCommentID) as totalOpened "&_
                  "FROM "&_
                  "  tblComments INNER JOIN "&_
                  "  ( "&_
                  "  tblEvents INNER JOIN "&_
                  "    tblUsers ON "&_
                  "    tblUsers.intUserID = "&_
                  "    tblEvents.intCreatorID "&_
                  "  ) "&_
                  "ON "&_
                  "  tblComments.intCommentID = "&_
                  "  tblEvents.intCommentID "&_
                  "WHERE "&_
                  "  tblEvents.intEventTypeID = 1 "&_
                  "  AND tblEvents.dtmDate BETWEEN "&_
                  "                '" & startDate & "' AND "&_
                  "                '" & endDate & "' "&_
                  "GROUP BY "&_
                  "  tblUsers.strFamily, "&_
                  "  tblUsers.strGiven "&_
                  "ORDER BY "&_
                  "  tblUsers.strGiven "

2. Clipper vs. Paradox Engine?

3. Multiple SPs or single SP with multiple selects?

4. Password

5. Selecting XML into an ADO Stream using multiple select statements


7. single select w/join versus multiple selects

8. Converting FPW 2.6 APP into VFP3 format

9. Multiple Select Statements

10. Multiple selects on same table

11. SOLUTION: Multiple Select Statements in Stored Proc

12. Multiple Select Statement in Stored Proc

13. Using multiple select statements in a SQL stored procedure