When working with graphic elements that are connected to database records, the process to extract the non‐graphic information is more complex. The element only stores a pair of numeric ids.
• The Entity Number, which is related to a database table by looking in the MSCATALOG table.
• The MSLink which is the unique value stored in a column of the table.
The macro must first query the MSCATALOG table to get the tablename from the Entity Number. Once the table name is known, the macro can then query that table based on the MSLink number to get the information from the table. To work with the data, the macro will build a RecordSet that will be used to hold the results of the queries. Some of the queries that you need will appear as follows.
'Get the Table name for a given entity.
Public Function GetTableName(MSCATstr As String, EntNum As Integer) _ As String
On Error Resume Next Dim RecordCount As Integer Dim RecSet1 As adodb.Recordset RecordCount = 1
'take this out for access since the USER_TABLES table does not exist!
If dbtype = "Oracle" Then
' Set RecSet1 = GetRecordSet(ADOconn, "SELECT TABLE_NAME FROM
‘ USER_TABLES WHERE TABLE_NAME = '" + MSCATstr + "'") ' RecordCount = RecSet1.RecordCount
End If
If RecordCount < 1 Then
'no entry found for given tablename MsgBox "MSCATALOG Not Found!"
RecSet1.Close
Set RecSet1 = Nothing GetTableName = ""
ElseIf RecordCount = 1 Then RecordCount = 0
Dim RecSet2 As adodb.Recordset
Set RecSet2 = GetRecordSet(ADOconn, "SELECT TABLENAME FROM " + _ MSCATstr + " WHERE ENTITYNUM = " & CStr(EntNum))
RecordCount = RecSet2.RecordCount If RecordCount = 0 Then
MsgBox "No Table for Entity: " & CStr(EntNum), vbOKOnly RecSet2.Close
Set RecSet2 = Nothing GetTableName = ""
ElseIf RecordCount = 1 Then
GetTableName = RecSet2!TableName RecSet2.Close
Set RecSet2 = Nothing Else
MsgBox "Multiple Tables for Entity: " _
& CStr(EntNum), vbOKOnly RecSet2.Close
Set RecSet2 = Nothing GetTableName = ""
End If Else
MsgBox "MSCATALOG is not Unique!"
RecSet1.Close
Set RecSet1 = Nothing GetTableName = ""
End If End Function
Public Sub PrintLink(LinkIndex As Integer) 'prints database info for a given dblink
Dim mslink As Integer Dim MSCATstr As String Dim EntNum As Integer Dim TableName As String Dim RS1 As adodb.Recordset Dim i As Integer
If oElement.HasAnyDatabaseLinks Then
Databases
'check to see if element has database link mslink = dbLinks(LinkIndex).mslink
EntNum = dbLinks(LinkIndex).EntityNumber MSCATstr = GetMSCATALOG
TableName = GetTableName(MSCATstr, EntNum) If TableName <> "" Then
Set RS1 = GetRecordSet(ADOconn, _
"SELECT * FROM " + TableName + " _ WHERE MSLINK = " & CStr(mslink)) Dim RecordCount As Integer
RecordCount = RS1.RecordCount If RecordCount = 0 Then
' no records found with matching mslink number Debug.Print "No Records Found!!"
ElseIf RecordCount = 1 Then
' one entry found for given mslink number For i = 0 To RS1.Fields.count - 1
Debug.Print RS1.Fields.Item(i).Name frmDBLinkInfo.ListBox1.List(i, 1) = "NULL"
Next Else
' multiple entries found for mslink number Debug.Print "MSLINK is not Unique!!"
End If RS1.Close
Set RS1 = Nothing End If
Else
' element does not have any database links Debug.Print "Element has no DB Links"
End If End Sub
Exercise: Obtain database information
1 Create a macro to query an element and get the database information that is related to the element.
XML
XML is a technology for creating self‐documented structured storage of
information. XML can be used to create text files that can be interpreted by any macro that is capable of parsing XML data.
Note: As of this writing, the XML API in VBA is limited to using the Microsoft MSXML6 libraries. In the future, methods will be provided to add XML as additional element information.
UserData
UserData is extra information that is added to an element by a macro. The structure of the information is determined by the macro.
The macro is assigned an ID number by Bentley. The ID number is used to prevent macro conflict. To work with user data, the macro will need to first get the user data as a block of information. Then it will need to process the block of data using the CopyXXX method. The Copy methods use the copyToDataBlock parameter to determine if the data is being copied to or from the datablock.
' Do not use 22352 as your attribute ID. You must obtain a ' unique attribute ID from Bentley Systems.
Private Const attrId As Long = 22352
' AddLinkage and GetLinkage both transfer the data using TransferBlock.
' That way, it is easy to be certain that the transfer always occur in the ' same order.
Private Sub TransferBlock(dblk As DataBlock, _ name As String, _
value As Long, _
copyToDataBlock As Boolean)
dblk.CopyString name, copyToDataBlock dblk.CopyLong value, copyToDataBlock End Sub
Sub AddLinkage() Dim ele As Element
Xdata
Dim id As DLong
Dim dblk As New DataBlock id = DLongFromLong(50296)
Set ele = ActiveModelReference.GetElementByID(id)
TransferBlock dblk, "Added by User Attributes Example", 50296, True ele.AddUserAttributeData attrId, dblk
ele.Rewrite End Sub
Sub GetLinkage() Dim ele As Element Dim id As DLong
Dim dblk() As DataBlock
Dim value As Long, name As String id = DLongFromLong(50296)
Set ele = ActiveModelReference.GetElementByID(id) dblk = ele.GetUserAttributeData(attrId)
TransferBlock dblk(0), name, value, False MsgBox "NAME: " & name & ", VALUE: " & value End Sub
Note: When macros use the CopyString method, VBA will copy a Unicode String to the element. If the macro needs to be compatible with MDL the macro needs to convert the String to an Array of bytes, then use the CopyByteArray method.
Xdata
Another method that applications can use to store information is Xdata. Xdata is a format that AutoCAD uses to store extra information on elements. This has the advantage of making the information compatible with the DWG format.
Sub InterpretXData(Xdata() As XDatum) Dim I As Long
Dim J As Long Dim D As DLong Dim Lev As Level
Dim vt As Variant
' Using Debug.Print, display all of the XData to the Immediate Window of ' the Visual Basic Editor.
For I = LBound(Xdata) To UBound(Xdata) With Xdata(I)
Debug.Print GetXDatumName(.Type) & " --- ";
If VarType(.Value) = vbEmpty Then Debug.Print "the value is empty"
Else
Select Case .Type
Case msdXDatumTypePoint, _ msdXDatumTypeWorldDirection, _
msdXDatumTypeWorldSpaceDisplacement, _ msdXDatumTypeWorldSpacePosition
' Value is of the type Point3d.
Debug.Print .Value.X; .Value.Y; .Value.Z Case msdXDatumTypeDatabaseHandle
' Value is a hex string. Get the element ' ID by calling DLongFromHexString Debug.Print "&H" & .Value
Dim eleID As DLong
eleID = DLongFromHexString(.Value) Case msdXDatumTypeBinaryData, _
msdXDatumTypeUnsupported ' Value is of the type Byte().
For J = LBound(.Value) To UBound(.Value) Debug.Print Hex(.Value(J)); " ";
Next J Case Else
' Value is of a type that can be printed directly.
Debug.Print .Value End Select
End If
End With ' Xdata(I) Next I
End Sub
Xdata
Sub ShowEleXData()
' Find all of the XData in any element Dim ee As ElementEnumerator
Set ee = ActiveModelReference.GraphicalElementCache.Scan Do While ee.MoveNext
If ee.Current.HasAnyXData Then Dim appNames() As String Dim index As Long
appNames = ee.Current.GetXDataApplicationNames
For index = LBound(appNames) To UBound(appNames) Dim aXdata() As XDatum
aXdata = ee.Current.GetXData(appNames(index)) InterpretXData aXdata
Next End If Loop
End Sub
Sub ShowAllModelXData()
' Find all XData contained on any model in the active design file Dim theModel As ModelReference
Dim appNames() As String Dim index As Long
For Each theModel In ActiveDesignFile.Models If theModel.HasAnyXData Then
Debug.Print "---Reporting XData for model " & _ theModel.Name & " ---"
appNames = theModel.GetXDataApplicationNames For index = LBound(appNames) To UBound(appNames)
Dim aXdata() As XDatum
Debug.Print "--- Application " & _ appNames(index) & "---"
aXdata = theModel.GetXData(appNames(index))
InterpretXData aXdata Next
End If Next
End Sub
Function GetXDatumName(xdType As MsdXDatumType) As String ' Translates an XData type into a String
Select Case xdType
Case msdXDatumTypeBinaryData
GetXDatumName = "Binary Data"
Case msdXDatumTypeControlString
GetXDatumName = "Control String"
Case msdXDatumTypeDatabaseHandle
GetXDatumName = "Database Handle"
Case msdXDatumTypeDistance
GetXDatumName = "Distance"
Case msdXDatumTypeInt16
GetXDatumName = "Int16"
Case msdXDatumTypeInt32
GetXDatumName = "Int32"
Case msdXDatumTypeLevel
GetXDatumName = "Level"
Case msdXDatumTypePoint
GetXDatumName = "Point"
Case msdXDatumTypeReal
GetXDatumName = "Real"
Module Review
Case msdXDatumTypeScaleFactor
GetXDatumName = "Scale Factor"
Case msdXDatumTypeString
GetXDatumName = "String"
Case msdXDatumTypeUnsupported
GetXDatumName = "Unsupported"
Case msdXDatumTypeWorldDirection
GetXDatumName = "World Direction"
Case msdXDatumTypeWorldSpaceDisplacement
GetXDatumName = "World Space Displacement"
Case msdXDatumTypeWorldSpacePosition
GetXDatumName = "World Space Position"
End Select
GetXDatumName = GetXDatumName & "(" & xdType & ")"
End Function
Module Review
Now that you have completed this module, let’s measure what you have learned.
Questions
1 What is the preferred method to work with an external database?
2 What is required to work with User Data?
3 What tools are used to work with XML data?
Module Overview
The “C” programming environment is available to applications in order to work with the data structures used in MicroStation. Applications work through the Get/
SetCExpressionValue method of the Application Object to access the “C”
expression evaluator that is in MicroStation’s runtime environment.
Module Prerequisites
• Familiarity with MDL as a platform for developing programmed customizations for MicroStation.
Refer to the Course Overview for additional pre‐requisites.
Module Objectives
After completing this module, you will be able to:
• Create VBA code utilizing MDL functions.
Introductory Knowledge
Before you begin this module, let's define what you already know.
Questions
1 What are the components of an ADOConnection string?