Last Updated on 2023-10-10, 21:43 (GMT+0200)

Update 14-07-2021
I just tried SQLcl latest version 21.2. First results: The registering of my custom commands with below library doesn’t work anymore. Could be my bad, in which I’ll try to fix it, could be something in SQLcl. I don’t know yet, have to investigate as soon as I have the time. For now: Either don’t use this library, or stick to 21.1.1 until the root cause is fixed.

Update 08-10-2021
The above mentioned problem was not due to my code, so the library can be safely used. It’s caused by new stuff inside SQLcl.
So, if you run javascript code and only receive one of these messages:
“!ScriptCommand.1!”
“JS Language engine not found”
The quickest way to solve it is to install JDK. (Or better yet: GraalVM. See next update.)

Update 14-04-2022
On 07-04-2022 version 22.1 of SQLcl was released which ends support for Java 8.
So, as of that version Java 11 is the minimum version. As of that version no separate JRE is supplied anymore, so you will need to install a JDK anyway.
Problem: Nashorn is depricated in Java 11 and removed in Java 15, so that implies that using JavaScript in SQLcl will is approaching the end.
However, instead of using a JDK we can use GraalVM which continues to support JavaScript.
The people behind SQLcl have promoted the use of GraalVM on Social media, so that implies it’s supported as well.
I will soon do a short blog post about how I let SQLcl use GraalVM (Spoiler: it’s simple).
You can find GraalVM here: https://www.oracle.com/downloads/graalvm-downloads.html
I’m currently using the latest and greatest: “GraalVM Enterprise Edition 22 version 22.0.0.2 For Java Version 17”

Update 22-04-2022
The library had some changes.
First and foremost: There was a bug due to a terribly wrong use of a global variable which could lead to a custom command suddenly not being recognized fater a couple of succesful executions.
This has been fixed.
Furthermore, I made a better distinction between code meant to be called and code meant for internal use inside the library.

Soon (I hope, available time is a factor) I will create a GitHub repo for this and other scripts, making it a lot easier for me to push new versions and for you to download them.

Update 23-112022
The scripts and libraries can now be found on GitHub.
I hope to add more in the future. I have a bunch of scripts I use, but that need to be made more robust before handing it to others.

https://github.com/erikvanroon/SQLcl

So far, in the previous post  and the one before that, we have seen what is necessary to register some Javascript as a custom command in SQLcl.
To be honest, with all the complexity that is needed, I would be surprised if anybody made it to here.
I am very enthusiastic about the scripting possibilities in SQLcl, but if registering a very simple script with a handful of lines requires more than a screen full of extra code just to be able to make it available as a custom command, I can imagine not a lot of people wanting to go there.

I myself copied the extra code from one script to the next, until I got sick and tired of it.

So, with the wise words of the Dutch medieval Philosopher Desiderius Erasmus in mind “Your library is your paradise” I turned the code I had so far into a library.

Below I will first explain how to use the library and what it does.
Then after that, if you just want a simple way to register custom commands, you can go about your business again while I discuss some aspects of the library itself.

Go straight to

How to load the library into your script
The functionality of the library
– Adding registration functionality to the script
– Just running the script without registering
– Syntax of running the script for registration as custom command
– Registering as custom command
– Registering as custom command with default name
– Controlling the amount of feedback of the register process
– Unregistering a command without re-registering
The listing of the library
Remarks about the code of the library
– Internal/external use
– Feedback
– Arguments
– Default functions
– runCommand

How to load the library into your script

Because this library is introduced to reduce the complexity of the code we need to add to register a command, it would be pretty silly if loading the library would be complex.
So don’t worry, it’s not.

Just save the code of the library (see below) to a file and use the “load” command to load it into your script.

The code in the library is implemented as functions of the object “registration”.
So, that would IMHO be a nice name for the library.
But I also like to be able to recognize by the name that a script is a library and not a directly executable script.
Of course I could use a different extension, but I prefer to use a prefix “ELib_”, where ELib stands for Erik’s Library (or EvROCS Library, but that is the same).

So, if I save the code in a file “d:\ELib\ELib_registration.js” then I can load the library in any script by adding this to the start of it:

Getting all that functionality into your script has never been this easy.

Of course it’s up to you if you would like to add a bit more complexity to make the path a bit more dynamic.
I personally like to have the location of my library scripts in a substitution variable so if I go to a different customer and have to put them in a different place I only have to change the one variable and everything works again.
But , as I’ve said, that comes with a bit of extra complexity because then you need to add a little bit of code to get the value from that substitution variable.

Do what and as you like.

Go Back to top

