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?

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:

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:

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

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:

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!

…So what do we get in 2.0…?

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:

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:

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: