Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

CUBRID Node.js 2.0 Tutorial - What’s new? (Part 1)


 

Just in case you missed it - along with the CUBRID 9.1 engine stable release, we have also released the 2.0 Node.js CUBRID driver!

The scope of this tutorial is to introduce some of the new features we have implemented in this new version.

In this first part of the tutorial, we will focus on the SQL statements queuing support, while in the 2nd part we will look into some other new driver features.

Friendly disclaimer: If you are not familiar with Node.js, we strongly recommend reading about it, before continuing further with the tutorial.

For this tutorial, the software prerequisites you will need to have installed are:

We are assuming you are already familiar with installing and using the CUBRID Node.js driver. There are many tutorials, code snippets and other resources that can help you getting started very quickly – just take a look at the driver home page.

 

What’s new in the 2.0 release?

The main features we have added to the driver are:

  • Support for SQL statements queuing
  • Support for database parameters
  • Sharding compatibility
  • LOB objects handling support
  • Extended database schema support

On top of these, there are many other smaller improvements, for example connection timeout support, improved events support etc. The readme file available on GitHub contains all the details about this release.

As a side note, SHARDING is one of the most interesting feature implemented in CUBRID and we have prepared a separate Node.js tutorial to show how you can use the node-cubrid driver in a CUBRID SHARD environment.

 

SQL Statements queuing

As we mentioned before, in this first part of the tutorial we will focus on the SQL statements queuing feature.

 

Overview

In the first driver release, only one SQL statement could be submitted for execution at any time – a “next query” had to “wait” for the previous query to complete execution:

query.png

The “standard” coding pattern in 1.x was:

 

CUBRIDClient.connect(function () {
   CUBRIDClient.query('select * from nation', function (err, result, queryHandle) {
     ....
     // Start another query...
     ...
     CUBRIDClient.closeQuery(queryHandle, function () {
       CUBRIDClient.close(function (err) {
       });
     });
   });
 });

 

The key thing to remember – and this has not changed in any way! – is the Node.js callbacks concept implemented for each driver method, which is still there, as a fundamental part of the Node.js programming model:

model.png

Of course, there is also the “events model” based on driver’s custom events, but, for simplicity, we will consider for now only the “callbacks model”.

What’s new is that, based on user’s feedback, in the 2.0 release we have introduced the concept of “queries queuing” – the SQL statements now can be added to a queue, without the need to wait for the previous query execution completion “event” notification

queue.png

Please note that in the “background”, the SQL statements are still executed in sequence – there is no “true parallel” execution.

This means that:

  • The queries executions results will always come in the exact same sequence in which they were added to the queue
  • There is no performance execution improvement – actually there is a (very) small decrease in performance due to the background queue manager which “supervises” the queue and starts the next query execution, whenever appropriate.

The big advantage is the “programming experience” – the client does not have to wait for the previous query to complete to submit a new query to be executed!

 

How to…?

It’s time to take a look of how can we use this new feature in code.

First of all, we will reiterate that there are 2 fundamental types of SQL statements:

A. QUERIES. These are “SELECT” SQL statements, which returns data as rows/records.

B. NON-QUERIES. These are “INSERT”, “UPDATE”, “ALTER”, etc. SQL statements which do not return rows of data.

The so-called “queries queue” in the CUBRID Node.js driver is able to handle both these SQL statement types, through 2 methods:

  • addQuery(…)
  • addNonQuery(…)

As the name implies, the first method is used to “add” to the queue QUERIES statements, while the other one is used for NON-QUERIES.

In the next section, here are some simple examples.

 

Examples

Let’s see first an example with some SQL statements, all QUERIES-type:

var SQL_1 = 'SELECT COUNT(*) FROM code';
 var SQL_2 = 'SELECT * FROM code WHERE s_name = \'X\'';
 var SQL_3 = 'SELECT COUNT(*) FROM code WHERE f_name LIKE \'M%\'';
 
 CUBRIDClient.connect(function () {
   CUBRIDClient.addQuery(SQL_1, function (err, result) {
     //Process query results...
   });
 
   CUBRIDClient.addQuery(SQL_2, function (err, result) {
     //Process query results...
   });
 
   CUBRIDClient.addQuery(SQL_3, function (err, result) {
     //Process query results...
     CUBRIDClient.close(function (err) {
     });
   });
 });

As you can see, the queries are added to the queue independently, the “next query” code does not have to be within the callback of the previous query – this is what the “queries queue” concept is all about!

As usually in our tutorials, please remember that we stripped out the error handlings, output messages etc. We are only keeping the relevant code for the scope of demo-ing the features we are presenting!

And here is an example mixing QUERIES and NON-QUERIES SQL statements:

