problem with recordset from Sybase and Oracle

[substring]

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.

[Ralph]

<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.
>
>
>
>
>

[substring]

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.
> >
> >
> >
> >
> >
>