This is Licensed and Supported Software.This document may not be reproduced or redistributed without prior written permission of Hume Integration Software. Licensed users of HIS provided software have permission to reproduce or electronically distribute this document to support their usage of the HIS software.
©Copyright 2010, Hume Integration Software
All Rights Reserved
A central concept of the Datahub is managing application data using
tables. Tables provide an organized, generic approach to
managing data, so you wind-up with a less complex application that does
not have a mess of special purpose classes just to work with various
data items. You also immediately benefit with having features to
display, edit, save, export, and restore complex data without having to
develop any code to deal with each of your special purpose
classes. So this is the basic value proposition - you can buy a
more advanced starting point for your application and not re-invent
generic data management features.
The subscription features of the Datahub provide the ability for
application logic to be notified when table data that matches a
selection criteria is changed. Subscriptions can provide
efficient event propagation to update GUI screens that display status
information such as equipment alarms, production statistics, or data
graphs. Subscriptions can also send messages to client
applications that are running on different controller systems, so they
are also useful to propagate dynamic setup and configuration changes,
or to feed-forward process data throughout a distributed control system.
The Java Datahub can be run without any Java code customization but it is more common for the Java developer to embed its features in a custom application. The Java programming API of the Datahub provides for direct, type-safe access to table data and this can be simpler than using SQL statements, especially when working with one table row at a time. However, the expressive power of SQL lets you manipulate thousands of table rows in one statement. So most applications will use a mix of Java methods and SQL statements for data manipulation depending on the context.
In typical use, a Datahub is also participating in a DMH message system group. In this usage, the Datahub receives DMH messages sent to its command mailbox, and executes the messages as SQL statements. Usually these command messages are standard SQL statements such as the SQL insert statement or the SQL select statement. Data that is held in tables is accessible by any client application that connects to the Datahub using the DMH message system. For convenience to its message system clients, the Datahub accepts and processes certain statements that are not standard SQL, which allow the clients to access custom commands that the Java developer creates to extend the Datahub command set - see the eval and the lval command descriptions.
The Graphical User Interface (GUI) of the Datahub provides features
to display and manipulate table data. The GUI also has features
to help the developer such as the display of example programming
statements. There is also a console window
for
the input of SQL commands, and a status display for the DMH message
system. Various Java classes used by the GUI are available for
use in your own applications. The Java Datahub can be used with
or without the GUI being shown.
The Datahub uses a subset of ANSI standard SQL for database management. The basic SQL commands are supported for operations on one table per SQL statement. Table joining, grant and revoke, commit and rollback, creation of views, or data integrity constraints are not directly supported by the SQL command. However, because the SQL functionality is imbeddable in a Java application, it is possible to provide equivalent functionality to many of these advanced SQL features by writing custom methods.
Table names and column names can have 1 to 31 alphanumeric or underscore characters. They should start with a leading alphabetic character. You are able to use either alphabetic case for table and column names or for SQL keywords in your SQL statements. The Datahub retains the original alphabetic case of table and column names from the table create statement, and uses this for query replies.
SQL character strings must be delimited with single quotes, even when they are a single character. To imbed single quotes in a character string, double them, as in the example: 'Here''s Johnny'.
The Datahub is able to convert data types in SQL statements and expressions, between the basic integer, long, floating point, or character types. For example, the string constant '43' is acceptable as the value of an integer or as the floating point value, 43.0. Similarly, the numeric value, 3.14, if used in context where a character value is expected, will be treated as the character string '3.14'.
There is sufficent information in the reference section of this document to understand the syntax of SQL statements that are accepted by the Datahub. If you are new to relational databases, you may wish to obtain additional information on table design and data normalization.
Database tables are created using the SQL create
command, and destroyed using the SQL drop command.
Each column of a table is a specific data type such as INTEGER. The
supported
data types are described with the create command.
Data is added to a table one row at a time using the SQL insert
command. The Java Datahub also features the eval
store command which provides for adding more than one row at a time.
The SQL select command is used to query existing data. The SQL update command is used to modify stored data, and the SQL delete command is used to delete data rows.
When a table is created, one or more columns are specified as
primary
key columns. Often there is an obvious key value such a lot ID or
a name. In situations where there is not an obvious key, a
unique timestamp value (for example see com.hume.datahub.Localtime)
or a sequence number can be used. When key columns are used,
insertion of duplicate rows is
not allowed. Said another way, you cannot insert a row into a table if
a row already exists in the table with the same values of the key
columns.
In addition to preventing duplicate rows, keys are used to greatly
speed the
lookup
of rows, when processing the SQL insert, update,
delete, or select
commands.
If all of the values of the key columns are specified, the specific row
is located directly without performing comparisons against every row in
the table.
Using key columns also causes the select command to return row data in sorted order. In contrast to ANSI standard SQL, the sorted result is obtained by default, without using an ORDER BY clause in the SQL select command. If the keys are VARCHAR data types, the sorted order will be a proper ascending sequence of the primary key columns. If numeric data types are used as keys, the natural ordering is not as useful. However, the HubTable class has a boolean value which can be set to control whether additional sorting is performed on selection results to properly sort numeric data. See HubTable.setSortFlag().
When using the select, update, and delete commands you optionally provide a WHERE clause which specifies selection criteria for the affected rows. Also, a WHERE clause can be specified when using the SQL open command to establish a subscription only for rows meeting the WHERE clause selection criteria. A WHERE clause consists of one or more comparisons between a column value and a constant, or two column values. All of the usual SQL comparison operators are supported: equal (=), not equal (<>), less than (<), less than or equal (<=), greater than (>), and greater than or equal (>=). Compound conditions are expressed using the NOT, AND, and OR operators. Parentheses may be used to group terms. All of the comparison operators have equal precedence, and their precedence is higher than the NOT, AND, and OR operators. So, if you write
select * from equipment where machine='hx14' or machine='hx15'
it is equivalent to
select * from equipment where (machine='hx14') or (machine='hx15')
since the OR operator binds less tightly than the comparison operator =.
NOT has a higher precedence than AND which has a higher precedence than OR. If you write
select * from equipment where
class='hx' and not color='green' or machine='zq2'
it is equivalent to
select * from equipment where
((class='hx') and (not (color='green'))) or (machine='zq2')
Your WHERE clauses can also use the LIKE comparison operator to compare a VARCHAR field to a string constant pattern. Standard SQL uses the % character as a wild card that matches 0 or more characters, and the underscore, _, as a wild card that matches 1 character. So if you write
select * from equipment where machine like 'hxq%'
you will select machine values such as 'hxq14'.
The LIKE operator has been implemented using the java.util.regex.Pattern class. If you look at the reference for this class, you will see that it has more features than the simple matching characters of the standard SQL LIKE. These powerful features have not been disabled, and you are able to use them in your LIKE operator patterns. However, these features are not standard SQL, and you should consider that these features are not portable to other SQL databases. The other implication of not disabling these features, is that if your LIKE pattern contains special characters such as *, ?, or [ and ], you need to escape them using the backslash character to have them treated as regular characters. You may also precede the SQL wildcard characters % and _ with the backslash character in order to force them to be interpreted as ordinary characters.
Table data subscriptions are opened to have notification messages sent any time specified table data is changed, or, to have a Java method executed any time specified data is changed. There can be any number of subscriptions on each table. For example, you may wish to have each user interface open a subscription to a table that holds active alarm data so that a message is sent to every user interface when a new row representing a new alarm is inserted.
Another example use of a table data subscription is to perform realtime analysis on new measurement data using a method that executes in the Datahub. The analysis code is activated without the overhead of interprocess communication. The logic that produces the measurement data, whether it be an equipment interface, or user interface, is developed, tested, and deployed independent of the analysis logic.
The SQL open command is
used to create a table
data
subscription that provides message notifications. The Java developer
uses methods in the HubTable class such as subscriptionAddJava()
to create subscriptions that use method calls for notifications.
A
subscription is created for one
or more columns of a specific table for all rows which match specified
selection criteria. There is a SYNC option that causes
notifications
for all of the existing data matching the selection
criteria
at the time the subscription is opened. The SYNC feature makes it easy
for a client to synchronize with the current and future states of the
application
data. The ease of meeting this application requirement stands in marked
contrast to other CIM products which offer broadcast or subscription
based
message
propagation without any history.
The SQL close command is used to remove table data subscriptions.
You can execute the Java Datahub directly as the default application
of the Datahub.jar file. Since the Datahub compiles Java code for
tables at runtime, you also need to have the Java compiler tools.jar on
your classpath. A Windows user could execute commands such as:
to run the Datahub using the Datahub.jar in the working
directory.
Command line arguments to the Datahub program can be added to the
invocation above:
"%JAVA_DIR%\bin\java.exe" -cp "%JAVA_DIR%\lib\tools.jar" -jar
Datahub.jar -server DATAHUB@mbx
A Java developer is more likely to execute the Datahub.main from his
own main method. Here is the startup line with comments from an
example application:
// Show the Datahub GUI
and also initialize as a DMH Server.
// You can run more than one
DMH Server if each one has its own DMH
// group name. The
startup logic insures that only one instance is serving
// a particular group.
The -setup argument creates a temporary license if needed.
Datahub.main(new String []
{"-setup", "-gui", "1", "-server", "DATAHUB@mbx"});
The possible command line options are:
| <blank> |
<error> |
# |
CLOSE |
CREATE |
DELETE |
| DROP |
ECHO |
EVAL |
EXIT |
HELP |
INCLUDE |
| INFO |
INSERT |
LVAL |
OPEN |
PING |
READ |
| REM |
SELECT |
UPDATE |
WRITE |
| <blank> | |
| SQL Syntax | |
| Examples |
|
| Description | A blank input line is somewhat exceptional in that the reply is also a blank line. The return code for the sql() method is 0. |
| Replies |
| <error> | |
| SQL Syntax | |
| Examples |
this text is not a valid SQL statement |
| Description | If the input line does not begin with a recognized SQL
command, the error message is similar to the following. If the
leading token of the command is recognized, the usage syntax from the
help command is shown. |
| Replies | -1 <UNKNOWN> {Unrecognized command, enter "help" for
command list.} -1 INSERT {INSERT INTO table [( column [,column]*)] VALUES ( value [,value]*)} |
| # | comment |
| SQL Syntax | # comment text |
| Examples |
# Comments are useful in include files or for annotating console interaction. |
| Description | This command lets you lets you add comments to files of SQL commands that will be processed using the SQL include command. The # command and the rem command are equivalent. |
| Replies | 0 # {} |
| CLOSE | Close a subscription |
| SQL Syntax | CLOSE SUB subname TO table |
| Examples |
close sub dataSaver to SmifPod |
| Java Equivalent | tableObject.subscriptionClose(subname); |
| Description | This command removes the subscription named subname from the table named table. Use info, or the Datahub GUI to display existing subscriptions. |
| Replies |
0 CLOSE {Table table, subscription subname closed.}
|
| CREATE | Create a new database table |
| SQL Syntax | CREATE TABLE table ( colname
type [, colname type]*,
PRIMARY KEY(colname [,colname]*)) type := { INT[EGER] | LONG | VARCHAR | VARCHAR(n) | FLOAT | DOUBLE [PRECISION] } n := non-zero unsigned integer |
| Examples |
"create table SpcType (typecode varchar, description varchar(80), primary key(typecode))" |
| Description | The SQL create statement creates an empty database table.
Each column of the table is a specific datatype:
The PRIMARY KEY( colname, [, colname]*) declaration does not
have to be
the last declaration phrase as long as the column names being declared
as keys have already been specified. You may use more than one
PRIMARY
KEY declaration phrase to declare a composite key. The
model syntax
above only shows the most concise way of declaring keys. See the MakeTableFiles.java
demonstration application for a useful example of preparing TableRow
.java files at development time in order to support Java code
development. In brief, you can manipulate Table data using type
safe Java method calls if you prepare TableRow .java files as part of
your application. There is no set limit to the number of rows that are stored in a table. If you will be querying tables that have many thousands of rows, you should design your application to use the primary keys for most queries so that performance scales well. There is no set limit to
However, if you want to interoperate successfully with the Tcl/Tk Datahub, then you should limit the number of characters in an SQL command to be less than 70,000 and the number of lexical tokens in an input line has to be less than 2500. This imposes a generous limit on the amount of data that can be stored in a single table row. These figures also imply that the maximum number of columns is limited to some value over 1200. Similarly there is no fixed limit on the size of a query result. If your operating system and TCP/IP implementation can handle it, you can successfully manipulate multiple megabyte queries. Use info, or the Datahub GUI to display which tables have been created. Use drop to delete tables. |
| Replies |
0 CREATE {table - table created.}
|
| DELETE | Delete rows from a table |
| SQL Syntax | DELETE FROM table [WHERE search_condition] |
| Examples |
delete from alarmsHistory where tsReported < '2010-07-05 00:00:00' delete from someTable delete from spcData where toolID like 'HXQ%' |
| Java Equivalent | tableObject.deleteRow(rowObject); tableObject.deleteWhere(whereClause); |
| Description | This command deletes rows that are selected by the search_condition from the table named table. If no WHERE clause is supplied, all of the rows are deleted. See The Where Clause for the grammar of the search_condition argument. |
| Replies |
0 DELETE {table - 1 row deleted.}
|
| DROP | Destroy an entire table |
| SQL Syntax | DROP TABLE table |
| Examples |
drop table reportTemp |
| Java Equivalent | Datahub.tableDrop(tableName); tableObject.drop(); |
| Description | This command destroys the named table, freeing the memory
resources it was using. Subscriptions on the table are closed as it is
dropped.
It is more efficient to empty a table
using delete than to drop and re-create it. Because of the behavior of Java with loading and caching class
files, you cannot dynamically drop a table and recreate it using the
same name with a different schema. |
| Replies |
0 DROP {table - Table Destroyed.}
|
| ECHO | send the arguments as a reply |
| Syntax | ECHO textArguments |
| Examples |
echo hello world |
| Description | This command exists for compatibility with the older
versions. It can be useful for inter-process communication
performance testing. |
| Replies |
0 ECHO {textArguments}
|
| EVAL | evaluate custom procedures |
| Syntax | EVAL procName [arg [arg]*]* |
| Examples |
eval dmh help eval localtime 15 eval pwd |
| Description | This command provides a means for message system clients to
execute custom Java commands in the Datahub process. Use
the command "help eval" to obtain a list of the installed custom
commands. Command arguments are parsed using list format
conventions where white space and braces or double quotes are used to
separate arguments. The eval command feature is provided for the developer to add his own commands to extend the set of commands available to the Datahub clients. The Java developer uses the Datahub.addEvalProc() method for this purpose. There are a number of built-in commands which support features such as obtaining clock values from the server, sending DMH messages, or accessing the file system when using the Java Datahub client application. Use the eval dmh command to send DMH messages if you have initialized the DMH message system: # obtain a syntax summary for the
eval dmh command
eval dmh help # obtain the last DMH message read from a mailbox eval dmh lastRead box # send a message to a mailbox eval dmh put box msg # send a message requesting an asynchronous reply eval dmh putr destBox replyBox msg # send a message and wait for the reply eval dmh xact box msg Other built-in commands include: # obtain the server's TCP/IP
hostname
eval dp_hostname # localtime examples # The default format is "yyyy-MM-ddTHH:mm:ss.mmmmmmZ", the Z implies the UTC timezone (GMT) eval localtime 0 2010-09-06T15:41:08.900998Z # format 9 is YYYY-MM-dd HH:mm:ss for the local timezone eval localtime 9 0 {2010-09-06 10:45:00} # format 10 is YYYY-MM-dd HH:mm:ss for the UTC timezone eval localtime 10 0 {2010-09-06 15:45:56} # format 15 is a higher resolution timestamp for the local timezone eval localtime 15 0 {2010-09-06 10:47:03.213666} # format 16 is a higher resolution timestamp for the UTC timezone eval localtime 16 0 {2010-09-11 13:37:41.229947} # eval pwd obtains the server's working directory eval pwd 0 {C:\hume\java} # Use eval show to have the Datahub show or hide the user interface eval show gui 1|0 # The store command can insert or update multiple rows of table data at once. # If a row with the same key value does not exist, an insert occurs, otherwise the existing row is updated. # The return code is the number of rows stored. # eval store table [col [col]*]* {rows | row} info schema abc select {abc Schema} {{Column Name} Key Type} {{{Column Name}}} 3 {} {{a 1 varchar(20)} {b {} int} {c {} long}} eval store abc {a b c} {{one 1 1} {two 2 2}} 2 {2 rows stored in table abc} # insert eval store abc {three 3 3} 1 {1 rows stored in table abc} # update the row just added eval store abc {three 33 3333} 1 {1 rows stored in table abc} |
| Replies | The reply value of eval commands is a two-element list
consisting of a return code integer and a string result. The
expected convention is that the return code value is negative for
error, zero for success, or greater than 0 for success with a warning
or information message. |
| EXIT | shutdown the Datahub process |
| Syntax | EXIT |
| Examples |
exit |
| Description | The exit command is used to terminate the Datahub
process. The termination occurs shortly after the command is
received so that the reply message can be sent. |
| Replies |
0 EXIT {shutdown initiated}
|
| HELP | display command usage |
| Syntax | HELP [commandName] |
| Examples |
help help create |
| Description | When used with no arguments, the help command returns a
selection result which contains the syntax summaries for all of the SQL
commands. When used with a command name argument, the help
command replies with a syntax summary for the particular command. |
| Replies |
0 HELP {command syntax summary}
|
| INCLUDE | process a file of SQL commands |
| Syntax | INCLUDE pathname |
| Examples |
include c:/home/cimdev/config.sql include startupData.tab |
| Description | The include command reads and processes a file of SQL
commands. The logic
reads each
text line in the file as
a new SQL command except if the ending newline occurs inside of an SQL
string constant or inside of an unfinished list format data item. In
the latter cases, additional lines
are read until the SQL command is complete. Semi-colons are
ignored, they are not used as command delimiters.
Relative pathnames are with respect to the current working directory of the Datahub process. We suggest you specify the pathname using
Unix style slashes even on Windows platforms for consistency. You
may optionally surround the pathname argument with SQL style single
quotes, or use surrounding braces and or double quotes per list
formatting conventions. The INCLUDE command may be nested inside of included files to arbitrary depths. |
| Replies |
A selection result reply which contains the reply value of every included command. |
| INFO |
Obtain status information |
| Syntax | INFO { TABLE | SCHEMA table | SUB table | VALUES } |
| Examples |
info table |
| Description | There are four variations of the INFO command:
|
| Replies |
See the specification of the selection list reply format. |
| INSERT | add a new row to a table |
| Syntax | INSERT INTO table [(column [, column]* )] VALUES (value [, value]*) |
| Examples |
insert into SpcChartType (code, description) values ('X', 'XBar, S') insert into SystemConfig (name, value) values ('HttpPort', '80') insert into abcd (a,b,c,d) values ('', 0, 0, 0.0); |
| Java Equivalent | tableObject.insert(row); |
| Description | This is a standard SQL syntax for adding a data row to a
table.
Columns that are not specifically named are given the default values
of 0 for numeric fields and the empty string, '', for character data.
As a portability issue you should know that some persistent databases do not allow empty strings to be inserted for character fields that are primary key columns. |
| Replies |
0 INSERT {table - row inserted.}
|
| LVAL | evaluate custom methods which return list results |
| Syntax | LVAL procName [arg [ arg]*]* |
| Examples |
lval help |
| Description | This command provides a means for message system clients to
execute custom Java commands in the Datahub process. Use
the command "help lval" to obtain a list of the installed custom
commands. Command arguments are parsed using list format
conventions where white space and braces or double quotes are used to
separate arguments. The lval command feature is provided for the developer to add his own commands to extend the set of commands available to the Datahub clients. The Java developer uses the Datahub.addLvalProc() method for this purpose. The feature provides for returning list or selection results using the same format as the SQL select command. There is only one built-in lval command - lval help. |
| Replies | The execution of an lval command returns a list result similar to an SQL select
command. |
| OPEN | create a new message subscription on a table |
| Syntax |
OPEN SUB subname TO table SENDTO=mailbox [REPLYTO=mailbox] [LISTFORMAT [ARGDATA=data]] [INSERT] [UPDATE] [SELECT] [DELETE] [DROP] [CREATE] [SYNC] { * | colname [, colname ]* } [WHERE matchingCriteria ] |
| Examples |
|
| Java Alternatives | A Java developer can use the
following Datahub methods to register callback objects which are
notified using method invocations instead of receiving notification
messages.subscriptionAddJava(SubscriberItf receiver,
String subname, String[] subcols, boolean wantInsert,
boolean wantUpdate, boolean wantDelete,
boolean wantDrop, boolean wantSync, String whereClause,
Object argdata)subscriptionAddJavaSync(SubscriberItf receiver,
String subname) subscriptionAddSql(SubscriberSqlItf sqlReceiver,
String subname, String[] subcols, boolean wantCreate,
boolean wantInsert, boolean wantUpdate,
boolean wantSelect, boolean wantDelete, boolean wantDrop,
boolean wantSync,
String whereClause, Object argdata) subscriptionAddSqlSync(SubscriberSqlItf sqlReceiver,
String subname) |
| Description |
The SQL open command opens a subscription to the table named table
for the purpose of obtaining message notifications when data changes
matching the subscription criteria occur. When opening a subscription, you specify the
mailbox to
receive the notifications using the SENDTO=mailbox clause.
By default, the message notifications will be SQL statements. Specify
the LISTFORMAT keyword in order to receive messages that are
formatted as list data - see the List Reply
Format section for details.
With the List Reply Format, you are also able to specify optional data
using the clause ARGDATA=data.
If this argument option is specified, then an
additional list element is appended to the message data list and the
value of this element is the data
argument that you have specified. Use the ARGDATA
option to pass additional context information. When using the ARGDATA = data option, the data value is formatted as a single argument using the formatting rules of list arguments. You may use braces { } or double quotes to delimit an argument containing spaces. Do not use SQL style single quotes. For example, ARGDATA = {this is correct}, or ARGDATA = "also correct", or ARGDATA = OneTokenCorrect. You
may optionally specify the REPLYTO=mailbox clause to indicate a
reply mailbox for the notification messages. The default value of the
REPLYTO mailbox is NULL, which is a special value that means no reply
mailbox is applicable. You can use the reply mailbox as a mechanism
to indicate
which Datahub or which subscription a subscription message came from as
long as your REPLYTO mailbox value is acceptable as a single token
mailbox name. You are able to choose the type of data changes you wish to
receive
notifications for by optionally specifying one or more of the keywords
INSERT, UPDATE, DELETE, or DROP. If you do not specify any of these
data change types, then you receive notifications for all of them. The SELECT option affects UPDATE notifications. If the
SELECT option is specified, then the update notification includes every
column value in the subscription's list whether the column value has
been changed or not. If the SELECT option is not specified, then
the update notification only includes column values whose data has been
changed by the update. The SYNC option causes your subscription to be notified immediately with insert notifications for each row in the table selected by your specified or implicit matchingCriteria. This option lets you synchronize with the existing data, and in the same action be registered for future changes. The default behavior is only to notify you of future changes. The CREATE keyword is used to receive an SQL create table
statement as
the
first SQL notification message.
The SQL create
table statement is for the entire table, not just the columns that you
are subscribing to. Using both the SYNC and CREATE options is a
convenient way to get all of the SQL statements needed to replicate a
table.
For example, you can use the SYNC and CREATE options and specify as the
SENDTO destination the mailbox of another datahub, to create a copy of
a table
that stays in synchronization with changes to the original table.
The CREATE option is only used with SQL formatted message notifications
- it cannot be used with the LISTFORMAT option. Specify a list of specific column names for your
subscription,
or use * to indicate all columns. Whether you ask for them or not,
all of the key columns for the table are included in your column list,
and included in all of your notifications. This
is done so that your notifications are unambiguous. You specify a
column name to indicate you would like
to be notified if a value in that column is updated, or you would like
to know the new value of that column when a row is inserted.
When a row is updated, update notifications always include values for
all of the key columns,
and values for the non-key columns requested by the subscription if
they have been changed in the update. You may use the SELECT
option, as described above to control whether column values that have
not changed are included in the update notification. An update
notification is delivered only if at least one subscribed column value
has been changed. For example,
if an update statement is processed that specifies updating a
field to its current value, no notifications are delivered. When opening the subscription you optionally supply a WHERE clause to specify row selection criteria for your notifications. The default is that you are notified for changes to any row. See The Where Clause for the grammar of the matchingCriteria argument. Here is a fine point on update notifications. The where clause is evaluated on the updated row data, not on the original row data. If your where clause specifies comparisons on non-key column values, (1) you will see update notifications for rows that are updated to match the where criteria from a previous unmatching state, and (2) you will not see update notifications for rows that are updated such that they no longer match your where criteria. If the value of a primary key column (or columns) is (are) updated, the Datahub processes two notifications; a delete notification for the original value of all the key columns, followed by an insert notification for the row data with new key column values. This behavior guarantees that if a WHERE clause is expressed only on values of key columns, the subscription notifications synchronize perfectly with the selected subset of the table. If a single update notification were sent, (1) a selective subscription would not be notified when a row is updated to no longer match the WHERE criteria, and (2) a selective subscription would receive update notifications for rows that it did not previously know about, as they were updated to match the WHERE criteria. There can be any number of subscriptions on a table. The subscription name, subname, must be unique among all of the subscriptions to table table. Subscription names follow the same conventions as table and column names, except that they can be as long as 80 characters in length. You use the subscription name with the close command to close a subscription. As each table data change occurs, the table
subscriptions are scanned and notifications for each matching
subscription are queued in sequence for delivery. The queue of
subscription notifications is processed asynchronously by the usual
event loop processing with a limit on the continuous amount of time
consumed by notification processing so that the usual GUI events can
also be dispatched. Because the notifications
are queued and delivered after the actual data change, it is possible
that there have been subsequent changes to the table when a
notification is received. In other words, an application cannot
assume that it is synchronized with the current state of table data
since there may be newer change notifications queued for future
delivery. You should avoid performing lengthy operations, or blocking
operations, such as waiting for user input in your subscription
handling logic. The Java Datahub has a subscription feature that can be used
to obtain notifications when new Datahub tables are created or when
tables are dropped. The Datahub behaves as if there is a built-in
table named tablelist which
can be referenced for subscriptions or selections. Subscription
notifications or selections from the tablelist
table provide one column value, tablename.
Newly created tables provide an insert notification to the tablelist table subscribers, and
dropped tables result in row deletion notifications. A Java
developer can have java method subscription notifications for the
tablelist virtual table
using the Datahub.tablelistSubscribe()
method. This feature makes it possible to create an application
which can maintain complete synchronization with the table data of a
Datahub without polling to discover if new tables have been created. |
| Replies |
0 OPEN {table, subscription subname opened.}
|
| PING | verify online responsiveness |
| Syntax | PING |
| Examples |
ping |
| Description | The ping command can be useful for verifying online
responsiveness or benchmarking send-and-reply performance from a client
workstation. |
| Replies |
0 PING {ServerMailbox isoTimeStamp}
|
| READ | create a populated table from a saved file |
| Syntax | READ [OVER] table |
| Examples |
read over lotdata |
| Description | This command restores a table with its data from a
previously
saved image. The saved image is created using the write
command. The OVER option
is used in order to replace an existing table using the read
command.
The read command expects to find a file of
the name table.tab in its current working directory in order
to process table table. The include command can be used instead of the read command since a saved table image is a file of ordinary SQL statements. The include command allows for specifying a relative or absolute pathname to the file, and it does not drop an existing table, or delete the existing data, or close subscriptions the way the read over command does. Subscription information is not saved and restored using read and write. When you use the OVER option and replace a table, existing subscriptions are all closed. |
| Replies |
0 READ {table - Table read from file "table.tab"}
|
| REM | comment |
| Syntax | REM comment text |
| Examples |
rem uncomment the following statements for option B |
| Description | This command lets you lets you add comments to files of SQL commands that are processed using the SQL include command. The # command and the rem command are equivalent. |
| Replies | 0 REM {} |
| SELECT | query table data |
| Syntax | SELECT { * | column [, column]* } FROM table [ WHERE where_criteria ] |
| Examples |
select helpText from HelpConfig where lotID='*' and step='etch' select * from TraceMessage where timestamp<='2010-12-31' |
| Java Alternatives | tableObject.select(String whereClause)
tableObject. selectAll()
|
| Description | select is used routinely to lookup stored table data. You can use the symbol * to specify that you would like all of the column values in your query, or you can explicitly list the columns of interest. If you do not provide a WHERE clause, you obtain all rows of the table. See The Where Clause for the grammar of the where_criteria argument. |
| Replies |
Selection results use the List
Reply Format which
is described in its own section. select tablename colList keyColList rowCount errorInfo dataRows |
| UPDATE | change existing rows in a table |
| Syntax | UPDATE table SET column=value [ , column=value ]* [ WHERE where_criteria ] |
| Examples | update Alarms set isSet=1 where alarmID=5012 and
spname='etch1' update LotData set processState='Completed' where lotID='6809HXQ15J' |
| Java Equivalent | tableObject.update(tableRow)tableObject.update(oldRow, newRow) |
| Description | This is the ANSI standard SQL statement for modifying the data of a single table. If you do not provide a WHERE clause, you update all rows of the table. See The Where Clause for the grammar of the where_criteria argument. |
| Replies |
0 UPDATE {abc - N row[s] updated.}
|
| WRITE | save a table to a file |
| Syntax | WRITE [OVER] table [pathname] |
| Examples |
write over TraceMessage write over SystemConfig backup/SystemConfig.tab |
| Java Equivalent | tableObject.write(String pathname,
boolean overWrite) |
| Description |
This command writes a create statement and insert statements representing a table to a file. The file can be read and the table restored using the read command or the include command. If pathname is specified, it is used as the path and
file
specification
for the table information. Otherwise, the command creates a file by the
name of table.tab in the current working directory. The OVER keyword is used to specify that overwriting an
existing file is permitted. |
| Replies |
0 WRITE {table - table data written to file "file" - 1 row}
|
The format is a seven element string list. Each element is now described.
| Element | Index | Description |
| notification | 0 | This element will be the literal string select for a selection result or list reply. For a subsciption notification, it will be the literal text insert, update, delete, or drop. |
| tablename | 1 | This item is the name of the database table. The table name
is
formatted exactly as it was in the table's create
statement. For non-table selections, the tablename may be more than one
token; for example the "info table" command sets this element to Table List. |
| columns | 2 | This element is a list of the column names in the same sequence as the data items in the dataList element. For a select statement, the columns are in the order of your request, or in the table create statement order if you queried for *. For a subscription notification, the list will always include the primary keys of the table. |
| keyColumns | 3 | This element will be a list of the column names of the primary key columns. |
| rowCount | 4 | This element is a count of the data rows in the dataList element. For the usual insert, update, or delete subscription notification, the value is 1 because a separate notification is issued for each row. |
| errorInfo | 5 | If your query has an error such as referring to a column or table that does not exist, or a syntax error, this element is a string containing a non-zero numeric return code, followed by a diagnostic message. If you have a valid query that does not find any data rows, it is not an error, and the errorInfo element is an empty string. |
| dataList | 6 | This element is a list of data rows. Each data row has the elements named in the columns element, in the same order. Character strings that have imbedded white space are delimited using braces. There are as many rows as specified by the rowCount element. |
A Java programmer can choose among several classes to parse and use
list results:
For building lists, the com.hume.DMH.ListBuilder
class has better performance and more convenient usage than the above
classes.
// SQL selectString reply = Datahub.sqlReply("select * from SchemaDescription
where
" + whereClause);String [][] data;String [] colnames;try { TclList parts = new TclList(reply); if(parts.llength() != 7) return; colnames = TclList.split(parts.lindex(2)); int colCount = colnames.length; TclList rows = new TclList(parts.lindex(6)); int rowCount = rows.llength(); data = new String[rowCount][colCount]; for(int i=0; i < rowCount; i++) { TclList row = new
TclList(rows.lindex(i)); for(int j=0; j <
colCount; j++) {
data[i][j] = row.lindex(j); } }} catch (ListFormatException e) {}BOLD is used to indicate commands, keywords, or punctuation that needs to be entered as written. With the SQL commands, the keywords are written in uppercase for emphasis, but you can enter them in lowercase too.
Italics are used to indicate names or other items where you supply your own instance value. For example, where we have written table you are expected to supply the name of a table.
We use square brackets to enclose items that are optionally specified. For example [WHERE where_criteria] indicates this clause may be omitted. The square brackets themselves are not entered.
When you see the * character following a phrase, it means the phrase is optionally repeated 0 or more times. For example, column [, column]* indicates that one column name must be supplied, followed by zero or more , column phrases.
Finally, the braces are used to show sets of alternate choices, with the choices separated by the | character. For example, { * | column [, column]* }. This example is shorthand for choosing either the * character or a comma separated list of column names. The braces and the | character are part of the notation and are not entered when the command is used.The new Java Datahub developed by Hume Integration Software is a revision of our Tcl/Tk Datahub which uses C code for its table management and DMH communication features. The Java Datahub is written entirely in Java and it is a substantial revision with some significant differences.
The remarks in this section provide guidance to porting Datahub
applications between the new Java version and the older Tcl/Tk version.
Date of last revision: $Date: 2011/01/16 20:13:38 $
This document covers the Hume Integration Software developed Java Datahub that is available for the Java SDK 1.6+ platform on the Windows and POSIX platforms.