Copyright: Copyright (c) 1990-1998 ESRI, Inc.


Error message displayed when a topic is not found
Topic not available

Sorry, the topic you chose to display is not available in ArcView's help system.

To see ArcView's Help Contents, Index, and Find tabs, please click the Help Topics button.


Add Database Table (Database Access extension)
Add Database Table

Lets you establish a connection to a database server and create an SQL query to retrieve records from the relational database. A database table representing the records retrieved by your query is added to your project.


Add Database Table (Dialog box)

This dialog is for the Database Access extension.

Lets you establish a connection to a database server and create an SQL Select statement to retrieve records from the database. A database table representing the records retrieved by your query is added to your project.

Dialog box options

Table name    The name of the table ArcView creates to display the results of the query.

Database Access    The database interface ArcView uses to connect to the external database; for example, SDE.

Create a new connection    This option lets you create a new connection between ArcView and the database. You can use the new connection to create a database table. Click the Connect button [image] to specify the parameters for your new connection.

Use an existing connection    This option lets you choose which existing database connection you are going to use to create a database table.

Owner    Specifies the owner(s) of the database tables that are listed in the Tables list. If you don't specify a name, only the tables owned by the current user are listed. By default you will see an asterisk (*) in this box, which will let you see a list of all the tables you can access owned by all the users in the database.

Tables    Provides a list of tables in the current database. If you click on a table name, ArcView displays all the table's columns in the Columns list box. When you double-click a table name, ArcView inserts the table name into the From text box. ArcView inserts commas between the names of multiple tables. Table names can appear in any order.

Columns    Provides a list of columns in the selected table. When you double-click on a column name ArcView inserts the column name, prepended by its table prefix, into the Select text box. ArcView inserts commas between multiple column names. If you select <All Columns>, ArcView inserts the wild card character asterisk (*) into the Select text box.

Select    Specifies the columns to retrieve. If you include more than one column in the Select clause, separate the column names with commas. List the columns in the order you want to them to appear in the database table. If a column name appears in more than one table listed in the From clause, precede the column name with the table name and a dot (.).

From    Specifies the tables that contain the columns to retrieve.

Where    Indicates which records to retrieve. You can use the Where clause to determine which records from the tables listed in the From clause will appear in the results of the Select statement. Records are selected according to the conditions listed in the Where clause. For example, you can select all stores in the Midwest region whose sales have increased since the previous year (Sales93 > Sales92 ). The Where clause is optional. If you don't include Where, the query will select all records.

Clear    Clears all text entry areas of the dialog box.

Query    Sends the query to the relational database and creates the new database table.

You don't have to add the keywords Select, From, or Where; ArcView adds them for you when you send the query. ArcView does not check the Where clause for syntax, it simply 'packages it' and sends it to the selected database.

Database table properties

If you opened this dialog by choosing Properties from the Table menu, it will be called Database Table Properties instead of Add Database Table. The dialogs are the same except for a few changes to the controls. You will see OK and Cancel buttons instead of Query and Clear. You will also see an SQL Statement box instead of the Select, From, and Where boxes.

SQL Statement   Contains the entire SQL Select statement used to retrieve the contents of the database table from the relational database. You can change the statement by adding or deleting text.


Add Database Theme (Database Access extension)
Add Database Theme

Lets you add one or more themes to the current view from a spatial column in a relational database. Once you add a theme it appears in the view's Table of Contents, where you can click it's check box to draw it in the view.


Database Table Preferences (Database Access extension)
Database Table Preferences

Lets you change the number of records that are retrieved from the relational database when you create a database table, or add more records to it. Also lets you change the interface you use to create a database table; you can use either the standard SQL dialog or the SQL Query Wizard.


Database Table Preferences (Dialog box)

This dialog is for the Database Access extension.

There are two properties that affect every database table in your project; this dialog lets you change them.

Dialog box options

Choose Add Database Table interface   This property selects the interface you use to create a new database table when you choose Add Database Table from the Project menu. You can choose between the Add Database Table dialog and the SQL Query Wizard, which are described below.

Add Database Table Dialog    This interface lets you create an SQL Select statement by typing the Select, From, and Where clauses into separate text boxes. Use this interface if you are familiar with SQL. There are no limitations to the SQL statement you can create with this interface.

SQL Query Wizard    This interface provides several panels that walk you through the steps of creating an SQL Select statement. Use this interface if you aren't familiar with SQL. If you are experienced in writing SQL queries, this interface can be limited; it doesn't provide the opportunity for you to calculate values, sort your results, or create nested queries.

Get x Rows at a Time    This property lets you choose how many rows are added to database tables at a time. After setting this property, new database tables will contain x rows when they appear in your project, and x rows will be added to database tables when you choose Get More Rows from the Table menu. x can be any number between 1 and 3500; by default it is 100.


Database Theme Definition (Theme Properties dialog box) (Database Access extension)
Definition (Theme Properties dialog box)

This dialog is for the Database Access extension.

Shows how the contents of the database theme are defined.

Dialog box options

Theme Name    The name of the theme. Theme names may include multiple words along with spaces, numbers and punctuation. By default, a new theme is given the name of the spatial column, which is the data source that the theme represents. The theme's name includes the name of the table the spatial column resides in, and the name of the table's owner if you don't own the table.

Use Suffix    Check the box if you want to show the name of the attribute used to classify the theme when the theme’s legend has been edited so that its features are symbolized based on the values of a field. That field name will be appended to the theme name and displayed in the Table of Contents. For example, a theme called USA that is classified on its Area field will appear in the Table of Contents named USA by Area.

Spatial Column   The name of the spatial column that contains the source data for this database theme. A spatial column is a field in a table in the database that has a collection of shapes sharing a common theme.

Connection   When you log in to a saved project, you may type your password incorrectly, or the SDE server may be down. ArcView will continue opening the project, but it will not be able to reestablish the database connections. You can repair a database theme's connection using the following two controls:

Create a new connection   This option lets you make a new database connection for your theme. Click the Connect button [image] to specify the parameters for your new connection.

Use an existing connection   This option lets you associate your theme with a different, existing connection.

SQL Query [image]   You can change a database theme's definition query using the SQL Query Wizard. For example, if the database theme currently represents all the polygon features in a spatial column representing land use, you can specify that only polygons with a particular land use type will be represented in the theme. You can also change which tables in the database the theme's attributes come from, and which attributes are available inside ArcView.

Query Order   Your database theme's definition query can include both attribute criteria and spatial criteria for choosing which features are part of the theme. To improve the performance of the theme's query, you can change the order in which these criteria are evaluated using the following three controls:

Optimized   The default. Optimized queries behave the same as Spatial first queries except when the theme's query uses a spatial filter with a negative test value (e.g., return all the features that fall outside this buffer).

Attributes first   Evaluates attribute criteria, defined in the Where clause of the theme's definition query, first. If a theme represents a very small number of wells with specific characteristics, and those wells are spread across the world, evaluating the attribute criteria first will improve draw performance. Attribute first queries are most effective when the attribute criteria consistently selects approximately 10% of the shapes in the spatial column.

Spatial first   Evaluates spatial criteria first. There are two components defining a theme's spatial criteria. One component is the view's current extent; ArcView only retrieves records whose features will appear in the view. This criteria is temporarily applied to the theme's query while drawing is in process. The other component is spatial filters that are applied to the theme's query using Avenue, for example, to select all the features that fall inside a buffer zone. You would evaluate spatial criteria first if you were zoomed in to a very small area, or if the buffer zone was very small.

Cancel    Closes the Database Theme Properties dialog box.  Any changes made during the life of this dialog box are canceled. For example, new connections are disconnected and the previous connection status is restored.


Export to Table (Database Access extension)
Export to Table

Creates a table of attributes for the selected features of the active database theme. The theme’s attributes are exported from the relational database to a local dBASE file. A dialog box appears, where you can select the location and filename of the new file. The new table opens in your project.

This menu choice is dimmed out if the active database theme does not have any attributes, if the active database theme does not have a selected set of features, or if the active theme is not a database theme.


Get More Rows (Database Access extension)
Get More Rows

Retrieves the next set of records that satisfy your database table's query from the relational database, and adds them to the table. You can change how many records are in a set.


SDE New Connection (Dialog box)

This dialog is for the Database Access extension.

Lets you establish a connection to an SDE database server.

Dialog box options

1.   Enter connection information   The first step is to create a connection to the SDE database server. To do that you need to enter information into the three text boxes described below. The server is case sensitive when evaluating these strings, so be sure you type them in correctly.

Server:Instance   Enter the SDE server name and the database instance name in the format <server>:<instance>. These two pieces of information together define which SDE database you are connecting to.

If the database instance uses SDE's default name, esri_sde, you only need to type in the server name (without the colon). If the database instance does not use the default name, you can set an environment variable called SDEINSTANCE to contain the instance name; if this variable has been set, you only need to type in the server name (without the colon).

Username   Enter your user name for accessing the SDE database.

Password   Enter your password for accessing the SDE database.

Your user name and password define your permissions in the SDE database. You may have permission to access the contents of many tables and to edit the contents of only one table; others may have permission to access different tables entirely and can't edit any tables.

2.    Press Connect to establish a connection

Connect   After filling in the text boxes above, click Connect; you are connected to the database when the Connect button dims out.

Disconnect   When the Connect button dims out the Disconnect button becomes available. If you've connected to the wrong SDE database, click Disconnect; the Disconnect button dims out and the Connect button becomes available. Now change the contents of the text boxes in step 1 to identify the correct SDE database. Click Connect when you're done.

3.   Select a database name   Some relational databases contain several databases, or groups of related tables. If the relational database you're connected to has databases of tables, a list of their names will appear; otherwise, this step will be dimmed out. You can only access one database of tables with a connection.

When you are satisfied with your new connection, click OK.


SQL Query Builder (Dialog box)

This dialog is for the Database Access extension.

 The SQL Query Builder lets you select features in a database theme by building an SQL where clause that tests the features’ attributes.

Dialog box options

Fields    Lists the theme's attributes. Double-click on a field's name to place it in the Where clause box. If you type in the name of a field instead of clicking in the field list, keep in mind that field names may be case sensitive depending on the relational database you are using. Typing Country, country or COUNTRY may or may not produce the same results.

A database theme may not have any attributes. If this is the case the Fields list will be empty. If the Fields list is not empty, you will see a field’s real name in this list instead of its ArcView alias. ArcView sends your SQL Where clause directly to the database, which does not know about ArcView's internal field aliases.

Operators    Use these operators to specify relationships between Fields and their Values in an SQL where clause. Click on an operator to place it into the Where clause box.

=	equals

>	greater than

<	less than

<>	not equal to

>=	greater than or equal to

<=	less than or equal to

 ( )	expressions enclosed in parentheses are evaluated first.

and	both expressions are true, e.g. ( area >= 100 ) and ( area <= 200 )

or	at least one expression is true, e.g. ( rainfall < 20 ) or ( slope > 35 )

not	excludes, e.g. not ( name = "california" )

The mathematical operators + - * / and arbitrary Avenue expressions can also be used in queries.

To enclose an expression in parentheses, select it in the Where clause box by dragging over it with the mouse, and then click the ( ) operator. Parentheses will enclose the selected part of the query.

Where clause box    This is where your Where clause is displayed as you build it. A list of unique attribute values is not provided for database themes. You must type attribute values directly into the Where clause box.

The following options are available for selecting features in a view:

New Set   Makes a new selected set containing the features selected by your SQL where clause. Features not in this set are deselected.

Add To Set    Adds the features selected by your Where clause to the existing selected set. If there is no existing selected set, the features selected by your Where clause become a new set. Use this option to widen your selection.

Select From Set    Selects features from the existing selected set that also satisfy the Where clause. Use this option to narrow down your selection. If there is no existing selected set, the features selected by your Where clause become a new set.

You can copy and paste queries. For example, you might use the Query Builder on a view to query a theme then decide to use this query as the theme's feature selection definition. In this case, you can copy the query, open the Query Builder from the theme's Theme Properties dialog box, and paste the query in. To copy all or part of a query, select what you want to copy and then press CTRL+C. To paste, press CTRL+V.


Adding a database theme to your view (Database Access extension)
Adding a database theme to your view

This topic is for the Database Access extension.

These steps describe how to create a database theme and add it to your view.

Before beginning these steps you need to find out some information (details for SDE) so you can connect to the database. After the preliminaries are taken care of, you can load the Database Access extension, then create a database theme.

To add a database theme to your view

1	From the View menu, choose Add Database Theme. The SQL Query Wizard appears.

2	Connect ArcView to the database containing the data (connection details for SDE).

3	Choose a spatial column, then click Next. (Tip: Hold down SHIFT to select several spatial columns and/or feature types and add them to your view at the same time.)

4	To add your theme(s) to the view now, click Finish, and skip the rest of these steps; each theme represents all polygon, line, point, or annotation features in the spatial column and all attributes in the spatial column's table. Instead, you can define which shapes and attributes your theme represents; choose the "Create a theme containing a subset of features or with joined table(s)" option, then click Next. The second option is only available if you selected one spatial column.

5	Select the tables containing the attributes you want in your theme, then click Next.

6	If you selected an attribute table above, the join panel appears; otherwise, skip this step. Join the spatial column's table and the attribute table(s) together, then click Next.

7	Create a Where clause defining which shapes your theme will represent. Some tips for creating SQL Where clauses are provided. Click Next.

8	Choose the attributes your theme will have.

9	Click Finish.

When finished, your database theme appears in your view’s Table of Contents.

When you add a theme to a view, ArcView does not immediately draw its contents. This lets you first edit the theme's legend, or change the drawing order if there are several themes, etc. To draw a theme, click the check box next to the theme's name in the view's Table of Contents.

