Solved

Query Analyzer -> VB

Posted on 1998-11-22
14
606 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now