Reading from Sockets

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Reading Data into s-Server > Reading from Other Sources  >

Reading from Sockets

Previous pageReturn to chapter overviewNext page

Configured for a socket, the ECD Adapter and Agent (ECDA) reads or writes data streamed from a client program using TCP or UDP. The adapter listens or inserts at a configurable port awaiting client connections. Incoming packets are streamed as described below.

Options Specific to Sockets

All adapter or agent implementations involve configuring options. For adapters, you configure and launch the adapter in SQL, using either server or foreign stream/table options. For agents, you configure such options using a properties file and launch the agent at the command line. Many of the options for the ECD adapter and agent are common to all I/O systems.

Implementing the ECD Adapter and Agent for Sockets

All adapter or agent implementations involve configuring options. For adapters, you configure and launch the adapter in SQL, using either server or foreign stream/table options. For agents, you configure such options using a properties file and launch the agent at the command line. Many of the options for the ECD adapter and agent are common to all I/O systems. The CREATE FOREIGN STREAM topic in the Streaming SQL Reference Guide has a complete list of options for the ECD adapter.

To read from a socket, you need to create a server object which references the ECDA data wrapper of type 'NET', as well as connection information for the socket, such as format type, port, character encoding, whether or not to write a header, and the host name. You could also configure this information as part of a stream definition, but it is best practice to define it as part of a server, since you can reuse the server object with multiple streams.

CREATE OR REPLACE SERVER "NetWriterServer" TYPE 'NET'

FOREIGN DATA WRAPPER ECDA

OPTIONS

(parser 'CSV',

server_port '5601',

character_encoding 'UTF-8',

write_header 'FALSE', );

 

Note: ECD Adapter server definitions need to reference the ECD foreign data wrapper. You can do so with the syntax FOREIGN DATA WRAPPER ECDA.

Next, you create a foreign stream object. Streams, like most SQL objects (but unlike data wrappers and servers), should be created within a schema. The following code first creates a schema in which to run the rest of the sample code below, then creates a foreign stream named "NetReaderStream."

CREATE OR REPLACE FOREIGN STREAM "NetReaderStream"

("recNo" INTEGER,

"ts" TIMESTAMP,

"accountNumber" INTEGER,

"loginSuccessful" BOOLEAN,

"sourceIP" VARCHAR(32),

"destIP" VARCHAR(32),

"customerId" INTEGER)

SERVER "NetReaderServer" ;

 

Implementing the ECD Agent for Sockets

The ECD agent takes similar options, but these options need to be formatted in a properties file along the lines of the following. These properties correspond to those defined for the adapter above.

# Port, format, host

PARSER=CSV

SERVER_PORT=5601

CHARACTER_ENCODING=UTF-8

SKIP_HEADER=FALSE

# Schema, name, and parameter signature of origin stream

SCHEMA_NAME='SOCKETWRITER'

TABLE_NAME=NetReaderStream

ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, VARCHAR(2040) route_variant_id)

 

Additional Columns Generated for Sockets

The following columns may be auto generated by the file adapter if you are doing a select, that is, reading from the file.

Column

Meaning

SOURCE_HOST

Input only. Remote host from which this row was read.

SOURCE_PORT

Input only. Remote port from which this row was read.