Commit Table statement - MapBasic - 2023

Purpose

Saves recent edits to disk, or saves a copy of a table. In the past, you were unable to save queries that contained indeterminate types, such as often occurred in ObjectInfo queries. We have added an Interactive parameter to allow you to specify indeterminate types in such a query. If you do not use the interactive parameter, the system uses a default type instead. You can issue this statement from the MapBasic window in MapInfo Pro.

Syntax

Commit Table table
	[ As  filespec  
	   [ Type { NATIVE [ Charset char_set ] |
            NATIVEX [ Charset char_set ] [ Locale locale ] |
			DBF [ Charset char_set ] | 
			Access Database database_filespec Version version Table tablename 
				[ Password pwd ] [ Charset char_set ] |
			QUERY |
			ODBC Connection ConnectionNumber Table tablename
			[ Type SQLServerSpatial { Geometry | Geography } ]
			[ ConvertDateTime { ON | OFF | INTERACTIVE } ] |
			GPKG [ Database database_filespec ] [ Table tablename ] 
				[ Charset char_set ] } ]
		[ CoordSys... ] 
		[ Version version_pro ] ] 
	[ Interactive ]
	[ Background [View None|Automatic|Map|Browse|Add To Map] 
			[Into { Variable var_name }] [Calling mb_sub_with_1_param_and_error_trap] ]
	[ BlockSize block_size ]
	[ { Interactive | Automatic commit_keyword } ] 
	[ ConvertObjects { ON | OFF | INTERACTIVE } ]

table 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. When used with the GPKG clause, it is the name of the table as you want it to appear in GeoPackage.

filespec is a file specification (optionally including directory path). This is where the MapInfo .TAB file is saved.

char_set is the name of a character set; see CharSet clause.

locale is a string that specifies the locale of the index to create, such as "en-US". If not set, then the default system locale is used.

version is an expression that specifies the version of the Microsoft Jet database format to be used by the new database. Acceptable values are 4.0 (for Access 2000), 3.0 (for Access '95/'97), 12.0 (for Access 2007). If omitted, the default version is 12.0. If the database in which the table is being created already exists, the specified database version is ignored.

database_filespec a string that identifies the name and path of a valid:

• Access database when used with Access. If the specified database does not exist, MapInfo Pro creates a new Access (.MDB or .ACCDB) file.
• GeoPackage file when used with GPKG.

pwd is the database-level password for the database, to be specified when database security is turned on.

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

version_pro is 100 (to create a table that can be read by versions of MapInfo Pro) or 300 (MapInfo Pro 3.0 format) for non-Access tables. For Access tables, version is 410.

Background : Specifying this token indicates that the Commit Table command will be executed on a non-blocking background thread.

Into is the task id of the background task for use in TaskInfo() function.

If a Calling clause is specified, then the task handler is called asynchronously after the task is completed and MapInfo Pro is idle. The task handler accepts one numeric argument with no return type and should have an error trap in it. Parameter to the task handler is the ID of the task.

block_size is a value of between zero (0) to 32767. If not specified, the default value is 16384. If the value is less than 512, it rounds up to 512.

commit_keyword is one of the following keywords: NoCollision, ApplyUpdates, DiscardUpdates

Description

If no As clause is specified, the Commit Table statement saves any pending edits to the table. This is analogous to the user choosing the Save command in MapInfo Pro.

A Commit Table statement that includes an As clause has the same effect as a user choosing the Save Copy As command. The As clause can be used to save the table with a different name, directory, file type, or projection.

To save the table under a new name, specify the new name in the filespec string. To save the table in a new directory path, specify the directory path at the start of the filespec string.

The optional Type clause indicates the format of the local table, as either MapInfo (NATIVE) or MapInfo Extended (NATIVEX). Use a Type clause within the As clause. The NATIVEX format supports tables larger than 2GB in size and with UTF-8 and UTF-16 character sets. When the The Type clause is not specified, then the table type defaults to NATIVE.

The optional Charset clause is set with the Type clause and saves to a specific character set. The char_set parameter should be a string constant, such as "WindowsLatin1". See CharSet clause for more information.

• If not provided with the Type clause, the character set of the source table is assumed. Using an incompatible character set may cause the character data in the destination table to convert into underscores (such as WindowsCyrillic to WindowsLatin1). The preference is to convert to or from UTF-8 or UTF-16 character sets.
• If not provided with the DBF and Table clauses, MapBasic uses the default character set for the hardware platform that is in use at runtime. Note, the Commit Table statement does not support UTF-8 or UTF-16 for DBF.

The Locale clause is set with the NATIVEX clause to specify the locale of the index to create, which will affect the sorting behavior of the table. It takes a string value, such as "en-US". If not specified, default system locale is used. This clause only applies to NativeX tables with a character set (charset) of UTF-8 or UTF-16.

