Server Create Table statement - MapBasic - 2023

Purpose

Creates a new table on a specified remote database. You can issue this statement from the MapBasic window in MapInfo Pro.

Syntax

Server ConnectionNumber Create Table TableName
	( ColumnName ColumnType [ , ... ] ) 
	[ KeyColumn ColumnName ]
	[ ObjectColumn ColumnName [ Type SQLServerSpatial 
		{ Geometry | Geography } ] ]
	[ StyleColumn ColumnName ]
	[ CoordSys... ]

ConnectionNumber is an integer value that identifies the specific connection to a database.

TableName is the name of the table as you want it to appear in a database.

ColumnName is the name of a column to create. Column names can be up to 31 characters long, and can contain letters, numbers, and the underscore(_) character. Column names cannot begin with numbers.

ColumnType is the data type associated with the column.

Description

The Server Create Table statement creates a new empty table on the given database of up to 250 columns.

TableName is the name of the table as you want it to appear in database. The name can include a schema name, which specifies the schema that the table belongs to. If no schema name is provided, the table belongs to the default schema. The user is responsible for providing an eligible schema name and must know if the login user has the proper permissions on the given schema. This extension is for SQL Server 2005 only.

The length of TableName varies with the type of database. We recommend using 14 or fewer characters for a table name to ensure that it works correctly for all databases. The maximum TableName length is 14 characters.

ColumnType uses the same data types defined and provided in the Create Table statement. Some types may be converted to the database-supported types accordingly, once the table is created on the database.

The optional KeyColumn clause specifies the key column of the table. If specified, a unique index will be created on this column. We recommend using this clause since it is also allows MapInfo Pro to open the table for live access.

The optional ObjectColumn clause enables you to create a table with a spatial geometry/object column. If it is specified, a spatial index will also be created on this column. However, if the server does not have the ability to handle spatial geometry/objects, the table will not be created. If the server is an SQL Server with SpatialWare, the table is also spatialized once the table is created. If the Server is Oracle Spatial, spatial metadata is updated once the table is created.

If Server Create Table is used and the ObjectColumn clause is passed in the statement, you will also have to use the Server Create Map statement in order to open the table in MapInfo Pro.

The optional StyleColumn clause specifies the Per Row Style column, which allows the use of different object styles for each row on the table.

The optional CoordSys clause clause specifies the coordinate system and projection to be used. This clause becomes mandatory only if the table is created with spatial object/geometry on Oracle Spatial (Oracle9i or later with spatial option). If Oracle9i is the server and the coordinate system is specified as Lat/Long without specifying the datum, the default datum, World Geodetic System 1984(WGS 84), will be assigned to the Lat/Long coordinate system. The coordinate system must be the same as the one specified in the Server Create Map statement when making it mappable. For other DBMS, this clause has no effect on table creation.

The supported databases include Oracle, SQL Server, PostGIS and Microsoft Access. However, to create a table with a spatial geometry/object column, SpatialWare is required for SQL Server and the spatial option is required for Oracle.

Notes on DateTime and Time Data Types

There is no specific change in terms of syntax. We do have following restrictions for the some data types:

The datatypes Time and DateTime are useful but you must consider the database when using them. Most databases do not have a corresponding DBMS TIME types. Before this release, we only supported the Date type. Even the Date was converted to server type If the server did not support Date type. In MapBasic 9.0 and later, this statement only supports the types that the server also supports. Therefore, the Time type is prohibited from this statement for Oracle, SQL Server and Access, and the Date type data type is prohibited for SQL Server and Access. Those "unsupported" types should be replaced with DateTime if you still want to create the table that contains time information on a column.

Examples

The following examples show how to create a table named ALLTYPES that contains seven columns that cover each of the data types supported by MapInfo Pro, plus the three columns Key, SpatialObject, and Style columns, for a total of ten columns.

For SQL Server with SpatialWare:

dim hodbc as integer
hodbc = server_connect("ODBC", "dlg=1")
Server hodbc Create Table ALLTYPES( Field1 char(10),Field2 integer,Field3 
SmallInt,Field4 float,Field5 decimal(10,4),Field6 date,Field7 logical)
KeyColumn SW_MEMBER
ObjectColumn SW_GEOMETRY
StyleColumn MI_STYLE

For Oracle Spatial:

dim hodbc as integer
hodbc = server_connect("ORAINET", "SRVR=cygnus;UID=mipro;PWD=mipro")
Server hodbc Create Table ALLTYPES( Field1 char(10),Field2 integer,Field3 
SmallInt,Field4 float,Field5 decimal(10,4),Field6 date,Field7logical)
	KeyColumn MI_PRINX
	ObjectColumn GEOLOC
	StyleColumn MI_STYLE
	Coordsys Earth Projection 1, 0

See Also: