RedPrairie/JDA WMS — MSQL explained with examples

When you install JDA WMS on either Windows Server or Redhat, a command-line interface utility — msql is also installed. Some of you may wonder of when to use it or perhaps never use it considering it useless. While it may not be as good as Oracular MOCA Client or LextEdit or even the standard JDA provided WINMSQL utility (that can be installed with JDA SCE Client), but it still serves a valid purpose in many scenarios.

Scenario 1:

The application is freezing and you would like to kill the database connection using [kill spid]; or [alter system kill session ‘sid,serial#’]; depending if you’re using MSSQL or Oracle. Another scenario would be if the Application Server is down because you’re reconfiguring it to a new database server and you would like to confirm if you are still able to connect to the database, this is the utility that allows you to do so.

Using msql -S flag to verify database connectivity

Scenario 2:

You’re writing a custom MOCA component for LDAP/AD authentication and would to see if your password is working or not or would like to trace it. This is how you do it. You can read my blog on “How to trace MOCA commands manually”.

using validate user password command to validate connectivity to Application

Scenario 3:

You would like to run a long-running command or script, perhaps for purging/archiving data, but you don’t want to stress the Application Server or are connected via VPN and don’t want to risk getting disconnected. You can do so my using MSQL -S (Server Mode) or MSQL -M (Multi-threaded Server Mode for Java commands).

msql utility — usage

Scenario 4:

You’d like to test some scripts that run from the server itself, you can use msql utility to accomplish it.

Running a script file using MSQL

MSQL utility can also be used for some other cool things … like:

1. Execute an OS command by using “!” as a prefix.

use ! to run an OS command

2. Using MSET to set a variable (works only on client-mode)

3. You want to write a complex script and don’t want to make typos and rather use the edit feature. Run msql, then type edit, a notepad will open, enter your script there, you can easily edit there. Once completed, close the notepad.

As soon as you close notepad, all the buffer data will enter into msql and then you press / to execute.

Is there a scenario I missed that you use? Please, feel free to share any insights that I may have missed.