The functionality of the library

For your scripts, the most important functionality in the library is the function “registration.runCommand”.
If you add a call to this function to the end of your script and provide it the main-code function as parameter, you can either just execute the script or register it as a custom command. If you register it you can choose any command name you like or not choose a command name in which case the default command name “cctest” (for Custom Command Test) will be used, which can be useful during development of the script.
Also, when registering you can choose the amount of feedback (information) messages that will be put on screen.

NOTE:
Up until now, in previous posts, in the code I showed you, my “MainCode” function had a parameter for the “args” array.
This was because when the script was just executed the out-of-the-box ‘array’ args had to be used, but when the script was registered as a custom command, a custom created array with the arguments that are parsed from the command line had to be used.

Now, when a script is registered as a custom command the library still puts the command line arguments of the execution of the command in a custom array, but the custom array is called “args”, so the code of the script can just refer to the args array, no matter whether it’s executed as script or as custom command. So, the MainCode function no longer needs the parameter for the array containing the arguments.

The registration.runCommand function takes up to 3 parameters: functionToExecute, beginEvent, endEvent.
In the first parameter “functionToExecute” you should pass the name of the function expression that is the main code of the script.
Parameters “beginEvent” and “endEvent” are optional. If you need some begin- or endEvent functionality, you can pass in the names of the respective function expressions here. If you don’t, ‘empty’ function expressions that don’t do anything will be used.

Go Back to top

Adding registration functionality to the script

If we want to be able to register this script

It would have to be changed into

In the new version, line 2 is added to load the library, and the call of function MainCode on line 10 in the original was changed into what is line 12 in the new version.
And that’s all.
That’s a lot less code to add isn’t it?
And it supplies some extra possibilities too.

Let’s have a look at what happens when we run this script.

Go Back to top

Just running the script without registering

If the script is saved as “listargs.js” and executed, for example like this:

the exact same behavior can be seen  with or without the change we described above.

“So?” I hear you say. “What’s the use?”.

Well, this is just so you can still choose to simply execute the script without registering it.
But you do now have the option to register it too if you want to.
That is what we’ll do next…….

Go Back to top

Syntax of running the script for registration as custom command

If we want to register the functionality of the script as a custom command, we need to execute it with some special parameters

In the following sections I will try to explain what these parameters mean and what the result of using them is.

Go Back to top

Registering as custom command

To tell the script we don’t want to execute it but to register it as a custom command a parameter is used

This parameter is “-cmdReg” and has to be the first parameter.
This parameter can then be followed by the name you want to use for the custom command.

This means that if I want to register this script as custom command “erik” I could just run:

After which I have added a command “erik” with the functionality from the script, so I can run

Notice that when registering the command some feedback is put on screen:

• A message that an attempt is going to be made to unregister the command, because it may already exist after a previous registration
• A message that the command has not been found, so it has not been unregistered
  If the command had been found, the message would say that the command has been unregistered
• A message that the command has been registered

We will see later how the amount of this feedback can be reduced if you want to.

Go Back to top

Registering as custom command with default name

As you can see in the general syntax, the parameter for the name of the custom command is optional.
That implies that you can register a script as a custom command without supplying a name for the command !?!
Correct. You can.
If no command name is supplied the library will register the functionality using the default name ” ccTest” (Where the “cc” stands for “Custom Command”).
Maybe this is not of any importance to you, but I like this in cases where I’m altering the functionality of existing commands. I can change the script and register it with the default name. Then I can run the command cctest and still have the original command available for comparison of the results.
As I said: maybe you don’t care. Fine. Don’t use it. But it’s there if you want.

By the way, if you would like to use a different default name you can obviously just change it in the library.

Go Back to top

Controlling the amount of feedback of the register process

By default registering a custom command using this library gives some feedback on screen.
It can be useful to see that indeed an attempt was made to unregister previous instances of the command, that it has actually been unregistered or that no previous instances of the command were found, and that the command eventually was successfully registered.
But once you have a stable script that you just want to register via for example your login.sql, you may not want these messages for, let’s say, 10 commands to scroll over your screen when you connect.

Of course you could simply set termout off while registering your commands. But that suppresses any errors too.
The registration functionality in the library has the option to reduce or completely suppress the positive, succesful feedback, while allowing all problems to go to screen after all.

At the end of the registration statement as we’ve seen above one of 3 parameter values kan be added to control the amount of feedback
These parameter values are:

• -all
  Shows all feedback. This is the default. Behavior will be as we’ve seen so far.
  .
• -minimal
  Only displays the confirmation that the command has been registered
  .
• -silent
  Does not display any of the normal feedback
  .

