Mai Technology Ltd

MWSQL arises from a need to develop migration scripts, automated reports and 2 way SQL based data entry screens quickly with little need for documentation as all the processes are script driven. The script is easy to learn and extended from standard SQL. Relatively complex operations can be written without the need for compiled source code with is easier to support and debug.

In the evolution of the program, there is a simple progression of increasingly complex requirements which are reflected in the design of the program.

A simple SQL Client.
The ability to run scripts
The ability to upload csv files into the database
The ability to view 2 databases simultaneously
The ability to upload tables between the two databases which results in the addition of the Scheduler panel
The ability to synchronise tables between databases dependant on primary fields
The ability to schedule synchronisation scripts for overnight processing.

Starting MWSQL

MWSQL is a java client. The exact command to start it will be dependant on where the java run time is installed. An example start up is given here:

C:\JBuilder4\jdk1.3\bin\javaw -classpath "C:\java1.3.1_02;C:\JBuilder4\lib\jbcl.jar;C:\JBuilder4\lib\dbswing.jar;C:\JBuilder4\lib\parser.jar;C:\java1.3.1_02\dom.jar;C:\java1.3.1_02\dx.jar;C:\java1.3.1_02\fop.jar;C:\java1.3.1_02\jdom.jar;C:\java1.3.1_02\avalon-framework.jar;C:\java1.3.1_02\struts.jar;C:\java1.3.1_02\struts-legacy.jar;C:\java1.3.1_02\mail.jar;C:\java1.3.1_02\mailapi.jar;C:\java1.3.1_02\j2ee.jar;C:\JBuilder4\jdk1.3\demo\jfc\Java2D\Java2Demo.jar;C:\JBuilder4\jdk1.3\jre\lib\i18n.jar;C:\JBuilder4\jdk1.3\jre\lib\jaws.jar;C:\JBuilder4\jdk1.3\jre\lib\rt.jar;C:\JBuilder4\jdk1.3\jre\lib\sunrsasign.jar;C:\JBuilder4\jdk1.3\lib\dt.jar;C:\JBuilder4\jdk1.3\lib\tools.jar;c:\java1.3.1_02\mysql-connector-java-2.0.14-bin.jar" MWSQL.ClientJ2

Startup options

-dw - open the debug window on startup

-v - make operations verbose in the debug window

c:\\java\mwsql\profile.mwq - load the specified startup file on startup

Opening a database connection

When MWSQL client starts up initially there is no connection to databases for either of the SQL panels. To open a connetion to at database, click on the blue question mark at the bottom of the SQL panel. A window will open prompting for the JDBC connection parameters. Either enter the connection parameters, or click on open to load from a file. A connection file example is given here:

www.mantaray.co.uk

org.gjt.mm.mysql.Driver

jdbc:mysql://www.allmydates.karisto.net:3306/allmydates_com?user=mwatts&password=maitech

On the connection window, only the class, url , database and password need to be specified.

SQL Commands

As well as the standard SQL commands there are some additional commands which may be run in the window.

backup

backup specified table to tab file

The location of the backup file directories is can be set from the main menu on the client. When no location is specified, then the default working directory is used.

example: backup tblSKU

setExportDeliminator

change the export deliminator (default is tab) to a different string.

When the export deminator is to include a space, the string SPACE is used.

Example: setExportDeliminator:,

Example2: setExportDeliminator:*SPACE*

executesqltofile

export SQL resultset to file

Parameters: file, append to existing file, include column names, SQL

Example : source executeSQLToFile:t://temp/activestock.tab,false,false,select

tblSKU.txtID ,

tblSKU.txtDescription from tblSKU;

tabledef

print out a tabledefinition

This will only work if the database is fully JDBC complient

Parameters: tablename

example:

tabledef tblCategory

class

Run a java class. The class must implement the MaiArch.Report interface

Parameters class to run

Example:

class:MIS.MultiOptionSalesReport

Data Entry

Running the following commands will turn the read only results table into a read / write table. They can be run from the SQL window. The must useful for this, is when a sequence of SQL commands is saved into a file or a report in the database, and then run as a single file command. This allows basic single table data entry screens to be generated very quickly in script.

primary

- set a primary key for updating. Once the primay key is set then all fields become read and write. On an update, all records with the primary field value are changed, so if you get this wrong is it possible to update multiple re cords be mistake i.e. overwrite data

Parameters: name of primary field

example:

select txtID,txtName from tblCategory;

primary txtID

dropdown

- attache a drop down list to a field. This is most used when a report is defined and loaded MTR report.

