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 of 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.

Once the SQL Anywhere PHP external environment module has been built and installed, you can use the PHP in the database support. 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 the database server fails to start PHP, then the problem probably occurs because 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.

ALTER EXTERNAL ENVIRONMENT PHP 
  LOCATION 'php-path';

For example:

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

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.