SQL::TableInfo Object

A SQL::TableInfo object (a table description) is a container for one or more SQL::DataTypeInfo objects (column descriptions) and SQL::IndexInfo objects (index descriptions).

Many of the SQL functions either ask you to provide a SQL::TableInfo object, or will create one for you. Using this object, it is possible to retrieve a description of a table from one database and construct a copy of that table in the same or another database. The SQL::TableInfo object is required for import/export and cross-sizing functions.

Example

dim conn as SQL::Connection

dim ti as SQL::TableInfo

dim connString as C

connString = "{A5API='Access', FileName='c:\program files\a5v8\mdbfiles\alphasports.mdb'}"

if .not. conn.open(connString)

ui_msg_box("Error", conn.CallResult.text)

end

end if

if .not. conn.GetTableInfo(ti, "customer")

ui_msg_box("Error", conn.CallResult.text)

conn.close()

end

end if

ui_msg_box("Table Information", ti.xml)

conn.close()

Note : as of the writing of this help topic, it is impossible to DIM an array of objects based on a class. The following code sample shows a work-around.

This example creates an array containing five SQL::IndexInfo objects.

dim ind as SQL::IndexInfo

dim arr[5] as P

arr[1] = iind

for i = 2 to 5

arr[i] = ind.clone()

next i

Properties

The SQL::TableInfo object has the following properties.

Property	Type	Mode	Description
CanUpdateInTransaction	L	R	If a table has a large text or binary column and the database can not cache updates for multiple rows, this value will be false.
Column	P	R	An array of index column ( SQL::IndexColumnInfo ) definitions.
Comment	C	R	The comment stored for the table, if the database supports it.
DBFRowSyntax	C	R	The syntax required to create an Alpha Five .DBF with the same columns.
IdentityColumnSubscript	N	R	Returns the index of the column which is auto-increment or identity (-1 if none).
Index	P	R	An array of index ( SQL::IndexInfo ) definitions.
LastKnownRowCount	N	R	Set by calling .UpdateStatistics().
Name	C	R	The unique name of the table. The table must be unique for the owner, but need not be globally unique.
NativeAPI	C	R	The name of the API active when this object was created.
NativeSyntax	C	R	The name of the syntax at the time the object was created.
Owner	C	R	The database user or group that owns the table.
PrimaryKeySubscript	N	R	The subscript value to use when referencing the Index property to access the primary key index. If there is no primary key defined, this property will have a value of -1. If the value is an integer greater than or equal to 1, the primary key may be accessed using the syntax Index[PrimaryKeySubscript].
QueryAlias	C	RW	This is an advanced property used to get more information about a column.
SelectRowLimit	N	RW	Defaults to -1 (no limit). This property is used when generating select statements to limit the number of rows retrieved.
SizeToFit	L	RW	When set to true, . DBFRowSyntax will return lengths adjusted based on the actual data. Notes: Call .UpdateStatistics() before getting the DBFRowSyntax. If the longest column is zero length, the default length is 15. .SizeToFit will limit the size of inserted or updated fields on imported or linked tables to the maximum size of the previous values.
TableType	SQL::TableType	R	An enumerated type describing the table type.
TableTypeName	C	R	The displayable name of the enumeration value in TypeName.
UniqueName	C	R	A concatenation of the owner (if any) and name properties.
Updateable	L	R	True (.T.) if the table is updateable.
XML	C	RW	The schema in XML format.

Methods

The SQL::TableInfo object has the following methods.

Method	Description
::AddColumn()	Adds a new column to the .column property.
::AddIndex()	Adds a new index to the .index property.
::Clear()	Clears out the SQL::TableInfo object as if it had just been created.
::Clone()	Returns a copy of a SQL::TableInfo object.
::ColumnNumber()	Gets the subscript index of the requested column name in the .column array property.
::IndexNumber()	Gets the subscript of the requested index number in the .index array property.
::InsertColumn()	Add a new column to the .column property.
::UpdateStatistics()	Gather information on the table for the connection.

QueryAlias Property

The QueryAlias property is only populated when a query object ( SQL::Query, SQL::Statement, etc.) is validated. After calling SQL::Query::Validate, the query object has a SQL::ResultSet object (no data, just definitions). The TableInfo property of the SQL::ResultSet is an array of SQL::TableInfo objects that describe the tables in the FROM clause (this is used by the query genie to map select list columns to the source table definition to get more information about the actual column.

dim q as sql::query

dim c as sql::connection

?c.open("{a5api=access,filename=c:\northwind.mdb,user=admin}")

= .T.

?q.parse("select * from customers cust")

= .T.

?q.validate(c)

= .T.

?q.resultset.tableinfo[1].queryalias

= "cust"

TableTypeName Property

This is the displayable name of the enumeration value in TypeName.

? tt.TableType

= -1

? tt.TableTypeName

= "Invalid"

tt.tabletype = View

? tt.TableType

= 1

? tt.TableTypeName

= "View"