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.

Monday Sep 26, 2011

Scripting Language Related Sessions at Oracle OpenWorld and JavaOne, October 2011

Oracle OpenWorld and JavaOne conferences are happening in San Francisco next week. It will be a busy and exciting time.

First, here's a shout out: For me the conference kicks off on Sunday morning. Marcelle Kratochvil from Piction (heavy users of PHP and Oracle DB) is hosting the inaugural Unstructured Data with Multimedia SIG for Oracle Database and MySQL database (32440) Sunday 9:00 am in Moscone West room 2011.

Below are some of the scripting and related sessions happening during the week.

Exhibition Hall

During the Exhibition Hall hours, come and talk to us at the Database Access Services and APIs booth. This year we're in Moscone South, Left SL-067. The Tuxedo application server booth is Moscone South, Right - SR-202. At JavaOne look out for the NetBeans booth, Hilton San Francisco - HHJ-023.

Scripting Sessions, Birds-of-a-Feather Meetings, and Hands-on-Labs at OOW

  • The Oracle Tuxedo team has scripting language support in their powerful application server environment:
    High-Performance Web Applications with C/C++/PHP/Python (15705)
    Monday, 05:00 PM, Moscone South - 300
  • This year we are running introductory Hands-on Lab sessions for three languages concurrently. Come along and choose which language you'd like to dip your toes into:
    Develop and Deploy High-Performance Web 2.0 PHP, Ruby, or Python Applications (30082)
    Monday, 05:00 PM, Marriott Marquis - Salon 10/11
  • Come and ask questions at the round table Birds-of-a-Feather session:
    Meet the Oracle Database Clients Developers: C, C++, PHP, Python, Ruby, and Perl (26240)
    Monday, 07:30 PM, Marriott Marquis - Salon 8
  • My overview and state-of-the-nation session is:
    PHP, Ruby, Python, and Perl: Develop and Deploy Mission-Critical Apps with Oracle Database 11g (14704)
    Wednesday, 11:45 AM, Marriott Marquis - Salon 8
  • The Tuxedo team Hands-on-Lab lets you code in C/C++/PHP/Python/Ruby:
    Develop High-Performance, Service-Oriented C/C++ Applications for Oracle Exalogic (31120)
    Thursday, 12:00 PM, Marriott Marquis - Salon 3/4
  • Raimonds Simanovskis, maintainer of the Rails adapter for Oracle is giving a session:
    Extending Oracle E-Business Suite with Ruby on Rails (8604)
    Thursday, 03:00 PM, Moscone West - 2002/2004

Several other sessions discuss topics that scripting language devotees will find invaluable:

  • Build, Deploy, and Troubleshoot Highly Performant, Highly Available Apps with Oracle Database (14703)
    Wednesday, 05:00 PM, Moscone South - 303
  • Net Services: Best Practices for Performance, Scalability, and High Availability (14345)
    Wednesday, 01:15 PM, Moscone South - 303

Also check out the full Oracle Tuxedo application server schedule here.

Scripting at JavaOne

Over in the concurrent JavaOne conference there are several scripting sessions driven by San Francisco's EngineYard. This year they have JRuby sessions but with their recent aquisition of PHP technnology, I hope they'll have more on PHP in one of the OOW streams next year:

  • Accelerate Your Business and Aim for the Cloud with Java and JRuby (25284)
    Wednesday, 03:00 PM, Parc 55 - Embarcadero
  • From Java to Rails: Techniques for Adding Ruby Agility to Your Java Web Stack (24582)
    Monday, 05:30 PM, Parc 55 - Market Street
  • Real-World JRuby (23600)
    Wednesday, 04:30 PM, Parc 55 - Market Street
  • Script Bowl 2011: A Scripting Languages Shootout (22060)
    Wednesday, 08:30 AM, Hilton San Francisco - Grand Ballroom B

Also keep an eye out for the various NetBeans IDE sessions and demo booth.

Linux

Check out the four pages of Focus on Linux sessions and events.

MySQL

There is a veritable plethora of MySQL content - four pages of sessions and activites are listed in the Oracle Focus on MySQL. Don't forget the MySQL Community Reception Tuesday 7:00pm - 9:00pm in the Marriott Marquis - Foothill G.

Having started this post with a shout out, let me end with one to Bill Karwin, who was instrumental in getting PHP's Zend Framework off the ground. He is talking about MaatKit and SQL Injection.

Update: Ligaya Turmelle, well known in the PHP community, confirmed overnight that despite a recent job move she is still on track to present her MySQL Performance Tuning talk (16040).

You can search the OOW session catalog here and the JavaOne session catalog here.

Saturday Oct 10, 2009

Oracle OpenWorld starts today!

[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
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
31
     
Today