PHP and SQL/J Pertemuan 12

2. PHP and SQL/J Pertemuan 12 Matakuliah : T0413 Tahun : 2009

3. What is PHP? PHP is the most popular Web language in the world today: 70% of the UNIX-based, Apache Web platform market… 40% + of the overall market • PHP is an open source, platform independent scripting language that is designed for web application development • Originally created by Rasmus Lerdorf in 1995 • Set of Perl scripts for tracking accesses to his online resume • Original name was 'Personal Home Page Tools‘ • PHP 3.0 was a completed rewrite in 1997 • Number of PHP developers has been growing ever since • It is one of the most widely deployed web languages in the world today . • PHP means (PHP Hypertext Preprocessor)

4. Why PHP is popular ? Rapid, iterative development cycles with a low learning curve Robust, high-performance & scalable; stable & secure Easily integrated into heterogeneous environments/systems Proven through widespread deployment; vibrant community

5. PHP - Key driver of LAMP Stack • LAMP – Technical • Linux, Apache HTTP Server, MySQL, PHP/Perl/Python • Open Source web technology stack • Often available on ISPs for reasonable monthly fees • Pure open source software componentsLAMP – Business (Appealing on many levels) • Rapid iterative development • Low cost of software acquisition • Scalable and robust scalable LAMP solutions requires significant investment • PHP Concerns (CTOs) • Lack of Professional level PHP developer skills • Good software design skills are still important for large projects • Considerable investment in .NET or J2EE already • Many enterprises are not comfortable with MySQL

6. PHP extensions/drivers • ibm_db2 • Written, maintained and supported by IBM • Can only be used to access DB2 databases • Provides a procedural API with extensive access to the db metadata • Can be compiled with PHP 4 or PHP 5. • pdo_ibm • pdo_ibm is a driver for the PHP Data Objects (PDO) extension • Provides an Object Oriented database abstraction layer • Support starts with PHP 5.1 • PDO can work with many database vendors • ibm_db2 and pdo_ibm are based on the IBM DB2 CLI Layer • PHP drivers are offered under open-source licenses • http://www.php.net

7. Setting up the PHP environment manually (Windows) • Download the precompiled binary version of PHP code and PECL from www.php.net • Unzip the PHP zip file into <install directory> • Unzip the PECL zip file into <install directory> \ext subdirectory • Copy or rename php.ini-recommended file to php.ini • Add the following lines to php.ini: • To enable the ibm_db2 extension: extension=php_ibm_db2.dll • To enable the PDO extension and pdo_ibm driver: extension=php_pdo.dll extension=php_pdo_ibm.dll

8. Setting up the PHP environment manually (cont’d) • Add the following to the httpd.conf file (Apache HTTP Server 2.x) to enable PHP support: LoadModule php5_module ‘<install directory>/php5apache2.dll' AddType application/x-httpd-php .php PHPIniDir ‘<install directory>' • Restart the Apache HTTP Server for the changes to take effect.

9. Connecting to a database – ibm_db2 extension • db2_connect() • For non-persistent connection • Close with db2_close() or when a PHP script ends • db2_pconnect() • Persistent connection to a DB2 database • PHP keeps the connection open for subsequent PHP scripts ignoring calls to db2_close() • May be better for performance • Both return FALSE if unsuccessful connection • Use db2_conn_error() and db2_conn_errormsg() to retrieve diagnostic information • Both return not FALSE if the connection attempt succeeded

