new

Allocates and initializes a dbChannel, which is the connection used to communicate with the database. You can open several dbChannels, each pointing to a different database or even the same database. If you open more than one dbChannel to the same database, however, you may find that you are deadlocked against yourself. This method corresponds to the VTXINIT call in VORTEXcli.

Parameters

maxLogicalCursors
Maximum number of logical cursors to allocate for the connection. A logical cursor is mapped to a real database cursor and lets an application reuse cursors using parameters. This approach minimizes the amount of work done to reuse a statement because it is more efficient than creating unique statements with parameter values hard-coded in the SQL statement. For best performance you should allocate one cursor for each SQL statement.

maxDbCursors
Number of actual database cursors to allocate. Logical cursors are automatically mapped to database cursors. The dbChannel keeps track of which cursors are in use and which are soft-closed (meaning that all results have been retrieved). If a logical cursor needs an database cursor and none are available, dbChannel looks for the first soft-closed cursor, marks it as hard-closed and gives its database cursor to the requesting logical cursor. Because database cursors are fairly expensive to allocate (database side), you should allocate fewer than the number of logical cursors.

maxColumns
Maximum number of output columns that any given SELECT statement can have.

fetchBufferSize
Size of the fetch buffer to allocate. When fetching data from the database the number of records to pre-fetch depends on the record size. The actual fetch buffer, which is per dbCursor, is not allocated until the fetch() is invoked. A good starting fetch buffer is 8192. If your application primarily reads large quantities of data sequentially, experiment with a larger buffer. For applications that execute random reads or reads where where it only uses the first few records, a smaller buffer size is actually faster.

Examples

Allocates a connection with an 8Kb fetch buffer.

$db = new dbChannel(256,64,128,8192);

dbConnect

Connects to a remote database. Returns false on success, true on failure. The complete connection syntax is described in the VORTEX Installation and Operations Manual available at www.trifox.com.

Parameters

hostName
Host to connect to. Can be in either the symbolic name format or the IP address dot notation. If the VORTEXserver (vtxnetd) requires authentication then you must append a userid and password to hostName: hostName(uid/pwd)

port
Port number on which VORTEXserver (vtxnetd) is listening.

hostProgram
Executable to service the connection. On server platforms that support DLLs this is the name of the DLL. See the VORTEX Installation and Operations Manual for more information.

dbConnectString
Connect string passed to the actual database.

envVariables
Optional environment variables to be set on the server; for example, database environment variables.

Examples

Connects to an Oracle database on a Unix machine.

$db->dbConnect(``orahost'',1958,``VTX0'', ``scott/tiger'', ``ORACLE_HOME=/usr/local/oracle,ORACLE_SID=A'');

Connects to a SQL Server on an NT machine.

$db->dbConnect(``sqlnt'',1958,``vtx12'',``sa/sa/master2/SQLNT'',``'');

Connects to a DB2 database on MVS with authentication.

$db->dbConnect(``sys1(SYS1/SYSPWD)'',1958,``7'',``/'',``'');

sql

Associates a SQL statement to a dbCursor.

This method simply caches the SQL statement and other information. If the dbCursor was previously used, its database cursor is hard closed and all resources are freed. The new SQL statement is not sent to the database until either a fetch() or execute() is performed. If you use parameters in your SQL, you do not have to call sql() again for this SQL statement. You merely use the setIntParam(), setCharParam(), and setBlobParam() methods to change the parameter values. This approach is much more efficient than creating unique SQL strings.

Parameters

cursor
dbCursor object.

sqlStatement
The SQL statement.

numDimension
If the SQL statement contains parameter markers (? or :n) then this parameter specifies how many ``sets'' of parameters exist. For a SELECT statement with parameters this must be 1. For INSERT/UPDATE/DELETE statements this parameter indicate how many records to process. If the SQL statement does not contain any parameters the value must be set to 0.

numParameters
If numDimension is not 0 then this parameter indicates the number of parameters in each ``set'' of parameters.

Examples

Prepares a SELECT statment with no parameters.

$db->sql($cursor,``select * from staff'',0,0);

Prepares a bulk INSERT statement with 7 parameters in batches of 50.

$db->sql($cursor,``insert into staff values (:1,:2,:3,:4,:5,:6,:7)'',50,7);

execute

Executes a non-SELECT dbCursor. Returns false on success, true on failure. You must bind all the parameters specified in the sql() method using either setCharParam(), setIntParam(), or setBlobParam(). The SQL statement is only sent to the database the first time. On subsequent calls only the parameters (if any) are sent.

Parameters

cursor
dbCursor object.

executeIO

Executes a stored procedure dbCursor. Returns false on success, true on failure. You must bind all the parameters specified in the sql method using setCharParam() or setIntParam(). Note that output parameters must set the flag parameter with setCharParam() and setIntParam. If the database's stored procedure facility supports a function return value, this value is located in the message buffer. Use sqlError() to retrieve the value. The SQL statement is only sent to the database the first time. On subsequent calls only the parameters (if any) are sent.

Parameters

cursor
dbCursor object.

cursor_close

Closes a dbCursor. Returns false on success, true on failure.

Parameters

cursor
dbCursor object.

hard
If true, performs a ``hard'' close, which sends a message to the database driver to close the cursor and release all associated resources. If not true, the cursor is ``soft'' closed. Hard close is not usually necessary and causes extra overhead. Use the soft close option whenever possible.

fetch

Opens and fetches from a SELECT dbCursor. Returns false on success, true on failure. You must bind all the parameters specified in the sql() method using setIntParam() and setCharParam(). The SQL statement is only sent to the database the first time. On subsequent calls only the parameters (if any) are sent.

Data is automatically retrieved into a fetch buffer (using the fetch buffer size specified when the dbChannel was created). The number of rows per fetch varies depending upon the total length of a row.

The column data must be retrieved sequentially using the getString() method.

Parameters

cursor
dbCursor object.

getString

Gets the next column as a string. Numeric and datetime data are automatically converted to a string representation. Returns ($errorcode,$string). Errorcode is false on success, true on failure.

Parameters

cursor
dbCursor object.

getSkip

Skips (passes over) columns. A large numColsToSkip with stopOnColZero set to true advances to the next row. Returns false on success, true on failure.

Parameters

cursor
dbCursor object.

numColsToSkip
Number of columns to skip over.

stopOnColZero
Never skip past column zero of the next row.

getRow

Gets the next row of data and return an array of data items. Returns ($errorcode,$row). Errorcode is false on success, true on failure.

Parameters

cursor
dbCursor object.

getStringIO

Gets the next stored procedure column as a string. Numeric data is automatically converted to a string representation. Returns ($errorcode,$string). Errorcode is false on success, true on failure.

Parameters

cursor
dbCursor object.

cancel

Cancels an outstanding request. This method is typically called after a user interrupt. Returns false on success, true on failure.

getRowsAffected

Gets the number of rows affected by the last execute() call. This value is valid only for INSERT, UPDATE, and DELETE statements. If you are performing a bulk operation that fails due to a uniqueness violation for example, getRowsAffected returns the number of rows that were successfully executed before the error.

command

Sends a database-specific command. A description of the supported commands is located in the VORTEXcli Reference Manual in the VTXCMD section.

Parameters

cursor
dbCursor object

cmd
Command identfier

value
Command value. The value is always passed as a string.

Example

Sets the resource timeout value to 5 seconds.

$db->command($cursor,TDB_CMD_TIMEOUT(),``5'');

commit

Commits a transaction.

Parameters

startUpdateTrans
If true, a begin transaction is performed immediately following the commit. All activity takes place on the server side which minimizes network traffic. Returns false on success, true on failure.

rollback

Rolls back a transaction.

Parameters

startUpdateTrans
If true then a begin transaction is performed immediately following the rollback. All activity takes place on the server side which minimizes the network traffic. Returns false on success, true on failure.

release2

Sends a temporary release, used with VORTEXweb. Returns false on success, true on failure.

synchronize

Resynchronize a cursor after a release2/connect. Used only with VORTEXweb. Returns false on success, true on failure.

Parameters

cursor
dbCursor object.

cursor number
cursor number as returned from the $dbCursor->save function.

cursor status
cursor status as returned from the $dbCursor->save function.

release

Releases a connected database. Returns false on success, true on failure. A rollback takes place before the actual database release request is sent.

sqlError

Returns the last SQL error code and message

putBlob

Sends BLOB data. The VORTEXperl blob/clob interface is rather involved. To bind a blob/clob parameter, you must first call setBlobParam() with the length of the blob/clob. Then you use putBlob to send the blob/clob parameter. Please refer to the sample3.pl program enclosed in the VORTEXperl download.

Parameters

cursor
dbCursor object

col
Blob column number

blob
Blob data

len
Blob length

diag

Turns on/off diagnostics messages.

Parameters

flag
1 - on, 0 - off