Parameters; column number ( start is column number 0 ), SQL for drop down box field values

example:

select txtCountry,txtDescription from tblCountry;

primary txtCountry;

dropdown:1,select txtCountry from tblCountry;

autonumber

- allocate autonumber to edit field

This is used when the primary field is an autonumber i.e. it cannot be updated itself, but must be generated when new data is added

Parameters; name of the autonumber fields

Example

Select lngID , txtDescription from tblState;

Autonumber: lngID

· primaryautonumber

this is no longer live. Use the above file

example:

runreport

- load and run an mtr file

An MTR file is a media tools report file. These files contain SQL which is then run in the open report panel.

Parameters: file name

example:

runreport:c://fgh.mtr

Emailing

There is a buffer in the memory of the SQL client for building an email. The email may be built up from mutliple SQL commands and the sent. The email server setup parameters are in the properties file MSQL/ClientJ2.prop

The fields from the email client are as follows:

MailServer=192.168.101.125

MailUser=webdev21

MailPassword=nottherealpasswordjustanexample

Change the fields for other email server.

clearmailer

- Clear the email buffer

Clears the email buffer to start to build a new email

example:

clearmailer

prependmailier

- Add text to the end of the email buffer. The mail is sent as HTML text.

example:

prepanedmailer:<B>the next report</B>

prependmailerwithurl

- add contents of a url to email buffer

If the client is running on a network which uses a proxy then the parameters for the proxy are set up in the MSQL/ClientJ2.prop file.

NB there is a maximum file size which can be read of about 500K. if the URL is returning longer than that this can cause the SQL client to hang.

The fields for the proxy are as follows:

proxySet=true

proxyHost=192.168.101.125

proxyPort=3128

example:

prependmailer:http://www.google.co.uk

prependmailerwithtable

- add table to email buffer

the current table which is displayed in the SQL client is added to the email buffer as an HTML table

example:

select * from tblCategory;

prependmailerwithtable;

mailtable

- mail the email buffer appended with the current table to the specified client

Parameters: from, title, emails to comma deliminated

example:

mailtable:autogen@mediatools.co.uk,Report: Serial Items Valuation calculated as if were non serial, kennygraham@soundcontrol.co.uk,marknicol@soundcontrol.co.uk;

mailtablerows

- mail different table rows to different people ( one row is email )

This is useful for example if you get a report for all the sales figures for all the salesmen and want them to be mailed individually the row which applies to them. The target emails must be in the table.

If there is more than one row with the same email, then all those rows are mailed in one email as a table.

Parameters: from, title/description, email column

example:

select txtEmail,txtName from tblUser where txtName is null;

mailtablerows: autogen@mediatools.co.uk, Please add your name to the TrackII system, 0

checkmailtable

- mail the email buffer appended with the current table to the specified client provided the current table is not empty. Useful for only sending emails when something is wrong.

Parameters: from, title, emails to comma deliminated

Example:

Select * from tblOrderItem WHERE tblSKUtxtID = ‘ALEXIS-TDK2000’

checkmailtable:autogen@mediatools.co.uk,Report: Alexis tdk 200 sales, kennygraham@soundcontrol.co.uk,marknicol@soundcontrol.co.uk;

maildiagnostics

mail ip of running computer and diagnostics

Useful when you do not know what computer is running a script but it keeps being run. This will tell you.

Parameters: none

example:

maildiagnostics

Utilities

sout

- write to sout

write to the debug window – useful for debugging

Parameters: script to write to the output window

example:

sout: we got to this point of the script OK!

writetolog

- write to the SQL log

Parameters: script to write to the output window

example:

sout: we got to this point of the script OK!

exit

- exit the system / stop the program

example:

maiAction

- source maiaction:0,MediaTools.Stock.StockItem,MediaTools.Stock.StockItemActionSynchroniseStarAndHInTracline

example:

copyfile

- copy a file on the network to another location

Parameters: sourcefilename, destinationfilenema

example:

copyfile:c://temp/data.csv,t://temp/newfile.txt

copyfilewithtimestamp

- copy a file on the network to new location adding the timestamp to the file name. This is useful if you need to backup a file multiple times during the day. The destination file is appended with the timestamp dd_MM_yy_hh_mm where dd is ay, MM is month, yy is year, hh is hour and mm is minute.

Parameters: sourcefilename, destinationfilenema

example:

copyfilewithtimestamp:c://temp/data.csv,t://temp/newfile.txt

executesqltofile

- write the results of the sql to a file

if AppendToFile is set to false, then the target file will be erased if it already exists, otherwise the data will be appended to the existing file.

