Informix Functions

The Informix driver for Online (ODS) 7.x, SE 7.x and Universal Server (IUS) 9.x is implemented in "functions/ifx.ec" and "functions/php3_ifx.h". At the moment of writing ODS 7.2 support is fairly complete, with full BLOB support. IUS 9.1 support is partly finished: the new data types are there, but SLOBS support is still under construction.

Configuration notes

Before you run the "configure" script, make sure that the "INFORMIXDIR" variable has been set.

The configure script will autodetect the libraries and include directories, if you run "configure -- with_informix=yes". You can overide this detection by specifying "IFX_LIBDIR", "IFX_LIBS" and "IFX_INCDIR" in the environment. The configure script will also try to detect your Informix server version. It will set the "HAVE_IFX_IUS" conditional compilation variable if your Informix version >= 9.00.

Some notes on the use of BLOBs

The current version (September 18, 1998) has complete select/insert/update support for BLOB columns.

BLOBs are normally addressed by integer BLOB identifiers. Select queries return a "blob id" for every BYTE and TEXT column. You can get at the contents with "string_var = ifx_get_blob($blob_id);" if you choose to get the BLOBs in memory (with : "ifx_blobinfile(0);"). If you prefer to receive the content of BLOB columns in a file, use "ifx_blobinfile(1);", and "ifx_get_blob($blob_id);" will get you the filename. Use normal file I/O to get at the blob contents.

For insert/update queries you must create these "blob id's" yourself with "ifx_create_blob(..);". You then plug the blob id's into an array, and replace the blob columns with a question mark (?) in the query string. For updates/inserts, you are responsible for setting the blob contents with ifx_update_blob(...).

The behaviour of BLOB columns can be altered by configuration variables that also can be set at runtime :

configuration variable : ifx.textasvarchar configuration variable : ifx.byteasvarchar runtime functions :

ifx_textasvarchar(0) : use blob id's for select queries with TEXT columns ifx_byteasvarchar(0) : use blob id's for select queries with BYTE columns

ifx_textasvarchar(1) : return TEXT columns as if they were VARCHAR columns, without the use of blob id's for select queries.

ifx_byteasvarchar(1) : return BYTE columns as if they were VARCHAR columns, without the use of blob id's for select queries.

configuration variable : ifx.blobinfile runtime function :

ifx_blobinfile_mode(0) : return BYTE columns in memory, the blob id lets you get at the contents. ifx_blobinfile_mode(1) : return BYTE columns in a file, the blob id lets you get at the file name.

If you set ifx_text/byteasvarchar to 1, you can use TEXT and BYTE columns in select queries just like normal (but rather long) VARCHAR fields. Since all strings are "counted" in PHP3, this remains "binary safe". It is up to you to handle this correctly. The returned data can contain anything, you are responsible for the contents.

If you set ifx_blobinfile to 1, use the file name returned by ifx_get_blob(..) to get at the blob contents. Note that in this case YOU ARE RESPONSIBLE FOR DELETING THE TEMPORARY FILES

CREATED BY INFORMIX when fetching the row. Every new row fetched will create new temporary files for every BYTE column.

