Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

Query Analyzer -> VB

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
sbg
Asked:
sbg
1 Solution
 
ElvasLionCommented:
I could'nt understand your question.
If you don't mind, i woul'd like you to rephrase it.
0
 
sbgAuthor Commented:
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
 
wizard2072098Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sbgAuthor Commented:
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
 
tomookCommented:
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
 
majed100598Commented:
Try to use dbgrid useing the data control you have
0
 
sbgAuthor Commented:
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
 
tomookCommented:
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
 
sbgAuthor Commented:
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
 
sbgAuthor Commented:
Can anyone provide sample ODBC code or good books on the subject?
0
 
tomookCommented:
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
 
tomookCommented:
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
 
sbgAuthor Commented:
Looks a little different then ado but I will give it a try.
Thanks.
0
 
tomookCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now