HOW TO

Create a Database Theme with fields defined by a SQL statement

Last Published: April 25, 2020

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

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.

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

Article ID:000002697

Software:
  • Legacy Products

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options