SQL Anywhere includes support for PHP stored procedures and functions. A PHP stored procedure or function behaves the same as a SQL stored procedure or function with the exception that the code for the procedure or function is written in PHP and the execution of the procedure or function takes place outside the database server (that is, within a PHP executable instance). There is a separate instance of the PHP executable for each connection that uses PHP stored procedures and functions. This behavior is quite different from Java stored procedures and functions. In the case of Java, there is once instance of the Java VM for each database rather than one instance per connection. The other major difference between PHP and Java is that PHP stored procedures do not return result sets, whereas Java stored procedures can return result sets. PHP only returns an object of type LONG VARCHAR, which is the output of the PHP script.

There are two prerequisites to using PHP in the database support:

In addition to the above two prerequisites, the database administrator must also install the SQL Anywhere PHP External Environment module. Prebuilt modules for several versions of PHP are included with the SQL Anywhere distribution. To install prebuilt modules, copy the appropriate driver module to your PHP extensions directory (which can be found in php.ini). On Unix, you can also use a symbolic link.

To install the external environment module (Windows)

1. Locate the php.ini file for your PHP installation, and open it in a text editor. Locate the line that specifies the location of the extension_dir directory. If extension_dir is not set to any specific directory, it is a good idea to set it to point to an isolated directory for better system security.

2. Copy the desired external environment PHP module from the SQL Anywhere installation directory to your PHP extensions directory. The following is a model to use:

   copy install-dir\Bin32\php-5.2.6_sqlanywhere_extenv11.dll php-dir\ext

3. Make sure that you have also installed the SQL Anywhere PHP driver from the SQL Anywhere installation directory into your PHP extensions directory. This file name follows the pattern php-5.x.y_sqlanywhere.dll where x and y are the version numbers. It should match the version numbers of the file that you copied in step 2.

To install the external environment module (Unix)

1. Locate the php.ini file for your PHP installation, and open it in a text editor. Locate the line that specifies the location of the extension_dir directory. If extension_dir is not set to any specific directory, it is a good idea to set it to point to an isolated directory for better system security.

2. Copy the desired external environment PHP module from the SQL Anywhere installation directory to your PHP installation directory. The following is a model to use:

   cp install-dir/bin32/php-5.2.6_sqlanywhere_extenv11.so php-dir/ext

3. Make sure that you have also installed the SQL Anywhere PHP driver from the SQL Anywhere installation directory into your PHP extensions directory. This file name follows the pattern php-5.x.y_sqlanywhere.so where x and y are the version numbers. It should match the version numbers of the file that you copied in step 2.

PHP in the database support is only available with SQL Anywhere version 11 or later databases. If a SQL Anywhere 10 database is loaded, then an error indicating that external environments are not supported is returned when you try to use the PHP in the database support.

To use PHP in the database, the database server must be able to locate and start the PHP executable. You can verify if the database server is able to locate and start the PHP executable by executing the following statement:

START EXTERNAL ENVIRONMENT PHP;

If you see a message that states that 'external executable' could not be found, then the problem is that the database server is not able to locate the PHP executable. In this case, you should execute an ALTER EXTERNAL ENVIRONMENT statement to explicitly set the location of the PHP executable including the executable name or you should ensure that the PATH environment variable includes the directory containing the PHP executable.

ALTER EXTERNAL ENVIRONMENT PHP 
  LOCATION 'php-path';

For example:

ALTER EXTERNAL ENVIRONMENT PHP
  LOCATION 'c:\\php\\php-5.2.6-win32\\php.exe';

To restore the default setting, execute the following statement:

ALTER EXTERNAL ENVIRONMENT PHP
  LOCATION 'php';

If you see a message that states that 'main thread' could not be found, then check for the following:

• Make sure that both the php-5.x.y_sqlanywhere and php-5.x.y_sqlanywhere_extenv11 modules are located in the directory indicated by extension_dir. Check the installation steps described above.
• Make sure that phpenv.php can be located. Check that the SQL Anywhere bin32 folder is in your PATH.
• For Windows, make sure that the 32-bit DLLs (dbcapi.dll, dblib11.dll, dbicu11.dll, dbicudt11.dll, dblgen11.dll, and dbextenv11.dll) can be located. Check that the SQL Anywhere bin32 folder is in your PATH.
• For Linux, Unix, and Mac OS X make sure that the 32-bit shared objects (libdbcapi_r, libdblib11_r, libdbicu11_r, libdbicudt11, dblgen11.res, and libdbextenv11_r) can be located. Check that the SQL Anywhere bin32 folder is in your PATH.
• Make sure that the environment variable PHPRC is not set, or make sure that it points to the version of PHP that you intend to use.

The START EXTERNAL ENVIRONMENT PHP statement is not necessary other than to verify that the database server can start PHP. In general, making a PHP stored procedure or function call starts PHP automatically.

Similarly, the STOP EXTERNAL ENVIRONMENT PHP statement is not necessary to stop an instance of PHP since the instance automatically goes away when the connection terminates. However, if you are completely done with PHP and you want to free up some resources, then the STOP EXTERNAL ENVIRONMENT PHP statement releases the PHP instance for your connection.

Once you have verified that the database server can start the PHP executable, the next thing to do is to install the necessary PHP code into the database. Do this by using the INSTALL statement. For example, you can execute the following statement to install a particular PHP script into the database.

INSTALL EXTERNAL OBJECT 'php-script' 
  NEW 
  FROM FILE 'php-file'
  ENVIRONMENT PHP;

PHP code can also be built and installed from an expression as follows:

