A PHP stored procedure or function behaves the same as a SQL stored procedure or function except 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. For Java, there is one 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.
In addition to the above two prerequisites, the database administrator must also install the PHP External Environment module. Prebuilt modules for several versions of PHP are included. 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.
copy "%SQLANY12%\Bin32\php-5.x.y_sqlanywhere_extenv12.dll" php-dir\ext
extension=php-5.x.y_sqlanywhere_extenv12.dll
Save and close php.ini.
cp $SQLANY12/bin32/php-5.x.y_sqlanywhere_extenv12.so php-dir/ext
extension=php-5.x.y_sqlanywhere_extenv12.so
Save and close php.ini.
START EXTERNAL ENVIRONMENT PHP;
ALTER EXTERNAL ENVIRONMENT PHP LOCATION 'php-path';
ALTER EXTERNAL ENVIRONMENT PHP LOCATION 'c:\\php\\php-5.2.6-win32\\php.exe';
ALTER EXTERNAL ENVIRONMENT PHP LOCATION 'php';
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.
INSTALL EXTERNAL OBJECT 'php-script' NEW FROM FILE 'php-file' ENVIRONMENT PHP;
INSTALL EXTERNAL OBJECT 'php-script' NEW FROM VALUE 'php-statements' ENVIRONMENT PHP;
CREATE VARIABLE PHPVariable LONG VARCHAR; SET PHPVariable = 'php-statements'; INSTALL EXTERNAL OBJECT 'php-script' NEW FROM VALUE PHPVariable ENVIRONMENT PHP;
REMOVE EXTERNAL OBJECT 'php-script';
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).
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.
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, 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.
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.