Monday Jul 20, 2015

node-oracledb 0.7.0 now supports Result Sets and REF CURSORS

A new release of the Node.js driver for Oracle Database is now on npmjs.com and GitHub.

node-oracledb 0.7 connects Node.js 0.10, Node.js 0.12, and io.js to Oracle Database. It runs on a number of platforms. For more information about node-oracledb see the node-oracledb GitHub page.

The changes in 0.7 are:

  • Added result set support for fetching large data sets. Rows from queries can now be fetched in batches using a ResultSet class. This allows large query results to be fetched without requiring all values to be in memory at once. New getRow() and getRows() methods can be called repeatedly to scroll through the query results.

    The original node-oracledb behavior of returning all rows at once remains the default. To return a resultSet, use the new execute() option { resultSet: true }. For example:

    //  (See the full code in examples/resultset2.js)
    
    . . .
    
    var numRows = 10;  // number of rows to return from each call to getRows()
    
    connection.execute(
      "SELECT employee_id, last_name FROM employees ORDER BY employee_id",
      [], // no bind variables
      { resultSet: true }, // return a result set.  Default is false
      function(err, result)
      {
        if (err) { . . . }
        fetchRowsFromRS(connection, result.resultSet, numRows);
      });
    });
    
    . . .
    
    function fetchRowsFromRS(connection, resultSet, numRows)
    {
      resultSet.getRows( // get numRows rows
        numRows,
        function (err, rows)
        {
          if (err) {
             . . .                        // close the result set and release the connection
          } else if (rows.length == 0) {  // no rows, or no more rows
            . . .                         // close the result set and release the connection
          } else if (rows.length > 0) {
            console.log(rows);
            fetchRowsFromRS(connection, resultSet, numRows);  // get next set of rows
          }
        });
    }
    

    It's important to use the new resultSet close() method to close the result set when no more data is available or required.

    There is more information on Result Sets in the manual.

  • Added REF CURSOR support for returning query results from PL/SQL. PL/SQL code that returns REFCURSOR results via bind parameters can now bind a new node-oracledb type Oracledb.CURSOR and fetch the results using the new ResultSet class.

    //  (See the full code in examples/refcursor.js)
    
    var oracledb = require('oracledb');
    
    . . .
    
    var numRows = 10;  // number of rows to return from each call to getRows()
    
    var bindvars = {
      sal:  6000,
      cursor:  { type: oracledb.CURSOR, dir: oracledb.BIND_OUT }
    }
    
    connection.execute(
      "BEGIN get_emp_rs(:sal, :cursor); END;",  // The PL/SQL has an OUT bind of type SYS_REFCURSOR
      bindvars,
      function(err, result)
      {
        if (err) { . . . }
        fetchRowsFromRS(connection, result.outBinds.cursor, numRows);
      });
    
    . . .
    
    function fetchRowsFromRS(connection, resultSet, numRows)
    {
      resultSet.getRows( // get numRows rows
        numRows,
        function (err, rows)
        {
          if (err) {
             . . .                        // close the result set and release the connection
          } else if (rows.length == 0) {  // no rows, or no more rows
            . . .                         // close the result set and release the connection
          } else if (rows.length > 0) {
            console.log(rows);
            fetchRowsFromRS(connection, resultSet, numRows);  // get next set of rows
          }
        });
    }
    

    There is more information on using REF CURSORS in the manual.

  • Added row prefetching support. The new ResultSet class supports prefetching via a new attribute oracledb.prefetchRows and a new execute() option prefetchRows. Each time the application fetches query or REF CURSOR rows in a ResultSet from Oracle Database, prefetching allows the underlying Oracle libraries to transfer extra rows. This allows better use of database and network resources, improving performance and scalability. Regardless of the prefetch size, the number of rows returned to the application does not change. Buffering is handled by the underlying Oracle client library.

    The default prefetch size is 100 extra rows. Applications should tune the prefetch size used by each execute() for desired performance and/or to avoid allocating and initializing unused memory. There are some more tips in the manual.

    With node-oracledb 0.7.0, non-ResultSet queries now use prefetching with a fixed size of 2. This should reduce the number of round trips required for these queries.

  • Added a test suite. Yay! See the README in the tests directory for how to run the tests. When you run the test suite, you'll notice each test has a unique number for ease of identification. The numbers are not necessarily sequential.

    We do most testing on Linux and Windows. If you see test output differences due to environment or version differences, please sign the OCA and submit a pull request with the fix and an explanation of why it is needed. See CONTRIBUTING.

    If you submit new tests (after signing the OCA), assign each one a unique number in the documented range that applies to the area being tested.

  • Fixed error handling for SQL statements using RETURNING INTO. A bug causing all errors with DML RETURNING statements to report the same error message was fixed.

  • Fixed INSERT of a date when the SQL has a RETURNING INTO clause. When using an INSERT to insert a date or timestamp and the SQL clause had a RETURNING INTO clause for character or number columns, then an error was being thrown. This has been fixed.

  • Renumbered the values used by the Oracledb Constants. If your application uses constant names such as Oracledb.OBJECT or Oracledb.BIND_INOUT then you won't notice the change. However if, for some reason, code has hardcoded numbers like 2, then you will have to update to use the new numbers, see lib/oracledb.js. Or, better, change the code to use the constants' names.

