Solved

Query Analyzer -> VB

Posted on 1998-11-22
14
617 Views
Last Modified: 2013-12-25
I was able to Declare a cursor and then do a fetch with a certain Index using SQL 7.0 Query Analyzer. The reason for  doing this is that I want to step through a huge table one row at a time.

Is there a way of doing this from VB?  Using ADO and record sets have been a nightmare.  The select is too complicated and SQL ends up doing a full Table scan.


I am using VB6 and SQL 7.0.
0
Comment
Question by:sbg
14 Comments
 
LVL 1

Expert Comment

by:ElvasLion
ID: 1497773
I could'nt understand your question.
If you don't mind, i woul'd like you to rephrase it.
0
 

Author Comment

by:sbg
ID: 1497774
Basically, I have to treat SQL table is an ISAM database.  I know that this is not the best thing to do but I have no choice.

I have a contact table of names that is huge.  I have an index on Lastname, firstname, middlename.  I have to be able to browse the table alphabetically.  I can't read the whole thing into a recordset, too large.  I want to display 10 names at a time.

So I went into MS SQL Query Analyzer and was able to do this by declaring a cursor, forcing the index and doing fetches.

I want to do this from VB.  I don't think I can do this with ADO so my only option may be ODBC.  I don't know much about it.

If you can help that would be great.
0
 
LVL 1

Expert Comment

by:wizard2072098
ID: 1497775
You are in for a nightmare. ADO can't do it as of yet -- at least not that I've found. You'll have to do extended fetch calls through ODBC which aren't simple.

However, if you use ADO with Server-Side cursors selected, you may be able to get something similar without hammering your client with a huge table. Just set the cursor location to adUseServer in the connection object. That should force the server to bear the brunt of the processing rather than the client.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:sbg
ID: 1497776
Is it really a nightmare using ODBC?  

The routine I have to use in ADO is not that simple either. My worst case scenario is when some browses 'John Smith'.  There can be thousands and thousands or worse if they just type in 'Sm'. I was hoping there would be a better way then working with recordsets.

I am still open to suggestions.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497777
Use RDO. Using an rdoQuery, you can specify the read-ahead buffer size (look at the KeysetSize and RowsetSize properties). Setting this to 1 should allow you to fetch each individual record, but I would recommend a value more like 100. This is all just a wrapper around ODBC, but RDO is easier to deal with than SQLExtendedFetch.
0
 
LVL 2

Accepted Solution

by:
majed100598 earned 400 total points
ID: 1497778
Try to use dbgrid useing the data control you have
0
 

Author Comment

by:sbg
ID: 1497779
I don't think dbgrid will help in this situation. If I have do a query and I end up with 50,000 John Smiths. How would dbgrid help?
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497780
You have to love these tables with millions of rows. We get the same kind of thing with our EDI system. I still think RDO or the ODBC API calls will be your ticket.
0
 

Author Comment

by:sbg
ID: 1497781
Can RDO and ADO be used in the same program? I use ado to get detailed info once a person is chosen from the initial list.

If not, what about ODBC and ADO? Would this cause any problems.

0
 

Author Comment

by:sbg
ID: 1497782
Can anyone provide sample ODBC code or good books on the subject?
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497783
RDO and ADO can both be used, no problem. One advantage of RDO is that it is a thin wrapper around ODBC. You can use RDO to create your environment and database context and save yourself a bunch of ODBC calls. RDO exposes the database context and statement handles so you can do your own SQLExtendedFetch calls without worrying about just connecting to the data source. I still think you could set things up with an rdoQuery/rdoResultset.

I am not an ODBC wizard, but the best reference I have found so far is MSDN. I could not find a decent book on the subject and would love to hear suggestions.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497784
As for a sample, hold onto your hat. Here is a module to open rdoConnections and rdoRecorsets. This is some test code I used to test the capabilities of an ODBC driver.

Option Explicit
Option Compare Text

Public rdoConn As RDO.rdoConnection
Public rdoRS As RDO.rdoResultset
Public FileNum As Integer

Public rdoenv As RDO.rdoEnvironment
Private Const m_DSN = "DSN=OR0T;"
'
Function OpenConn(ConnectString As String) As RDO.rdoConnection
    Dim tmpConn As RDO.rdoConnection
    Dim sDSN As String
    Dim sConnect As String

    sConnect = ConnectString
   
    Set tmpConn = New RDO.rdoConnection
    With tmpConn
        .Connect = sConnect
        '.CursorDriver = rdUseServer
        .CursorDriver = rdUseOdbc
        '.CursorDriver = rdUseIfNeeded
        '.CursorDriver = rdUseClientBatch
        '.CursorDriver = rdUseNone
    End With

    tmpConn.EstablishConnection rdDriverComplete, False
    Set OpenConn = tmpConn
    Set tmpConn = Nothing
End Function

' Open a resultset
Function OpenRS(Conn As RDO.rdoConnection, strSQL As String) As RDO.rdoResultset
    If Conn Is Nothing Then
        Set Conn = OpenConn(m_DSN)
    End If

    If (InStr(strSQL, vbCrLf)) Then
        strSQL = Mid$(strSQL, 1, InStr(strSQL, vbCrLf) - 1)
    End If
    If InStr(strSQL, ";") Then
        strSQL = Mid$(strSQL, 1, InStr(strSQL, ";") - 1)
    End If
   
'rdOpenForwardOnly, rdOpenKeyset, rdOpenDynamic, rdOpenStatic
'rdConcurReadOnly, rdConcurLock, rdConcurRowVer, rdConcurValues, rdConcurBatch
'                   FO KS DY ST
'------------------|--|--|--|--
'rdConcurReadOnly  |- |- |- |-
'rdConcurLock      |- |- |- |-
'rdConcurRowVer    |  |  |  |
'rdConcurValues    |- |- |- |-
'rdConcurBatch     |- |- |- |-

    'Set OpenRS = Conn.OpenResultset(strSQL, rdOpenDynamic, rdConcurValues)
    'Set OpenRS = Conn.OpenResultset(strSQL, rdOpenKeyset, rdConcurReadOnly)
    Set OpenRS = Conn.OpenResultset(strSQL, rdOpenStatic, rdConcurReadOnly)
    'Set OpenRS = Conn.OpenResultset(strSQL, rdOpenStatic, rdConcurValues)
    'Set OpenRS = Conn.OpenResultset(strSQL)
End Function

' Execute a statement
Function ExecSQL(Conn As RDO.rdoConnection, strSQL As String) As RDO.rdoResultset
    If Conn Is Nothing Then
        Set Conn = OpenConn(xlinkClink)
    End If
    If (InStr(strSQL, vbCrLf)) Then
        strSQL = Mid$(strSQL, 1, InStr(strSQL, vbCrLf) - 1)
    End If
    If InStr(strSQL, ";") Then
        strSQL = Mid$(strSQL, 1, InStr(strSQL, ";") - 1)
    End If
   
    Conn.Execute strSQL    
End Function

0
 

Author Comment

by:sbg
ID: 1497785
Looks a little different then ado but I will give it a try.
Thanks.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497786
If you want to try ODBC API calls, check out SQLExtendedFetch, SQLGetRowCount, and SQLExtendedFetch.

The following may provide some help:
http://premium.microsoft.com/msdn/library/sdkdoc/sql/odbc/odbc06_5l6b.htm

MSDN has the complete list of calls if you decide to try that route.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question