1 / 30

PHP and SQL/J Pertemuan 12

PHP and SQL/J Pertemuan 12. Matakuliah : T0413 Tahun : 2009. 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.

shen
Télécharger la présentation

PHP and SQL/J Pertemuan 12

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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)

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

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

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

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

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

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

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

  10. Retrieving database metadata – ibm_db2 extension

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

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

  13. 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");?> … ?>

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

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

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

  17. 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};

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

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

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

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

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

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

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

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

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

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

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

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

More Related