SQL::DataTypeInfo Object

The SQL::DataTypeInfo object description describes a column in a SQL::ResultSet or an element in a SQL::Row. The object includes the name, size, precision, Alpha Five type, the native type, and an intermediate type (see SQL::IntermediateType below) which contains more precise type information. This intermediate type makes it possible to recreate a table using the closest possible matching type in the target table.

Example

The following script retrieves data into a SQL::ResultSet, extracts a SQL::Row with SQL::ResultSet.NextRow(), then lists the properties of one of the row's SQL::DataTypeInfo objects.

dim conn as SQL::Connection

dim rs as SQL::ResultSet

dim cr as SQL::Row

dim dti as SQL::DataTypeInfo

dim connString as C

dim select_exp as C

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

select_exp = "select Lastname from customer where bill_state_region = 'ma'"

if .not. conn.open(connString)

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

end

end if

if .not. conn.execute(select_exp)

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

conn.close()

end

end if

rs = conn.ResultSet

rs.NextRow()

cr = rs.CurrentRow

dti = cr.ColumnInfo[1]

ui_msg_box("Data Type Info", dti.xml)

conn.close()

This example in the Interactive Window shows the use of the .HTMLEscapeFormattedData property.

dim c as SQL::connection

dim ti as SQL::tableinfo

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

c.execute("select * from categories")

ti = c.resultset. ToTableInfo()

ti.column[3].HTMLEscapeFormattedData = .t.

c.ToJSON("select * from categories", -1, 1, ti)

Properties

The SQL::DataTypeInfo object has the following properties.

Property	Type	Mode	Description
AlphaType	C	R	Default = "". The type of the column as specified with Xbasic.
AlphaTypeName	C	R	Default = "Invalid". A printable description of the AlphaType.
AutoGenerate	L	R	Default = FALSE (.F).
AutoIncrement	L	R	Default = FALSE (.F).
CanBeUsedInWhereClause	L	R	Default = TRUE (.T). If TRUE, the column can be used in a WHERE clause. Some binary and long text columns are not permitted to be used in a where clause for some databases.
Comment	C	R	Default = "". The comment stored for the column, if the database supports it.
CompressedUnicode	L	R	Default = FALSE (.F). Data is stored on native platform as compressed Unicode strings.
DataTransformExpression	C	R	Default = "". An Xbasic expression to be evaluated when retrieving data values from a SQL::ResultSet or a SQL::Row. The data is transformed each time the column is referenced using the Data function.
DBFDecimals	N	R	Default = 0. The number of decimal places to be used in specifying an Alpha Five .DBF.
DBFLength	N	R	Default = 0. The length required for a column specification. See .SizeToFit.
DBFName	C	R	Default = "". The name of the column with any reformatting required to make it a valid DBF column name.
DBFType	C	R	Default = "". The column type to be used in specifying an Alpha Five .DBF.
DBFColumnSyntax	C	R	The complete syntax to be used in specifying the column for an Alpha Five .DBF.
DecimalDigits	N	R	Default = 0. For numbers only. This is the number of digits to the right of the decimal point.
DefaultValue	C	R	Default = "". The initial value to be assigned.
DefaultValueName	C	R	Default = "". The name of the rule (constraint).
DisplayFormat	C	R	Default = "". Currently for numeric fields only, this value is used when calling FormatData on a SQL::ResultSet, SQL::Row, or SQL::DataTypeInfo.The formatting has the same effect as calling str()using the numeric value of the column and the properties DBFLength and DBFDecimals.
EmptyStringIsNull	L	R	Default = FALSE (.F). Set to TRUE if an empty string should be sent to the database as NULL.
ExternalFileNameExpression	C	RW	Default = "". An Xbasic expression to be used in formatting a filename to persist the column data to. When FormatData is called, the current value of this column is written to the file name that results from evaluating this expression.
FixedLength	L	R	Default = FALSE (.F). The column is fixed length.
HTMLEscapeFormattedData	L	RW	Default = FALSE (.F). If this property is set to true, the contents of the column will be formatted with *HTML_ESCAPE() before being returned to the caller.
HyperLink	C	R	Default = FALSE (.F). Hyperlink string.
Identity	L	R	Default = FALSE (.F). The identity column.
IdentitySeed	N	R	Default = 0. The initial value of a the set of identities.
IdentityIncrement	N	R	Default = 0. The value to increment the identity prior to insert.
IdentityReplicate	L	R	Default = TRUE (.T). Whether to copy this identity when doing replication to another table.
IdentitySequenceName	C	R	Default = "". This property applies to Oracle (and probably DB2 ) and is necessary to generate the correct SQL.
IntermediateType	SQL::IntermediateType	R	Default = -1. A product independent type definition.
IntermediateTypeName	C	R	Default = "Invalid". A printable description of the .IntermediateType.
ISAMNotLastColumn	L	R	Default = FALSE (.F). Jet Driver Specific
Length	N	R	Default = 0. The size of the field. For strings and blobs, this is the number of characters and bytes respectively. For numbers this is the total number of digits.
MaximumActualLength	N	R	Default = -1. Set by UpdateStatistics(). This value is the maximum actual length of character and binary columns. Otherwise it is set to zero.
Name	C	R	Default = "". The unique name of the column within the table.
NamedFormatExpression	C	R	Default = "". This property is a CR-LF delimited list of expressions, where each line is made up of a name followed by an equal sign and the Xbasic expression to execute. The Xbasic Expression is used to format the data when the named format is requested. The expression executes in a context that has the following predefined variables: CurrentRow as SQL::Row, ColumnDefinition as SQL::DataTypeInfo, Context as P. For example: Short=Left(CurrentRow.Data("Description")) Long=CurrentRow.Data("A")
NativeAPI	C	R	Default = "". The name of the API used (ODBC, MySQL, ADO, etc.).
NativeSyntax	C	R	Default = "". The name of the syntax used to interact with the database (usually the name of the vendor or database type).
NativeType	N	R	Default = 0. A database specific type which uniquely describes the data.
NativeTypeName	C	R	Default = "". A printable description of the .NativeType.
NativeValidationRule	C	R	Default = "". An expression describing the rule for validation in native syntax.
NativeValidationRuleName	C	R	Default = "". The name of the validation rule.
Nullable	L	R	True (.T.) if the column can be Null.
OneBlobPerPage	L	R	Default = FALSE (.F). Jet Driver Specific
SizeToFit	L	R	Default = FALSE (.F). When set to true, .DBFColumnSyntax and .DBFLength properties 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. Size to fit will limit the size of inserted or updated fields on imported or linked tables to the maximum size of the previous values.
SourceColumnName	C	R	Default = "". The column name in the table (not always available).
SourceTableName	C	R	Default = "". The table name (not always available).
SourceTableOwner	C	R	Default = "". The owner of the table (not always available).
SourceType	N	R	Default = 0.
SQLExpression	C	R	Default = "".
Updateable	L	R	Default = TRUE (.T). True (.T.) if this column is updateable.
ValidationText	C	R	Default = "". The message to display.
XML	C	RW	The schema in XML format.
ZeroFill	L	R	Default = FALSE (.F). Indicates if the field should initially be filled with zeroes.

Methods

The SQL::DataTypeInfo object has the following methods.

Method	Description
::Clone()	Makes a copy of the SQL::DataTypeInfo object and its contents.
::FormatData()	Returns a string representation of a data value in a result set row.