connection w/ Oracle tables

[Tom Yasnowski]

Hello Again,

I got past the regsvcs issue, but have a new one. How does one set up a data connection/command to Oracle data as opposed to
MS SQL. I have no smaples to go by, I winged itm, but it blew up.
(The connection is in a DLL) I was using Miscrosoft OLE driver for
Oracle, not sure if that is even right. Isn't there a .net version? Any help samples appreciated
Thanks
Tom

[MrGTI]

I'd like to see an example as well.

My attempts have all failed.

[Tom Yasnowski]

I found out how to do it by chance:

(Doesn't that suck; such "hi-tech" stuff, but goodluck trying
to find solid documentation)

This example will read the first row and update a field:


Dim s_success As String
Dim s_connection As String
Dim s_command As String

Dim o_connection As OleDb.OleDbConnection
Dim o_adapter As OleDb.OleDbDataAdapter
Dim o_builder As OleDb.OleDbCommandBuilder

Dim o_dataset As DataSet
Dim o_datatable As DataTable
Dim o_datatablerow As DataRow

s_connection = "Provider=MSDAORA.1;Password=XXXX;User ID=XXX;Data Source=XXX;Persist Security Info=True"
s_command = "select * from tablename"

o_connection = New OleDb.OleDbConnection(s_connection)
o_adapter = New OleDb.OleDbDataAdapter(s_command, o_connection)
o_builder = New OleDb.OleDbCommandBuilder(o_adapter)

o_dataset = New DataSet()
o_adapter.Fill(o_abr, "tablename")

o_datatable = o_dataset.Tables("tablename")
o_datatablerow = o_datatable.Rows(0)
o_datatablerow("fieldname") = "GX"
o_adapter.Update(o_datatable)



The "trick" i discovered (by a guy trying to do the same to db2 at Wrox's site) is that you have to reference the table in the o_adapter.Fill(o_abr, "tablename"). In SQL you do not.

Good Luck
Tom

[MrGTI]

MISSING: you didn't say what o_abr was!

I can't even try the code until i know that that is supposed to be. Sorry.

[Tom Yasnowski]

Sorry,
o_abr should be changed to o_dataset;

I replaced my propriatary table names w/generic and
I missed a line!

[MrGTI]

I managed to use your code, along with some other knowledge i had to create this tighter way to get data from a table:

Private Sub btnSelectData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectData.Click
        Dim sEntry As String = "Plasma"
        Dim DS As DataSet = MakeConnectionOracle("SELECT * FROM ContactManagerCodes", sEntry)
        If DS.Tables(sEntry).Rows.Count > 0 Then
            Dim sText As String = DS.Tables(sEntry).Columns(0).Caption & ": " & DS.Tables(sEntry).Rows(0).Item(0) & vbCrLf
            sText = sText & DS.Tables(sEntry).Columns(1).Caption & ": " & DS.Tables(sEntry).Rows(0).Item(1) & vbCrLf
            sText = sText & DS.Tables(sEntry).Columns(2).Caption & ": " & DS.Tables(sEntry).Rows(0).Item(2) & vbCrLf
            MsgBox(sText, MsgBoxStyle.Information, "Found")
        Else
            MsgBox("No records found!", MsgBoxStyle.Critical, "-")
        End If
    End Sub
 
    Public Const ORACLE_DB_CONNECT_STRING = "Provider=MSDAORA.1;Password=HaHaHa;User ID=HoHoHo;Data Source=SISSOL.WORLD;Persist Security Info=True"
 
    Public Function MakeConnectionOracle(ByVal sSQL As String, ByVal sTempDataSetName As String) As DataSet
        'Creates connection to database and gets a dataset of values
        Dim Conn As OleDb.OleDbConnection = New OleDb.OleDbConnection(ORACLE_DB_CONNECT_STRING)
        Dim xAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sSQL, Conn)
        Dim DS As DataSet = New DataSet()
        'Sends back a dataset object full of data from the SQL statement
        xAdapter.Fill(DS, sTempDataSetName)
        Return DS
    End Function

[MrGTI]

I'm within a hair of having the INSERT code working. I'm having a tiny problem, but i'm trying to solve it.

It's sad how hard it is to get it to work with Oracle. All my SS2K code worked like a charm. But everything i do with Oracle is like a constant battle. But hey .... whatca gonna do right? Work uses Oracle, and i can't change that!

[Tom Yasnowski]

Yeah,
mine is very rudimentary, but based on my rather limited experience, was tickled to get it to work. Yours look interseting.
I will try it out

Thanks
Tom

[MrGTI]

Hope it works out for you Tom Yasnowski.

I should have mentioned that (of course), my project has this at the top:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

[Tom Yasnowski]

Yep Got it!

Yeah, All these tutorials/sample use SQL. I do not know why
they ignore arguably the biggest DB in terms of market share,Oracle. I guess its a lot easier to just grab "Northwind"--
Laziness IMHO.

Tom

[Tom Yasnowski]

GTI,

I realized that I did not change the sEntry = "Plasma" parameter. It still worked anyway (Good- I think), what does this this setting mean/do?

Thanks
Tom

[MrGTI]

Good to hear you also got it working.

