problem with recordset from Sybase and Oracle


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: problem with recordset from Sybase and Oracle

Hybrid View

  1. #1
    substring Guest

    problem with recordset from Sybase and Oracle

    Hello Everybody,

    This is a 2 parts question. I have not worked with Sybase and Oracle thru
    VB
    before, and I am having problem getting a returned recordset from these two
    databases (by the way, the similar code works on SQL Server).

    Sybase Problem:
    '//code starts
    acnSybase.Open "Provider=MSDASQL.1;Password=(password);Persist Security
    Info=True;" & _
    "User ID=(userid);Data Source=(DSN)"
    acnSybase.CommandTimeout = 3360

    Set arsData = New ADODB.RecordSet
    strSQL = "SELECT ......"
    arsData.Open strSQL, acnSybase, adOpenDynamic, adLockOptimistic
    '//code ends
    I am getting error message of "ODBC driver does not support the requested
    properties" at the arsData.Open line. The connection was tested okay.
    There must be something on my arsData.Open that Sybase does not like, and I
    don't know what it is.


    Oracle Problem:
    '//code starts (in different module)
    acnSybase.Open "Provider=MSDASQL.1;Password=(password);Persist Security
    Info=True;" & _
    "User ID=(userid);Data Source=(DSN)"

    acnOracle.Open "Provider=MSDAORA.1;Password=(password);User
    ID=(userid);Data Source=(server name);" & _
    "Persist Security Info=True"
    acnOracle.CommandTimeout = 3360

    Set arsData = New ADODB.RecordSet
    strSQL = "SELECT ........ "
    arsData.Open strSQL, acnOracle, adOpenDynamic, adLockOptimistic

    Set acnMyDB = New ADODB.Connection
    acnMyDB.Open "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;"
    & _
    "User ID=sa;Initial Catalog=ATS;Data Source=(local SQL
    Server name)"
    acnMyDB.CommandTimeout = 3360

    If arsData.EOF = True Then 'the data cannot be found in this database
    acnMyDB.Execute "INSERT INTO ......" 'set a dummy record
    Exit Sub
    End If
    '//code ends
    Again, there is no problem with the connection to Oracle and no problem with
    the arsData.Open. But my application hangs at the "If arsData.EOF"
    statement. Does anyone know why?

    Any help will be very much appreciated.






  2. #2
    Ralph Guest

    Re: problem with recordset from Sybase and Oracle



    <Warning! What follows is a best guess.>

    First I would investigate gathering up all the possible errors.
    Check out the following KnowledgeBase articles:

    INFO: Extracting Error Information from ADO in VB ID: Q167957
    INFO: Underlying OLE and OLEDB Provider Errors Exposed via ADO ID: Q168354


    Second, I would use the OLE DB provider for each of the databases. (MSDAORA
    for Oracle, for example.)

    Third, I haven't used Oracle in awhile myself, but it didn't use to provide
    a scrollable server cursor. In addition, there was no way to update a row
    without using a SQL UPDATE statement. Therefore, you could only use forward-only,
    read-only rowsets. But there was talk about that changing, make sure you
    are using the latest MSDAC (2.6/2.7 ?) They might have added it by now.

    So try something like...
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    cn.Open "Provider=MSDAORA;Data Source=Mydb;User ID=substring; Password=junk"
    rs.CursorLocation = adUseServer
    rs.Open "SELECT * ...", cn, adOpenForwardOnly, adLockReadOnly

    Hope this helps.

    "substring" <substring0@hotmail.com> wrote:
    >Hello Everybody,
    >
    >This is a 2 parts question. I have not worked with Sybase and Oracle thru
    >VB
    >before, and I am having problem getting a returned recordset from these

    two
    >databases (by the way, the similar code works on SQL Server).
    >
    >Sybase Problem:
    >'//code starts
    > acnSybase.Open "Provider=MSDASQL.1;Password=(password);Persist Security
    >Info=True;" & _
    > "User ID=(userid);Data Source=(DSN)"
    > acnSybase.CommandTimeout = 3360
    >
    > Set arsData = New ADODB.RecordSet
    > strSQL = "SELECT ......"
    > arsData.Open strSQL, acnSybase, adOpenDynamic, adLockOptimistic
    >'//code ends
    >I am getting error message of "ODBC driver does not support the requested
    >properties" at the arsData.Open line. The connection was tested okay.
    >There must be something on my arsData.Open that Sybase does not like, and

    I
    >don't know what it is.
    >
    >
    >Oracle Problem:
    >'//code starts (in different module)
    > acnSybase.Open "Provider=MSDASQL.1;Password=(password);Persist Security
    >Info=True;" & _
    > "User ID=(userid);Data Source=(DSN)"
    >
    > acnOracle.Open "Provider=MSDAORA.1;Password=(password);User
    >ID=(userid);Data Source=(server name);" & _
    > "Persist Security Info=True"
    > acnOracle.CommandTimeout = 3360
    >
    > Set arsData = New ADODB.RecordSet
    > strSQL = "SELECT ........ "
    > arsData.Open strSQL, acnOracle, adOpenDynamic, adLockOptimistic
    >
    > Set acnMyDB = New ADODB.Connection
    > acnMyDB.Open "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;"
    >& _
    > "User ID=sa;Initial Catalog=ATS;Data Source=(local SQL
    >Server name)"
    > acnMyDB.CommandTimeout = 3360
    >
    > If arsData.EOF = True Then 'the data cannot be found in this database
    > acnMyDB.Execute "INSERT INTO ......" 'set a dummy record
    > Exit Sub
    > End If
    >'//code ends
    >Again, there is no problem with the connection to Oracle and no problem

    with
    >the arsData.Open. But my application hangs at the "If arsData.EOF"
    >statement. Does anyone know why?
    >
    >Any help will be very much appreciated.
    >
    >
    >
    >
    >



  3. #3
    substring Guest

    Re: problem with recordset from Sybase and Oracle

    Ralph,

    Thanks! Your best guess is good enough for me. I didn't expect my question
    to be THAT tough, ha. I posted this question on multiple VB newsgroups/VB
    discussion groups and you are the ONLY one that replies. You are the champ!

    I will follow your suggestion and hope that works. Will see. Thank you so
    much!



    "Ralph" <nt_consulting32@SPAMhotmail.com> wrote in message
    news:3b5db1b2$1@news.devx.com...
    >
    >
    > <Warning! What follows is a best guess.>
    >
    > First I would investigate gathering up all the possible errors.
    > Check out the following KnowledgeBase articles:
    >
    > INFO: Extracting Error Information from ADO in VB ID: Q167957
    > INFO: Underlying OLE and OLEDB Provider Errors Exposed via ADO ID: Q168354
    >
    >
    > Second, I would use the OLE DB provider for each of the databases.

    (MSDAORA
    > for Oracle, for example.)
    >
    > Third, I haven't used Oracle in awhile myself, but it didn't use to

    provide
    > a scrollable server cursor. In addition, there was no way to update a row
    > without using a SQL UPDATE statement. Therefore, you could only use

    forward-only,
    > read-only rowsets. But there was talk about that changing, make sure you
    > are using the latest MSDAC (2.6/2.7 ?) They might have added it by now.
    >
    > So try something like...
    > Dim cn As New ADODB.Connection
    > Dim rs As New ADODB.Recordset
    >
    > cn.Open "Provider=MSDAORA;Data Source=Mydb;User ID=substring;

    Password=junk"
    > rs.CursorLocation = adUseServer
    > rs.Open "SELECT * ...", cn, adOpenForwardOnly, adLockReadOnly
    >
    > Hope this helps.
    >
    > "substring" <substring0@hotmail.com> wrote:
    > >Hello Everybody,
    > >
    > >This is a 2 parts question. I have not worked with Sybase and Oracle

    thru
    > >VB
    > >before, and I am having problem getting a returned recordset from these

    > two
    > >databases (by the way, the similar code works on SQL Server).
    > >
    > >Sybase Problem:
    > >'//code starts
    > > acnSybase.Open "Provider=MSDASQL.1;Password=(password);Persist

    Security
    > >Info=True;" & _
    > > "User ID=(userid);Data Source=(DSN)"
    > > acnSybase.CommandTimeout = 3360
    > >
    > > Set arsData = New ADODB.RecordSet
    > > strSQL = "SELECT ......"
    > > arsData.Open strSQL, acnSybase, adOpenDynamic, adLockOptimistic
    > >'//code ends
    > >I am getting error message of "ODBC driver does not support the requested
    > >properties" at the arsData.Open line. The connection was tested okay.
    > >There must be something on my arsData.Open that Sybase does not like, and

    > I
    > >don't know what it is.
    > >
    > >
    > >Oracle Problem:
    > >'//code starts (in different module)
    > > acnSybase.Open "Provider=MSDASQL.1;Password=(password);Persist

    Security
    > >Info=True;" & _
    > > "User ID=(userid);Data Source=(DSN)"
    > >
    > > acnOracle.Open "Provider=MSDAORA.1;Password=(password);User
    > >ID=(userid);Data Source=(server name);" & _
    > > "Persist Security Info=True"
    > > acnOracle.CommandTimeout = 3360
    > >
    > > Set arsData = New ADODB.RecordSet
    > > strSQL = "SELECT ........ "
    > > arsData.Open strSQL, acnOracle, adOpenDynamic, adLockOptimistic
    > >
    > > Set acnMyDB = New ADODB.Connection
    > > acnMyDB.Open "Provider=SQLOLEDB.1;Password=;Persist Security

    Info=True;"
    > >& _
    > > "User ID=sa;Initial Catalog=ATS;Data Source=(local SQL
    > >Server name)"
    > > acnMyDB.CommandTimeout = 3360
    > >
    > > If arsData.EOF = True Then 'the data cannot be found in this

    database
    > > acnMyDB.Execute "INSERT INTO ......" 'set a dummy record
    > > Exit Sub
    > > End If
    > >'//code ends
    > >Again, there is no problem with the connection to Oracle and no problem

    > with
    > >the arsData.Open. But my application hangs at the "If arsData.EOF"
    > >statement. Does anyone know why?
    > >
    > >Any help will be very much appreciated.
    > >
    > >
    > >
    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center