SQL Interface 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.

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")

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.

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.

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.