Solved

Query Analyzer -> VB

Posted on 1998-11-22
14
620 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

752 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