Database Read Modlet |
This document describes a TLI, which resides in a SoftPLC Java Modlet (Modlet) that is provided at no charge with all SoftPLC licenses. The Modlet is called JDBC_TLM and resides in a file with the name JDBC.java. This Modlet was developed by SoftPLC Corporation, but in order to apply it you will have to modify the source code and compile and zip it to a JAR file. As is, JDBC_TLM can be used to perform simple INT column reads from a SQL-compliant relational database. You will have to decide on the SQL query string to use and the column names to read. A single ladder instruction is implemented in the Modlet, however you can add additional ones that might vary on any of: table name, query string, or even SQL command. That is, you could change the code to do SQL UPDATEs rather than or in addition to SQL SELECTs
Overview | ||
The TLI included in JDBC.java is JDBC_READ, which is designed handle a single row read of columns given by an SQL query string. As written, only a single row is handled. (However you can modify this to you’re liking). The sample source reads only three INT columns but it is a simple matter to extend this to any number of columns, all of which would be INT.
From this point forward, this application note will assume that you intend to use it as is. Customization should be straight forward for someone familiar with Java. If you are not a Java programmer, you will likely need the help of one to do any major modifications to the source code. Refer the Java programmer to the SoftPLC Java API's which are online at http://softplc.com/api/index.htm.
Source Code | ||
The source code to the JDBC Modlet is available at http://dl.softplc.com/pub/JDBC.java. Download this file and save to your development system's disk as JDBC.java (as always, case is significant with Java source code filenames).
Instructions | ||
-
Obtain an SQL-compliant relational database server.
If you are not constrained to use one provided by the powers that be, you can use MYSQL. This is a free database server that runs on Linux or Windows. Download it from http://www.mysql.com/.
he JDBC.java is setup for this database. Download the latest server version for your server’s operating system.
-
Install the database server software on a server computer.
Consult the database server documentation on how to do this. If using MYSQL, this is fairly well documented.
-
Create a database called “test” and a table called “recipe”. We used a component called “JDBC Explorer” which is part of the ENTERPRISE version of JBUILDER, not the FOUNDATION version of JBUILDER. However there are numerous free MYSQL or JDBC clients that you can use to manipulate your relational database. Here is one for MYSQL:
http://www.mysql.com/downloads/gui-mysqlgui.html
Install the client on a development system. It may be the same system. The columns we used were Day, Month, Timer1, Timer2 and Timer3. All were type INT in the SQL vernacular. You will need to define a user and a password, and possibly need to tell the database from which IP ADDRESS this user is allowed to login from.
-
Obtain a Java compiler
You can use JIKES from IBM at http://www10.software.ibm.com/developerworks/opensource/jikes/ or the one in any JavaSoft Java Development Kit (JDK) from http://java.sun.com or you can download Borland’s JBUILDER FOUNDATION which is a free Java development environment from http://www.borland.com.
Jikes is the most direct path but it does not come with any API documentation. The JDK is the most authentic definition of a Java development platform with complete API documentation. JBUILDER is recommended only if you want to incur the additional learning curve of the JBUILDER IDE itself. It includes the JDK documentation and is a much larger download. JBUILDER makes sense if you have plans for Java beyond this Modlet. If you want to become a Java programmer use JBUILDER. If you just want a quick compile, download and use JIKES.
-
Obtain a type 4 JDBC driver (http://industry.java.sun.com/products/jdbc/drivers for you database. You can most, but not all, JDBC drivers from this website. You want a type 4 driver as a first choice. Type 3 may also work. Type 1 and 2 will not work on SoftPLC.
We used MM MYSQL version 2.0.7. You have to un zip the distribution JAR and then rename the enclosed JAR has a long filename and is the driver itself. Rename this enclosed jar file to shorter name compatible with SoftPLC’s 8.3 file system. E.g. JDBC_DVR.JAR
-
Obtain a JAR making tool such as ZIP.EXE or WINZIP.EXE. A JAR file is basically a ZIP file with a different file extension. You can download the public domain zip.exe and unzip.exe command line driven programs for Windows from:
http://dl.softplc.com/pub/zip.exe
http://dl.softplc.com/pub/unzip.exe -
On a windows development machine, edit the JDBC.java file. There is a section in the source bracketed as <Configuration Parameters>. Change the items in this section to match your setup. Make sure the source file retains the filename JDBC.java (case and spelling are significant in this filename).
-
Compile the JDBC.java file to JDBC.class. For example:
C:>jikes -classpath .;RT.JAR; JDBC.java -
Zip the JDBC.class file to JDBC.zip or JDBC.jar. This is the "JAR FILE". For example: C:>zip JDBC.JAR JDBC.class
-
Place the JDBC.JAR file on SoftPLC using a 3rd party FTP client like FileZilla in the C:\JAR\MODLET directory.
-
Place the JDBC driver JAR file into the C:\JAR directory on SoftPLC’s flashdisk.
-
Tell SoftPLC about the MODLET in the JDBC.JAR file and reboot SoftPLC. The JDBC's main classname (JDBC) should be added to your MODLET.LST file.
And the JDBC driver and JDBC.JAR both should be added to your CLASSPATH= statement in your MODULE.LST file (all one line):
driver=machj.tlm classpath = \jar\splc.jar;\jar\rt.jar;\jar\modlet\jdbc_tlm.jar;\jar\mm.mysql-2.0.7\mm.mysql-2.0.7-bin.jar;
-
Go online with TOPDOC and add the JDBC_READ instruction to the ladder program.
-
Figure out under what conditions the JDBC_READ instruction should be energized and add that supporting logic.