Question? Leave a message!

Building Web Application Using PHP & Oracle

Building Web Application Using PHP & Oracle 3
Building Web Application Using PHP Oracle Balaranjith Amit Surana Date: November 8, 2013 CO Performance(Mar’12) Topics • Introduction about PhP • Architecture of OraclePhP • Using PhP along with Oracle Database • Best practices (DRCP) • Introduction to Frameworks • Integration with Oracle EBS CO Performance(Mar’12) About PHP •PHP is a serverside scripting language designed for web development but also used as a general purpose programming language •PHP is compatible with most of the databases including Oracle. •Any web based application can be easily developed in PHP CO Performance(Mar’12) Why PHP • PHP is freely accessible • It's quick to develop in PHP • Programmers of Java, PERL, BASIC, and other popular languages can find many parallels to ease transition to PHP • It runs on disparate operating systems • There are many support groups, forums, and teams supporting PHP CO Performance(Mar’12) Architecture PHP Oracle CO Performance(Mar’12) OCI8 • These functions allow you to access Oracle Database 12c, 11g, 10g, 9i and 8i. • They support SQL and PL/SQL statements. • Basic features include transaction control, binding of PHP variables to Oracle placeholders, and support for large object (LOB) types and collections. • Oracle's scalability features such as Database Resident Connection Pooling (DRCP) and result caching are also supported. CO Performance(Mar’12) Connection with Oracle Database • To enable the oracle database for PHP, after installation of LAMP or WAMP one needs to make some changes. • Find the PHP configuration file php.ini and locate the below line in the file and uncomment it. ;extension=phpoci8.dll CO Performance(Mar’12) Connecting to Database • To access the oracle database, first connect the database. Database could reside on local or remote server. • Before starting the connection one would require the username, password and tnsnames for the database. Below is the code for how to connect to Oracle database. php c = ociconnect('un', 'pw', '//localhost/XE'); CO Performance(Mar’12) Retrieving and Modifying data in Oracle using PHP • SELECT php s = ociparse(conn, 'select from employees'); ociexecute(s); while (row = ocifetcharray(s)) foreach (row as item) print item; CO Performance(Mar’12) Retrieving and Modifying data in Oracle using PHP • INSERT • Below is example code for “INSERT” statement. We can also create dynamic insert query if values from the form are to be inserted. • php s = ociparse(conn, 'insert into employees(id,firstname,lastname,dob) values (1,‘Amit’, ‘Surana’, ’15JUL1990’)'); ociexecute(s); CO Performance(Mar’12) Calling Oracle Stored Procedure php s = ociparse(conn, "begin atulorctkpkg.ATLTKGETWEEKOFF(:bind0, TODATE(:bind1,'DDMonYYYY'), TODATE(:bind2,'DDMonYYYY'), :bind3); exception when others then :bind4:=sqlerrm; end;"); ocibindbyname(s, ":bind0", GET'empweekoffchg',32); ocibindbyname(s, ":bind1", GET'weekoffdate',32); ocibindbyname(s, ":bind2", GET'newdate',32); ocibindbyname(s, ":bind3", msg,1000); // 32 is the return length ocibindbyname(s, ":bind4", err,1000); ociexecute(s, OCIDEFAULT); OCIBINDBYNAME() Binds a PHP variable to an Oracle placeholder CO Performance(Mar’12) SQL Statement Execution • Parse ociparse() Prepares a statement for execution • Bind ocibindbyname() Optionally binds variables in WHERE clause or to PL/SQL arguments • Execute ociexecute() The Database executes the statement and buffers the results • Fetch ocifetcharray() Optionally retrieves results from the database CO Performance(Mar’12) Performance Improvement – DRCP (Data Resident Connection Pooling) • DRCP pools a set of dedicated database server processes (known as pooled servers), which can be shared across multiple applications running on the same or several hosts. • A connection broker process manages the pooled servers at the database instance level. • A connection broker accepts incoming connection requests from PHP processes (e.g. web server processes handling PHP requests) and assigns each a free server in the pool. CO Performance(Mar’12) DRCP Architecture CO Performance(Mar’12) DRCP Configuration • The pool needs to be started before connection requests begin. The command below does this by bringing up the broker, which registers with the database listener: – SQL execute dbmsconnectionpool.startpool(); • Configuring PHP for DRCP  PHP must be built with the OCI8 1.3 or later extension. PHP 5.3 contains OCI8 1.4. For PHP 5.2 and older versions of PHP, download the latest release of OCI8 from PECL, extract it and use it to replace the existing ext/oci8 directory in PHP. oci8.connectionclass = MYPHPAPP c = ocipconnect('myuser', 'mypassword', 'myhost/sales:POOLED'); CO Performance(Mar’12) Introduction to Frameworks • A framework provides the much sought after robustness and strength to the website. • It helps the developers to fast track the entire development process and consequently they can meet the highend demands from the client side easily. • There are number of reasons for the need of frameworks required for PHP. CO Performance(Mar’12) Advantages of Framework • Code and File Organization • Utilities and Libraries • The MVC Pattern • Security • Less Code Faster Development • Performance Tools • Suitable for Teamwork CO Performance(Mar’12) Framework Examples • Yii Framework  Yii is one of the most popular and top Framework amongst all PHP Frameworks.  Yii has myriad of features such as MVC, DAO / Active Record , I18N/L10N, caching, authentication and rolebased access control, scaffolding, testing, open source, high performing, object oriented, database access object, easy form validation, default support for web services and many more. • CodeIgniter  Built for PHP coders who need a simple and elegant toolkit to create fullfeatured web applications.  It is a framework requires nearly zero configurations.  It helps in eschewing complexity, and favoring simple CO solution. Performance(Mar’12) Integrating with Oracle EBS • Have you tried opening Oracle EBS from disparate devices like mobile or tablet • Found difficulty • Is there any solution to this CO Performance(Mar’12) Solution • We can create our bespoke applications to obtain the required data from the database and display it in vanilla form or do the manipulation and calculations and show it to users. • We can also create forms in PHP which are easy to build, rich in user interface and easy to deploy. CO Performance(Mar’12) Case • Sales persons are always on the fly and getting the access to Oracle EBS is not feasible. • At such point of time integration of PHP and Oracle database comes to rescue as we can create PHP forms which can be accessed from varied devices or create Android application which can easily talk with PHP and Oracle database. • Oracle EBS is already providing most of the API’s for inserting and updating the record for most of the functionality which can be easily used and can be made accessible across disparate devices like smart phones, tablets and PCs. CO Performance(Mar’12) Benefits Challenges • Benefits Reduced load on Application Server Rich user interfaces Interactive websites • Challenges Security Validation of data Getting the stakeholders information CO Performance(Mar’12) Conclusion • Though there are some challenges in integrating such solution but with strong and robust architecture and security design, these solutions can help system to be ubiquitous. CO Performance(Mar’12)