var SQL_DELETE = 'DELETE FROM code WHERE s_name = \'A\'';
 var SQL_INSERT = 'INSERT INTO code VALUES(\'A\', \'ABC\')';
 var SQL_COUNT = 'SELECT COUNT(*) from code';
 
 CUBRIDClient.connect(function (err) {
     CUBRIDClient.addNonQuery(SQL_DELETE, function (err) {
      …
     });
 
     CUBRIDClient.addNonQuery(SQL_INSERT, function (err) {
      …
     });
 
     CUBRIDClient.addQuery(SQL_COUNT, function (err, result) {
      …
     });
 
     CUBRIDClient.addNonQuery(SQL_DELETE, null);
 
     CUBRIDClient.addQuery(SQL_COUNT, function (err, result) {
      …
     });
 …

 

…So what do we get in 2.0…?

The brief answer is:

  • We can add QUERIES to the driver execution queue, and the system will process automatically one query after another
  • And we can also add NON-QUERIES to the driver execution queue

We have NOT removed the support for “single”/”direct” queries execution which we had in the 1.x previous releases!

If you do not need the queries queue, the driver is 100% compatible with the previous releases and you can keep on doing “query(…)” or “batchExecuteNoQuery(…)” as before.

Actually, this is even recommended whenever you have performance issues to consider and a queries queue is not actually needed…!

 

Events support

Yes, we do keep on going with the events model support in 2.x, including the queries queue! More than ever, we paid special extra-attention to the events model, in order to properly enable queries queuing in pair with data retrieval through events. Let’s see a quick example:

var SQL_A = 'SELECT * from nation';
 var SQL_B = 'SELECT * from code';
 var SQL_C = 'SELECT * from game';
 
 CUBRIDClient.connect(function () {
 });
 
 CUBRIDClient.on(CUBRIDClient.EVENT_ERROR, function (err) {
   throw err;
 });
 
 CUBRIDClient.on(CUBRIDClient.EVENT_CONNECTED, function () {
   Helpers.logInfo('Connected.');
   CUBRIDClient.addQuery(SQL_A, null);
   CUBRIDClient.addQuery(SQL_B, null);
   CUBRIDClient.addQuery(SQL_C, null);
 });
 
 CUBRIDClient.on(CUBRIDClient.EVENT_QUERY_DATA_AVAILABLE, function (result, queryHandle, sql) {
 …
   switch (sql) {
     case SQL_A:
       …
       break;
     case SQL_B:
       …
       break;
     case SQL_C:
       …
   }
   CUBRIDClient.fetch(queryHandle, null);
 });
 
 CUBRIDClient.on(CUBRIDClient.EVENT_FETCH_DATA_AVAILABLE, function (result, queryHandle) {
   …
   CUBRIDClient.closeQuery(queryHandle, null);
 });

 

As you can imagine, the “big” challenge is knowing from what query the (fetch) results are coming from… …and this is handled via a pair of SQL statement source and queryHandle “values”. You might actually need to implement your own “dictionary” with this data, using as key the queryHandle, if you find yourself in a more complex situation where an advanced sql statements tracking system is needed.

Also, do not forget: The SQL statements are executed in exactly the same sequence in which they were added to the queries queue!

 

What else you should know…?

Related to queries, one of the new things we have added in the 2.0 release is the support for queries with typed parameters. Previously, when you were dealing with parameters in queries, what you had to do was to specify the delimiters for the parameters. We added this “extension” because for some data types, simply specifying the parameter delimiters was just not enough – for example, when trying to figure out if you are dealing with VARCHAR or CLOB…

Now, we have added support for specifying each parameter type. Without any more, here is an example which is self-explaining:

var sql = 'select * from nation where continent = ?';
 var arrValues = 'Oceania';
 
 CUBRIDClient.connect(function (err) {
     CUBRIDClient.queryWithTypedParams(sql, arrValues, 'varchar', function (err, result, queryHandle) {
     …

As you can see, we can specify now the parameter data type – in our example above is “varchar”.

The recommendation is to use this only when there is some confusion potential regarding the parameter data type, like the one we mention above with VARCHAR and CLOB! Otherwise, just go on with delimiters (there are some downsides when using this data type explicit method – “queryWithTypedParams(…)”).

Another thing you should know and it might be very useful in your code is that the new queries queue processor supports implicit connect!

In other words, once you have created your client instance, you don’t need to explicitly call “connect(…)” – the driver will automatically detect if a connection is needed and will do it for you. Here is an example:

var SQL_1 = 'SELECT COUNT(*) FROM code';
 var SQL_2 = 'SELECT * FROM code WHERE s_name = \'X\'';
 
 CUBRIDClient.addQuery(SQL_1, function (err, result) {
   …
 });
 
 CUBRIDClient.addQuery(SQL_2, function (err, result) {
   …
 });

 

We hope you enjoyed this first part of the tutorial!

Stay tuned for the next part and please let us knows your feedback and suggestions!

 

Thank you!

The CUBRID API team

comments powered by Disqus
Page info
viewed 2095 times
translations en
Author
posted last year by
CUBRID
Contributors
updated last year by
View revisions
Share this article