I need some examples of SQL statements and how they can be used within a JDBC Query operator.

Background: The Lifecyle of a JDBC data-source 

Each data-source connection is established at startup and a failure to connect within the timeout is a fatal server error. This initial connection is used to typecheck each JDBC Query operator and validate its output schema. Every result for the duration of the session (including after reconnection) is expected to match the verified schema. Reconnection behavior is controlled by properties for each data-source. A JDBC Query operator using a disconnected data-source will drop the query event unless the connection is being retried, in which case all query events are queued and will execute when re-connected.

Allowed statements

All SQL Data Definition Language (DDL) statements (for example: CREATE TABLE, CREATE INDEX, DROP TABLE) and Data Manipulation language (DML) statements (for example: SELECT, INSERT, UPDATE, and DELETE) which are supported by the JDBC driver for the database are accepted in the free-form SQL field of the JDBC Query operator.

If the set of statements returns a result-set, it must return only one, otherwise it must return no result. Individual scalar value results are not supported.

Behavior

If multiple statements are run from a single JDBC Query operator, these are treated as a single transaction and all will roll-back (excluding side-effects) if any one statement fails.

Only one SQL statement in the set of statements supplied to a single JDBC Query operator may return a result-set. For example, you cannot have two independent SELECT statements on one operator.

If there are complex relationships, calculations, variables, or finer transaction control needed for a single set of statements, place those statements into a stored procedure. Stored procedures must return one result-set or cursor (which may have zero or more rows). No plain type return values are supported.

The SELECT statement column names must be unique. For example, this cannot be executed to get a result set:

SELECT eq.*, ra.* FROM equities eq, ratings ra 
  WHERE eq.close_date={close_dt} and eq.symbol = ra.symbol 
  and eq.symbol in (SELECT symbol FROM ratings order by rating DESC limit 2);

because even though it is legal SQL, it raised the error in StreamBase:
 

InvocationTargetException: Invalid SQL expression "SELECT eq.*, ra.* FROM equities eq, ratings ra WHERE eq.close_date=? and eq.symbol = ra.symbol and eq.symbol in (SELECT symbol FROM ratings order by rating DESC limit 2);": StreamBaseIllegalArgumentException: Field list has multiple fields named "symbol"
java.lang.reflect.InvocationTargetException

This is because "eq.*" and "ra.*" both included a column named "symbol".

Instead, rename one of the fields, as so:

SELECT eq.*, ra.symbol as rsymb, ra.rating FROM equities eq, ratings ra 
  WHERE eq.close_date={close_dt} and eq.symbol = ra.symbol 
  and eq.symbol in (SELECT symbol FROM ratings order by rating DESC limit 2);

Example valid SQL statements

(PostgreSQL 9.1)
 

CREATE TABLE equities
(
  rid integer NOT NULL,
  symbol character(10) NOT NULL,
  close_date date,
  close double precision,
  volume integer,
  CONSTRAINT rq_rid_pk PRIMARY KEY (rid)
);
CREATE INDEX eq_symbol_idx ON equities (close_date);

CREATE TABLE ratings
(
  symbol character(10) NOT NULL,
  rating integer,
  CONSTRAINT symbol_ra PRIMARY KEY (symbol)
);
CREATE INDEX ratings_idx ON ratings (rating);

INSERT INTO equities VALUES
(1,'MSFT','03/02/2017',64.01,24510900),
(2,'MSFT','03/01/2017',64.94,26937500),
(3,'MSFT','02/28/2017',63.98,23239800),
(4,'MSFT','02/27/2017',64.23,15871500),
(5,'MSFT','02/24/2017',64.62,21796800),
(6,'MSFT','02/23/2017',64.62,20273100),
(7,'ORCL','03/02/2017',42.89,9418200),
(8,'ORCL','03/01/2017',42.92,11402200),
(9,'ORCL','02/28/2017',42.59,14450300),
(10,'ORCL','02/27/2017',42.7,13876500),
(11,'ORCL','02/24/2017',43.17,11426700),
(12,'ORCL','02/23/2017',42.96,15484000),
(13,'AAPL','03/02/2017',138.96,26153300),
(14,'AAPL','03/01/2017',139.79,36414600),
(15,'AAPL','02/28/2017',136.99,23482900),
(16,'AAPL','02/27/2017',136.93,20257400),
(17,'AAPL','02/24/2017',136.66,21776600),
(18,'AAPL','02/23/2017',136.53,20788200),
(19,'QCOM','03/02/2017',56.37,8415600),
(20,'QCOM','03/01/2017',57.01,11082400),
(21,'QCOM','02/28/2017',56.48,10388200),
(22,'QCOM','02/27/2017',56.73,7127600),
(23,'QCOM','02/24/2017',57.22,10644800),
(24,'QCOM','02/23/2017',57.14,10152800);

INSERT INTO ratings VALUES ('MSFT',4),('AAPL',5),('ORCL',3),('QCOM',3);

UPDATE equities SET close = 64.90 WHERE symbol = 'MSFT' and close_date='03/01/2017';

SELECT * from equities where symbol='MSFT' ORDER BY close_date;

SELECT eq.symbol, eq.close, ra.rating from equities eq, ratings ra where eq.close_date='03/02/2017' and eq.symbol=ra.symbol; 

If the input tuple has the field "close_dt" or type string or timestamp, then the following will work to return data from the tables defined above:
 

SELECT eq.symbol, eq.close, ra.rating from equities eq, ratings ra where eq.close_date={close_dt} and eq.symbol=ra.symbol;

SELECT eq.*, ra.symbol as rsymb, ra.rating FROM equities eq, ratings ra WHERE eq.close_date={close_dt} and eq.symbol = ra.symbol and eq.symbol in (SELECT symbol FROM ratings order by rating DESC limit 2);

What are the types of SQL statements allowed in the StreamBase JDBC Query operator?