'MACRO TITLE: SELECTIVELY ADD ORACLE SEQUENCE ' This macro presents a dialog that allows the user to specify the ' naming convention and options for an Oracle Sequence. The ' Sequence is added to the PreSQL of the selected tables. ' Author: Jason E. Tiret ' Date: 8/17/2000 Sub Main Dim MyEntity As Entity Dim MySelectedObj As SelectedObject Dim MyDiagram As Diagram Dim MyModel As Model Dim MySubModel As SubModel Dim Sequence As String Dim ID As Integer ' Set ER variables Set MyDiagram = DiagramManager.ActiveDiagram Set MyModel = MyDiagram.ActiveModel Set MySubModel = MyModel.ActiveSubModel Begin Dialog UserDialog 470,364,"Oracle Sequence Macro" ' %GRID:10,7,1,1 CheckBox 20,126,160,14,"&INCREMENT BY",.IncreChbx CheckBox 20,154,120,14,"&START WITH",.startwChbx CheckBox 20,182,310,14,"M&AXVALUE / NOMAXVALUE (gray check)",.maxvalChbx,2 CheckBox 20,210,300,14,"MI&NVALUE / NOMINVALUE (gray check)",.minvalChbx,2 CheckBox 20,238,280,14,"C&YCLE / NOCYCLE (gray check)",.cycleChbx,2 CheckBox 20,266,270,14,"&CACHE / NOCACHE (gray check)",.cacheChbx,2 CheckBox 20,294,290,14,"&ORDER / NOORDER (gray check)",.orderChbx,2 TextBox 340,119,110,21,.increTxt TextBox 340,147,110,21,.startwTxt TextBox 340,175,110,21,.maxvalTxt TextBox 340,203,110,21,.minvalTxt TextBox 340,259,110,21,.cacheTxt OKButton 60,322,140,21 CancelButton 270,322,140,21 Text 20,98,230,14,"Choose Options:",.Text1 Text 20,7,380,14,"Choose Naming Convention. Default is ""S_""",.Text2 OptionGroup .Group1 OptionButton 40,28,130,14,"Use &Default",.OptionButton0 OptionButton 40,49,100,14,"Add &Prefix",.OptionButton1 OptionButton 40,70,120,14,"Add S&uffix",.OptionButton2 Text 190,70,90,14,"Prefix/Suffix:",.Text3 TextBox 290,63,110,21,.attachTxt End Dialog Dim dlg As UserDialog If Dialog(dlg) = -1 Then For Each MySelectedObj In MySubModel.SelectedObjects ' Only need entities If MySelectedObj.Type = 1 Then ID = MySelectedObj.ID Set MyEntity = MyModel.Entities.Item(ID) If dlg.Group1 = 0 Then ' use default Sequence = "CREATE SEQUENCE " & "S_" & MyEntity.TableName & vbCrLf ElseIf dlg.Group1 = 1 Then ' use prefix Sequence = "CREATE SEQUENCE " & dlg.attachTxt & MyEntity.TableName & vbCrLf Else ' use suffix Sequence = "CREATE SEQUENCE " & MyEntity.TableName & dlg.attachTxt & vbCrLf End If ' Add Increment If dlg.IncreChbx = 1 Then Sequence = Sequence & vbTab & "INCREMENT BY " & Val(dlg.increTxt) & vbCrLf End If ' Add Start With parameter If dlg.startwChbx = 1 Then Sequence = Sequence & vbTab & "START WITH " & Val(dlg.startwTxt) & vbCrLf End If ' Add MaxValue parameter If dlg.maxvalChbx = 1 Then Sequence = Sequence & vbTab & "MAXVALUE " & Val(dlg.maxvalTxt) & vbCrLf ElseIf dlg.maxvalChbx = 2 Then Sequence = Sequence & vbTab & "NOMAXVALUE " & vbCrLf End If ' Add MinValue parameter If dlg.minvalChbx = 1 Then Sequence = Sequence & vbTab & "MINVALUE " & Val(dlg.minvalTxt) & vbCrLf ElseIf dlg.minvalChbx = 2 Then Sequence = Sequence & vbTab & "NOMINVALUE " & vbCrLf End If ' Add Cycle parameter If dlg.cycleChbx = 1 Then Sequence = Sequence & vbTab & "CYCLE " & vbCrLf ElseIf dlg.minvalChbx = 2 Then Sequence = Sequence & vbTab & "NOCYCLE " & vbCrLf End If ' Add Cache parameter If dlg.cacheChbx = 1 Then Sequence = Sequence & vbTab & "CACHE " & Val(dlg.cacheTxt) & vbCrLf ElseIf dlg.cacheChbx = 2 Then Sequence = Sequence & vbTab & "NOCACHE " & vbCrLf End If ' Add Order parameter If dlg.orderChbx = 1 Then Sequence = Sequence & vbTab & "ORDER " & vbCrLf ElseIf dlg.orderChbx = 2 Then Sequence = Sequence & vbTab & "NOORDER " & vbCrLf End If Sequence = Sequence & ";" & vbCrLf MyEntity.PreSQL = MyEntity.PreSQL & vbCrLf & Sequence Sequence = "" End If Next MySelectedObj End If End Sub