The location of the temporary files can be influenced by the environment variable "blobdir", default is "." (the current directory). Something like : putenv(blobdir=tmpblob"); will ease the cleaning up of temp files accidentally left behind (their names all start with "blb").

Automatically trimming "char" (SQLCHAR and SQLNCHAR) data

This can be set with a configuration variable :

ifx.charasvarchar : if set to 1 trailing spaces will be automatically trimmed

Informix Functions

ifx_connect

Name

ifx_connect — Open Informix server connection

Description

int ifx_connect(string [database] , string [userid] , string [password] );

Returns an connection identifier on success, or FALSE on error.

ifx_connect establishes a connection to an Informix server. All of the arguments are optional, and if they're missing, defaults are taken from values supplied in php3.ini (ifx.default_host for the host (Informix libraries will use $INFORMIXSERVER environment value if not defined), ifx.default_user for user, ifx.default_password for the password (none if not defined).

In case a second call is made to ifx_connect with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling ifx_close.

Example 1. Connect to a Informix database

$conn_id = ifx_pconnect (mydb@ol_srv1, "imyself", "mypassword");

ifx_pconnect

Name

ifx_pconnect — Open persistent Informix connection

Description

int ifx_pconnect(string [database] , string [userid] , string [password] );

Returns: A positive Informix persistent link identifier on success, or false on error

ifx_pconnect acts very much like ifx_connect with two major differences.

This function behaves exactly like ifx_connect when PHP is not running as an Apache module. First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (ifx_close will not close links established by ifx_pconnect).

This type of links is therefore called 'persistent'. See also: ifx_connect.

ifx_close

Name

ifx_close — Close Informix connection

Description

int ifx_close(int [link_identifier] );

Returns: always true.

ifx_close closes the link to an Informix database that's associated with the specified link identifier. If the link identifier isn't specified, the last opened link is assumed.

Note that this isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

ifx_close will not close persistent links generated by ifx_pconnect. See also: ifx_connect, and ifx_pconnect.

Example 1. Closing a Informix connection

$conn_id = ifx_connect (mydb@ol_srv, "itsme", "mypassword");

... some queries and stuff ... ifx_close($conn_id);

ifx_query

Name

ifx_query — Send Informix query

Description

int ifx_query(string query, int [link_identifier] , int [cursor_type] , mixed

[blobidarray] );

Returns: A positive Informix result identifier on success, or false on error.

An integer "result_id" used by other functions to retrieve the query results. Sets "affected_rows" for retrieval by the ifx_affected_rows function.

ifx_query sends a query to the currently active database on the server that's associated with the specified link identifier. If the link identifier isn't specified, the last opened link is assumed. If no link is open, the function tries to establish a link as if ifx_connect was called, and use it.

Executes query on connection conn_id. For "select-type" queries a cursor is declared and opened. The optional cursor_type parameter allows you to make this a "scroll" and/or "hold" cursor. It's a mask and can be either IFX_SCROLL, IFX_HOLD, or both or'ed together. Non-select queries are "execute immediate".

For either query type the number of (estimated or real) affected rows is saved for retrieval by

ifx_affected_rows.

If you have BLOB (BYTE or TEXT) columns in an update query, you can add a blobidarray parameter containing the corresponding "blob ids", and you should replace those columns with a "?" in the query text.

If the contents of the TEXT (or BYTE) column allow it, you can also use "ifx_textasvarchar(1)" and "ifx_byteasvarchar(1)". This allows you to treat TEXT (or BYTE) columns just as if they were ordinary (but long) VARCHAR columns for select queries, and you don't need to bother with blob id's.

With ifx_textasvarchar(0) or ifx_byteasvarchar(0) (the default situation), select queries will return BLOB columns as blob id's (integer value). You can get the value of the blob as a string or file with the blob functions (see below).

Example 1. Show all rows of the "orders" table as a html table

ifx_textasvarchar(1); // use "text mode" for blobs

$res_id = ifx_query("select * from orders", $conn_id); if (! $res_id) {

printf("Can't select orders : %s\n %s \n", ifx_error();

ifx_errormsg();

die;

}

ifx_htmltbl_result($res_id, "border=\"1\"); ifx_free_result($res_id);

Example 2. Insert some values into the "catalog" table

// create blob id's for a byte and text column

$textid = ifx_create_blob(0, 0, "Text column in memory");

$byteid = ifx_create_blob(1, 0, "Byte column in memory");

// store blob id's in a blobid array

$blobidarray[] = $textid;

$blobidarray[] = $byteid;

// launch query

$query = "insert into catalog (stock_num, manu_code, " . "cat_descr,cat_picture) values(1,'HRO',?,?)";

$res_id = ifx_query($query, $conn_id, $blobidarray); if (! $res_id) {

... error ...

}

// free result id

ifx_free_result($res_id);

ifx_prepare

Name

ifx_prepare — Prepare an SQL-statement for execution

Description

int ifx_prepare(string query, int conn_id, int [cursor_def], mixed

blobidarray);

Returns a integer result_id for use by ifx_do. Sets affected_rows for retrieval by the

ifx_affected_rows function.

Prepares query on connection conn_id. For "select-type" queries a cursor is declared and opened. The optional cursor_type parameter allows you to make this a "scroll" and/or "hold" cursor. It's a mask and can be either IFX_SCROLL, IFX_HOLD, or both or'ed together.

For either query type the estimated number of affected rows is saved for retrieval by

ifx_affected_rows.

If you have BLOB (BYTE or TEXT) columns in the query, you can add a blobidarray parameter containing the corresponding "blob ids", and you should replace those columns with a "?" in the query text.

ifx_do

Name

ifx_do — Execute a previously prepared SQL-statement

Description

int ifx_do(int result_id);

Returns TRUE on success, FALSE on error.

Executes a previously prepared query or opens a cursor for it. Does NOT free result_id on error.

Also sets the real number of ifx_affected_rows for non-select statements for retrieval by

ifx_affected_rows

See also: ifx_prepare. There is a example.

ifx_error

Name

ifx_error — Returns error code of last Informix call

Description

string ifx_error(void);

The Informix error codes (SQLSTATE & SQLCODE) formatted as follows : x [SQLSTATE = aa bbb SQLCODE=cccc]

where x = space : no error E : error

N : no more data W : warning

? : undefined

If the "x" character is anything other than space, SQLSTATE and SQLCODE describe the error in more detail.

See the Informix manual for the description of SQLSTATE and SQLCODE

Returns in a string one character describing the general results of a statement and both SQLSTATE and SQLCODE associated with the most recent SQL statement executed. The format of the string is "(char) [SQLSTATE=(two digits) (three digits) SQLCODE=(one digit)]". The first character can be ' ' (space) (success), 'W' (the statement caused some warning), 'E' (an error happened when executing the statement) or 'N' (the statement didn't return any data).

ifx_errormsg

Name

ifx_errormsg — Returns error message of last Informix call

Description

string ifx_errormsg(int [errorcode]);

Returns the Informix error message associated with the most recent Informix error, or, when the optional "errorcode" param is present, the error message corresponding to "errorcode".

ifx_affected_rows

Name

ifx_affected_rows — Get number of rows affected by a query

Description

int ifx_affected_rows(int result_id);

result_id is a valid result id returned by ifx_query or ifx_prepare. Returns the number of rows affected by a query associated with result_id.

For inserts, updates and deletes the number is the real number (sqlerrd[2]) of affected rows. For selects it is an estimate (sqlerrd[0]). Don't rely on it.

Useful after ifx_prepare to limit queries to reasonable result sets. See also: ifx_num_rows

Example 1. Informix affected rows

$rid = ifx_prepare ("select * from emp where name like " . $name, $connid); if (! $rid) {

... error ...

}

$rowcount = ifx_affected_rows ($rid); if ($rowcount > 1000) {

printf ("Too many rows in result set (%d)\n ", $rowcount); die ("Please restrict your query \n");

}

ifx_fetch_row

Name

ifx_fetch_row — Get row as enumerated array

Description

array ifx_fetch_row(int result_id, mixed [position] );

Returns an associative array that corresponds to the fetched row, or false if there are no more rows.

Blob columns are returned as integer blob id values for use in ifx_get_blob unless you have used ifx_textasvarchar(1) or ifx_byteasvarchar(1), in which case blobs are returned as string values. Returns FALSE on error

result_id is a valid resultid returned by ifx_query or ifx_prepare (select type queries only!).

[position] is an optional parameter for a "fetch" operation on "scroll" cursors: "NEXT", "PREVIOUS", "CURRENT", "FIRST", "LAST" or a number. If you specify a number, an "absolute" row fetch is executed. This parameter is optional, and only valid for scrollcursors.

ifx_fetch_row fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0.

Subsequent call to ifx_fetch_row would return the next row in the result set, or false if there are no more rows.

Example 1. Informix fetch rows

$rid = ifx_prepare ("select * from emp where name like " . $name,

$connid, IFX_SCROLL);

if (! $rid) {

... error ...

}

$rowcount = ifx_affected_rows($rid); if ($rowcount > 1000) {

printf ("Too many rows in result set (%d)\n ", $rowcount); die ("Please restrict your query \n");

}

if (! ifx_do ($rid)) {

... error ...

}

$row = ifx_fetch_row ($rid, "NEXT"); while (is_array($row)) {

for(reset($row); $fieldname=key($row); next($row)) {

$fieldvalue = $row[$fieldname];

printf ("%s = %s,", $fieldname, $fieldvalue);

}

printf("\n ");

$row = ifx_fetch_row ($rid, "NEXT");

}

ifx_free_result ($rid);

ifx_htmltbl_result

Name

ifx_htmltbl_result — Formats all rows of a query into a HTML table

Description

int ifx_htmltbl_result(int result_id, string [html_table_options]);

Returns the number of rows fetched or FALSE on error.

Formats all rows of the result_id query into a html table. The optional second argument is a string of <table> tag options

Example 1. Informix results as HTML table

$rid = ifx_prepare ("select * from emp where name like " . $name,

$connid, IFX_SCROLL);

if (! $rid) {

... error ...

}

$rowcount = ifx_affected_rows ($rid); if ($rowcount > 1000) {

printf ("Too many rows in result set (%d)\n ", $rowcount); die ("Please restrict your query \n");

}

if (! ifx_do($rid) {

... error ...

}

ifx_htmltbl_result ($rid, "border=\"2\""); ifx_free_result($rid);

ifx_fieldtypes

Name

ifx_fieldtypes — List of Informix SQL fields

Description

array ifx_fieldtypes(int result_id);

Returns an associative array with fieldnames as key and the SQL fieldtypes as data for query with

result_id. Returns FALSE on error.

Example 1. Fielnames and SQL fieldtypes

$types = ifx_fieldtypes ($resultid); if (! isset ($types)) {

... error ...

}

for ($i = 0; $i < count($types); $i++) {

$fname = key($types);

printf("%s :\t type = %s\n", $fname, $types[$fname]); next($types);

}

ifx_fieldproperties

Name

ifx_fieldproperties — List of SQL fieldproperties

Description

array ifx_fieldproperties(int result_id);

Returns an associative array with fieldnames as key and the SQL fieldproperties as data for a query with

result_id. Returns FALSE on error.

Returns the Informix SQL fieldproperies of every field in the query as an associative array. Properties are encoded as: "SQLTYPE;length;precision;scale;ISNULLABLE" where SQLTYPE = the Informix type like "SQLVCHAR" etc. and ISNULLABLE = "Y" or "N".

Example 1. Informix SQL fieldproperties

$properties = ifx_fieldtypes ($resultid); if (! isset($properties)) {

... error ...

Name

ifxus_open_slob — Opens an slob object

Description

int ifxus_open_slob(long bid, int mode);

Opens an slob object. bid should be an existing slob id. Modes: 1 = LO_RDONLY, 2 = LO_WRONLY, 4 = LO_APPEND, 8 = LO_RDWR, 16 = LO_BUFFER, 32 = LO_NOBUFFER -> or-

mask. Returns FALSE on error otherwise the new slob object-id.

ifxus_tell_slob

Name

ifxus_tell_slob — Returns the current file or seek position

Description

int ifxus_tell_slob(long bid);

Returns the current file or seek position of an open slob object bid should be an existing slob id. Return FALSE on error otherwise the seek position.

ifxus_seek_slob

Name

ifxus_seek_slob — Sets the current file or seek position

Description

int ifxus_seek_blob(long bid, int mode, long offset);

Sets the current file or seek position of an open slob object. bid should be an existing slob id. Modes: 0

= LO_SEEK_SET, 1 = LO_SEEK_CUR, 2 = LO_SEEK_END and offset is an byte offset. Return FALSE on error otherwise the seek position.

ifxus_read_slob

Name

ifxus_read_slob — Reads nbytes of the slob object

Description

int ifxus_read_slob(long bid, long nbytes);

Reads nbytes of the slob object. bid is a existing slob id and nbytes is the number of bytes zu read. Return FALSE on error otherwise the string.

ifxus_write_slob

Name

ifxus_write_slob — Writes a string into the slob object

Description

int ifxus_write_slob(long bid, string content);

Writes a string into the slob object. bid is a existing slob id and content the content to write. Return FALSE on error otherwise bytes written.