Friday Sep 26, 2014

Oracle OpenWorld is Upon Us! Application Developers Unite!

It's a super, extra, enormously busy time of the year at Oracle because our big Oracle OpenWorld Conference is next week. We have all been working on new products and new presentations.

I have a full mix of events, meetings, presentations and booth duty. If you're at the conference drop by to say Hi. I'll be at the Application Development booth (Left - SLD-163) in Moscone South on Monday morning, Tuesday lunchtime and Wednesday morning (plus other odd times).

Also lookout for me onstage with Tom Kyte on Monday and with Steven Feuerstein on Wednesday.

The session details, and other sessions you might find interesting are listed on my Focus On Application Development page.

If you are a developer, I recommend coming to the Programming and Scripting "Meet the Expert" session at 6pm Tuesday evening (Moscone South - 307). I cannot stress enough how valuable it is to have a dialog and relationship with the developers who create the software and APIs you use.

Wednesday Jun 15, 2011

Oracle Resources for PHP

Here are some key resources for working with PHP and Oracle technologies.

  • Overall Links

    Oracle : Oracle Technology Network (OTN)

    Homepage : The PHP Developer Center -- downloads, how-tos, sample code and discussion forums brought to you by OTN.

    Read : PHP Articles -- a collection of informative articles on OTN.

    Technology : Download the newest versions of other software in Oracle's Technology Stack. Read about other Oracle application development tools.

  • PHP Oracle Database Extension : OCI8 is the most available and scalable PHP adapter for the Oracle database. It is included with PHP, and is also separately downloadable for upgrading older PHP releases. OCI8 works with PHP 4 and PHP 5, and will compile with Oracle 9iR2, 10g and 11g client libraries. Oracle's standard cross-version compatibility and connectivity is applicable, so OCI8 can connect to older or newer databases, locally or remotely. The latest OCI8 release includes support for Oracle Database 11g Database Resident Connection Pooling (DRCP), and for Fast Application Notification (FAN) [whitepaper here], and also has support for Oracle's authentication and end-to-end tracing meta data attributes [article here]. These features improve scalability and availabilty of the OCI8 extension.

    The OCI8 extension can also be used with the Oracle In-Memory Database Cache.

    Book : The Underground PHP & Oracle Manual -- the definitive, free guide to using PHP with Oracle Database.

    Tutorial : Oracle & PHP tutorials -- step by step Oracle Learning Library tutorials for using PHP with Oracle. (Free OTN login required)

    Read : PHP & High Scalability -- covers enabling DRCP and FAN for PHP applications.

    Read : PHP Web Auditing, Authorization and Monitoring with Oracle Database -- learn how to audit individual web users, automatically apply rules to individual web users to restrict data access, and monitor and trace database usage per application user.

    Forum : The Oracle & PHP forum -- technical discussion forum for using PHP with Oracle.

    Blog : PHP and Oracle: Christopher Jones -- The latest news on PHP and Oracle.

  • Database : Oracle Express Edition Database -- an entry-level, small-footprint database based on the standard Oracle Database code base that's free to develop, deploy, and distribute; fast to download; and simple to administer.

    PHP OCI8 works with Oracle Database XE the same way it works with the other editions of Oracle Database.

    Oracle Database XE is a great starter database for:

    • Developers working on PHP, Python, Ruby, Java, .Net and other open source applications.
    • DBAs who need a free, starter database for training and deployment
    • Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
    • Educational institutions and students who need a free database for their curriculum

    Install Now!

  • Database : MySQL -- The world's most popular open source database. MySQL Community Edition is the freely downloadable version. Commercial customers have the flexibility of choosing from multiple editions to meet specific business and technical requirements. Also available for free is the MySQL Workbench for SQL Authoring, System Administration, and schema modeling.

    Recent additions to MySQL include semi synchronous replication, direct memcached-to-InnoDB access, multi-threaded replication, and enhanced partitioning. See What's new in MySQL 5.6

    PHP's mysqli or PDO_mysql extensions can be used to access MySQL.

    Check out the new Query Cache plugin documented here and the Replication and Load-Balancing plugin for master-slave-splitting, documented [here].

    Homepage : Using MySQL With PHP -- Articles and downloads

    Forum : PHP and MySQL forum -- The place to ask questions

    Blog : Planet MySQL -- The latest MySQL news from the community

    Blog : Johannes Schl├╝ter -- A member of the MySQL connector team and the current PHP Release Master.

    Blog : Me Talking Out Loud -- Ligaya Turmelle, a member of MySQL Support. (and contributer to The PHP Anthology: 101 Essential Tips, Tricks & Hacks)

    Blog : Internet Super Hero -- Ulf Wendel, a member of the MySQL connector team.

    Books : Many! Including PHP and MySQL Web Development and Web Database Applications with PHP & MySQL and Learning PHP & MySQL: Step-by-Step Guide to Creating Database-Driven Web Sites

    Install Now!

  • Database : Oracle Berkeley DB 11g -- provides the complex data management features found in enterprise class databases. These facilities include high throughput, low-latency reads, non-blocking writes, high concurrency, data scalability, in-memory caching, ACID transactions, automatic and catastrophic recovery when the application, system or hardware fails, high availability and replication in an application configurable package. Simply configure the library and use the particular features available to satisfy your particular application needs. Berkeley DB can be configured to address any application need from the hand-held device to the datacenter, from a local storage solution to a world-wide distributed one, from kilobytes to petabytes.

    Berkeley DB's SQL API allows PHP's sqlite3 and PDO_sqlite extensions to be used.

    Berkeley DB also includes source code for a php_db4 extension.

    Install Now!

  • IDE : NetBeans -- lets you develop desktop, mobile and web applications using Java, PHP, C/C++ and more. Runs on Windows, Linux, Mac OS X and Solaris. NetBeans IDE is open-source and free.

    Understand: NetBeans PHP features -- All the great features that NetBeans has for PHP developers

    Learn: NetBeans PHP Learning Trail -- the best way to learn how to use NetBeans

    Install Now!

  • Virtualize : Oracle VM VirtualBox -- powerful Cross-platform Virtualization Software for x86-based systems. "Cross-platform" means that it installs on Windows, Linux, Mac OS X and Solaris x86 computers. And "Virtualization Software" means that you can create and run multiple Virtual Machines, running different operating systems, on the same computer at the same time. For example, you can run Windows and Linux on your Mac, run Linux and Solaris on your Windows PC, or run Windows on your Linux systems.

    Oracle VM VirtualBox is available as Open Source or pre-built Binaries for Windows, Linux, Mac OS X and Solaris.

    OTN hosts some pre-built Developer VMs , including an Oracle Tuxedo Web Application Server VM showing PHP support. There is also a Zend Server VM for PHP created by Zend. Zend Server is a complete, Oracle-enabled, enterprise-ready Web Application Server for running and managing PHP applications that require a high level of reliability, performance and security. Zend Server is available in community and supported editions.

    Install Now!

  • Operating system : Oracle Linux -- free to download and distribute. As one of the most widely deployed operating systems today, Linux is increasingly being adopted for cloud-based solutions. Oracle Linux is the most complete and integrated solution available and delivers higher performance and better reliability at up to 7 times lower cost than Red Hat. Why Choose Oracle Linux over Red Hat Linux?

    Oracle Linux 5.6 and 6 introduced PHP 5.3 packages. Subscribers to the Unbreakable Linux Network have access to an additional pre-built PHP OCI8 RPM, and also direct access to an RPM for installing Zend Server .

    Users of older Linux 5 releases can get PHP 5.3 RPMs from oss.oracle.com .

    Install Now!

  • Application Server : Oracle Tuxedo -- provides a robust, grid enabled platform for developing enterprise applications. It provides mainframe-class scale and performance on open, distributed systems for software written in C, C++, COBOL, PHP, Python and Ruby. Oracle Tuxedo provides cost-effective reliability, extreme scalability and throughput of hundreds of thousands of transactions per second. Functionality like Web services, SCA programming model, metadata driven application development make it simple to develop and integrate applications written in many programming languages.

    PHP applications can be hosted on the Tuxedo platform without requiring any code changes and can leverage high availability, scalability and integration capabilities.

    The whitepaper Oracle Tuxedo - An Enterprise Platform for Dynamic Languages explains the architecture and features. A Tuxedo Demo VM for VirtualBox is available for immediate testing.

    Install Now!

  • Client Libraries : Oracle Instant Client -- a small footprint set of libraries that allows applications and tools to connect to an existing Oracle Database. Oracle OCI, OCCI, Pro*C, ODBC, and JDBC applications work without modification.

    The PHP OCI8 extension can be built with Oracle Instant Client to connect to a remote database.

    Instant Client is provided under a separate OTN Development and Distribution License for Instant Client that allows most licensees to download, redistribute, and deploy in production environments, without charge.

    Install Now!

