Creating and Running Scripts

January 10, 2011 admin

Off

Introduction Buy Now

Channergy includes files that enable users to write and run SQL scripts from the Tools menu. There are a number of scripts that are installed in the data directory when the application is installed.

NOTE: An in-depth knowledge of the Channergy database structure and general knowledge of SQL (Structured Query Language) is required before writing custom SQL scripts on your database. Failure to understand both the database structure and how SQL works can do serious damage to the integrity of your database.

Creating and Editing Channergy Scripts

Open window explorer and navigate to the C:\Program Files\Channergy 2011 or C:\Program Files (x86)\Channergy 2011 (on 64-bit systems)
Double click on the ChannergyScriptEditor.exe and you will get the following screen.
1. NOTE: You can create a shortcut to the ChannergyScriptEditor on your desktop by right-clicking on the ChannergyScriptEditor.exe and select Send To ->Desktop (create shortcut).
The editor is generally divided into three areas, the text menu, the command buttons and the SQL grid.
1. Text Menu
  1. File
    1. New Script-Creates a new script in the window.
    2. Open Script…-Opens a file browser and allows you to navigate to an existing query
    3. Recent Scripts -> Lists the last 10 scripts that have been opened.
    4. Save Script…-Opens a file browser to save the file.
    5. Save Script As…-Saves the script as a new file.
  2. Edit
    1. New Line-Adds a new line to the query.
    2. Delete Line…-Deletes the current line.
    3. Move Line Up-Moves the selected line up one row.
    4. Move Line Down-Moves the selected line down one row.
  3. Options
    1. Channergy Directory…-Use this function to specify the location of the Channergy data directory.
    2. Mailpriv Directory…–
    3. Reports Directory…-Use this function to point the Reports directory.
  4. Help
    1. SQL Templates-Clicking on this menu option opens up the following dialog.
    2. You can use these templates to help you build SQL queries in the DBIASM version of SQL.
    3. You can get more in-depth documentation from here.
2. Command Buttons
  1. New Line- Clicking on the new line will add a new line to the script grid.
  2. Delete…-This will delete the selected line in the script.
  3. Move Up-Moves the selected script row up one row.
  4. Move Down-Moves the selected row down one row.
  5. Run..-Runs the current script.
  6. SQL Template-Clicking on this option will show a list of SQL commands available to you
    1. Clicking on one of the commands will open up the following dialog box.
      1. You can use these templates to help you build SQL queries in the DBIASM version of SQL.
      2. You can get more in-depth documentation from here.
3. The SQL Grid
  1. Line #-This is just a sequential line number in the script. When you first open the Script Editor this field is blank, after clicking on the New Line button it is populated with the number 1.
  2. Command- Clicking on the will show you a list of options that are available here. Note: This button is not visible if there is no number in the Line # field. The available options are:
    1. SQL- This option allows you to enter SQL commands into the Command Text window
      1. TIP: If you double-click on the Command Text window you will get a larger dialog box that will enable you to enter your SQL without it being truncated.
    2. SHOWMESSAGE-Anything you type into the Command Text window will show up in a message box with an OK button when the script is run.
    3. SHOWOKCANCEL-Anything you type in the Command Text window will show up in a message box with OK and Cancel buttons when the script is run.
    4. EMPTYTABLE-When this option is selected the Options fields are populated with a drop down showing all of the tables available based on the Channergy directory defined in the Channergy Directory… from the Options menu.
      1. Note: Use this feature with great care. This is designed to empty a temporary table that you will use in the SQL. Do not empty any of the Channergy tables that you have not created in a script.
    5. OPENREPORT-When this option is selected the Options fields are populated with a drop down showing all of the reports available based on the Channergy directory defined in the Reports Directory… from the Options menu.
      1. TIP: If you want to open a report based on a table that was manipulated in a previous line of the query you may get a table locked error when trying to run the report. To avoid this error add a simple SELECT * FROM SomeTable before the line that opens the report. Where SomeTable is any table in the Channergy database that is not used in the report.
    6. IMPORT-Not currently implemented.
Adding user prompts to your scripts-The Channergy script engine allows you to add prompts for data input while the script is running. Below are the prompts available to use and examples of the syntax in which to use them.
1. [Browse] – The browse command is used to have the script open a file browser for importing a file into Channergy. Below is an example of using the browse command:
  1. Note the use of the EXCLUSIVE clause. Using the EXCLUSIVE clause greatly improves the speed that files are imported into the database.
2. :DATE_ – Adding this to a SQL statement followed by a prompt (with underscores between the words) will accept a date input from the user. The input format will be of the form MM/DD/YYYY.
3. :INTEGER_-Adding this to a SQL statement followed by a prompt (with underscores between the words) will accept an integer value input from the user.
4. :NUMBER_-Adding this to a SQL statement followed by a prompt (with underscores between the words) will accept a float value input from the user.
5. :CURRENCY_-Adding this to a SQL statement followed by a prompt (with underscores between the words) will accept a currency value input from the user.
6. :TEXT_-Adding this to a SQL statement followed by a prompt (with underscores between the words) will accept a text value input from the user.
  1. NOTE: If you are prompting a user to enter a product number you will need to ensure that the value is all upper case. You can do this by combining this with the UPPER function
7. :TODAY_-Adding this to a SQL query will use the current date in the field.
8. Below is an example of using the TEXT prompt in a SQL query.

Creating a Simple Script

The following is a simple script that you can write with the script editor. It will prompt you to enter a ProductNo and print a report that shows all of the customers who ordered the selected product.

Open the Script Editor
Click on the button
Select the SHOWOKCANCEL option from the command drop down.
Double-Click on the Command Text field and enter in a prompt
Click on the button
Add a new line using the button
Select the SQL button from the Command drop down
Double-Click on the Command Text field and enter in the following SQL statement.
Click on the button
Add a new line using the button
Select the SQL button from the Command drop down
Double-Click on the Command Text field and enter in the following SQL statement.
Click on the button.
Add a new line using the button.
Select the OPEN REPORT option from the Command drop down
Select the ProductsOrdered report from the Report Name drop down.
Click on File -> Save Script and save the script in the data directory.
To test the script click on the button.
You will get a confirmation dialog like Click on the OK button.
You will now see the dialog box that you created.Click on the OK button.
You will now get a prompt asking for a product number. Key in a product number and click on the OK button.
You will now see a report similar to
1. Note: If you get an error message saying that the report could not be found, check that the Channergy Script Editor is pointing to the correct reports directory. Go to Options -> Reports Directory…

Installing Channergy Scripts

The scripts are installed so that they are accessible from the Tools menu in Channergy. The installation method depends on whether or not you are using the Client-Server engine to connect to the Channergy database.

Normal Installation (Not using the client-server engine)
1. Copy the Channergy script file(s) into the data directory on your system hosting the database. Normally it is C:\Channergy\Data
2. From the shared network folder create shortcuts to the script files you want to appear in the Tools menu.
  1. Select the files and right-click on them and select Create Shortcut
3. Copy the shortcuts into the Channergy tools folder. This is usually C:\ChannergyDataTools
4. The links to the scripts will now show up in the Tools menu in Channergy.
Client Server Installation
1. Copy the Channergy script files to the Client-Server directory. Normally this is C:\ChannergyCS, although this may vary considerably.
2. From the shared network folder that points to the client-server directory, select the MWS script files you want to appear in the Tools menu and create shortcuts for them.
3. Copy the shortcuts to the Tools folder in the client-server directory. Normally this is C:\ChannergyCS\Tools however your set up may be different from this.
4. The links to the script will now show up in the Tools menu in Channergy.

UNDER THE HOOD: The ChannergyScirptEdior and ChannergyScript files are automatically installed in with Channergy installers dated 2009 and later. For Channergy 2009 the script files are installed in C:\Program Files\Channergy\2009\Scripts. For Channergy 2010 and later the script editor and script engine are installed in the same directory as the Channergy.exe file. Normally this is C:\Program Files\Channergy 20XX where XX is the year version of your copy of Channergy.

Earlier versions of Channergy installers did not include the script utilities. You can download and install them from here.

The Channergy script file extention (mws) is associated with the script engine when the software is installed. If you get an Access violation message when trying to open a channergy script file, this most likely means that the file association with the script engine was not made or somehow broken.

There are a large number of scripts that are installed with the software. It is highly recommended that you open up some of them in the script editor and look at the syntax that is used and research what the scripts do. It is possible that you may be able to take one of the existing scripts and customize it to do what you want it to do.

If the script you are writing is complex it is highly recommended that you write the SQL and test it using the DBAISM database utilities before adding the SQL to the script editor. The database utilities offer better error checking and provide you with better tools for determining whether or not your SQL did what you wanted it to. The Database Utilities are installed automatically when you select the server install option for versions of Channergy 2010 or later. You can also download and install the utilities from here.

In order for the script engine to run correctly the following criteria have to be met.

The mws file needs to be installed in either the data directory or the root of the client-server folder.
Any reports that are requested by the script engine need to be installed in the associated reports folder.