10. Connecting to a database - Example <?php $database = 'SAMPLE'; $user = 'rfchong'; $password = 'passwd'; $conn = db2_connect($database, $user, $password); if ($conn) { echo “Successful connection!"; db2_close($conn); } else { echo "Connection failed."; } ?> 1 2 3

11. Retrieving database metadata – ibm_db2 extension

12. Database Metadata example <?php$conn = db2_connect('sample', 'db2inst1', 'ibmdb2');$server = db2_server_info( $conn );if ($server) {    echo "DBMS_NAME: ";      var_dump( $server->DBMS_NAME );    echo "DBMS_VER: ";       var_dump( $server->DBMS_VER );    echo "DB_CODEPAGE: ";    var_dump( $server->DB_CODEPAGE );    echo "DB_NAME: ";        var_dump( $server->DB_NAME );    echo "INST_NAME: ";      var_dump( $server->INST_NAME );    echo "SPECIAL_CHARS: ";  var_dump( $server->SPECIAL_CHARS ); db2_close($conn);}?> 1 2 3

13. Executing statements – ibm_db2 extension • db2_exec() • For single statements, with no input parameters • If it returns FALSE, the statement failed: • Use db2_stmt_error() and db2_stmt_errormsg() for info about error • If it did NOT return FALSE, the statement was successful: • If the SQL statement selected rows using a scrollable cursor, or inserted, updated, or deleted rows, call db2_num_rows() to return the number of rows affected. • If the SQL statement returned a result set, you can fetch rows.

14. db2_exec example <?php$create = 'CREATE TABLE test (id INTEGER, name CHAR(30))'; $result = db2_exec($conn, $create); if ($result) { print "Successful table creation\n"; } ?> 1 2 3 Example with an XQuery statement: <?php $xquery = '$doc/customerinfo/phone'; $stmt = db2_exec($conn, "select xmlquery('$xquery' PASSING INFO AS \"doc\") from customer");?> … ?>

15. Preparing & executing statements – ibm_db2 extension • Use db2_prepare() and db2_execute • Use with parameter markers as input • Better performance than db2_exec() • May need to bind parameters with db2_bind_param() • Can use db2_fetch_assoc to fetch rows from a result set.

16. db2_execute example $sql = "SELECT lastname FROM emp WHERE bonus > ? AND bonus < ?"; $stmt = db2_prepare($conn, $sql); if (!$stmt) { // Handle errors } // Explicitly bind parameters db2_bind_param($stmt, 1, $_POST['lower']); db2_bind_param($stmt, 2, $_POST[‘upper']); db2_execute($stmt, array($_POST['lower‘],$_POST[‘upper’]); while ($row = db2_fetch_assoc($stmt)) { print "{$row['LASTNAME']}\n"; } 1 2 3 4 5

17. What is SQLJ? • SQLJ allows for embedding SQL statements into Java programs. • All SQL statements are run statically • SQLJ programming uses “contexts”. • A “Connection context” is equivalent to the Connection object in JDBC • A default connection context is used when no connection context is specified • An “Execution context” is required to get the information regarding the SQL statement before and after executing the statement.

18. SQLJ syntax • Different syntax to use: • #sql [connection-context] { sql statement } • #sql [connection-context, execution context] { sql statement } • #sql { sql statement } • #sql [execution context] { sql statement } • With this syntax, a precompiler can identify the statements to translate: Start with “#sql” and use curly brackets as delimiters. • Host variables can be used and identified by a colon. Eg: #sql { SELECT EMPNO FROM EMP WHERE WORKDEPT = :dept};

19. Connection context Connection context example: #sql context ctx; // This should be outside the class Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); ctx ctx1 = new ctx(“jdbc:db2:sample”,false); #sql [ctx1] { DELETE FROM dept }; 1 2 3 4 Connection context from Connection object example: #sql context ctx; // This should be outside the class Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); Connection con=DriverManager.getConnection(); ctx ctx1 = new ctx(con); #sql [ctx1] { DELETE FROM dept }; 5 6

20. Default context Connection with default context example: Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); Connection con = DriverManager.getConnection(); DefaultContext ctx1 = new DefaultContext(con); DefaultContext.setDefaultContext(ctx1); #sql { DELETE FROM dept }; 1 2 3

21. Execution context • Within a connection context object, execute any SQL statement. • Use the ExecutionContext class to monitor and control the SQL statements while executing. Equivalent to the JDBC “statement” interface. • Create an ExecutionContext objext with the getExecutionContext method of the connection context. • Some ExecutionContext methods work before an SQL statement is executed while others apply only after execution.

22. Execution context #sql context ctx; // this should be outside the class String url = "jdbc:db2:sample"; Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); Connection con=DriverManager.getConnection(url); ctx ctx1=new ctx(con); ExecutionContext exectx1 = ctx1.getExecutionContext(); #sql[ctx1,exectx1] = { DELETE FROM purchaseorder WHERE status='UnShipped'} int i = exectx1.getUpdateCount(); 1 2 3

23. Iterators • Iterators are equivalent to a JDBC result set • Two types of iterators: • Named iterators • Identify a row by the name of the column in the result set. • While defining the named iterator, specify the name of the columns and their data types • Position iterators • Identify a row by its position in the result set. • While defining the position iterator, specify only the data types of the columns.

24. Named iterator example #sql iterator namediterator (int poid, String status) namediterator iterator1; #sql [ctx1] iterator1 = { select poid,status from purchaseorder }; while(iterator1.next()) { System.out.println("poid: " + iterator1.poid() + "Status: "+ iterator1.status()); } iterator1.close(); 1 2 3 4 5 6

25. Positioned iterator example #sql iterator positionedIterator (int, String); String status = null; int poid = 0; positionedIterator iterator1; #sql [ctx1] iterator1={ select poid, status from purchaseorder }; #sql { fetch :iterator1 into :poid, :status }; while(!iterator1.endFetch()) { System.out.println("poid: " + poid + "Status: "+ status); #sql { fetch :iterator1 into :poid, :status }; } 1 2 3 4 5 6 7 8 9

26. Updatable and scrollable iterators • Iterators in SQLj are read-only and can only move forward by default. • To define a scrollable iterator, you need to implement sqlj.runtime.Scrollable while defining the iterator. • To define an updatable cursor, you need to implement sqlj.runtime.ForUpdate while defining the iterator. • When defining an updatable iterator, you also need to specify the columns you would like to update.

27. Updatable and scrollable iterators #sql public iterator namediterator implements sqlj.runtime.ForUpdate with (updateColumns="STATUS") (int poid, String status); namediterator iterator1; #sql [ctx1] iterator1={ select poid,status from purchaseorder }; while(iterator1.next()) { System.out.println("before update poid: " + iterator1.poid() + "Status: "+ iterator1.status()); if(iterator1.status().toUpperCase().compareTo("UNSHIPPED")==0) #sql [ctx1] {update purchaseorder set status= 'shipped' where current of :iterator1 }; } #sql [ctx1] {commit}; 1 2 3 4 5 6 7 8

28. myapp_SJProfile0.ser myapp_SJProfile1.ser Package myapp.java myapp.sqlj Preparing an SQLJ program DB2 Customizer db2sqljcustomize SQLJ Translator sqlj java myapp Java Compiler javac myapp.class

29. Translation using “sqlj” • “sqlj” is the SQLJ translator. The syntax is: sqlj <options> filename For example: sqlj myprg3.sqlj • “sqlj” in the above example will create several files: • myprg3.java: • The generated source program • myprg3.class: • The class file after sqlj invokes javac • myprg3_SJProfile0.ser: • A serialized profile file for each connection context class that is used in an SQLJ executable clause • myprg3_SJProfileKeys.class: • The class file after sqlj invokes javac

30. Customization using “db2sqljcustomize” • “db2sqljcustomize” is the DB2 Customizer. The syntax is: db2sqljcustomize <options> filename For example: db2sqljcustomize -url jdbc:db2://localhost:50000/sample -user rfchong -password mypasswd myprg3_SJProfile0.ser In this example we are using these options: • -url jdbc:db2://localhost:50000/sample • Is the URL needed to connect to the database • -user rfchong • user ID to connect to the database • -password mypasswd • password to connect to the database myprg3_SJProfile0.ser is the file name to “customize” to create the package in the db