The variable sEntry is basically a "name" for the set of data that it retrieves. You can populate the DataSet with multiple sets of data - you just need to name each one. In my case i only use the DataSet once, so i give it a basic/useless name.

As for SS2K vs Oracle - yes Oracle is still the biggest. But Microsoft has made SS2K just as powerful as Oracle, so it's only a matter of time before Oracle loses market share. Yes, i personally enjoy using SS2K way more than Oracle.

[MrGTI]

And here is the INSERT statement i promised:

Const INSERT_SQL = "INSERT INTO STU_Programs24HR (UniqueID, StudentID) VALUES (?, ?)"
        Dim Conn As OleDb.OleDbConnection = New OleDb.OleDbConnection(ORACLE_DB_CONNECT_STRING)
        Dim Cmd As New OleDbCommand()
 
        With Cmd
            .CommandText = INSERT_SQL
            .Connection = Conn
 
            .Parameters.Clear()
            .Parameters.Add(New OleDb.OleDbParameter("@p1", OleDbType.Numeric, 10))
            .Parameters.Add(New OleDb.OleDbParameter("@p2", OleDbType.VarChar, 9))
            .Parameters("@p1").Value = 123
            .Parameters("@p2").Value = "working"
            '.Parameters.Add("@p1", OleDbType.VarChar, 9).Value = "Mmmm"
 
            .Connection.Open()
            Try
                .ExecuteNonQuery()
                MsgBox("done")
            Catch Exp As OleDbException
                MsgBox(Exp.ErrorCode & " " & Exp.Message)
            End Try
            .Connection.Close()
        End With

Special thanks to my buddy Stew how helped me sort out a few minor glitches i had in getting this to work.

You'll need to use the ORACLE_DB_CONNECT_STRING constant that i created in the previous postings. As well, the line that is commented out is an alternate way of doing it.

And of course, .... you can use this to create an UPDATE statement as well.

[Tom Yasnowski]

GTI,
Thanks for the sample.

But I was wondering what these parameters do for you? (don't have the time right now to "play around")

.Parameters.Clear()
.Parameters.Add(New OleDb.OleDbParameter("@p1", OleDbType.Numeric, 10))
.Parameters.Add(New OleDb.OleDbParameter("@p2", OleDbType.VarChar, 9))
.Parameters("@p1").Value = 123
.Parameters("@p2").Value = "working"


As for the "plasma" query--thanks for clearing that up.
all in all, it seems to me the sample I originally gave you is not reflected at all in the "plasma" sample other than the standard OLE connections. So I take it you got mine to work which gave you enough of a push to try a totally different approach?

Tom

[Tom Yasnowski]

GTI
Ok, I see where my stuff is reflected in your sample. Boy I must
come off even more stupid than I really am sometimes


I didn't look at it close enough (doing many things at once today)


I still do not know what those parms are about; they wern't used
to fill the values of the insert statement where they?

[MrGTI]

In my insert statement, you will notice two question marks where there should have been values.

When you add a parameter to the Command object (called Cmd in my example) it is basically an object that will recieve a value. The first parameter is called @p1. You could have called it @UniqueID if you wanted too. You can also see where i assign a value to the parameter.

Once you peform the insert ( Cmd.ExecuteNonQuery() ) you can then just assign the parameter a new value, and then execute the insert statement again.

This code is a condensed version of what i normally do. In most situations i create all the parameters first, and then perform a loop where i assign value to the parameters and execute the SQL statement.

[Musician]

Well after all that you could download the new .net provider for oracle. http://msdn.microsoft.com/downloads/...mpositedoc.xml

[Tom Yasnowski]

GTI,

I thought that was what you were doing, but I never saw it before. Wasn't sure if those "?" where reserved or were just
your way of saying "whatever value you want". In VB6 I
built the insert sql statement with the values imbedded via
variables: sql_statement = "insert into xxx..,' " & str_value & " ' "
Your method looks a lot more practical.

Thanks

Musician,

Thanks for the link;But, Assuming your link provides the samples we have discussed, isn't it fun to watch people reinvent the wheel?

Tom

[Frans C]

I noticed you use the OleDbConnection object to connect to oracle. I would recommend using the new Oracle managed provider for .NET like Musician mentioned. There is a lot of performance to gain, because the OleDbConnection needs COM interop that slows things down.

If you still want to use the OleDbConnection object; this is from the developers guide of the Oracle OLEDB provider:

Using the OLEDB.NET Attribute in a Connection String
When using OraOLEDB with the OLE DB .NET Data Provider, the OLEDB.NET
connection attribute must be set to True as shown in the following examples:
// in VB.NET
Dim con As New OleDbConnection()
con.ConnectionString = "Provider=OraOLEDB.Oracle;User Id=scott;" & _
"Password=tiger;Data Source=Oracle;OLEDB.NET=True;"
con.Open
// in C#
...
OleDbConnection con = new OleDbConnection();
con.ConnectionString = "Provider=OraOLEDB.Oracle;User Id=scott;" +
"Password=tiger;Data Source=Oracle;OLEDB.NET=true;"
con.Open();
...

[Tom Yasnowski]

I have Oracle 8.0, This is for 8i I understand..