Connecting Oracle Database with AutoCAD

By Madhukar Moogala

Q. Problems connecting AutoCAD to Oracle database, is Oracle database is supported. How to use to API to retrieve information from my Oracle database to AutoCAD.

A. From AutoCAD 2020 there is no 32 bit application, AutoCAD comes only in 64 bit application.

If you want to connect to Oracle, the Oracle client OLE Db drivers need to in 64 bit.

You need to install these based on your Oracle database installation, at my end I have Oracle 19c database, accordingly I need to install associated Oracle 19c OLE Db drivers.

Oracle Db

Oracle Ole Db

Now connecting to Oracle Db from AutoCAD VBAIDE

Step1: Setting necessary reference files.

AutoCAD 2021 Type Library
Ole Automation
Microsoft Activex Data Objects
Microsoft Activex Data Objects Recordset 
OraOLEDb 1.0 Type Library

Step2 : Preparing Connection String

"Provider=OraOLEDB.Oracle;User ID=<yourUserId>;Password=<yourPassword;Data Source=<yourDatabase>;"

For example:

"Provider=OraOLEDB.Oracle;User ID=SYSTEM;Password=Abcdef!23;Data Source=moogalm19;"

UserId : The User you have created for your Database

Password: The Password required to log in to Database

DataSource: The Database to which you would like to connect.

Step 3: Code to make a connection and open the Database.

 
Sub ReadOracleDB()
    Dim adoDbConn   As New ADODB.Connection
    Dim adoDbRs     As New ADODB.Recordset
    Dim selectCmd   As New ADODB.Command
    
    Dim strCon      As String
    Dim RC, CC      As Long
    
    ' connection string, same userId and password, which used to logon to with sqlplus'
    ' Data Source = name of your Database'
    
    strCon = "Provider=OraOLEDB.Oracle;User ID=SYSTEM;Password=Aut0desk!23;Data Source=moogalm19;"
    adoDbConn.Open (strCon)
    
    ' open the table with adOpenStatic, so we traverse to end of all recordses'
    adoDbRs.Open "SELECT * FROM EMPLOYEES", adoDbConn, adOpenStatic
    
    If IsNull(adoDbRs.RecordCount) Or (adoDbRs.RecordCount = 0) Then
        MsgBox "No Records Found!"
        Exit Sub
    End If
    
    RC = adoDbRs.RecordCount
    CC = adoDbRs.Fields.Count
    
    Dim MyModelSpace As AcadModelSpace
    Set MyModelSpace = ThisDrawing.ModelSpace
    Dim pt(2)       As Double
    Dim MyTable     As AcadTable
    
    ' RC+2 accounts for Title and Header rows'
    
    Set MyTable = MyModelSpace.AddTable(pt, RC + 2, CC, 10, 60)
    
    Dim i           As Integer
    Dim j           As Integer
    
    
    With MyTable
        .RegenerateTableSuppressed = True
        .RecomputeTableBlock False
        .TitleSuppressed = False
        .HeaderSuppressed = False
        .SetTextStyle AcRowType.acTitleRow, "Standard"
        .SetTextStyle AcRowType.acHeaderRow, "Standard"
        .SetTextStyle AcRowType.acDataRow, "Standard"
        
        Dim col         As New AcadAcCmColor
        col.SetRGB 255, 0, 255
        
        ' title'
        col.SetRGB 194, 212, 235
        .SetCellBackgroundColor 0, 0, col
        col.SetRGB 127, 0, 0
        .SetCellContentColor 0, 0, col
        .SetCellType 0, 0, acTextCell
        .SetText 0, 0, "MOOGALM19"
        
        ' headers'
        
        i = i + 1
        For j = 0 To .Columns - 1
            .SetCellType i, j, acTextCell
            .SetText i, j, CStr(adoDbRs.Fields(j).Name)
            
        Next
        
        ' dataRows'
        
        For i = 2 To .Rows - 1
            For j = 0 To .Columns - 1
                .SetCellType i, j, acTextCell
                .SetText i, j, adoDbRs.Fields(j).Value
                
            Next j
            adoDbRs.MoveNext
        Next i
        
        .RegenerateTableSuppressed = False
        .RecomputeTableBlock True
        .Update
        .GetBoundingBox minp, maxp
        ZoomWindow minp, maxp
        ZoomScaled 0.9, acZoomScaledRelative
        
    End With
    
    ' Close the connection and free the memory'
    
    adoDbRs.Close
    Set adoDbRs = Nothing
    Set selectCmd = Nothing
    adoDbConn.Close
    Set adoDbConn = Nothing
       
    ThisDrawing.SetVariable "LWDISPLAY", 1
    
End Sub

Demo