Solved

oracle link

Posted on 1997-08-21
5
326 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

792 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