INSTALL EXTERNAL OBJECT 'php-script' 
  NEW
  FROM VALUE 'php-statements'
  ENVIRONMENT PHP;

PHP code can also be built and installed from a variable as follows:

CREATE VARIABLE PHPVariable LONG VARCHAR;
SET PHPVariable = 'php-statements';
INSTALL EXTERNAL OBJECT 'php-script' 
  NEW 
  FROM VALUE PHPVariable
  ENVIRONMENT PHP;

To remove PHP code from the database, use the REMOVE statement as follows:

REMOVE EXTERNAL OBJECT 'php-script';

To modify existing PHP code, you can use the UPDATE clause of the INSTALL statement as follows:

INSTALL EXTERNAL OBJECT 'php-script' 
  UPDATE 
  FROM FILE 'php-file'
  ENVIRONMENT PHP;

INSTALL EXTERNAL OBJECT 'php-script' 
  UPDATE 
  FROM VALUE 'php-statements'
  ENVIRONMENT PHP;

SET PHPVariable = 'php-statements';
INSTALL EXTERNAL OBJECT 'php-script' 
  UPDATE 
  FROM VALUE PHPVariable
  ENVIRONMENT PHP;

Once the PHP code is installed in the database, you can then go ahead and create the necessary PHP stored procedures and functions. When creating PHP stored procedures and functions, the LANGUAGE is always PHP and the EXTERNAL NAME string contains the information needed to call the PHP subroutines and for returning OUT parameters.

The arguments are passed to the PHP script in the $argv array, similar to the way PHP would take arguments from the command line (that is, $argv[1] is the first argument). To set an output parameter, assign it to the appropriate $argv element. The return value is always the output from the script (as a LONG VARCHAR).

A PHP stored procedure can be created with any set of datatypes for input or output arguments. However, the parameters are converted to and from a boolean, integer, double, or string for use inside the PHP script. The return value is always an object of type LONG VARCHAR. A simple PHP example follows:

INSTALL EXTERNAL OBJECT 'SimplePHPExample' 
  NEW 
  FROM VALUE '<? function SimplePHPFunction(
    $arg1, $arg2, $arg3, $arg4 ) 
    { return ($arg1 * 1000) + 
      ($arg2 * 100) + 
      ($arg3 * 10) + 
      $arg4; 
    } ?>'
  ENVIRONMENT PHP;

CREATE FUNCTION SimplePHPDemo( 
  IN thousands INT, 
  IN hundreds INT, 
  IN tens INT, 
  IN ones INT) 
RETURNS LONG VARCHAR
EXTERNAL NAME '<file=SimplePHPExample> print SimplePHPFunction(
    $argv[1], $argv[2], $argv[3], $argv[4]);' 
LANGUAGE PHP;

// The number 1234 should appear
SELECT SimplePHPDemo(1,2,3,4);

For PHP, the EXTERNAL NAME string is specified in a single line of SQL.

To use server-side PHP, the PHP code can use the default database connection. To get a handle to the database connection, call sasql_pconnect with an empty string argument ('' or ""). The empty string argument tells the SQL Anywhere PHP driver to return the current external environment connection rather than opening a new one. The following example creates a table and then calls a PHP stored procedure to populate the table:

CREATE TABLE phpTab(c1 int, c2 char(128));

INSTALL EXTERNAL OBJECT 'ServerSidePHPExample' 
  NEW 
  FROM VALUE '<? function ServerSidePHPSub() { 
    $conn = sasql_pconnect( '''' ); 
    sasql_query( $conn,
    "INSERT INTO phpTab 
       SELECT table_id, table_name FROM SYS.SYSTAB" );
    sasql_commit( $conn ); 
  } ?>'
  ENVIRONMENT PHP;

CREATE PROCEDURE PHPPopulateTable() 
EXTERNAL NAME '<file=ServerSidePHPExample> ServerSidePHPSub()'
LANGUAGE PHP;

CALL PHPPopulateTable();

// The following should return 2 identical rows
SELECT count(*) FROM phpTab 
UNION ALL 
SELECT count(*) FROM SYS.SYSTAB;

For PHP, the EXTERNAL NAME string is specified in a single line of SQL. In the above example, note that the single quotes are doubled-up because of the way quotes are parsed in SQL. If the PHP source code was in a file, then the single quotes would not be doubled-up.

To return an error back to the database server, throw a PHP exception. The following example shows how to do this.

CREATE TABLE phpTab(c1 int, c2 char(128));

INSTALL EXTERNAL OBJECT 'ServerSidePHPExample' 
  NEW 
  FROM VALUE '<? function ServerSidePHPSub() {
    $conn = sasql_pconnect( '''' );
    if( !sasql_query( $conn,
      "INSERT INTO phpTabNoExist
         SELECT table_id, table_name FROM SYS.SYSTAB" )
    ) throw new Exception(
      sasql_error( $conn ),
      sasql_errorcode( $conn )
    );
    sasql_commit( $conn );
  } ?>'
  ENVIRONMENT PHP;

CREATE PROCEDURE PHPPopulateTable()
  EXTERNAL NAME
    '<file=ServerSidePHPExample> ServerSidePHPSub()'
  LANGUAGE PHP;

CALL PHPPopulateTable();

The above example should terminate with error SQLE_UNHANDLED_EXTENV_EXCEPTION indicating that the table phpTabNoExist could not be found.

For additional information and examples on using the PHP in the database support, refer to the examples located in the samples-dir\SQLAnywhere\ExternalEnvironments\PHP directory.