How To: Create a Database Theme with fields defined by a SQL statement


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.


Create a dbtable or SDEtable, then trap the field names and pass them to the SetSelectColumns request.

  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.

    '-- 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

    '-- From statement

    '-- Make scolumn and dbtheme
    theSCol = SDESColumn.Make(anSDECon, "TABLE.NAME", "Shape")
    theDBTheme = DBTheme.Make(aQDef2, theSCol, Polygon)

  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
    [O-Image] Script compile button
    button and then the run
    [O-Image] Run compiled script button