Note   There are several ways you can join tables together in a relational database. The Join panel lets you join tables together using an inner join (the resulting table only has records where the common field's values are the same in both tables). If you want to use a different kind of join, skip the Join panel, and define the join statement you want in the Where clause panel. Remember to join the join statements and attribute criteria together using " and ".


Changing a database table's query (Database Access extension)
Changing a database table's query

This topic is for the Database Access extension.

You can change the columns and records that a database table represents by changing its query, or SQL Select statement. Using the correct SQL syntax for your relational database, you may be able to provide aliases for the columns.

To change a database table's query

1	From the Table menu, choose Properties. The Database Table Property dialog appears. The Tables list contains a list of all the tables you can access for one of the active connections. The SQL Statement box contains the complete SQL Select statement used to create the database table.

2	Change the SQL Select statement by typing in the SQL Statement box or deleting text from the Select statement. You can add a column by positioning the cursor where you want it to appear in the SQL statement, clicking on the column's table in the Tables list, then double-clicking the column in the Columns list. Some tips for creating SQL Where clauses are provided.

3	To change the table's name, type in the Table Name box.

4	To add comments about the Select statement or the purpose of the table, type in the Comments box.

5	Click OK.

The contents of the database table changes to reflect the new SQL query. The order in which the columns are listed in the SQL statement is the order in which they appear in the table. The number of records you see in the database table will not change as a result of modifying the table's query.


Changing a database theme's query (Database Access extension)
Changing a database theme's query

This topic is for the Database Access extension.

Every database theme has a query that selects columns and records from a relational database. One of the columns in a database theme's query must be a spatial column. Database themes display the shape value contained in the spatial column for each of the records retrieved from the database.

You can change the query that your database theme represents by using its definition theme property. You can change which columns of data are retrieved from the database, which tables those columns come from, what those columns are called in ArcView, and which records are retrieved from the joined tables.

To change a database theme's query

1	Click on the theme's name in the view's Table of Contents to make it active.

2	Click the Theme Properties button [image], or from the Theme menu choose Properties. The Theme Properties dialog appears.

3	Choose the Definition category if it is not already chosen.

4	Click the Query Builder button [image] in the Theme Properties dialog. The SQL Query Wizard appears.

5	Select the tables containing the attributes you want in your theme, then click Next.

6	Create a Where clause defining which shapes your theme will represent, then click Next. If tables were originally joined together to create this theme, the join statement(s) appear as part of the Where clause. If you changed the theme's tables, you must add to or remove join statements from the Where clause. Some tips for creating SQL Where clauses are provided.

7	Choose the attributes your theme will have.

8	Click Finish in the SQL Query Wizard.

9	Click OK in the Theme Properties dialog.

ArcView redraws the view. Only the features that are retrieved by the modified query appear in the theme.

What happens to the query if the source data is updated?

ArcView stores a theme’s definition query. Every time your view draws, ArcView retrieves the records from the database that both fit into the view and satisfy the theme’s definition query. When someone updates data in the database, you will see the changes in your view the next time it redraws.

For example, if you have created a theme representing the major highways in a roads spatial column, and someone edits the data to reflect new highways that are going to be built, the new highways will automatically be displayed when the view redraws.


Changing database table properties for a project (Database Access extension)
Changing database table properties for a project

This topic is for the Database Access extension.

There are two database table properties that apply to your project rather than a specific database table. One is the interface you use to create a database table, and the other is the number of records ArcView gets each time it retrieves records from the database.

ArcView provides two interfaces for creating database tables: the SQL Query Wizard, and the Add Database Table dialog. The SQL Query Wizard walks you through the steps of choosing the data you want to see in your table, then ArcView creates an SQL statement based on your choices. Use this interface if you aren't familiar with SQL.

The Add Database Table dialog lets you create an SQL Select statement by typing its Select, From, and Where clauses into text boxes. This is the default interface. You need to use this interface to create complex SQL queries.

When ArcView creates a database table, by default you will only see the first 100 records selected by that table's query. You can add more records to a database table by choosing Get More Rows from the Table menu; it will get the next 100 records selected by the table's query and add them into the table. You can change the number of records ArcView retrieves at a time to any number between 1 and 3500.

You can choose the interface you use to create a table and how many records ArcView retrieves from the database by following the steps below.

To change database table project properties

1	Make the Project window active.

2	From the Project menu, choose Database Table Preferences.

3	Select the interface you want to use to create database tables, either the SQL Query Wizard or the Add Database Table dialog.

4	Type the number of rows you want ArcView to retrieve into the text box.

5	Click OK.

The next time you choose the Add Database Table option from the Project menu, you'll see the interface you selected above. The next time you create a database table or choose Get More Rows from the Table menu, ArcView will get the number of rows you specified above and place them in the table.


Connecting to SDE (Database Access extension)
Connecting to SDE

This topic is for the Database Access extension.

Before you can start working with SDE data in ArcView, you need to find out some information  from your SDE database administrator.

To create database themes and database tables using data from an SDE database you first need to connect to the SDE server. The database is case sensitive when evaluating the strings you type in, so be sure you type them in correctly.

To connect to an SDE database server

1	Load the Database Access extension.

2	You make a connection to the database when you create a database theme or a database table, or when you repair a database theme's or a database table's connection. In the dialog you use for one these tasks, choose the Create a new connection option.

3	Choose the Create a new connection option. If you're using the SQL Query Wizard, click Next; otherwise, click the Connect button [image] to open the SDE New Connection dialog.

4	In the SDEServer:Instance text box, type the SDE server name and the database instance name, separated by a colon, like <server>:<instance>. These two pieces of information together define which SDE database you are connecting to. For more information, see the note below.

5	In the Username text box, type your user name for accessing the SDE database.

6	In the Password text box, type your password for accessing the SDE database. Together, your user name and password define your permissions in the SDE database.

7	Click Connect. You are connected to the database when the Connect button dims out.

8	Some relational databases contain several databases, or groups of related tables. If the relational database used by SDE has databases of tables, a list of their names will appear; otherwise, this step will be dimmed out. Choose the database containing the tables you want to access, or skip to the next step.

9	Click Next or OK, whichever is present in the dialog you are connecting from.

You've finished making an SDE connection. Now you can create database themes and database tables.

Note   If the database instance uses SDE's default name, esri_sde, you only need to type in the server name (without the colon). If the database instance does not use the default name, you can set an environment variable called SDEINSTANCE to contain the instance name; if this variable has been set, you only need to type in the server name (without the colon).

How and when to use the Disconnect button

When the Connect button dims out, the Disconnect button becomes available. You can see the contents of the database you're connected to in the database names list. If you look at this list and realize that you've connected to the wrong SDE database, you can change your connection by clicking Disconnect. When you click Disconnect, it dims out and Connect becomes available. Change the contents of the text boxes to identify the correct SDE database. Click Connect when you're done.

If you realize you've connected to the wrong SDE database after clicking OK or Next, how you change the connection depends on which interface you are using:

  If you're using the SQL Query Wizard and you've already clicked Next, you have two choices. One option is to click Cancel, then open the SQL Query Wizard and start again. The other option is to move back to the connection panel. Click Disconnect, then make a new SDE connection. You will lose any choices you've made in the wizard.

  If you're using the Add Database Table dialog and you've already finished creating a connection with the SDE New Connection dialog, you need to make another new connection. Choose the Create a new connection option, click the Connect button [image], then make a new SDE connection.

When connections disappear from the existing connections list

ArcView only saves connections in a project if they are associated with a database theme or a database table.

 If you create a connection in the SQL Query Wizard or the Add Database Table dialog, then click Cancel before creating a database theme or a database table, ArcView won't save the connection in the existing connections list; the connection isn't associated with a theme or a table.

  If you delete from a project all the database themes and database tables that require a connection, then the connection will be removed from the existing connections list.

 If you create a database table, join that database table to a local ArcView table, then delete the original database table, ArcView will save the connection required for the joined attributes until you remove all joins from the local table. Then, if no themes or tables require the connection, it will be removed from the existing connections list.


Creating a database table (Database Access extension)
Creating a database table

This topic is for the Database Access extension.

These steps describe how to create a database table in ArcView.

Before beginning these steps you need to find out some information (details for SDE) so you can connect to the database. After the preliminaries are taken care of, you can load the Database Access extension, then create a database table.

To create a database table you need to create an SQL Select statement that selects the data you want to see from the database. The Select statement can be broken into three parts, or clauses: Select, From, and Where. The Select clause chooses the columns you want to see, the From clause identifies the tables you're getting the columns from, and the Where clause specifies how the tables are joined together and chooses the records you want to see.

ArcView provides two interfaces for creating database tables. The SQL Query Wizard walks you through the steps of creating an SQL Select statement, while the Add Database Table dialog, the default, lets you create an SQL Select statement by typing the clauses into text boxes. You can choose the interface you want to use. Use the SQL Query Wizard if you aren't familiar with SQL.

To create a database table using the SQL Query Wizard

1	Make the Project window active.

2	From the Project menu, choose Add Database Table. The SQL Query Wizard appears.

3	Connect ArcView to the database containing the data (connection details for SDE).

4	Select the tables containing the columns you want to see, then click Next.

5	If you selected more than one table above, the join panel appears; otherwise, skip this step. Join the tables together, then click Next.

6	Create a Where clause defining which rows the database table will represent. Some tips for creating SQL Where clauses are provided. Click Next.

7	Choose the columns you want to see in the table.

8	Click Finish.

A database table appears in your project displaying the first 100 records satisfying your query.

To create a database table using the Add Database Table dialog

1	Make the Project window active.

2	From the Project menu, choose Add Database Table. The Add Database Table dialog appears.

3	Choose the "Create a new connection" option, then click the Connect button [image]. A connection dialog appears.

4	Connect ArcView to the database containing your data (connection details for SDE). A list of all the tables you can access in the database appears in the Tables list. When you click on a Table in the Tables list, its columns appear in the Columns list.

5	Double click a table in the Tables list to select it. The table's owner and the name of the table, separated by a dot, will appear in the From box. Alternatively, type the table's name directly into the From box.

6	Double click a column in the Columns list for one of the tables above to select it. The table's owner, the table and the name of the column, separated by dots, will appear in the Select box. Alternatively, you can type column names directly into the Select box.

7	Create a Where clause in the Where box if you selected more than one table or to choose the records you want to see. Create a join statement to join two tables by a common field of values. All selected tables must be joined together. To choose the records you want to see, create attribute criteria; some tips for creating SQL Where clauses are provided. Combine the join statements and attribute criteria by typing " and " between them.

8	In the Table Name box, type a name for the database table that will be created using this query.

9	If you are satisfied with your query, click Query; otherwise click Clear and start again. After clicking Query, your Select statement remains in the Add Database Table dialog so you can modify it and create a new database table if necessary.

A database table appears in your project displaying the first 100 records satisfying your query.

Using the functions provided by the relational database, you can write SQL statements that will concatenate columns of values, summarize the values in a table, calculate statistics, and sort the records by one or more columns.

Values in a database table cannot be edited directly in ArcView. To edit a copy of the data in one of these tables, you should export the table to a file on disk, and then add this file back into ArcView as a new table. You can now edit this new table. To save your changes in the database you need to write Avenue scripts.


Creating a table of database theme attributes (Database Access extension)
Creating a table of database theme attributes

This topic is for the Database Access extension.

The primary interface for database themes is the view document rather than the table document. A database theme always refers directly to its source data on the database server when the view draws or the theme is queried. Database themes do not have a local copy of the data that you can automatically see in a table document.

You can create a table of attributes for your database theme in two ways. You can export the attributes of selected features to a local dBASE file, or you can create a database table to see all of your theme's attributes.

To create a local table of database theme attributes

1	In the view's Table of Contents, click on the name of a database theme to make it active.

2	Select the features whose attributes you want to see.

3	From the Theme menu, choose Export to Table.

ArcView retrieves the attribute values for the selected features from the database, stores them in a dBASE file, then displays that file in a table document in ArcView. The exported table will have one field for each of the database theme’s attributes; it will not have a field representing the theme's spatial column.

To create a database table representing a database theme's attributes

1	In the view's Table of Contents, click on the name of a database theme to make it active.

2	Click the Open Theme Table button [image].

ArcView copies the database theme's query, then creates a database table from the new query representing the database theme's attributes. The database table displays attributes for all the shapes in the theme, not just the shapes you currently see in the view or the currently selected shapes. The database table will have one field for each of the database theme’s attributes and a field representing the theme's spatial column. This column doesn't contain shape data; the word "Shape" will appear for each record where a shape value exists.

Neither the exported table nor the database table will be connected to the database theme you created it from.

If you want to change which fields you can see in the attribute table or what those fields are called, you need to change either your theme's query before you create the attribute table or the table's properties afterwards. Changing the properties of the local table or the database table has no effect on the database theme it was created from.

Why would you create an attribute table that is not linked to the theme? You can join this table with other ArcView tables to find relationships between them, you can make charts from it to illustrate trends in your data, and you can manipulate this table's query to calculate or summarize the attribute values.

I want to create a local table of attributes, but the Export to Table option is dimmed out?

There are two reasons why the Export to Table option might be dimmed out. Either the database theme doesn’t have any attributes, or it doesn't have a selected set of features (shapes that are highlighted in yellow).


Database Access extension (description)
Database Access extension

This ArcView extension provides multi-user, client-server access to spatial and non-spatial data stored in relational databases. You can create database themes to see the spatial data and database tables to see the non-spatial data in ArcView.

To load the Database Access extension, make the ArcView Project window active and then choose Extensions from the File menu.


Changes to classes and requests for Database Access

With new releases of Database Access, changes are often made to the classes and requests. These changes may affect existing projects and scripts. Using the topics below, you can determine what changes have been made and the effect they have on existing applications.

If classes or requests have changed, there is some information indicating what change has occurred. For requests, if the change has been a name change, then the explanation shows the old name and the new name. If the change has been to the request's return object, you will see the key "return:" followed by the old return type and the new return type. If the change has been to the request's arguments, you will see the key "arguments:" followed by the old argument list and the new argument list.

Database Access v1.0 to v1.1

Database Themes v3.0a to Database Access v1.0


Database Access v1.0 to v1.1

All the changes to classes and requests from Database Access version 1.0 to version 1.1 are listed below.

Classes: New

DBTableDef

Requests: New

Class	Request
Connection	GetAllConnections
	GetAllTableNames
	GetErrorCode
	GetErrorMsg
	HasError
*DBTableDef	GetConnection
	GetFieldNames
	GetFields
	GetName
*QueryDef	GetFromTables
	GetSelectColumns
	GetWhereClause
	SetFromTables
	SetSelectColumns
	SetWhereClause
SDEConnection	MakeWithDialog
	ReturnConnectionType
SDESColumn	GetStorageType
	HasStorageType
	SetStorageType

*  The requests added to these abstract classes are inherited by all subclasses. Help for these requests appears only in the abstract classes.

Enumerations: Changed

Class	Enumeration	Change
SDEConnection	SDEErrorEnum	added elements for SDE v3.0.1:    SDEERR_INVALID_SPATIAL_COL_NAME    SDEERR_INVALID_DATABASE added elements for SDE v3.0.2:    SDEERR_SPATIAL_SQL_NOT_INSTALLED    SDEERR_NORM_DIM_INFO_NOT_FOUND    SDEERR_NORM_DIM_TAB_VALUE_NOT_FOUND    SDEERR_UNSUPPORTED_NORMALIZED_OPERATION    SDEERR_INVALID_REGISTERED_LAYER_OPTION
	SDERdbmsEnum	added elements for SDE v3.0.2:    SDE_RDBMS_IUS    SDE_RDBMS_DB2EXTENDER    SDE_RDBMS_ARCINFO
Enumerations: Removed

Class	Enumeration
+SDEConnection	SDETablePrivilegeEnum
Enumerations: New

Class	Enumeration
+Connection	TablePrivilegeEnum
SDESColumn	SDESColumnStorageTypeEnum

+  The TablePrivilege enumeration replaces the SDETablePrivilege enumeration. The TablePrivilege enumeration is inherited by the SDEConnection class; help appears only in the abstract class.


Database Themes v3.0a to Database Access v1.0

All the changes to classes and requests from Database Themes version 3.0a to Database Access version 1.1 are listed below.

Classes: Changed

Database Access	Database Themes	Change
AddSDEDBThemeDialog	AddDBThemeDialog	name change
DBTheme	DBTheme	properties changed
DBTQueryWin	DBTQueryWin	dialogs that appear
SDELog	SDELog	temporary by default
Classes: Removed

Database Themes

SDEDataset

SDEFeature

SDELayer

Classes: New

Database Access

DBTable

Connection

QueryDef

RecordSet

SColumn

SDEConnection

SDEQueryDef

SDERecordSet

SDESColumn

SDESColumn

SDETable

Requests: Changed

Class	Database Access Request	Database Themes Request	Change
DBTheme	BlinkRecord	BlinkSDEFeature	renamed from BlinkSDEFeature to BlinkRecord
	Make	Make	arguments: (anSDELayer, aClass) changed to (aQueryDef, anSColumn, aClass)
SDELog	Copy	Copy	arguments: (aSourceLogName, anOutputLogName) changed to (anSDEConnection, aSourceLogName, anOutputLogName)
	Delete	Delete	arguments: (aLogName) changed to (anSDEConnection, aLogName)
	Make	Make	arguments: (aLogName, theOpenMode) changed to (anSDEConnection, aLogName, aTableName, theOpenMode)
	MakeLogName	MakeLogName	arguments: () changed to (anSDEConnection)
Requests: Removed

Class	Database Themes Request
DBTheme	GetAttributesFirst
	GetDefinition
	GetSDELayer
	SetAttributesFirst
	SetDefinition
	UpdateAttributes
SDEDataset	AddLayer
	AdjustPriority
	ClearSpatialFilter
	CommitTransaction
	DeleteLayer
	FindActiveDataset
	FreeAllLocks
	GetAllLayerNames
	GetError
	GetErrorMsg
	GetMaxLayers
	GetMaxPoints
	GetName
	GetPriority
	GetResolution
	GetUnits
	IsConnected
	IsUsingServerExtent
	Make
	RollbackTransaction
	SetBuffers
	SetConnected
	SetSpatialFilter
	SetSpatialFilterByID
	StartTransaction
	UseServerExtent
SDEFeature	AddToLog
	AsSDEFeature
	AsShape
	Buffer
	ClipPoints
	ClipToArc
	ClipToPoly
	CopyGeometry
	Count
	GetID
	GetSDELayer
	GetType
	HasAttributes
	HasRelation
	Intersects
	Make
	MakeEmpty
	MakeList
	Merge
	MoveGeometry
	Overlay
	Remove
	Resize
	ReturnArea
	ReturnCenter
	ReturnCommonLength
	ReturnConvexHull
	ReturnExtent
	ReturnLength
	ReturnValue
	SetID
	SetSDELayer
	SetValue
	Simplify
	Verify
SDELog	Query
	QueryNext
	ReturnID
	ReturnNextID
	SetAutoLog
SDELayer	AddFeaturesByLinearDissolve
	AddFeaturesByLinearDissolveLog
	AddFeaturesByPolygonDissolve
	AddFeaturesByPolygonDissolveLog
	AddFeaturesByPolygonize
	AddFeaturesByPolygonizeLog
	AddFields
	AddToLog
	BuildIslands
	CancelQuery
	CancelSearch
	Clean
	ConnectLines
	CreateAttributeTable
	CreateIndex
	DeleteAttributeTable
	DeleteIndex
	DeleteSDEFeature
	DeleteSDEFeatureByLog
	FindField
	FindToLog
	GetCreator
	GetFields
	GetGridSizes
	GetID
	GetName
	GetSDEDataset
	GetSecurityClass
	HasType
	IsWritable
	LockArea
	LockLayer
	Make
	MakeFromLayerNumber
	MakeNew
	ModifyAreaLock
	Query
	QueryByDateRange
	QueryByIDRange
	QueryNext
	QuerySDEFeature
	ReturnExtent
	ReturnID
	ReturnIDByDateRange
	ReturnIDByIDRange
	ReturnNextID
	ReturnSDEFeatureExtent
	ReturnSDEFeatureValue
	ReturnStatistics
	ReturnUniqueValues
	SearchByID
	SearchBySDEFeature
	SearchByShape
	SearchForID
	SearchForNextID
	SearchForNextSDEFeature
	SearchForSDEFeature
	SetAutoSearch
	StoreSDEFeature
	UnLock
Requests: New

Class	Database Access Request
DBTheme	Clone
	GetQueryDef
	GetSColumn
	GetSrcName
	SetQueryDef
	UseConnectionFrom
DBTable	CanAddFields
	CanAddRecord
	CanRemoveFields
	CanRemoveRecords
	Deactivate
	EditProperties
	Export
	FindField
	GetFields
	GetNext
	GetNRows
	GetNumRecords
	GetQueryDef
	GetVisibleVTab
	GetVTab
	HasError
	IsEditable
	IsEOF
	JoinToVTab
	Make
	MakeWithGUI
	Refresh
	ReturnUserField
	ReturnUserRow
	SetNRows
	ShowAddDialog
	ShowPrefDialog
Connection	=
	BeginTrans
	Close
	CommitTrans
	GetConnectionString
	GetDatabaseName
	GetUserName
	IsConnected
	Open
	Rollback
	SavePassword
QueryDef	Clone
	Execute
	GetConnection
	GetSQL
	Make
	OpenRecordSet
	SetSQL
RecordSet	AddNew
	CanUpdate
	Close
	Delete
	Edit
	EndUpdate
	FindField
	GetFields
	GetRow
	GetValue
	GetValueByIndex
	IsBeingEdited
	IsEOF
	Next
	SetRow
	SetValue
	SetValueByIndex
	Update
SColumn	Clone
	FreeLock
	GetConnection
	GetSColumnName
	GetTableName
	LockAll
	LockArea
	Make
	ReturnExtent
	ReturnStatistics
SDEConnection	BeginTrans
	Close
	CommitContinue
	CommitTrans
	CreateTable
	DeleteTable
	FreeAllLocks
	GetAllConnections
	GetAllDatabaseNames
	GetAllSColumnNames
	GetAllTableNames
	GetDBErrorCode
	GetDBErrorMsg
	GetErrorCode
	GetErrorMsg
	GetRDBMS
	GetVersion
	HasError
	Make
	Open
	Rollback
	SetAutoCommitFreq
	SetDatabase
	TruncateTable
SDEQueryDef	Execute
	GetFromTables
	GetQueryOrder
	GetSelectColumns
	GetSFilters
	GetSQL
	GetWhereClause
	Make
	ReturnStatistics
	SetFromTables
	SetQueryOrder
	SetSelectColumns
	SetSFilters
	SetSrcDest
	SetWhereClause
SDERecordSet	AddNew
	Close
	Delete
	Edit
	EndUpdate
	Next
	SetRow
	SetValue
	SetValueByIndex
	Update
SDESColumn	FreeLock
	GetCreationDate
	GetGridSizes
	GetSColumnName
	GetTableName
	HasAnnotation
	HasType
	HasZ
	IsMeasured
	LockAll
	LockArea
	Make
	MakeNew
	ReturnExtent
	ReturnStatistics
	SetAveragePoints
	SetCoordRefByRect
	SetCreationKeyword
	SetGridSizes
	SetMeasured
	SetNumShapes
	SetSColumnName
	SetZ
SDESFilter	Make
	SetTest
SDETable	CreateIndex
	CreateSColumn
	DeleteIndex
	DeleteSColumn
	GetConnection
	GetFieldNames
	GetFields
	GetName
	Make
	MakeNew
	OpenRecordSet
	SetCreationKeyword
	TruncateSColumn
SDELog	AddFeature
	AddFeatures
	GetTableName
Requests: Unchanged

Class	Request
AddSDEDBTheme Dialog	Show
DBTheme	BuildQuery
	CanEdit
	CanExportToFTab
	CanHotLink
	CanLabel
	CanProject
	CanReturnClassCounts
	CanSelect
	ClearSelection
	EditLegend
	ExportToFTab
	ExportToVTab
	FindByPoint
	GetFixedSizeText
	GetHotField
	GetHotScriptName
	GetLabelField
	GetLabelTextSym
	GetLastSelection
	GetSelectedExtent
	GetSelection
	GetShapeClass
	GetTextPositioner
	HasAttributes
	Identify
	RememberSelection
	ReturnClassCounts
	ReturnDefaultLegend
	ReturnExtent
	ReturnLabel
	ReturnSymbolSize
	SelectByAttributes
	SelectByPoint
	SelectByPolygon
	SelectByRect
	SelectByShapes
	SelectByTheme
	SetDefaultLegend
	SetFixedSizeText
	SetHotField
	SetHotScriptName
	SetLabelField
	SetLabelTextSym
	SetName
	SetSelection
	UpdateLegend
DBTQueryWin	Close
	Make
	Open
	ReturnQuery
SDELog	ChangeType
	Close
	Combine
	Copy
	Count
	GetName
	ReturnExtent
	Sort


Database Access Avenue class hierarchy

Obj

    Theme

        DBTheme

    Window

        DBTQueryWin

    ModalDialog

        AddSDEDBThemeDialog

    Doc

        Table

            DBTable

    Connection

        SDEConnection

    QueryDef

        SDEQueryDef

    RecordSet

        SDERecordSet

    DBTableDef

        SDETable

    SColumn

        SDESColumn

    SDESFilter

    SDELog


Functional overview of Database Access classes

Object Model diagram

Loading the Database Access extension adds several Avenue classes.

DBTheme

The DBTheme class, a subclass of Theme, lets you create database themes. A database theme has a spatial query that selects rows and columns from a relational database. One of the selected columns must be a spatial column, containing geographic shapes. When you draw a database theme in a view, it displays the shape in the spatial column for each row retrieved by the query.

DBTQueryWin

The DBTQueryWin class defines the SQL Query Builder dialog, which lets you select the shapes in a database theme based on their attribute values. The SQL Query Builder is displayed when you click the Query Builder button in the View user interface. From Avenue you can display this dialog using the DBTheme.BuildQuery request.

AddSDEDBThemeDialog

The AddSDEDBThemeDialog class lets you display the SQL Query Wizard dialog for adding database themes to a view with the Show request; this is the dialog displayed when you choose Add Database Theme from the View menu in the View user interface. From the dialog, you connect to an SDE server, then choose the data you want to see in ArcView.

DBTable

The DBTable class, a subclass of Table, lets you create database tables. A database table has a query, generally an SQL Select statement, that lets you select rows and columns from a relational database. If the query selects a spatial column in SDE, the database table won't contain shape values. Because queries against a relational database can select a large number of records, database tables only display some of the records retrieved by the query.

Connection

Connection is the abstract class which defines general properties and methods of working with Database Access connections. When you create a connection between your ArcView session and a relational database server, the you will use one of the extension's database interfaces (e.g., SDE). Each interface has its own way of making a connection to the database. You must have an active connection before you can create other Database Access objects and retrieve data.

SDEConnection

An SDEConnection connects ArcView to an SDE database server. You would use an SDEConnection to perform error checking in your Avenue scripts. The SDEConnection class provides requests to add and delete tables in the database, and provides additional methods for managing transactions against the database.

QueryDef

QueryDef is the abstract class which defines general properties and methods of querying a relational database. The general method of creating a query is by writing an SQL statement. If the query selects rows, you can retrieve them by opening a RecordSet; otherwise, you can send the query to the database using the Execute request. You create DBThemes and DBTables from QueryDef objects.

SDEQueryDef

An SDEQueryDef lets you create spatial queries in addition to SQL queries. Spatial queries can retrieve shapes from the database and can use spatial criteria to select rows. An SDE spatial query is similar to an SQL Select statement, but has a different structure.

RecordSet

RecordSet is the abstract class which defines general properties and methods of working with the rows retrieved by a query. As you loop through the rows in a RecordSet, you can work with the current row's values. The general methods define how to edit the source data using a RecordSet if editing is permitted by the database interface.

SDERecordSet

An SDERecordSet represents the set of rows and columns selected by its query. Its methods lets you edit the source data if the query is a spatial query, and if the query selects rows from one table only.

DBTableDef

The DBTableDef class represents a table in the database. Its methods let you open a RecordSet representing all columns in the table and all rows selected by the OpenRecordSet request's Where clause.

SDETable

The SDETable class represents a table in an SDE database. Its methods let you create new tables and spatially-enable existing tables in the database, or create indexes in the database for a table's columns. You can open an SDERecordSet from an SDETable.

SColumn

SColumn is the abstract class which defines general properties and methods of working with a spatial column. An SColumn object is required to create a DBTheme. The general methods let you spatially lock records in the spatial column's table if this function is permitted by the database interface.

SDESColumn

An SDESColumn class represents a spatial column in an SDE database. The SDESColumn's methods let you create new spatial columns in the database, including measured and 3D spatial columns.

SDESFilter

The SDESFilter class lets you define spatial criteria that you can apply to an SDEQueryDef. The spatial filter has a shape and one of several search methods. Each search method finds all the rows whose shapes have a specific spatial relationship with the spatial filter's shape.

SDELog

The SDELog class represents an SDE logfile, which records a set of shapes in an SDE spatial column. You can place in a logfile all the shapes selected by an SDEQueryDef by assigning a destination to the query. You can retrieve rows whose shapes are in a logfile by setting a query's source to an SDELog. You change a query's source and destination using the SDEQueryDef.SetSrcDest request. DBThemes use SDELogs to record the selected set of features.


Database Access extension (Avenue object model diagram)
The Database Access extension


Joining a database table to an ArcView table (Database Access extension)
Joining a database table to an ArcView table

This topic is for the Database Access extension.

You can join a database table to an ArcView table (e.g., a shapefile theme's attribute table), if they share a common fields of values. All of the rows selected by the database table's query can be joined to the ArcView table.

To join a database table to an ArcView table

1	Open the database table; if the table's window is already open, make it active.

2	Click on the common field's name in the database table to make the field active.

3	Open the ArcView table; if the table's window is already open, make it active.

4	Click on the common field's name in the ArcView table to make the field active.

5	Click the Join button [image].

The contents of the ArcView table changes to include the joined attributes from the database table while the database table remains open and unchanged.

The joined attributes are not permanently part of the ArcView table. ArcView gets the joined attributes from the database using a join query taken from the database table's query at the time of the join. If the values in the database change you can see those changes by refreshing the joined table. When you open a saved project, ArcView will recreate the join and retrieve the appropriate values into the local table.

The join query is not linked to the database table's query. After joining a database table and a local table, if you change the database table's query in a way that affects which rows are joined, the rows joined to the local table won't change when you refresh its values. To update which records are joined, you need to remove the join from the local table then join the tables again following the steps above.


Loading the Database Access extension

ArcView's Database Access extension lets you create database themes to see spatial data and database tables to see non-spatial data that is stored in a relational database.

Before you can start working with the Database Access extension, you need to find out some information (details for SDE) so you can connect to the database. After the preliminaries are taken care of, you can load the Database Access extension and start using relational database data in ArcView.

To load the Database Access extension

1	Activate the ArcView Project window.

2	From the File menu, choose Extensions. The Extensions dialog appears.

3	Check the box next to Database Access in the Extensions dialog. If you want the Database Access extension to load automatically every time you start ArcView, press the Make Default button after checking Database Access. The next time you start ArcView the Database Access extension will automatically load.

4	Click OK in the Extensions dialog.

Loading the extension adds some new menu choices to the Project, View, and Table user interfaces. These choices let you create and work with database themes and database tables.


Repairing a database table's connection (Database Access extension)
Repairing a database table's connection

This topic is for the Database Access extension.

When you open a project containing database tables you need to reestablish the database connections stored in your project by typing your user name and password into the Login dialog.

If you type your password in wrong, or if the database server isn't running, ArcView won't be able to reestablish your database connection. ArcView will still open your project, but you won't see values in your database tables.

You can repair a database table's connection using the Database Table Properties dialog.

To repair a database table's connection

1	If the table's window is already open, make it active. Otherwise, make the project window active then open the database table.

2	From the Table menu, choose Properties. The Database Table Property dialog appears.

3	Choose the Create a new connection option.

4	Click the Connect button [image]. A connection dialog appears.

5	Connect ArcView to the database containing your data (connection details for SDE). A list of all the tables you can access in the database appears in the Tables list.

6	Click OK in the Database Table Property dialog. The rows appear that were visible in the database table when the project was saved.

7	If another database table requires the connection you created above, open the table or make its window active. Otherwise, skip the rest of these steps.

8	Choose Properties from the Table menu.

9	Choose the Use an existing connection option.

10	From the drop-down list, choose the connection you created above.

11	Click OK.

12	Repeat steps 7-10 until all database tables requiring this connection have been repaired.

Database themes and database tables can share the same connection. After repairing a database table's connection you may be able to repair a database theme using the same connection.


Repairing a database theme's connection (Database Access extension)
Repairing a database theme's connection

This topic is for the Database Access extension.

When you open a project containing database themes you need to reestablish the database connections stored in your project by typing your user name and password into the Login dialog.

If you type your password in wrong, or if the database server isn't running, ArcView won't be able to reestablish your connection. ArcView will still open your project, but you won't see any features in your database themes. ArcView will display an error message for each database theme that is turned on in the view that doesn't have an active connection.

You can repair a database theme's connection using the Theme Properties dialog.

To repair a database theme's connection

1	Make the database theme active by clicking on it in the view's Table of Contents.

2	Click the Theme Properties button [image]. The Theme Properties dialog appears.

3	Choose the Create a new connection option.

4	Click the Connect button [image]. A connection dialog appears.

5	Connect ArcView to the database containing the data (connection details for SDE).

6	Click OK in the Theme Properties dialog. The view redraws displaying the database theme's shapes if the theme is turned on in the view.

7	If another database theme requires the connection you created above, make it active. Otherwise, skip the rest of these steps.

8	Click the Theme Properties button [image].

9	Choose the Use an existing connection option.

10	From the drop-down list, choose the connection you created above.

11	Click OK. The view redraws displaying the database theme's shapes if the theme is turned on in the view.

12	Repeat steps 7-10 until all database themes requiring this connection have been repaired.

Database themes and database tables can share the same connection. After repairing a database theme's connection you may be able to repair a database table using the same connection.


SDE data structure and feature types (Database Access extension)
SDE data structure and feature types

This topic is for the Database Access extension.

SDE data structure

SDE stores your spatial data in relational database tables. Your database has a collection of tables, some of which may contain spatial data and others which do not. Spatial data is stored in a spatial column of a table in the same way that numeric data is stored in a numeric column. You can add a spatial column to any table in the database, however a table can only have one spatial column when you are using SDE 3.0. A table that has a spatial column is often referred to as a business table in the SDE documentation.

A collection of geographic features that share a common thematic subject should all be stored together in one business table. There are no limitations to the kinds of features one table can store, only that all features in a table must lie within a defined range of coordinates. For example, you might have a business table containing all the hydrographic features in the United States including lakes (polygon), rivers (line), aqueducts (line), dams (point), and annotation for these features (anno). Each record in the business table is not required to have a value in the spatial column; records can also contain SDE's null feature type.

The business table could contain attributes such as the name of the feature, its depth (if appropriate), the date it was created (if appropriate) and the type of feature it is. Your database can have several other tables in the database containing attributes about the features in a business table, including tables describing the levels of pollutants or maintenance work, and lookup tables describing  the features. These attribute tables can be joined to the business table in a database theme's query.

ArcView and SDE feature types

Even though a business table can contain many classes of features, a database theme in ArcView can represent only one of them: polygon, line, point, or anno.

When you create database themes using the SQL Query Wizard, you choose the spatial column you want to see from a list of the spatial columns you can access. In that list, if a spatial column has feature types from more than one ArcView class it will have a folder icon. Click on the folder to choose the feature class your theme will represent. If you select the name of a spatial column that has a folder icon without choosing a feature class, ArcView makes a theme for the default feature class. ArcView decides which class is the default by checking the feature types a spatial column can have; ArcView looks for polygons, then for lines, then for points, and, lastly, for anno.

A spatial column that can have features from many classes may presently contain only one class of features. For example, you will be able to create both polygon and line themes from a spatial column that can have both polygons and lines even if it only contains lines at that time. If you create a polygon theme, it will appear empty when you draw it. Another person might be adding polygons, but hasn't yet committed the new features to the database.

Each SDE feature type has specific rules about the geometry it represents. ArcView shapes do not follow the same rules, and therefore are not always valid SDE features. You may not be able to store some ArcView shapes in SDE.

Points

Points represent features found at discrete locations, such as telephone poles, wells, and mountain peaks. MultiPoints represent a group of related point features.

ArcView stores point and multipoint features in separate shapefiles, not together. You can create an SDE spatial column that can contain point features by defining the spatial column with the Point feature type; to store multipoint features, the spatial column must also be defined with the MultiPart feature type.

When you create a point database theme, you will see both point and multipoint features. When you export features from a point database theme, the results depend on how the spatial column is defined. If the spatial column is defined with the point feature type only, all features will be stored in a point shapefile. If the spatial column is defined with the multipart feature type as well, any single-point features will be converted to multipoint features, and all features will be stored in a multipoint shapefile.

Lines

Lines (Arcs) represent linear features such as streams, streets, and contours.

SDE defines two kinds of line features. A Linear line feature is allowed to cross itself, while a SimpleLinear line feature is not allowed to cross itself. Both linear and simplelinear features are single-part features that can have more than two points. ArcView defines two different kinds of linear shapes. A Line shape is a two-point line feature, while a PolyLine shape is a set of one or more line segments, each of which can have two or more points. Only polyline shapes can be stored in shapefiles.

To store ArcView line features in SDE, the spatial column must be defined with the linear feature type; to store polyline features, the spatial column must also be defined with the MultiPart feature type. There are no rules in ArcView about polylines crossing themselves, therefore you can't create simplelinear features or store them in a spatial column using ArcView.

When you create a line database theme you will see both linear and simplelinear features. When you export features from a line database theme, the features will be converted to polyline features and stored in a polyline shapefile.

Polygons

Polygons represent areas enclosed by specific boundaries, such as countries, states, land parcels, and soil types.

An ArcView Polygon shapefile consists of polygons, where one polygon can have many parts. To store ArcView polygons in SDE, the spatial column must be defined with the Area feature type. To store polygons that have more than one part, the spatial column must also be defined with the MultiPart feature type.

When you create a polygon database theme you will see single and multipart area features. When you export features from a polygon database theme, the features will be stored in a polygon shapefile.

Annotation

Annotation is text stored in a coverage or in a spatial column to label the features it contains. Annotation stores text strings, text characteristics and a shape (either a point or a line) used to position the text string.

ArcView can't store annotation in shapefiles, and you can't create annotation for SDE using ArcView. You can't create a spatial column that can contain annotation using ArcView.

If an SDE spatial column exists that already contains annotation you can see it by creating an anno database theme. You can't export annotation from an anno database theme using ArcView.

Measure and Z values

All SDE features can have measure and z values. That means that each vertex in a shape can have an additional value, either a measure value or a z value. Measures are useful with linear features to represent accident locations, or different surfaces along a road. You can use Z values to represent a feature in 3D space.

You can create measured and 3D shapefiles using the new -M and -Z shape classes, respectively. For example, you can create a PolyLineM shapefile to store measured road lines, and create a PointZ shapefile to store well points.

To create a spatial column whose shapes can have measure values you need to use the SetMeasured request on the new spatial column object before adding it to the database. Similarly, for shapes to have 3 dimensions you need to use the SetZ request. You can store measured and 3D shapes in SDE if the spatial column has been defined correctly.

A database theme created from a measured spatial column will look the same as if the shapes didn't have measures. Similarly, a database theme created from a 3D spatial column will draw in 2 dimensions. When you export shapes from these themes you will get -M and -Z shapefiles, respectively. You can't add a database theme directly to a Scene document to see it in 3 dimensions; export the features you want to see, then add the -Z shapefile to a Scene document.


Selecting database theme features by their attributes

This topic is for the Database Access extension.

To select features in a database theme by their attribute values you need to build an SQL Where clause.

A simple SQL Where clause needs a Field, an Operator, and a Value. You can build a simple Where clause by double-clicking on a Field and an Operator with the mouse and typing a Value into the query text box.

To build a simple SQL Where clause

1	Click on a database theme in the view's Table of Contents to make it active.

2	Click the Query Builder button [image].

3	Double-click a field from the Fields list, click an Operator, and then type a Value into the Query text box. For example, double-click Population in the Fields list, click the > Operator, and then type the number 10000000 into the Query text box to select all cities whose population is greater than ten million.

4	Click New Set to make a new selection of features. The selected features are draw in yellow in the view.

This query might look like:

 ( population > 10000000 )

Alternatively you can type the whole Where clause in yourself; if you do, remember that field names are not enclosed in square brackets ([ ]) like they are for other themes, and you can't use field aliases instead of proper field names.

The Where clause MUST follow the SQL syntax rules of your relational database. While the principles are the same, each database uses a different SQL syntax. You should always refer to documentation for your relational database to make sure your statements are correct. Some tips are provided to get you started.

How attribute queries on database themes work

Relational databases use SQL, Structured Query Language, to access and manage data. When you create a database theme you are really creating a query like an SQL Select statement that chooses the shapes and attributes the theme represents. The Where clause part of the query is similar to the query strings you build for feature themes; it chooses which shapes the theme represents using attribute criteria.

When you select a database theme's features following the steps above, you are really creating a new selection query that combines the theme's query with the attribute criteria from the SQL Query Builder. Selecting features using the SQL Query Builder doesn't change the theme's query.

Suppose the database has a spatial column containing points for many cities in the world. You can create a database theme representing only the cities in Canada by creating a query with the Where clause, "Country = 'Canada' ". Now, if you want to select the cities whose population is greater than 100,000, you would open the SQL Query Builder and create the Where clause, "Population > 100000". The selection query will find the cities satisfying the attribute criteria, "Country = 'Canada' and Population > 100000", then draw those cities in yellow.


Setting up your machine for SDE (Database Access extension)
Setting up your machine for SDE

This topic is for the Database Access extension.

Before you can create database themes and database tables, find out if you have to set up your machine. After following the instructions below, you should be able to connect to SDE to create database themes and database tables.

To set up your machine for SDE you need to edit its Hosts and Services files. You can do this in any text editor. (Warning for PCs: Notepad is known to sometimes store hidden characters in text files. If you have trouble connecting, check these files in the DOS editor.)

File Locations

Windows NT: <winnt root>\system32\drivers\etc

Windows 95: <win95 root>

UNIX: /etc

Services

This file must contain the SDE database instance name and its service number. For each instance that you want to access, add the following line to the Services file:

<instance name>	<service number>/tcp

For example, the default instance name is esri_sde and the default service number is 5150:

esri_sde	5150/tcp

Machines can be sensitive to the order in which entries appear in the services file. If you have trouble connecting, try changing the order in which the SDE database instances are listed.

Hosts

For each SDE server that you want to access, add the following line to the Hosts file:

<host name> 	<IP address>

For example,

stout	198.102.46.225

After editing the Hosts file, make sure you can 'ping' the SDE server. You can ping a machine by typing "ping <host name>" at your prompt. You will be able to connect if you get a reply from the server. If you don't get a reply, then you likely spelled the server's name wrong or typed the IP address incorrectly in the Hosts file.

PCs only: If your PC doesn't have a Hosts file you must create a new one. A sample Hosts file is often provided, called Hosts.sam. When saving the new Hosts file, some text editors will automatically attach a .txt extension to it; rename the file to eliminate the .txt extension. If you don't have a Ping utility to check communication with the server, you need to open a DOS window.

Yellow Pages

Networks often have 'yellow pages' Hosts and Services files. Depending on how your network is set up, your machine may use the yellow pages files in addition to the local Hosts and Services files. Suppose you can't connect to SDE unless you edit the local Services file, but you can connect without editing the local Hosts file. This happens because your machine knows to check the yellow pages Hosts file, which has an entry for that server; however, the database instance wasn't in the yellow pages Services file, so you had to edit the local Services file.

We recommend that you do not rely on the yellow pages files. You should always edit your local Hosts and Services files to contain the correct information. Yellow pages files are often incomplete. There are usually different yellow pages files for different sub-networks in a large network environment; an SDE server may appear in one yellow pages Hosts file but not in another. To change the yellow pages files you need to contact your system administrator.

For HP users only

HP users have an extra step. These computers use a configuration file to determine the order in which they search for local or network Services files; they use whichever Services file is found first. If you've made changes to the local Services file, you need to make sure that file will be used; this may require editing the configuration file.

The default configuration file for HPs is /usr/newconfig/etc/nsswitch.conf. The line in question is:

Services:	nis	files

where "nis" stands for "network information services". This configuration line tells the computer to look for the network's Services file first. Most networks have Services files; therefore, with this configuration, the changes you've made to the local Services file will be ignored. "files" refers to the local system files.

Before looking for the default configuration file, HPs look for a custom configuration file, /etc/nsswitch.conf. If this file exists, it will be used instead of the default configuration file. If this file doesn't exist, and if you want the local Services file to be used, copy /usr/newconfig/etc/nsswitch.conf to /etc/nsswitch.conf.

In the /etc/nsswitch.conf file, change the order of the Services entry appears like this:

Services:	files	nis

Now your computer will use the local Services file, which contains the SDE instance information required for connecting to an SDE database.

Contact your system administrator if you need help setting up your computer.


Tips for creating SQL Where clauses

This topic is for the Database Access extension.

Relational databases use SQL, Structured Query Language, to access and manage data. The Where clause MUST follow the SQL syntax rules of your relational database. While the principles are the same, each database uses a different SQL syntax. You should always refer to documentation for your relational database to make sure your statements are correct.

SQL Where clauses are used in ArcView to define the rows a database table represents, to define the features a database theme represents, or to select features in a database theme by their attribute values.

The examples provided below are valid only for Oracle databases.

 A simple SQL Where clause needs only the column name, an operator, and a value. For example:

 ( population > 10000000 )

Column names are not enclosed in square brackets ([ ]) like they are in ArcView queries. The column name must be the real name of the column in the database; you can't use field aliases instead of proper field names.

 String values such as names are always quoted in queries with single quotation marks. Strings are case sensitive, so you can select Canada with:

 ( country_name = 'Canada' )

but not:

 ( country_name = 'canada' )

if the country names are capitalized.

If you type field names into your Where clause, the results produced from the field names Country_Name, country_name, and COUNTRY_NAME depend on whether or not the relational database is case sensitive for field names. For example, Oracle isn't case-sensitive for field names, but Sybase is; Oracle's results for all three field name versions will be the same, but Sybase's results won't.

 To select all the cities with names starting with the capital letters S to Z, you could use:

 ( city_name > 'S' )

 Using wildcards in queries is different for a Where clause than for an ArcView query string. To use wildcards you need to use the LIKE operator. For example, Oracle lets you build a wildcard statement such as:

 ( country_name LIKE 'United%' )

where % is a multiple character wildcard. (Other relational databases use different wildcard characters.) In a world dataset this query would select features where the countries are United States, United Kingdom, or United Arab Emirates. If you use an equal sign instead of LIKE, each character in the string is taken literally. The Where clause:

 ( country_name = 'United%' )

is literally looking for the country United%, and will select no features in a world dataset.

Other SQL operators can be useful when querying your database, though not all are appropriate for selecting features from an SDE layer. You can use the BETWEEN operator to select cities whose population falls between 1,000,000 and 15,000,000:

 ( population BETWEEN 1000000 AND 15000000 )

You can use the IS NULL operator to identify features that have null attribute values. For example, to find all cities whose 1996 population hasn't been entered, you could use:

 ( population96 IS NULL )

For more information about SQL operators see the SQL reference for your relational database.

 You can't use Avenue statements in an SQL where clause because your relational database does not know how to interpret them. The string you build in the query builder is not processed within ArcView, it is sent directly to the relational database.

 To specify date values in your SQL where clause you need to convert number or character values into date values with the TO_DATE function. For example, to select all the sale dates before June 28, 1996 in Oracle, you could use:

( sale_date < TO_DATE('28-06-1996', 'DD-MM-YYYY') )

When your source data is stored in Oracle, if you provide a character string in the standard date format Oracle will convert the string to a date value for you. For example, if you want to find all the land parcels that were sold between May 15th and June 30th, you could use:

( sale_date BETWEEN '15_MAY_96' AND '30_JUN_96' )

When you are querying a date field you can select all dates before now using the SYSDATE function; it returns the current date and time. To find all the features that have already been edited, you could use the where clause:

 ( edit_date < SYSDATE )

Oracle includes time information with its date values. When you specify a date value in your where clause without a time, the time defaults to 12:00:00a.m. If you want to compare dates keep in mind that two date values stored on the same day can have different times; you can use the ROUND and TRUNC functions to compensate. For example, to find all the features that were edited on the day June 28, 1996, you could use the where clause:

 ( TRUNC(edit_date) = TO_DATE('28-06-1996', 'DD-MM-YYYY') )

By default the TRUNC function truncates the date value stored in the edit_date field to the day. For more information about SQL functions see the SQL reference for your relational database.

 Complex queries can be built by combining expressions together with the And and Or operators. For example, to select all the houses that have more than 1,500 square feet and a garage for three or more cars, use the query:

 ( area > 1500) and ( garage >= 3 )

Use the Not operator to exclude. For example, to select all the New England states except Maine, use the query:

 ( sub_region = 'N Eng') and ( not ( state_name = 'Maine') )

Queries can compare the values of two fields. For example to find all the counties with a declining population, use the query:

 ( pop1996 < pop1990 )

 Calculations can be included in queries. For example, to find the counties with a population density of less or equal to 25 people per square mile, you could use the query:

 ( population / area <= 25 )

Normally your relational database evaluates queries from left to right, but expressions that you enclose in parentheses are evaluated first. For example, the query:

 ( netvalue > area * price + tax )

will be evaluated differently from:

 ( netvalue > area * ( price + tax ) )

 By default, Where clauses for selecting database theme features are contained within parentheses, but the parentheses may not be required, depending on the complexity of your query. ArcView query strings must be enclosed in parentheses to take advantage of field indexes, but relational databases always uses field indexes if they exist.


Using SDE data in ArcView

The Database Access extension provides multi-user, client-server access to data managed by ESRI's Spatial Database Engine (SDE) version 3.x. This extension turns ArcView into a client for browsing, viewing, and querying SDE data. A theme using an SDE spatial column as its data source is known as a database theme. A table whose data source is tables from an SDE database is known as a database table.

Using Avenue, you can develop SDE client applications that go beyond the capabilities of the user interface provided with this extension. You can create scripts to perform advanced queries and spatial operations on database themes, such as applying an area of interest, and analyzing measured and three-dimensional shapes stored in SDE. You can also create custom applications for updating the attributes and geometry of SDE spatial columns. Data can be retrieved from SDE, updated, and then written back to the SDE dataset.

You can find the help topics for the Database Access extension under the Extensions book in the contents page. All help topics for this extension have the words "Database Access extension" in the title or in the first line of the topic.


What is a database table (Database Access extension)
What is a database table?

This topic is for the Database Access extension.

Using ArcView's Database Access extension, you can create tables in your project representing data that is stored in a relational database; these are called database tables.

With one of the extension's Database Interfaces (e.g., SDE) you can connect to a relational database, such as Oracle or Sybase, and create an SQL Select statement to choose the records you want to work with. ArcView creates a database table to represent the records selected by your query.

Your SQL statement can calculate values using the source data, concatenate character fields together, and summarize the records in a table. If your shapes are stored in a shapefile and you have attributes of those shapes stored in a relational database, you can use database tables to join the database attributes to the shapefile.

Before you can start working with the Database Access extension, you need to find out some information (details for SDE) so you can connect to the database. After the preliminaries are taken care of, you can load the Database Access extension then start using relational database data in ArcView.

How database tables work

A database table represents all the records selected by your query. However, ArcView doesn't know how many records will be retrieved by the Select statement; your query could retrieve over a million records. Rather than getting all the selected records at once, ArcView gets the first 100 records and displays them in the database table.

If you want to see more then the first 100 records, you can add the next 100 records to the database table by choosing Get More Rows from the Table menu. Afterwards, your table displays the first 200 records retrieved by the table's query. You can continue adding records 100 at a time until there are no more records to add. You can change the number of records retrieved each time from 100, the default, to any number between 1 and 3500.

The records you see in the database table are stored on your machine in a temporary file that is deleted when the database connection is broken, for example, when you close your project. When you save a project containing database tables, ArcView stores the SQL query used to create the table not the values that were visible in the table. ArcView recreates the temporary file and the database table when you open the project again.

Working with database tables in ArcView

You can work with database tables almost the same as other tables in ArcView. Because a database table’s source data resides in a relational database, some tasks work differently than for other tables. The following list outlines these differences:

 Adding a database table to your project is a different procedure where you have to make a network connection between ArcView and the database. You start this procedure with the Add Database Table choice from the Project menu (not by clicking the Add button in the Project window or by choosing SQL Connect from the Project menu). A database table’s properties are different from the properties of other tables in ArcView.

 Database tables are a read-only look at the data in your database; however, you can edit the data in your database using Avenue. To see the changes, refresh your database table by choosing Refresh from the Table menu.

 You can join a database table to a regular ArcView table; however, you can't join an ArcView table to a database table. The difference lies in which table is active when they are joined. You can't link a database table to an ArcView table.

 Unlike tables created using SQL Connect, you can redefine your table's SQL query. Also, the query you used to create the table will be remembered by the Add Database Table dialog. This will help you refine your SQL Select statement to get exactly what you want.

 The Query Builder button is dimmed out [image]. Database tables don't have selections because the database table itself is a selected set of records. If you want to select a subset of the features in your database table you need to redefine the table's query, or create a new table with an SQL query modified from the original table.

 A database table's properties don’t include field visibility and field aliases like other tables in ArcView. You control which fields are visible by adding or removing columns from the Select clause when you redefine the table's SQL query. You change the way a field's name appears in ArcView by assigning it an alias in the Select clause of the SQL query if this functionality is supported by your relational database. In Oracle, for example, you can specify an alias for a field by typing " as " then the alias after a column's real name.

 Some functions work on the visible records in the database table (Find, Sort Ascending, and Sort Descending), while others work on all the records that satisfy your SQL query (Export, Chart, Statistics, and Join). Keep this in mind when you are analyzing the contents of a database table. For example you may not be able to create a chart because charts work on all of a database table's records; by default you can only create a chart with 100 records or less.

 Once you've created a database table, you can't create ArcView indexes for its fields. The database table's SQL Select statement can take advantage of indexes that exist in the relational database, but not ArcView indexes.

 The Summarize button [image] is dimmed out for database tables. You can summarize the data in your database more effectively by creating a new SQL Select statement that uses SQL aggregate functions such as Count, Avg, and Group By.

 If one of the fields you retrieve from the database is a spatial column, you won't be able to access the shapes in it using a database table. Instead you will see numbers; these are internal reference numbers for the shapes. If you create a database table directly from a database theme by clicking the Open Theme Table button [image] you will see the word "Shape" instead of a number to indicate that a shape value is present; however, the column does not contain shape values.


What is a database theme (Database Access extension)
What is a database theme?

This topic is for the Database Access extension.

Using ArcView's Database Access extension, you can create themes in a view representing spatial data that is stored in a relational database; these are called database themes.

A database theme has a query that retrieves a set of fields and records from one or more tables in the database. One of the fields retrieved must be a spatial column; spatial columns contain spatial data the way numeric columns contain numeric data. What the database theme displays in your view is the value contained in the spatial column for each of the records retrieved from the database.

Before you can start working with the Database Access extension, you need to find out some information (details for SDE) so you can connect to the database. After the preliminaries are taken care of, you can load the Database Access extension then start using relational database data in ArcView.

Why use database themes?

The reasons why you would use database themes in ArcView are essentially the same reasons why you would store your spatial data in a relational database.

 relational databases use client-server technology, letting you develop a corporate database. Your data is stored in a central location on the database server, and is accessed across local or wide area networks by users with client software for the database. With the Database Access extension, ArcView can be a client to the relational database.

 relational databases let you store an extremely large number of records together in a single table in the database. As a result, your spatial data will not be tiled like it is with Librarian.

 relational databases provide efficient multi-user access to your data. With locking mechanisms, many people can update and modify the same layer of data at the same time. With transaction management, updates to the database aren’t saved and available to others until you commit the transaction, and you have the option to undo an editing session. These features let many database users access the most current data while others are editing it.

 you can develop ArcView applications based on your spatial database data using Avenue.

Working with database themes in ArcView

You can work with a database theme almost the same as any other feature data source. You can query your data, set a database theme’s properties, project data that is in decimal degrees, perform spatial analysis, create a thematic map, and incorporate your map in a layout. Because a database theme’s source data resides in relational database tables, some tasks work differently than for other themes. The following list outlines these differences:

 Adding database themes to your view is a different procedure where you have to make a network connection between ArcView and the database. You start this procedure with the Add Database Theme choice from the View menu, not with the Add Theme button [image]. A database theme’s definition property reflects the differences in how ArcView accesses spatial database data.

 Database themes directly reference their source data in the database because it is a multi-user data source; therefore they do not have FTabs like feature themes. Because a database theme does not have an FTab, ArcView doesn’t have a copy of its attribute table that you can see inside a table document. You can create a table to see your database theme's attributes; however, this table is not dynamically linked to the database theme you created it from. This means you can't select features in the theme using records in the table.

 There are two reasons why you might convert a database theme to a shapefile: you can’t edit a database theme’s features directly, you can’t use the Spatial Analyst, the Network Analyst, or the 3D Analyst directly with database themes, and you usually can’t be connected to the source data across a network when you are traveling with a portable computer.

 Unlike feature themes, ArcView doesn't assume that all features in the active theme are selected if there isn't a selected set. This means the Convert to Shapefile and Export to Table options in the Theme menu will be dimmed out if the active database theme doesn't have a selected set of features. Also, nothing will happen if you try to select a theme's features with the Select by Theme option based on a spatial relationship with a database theme that does not have a selected set of features.

 The Find button [image] is dimmed out for database themes. Use the query builder to locate features with specific attributes.

 The Area of Interest button [image] is dimmed out for database themes. However, you can apply a spatial filter to a database theme using Avenue. Spatial filters can be any shape, not just rectangles, and you can define exactly what relationship the features in your theme must have to that shape in order to be part of that theme. For example, you could define your database theme to contain all the features that fall outside of a buffer zone. You can apply more than one spatial filter to a database theme, and you can apply different spatial filters to all the database themes in a view.

 You can’t use your own null values in a database theme’s legend.

 When querying your database theme you build an SQL where clause instead of an ArcView query string.

 Since a database theme doesn’t have a table of attributes in ArcView, you control which fields are visible and assign field aliases by changing the theme's query.

 If you had a selected set of features in your database theme when you saved your project, the next time you open the project the selected set won't appear. The results of a selection operation on a database theme are recorded in a static manner. However, database themes exist in a multi-user environment. The current selection could produce different results at a later time if someone else is changing the data. Your selection won't change automatically when the database changes. Selections aren't saved with your project because the next time you open it, the features you selected in your previous session may no longer exist.

 Database themes are a read-only look at the shapes in your database. You can edit the shapes in your database using Avenue. To see the changes, redraw the database theme.


What you need to know before connecting to SDE (Database Access extension)
What you need to know before connecting to SDE

This topic is for the Database Access extension.

Before you can start working with SDE data in ArcView you need to find out some information and make sure your machine is set up correctly. Find out who the SDE administrator is in your organization, then arrange for some time to ask the following questions.

 What is the name of the SDE server I'm connecting to?

The SDE server is the machine that has your data.

 What is the name of the SDE database instance I'm connecting to?

The SDE database instance is the SDE process that lets you access a set of spatial data.

 What user name and password do I use to access the data?

 Is my machine already set up to connect to SDE?

If your machine isn’t already set up and you can do the work yourself by following these instructions. You need to find out two things: the service number of the SDE database instance, and the IP address of the SDE server.

 Which tables in the database contain the data I want?

If you aren’t familiar with the contents of the tables, ask for a data dictionary. It is important to know which tables contain what data, how those tables can be joined together, and what the column names in the tables mean.

 Which database contains the tables I need?

Some relational databases group associated tables together in a database (e.g., SQL Server and Sybase), while others don't (e.g., Oracle). If the relational database you're connecting to contains databases, you need to know the name of the database containing your tables.

 Who do I contact if I can't connect to the database?

Sometimes you won't be able to connect to SDE because the SDE server isn’t running, or because of network problems. Make sure you can contact someone to find out what is wrong and to have them solve the problem.

Now you have all the information you need to create database themes and database tables.


Known issues with Database Access

General

Moving data to new server invalidates themes and tables saved in projects

Changing column names in RDBMS invalidates themes and tables saved in projects

Can't retrieve large values from Oracle Number(38) columns

Dates around 1900 appear incorrectly with SDE servers on SGI and Compaq's Digital UNIX platforms

Field selection not remembered when switching between tables in SQL Query Wizard

Database Tables

Deactivate database tables before deleting source tables in SYBASE or DB2

Database Themes

ArcView hangs on operation when View contains a database theme

Features disappear on zoom when SDE layers have multiple spatial indexes

Annotation disappears when you zoom in close

Can't symbolize database themes with duplicate columns

Different standard deviation for database and feature themes with the same data

Different class breaks for numeric columns in database themes

Can't access a database theme's attribute aliases with Avenue

Features are highlighted, but a selection doesn't exist

Export features from themes with 1:N join, get N records for each feature

Can't use database themes if two tables with spatial columns are joined together

Theme-on-theme selection with 'Have Their Center In' does nothing

Warning when projecting themes with large extents

Queries

Can't use double quotes around string values in queries

Can't use * in SetSelectColumns to create DBThemes with all possible columns

DBASE limitations affect field names in DBThemes, DBTables, and RecordSets

Can't set table aliases in SetFromTables and use them in SetSelectColumns

Same performance for Optimized and Spatial First queries

Loading data into and updating data in SDE

Using RecordSet requests improperly for inserting and updating produces unexpected results

Number values default to 0 when inserting and updating with RecordSets

To store shapes, their coordinates must fall inside the spatial column's maximum extent

Can delete locked features from SDE

Don't get error message immediately when Update fails

BLOB fields must be last in the SelectedColumns list

Can't load measured shapes into SDE when measures are null numbers

When creating new tables, columns are defined incorrectly in the database


Database Access Known Issue
Moving data to different server invalidates themes and tables saved in projects

When you open a project, you are prompted to login to the saved connections; these connections lets you access the data required for the database themes and database tables saved in the project. To login, you provide your user name and password. You can't change details about the saved connection when you login. If the data required for the database themes and database tables has been relocated to a new SDE server, they will appear empty in ArcView.

To fix this problem, create a connection to the new data location, and associate the saved themes and tables with the new connection. Follow the instructions in Repairing a database theme's connection  or Repairing a database table's connection. (SDBsn55376)


Database Access Known Issue
Changing column names in RDBMS invalidates themes and tables saved in projects

When ArcView saves a project containing database themes or database tables, only the queries used to retrieve the data from the database are saved. If the name of an attribute column has been changed in the RDBMS, database themes whose queries use the old column name will draw in the view but you won't be able to access its attributes; tables using the old column name will appear empty.

To fix this problem, change the queries for the affected themes and tables to use the new column name instead. Follow the instructions in Changing a database theme's query  or Changing a database table's query. (SDBsn59955)


Database Access Known Issue
Can't retrieve large values from Oracle Number(38) columns

Database tables have problems when trying to retrieve large values from a Number(38) column in Oracle. SDE considers this data type an Integer because it is the default Integer data type in Oracle. However, SDE requires the size (or width) of integer columns to be less than ten. When you retrieve values from this column, SDE represents them as Number(10) values. When the value in the Number(38) column is too large to fit into a 32 bit integer, this operation fails and the resulting database table will be empty.

Instead of storing large integer values in a Number(38) column, store them in a Number(37) column. When retrieving values from a Number(37) column, SDE represents them as floating point numbers instead of integers. All conversions should succeed.(SDBsn54215)


Database Access Known Issue
Dates around 1900 appear incorrectly with SDE servers on SGI and Compaq's Digital UNIX platforms

The operating systems for SGI (IRIX) and Compaq's Digital UNIX platforms have difficulty representing some dates around 1900. As a result, Database Access users will encounter some incorrect dates in ArcView even though the dates are stored correctly in the database. This problem is resolved with SDE 3.0.2 servers; different methods are used to work with date values to avoid the problem.

With earlier servers, the problem occurs when ArcView Date objects are used to represent the date values retrieved by a query. Queries created with the SetSelectColumns and SetFromTables requests and queries created with the SetSQL request will both experience this problem.

On Compaq's Digital UNIX systems:

•	dates between December 14, 1901 00:00:00 and March 31, 1918 00:00:00 appear in ArcView an hour earlier. For example:

−	December 14, 1901 00:00:00 appears as December 13, 1901 23:00:00

•	dates between November 25, 1833 00:00:00 and December 14, 1901 00:00:00 appear in ArcView with the following time shift: add 136 years, 1 month, and 7 days to the original date. For example:

−	November 25, 1833 00:00:00 appears as January 1, 1970 6:28:16

On IRIX systems:

•	dates earlier than December 14, 1901 00:00:00 appear in ArcView with a time shift approximately equal to the time shift described above. At intervals approximately equal to the time shift, the shift itself appears to double. For example:

−	December 14, 1901 00:00:00 appears as December 14, 1901 00:00:00

−	December 13, 1901 00:00:00 appears as January 18, 2038 6:28:16

−	November 8 1765 00:00:00 appears December 14, 1901 6:28:16

−	November 7, 1765 00:00:00 appears as January 18 2038 12:56:32

Workaround:

Instead of retrieving the date value itself, modify your query to return the string equivalent of the date value; e.g., "Select To_char(dateColumn, 'MM/DD/YYYY HH24:MI:SS') from myTable". (SDBsn68064)


Database Access Known Issue
Field selection not remembered when switching between tables in SQL Query Wizard

When you create a query using the SQL Query Wizard, you select the fields you want to retrieve in the final panel. You select a table from the drop-down list at the top of that panel, then you select which of its columns you want to retrieve. You repeat this method to choose the columns you want to retrieve for each joined table. When you finish with the Wizard, your database theme or database table will only use the columns you've selected.

When several tables are joined together, if you go back to a table after you've already selected its fields, ArcView will forget your previous selection; all fields from that table will be selected. (SDBsn58768)


Database Access Known Issue
Deactivate database tables before deleting source tables in SYBASE or DB2

If you've created a database table to see data from a table in a SYBASE or DB2 relational database, and later in the same ArcView session you want to delete that table from the relational database, you may have problems.

When creating the database table, ArcView opens a cursor in the relational database on the source table. This cursor is not closed until the SDE connection is closed. If a cursor is open, you won't be able to delete the source table from the relational database. If you try to delete the table with the SDEConnection.DeleteTable request, ArcView will hang.

To avoid this situation, use the DBTable.Deactivate request on appropriate database tables before attempting to delete the source table from the relational database. Deactivate closes the cursor. If you have removed database tables that selected data from the source table from the project during the ArcView session, cursors for those tables will still be open. To resolve this situation, save and close the project; when you open the project again you will be able to delete the source table. (SDBsn58888)


Database Access Known Issue
ArcView hangs on operation when View contains a database theme

Several View operations need to know a theme's extent before the operation can continue. These operations include:

 projecting a view

 zooming to the extent of a theme

 zooming to the extent of all themes

 first draw operation in a new view [uses extent of first theme(s) added to the table of contents]

The first time ArcView needs a database theme's extent, ArcView requests that extent from the SDE server. A database theme's extent is the extent of its spatial column. ArcView caches the extent returned and uses it for the remainder of the ArcView session.

If the spatial column's extent property has been set, SDE sends it to ArcView immediately. Otherwise, SDE must calculate the spatial column's current extent. This operation can take a significant amount of time. The calculated extent is the minimum and maximum coordinates of all spatial index grids containing features. ArcView appears to hang while waiting for the results.

To set a spatial column's extent, use the the sdelayer utility's -E option. You can explicitly set the maximum and minimum coordinates or specify the Calc keyword. If you use ArcView to make spatial columns and load features, the columns' extent will not be set. If you use the Create option with the sdeimport utility to make a spatial column, its extent will not be set. (SDBsn63584)


Database Access Known Issue
Features disappear on zoom when SDE layers have multiple spatial indexes

SDE version 3.0 has some problems retrieving data from layers that have more than one spatial index grid. When viewing database themes based on these layers, sometimes features in the database theme will disappear after zooming in. Other times, on a zoom you will get the error message "Network I/O error" and the connection will be terminated. These problems are resolved with SDE version 3.0.1 servers. (SDBsn60516)


Database Access Known Issue
Annotation disappears when you zoom in close

Annotation in ArcView must meet specific requirements in order to be displayed in the view. Most of the annotation's extent must be contained within the view's extent; an annotation's extent may not be limited to the extent of the letters, depending on how the annotation was created. Additionally, for database themes the feature associated with the annotation must also be within the visible extent of the view. (SDBsn56255)


Database Access Known Issue
Can't symbolize database themes with duplicate columns

When you create database themes you can join several tables together. Each table may have columns that contain different data but have the same column name. You won't be able to symbolize the database theme with any column whose name appears more than once in the theme's query definition.

If two different tables have a column titled "Name", you will be able to create a Unique Value legend based on either column. However, when you apply the legend and re-draw the theme you will get the error message, "Underlying DBMS error". (SDBsn62819)


Database Access Known Issue
Different standard deviation for database and feature themes with the same data

ArcView calculates the standard deviation for numeric fields in a feature theme. For database themes, the standard deviation is calculated by the RDBMS or by SDE if the RDBMS doesn't provide the functionality. The results may differ depending on whether the equation used to calculate the standard deviation contains an (n) or an (n-1). ArcView uses (n) because it assumes a population of data. Both SDE and Oracle use (n-1), which is for a sample of data. (SDBsn60186)


Database Access Known Issue
Different class breaks for numeric columns in database themes

Class breaks in legends for numeric columns in database themes may be different than class breaks for the same data in feature themes when using Natural Breaks, Quantile, or Equal Area classifications. ArcView's algorithms for these three classifications require a list of unique values. SDE will only generate a list of 500 unique values in any column; 500 is a default value set on the SDE server.

To make the three classifications work, ArcView samples 500 values in a numeric column, then extrapolates class breaks based on the sample values. The resulting class breaks won't represent actual values in the column.

The maximum and minimum class breaks may not correspond to the maximum and minimum values in the column at the time the legend was generated. As a result, some features may not be accounted for in the classification. For these situations, you can get statistics for a column by clicking the Statistics button in the legend editor, then modify a legend's class breaks manually based on the current maximum and minimum values. (SDBsn60692)


Database Access Known Issue
Can't access a database theme's attribute aliases with Avenue

A database theme's query contains a list of column names that will be retrieved from the relational database; these are the theme's attributes. Because the column names may not be descriptive enough, you can specify an alias for these columns in the SQL Query Wizard when you create a database theme or modify its query. This alias is internal to ArcView; it corresponds to the Alias property of ArcView's Field object.

A database theme's Field objects, representing its attributes, are internal to ArcView; you can't access them using Avenue. There are no additional Avenue requests for database themes that let you set aliases for their attributes or access aliases after they have been set. At this time the only way to set aliases for a theme's attributes is through the user interface. You may think that setting a column's alias in the query's SelectColumns property (e.g., using AS) will solve this problem. For a description of why this is not an acceptable workaround, see the Known Issue concerning DBASE limitations for field names.(SDBsn60113)


Database Access Known Issue
Features are highlighted, but a selection doesn't exist

If you create very large selections and make those logfiles persistent, or if you make a large number of small logfiles persistent, you may fill the allocated space for logfiles on the SDE server. If there is not enough room on the server to create additional logfiles, the selected features will still be highlighted in the view. However, the Convert to Shapefile and Export to Table options in the Theme menu will be dimmed out; if you turn the theme off then on again, the features won't be highlighted. To solve this problem, delete the logfiles for your user from the SDE server. Logfiles are temporary by default to avoid this behaviour. (SDBsn47459)


Database Access Known Issue
Export features from themes with 1:N join, get N records for each feature

When you create a database theme, you can join several tables together. A one to many (1:N) join may exist between the shapes in an SDE layer and their attributes in another table. For example, water samples are taken from the same point on many different days; the database theme's query joins the sample points to the sample values in a 1:N relationship.

You can select the locations sampled in one day. Exporting the selected points creates a shapefile with records for every sample selected by the theme's query at each location. you might have expected the shapefile to contain one record for each point for the selected day instead.

A logfile records a set of shapes. It doesn't contain any information about those shapes, such as the query used to select them; a query may not have been used to create a logfile at all. Exporting the selected features involves processing the theme's definition query for all shapes recorded in the logfile; the definition query will select the maximum N records for each shape according to the theme's criteria. (SDBsn54221)


Database Access Known Issue
Can't use database themes if two tables with spatial columns are joined together

A database theme's query can join a table with a spatial column to any other tables in the database. If one of the joined tables contains a spatial column you won't be able to use the resulting database theme. SDE doesn't support feature operations in these circumstances. This is true even if you didn't include the joined table's spatial column in the query's SelectedColumns list.

The database theme created will draw, but other operations, like Identifying features, and symbolizing the theme's features, will fail. (SDBsn65511)


Database Access Known Issue
Theme-on-theme selection with 'Have Their Center In' does nothing

SDE version 3.x doesn't support the Contains Centroid spatial searches. Therefore, the 'Have Their Center In' option in the Select By Theme dialog is not supported for database themes. If you choose this option for a theme-on-theme selection, nothing will happen to the active database theme. (SDBsn52668)


Database Access Known Issue
Warning when projecting themes with large extents

ArcView checks the extents of all themes in a View before projecting its contents to ensure their data is in decimal degrees (ArcView can only project decimal degrees data). If the extent of any theme in the view isn't contained by the rectangle whose origin is (-200@-100) and whose size is (400@200), a warning will appear indicating that the view doesn't contain decimal degrees data.

It is possible for a database theme's extent rectangle to fail this comparison even though it contains decimal degrees data. Despite the warning, you can choose to project the view; if its data is in decimal degrees, a theme will be projected correctly even if it fails the initial test.

Similarly, when adding the first database theme to a view, its extent is checked using the same test. If the theme is determined to contain decimal degrees data the View's map and distance units are set appropriately. When the theme's extent is very large, the test above will determine the theme doesn't contain decimal degrees data, and the View's units won't be set. (SDBsn64181)


Database Access Known Issue
Can't use double quotes around string values in queries

When querying string fields in ArcView, you use double quotation marks around string values. RDBMS systems, however, have their own rules for the syntax that must be used when constructing queries; they require that you use single quotation marks around string values. If you use double quotation marks instead, the database won't be able to process your query; Oracle, and therefore SDE, will return an error indicating that it can't find the specified column. (SDBsn52834)


Database Access Known Issue
Can't use * in SetSelectColumns to create DBThemes with all possible columns

To retrieve shapes and their attributes from an SDE database, use the SetSelectColumns, SetFromTables, and SetWhereClause requests to create the query definition. For the SetSelectColumns request, provide the name of the spatial column and the name of attribute columns whose values you want to retrieve.

In a standard SQL statement you can choose to retrieve values from all columns in a table like this: "Select * from aTable". Using the same notation for a spatial query, e.g. "aQuery.SetSelectColumns({"*"}), you can create a database table, and open a RecordSet that will retrieve shapes and all the attributes in an SDE table. However, if you use the same query to create a database theme, the shapes will draw, but you won't be able to use any attributes in the theme. (SDBsn52789)


Database Access Known Issue
DBASE limitation affects field names in DBThemes, DBTables, and RecordSets

There are problems naming field objects that are created from query definitions because of dBASE limitations on field names in core ArcView. Field names must be 10 characters long or less, and cannot support non-alpha-numeric characters other than "_". When column names don't follow these rules, the field object's alias will be correct, but the field name will be different, and will not correspond to the column defined in the query definition. For example, columns using numeric functions (e.g., ABS(field)), involving calculations (e.g., field*100), or with aliases (e.g., columnName AS anAlias) will produce incorrect field names.

When you create a database table, its column names appear correct in the user interface because field aliases are displayed instead of field names. The same problem happens when creating Field objects for a RecordSet.

Database themes created from these queries will draw shapes, but you won't be able to access their attributes. Database themes have internal Field objects that are inaccessible from Avenue. Lists of attributes and operations on them involve locating columns in the database using field names. Fields whose names don't correspond to columns in the database won't appear in the Theme Properties dialog, the SQL Query Builder, the Auto Labeler, the Legend Editor, or in the Identify window. (SDBsn53199)


Database Access Known Issue
Can't set table aliases in FromTables and use them in SetSelectColumns

Standard SQL lets you specify aliases for table names and lets you use those aliases to define column names elsewhere in the same query, e.g., "Select A.Name, B.Name From table1 A, table2 B Where A.Name = B.Country".

When you create a spatial query for database themes, you use a combination of three requests: SetSelectColumns, SetFromTables, and SetWhereClause. If you try to set a table name alias in the FromTables request, you won't be able to use that alias in the SetSelectColumns and SetWhereClause requests. Spatial queries are processed by SDE, not by the relational database, which is why a different query structure is required. (SDBsn57113)


Database Access Known Issue
Same performance for Optimized and Spatial first queries

Optimized queries always have the same performance as Spatial first queries except in one circumstance: when a query uses a spatial filter to select features according to their location and the spatial filter uses a negative test value (e.g., return all features that aren't within this buffer), the query is processed as an Attributes first query. By default, all queries are optimized. (SDBsn54795)

When should you use a query that isn't optimized? If the Where clause selects a very small percentage of the features in an SDELayer, and those features are spread across a large area, using an Attributes first query will improve performance.


Database Access Known Issue
BLOB fields must be last in the SelectedColumns list

Most databases require that BLOB fields appear last in the list of selected columns. If the BLOB field isn't last, you won't be able to work with field that follow it in the list. You won't be able to edit records in the database if the BLOB field isn't last in the selected columns list. (SDBsn58277)


Database Access Known Issue
Using RecordSet requests improperly for inserting and updating produces unexpected results

When a RecordSet opens, a set of Field objects and a set of Value object buffers are created, one each for each column in the query's SelectColumns property. The Value objects are initialized with default values. The Update request sends the current value of each Value object to the database; these are stored in a new record if you used the AddNew request after opening the RecordSet, or they will overwrite values in an existing record if you used the Edit request. Use EndUpdate after storing all new records or changes to the database to complete the process.

The RecordSet's query should only select the columns you intend to set or change. You don't need to select the column(s) used in either the Query's or the Edit request's Where clause. If your RecordSet contains extra fields, e.g., because you opened it from an SDETable, consider the following behaviors in relation to the examples below:

   the Next request automatically sets each Value object to the default values

   the GetValue request sets one of the Value objects to the appropriate value in the current row of the RecordSet

   the GetRow request sets each Value object to the corresponding value in the current row of the RecordSet

   the SetValue request sets one of the Value objects to a custom value

   the SetRow request sets each Value object to the corresponding value in a list of custom values

Example 1   The RecordSet has two fields, A, a string, and B, a number, and represents an existing record in the database, which you want to edit. The record has values in both columns. Next sets the Value objects for both fields to default values and positions the selected record as the current record in the RecordSet. You use SetValue to make A "aValue". Update sends "aValue" for A and zero, the default number value, for B to the database. The original value in B is replaced by the zero.

Instead, use GetRow to set the Value objects to the original values in the database, then use SetValue to change the value of A. Now, both columns A and B are updated in the database, but the original value in B is unchanged.

Example 2   Add two new rows to the same table. For the first row, SetValue for A to "Apple" and SetValue for B to one. Update adds a new row to the database with the values {"Apple", 1}. You don't use Next, but move directly to adding the second row. Use SetValue for A, changing its value to "Orange"; B should contain a null number, but you don't explicitly set it with SetValue, and you don't use Next. Update adds another new row to the database with the values {"Orange", 1}.

Instead, use SetRow to explicitly set the Value objects. Now, to add the second row correctly, use SetRow{"Orange",Number.MakeNull}.(SDBsn52101)


Database Access Known Issue
Number values default to 0 when inserting and updating with RecordSets

When a RecordSet is created, a Field object and a Value object are created for each selected column. The Value object's value is initialized using a set of default values. Zero is used for Number columns; all other columns use the appropriate Value object's null value.

Therefore, if you insert or update a record into an SDE database without explicitly setting values for Number columns, the default value of zero will be stored in the database. The insert or update will be successful even if the Number columns have a NOT NULL constraint in the database because the value of zero exists.

If you want a NULL value (i.e. no value) stored in the database for Number columns, there are two options. First, explicitly set the Number column to the value Number.MakeNull. Second, do not select these columns in the query. The database ensures that NULLs exist for each column not present in the RecordSet when you insert a record; columns not represented in the RecordSet are not updated when you edit existing records in the database. (SDBsn53905)


Database Access Known Issue
To store shapes, their coordinates must fall inside the spatial column's maximum extent

An SDE spatial column has a maximum extent; the coordinates of all shapes in the spatial column must fall inside this extent. If a shape's coordinates fall on or outside the maximum extent rectangle, the shape will be rejected by SDE. In some circumstances ArcView will clip a polygon to the maximum extent rectangle rather than reject it.

When you create a measured or three-dimensional spatial column in SDE, you must provide a RectM or a RectZ shape for the maximum extent rectangle; the rectangle's measure or Z values define the maximum and minimum measure and Z values allowed in the spatial column. When storing measured and three-dimensional shapes, their measure or Z values must fall inside that range.

To make this situation more forgiving, when you create an SDE spatial column ArcView automatically scales the boundary rectangle that you provide to make it 1% larger. The range of the rectangle's M and Z values is scaled accordingly.. (SDBsn33527)


Database Access Known Issue
Can delete locked features from SDE

It is possible to delete features from the database that have been locked using the SDESColumn locking requests (i.e., SDESColumn.LockAll (canModify ), SDESColumn.LockArea (aRectangle, canModify )). This is true even if a Read lock has been established (canModify = false), where no users should be able to edit the locked features.

Because ArcView doesn't know SDE's internal ID number for each record to be deleted, the delete request is sent directly to the RDBMS, bypassing SDE and its locks. Other editing operations, like updating or adding features, will function properly in locked areas (i.e., if a lock has been established, changes will be denied by SDE). (SDBsn58765)


Database Access Known Issue
Don't get error message immediately when Update fails

To store a new record in or to send changes for a record to the database you use the Update request. On the surface, Update sends the record to the database, and you can check whether or not that Update succeeded by the Boolean value it returns. Actually, the record is sent to the SDE client-side buffer. When the buffer is full, all the rows in it are sent to the database; they are processed in a batch by the SDE server.

Update can fail if ArcView's preliminary checks are not satisfied, e.g., because you attempted to put a string into a number column. If the row passes all of ArcView's checks, Update will return TRUE. However, storing this row in the database may still fail. SDE checks shapes before storing them to ensure they are the appropriate size for a layer's spatial index grid, and that they are within the layer's extent. The RDBMS checks the rest of the query, e.g., that constraints on columns in the database are satisfied.

If storing a row fails during batch processing, all other records in the same batch will also be rejected. The next time the Update or EndUpdate request is used after the failure, an error will be reported indicating why the problem occurred. However, it is impossible to determine which record caused the problem. SDE provides no mechanism for getting this information with buffered inserts. (SDBsn56706)


Database Access Known Issue
BLOB fields must be last in the SelectedColumns list

When the SelectedColumns list includes a BLOB field, the BLOB must be the last field in the list. If the BLOB field is not last, you won't be able to retrieve values from fields that follow the BLOB in the SelectedColumns list. The BLOB value overwrites in memory the values that follow in the list because of its nature as a variable length, large size value. If you attempt to edit records in the database when the BLOB field is not last in the list, you will get the error message "Invalid indicator variable value". (SDBsn58277)


Database Access Known Issue
Can't load measured shapes into SDE when measures are null numbers

In ArcView, a measured shape is not required to have measure values. If a measure value hasn't been explicitly defined for a -M shape, the value will be a null number. SDE 3.x doesn't support these shapes; if you attempt to load them into SDE, the Update request will return the error message "Entity type not allowed".

For example, converting a regular PolyLine shape to a PolyLineM with the AsPolyLineM request produces a PolyLineM shape; the measure value for each vertex will be a null number. This shape won't load into SDE. After using the AsPolyLineM request, use one of the SetM, SetMAsLength, or SetMAsIndex requests before attempting to load the shape into SDE. (SDBsn65026)


Database Access Known Issue
When creating new tables, columns are defined incorrectly in the database

SDE must support many different Relational Database Management Systems. These systems don't have common data types. Therefore, SDE can only support data types that are common to all RDBMSs. For example, Integer fields must have a size (width in ArcView) that is greater or equal to 5 and less than or equal to 9.

To create a new table in SDE, you provide a list of ArcView Field objects. The data types for ArcView's fields don't directly correspond to the data types supported by SDE. The Field definitions you provide will be altered to the closest data type possible supported by SDE that will let you successfully store any values that might be contained in the ArcView Field.

For example, using an ArcView Integer Field with a width of 11 as a definition, the column created in an Oracle SDE database will be defined as Number(15,4). The size 11 is too large to be represented as an Integer in an SDE database; a Double column will be created instead, so 11 digit Integers can be stored. However, SDE requires Double columns to have decimal places. For Double columns, ArcView adds 4 decimal places to the column's definition to satisfy that requirement; the column's size is also increased by 4 to accomodate the decimal places.

For more detailed information, see Appendix A in the book Using ArcView Database Access. (SDBsn55088)


Frequently asked questions about database access (Database Access extension)
Questions about Database Access

What do you need help with?

Connecting to SDE

Why do I get the error message "Entry for SDE instance not found in services file" when trying to connect to SDE?

Why does ArcView do nothing when I try to connect to SDE?

What does the message "Network Failure" mean?

How many connections can I make?

How do I release an SDE client license?

Do I need to install client software for the relational database on my computer to connect to SDE?

Database Themes

Can I edit my database theme's features or their attributes?

Can I use database themes with 3D Analyst, Spatial Analyst, and Network  Analyst?

Why does my database theme draw slower when its query joins tables together?

Why do database themes that contain more than one feature type draw slower?

When I open a database table from a database theme, is it linked to the view?

How can I select all features in a database theme without drawing a box around them?

Why is a database theme's extent different than the exact extent of its features?

Database Tables

What is the total number of records that my database table represents?

Where are the temporary files for my database table?

Why does nothing happen when I create a database table?

Why can't I select records in my database table?

Why don't I see records I've just added to the database when I refresh my database table?

General

How do I add SDE data to my project?

What are measures?

What is a BLOB field?

SDE

What is SDE, and why should I use it?

What data can I store in SDE?

Can I work with SDE’s 3D and measured shapes in ArcView?

Can I load data into SDE using ArcView?

How many features can I store in an SDE spatial column?

Can I use the Database Access extension with an SDE 2.x database?

Is the spatial column always first in an SDE spatial table?

Avenue

Why can't I create a database theme using a QueryDef created with the SetSQL request?

Can I use SQL functions like Order By in a database theme's query?

Can I access tables stored in a remote database?

How can I run an Oracle stored procedure from an Avenue script?

How can I get SDE's internal ID number for a feature?

How can I query the columns in SDE's F-tables?

With SDE's Administration tools I set an offset and scale when loading spatial data. How can I do this with Avenue?

Why do I get the Oracle error 1658, "Unable to create initial extent for segment in tablespace", when creating a spatial column?


Why do I get the error message "Entry for SDE instance not found in services file" when trying to connect to SDE?

Have you modified the Services file on your computer? If not, get the information described in What you need to know before connecting to SDE, then follow the instructions in Setting up your computer for SDE. If you have already modified the services file and you are still having problems, try the suggestions below:

   Restart ArcView.

   Reboot your computer.

   Check the Services file in the DOS editor or vi to see if hidden characters have been added accidentally; remove them if they exist.

   Contact your system administrator.

Note	Setting up an HP machine may require an extra step. For details, see the section for HP users in Setting up your computer for SDE.


Why does ArcView do nothing when I try to connect to SDE?

Have you modified the Hosts file on your computer? If not, get the information described in What you need to know before connecting to SDE, then follow the instructions in Setting up your computer for SDE. If you've already modified the Hosts file, did you spell the server name and instance correctly? Remember, these names are case-sensitive. You may need to restart ArcView after editing the Hosts file.


What does the message "Network Failure" mean?

There is a network problem that is preventing ArcView from connecting to SDE. Talk to the SDE administrator to see if there is a reason why you can’t connect to SDE. If you were previously connected to an SDE server, something happened to break your network link to the server; make a new connection to the server to continue working.


How many connections can I make?

You can make as many connections as you want. Generally, you would create one connection to one SDE server; you would connect to as many SDE servers as necessary to access the data you require. However, every connection uses a client license on the server that you are connecting to; to connect to a server, a client license must be available.


How do I release an SDE client license?

To release an SDE client license, you need to close your connection to the SDE server. There are two ways to do this:

   Use the SDEConnection.Close request in an Avenue script on the SDEConnection object.

   Delete all database themes and database tables from your project that use the connection.

If you want to use SDE data in ArcView, but you need to minimize the number of SDE connections, you can develop a system of exporting the data you need from SDE, then closing the SDE connection. If you will be changing the data during the day, you will need to develop an application that locks the data in SDE and loads your changes into the database when you're done. For some ideas of how to build an application like this, see the sde3edit extension, located in the samples directory.


Do I need to install client software for the relational database on my computer to connect to SDE?

You do not need any database client software, ODBC drivers, or DBIs to connect to SDE. SDE, not ArcView, is responsible for connecting to the relational database and retrieving data from it.

You still need to use client software, and ODBC drivers or DBIs if you want to connect to the relational database using ArcView's SQL Connect functionality.


Can I edit my database theme's features or their attributes?

You can’t edit a database theme in ArcView’s interface the same way you edit shapefiles. Database themes can't enter an editable state, where you can use shape editing tools to modify features, and where you can directly edit attributes in the theme's table.

However, you can edit features and their attributes stored in SDE with Avenue scripts. See chapter 4 in Introducing ArcView Database Access for more details. Two sample extensions are provided which illustrate different editing applications: sde3edit and sde3aedt, located in the samples directory.


Can I use database themes with 3D Analyst, Spatial Analyst, and Network  Analyst?

No, you can’t examine database themes directly with 3D Analyst, Spatial Analyst, or Network Analyst. To analyze SDE data with these extensions, you need to convert your database theme to a shapefile.

If the theme’s spatial column in SDE contains measured or three-dimensional shapes, exporting it will produce measured or three-dimensional shapefiles, respectively. You can't edit measured or three-dimensional shapefiles with ArcView at this time.


Why does my database theme draw slower when its query joins tables together?

Every time a database theme draws its query is executed, choosing which shapes are part of the theme. If the query joins tables together, the tables will be joined when the theme draws to evaluate the query's criteria for choosing shapes. If a theme draws slower when tables are joined, you may need to create an index in the database on the columns that join the tables together. You can index columns in the database using the SDETable.CreateIndex request in an Avenue script.

To ensure you're getting the best performance possible from your theme's query, look at the relational database’s documentation. It may improve performance to change which table is on the left side of a join statement. Changing the SQL operators used in the query's attribute criteria may improve performance because some operators don't use column indexes even when they exist.


Why do database themes that contain more than one feature type draw slower?

Every time a database theme draws its query is executed, choosing which shapes are part of the theme. When a spatial column contains more than one feature type, the theme's query must select only the features that are appropriate for the theme's type. For line database themes, the query will select only the line shapes from the spatial column.

To select the right features, the theme must query a column called "entity" in the spatial column's feature table. The additional query itself will add some time to the length of the query; to reduce its impact on the theme's performace, indexing the entity column in the database.

A spatial column's feature table is hidden from ArcView users because it is only used behind the scenes by SDE. However, to index the entity column you must first know the name of the table it resides in. The SDE administrator can obtain this information with the SDE layer utilities. When you know the name of the feature table, you can index its entity field either using ArcView requests or by directly indexing the column in the database.


When I open a database table from a database theme, is it linked to the view?

There is no dynamic link between a database theme and a database table created from it. By default, the first 100 records retrieved from the RDBMS by the database theme’s query are displayed in the database table. There is no relationship between the 100 records and the features currently visible in the database theme. Database tables similarly won’t highlight the records corresponding to the selected features in the view.

Chapter 4 in Introducing ArcView Database Access contains examples that illustrate how to make database tables that represent only the features that are visible in the view or the currently selected features. You can also reference the sample script sde3link, located in the samples directory, which mimics a dynamic link between database tables and database themes.


How can I select all features in a database theme without drawing a box around them?

Create an Avenue script that uses the DBTheme.SelectByAttributes request. Provide an empty string, "", for the parameter anSQLWhereClause. The script will select all shapes in the database theme.


Why is a database theme's extent different than the exact extent of its features?

A theme's extent is defined as the extent of the spatial column referenced by the theme, NOT as the extent of the features the theme contains. Whether displaying features selected by an attribute or a spatial query, or one class of features in a spatial column (like points or annotation), the extent will always be the same. A spatial column's extent can be defined two ways.

A spatial column's extent isn't defined automatically when features are added to it, but you can explicitly define it's extent with the SDELayer server utilities. The spatial column's extent rectangle is used as the database theme's extent in ArcView. The extent might be larger or smaller than the actual extent of the stored features.

If a spatial column's extent hasn't been defined, the first time ArcView needs to know a theme's extent, SDE calculates the actual extent of all features currently stored. The calculated extent is the extent of all spatial index grid cells containing features. The size of the spatial index grid cells is determined when you create the spatial column; this determines how much larger the extent is than the actual extent of the features in the spatial column.

Calculating a spatial column's extent can be very time consuming. ArcView caches the extent, and uses it for the duration of the ArcView session whether or not the stored features or the features comprising the theme change.


What is the total number of records that my database table represents?

Database tables are created from queries that select records from a relational database. A database table represents all the records selected by a query. By default, you will see the first 100 records retrieved by its query; the records you see represent the visible portion of the entire set of records that the database table represents.

ArcView's TableGUI show how many records are currently visible in the database table, but what is the total number of records that the table represents? To find out, you need to send another query to the relational database.

1	From the Project menu, choose Database Table Preferences.

2	In the Database Table Preferences dialog, choose Add Database Table Dialog.

3	Click OK.

4	From the Project menu, choose Add Database Table.

5	Place the same tables as the original database table into the From text box.

6	Enter the same join statements and attribute criteria as the original database table into the Where text box.

7	In the Select text box, type Count(*).

8	Click Query.

The resulting Database Table will contain one record that shows the total number of records that your original database table represents.


Where are the temporary files for my database table?

When you create a database table, you only see some of the records selected by the its query; these records are contained in a temporary VTab located in the /temp directory (e.g., c:\temp or /tmp) on your machine. They are called vtab<N>.tmp. When the project is closed or the database table is deleted, the temporary files will automatically be deleted.


Why does nothing happen when I create a database table?

If the query you created selected no data, a database table will be created with columns but no records. If the query you created had a syntax error, an error message will appear; the table will be added to the project, but it won't open automatically.

Under some circumstances, an empty database table will be added to your project but no error message will appear. There are two reasons why that can happen. The query may have an error that was detected by the relational database when it started processing the query. This is problem is described in more detail in Known Issues.

When ArcView draws a database theme it creates a RecordSet to retrieve data, and closes it when the drawing is finished. However, for each database table in a project ArcView maintains an open RecordSet to let you continue working with and modifying the table. By default, the SDE server lets you create a maximum of eight RecordSets at a time for each connection. If a project has eight database tables from one connection, additional database tables will be added to the project but will appear empty.

To check if the second issue is the problem with your database table, try redrawing a database theme that uses the same connection. Database themes create RecordSets to retrieve the data that draws in the view. If the eight RecordSet limit has been reached, you'll get the error, "Maximum number of streams exceeded".

If you need to create more than eight database tables from one SDE server, you can do one of the following:

   Make a second connection to the same SDE server. Create the database tables nine through sixteen with the new connection. (NOTE: one connection = one client license).

   Have the SDE administrator increase the number of streams per connection on the server. They need to adjust the MAXSTREAMS parameter in the $SDEHOME/etc/giomgr.def file on the server.


Why can't I select records in my database table?

Unlike regular ArcView tables, it is not possible to create a selected set of records in a database table. The records in a database table are a set of records selected from the database. To reselect some of the features contained in a database table, choose Properties from the Table menu, then modify the database table's SQL query to choose the records you want to see.


Why don't I see records I've just added to the database when I refresh my database table?

When you refresh a database table, its local, temporary files are updated with the most current data; however, the number of records contained in those files will remain the same. To add more rows to your database table, choose Get More Rows from the Table menu; repeat as necessary. If choosing Get More Rows doesn't work, make sure your changes have been committed in the database.


How do I add SDE data to my project?

First, you need to load the Database Access extension.

To see shapes stored in SDE in ArcView, make a View document active, then choose Add Database Theme from the View menu. Step-by-step instructions are contained in the Adding a database theme to your view help topic.

If you want to see non-spatial data stored in SDE in tabular format, make the Project window active, then choose Add Database Table from the Project menu. Step-by-step instructions are contained in the Creating a database table help topic.

For more details about creating and working with database themes and database tables, see Chapter 2 in Introduction to Database Access.


What are measures?

Measures let you store information along linear features like distance, time, address, or any other event at a given point along a linear feature. A measure is a discrete location along a linear feature. A measured shape stores a measure value at each of the shape's vertices in the form of an x, y, m coordinate.

Measured shapes have their own classes (e.g., the PolyLineM class) and requests. You can create measured shapefiles (e.g., PolyLineM shapefiles) when the Database Access extension is loaded. You can load measured shapefiles into SDE using Avenue scripts.


What is a BLOB field?

A BLOB (Binary Large Object) is a data type. A BLOB column in an RDBMS can store any type of data in any format, for example, text files, ArcView ODB files, image files, or Word document files.

The Database Access extension provides access to the data in a BLOB field using files. You can write files to a BLOB column in a relational database by using the RecordSet.SetValue request in an Avenue script and providing a FileName object as the value.

ArcView retrieves BLOBs when a query selects values from the column, and you use the identify tool on a database theme, create a database table, or retrieve the value using an Avenue script. When ArcView retrieves BLOBs, a file called blob<N>.sde is created containing the data. These files are located in the project's work directory, which can be set using the Project Properties dialog.

If you have a project where BLOBs will be retrieved immediately on loading the project, the blob files are initially placed in your TEMP or TMPDIR directory; for example, this happens when the project contains a database table that displays the contents of a BLOB field. After the project has finished loading, the next time blob values are retrieved they will be placed in the project's work directory.


What is SDE, and why should I use it?

ESRI's Spatial Database Engine (SDE) runs on top of a conventional Relational Database Management System (RDBMS). It lets you store geographic shapes together with their attributes in an RDBMS. Spatial tables can coexist with other attribute tables in the database. SDE retrieves spatial data as regular ArcView shapes.

You should consider storing your data in a relational database and accessing it using SDE if you either have an extremely large number of features in your database, or if you have a dynamic database where many people need to update and view the same data at the same time.


What data can I store in SDE?

You can store the contents of ESRI's shapefiles and coverages in SDE. Using the CAD client, you can also store CAD data in SDE and view that data in ArcView. SDE supports point, line, and polygon data, multipart shapes for each of those types, and annotation; SDE shapes can have measure and Z values.

You can also store non-spatial data in tables in the same database. In addition to the standard data types, character, numeric, and dates, the database may support Binary Large Object (BLOB) columns. You can store text files, images, Microsoft Word files, or ArcView ODB files in BLOB columns, and retrieve them later using the Database Access extension.


Can I work with SDE’s 3D and measured shapes in ArcView?

You can work with three-dimensional and measured shapes in ArcView that are stored in SDE using Avenue. In a script, create a query to select the shapes you want, then open a RecordSet to retrieve them. If a spatial column contains measured or three-dimensional shapes, the objects returned will be measured or three-dimensional shapes, e.g., PolyLineM or PointZ shapes. You can analyze the returned shapes with the appropriate shape class' requests.

Database themes draw in two-dimensions only. Database themes that reference spatial columns containing measured or three-dimensional shapes will draw in a view as if the shapes had x,y coordinates only. To see a database theme's shapes in the 3D Scene document, you need to export them to a shapefile.


Can I load data into SDE using ArcView?

The Database Access extension provides Avenue requests that allow you to load shapes into SDE and to load attribute data into RDBMS tables. The sde3tls extension, located in the samples directory, contains scripts that will load the contents of feature themes (containing shapefile, coverage, or CAD data) and ArcView tables (containing dBASE, INFO, or Text data) into the database.

NOTE: each RDBMS has its own rules regarding the characters that are supported in column names. For example, Oracle doesn't support column names containing hyphens, "-". INFO tables containing coverage attributes may have a column like "cityanno-id"; you can load these tables into Oracle if you change the field's name in the table's VTab to "cityanno_id".


How many features can I store in an SDE spatial column?

There is no limit to the number of features you can store in an SDE spatial column. The shapes in a spatial column aren't tiled even if there are a very large number of features; you use queries to select only the shapes you want to work with in ArcView.


Can I use the Database Access extension with an SDE 2.x database?

No, Database Access can only be used with SDE version 3.x databases.


Is the spatial column always first in an SDE spatial table?

No, unlike ArcView shape files, SDE does not require the spatial column to be the first column in a spatially-enabled table. If you load spatial data into SDE using ArcView, the spatial column will be the last column in the table. The order of columns in a database theme or a database table depends on the order in which the columns appear in their query.


Why can't I create a database theme using a QueryDef created with the SetSQL request?

You cannot retrieve shapes from an SDE database with an SDEQueryDef whose SQL statement was defined using the QueryDef.SetSQL request. Queries created with the SetSQL request are sent directly to the RDBMS, bypassing SDE.

To retrieve shapes, you need to use the SDEQueryDef requests SetSelectColumns, SetFromTables, and SetWhereClause. These queries are interpreted by SDE. If you modify a database theme's query, you must use these same requests.


Can I use SQL functions like Order By in a database theme's query?

You can use the Order By function and aggregate functions, like Group By, in a database table's query. However, you cannot use them for database themes. When these functions have been added to a spatial query's Where clause, SDE won't be able to retrieve shapes from the database.


Can I access tables stored in a remote database?

No, it is currently not possible to access attribute tables or spatial data stored in remote databases with spatial queries; this is an SDE limitation.


How can I run an Oracle stored procedure from an Avenue script?

You can run a stored procedure with an SQL query. The QueryDef.SetSQL request should contain a string in the format, "Begin <procedureName>;END;". The QueryDef.Execute request sends this query to the database, which then runs the stored procedure called procedureName.


How can I get SDE's internal ID number for a feature?

All shapes have an internal ID number which is used by SDE to identify the row it's contained in. There are two ways to these ID numbers using Database Access:

   Use the FindByPoint request to get the ID numbers for all shapes that intersect a point. Using FindByPoint in connection with a mouse click event, you can mimic the Identify tool.

   A RecordSet will return a shape's ID number if the pseudo column "SE_ROW_ID" is one of the selected columns.

Note: You can't insert or edit records in the database with a RecordSet whose query contains "SE_ROW_ID". For an example of how to edit records using the spatial column as the primary key, see the sde3edit sample extension, located in the samples directory. To edit records in a non-spatial table, it must have its own primary key (a column containing a unique value for each record in the table).


How can I query the columns in SDE's F-tables?

To query the contents of SDE's F-tables you need to construct a spatial query with the SetSelectColumns, SetFromTables, and SetWhereClause requests. In the SetSelectColumns request, select the spatial column from a spatially-enabled table in the database. Select columns from the F-Table, with the syntax <spatialColumnName>.<FTableColumnName>. For example:

aQuery.SetSelectColumns({"Shape","Shape.Fid","Shape.Entity"})

Do not preface "Shape.Fid" with the spatially-enabled table's name or its owner's name. SDE implicitly joins the business table and the F-table when shapes are retrieved.

If you create a database table with a query that selects columns from the F-table, you won't be able to modify the table's properties from the user interface; if you try, you will get the error message "Attribute column not found" when you apply your changes. Changing a database table's query from the user interface uses the SetSQL request; database tables created with SQL queries don't retrieve shapes, and therefore don't join the business and F-tables. To query an F-table with an SQL query, use its actual table name (F<n>, where <n> represents the layer number) and preface columns with the table and owner names.


With SDE's Administration tools I set an offset and scale when creating a spatial column. How can I do this with Avenue?

The request SDESColumn.SetCoordRefByRect calculates both of these parameters automatically from the bounding rectangle that you provide. The offset parameter is taken from the lower left coordinate of the rectangle. The scale is set to the most precise scale allowed by SDE for the spatial column you are creating.


Why do I get the Oracle error 1658, "Unable to create initial extent for segment in tablespace", when creating a spatial column?

When a spatial column is created, several ORACLE tables are needed to store the spatial information (see SDE's documentation for more information). This error means that there is not enough free space in your default ORACLE tablespace to create the tables needed for the spatial column.

To avoid this error, there are several things that you can do:

   If you're using the SetAveragePoints and SetNumShapes requests in your script, make sure you have provided reasonable values. This information is used by SDE to calculate the size of the initial extents of the spatial column's tables.

   Alternatively, you can use the SetCreationKeyword request in your script to reference the dbtune.sde file. This file can explicitly define the size of the spatial column's tables and the tablespace they are created in. See SDE's documentation for more information about the dbtune.sde file.

   If you still have problems creating the spatial column, ask the Database Administrator to make the tablespace larger.