Oracle's OLEDB Provider ?

[theMaverick]

Hi,

Im new in VB/Oracle development and I just want to know where I can get Oracle's OLEDB Provider (OraOLEDB)? Im currently using Microsoft OLEDB Provider for Oracle (MSORA).

BTW, how do i access / manipulate data in Oracle using this method? do i use the Execute method?

Thanks

[Kunchesm]

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
 
con.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=scott;Password=Tiger;Data Source=ksmdsn"
''give your DSN name instead of ksmdsn for 'Data Source'
con.Open
rs.CursorLocation = adUseClient
rs.Open "select * from t_person ", con
MsgBox rs.RecordCount

u can also use con.Execute method to Execute your Queries.

[theMaverick]

Thanks Kunchesm. Can you post some code on how to Insert, Update & Delete row in Oracle using ADO code / Exceute method?

How do I enable Transaction Procesing? Commit & Rollback?

Thanks

[Kunchesm]

con.Open
eno=1
ename="test1"
'' Replace t_person with ur table name and eno, ename with fields in ur table.
strSql= "insert into t_person values(" & eno & ",'" & ename & "') "  ''for Inserting
strSql = "update t_person set ename='" & ename & "' where eno=" & eno ''for updating
strSql = "delete from  t_person set ename='" & ename & "' where eno=" & eno ''for Deleting
con.Execute strSql '' strSql contains the SQL Query for insert or updaet or delete

[theMaverick]

Thanks so much Kunchesm for your help. BTW, how do issue commit & Rollback? do i use the BeginTrans... commit of VB or issue COMMIT as an SQL and use the Execute method? Thanks in advance.

[abhijit]

Originally posted by theMaverick
Thanks so much Kunchesm for your help. BTW, how do issue commit & Rollback? do i use the BeginTrans... commit of VB or issue COMMIT as an SQL and use the Execute method? Thanks in advance.

Without a commit your inserts will be lost in Oracle rollback segments. I suggest that you use the connection object to control the transaction.

For instance if adoCn is your connection object then

adoCn.BeginTrans
    --Use your insert / update / delete statements
adoCn.CommitTrans
 
In case of any error you could revert to 
adoCn.RollbackTrans

Cheers!
Abhijit

[theMaverick]

Thanks for your answers. but w/c is better/faster in terms of system performance? issue COMMIT as and SQL statement using the Excute Method or using BeginTrans, CommitTrans & RolbackTrans?

[abhijit]

In my experience, i have found the adoCn.CommitTrans to be slightly faster.

Again, the committing doesn't take much time, cos the database requires only a flag to be changed. The record insert has already taken place by then.

Its a rollback which usually takes time.

Another advice, if you want your inserts to be really fast, do not issue them against a connection object.

adoCn.Execute ('insert into')

Instead use command-objects with parameters and bind variables. This is highly scalable, which is what you want Oracle to be. Example

adoCmd.CommandType = adcmdtext
adoCmd.Commandtext = "Insert into table values(?,?)"
adoCmd.Parameters.Append adoCmd.CreateParameter("p0",advarchar,"hello")
adoCmd.Parameters.Append adoCmd.CreateParameter("p1",advarchar,"world")
adoCmd.Execute

Cheers,
Abhijit

[theMaverick]

Many Thanks abhijit.

based on your advise, can i also assume that Updates & Deletes are faster using ADO's Command object?

[abhijit]

Originally posted by theMaverick
Many Thanks abhijit.

based on your advise, can i also assume that Updates & Deletes are faster using ADO's Command object?

YES

[theMaverick]

Hi do you suggest using Data Environment Designer in developing VB app on Oracle backend? any known issues on this?

Another thing, is it advisable to use AddNew, Update (etc ...) methods instead of executing SQL statements on Connection & Command objects? or AddNew, Update (etc...) methods are not available in Oracle OLEDB providers?

Many Thanks