Create a Database Theme with fields defined by a SQL statement

Summary

You can use Avenue to create a Database Theme with the fields defined by a SQL statement. The sample script below demonstrates how to create a DBtheme with all columns, for example: Select * From table.

Procedure

1. Open a new script window.
   
   
   A. Activate the Project window.
   B. Click the Scripts icon.
   C. Click New.
   
   
2. Copy the following code into the new script window.
   
   

   Code:
   '-- Get view
   aView = av.GetProject.FindDoc("View1")
   
   '-- Establish connection parameters
   serv = "SERVERNAME"
   inst = "INSTANCE"
   datab = ""
   user = "USERNAME"
   pass = "PASSWORD"
   
   '-- Make sde connection with above parameters
   anSDECon = SDEConnection.Make(serv+":"+inst, datab, user,pass)
   
   '-- Make a querydef object of the connection
   aQDef = SDEQueryDef.Make(anSDECon)
   
   '-- Use sql select statement "select *" to get all fields
   aQDef.SetSQL("Select * from TABLE.NAME")
   
   '-- Make a dbtable from this querydef object
   aDBtable = DBtable.Make(aQdef)
   
   '-- Get all fieldnames for use in the dbtheme
   theFields = aDBtable.GetFields
   
   '-- Make another querydef object for dbtheme
   aQdef2 = SDEQueryDef.Make(anSDECon)
   
   '-- Pass fieldnames list to the SetSelectColumns
   aQDef2.SetSelectColumns(theFields)
   
   '-- From statement
   aQDef2.SetFromTables({"TABLE.NAME"})
   
   '-- Make scolumn and dbtheme
   theSCol = SDESColumn.Make(anSDECon, "TABLE.NAME", "Shape")
   theDBTheme = DBTheme.Make(aQDef2, theSCol, Polygon)
   aView.AddTheme(theDbtheme)

   
   
3. Find the words below in the code and replace them with the proper values.
   
   

   SERVERNAME - Name of the SDE server
   INSTANCE - Instance found in the Services File
   USERNAME - SDE login name
   PASSWORD - SDE password
   TABLE.NAME - Name of table to query

   
   
4. Click the compile
   
   button and then the run
   
   button.