How to use the PHP from the database

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.4.8-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_extenv16 modules are located in the directory indicated by extension_dir in the php.ini file. Check the installation steps described in the previous section.
Make sure that both the php-5.x.y_sqlanywhere and php-5.x.y_sqlanywhere_extenv16 modules are listed in the Dynamic Extensions section of the php.ini file. Check the installation steps described in the previous section.
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, dblib16.dll, dbicu16.dll, dbicudt16.dll, dblgen16.dll, and dbextenv16.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, libdblib16_r, libdbicu16_r, libdbicudt16, dblgen16.res, and libdbextenv16_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 data types 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 '<?php 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 '<?php 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, 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 '<?php 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 more information and examples on using the PHP in the database support, refer to the examples located in the %SQLANYSAMP16%\SQLAnywhere\ExternalEnvironments\PHP directory.

Discuss this page in DocCommentXchange.