# SQL Interface Objects

Legacy SQL Objects |

The following legacy objects are still available for use:

| odbsql | Legacy open database connectivity object. Use db_odb for improved functionality.
| ocisql | Legacy object to access a Oracle database file. Use db_oci for improved functionality.
| db2sql | Legacy object to access a DB2 database file. Use db_db2 for improved functionality.

The methods and properties from the legacy objects remain compatible with the new objects. Methods are organized according to functionality: Connectivity, Table Operations, Column Operations and Index Operations.

For descriptions of subordinates to the primary SQL database objects, see Subordinate SQL Objects.

Properties | Description
ConnectOption$ | (Read Only) Current connect options. Example: print sql'ConnectOption$ USER=guest;PSWD=******
LastMethodErrMsg$ | (Read Only) Contains reason why last executed method failed.
LastOsErrorMsg$ | (Read Only) Contains PxPlus MSG(-1) of the last error.
LastPvxErr | (Read Only) Contains last encountered PxPlus error.
LastSqlStatment$ | (Read Only) Contains last SQL statement that caused the method to fail.

## Connectivity

The methods are:

Methods - Connectivity | Description
ClearConnectOption( ) | Clear all connect options.
Connect(sid$ | dsn$) | If odbcsql, then name of the ODBC database DSN If ocisql, then Oracle system ID of file to open. If not supplied, then the value of the environment variable ORACLE_SID is used. If db2sql, then you supply the name of a DB2 database. #### Note: During the connect method, all options set in ConnectOption will be passed to the Connect statement. If a table name is set during the connect, then all update, rename, delete, add and modify methods will not be allowed.
ReturnConnectOption(opt$) ReturnConnectOption(opt$, sep$) | Return list of all the value(s) set by this connect option. The default separator is a Hex$8A$; however, you can specify the sep$ to be used.
SetConnectOption(opt$, val$) | Set, modify and remove the OPEN options for connecting to an Oracle server, PxPlus SQL ODBC Driver, or DB2 server Example: sql'SetConnectOption("USER","guest") - Sets option sql'SetConnectOption("USER","user1") - Modifies option sql'SetConnectOption("USER","") - Removes this option only #### Note: Options that may have more than one value must be set individually. sql'SetConnectOption("KEY","Name") - Sets option sql'SetConnectOption("KEY","Company") - Adds another KEY option
SetConnectString(val$) | Set all the connect options with one statement. Example: sql'SetConnectString("USER=scott;PSWD=foobar;KEY=Name;KEY=ID")

## Table Operations

The methods are:

Methods - Table Operations | Description
AddTable(obj) | Add new table based on the information supplied by the table object. If the database connection rules require at least one column, then a dummy column will be created (i.e. Default Column + Table Objects Name). This dummy column will be type char.
AddTable(obj, colobjs$) AddTable(obj, colobjs$, idxobjs$) | Add new table based on the information supplied by the table object. Add each column based on the comma-separated list of column objects passed in. Add each index based on the comma-separated list of index objects passed in. Example: colobjs$=(100002,100003,100004) idxobjs$=(100005) Sql'AddTable(obj, colobjs$, idxobjs$)
DropTable(table$) | Delete table and all its related information.
GetIOList$(table$) | Return tables IOList.
GetTableCount( ) | Return number of tables presently found.
GetTableInfo(table$) | Return table information object containing information about the table. This object is static and will be reused on all subsequent calls to this method. It will be released when the database object is released.
GetTableList$(sep$) | Return list of all the tables presently found in the currently set connection. The default separator is a Hex $8A$; however, you can specify the sep$ to be used.
RenameTable(oldname$, newname$) | Rename the table.

## Column Operations

The methods are:

Methods - Column Operations | Description
AddColumn(table$, obj) | Add new column based on the description supplied by the column object. #### Note: No control is given over the column number that will be assigned to the column or its placement in the table.
DropColumn(table$, colnum | colname$) | Delete data column and its information from the table.
GetColumnCount(table$) | Return total number of data columns present in the specified table.
GetColumnInfo(table$, colnum | colname$) | Return column object containing information about the data column. This object is static and will be reused on all subsequent calls to this function. It will be released when the database object is released. You can pass either a data column number or name. The name may contain a trailing $.
GetColumnList$(table$, sep$) | Return a list of all data column names for the specified table. The default separator is a Hex$8A$; however, you can specify the sep$ to be used.
GetColumnName$(table$, colnum) | Return queried data column's name, which is type independent. Strings will not have a trailing $.
GetColumnNo(table$, colname$) | Return queried data column's number.
GetColumnVariable$(table$, colnum | colname$) | Return queried data column's variable name. String variables will return the variable with a trailing $.
RenameColumn(table$, oldname$, newname$) | Rename the column.
UpdateColumn(table$, obj) | Update column information based on the changes occurred by the column object.

## Index Operations

The methods are:

#### Note:
If a key was set in the Connect option, then only this information will be returned.

Methods - Index Operations | Description
AddIndex(table$, obj) | Add new index to the table based on the information in the index object. The new index will be assigned the next highest index number.
DropIndex(table$, indexnum | indexname$) | Delete index and its information from the table.
GetIndexCount(table$) | Return total number of indexes present on the specified table.
GetIndexDescription$(table$, indexnum | indexname$) | Return list of column descriptions that were used to make up the index, each separated by a + (plus sign). If no description exists, then the column name will be used.
GetIndexInfo(table$, indexnum | indexname$) | Return index object containing information about the index. This object is static and will be reused on all subsequent calls to this function. It will be released when the object is released. You can pass either an index number or name.
GetIndexList$(table$, sep$) | Return a list of indexes for the specified tables. If the index does not have a name, then a # followed by the index number will be in the list. The default separator is a Hex$8A$; however, you can specify the sep$ to be used.
GetIndexName$(table$, indexnum) | Return name of the specified index. If the index does not have a name, then a # followed by the index number will be returned.
RenameIndex(table$, oldname$, newname$) | Rename index.
UpdateIndex(table$, obj) | Update index information based on the changes occurred by the index object.