Friday Jul 08, 2016

Node-oracledb 1.10 has Enhanced Metadata

Top feature: Enhanced Metadata

The changes in node-oracledb 1.10 are:

  • Enhanced query metadata thanks to a Pull Request from Leonardo. He kindly allowed us to take over and fine tune the implementation.

    Additional metadata for query and REF CURSOR columns is available in the metaData object when the new boolean oracledb.extendedMetaData attribute or corresponding execute() option attribute extendedMetaData are true.

    For example, if the DEPARTMENTS table is like:

    SQL> desc departments
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     DEPARTMENT_ID                             NOT NULL NUMBER(4)
     DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
     MANAGER_ID                                         NUMBER(6)
     LOCATION_ID                                        NUMBER(4)
    

    Then a query in node-oracledb would give extended metadata:

    [ { name: 'DEPARTMENT_ID',
           fetchType: 2002,
           dbType: 2,
           precision: 4,
           scale: 0,
           nullable: false },
         { name: 'DEPARTMENT_NAME',
           fetchType: 2001,
           dbType: 1,
           byteSize: 30,
           nullable: false },
         { name: 'MANAGER_ID',
           fetchType: 2002,
           dbType: 2,
           precision: 6,
           scale: 0,
           nullable: true },
         { name: 'LOCATION_ID',
           fetchType: 2002,
           dbType: 2,
           precision: 4,
           scale: 0,
           nullable: true } ]

    You can see that the available attributes vary with the database type. The attributes are described in the metaData documentation.

    The commonly used column name is always available in metaData regardless of the value of extendedMetaData. This is consistent with previous versions.

    The metadata dbType and fetchType attributes numbers are described in new DB_TYPE_* constants and the existing node-oracledb type constants, respectively. Your code should use these constants when checking metadata types.

    Why did we make the extra metadata optional and off by default? Why do the types use numbers instead of strings? We had a lot of debate about common use cases, out-of-box experience, performance etc. and this is the way the cookie crumbled.

    I know this enhancement will make your applications easier to maintain and more powerful.

  • Fixed an issue preventing the garbage collector cleaning up when a query with LOBs is executed but LOB data isn't actually streamed.

  • Report an error earlier when a named bind object is used in a bind-by-position context. A new error NJS-044 is returned. Previously errors like ORA-06502 were given since the expected attributes were not found and bind settings ended up as defaults. You can still use unnamed objects for bind-by-position binds like:

    var sql = "begin myproc(:1, :2, :3); end;";
    var binds = [ id, name, { type: oracledb.STRING, dir: oracledb.BIND_OUT } ];
    

    Here the third array element is an unnamed object.

  • Fixed a bug where an error event could have been emitted on a QueryStream instance prior to the underlying ResultSet having been closed. This would cause problems if the user tried to close the connection in the error event handler as the ResultSet could have prevented it.

  • Fixed a bug where the public close method was invoked on the ResultSet instance that underlies the QueryStream instance if an error occurred during a call to getRows. The public method would have thrown an error had the QueryStream instance been created from a ResultSet instance via the toQueryStream method. Now the call to the C layer close method is invoked directly.

  • Updated Pool._logStats to throw an error instead of printing to the console if the pool is not valid.

  • Added GitHub Issue and Pull Request templates.

  • Updated installation instructions for OS X using the new Instant Client 12.1 release.

  • Added installation instructions for AIX and Solaris x64.

  • Some enhancements were made to the underlying DPI data access layer. These were developed in conjuction with a non- node-oracledb consumer of DPI, but a couple of changes lay groundwork for potential, user-visible, node-oracledb enhancements:

    • Allow SYSDBA connections

    • Allow session tagging

    • Allow the character set and national character set to be specified via parameters to the DPI layer

    • Support heterogeneous pools (in addition to existing homogeneous pools)

    To reiterate, these are not exposed to node-oracledb.

Resources

Issues and questions about node-oracledb can be posted on GitHub. Your input helps us schedule work on the add-on. Drop us a line!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

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.
Mail: christopher.jones@oracle.com
Twitter: @ghrd
OTN: Scripting Languages
Book: Free PHP Oracle book

Follow:
Blaine Carter
Dan McGhan

Search

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