The data is exported comma deliminated.

Parameters:File, Append to file?, Include column names? , SQL

example:

sexecutesqltofile:t://temp/export.txt,false,true,SELECT * from tblSKU;

setexportdeliminator

change the of executesqltofile command. The string SPACE is changed to a space in the specified deliminator

Parameters:File, Append to file?, Include column names? , SQL

Example1 set to tab delimnated:

setexportdeliminator: \t

Example1 set to < >:

Setexportdelimnator: <SPACE>

ftpfiletoserver

- ftp a file from the network to an ftp server

In order for this to work the Client must be started with ftpbean.jar in the classpath

Parameters:file,IP,targetfilename,username,password

example:

ftpfiletoserver:t:/temp/export.txt,192.168.100.1,/export/mdtls/temp/newfile.txt,martin,nottherealpassword

doMaiAction

instantiate an object from the database based on a column in the resultset, and perform an action class on it.

Parameters:primarykeycolum,class,actionclass

Example:

Select tblSKU.txtID from tblSKU WHERE tblSKU.txtID LIKE ‘ROLA%’;

DoMaiAction:0,MediaTools.Stock.StockItem,MediaTools.Stock.StockItemActionUploadToWeb

This example will upload all roland items to the web site.

N.B. these actions depend on a on of the SQL panels being set as the System data panel ( see profile file information below ). The source panel is the default panel to be used as the System data access.

Data upload panel

The data upload panel allows the importing of text files of data directly into the database on a per table basis.

Once the fields are mapped from the import file to the database fields, you must specify a primary key in the definition . As each line is processed, a matching record is searched for in the database. If a matching record is found, the line is processed with an UPDATE command. If the line is not found the line is processed with an INSERT command. In order to save time over a remote connection the user can specify that all lines should be INSERTED or all lines can be UPDATED. This can lead to errors in the following way.

if you specify INSERT only and the record exists already in the database then the line will not be processed.
if you specify UPDATE only and the record does not already exist then the line will not be processed.

Upload Steps for simple upload

Click the open file button and select the file that you want to upload OR paste the data into the upload text box and click the check box marked use this text not file
Select the delimnator for the data
Select the target table for the import
Match the import fields
Select the fields which are included in the primary keys
Click the go button.

Saving a profile

It can be quite time consuming defining an import profile. The buttons at the bottom of the import panel perform the following:

Copy copy the import definition to the clipboard

Paste past the clipboad from the panel into the data upload panel

Load load an import definition from a file

Save save an import definition to a file.

Below is a sample import definition based on the screen shot above:

description:

deliminator:,

Table:tblAddress

InsertOnly:false

Field: name,txtAddress1,true,%,null

Field: age,txtAddress2,false,%,null

Field: location,txtTown,false,%,null

Complex table data loads using java classes

The above described data import process will only allow you to impor directly into a single table. This relies on the user having a good understanding of the table structures in the system. For example, the web price of a stock item is not stored in the stock item table, but a linked table. Therefore if you wanted to import a file with the stockcode, description and web price in it, you would have to do 2 imports. In particular, some fields are not possible to import directly. These are the copy fields which are split across multiple records in the same table.

A factility is given whereby you can import via the getter and setter methods of any object in the MediaTools library. Instead of selecting the tablename you select the target class from the second drop down box and then set the import fields in an identical way using the getter method names. A screen shot of this is shown below. Again, the import profiles can be saved to file. These profiles should NOT be constructed without an understanding of how the target application works internally.

The Scheduler Panel

The scheduler panel is for running commands which can involve both databases.

Primarily this is used for performing data panel uploads between the source and the destination table using upload scripts defined in the upload panel and using the source panel selected data instead of a file.

Scheduler commands

Upload

- Delete the contents from the target database, and then upload all data from the source database into the table

Parameters: tablename

Example:

Upload tblCountry;

source

- Run the following command on the source database

Parameters: the command to run on the source

Example:

Source mailtable:autogen@mediatools.co.uk,Report: Serial Items Valuation calculated as if were non serial, kennygraham@soundcontrol.co.uk,marknicol@soundcontrol.co.uk;

dest

Run the following command on the destination database / see above for details

clearfunctions

Clear all functions in the function buffer

datapanelupload

Runs a datapanel upload script using the data in the source panel as data. This is used to synchronise tables where we do not want to upload all the data from the source, just particular fields. In the example below we select the ID and the post code from the customer table in the source database and then update those fields in the destination database.

source select TxtID, txtPostCode

FROM tblCustomer WHERE txtID LIKE ‘AB%’;

;

datapanelupload