Thursday May 12, 2011

Oracle Tuxedo: A New way to Run PHP Applications

The Oracle Tuxedo team have posted an Oracle Tuxedo 11.1.1.2 Web Application Server Demo for PHP. It is a VirtualBox VM so you can immediately test it out. The free VM is available from OTN's Pre-Built Developer VMs page.

Oracle Tuxedo provides a scalable and highly available mid tier platform capable of managing and integrating heterogeneous applications. This overview datasheet explains its capabilities: "Oracle Tuxedo provides a service-oriented infrastructure for efficiently routing, dispatching, and managing requests, events, and application queues across system processes and application services". A raft of other material is on the Tuxedo landing page. There is also a new white paper on PHP being reviewed at this very moment.

The VM uses Oracle Linux 5.6. It has PHP 5.3 and Oracle Tuxedo 11.1.1.2 installed and configured. The SALT and TSAM components of Tuxedo are also available.

Tuxedo's PHP support comes in the form of a new mod_tuxedo module for Apache 2.2 (or Oracle HTTP Server). This handles all PHP scripts, executing them using managed PHP processes. Existing applications can run normally:

Tuxedo also lets PHP be exposed as a web service:

The tutorial manual provided inside the VM steps through using PHP in both ways. The NetBeans IDE, Oracle Database XE and MySQL are included on the VM to make it easier to carry out your own further experiments with Tuxedo and PHP.

Tuxedo has been around for a long time and has an established customer base. If you look at Oracle Database's big TPC-C benchmark's you can see Tuxedo was involved. It's even certified on Exalogic. The new dynamic language support in Tuxedo is going to open up "Enterprise" infrastructure, making it easy to integrate modern solutions in a heterogeneous environment.

When I get back from the Dutch PHP and PHP|Tek conferences, which run back to back from next week, I plan to write up more details about using Tuxedo and PHP.

Update: a new whitepaper is available: Oracle Tuxedo: An Enterprise Platform for Dynamic Languages.

Thursday Jul 29, 2010

Scripting Languages at the Oracle Tuxedo Virtual Developer Day: Review

[Read More]
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book

Follow:
Blaine Carter
Dan McGhan

Search

Archives
« July 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
21
22
23
24
25
27
28
29
30
31
 
       
Today