Vb Code to Connect to Oracle Database

sdk development
1

I have some problem in finding the code to connect Vb to my Oracle database through DAO.

If someone have this code and is willing to help, it will be very appreciated.

Thanks in advance,

Dani

Dani (BOB member since 2002-09-26)

2

I don’t have any ideas on how to do this…anybody? Since I’m relatively ignorant about Oracle…what is DAO? Exactly what kind of a connection are you trying to make? :roll_eyes:

Eileen King :us: (BOB member since 2002-07-10)

3

Maybe tell us a bit more about what you’re trying to do. I’m not understanding what the BusinessObjects related question is. What BO problem are you trying to solve. Maybe we can give you some other ideas.

Cindy Clayton :us: (BOB member since 2002-06-11)

4

for DAO see http://searchdatabase.techtarget.com/sDefinition/0,sid13_gci213877,00.html

You might look into some books, e.g. JavaScript (yes, I know you are looking for VB) by Paul Wilton.

You can also search on Microsoft’s website, Developer Network for DAO/ADO, e.g.:

Andreas :de: (BOB member since 2002-06-20)

5

Funny…I didn’t know anything about this a couple of weeks ago and now I’m trying to do it…and I still don’t know anything…but I’m learning!!! :wah: :crazy_face:

Eileen King :us: (BOB member since 2002-07-10)

6

Andreas,

Have you done this? :blue: The one article you suggested indicates that some methods are faster than others. Do you have any thoughts on that? I’m trying to call Oracle packages / procedures through the SDK and I’m now to the point of trying to make the connections.

Any hints? :confused:

Eileen King :us: (BOB member since 2002-07-10)

7

use ADO (Active-X Data Object)…

It is the easiest and the most common way of connecting to databases. ADO allows access to most common db’s

avaksi :us: (BOB member since 2002-08-22)

8

I’ve currently got Project References for:

[list]Visual Basic for Applications
Business Objects 5.1 Object Library
OLE Automation
Microsoft Forms 2.0 Object Library[/list]

Do I need the Microsoft ADO Libraries or the Oracle InProc Server 3.0 Type Library?

Eileen King :us: (BOB member since 2002-07-10)

9

OK…I guessed and added the Microsoft ADO 2.0 Library.

Eileen King :us: (BOB member since 2002-07-10)

10

Eilleen

I use ADO to connect to Oracle. While connecting you can use ‘Microsoft ODBC for ORACLE’; OLEDB or ‘ORACLE ODBC Driver’ depending on what you have. You still need SQL*NET as all these still travel on that.

A simple connect to the DB might look like this…


Sub Open_RS()

    Dim oRs As New ADODB.Recordset
     
     oRs.Open "SELECT invoice_number from INVOICE where invoice_number < 100", _
    "DRIVER={ORACLE ODBC DRIVER};SERVER=prd;UID=xx;PWD=xx;DBQ=prd;"
      
    oRs.Close
    Set oRs = Nothing
    
End Sub

The basic Objects that u need to be familiar with to play with ADO are Connection, Command and Recordset. For simple things u can do everything with Recordsets alone, like in the example above.

avaksi :us: (BOB member since 2002-08-22)

11

Thank you so much!!! 8)

I’m trying to put this together with the array table information. If I ever succeed (and I will!!!) :hb: then I will post the code here. :mrgreen:

What I am attempting to do is use Oracle Packages to bring in data from some very complex calculations/business rules. This is something I was told by tech support that I couldn’t do – a year ago :reallymad: . By attending the conference and taking a class on the SDK, Joe Cotugno, Business Objects Consultant, (I’ll have to double check the spelling) said that I could do it through the VBA Procedure…

I lost one year…and Business Objects lost a lot of credibility with my department :flush: …we look like we can do processes that I was sure we could do and I wouldn’t take no for answer!

This forum and the participants here have provided answers that I would still be fumbling for. I haven’t coded VBA for almost 2 years… I am just so appreciative that the people here are willing to share their information to try and help others. What a wonderful community we are a part of!!! :cheers:

Eileen King :us: (BOB member since 2002-07-10)

12

Eileen

I will check that tonite and post an example by tomorrow morning, hopefully in time for your meeting :wink:, on how to access Oracle packages from VB and then moving the result set into a dpVBA cube.

Essentially, packages can be accessed by dot notation, for e.g. Package.SPname, I am not sure as to how to do the stuff with BO but theoretically it should be simple. You might wish to check with Oracle Developers at your location who are using these packages for details as well…

also see

avaksi :us: (BOB member since 2002-08-22)

13

They don’t like me :evil: ! I think they’re afraid that if I can make this simple, they’re in trouble…! :wah: :rotf:

Eileen King :us: (BOB member since 2002-07-10)

14

Here’s the link to the code for connecting to Oracle via ADO and running the stored procedure.

Thanks to everybody! :mrgreen:

Eileen King :us: (BOB member since 2002-07-10)

15

Hi Eileen,

I checked the code just now.
Will it be any different with Sybase or Can I use the same code?
Basically, What are all the changes need to be made?

Thanks,

Ravi Amara :us: (BOB member since 2002-10-02)

16

Each connection seems to have the same basic parameters…but with minor modifications. I’d start there and see if it comes back with any parameters missing…

Eileen King :us: (BOB member since 2002-07-10)

17

The procedure will essentially remain the same but you will have to use the Sybase ODBC driver instead of the Oracle ODBC driver. Also refer to the Merant ODBC connector guide to get a better overview of the options available.

Please note that though ODBC is simple to use Sybase V 12.x onwards ship with an OLE DB Provider that is far more proficient. It will show up in the list of drivers as Sybase ASE Provider.
Sybase also has the TDS driver that has no dependence on the OpenClient and therefore less problems and better performance.
For Sybase 11.x use OLEDB Provider for ODBC Drivers.
You could always use good old ODBC and for most queries there will be no performance loss. Use the following Connection string: (“Driver={Sybase System 11};PWD=syb;UID=syb;DB=dictionary;servername=SYB_PRD”)

I am sorry but Sybase has a TOTALLY useless website. You will have to CALL Sybase tech support and ask for ftp location of the drivers. Sybase doesn’t write its own drivers, they are made by Merant who also sells a value-added driver!! Can u believe that… I am sure u would jump off a cliff before buying a driver for a database u already have but these drivers do run a version ahead of Sybase and include some cool stuff. A trial should be avl at Merant’s site…

avaksi :us: (BOB member since 2002-08-22)