Solved

oracle link

Posted on 1997-08-21
5
320 Views
Last Modified: 2006-11-17
i need to make an application to access my oracle database
i am new to vb4 and need help with ODBC maybe someone could help me to simply run any sql query in a simple form so i can get the idea ??? or maybe that`s too much to ask :) ... thanks
0
Comment
Question by:mattwarr
  • 2
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
tward earned 200 total points
ID: 1431774
One of the easiest  ways to connect to Oracle is through the Oracle Objects for Ole, here is some code that I use in order to connect to a database.  If you need any clarification just let me know.

  Option Explicit

  ' Oracle setup '
  Public OraSession As Object
  Public oraDatabase As Object

  ' Oracle Connect String (Used in DatabaseOpen) '
  Public CONNECT$

  ' Oracle Database String (Used in DatabaseOpen) '
  Public DATABASE$

  ' Oracle SQL String (Used in OpenDynaset) '
  Public SQL$

  Public DynaSetOpenError As Boolean

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  DatabaseOpen(CONNECT$,DATABASE$)  '
'                                                 '
' Description:  Opens the Database Passed to it   '
'               in DATABASE using the             '
'               NAME/PASSWORD in CONNECT.         '
'                                                 '
'       Input:   CONNECT$ - "NAME/PASSWORD"       '
'               DATABASE$ - "X:ORA_DB_SRV:ORCL"   '
'                           "PROD"                '
'                                                 '
'      Output:  Message Box on Error, otherwise   '
'               an Open Database Object.          '
'                                                 '
'     Returns:   True = Database was Opened.      '
'               False = Database Open Failed.     '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function DatabaseOpen(ByVal CONNECT$, ByVal DATABASE$) As Boolean
 
  DatabaseOpen = True
 
  ' Setup The Oracle Session '
  On Error Resume Next
  Set OraSession = CreateObject("oracleinprocserver.xorasession")
  If Err.Number <> 0 Then
   
    MsgBox "Oracle Error:  " & Err.Description, vbOKOnly + vbCritical, "Database Session Error..."
    DatabaseOpen = False
    On Error GoTo 0
    Exit Function
   
  End If
 
  ' Setup The Oracle Connection '
  On Error Resume Next
  Set oraDatabase = OraSession.dbopendatabase(DATABASE$, CONNECT$, 0&)
 
  If Err.Number <> 0 Then
   
    MsgBox "Oracle Error:  " & Err.Description, vbOKOnly + vbCritical, "Database Connection Error..."
    DatabaseOpen = False
   
  End If
 
  On Error GoTo 0
 
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  DatabaseClose()                   '
'                                                 '
' Description:  Closes an Open Oracle Database.   '
'               Database should have been opened  '
'               using OpenDatabase.               '
'                                                 '
'       Input:  NONE.                             '
'                                                 '
'      Output:  Clears OraSession and oraDatabase.'
'                                                 '
'     Returns:  NONE.                             '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub DatabaseClose()
 
  Set OraSession = Nothing
  Set oraDatabase = Nothing

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  OpenDynaset(SQL$)                 '
'                                                 '
' Description:  Opens a Dynaset using the SQL$    '
'               SQL Statement passed to it.       '
'                                                 '
'       Input:  SQL$ - Any valid SQL Statement.   '
'                                                 '
'      Output:  An Open Dynaset, Use CloseDynaset '
'               to Close Dynasets.                '
'                                                 '
'     Returns:  The OpenDynaset Object.           '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function OpenDynaSet(ByVal SQL$) As Object
 
  On Error Resume Next
 
  DynaSetOpenError = False
 
  Set OpenDynaSet = oraDatabase.DBCREATEDYNASET(SQL$, 0&)
 
  If Err.Number <> 0 Then
 
    Call MsgBox("Error #" & Err.Number & ":  " & Err.Description, vbCritical + vbOKOnly, "SQL Query Error.....")
    DynaSetOpenError = True
   
  End If
  On Error GoTo 0
 
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  CloseDynaSet()                    '
'                                                 '
' Description:  Closes the Open Dynaset.          '
'                                                 '
'       Input:  None.                             '
'                                                 '
'      Output:  None.                             '
'                                                 '
'     Returns:  Nothing into the DynaSet Object.  '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function CloseDynaSet() As Object

  Set CloseDynaSet = Nothing

End Function

Public Sub btnStart_Click()

  Dim DocumentDynaset As Object

  If DatabaseOpen("NAME/PASS", "DATABASE") = False Then
   
    End
 
  End If
 
       Set DocumentDynaset = OpenDynaSet("SELECT * FROM DOCUMENT WHERE CONCAT(PREFIX,DOCNO)='" & DocId & "'")
       
       If DocumentDynaset.Fields("NRPAGES").Value <> 0 Then
       End If
       
      Set DocumentDynaset =  CloseDynaset
      DatabaseClose

End Sub

0
 

Author Comment

by:mattwarr
ID: 1431775
thanks very much for your excellent answer ... now i just need to know how to run the thing ... and see what it does .. i`m gonna start a new project and paste the code in general ... see what happens .. thanks again :)
0
 

Author Comment

by:mattwarr
ID: 1431776
sorry tward i think you went to a lot of trouble with this answer but i am unable to inplement the code you gave ... i pasted it into general desc in a new form .. but cant make it do anything ...sorry for being so lame .. please explain further if you can
0
 
LVL 7

Expert Comment

by:tward
ID: 1431777
You need to paste it into a Module, not a form.  Also you need to install Oracles Objects for OLE.

I can send you the module via Email if that would be easier then all you have to do is add it to your project.

Send me email requesting the ORACLE.BAS file and I'll send it to you.

tward@msii.com

After you receive the module include it in your project and then use the stuff in the btnStart_Click event to actually connect to the database and open a Dynaset.

If you still have problems as always just post some of the code that you are using here and I'll try to help you out the best I can.  

I have about 30 different programs that use the same ORACLE.BAS to connect to an Oracle 7 database via Objects for OLE v2.0.


0
 

Expert Comment

by:mohd130
ID: 2189432
Dear tward

Please if you can help me by sending the oracle.bas to e-mail : mohd@aus.ac.ae

also if you can aswer my question:

http://www.experts-exchange.com/jsp/qShow.jsp?ta=vbdatabases&qid=10230212
Thanks alot
Moh
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now