To save the table using a different coordinate system or projection, include a CoordSys clause within the As clause. Note that only a mappable table may have a coordinate system or a projection.

To save a Query use the QUERY type for the table. Only queries made from the user interface and queries created from Run Command statements in MapBasic can be saved. The Commit Table statement creates a .TAB file and a .QRY file.

The Version clause controls the table's format. If you specify Version 100, MapInfo Pro stores the table in a format readable by versions of MapInfo Pro. If you specify Version 300, MapInfo Pro stores the table in MapInfo Pro 3.0 format. Note that region and polyline objects having more than 8,000 nodes and multiple-segment polyline objects require version 300. If you omit the Version clause, the table is saved in the version 300 format.

The ODBC clause indicates a copy of the Table will be saved on the DBMS specified by ConnectionNumber.

Setting the SQLServerSpatial clause supports spatial data with GEOGRAPHY and GEOMETRY data types.

The ConvertDateTime If the source table contains Time or Date type columns, these columns will be converted to DATETIME or TIMESTAMP depending on whether the server supports the data types. However, you can control this behavior using the clause ConvertDateTime. If the source table does not contain a Time or Date type, this clause is a non-operational. If ConvertDateTime is set to ON (which is the default setting), Time or Date type columns will be converted to DATETIME or TIMESTAMP. If ConvertDateTime is set to OFF, the conversion is not done and the operation will be canceled if necessary. If ConvertDateTime is set to INTERACTIVE a dialog box will pop up to prompt the user and the operation will depend on the user's choice. If the user chooses to convert, then the operation will convert and continue; if the user chooses to cancel, the operation will be canceled.

The Time type requires conversion for all supported servers (Oracle, PostGIS, SQL Server Spatial, MS SQL Server and Access) and the Date type requires conversion for MS SQL Server and Access database servers.

CoordSys is a coordinate system clause; see CoordSys clause.

The commit table statement returns as soon as the background task gets created. The execution continues without waiting for this task to complete. So, the table does not get created until the task completes at a later time. If a calling clause is specified, then the task handler is asynchronously called after the task is completed and MapInfo Pro is idle (because two sets of MapBasic code cannot execute simultaneously). For this to work, the MBX must not exit before that happens. You must have an EndHandler procedure to ensure that the MBX remains loaded and can process the task handler. While not recommended, since it negates the benefit of a background task and blocks the user from doing anything, an MBX could use polling to keep checking the CurrentStatus of the task id until it is complete and then continue with executing the MBX.

The BlockSize clause have a value of zero (0) to 32767. If not specified, the default is 16384. If the value is less than 512, it rounds up to 512. Generally, the larger the block size, the faster the data transfer rate. When converting from NativeX (MapInfo Extended) format to Native (MapInfo) format, the original block size of the NativeX file is preserved. Users can specify a new block size when committing from NativeX to Native format.

Setting the ConvertObjects clause to ONautomatically converts any unsupported objects encountered in supported objects. Setting it to OFF does not convert unsupported objects. If they are encountered, an error message displays saying the table cannot be saved. Setting to Interactive asks the user what they want to do when unsupported objects are encountered in a table.

Example

The following example opens the table STATES, then uses the Commit Table statement to make a copy of the states table under a new name (ALBERS). The optional CoordSys clause causes the ALBERS table to be saved using the Albers equal-area projection.

Open Table "STATES" 
Commit Table STATES
	As "ALBERS" 
	CoordSys Earth
	Projection 9,7, "m", -96.0, 23.0, 20.0, 60.0, 0.0, 0.0

The following example illustrates an ODBC connection:

dim hodbc as integer
hodbc = server_connect("ODBC", "dlg=1")
Open table "C:\MapInfo\USA"
Commit Table USA
as "c:\temp\as\USA"
	Type ODBC Connection hodbc Table "USA"

See Also:

ConvertDateTime Examples

Commit Table DATETIME90 As "D:\MapInfo\Data\Remote\DATETIME90CPY.TAB" 
Type ODBC Connection 1 Table """EAZYLOADER"".""DATETIME90CPY""" 
ConvertDateTime Interactive

Server 1 Create Table """EAZYLOADER"".""CITY_125AA""" (Field1 
Char(10),Field2 Char(10),Field3 Char(10),MI_STYLE Char(254)) KeyColumn 
SW_MEMBER ObjectColumn SW_GEOMETRY
or
Server 1 Create Table "EAZYLOADER.CITY_125AA" (Field1 Char(10),Field2 
Char(10),Field3 Char(10),MI_STYLE Char(254)) KeyColumn SW_MEMBER 
ObjectColumn SW_GEOMETRY
Commit Table City_125aa As 
"C:\Projects\Data\TestScripts\English\remote\City_125aacpy.tab" Type ODBC 
Connection 1 Table """EAZYLOADER"".""CITY_125AACPY"""
or
Commit Table City_125aa As 
"C:\Projects\Data\TestScripts\English\remote\City_125aacpy.tab" Type ODBC 
Connection 1 Table "EAZYLOADER.CITY_125AACPY"

