Creating DBMS tables

ArcSDE provides a DBMS independent mechanism for creating tables. The SE_table_create function takes a table name and a list of column types as input. The column types are defined independently of DBMS, using the following ArcSDE data types:

SE_SMALLINT_TYPE
SE_INTEGER_TYPE
SE_FLOAT_TYPE
SE_DOUBLE_TYPE
SE_STRING_TYPE
SE_DATE_TYPE
SE_BLOB_TYPE

The ArcSDE data types are mapped to DBMS types differently for each DBMS. See the ArcGIS Server or ArcGIS Desktop help topic About data types for the exact values. When you are defining a column, you need to define the size and, optionally, number of decimal digits. For integer, date, and small integer columns, the size and decimal digits are zero. For float and double columns, the size and decimal digits represent the column definition. For string (character) and BLOB columns, the size represents the column definition and the decimal digits equals zero. You need to remember to add 1 to the size for the null terminator for string type columns.

You can define DBMS-specific column types using SQL expressions executed through SE_stream_prepare_sql. There are no assurances that tables created with SE_stream_prepare_sql are transportable between different database management systems.

In this example, you create a three column table named cities. The table contains the following columns:

* city_name, 20 characters long, NULL values are not permitted.
* area, double precision, with 12 digits and four decimal digits.
* population, integer, NULL values are not permitted.

num_cols = 3;

column_defs = (SE_COLUMN_DEF *) calloc (num_cols, sizeof(SE_COLUMN_DEF));

if (NULL == column_defs)

{

printf ("Failed to allocate column list\n");

exit(0);

}

/* city_name is a 20-character, NOT NULL column */

strcpy (column_defs[0].column_name, "city_name");

column_defs[0].sde_type = SE_STRING_TYPE;

column_defs[0].size = 20;

column_defs[0].nulls_allowed = FALSE;

/* area is a double-precision column, with 12 digits and 4 decimal digits */

strcpy (column_defs[1].column_name, "area");

column_defs[1].sde_type = SE_DOUBLE_TYPE;

column_defs[1].size = 12;

column_defs[1].decimal_digits = 4;

column_defs[1].nulls_allowed = TRUE;

/* population is an integer, NOT NULL column */

strcpy (column_defs[2].column_name, "population");

column_defs[2].sde_type = SE_INTEGER_TYPE;

column_defs[1].size = 0;

column_defs[1].decimal_digits = 0;

column_defs[2].nulls_allowed = FALSE;

/* Create the table with the CITIES dbtune.sde configuration keyword */

rc = SE_table_create (Connection, "cities", num_cols, column_defs, "CITIES");

check_error(Connection, NULL, rc, "SE_table_create");

This next example creates the same table using an SQL statement and the SE_stream_prepare_sql function. Keep in mind that this approach may not be transportable across multiple DBMS platforms.

sprintf (sql_stmt, "create table cities (%s, %s, %s)",

"city_name varchar2(20) not null",

"area number(12,4)",

"population integer not null");

rc = SE_stream_prepare_sql (Stream, sql_stmt);

check_error(Connection, NULL, rc, "SE_stream_prepare_sql");

rc = SE_stream_execute (Stream);

check_error(NULL, Stream, rc, "SE_stream_execute");

Describing DBMS tables

In order to obtain the column definitions of a business table, the SE_table_describe() function can be used. This functions takes the connection, table name, pointer to SE_COLUMN_DEF structure, and number of columns as input and populates the SE_COLUMN_DEF structure with column definitions.

SE_COLUMN_DEF *pcoldef=NULL;
short ncols;
char *tableName;

/* Get table description */

sr = SE_table_describe(connection, tableName, &ncols, &pcoldef);

checkError(NULL, Stream, rc, "Se_table_create");

Deleting DBMS tables

DBMS tables can be deleted in a DBMS-independent fashion through the SE_table_delete function. When a table is spatially enabled, SE_table_delete deletes the business table, the feature table, the spatial index tables, and entries in the LAYERS table.

rc = SE_table_delete (Connection, "Cities");

check_error(Connection, NULL, rc, "SE_table_delete");

You can also delete tables with an SQL expression, using SE_stream_prepare_sql. Do not use this approach to delete spatially enabled tables, because the support tables (feature, index) and LAYERS entry are left behind. This example deletes a nonspatial table called taxes.

rc = SE_stream_prepare_sql (Stream, "drop table taxes");

check_error(NULL, steam, rc, "SE_stream_prepare_sql");

rc = SE_stream_execute (Stream);

check_error(NULL, Stream, rc, "SE_stream_execute");

You can remove the data but retain the table definition with the SE_table_truncate function.

/* Truncate the table */

rc = SE_table_truncate(Connection, "Cities");

check_error(Connection, NULL, rc, "SE_table_truncate");

Indexing a column

You can add and drop an index on a column with SE_table_create_index and SE_table_delete_index. Indexes can be ascending, descending, or unique. The following example adds an index to the cities table created earlier. The table has three columns: city_name, area, and population. This example creates an ascending index on the city_name column.

/* Create an ascending index called name_index on the city_name column */

rc = SE_table_create_index

(Connection, "cities", "city_name", FALSE, TRUE, name_index, "CITIES");

check_error(Connection, NULL, rc, "SE_table_create_index");

This next example creates the same column index using an SQL statement and the SE_stream_prepare_sql function. Keep in mind that this approach may not be transportable across multiple DBMS platforms.

sprintf (sql_stmt, "create index name_index on cities (city_name ASC)");

rc = SE_stream_prepare_sql (Stream, sql_stmt);

check_error(NULL, Stream, rc, "SE_stream_prepare_sql");

rc = SE_stream_execute (Stream);

check_error(NULL, Stream, rc, "SE_stream_execute");