Executing Queries with CUBRID Node.js Driver

Executing a non-query SQL command

In the next example, we will see how we can use the batchExecute function. We will:

• Create a table
• Insert some data in the table
• Drop the table

For simplicity, the error-handling code was removed.  Remember, it is a best practice in your live applications to always test for errors!

var CUBRID = require('node-cubrid');
var CUBRIDClient = CUBRID.createCUBRIDConnection('localhost', 33000, 'public', '', 'demodb');

CUBRIDClient.connect(function () {
  CUBRIDClient.batchExecuteNoQuery('drop table if exists node_test', function () {
    CUBRIDClient.batchExecuteNoQuery('create table node_test(id int)', function () {
      CUBRIDClient.batchExecuteNoQuery('insert into node_test values(1)', function () {
        CUBRIDClient.batchExecuteNoQuery('drop table node_test', function () {
          CUBRIDClient.close();
        });
      });
    });
  });
});

Executing a query

In order to execute a SQL query which returns data to the client, we need to use the standard pattern:

1. Send a query
2. Receive:
1. Total records count
2. Records information:
1. column names
2. column data types
3. The first batch of records (the number of records return depends on the socket internal buffer size)
3. Send command to fetch more data
4. Receive fetched data
5. Repeat steps 3-4 while more data is available
6. Close the query request

Let’s see now some query examples.

Simple query

var CUBRID = require('node-cubrid'),
    Helpers = CUBRID.Helpers,
    Result2Array = CUBRID.Result2Array,
    CUBRIDClient = CUBRID.createCUBRIDConnection('localhost', 33000, 'public', '', 'demodb');
 
CUBRIDClient.connect(function () {
  CUBRIDClient.query('select * from code', function (err, result, queryHandle) {
    var arr = Result2Array.RowsArray(result);
 
    for (var j = 0; j < arr.length; j++) {
      Helpers.logInfo(arr[j].toString());
    }
 
    CUBRIDClient.closeQuery(queryHandle, function () {
      CUBRIDClient.close(function () {
 
      });
    });
  });
});

The result of the above code is:

C:\>node scratch.js
X,Mixed
W,Woman
M,Man
B,Bronze
S,Silver,
G,Gold

C:\>

Result2Array object contains a set of functions which converts JSON data received by the driver into array data. The following functions are available:

• RowsArray: Return the query records as a two-dimensional arrayrows, columns
• ColumnNamesArray: Returns a single-dimensional array with the columns names returned by the query.
• ColumnTypesArray: Returns a single-dimensional array with the columns data types returned by the query.
• TotalRowsCount: Returns the total number of records returned by the query. Not the same as the current rows count in the data array – TotalRowsCount includes also the result of all possible successive fetch commands.

Fetching more results

We will now implement all the steps described at the beginning at this section, including consecutive fetch commands. And this time we will use driver events in our code:

var CUBRID = require('node-cubrid'),
    Result2Array = CUBRID.Result2Array,
    CUBRIDClient = CUBRID.createCUBRIDConnection('localhost', 33000, 'public', '', 'demodb');

CUBRIDClient.connect(null);

CUBRIDClient.on(CUBRIDClient.EVENT_ERROR, function (err) {
  throw  err.message;
});

CUBRIDClient.on(CUBRIDClient.EVENT_CONNECTED, function () {
  console.log('Connection opened.');
  CUBRIDClient.query('select * from participant', null);
});

CUBRIDClient.on(CUBRIDClient.EVENT_QUERY_DATA_AVAILABLE, function (result, queryHandle) {
  console.log('Total query result rows count: ' + Result2Array.TotalRowsCount(result));
  console.log('First "batch" of data returned rows count: ' + Result2Array.RowsArray(result).length);
  CUBRIDClient.fetch(queryHandle, null);
});

CUBRIDClient.on(CUBRIDClient.EVENT_FETCH_DATA_AVAILABLE, function (result, queryHandle) {
  console.log('Next fetch of data returned rows count: ' + Result2Array.RowsArray(result).length);
  CUBRIDClient.fetch(queryHandle, null);
});

CUBRIDClient.on(CUBRIDClient.EVENT_FETCH_NO_MORE_DATA_AVAILABLE, function (queryHandle) {
  CUBRIDClient.closeQuery(queryHandle, null);
});

CUBRIDClient.on(CUBRIDClient.EVENT_QUERY_CLOSED, function () {
  CUBRIDClient.close(null);
});

CUBRIDClient.on(CUBRIDClient.EVENT_CONNECTION_CLOSED, function () {
  console.log('Connection closed.');
});

And the result is, as expected:

C:\>node scratch.js
Connection opened.
Total query result rows count: 916
First "batch" of data returned rows count: 309
Next fetch of data returned rows count: 315
Next fetch of data returned rows count: 292
Connection closed.

C:\>