Saving Linked Tables

Saving a linked table can generate a conflict, when another user may have edits the same data in the same table MapInfo Pro will detect if there were any conflicts and allows the user to resolve them. The following clauses let you control what happens when there is a conflict. (These clauses have no effect on saving a conventional MapInfo table.)

Interactive

In the event of a conflict, MapInfo Pro displays the Conflict Resolution dialog box. After a successful Commit Table Interactive statement, MapInfo Pro displays a dialog box allowing the user to refresh.

Interactive when invoked for Commit Table As, handles the case when a user is saving a query with one or more columns which are of indeterminate type. Using the Interactive parameter presents the user with a message indicating which column(s) contain the indeterminate type and allows the user to select new types and/or widths for these columns. If the Interactive parameter is not used, the system assigns a Char(254) type to the indeterminate type column(s) by default.

Example

Issue the following query in the SQL Select dialog box and click OK or type this query in the MapBasic window:

	Select Highway, objectinfo(obj, 20) from US_HIWAY into Selection

When you select select the current query and then click the Save Copy As button, the following error message displays:

Typically this dialog box contains a list of all columns that contain indeterminate types. In this query, there is only one.

Click OK to display the Set Field Properties dialog box.

Use this dialog box to select the type information for this column. If there is more than one indeterminate type, you can set each of these types one at a time. If there are columns whose type is already defined, you will not be able to edit that information.

Click OK to save your query.

Automatic NoCollision

In the event of a conflict, MapInfo Pro does not perform the save. (This is the default behavior if the statement does not include an Interactive clause or an Automatic clause.)

Automatic ApplyUpdates

In the event of a conflict, MapInfo Pro saves the local updates. (This is analogous to ignoring conflicts entirely.)

Automatic DiscardUpdates

In the event of a conflict, MapInfo Pro saves the local updates already in the RDBMS (discards your local updates). You can copy a linked table by using the As clause; however, the new copy is not a linked table and no changes are updated to the server.

ODBC Connections

The length of tablename varies with databases. We recommend 14 or fewer characters for a table name in order to work correctly for all databases. The statement limits the length of the tablename to a maximum of 31 characters.

If the As clause is used and ODBC is the Type, a copy of the table will be saved on the database specified by ConnectionNumber and named as tablename. If the source table is mappable, three more columns, Key column, Object column, and Style column, may be added to the destination database table, tablename, whether or not the source table has those columns. If the source table is not mappable, one more column, Key column, may be added to the database table, tablename, even if the source table does not have a Key column. The Key column will be used to create a unique index.

A spatial index will be created on the Object column if one is present. The supported databases include Oracle, SQL Server, IIS (SQL Server Spatial, Universal Server), and Microsoft Access. However, to save a table with a spatial geometry/object, (including saving a point-only table) SpatialWare is required for SQL Server, in addition to the spatial option for Oracle. The XY schema is not supported in this statement.

Saving a Non-Earth Table to a GeoPackage

You can use the MapBasic Commit Table statement to save a non-earth table to a GeoPackage. For this you must specify the CoordSys clause with specific bounds while issuing the statement.

Saving a MapInfo TAB (Native) or MapInfo Extended TAB (NativeX) File

When Saving a MapInfo *.tab (Native) or MapInfo Extended *.tab (NativeX) table to Access, DBF, SQLIte, or GeoPackage from MapInfo Pro, you cannot change the character set of the destination file. When saving to GeoPackage it is written in UTF-16 and for other formats it is saved in the current system character set by default.

If using a Commit Table statement in MapBasic and saving a MapInfo *.tab (Native) or MapInfo Extended *.tab (NativeX) table to DBF, Access, or Geopackage, you can provide an optional Charset clause. This Charset clause is set with the Type clause and saves to a specific character set. The Charset parameter requires a string constant, such as "WindowsLatin1". For more information, see the CharSet clause.

When there is no Type clause, MapBasic assumes the character set of the source table. Using an incompatible character set may cause the character data in the destination table to convert into underscores (such as WindowsCyrillic to WindowsLatin1). The preference is to convert to or from UTF-8 or UTF-16 character sets.

When there are no DBF and Table clauses, MapBasic uses the default character set for the hardware platform that is in use at runtime. The Commit Table statement does not support using UTF-8 or UTF-16 for DBF format files.