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
Recent Comments