So if the command “erik” we’ve registered before would be registered with the “-minimal” parameter like this

the feedback would look like this

Doing this with the 10 command registrations in your login.sql would just show a clean list of 10 “Command xxx has been registered” remarks.
If even that is too much for you, you can just use “-silent” instead of “-minimal”.

These feedback parameters also work if no command name is supplied after “-cmdReg”.

Will also work.

Go Back to top

Unregistering a command without re-registering

Unregistering a custom command is built into the library and is automatically done just before a new version of that command is registered.
This means that unregistering is a fixed part of registering.

There will probably not be a lot of use cases for when you would like to explicitly unregister a command if it’s not just to get rid of it before registering a new version.
If you’re just not interested in the command anymore, you would normally probably just leave the command where it is and remove the registering of the command from your login.sql. Then next time you run SQLcl it will be gone, and until then you just don’t use the command. You’re not interested in it anymore, after all.

But just in case you found the perfect use case to clean up a custom command, you can.

In fact there are two ways to do that.

Option 1

If you have any script that contains the changes I described to add the registration functionality (loading the library and executing registration.runCommand), then that script can be used to unregister any custom command that has been registered before.
Just execute the script with the ” cmdUnReg” parameter followed by the name of the command you wish to unregister.

Here too you can leave out the command name to unregister the command with the default name (cctest).

To be absolutely clear: this does not, I repeat: not(!!), unregister whatever code is in that script file “listargs.js”, but it unregisters the command that is explicitly typed after “-cmdUnreg” or if no command is typed there, the default command “cctest”.

Option 2

If you do this manual unregistering regularly, you may want to just create a simple dedicated script for that.
That script can be as simple as

Save that code in a file called unreg.js and then unregistering a command can be done like this

Or to unregister the default command cctest

Go Back to top

The listing of the library

Okay, we now all know how the stuff works and how it should be used.
All that is left is the library itself.

Here it is:

Go Back to top

Remarks about the code of the library

Overall the library is just an implementation of what I’ve discussed about registering  and unregistering in previous posts, so I won’t repeat all that I’ve said there.
I will only highlight some stuff that is specific for this library.

Go Back to top

Internal/external use

Most of what’s in the library is for internal use only.
The library declares two objects with functions.

• LibLocalRegUnreg
• registration

The functionality in object LibLocalRegUnreg is primarily meant for use inside the library.
You might look at the code and come to the conclusion you can use it elsewhere too.
That could even be true, but I did not develop it with such use in mind, so there are no guaranties whatsoever.

The functionality in object registration is what’s meant for use by “the outside world”.

The registration.runCommand function is really the only one that is supposed to be called from outside.
The registration.unregisterCommand command is an exception to this rule. It shouldn’t be necessary to call that from outside this library, but if you want to, you can. See the unregistering of commands.

Go Back to top

Feedback

The LibLocalRegUnreg.feedback on line 133 writes the messages to screen conditionally.
Only messages with a level equal or higher than the feedback level that was requested through the -all/-minimal/-silent parameter are actually written to screen. Others are ignored

Go Back to top

Arguments

The LibLocalRegUnreg.commandArguments function on line 140 is the one that parses the command line for executions of the custom command it then calls the LibLocalRegUnreg.replaceArgsWithCustomArgs function to write the parameter values in that call to a custom “args” array.

Go Back to top

Default functions

In the registration.runCommand function all three parameters,  for the function to be executed, the begin event and the end event, can be empty.
For each of those, if the parameter is empty, an ‘empty’ function doing nothing is used (See internal function LibLocalRegUnreg.registerCommand, Line 227, 232 and 237).

Yes, this means that it is possible to register a command with only no-op functions for handleEvent and beginEvent and endEvent.
So registering a command that does absolutely completely totally nothing.
Is this a problem? I don’t think so.
Would that be a stupid thing to do? Absolutely.
Do I think you’re stupid? Not at all.

So in conclusion: I don’t think this is a problem, and preventing it does not justify adding extra complexity to the library.

Go Back to top

runCommand

On line 276. the function registration.runCommand handles the running of the script either normally or in registration/unregistration mode.
If the first parameter used when executing the script is neither “-cmdReg” nor “-cmdUnReg” then the main code function of the script is executed.
If it’s “cmdUnReg” the unregistration functionality is executed.
If it’s “cmdReg” the registration functionality is executed.

I hope this library will be as useful to you as it has been to me so far. This blog does not have the -silent parameter set, so feel free to comment.
Love to hear if it’s useful. Don’t love to but definitely want to hear if you have any criticism.

Go Back to top