description:

deliminator:,

Table:tblCustomer

InsertOnly:false

Field:tblCustomerTxtID,txtID,true,%,null

Field:txtPostCode,txtPostCode,false,%,null

Function definition

Simple

The scheduler panel holds a buffer of scripts which can be defined and referenced by name. This gives any database the ability to do sub queries which MYSQL cannot, and also gives a limited amount of server side scripting mimicry. Any command surrounded by curly brackets is kept as a memory script to be referenced later. E.g.

{

mailMartinTheCategories

select * from tblCategory;

Source mailtable:autogen@mediatools.co.uk,Report:mycategories, martin.watts@mediatools.co.uk;

}

would define a method which we could call later by running the command

mailMartinTheCategories;

Parameters

We can include parameters in the function in brackets, and by placing question marks in the function definition which will be replaced e.g.

{

mailTheCategories

select * from tblCategory;

Source mailtable:autogen@mediatools.co.uk,Report:mycategories, ?;

}

which could be called using

mailTheCategories(martin.watts@allmydates.com);

Subqueries

If the parameter is a SQL command, then the field returned from that command are substitued into the function and the function is run for each line of the SQL. This is particularly useful for uploading very large tables. E.G. to perform a customer upload for every letter of the alphabet we can use:

{

tblCustomerFullLoad

source select TxtID, txtPostCode

FROM tblCustomer WHERE txtID LIKE ?;

;

datapanelupload

description:

deliminator:,

Table:tblCustomer

InsertOnly:false

Field:tblCustomerTxtID,txtID,true,%,null

Field:txtPostCode,txtPostCode,false,%,null

}

;

tblCustomerFullLoad (select DISTINCT CONCAT(SUBSTRING(txtID,1,2),'%') from tblCustomer);

This last example is from the Myriad upload script where we are selectively clearing a table and uploading the first 10 stock quantities for each branch

//=================================Stock Quantity

;

{

doBranchUpload

dest DELETE FROM tblStockQuantity

WHERE tblBranchTxtID = ?;

source SELECT TOP 10

ProductCode as tblSKUtxtID,

CostCentre as tblBranchTxtID,

FreeStock as flQuantityOnHand,

AllocatedStock as flQuantityAllocated,

StockOnOrder as flQuantityOnOrder

FROM PRODUCT WHERE CostCentre = ?

AND ProductCode NOT LIKE 'P%'

AND ProductCode NOT LIKE 'S%'

;

datapanelupload

description:

deliminator:,

Table:tblStockQuantity

InsertOnly:true

Field: tblSKUtxtID,tblSKUtxtID,true,%,null

Field: tblBranchTxtID,tblBranchtxtID,true,%,null

Field: flQuantityOnHand,flQuantityOnHand,false,%,null

Field: flQuantityOnOrder,flQuantityOnOrder,false,%,null

Field: flQuantityAllocated,flQuantityAllocated,false,%,null

;

}

;

doBranchUpload(dest SELECT txtID,txtID FROM tblBranch);

Profile Files

Profile files contain information necessary to schedule MWSQL to run an automated process.

A profile file can be loaded either from the main menu, or specified as a parameter on the command line e.g.

Will start MWSQL.ClientJ2 and load the specified profile file. There follows and example of a profile file content:

sourceClass=com.hxtt.sql.paradox.ParadoxDriver

sourceURL=jdbc:paradox:/m:/Data

sourceUserName=

sourcePassword=

destClass=org.gjt.mm.mysql.Driver

destURL=jdbc:mysql://192.168.100.12:3306/dbIntegratedMIS

destUserName=LineIISync

destPassword=L2S

backupDirectory=C:\fgh

logDirectory=C:\fgh

sourceSQL=

destSQL=

schedulerSQL=t:\java\ScheduledReports\myriadupload\MyriadToMISScheduler.sql

runSource=false

runDest=false

runScheduler=false

nextProfile=

disableDialogs=true

exitOnEnd=false

systemDataAccess=source

Profile file parameters

SourceClass	Source Panel JDBC driver class name
SourceURL	Source Panel JDBC driver URL
sourceUserName	Source panel database user name
sourcePassword	Source panel database password
destClass	Dest Panel JDBC driver class name
destURL	Dest Panel JDBC driver URL
destUserName	Dest panel database user name
destPassword	Dest panel database password
backupDirectory	Location for backup directoyr
logDirectory	Location for log file
sourceSQL	File to be loaded into the Source SQL Panel
destSQL	File to be loaded into the Dest SQL Panel
schedulerSQL	File to be loaded into the Scheduler Panel