# Poco::Data

# class SQLChannel

Library: Data
Package: Logging
Header: Poco/Data/SQLChannel.h

## Description

This Channel implements logging to a SQL database. The channel is dependent on the schema. The DDL for table creation (subject to target DDL dialect dependent modifications) is:

"CREATE TABLE T_POCO_LOG (Source VARCHAR, Name VARCHAR, ProcessId INTEGER, Thread VARCHAR, ThreadId INTEGER, Priority INTEGER, Text VARCHAR, DateTime DATE)"

The table name is configurable through "table" property. Other than DateTime filed name used for optional time-based archiving purposes, currently the field names are not mandated. However, it is recommended to use names as specified above.

To provide as non-intrusive operation as possible, the log entries are cached and inserted into the target database asynchronously by default. The blocking, however, will occur before the next entry insertion with default timeout of 1 second. The default settings can be overridden (see async, timeout and throw properties for details). If throw property is false, insertion timeouts are ignored, otherwise a TimeoutException is thrown. To force insertion of every entry, set timeout to 0. This setting, however, introduces a risk of long blocking periods in case of remote server communication delays.

A default-constructed SQLChannel operates without an active DB connection, in a store-and-forward mode. For this mode of operation, a separate service is required to consume and execute the SQL statements from the stored files and insert the log entries into the database. Since this setup stores SQL inserts in the OS file system, it is strongly recommended to take the necessary precautions to limit and secure the access to those files.

## Inheritance

Direct Base Classes: Poco::Channel, Poco::Runnable

All Base Classes: Poco::Channel, Poco::Configurable, Poco::RefCountedObject, Poco::Runnable

## Member Summary

Member Functions: close, getProperty, isRunning, log, logged, open, registerChannel, run, setProperty, stop, wait

Inherited Functions: close, duplicate, getProperty, log, open, referenceCount, release, run, setProperty

## Nested Classes

### class LogNotification

## Constructors

### SQLChannel

SQLChannel();

Creates SQLChannel. An SQLChannel without DB connector and local file name only logs SQL inserts into local files. A separate file is created for each insert batch; files are named <YYYYMMDDHH24MISSmmm>.<UUID>.log.sql.

### SQLChannel

SQLChannel(
const std::string & connector,
const std::string & connect,
const std::string & name = "-",
const std::string & table = "T_POCO_LOG",
int timeout = 1000,
int minBatch = DEFAULT_MIN_BATCH_SIZE,
int maxBatch = DEFAULT_MAX_BATCH_SIZE,
int maxSQL = DEFAULT_MAX_SQL_SIZE
);

Creates an SQLChannel with the given connector, connect string, timeout, table and name. The connector must be already registered.

## Destructor

### ~SQLChannel

~SQLChannel() override;

## Member Functions

### close

void close() override;

Closes the SQLChannel.

See also: Poco::Channel::close()

### getProperty

std::string getProperty(
const std::string & name
) const override;

Returns the value of the property with the given name.

See also: Poco::Channel::getProperty()

### isRunning

bool isRunning() const;

Returns true if the logging thread is running.

### log

void log(
const Message & msg
) override;

Writes the log message to the database.

See also: Poco::Channel::log()

### logged

size_t logged() const;

Returns the number of logged entries.

### open

void open() override;

Opens the SQLChannel. Returns true if succesful.

See also: Poco::Channel::open()

### registerChannel

static void registerChannel();

Registers the channel with the global LoggingFactory.

### run

void run() override;

Dequeues and sends the logs to the DB.

See also: Poco::Runnable::run()

### setProperty

void setProperty(
const std::string & name,
const std::string & value
) override;

Sets the property with the given value.

The following properties are supported:

- name: The name used to identify the source of log messages. Defaults to "-".

- connector: The target data storage connector name.

- connect: The target data storage connection string.

- table: Destination log table name. Defaults to "T_POCO_LOG". Table must exist in the target database.

- keep: Max row age for the log table. To disable archiving, set this property to empty string or "forever".

- archive: Archive table name. Defaults to "T_POCO_LOG_ARCHIVE". Table must exist in the target database. To disable archiving, set this property to empty string.

- async: Indicates asynchronous execution. When excuting asynchronously, messages are sent to the target using asyncronous execution. However, prior to the next message being processed and sent to the target, the previous operation must have been either completed or timed out (see timeout and throw properties for details on how abnormal conditos are handled). This property is deprecated and has no effect - all logging is asynchronous since the 1.13.0. release.

- timeout: Timeout (ms) to wait for previous log operation completion. Values "0" and "" mean no timeout.

- throw: Boolean value indicating whether to throw in case of timeout. Setting this property to false may result in log entries being lost. True values are (case insensitive) "true", "t", "yes", "y". Anything else yields false.

- minBatch: Minimal number of log entries to accumulate before actually sending logs to the destination. Defaults to 1 entry (for compatibility with older versions); can't be zero or larger than `maxBatch`.

- maxBatch: Maximum number of log entries to accumulate. When the log queue reaches this size, log entries are silently discarded. Defaults to 100, can't be zero or larger than 1000.

- maxSQL: Maximum total length of the SQL statement. Defaults to 65536.

- bulk: Do bulk execute (on most DBMS systems, this can speed up things drastically).

- file Destination file name for the backup FileChannel, used when DB connection is not present to log not executed SQL statements.

- flush Time in seconds to flush outstanding log entries; since logging is performed in batches of entries, the entries that do not make it into the last logged batch may remain unlogged for a long time during an extened period of inactivity. This setting ensures that unlogged entries are flushed in such circumstances, even when the minimum batch size was not reached. Zero value means no flushing.

See also: Poco::Channel::setProperty()

### stop

void stop();

Stops and joins the logging thread..

### wait

std::size_t wait(
int ms = 1000
);

Waits for the completion of the previous operation and returns the result. If chanel is in synchronous mode, returns 0 immediately.

## Variables

### DEFAULT_FLUSH_SECONDS

static const int DEFAULT_FLUSH_SECONDS = 10;

### DEFAULT_MAX_BATCH_SIZE

static const int DEFAULT_MAX_BATCH_SIZE = 1000;

### DEFAULT_MAX_SQL_SIZE

static const int DEFAULT_MAX_SQL_SIZE = 65536;

### DEFAULT_MIN_BATCH_SIZE

static const int DEFAULT_MIN_BATCH_SIZE = 1;

### PROP_ARCHIVE_TABLE

static const std::string PROP_ARCHIVE_TABLE;

### PROP_ASYNC

static const std::string PROP_ASYNC;

### PROP_BULK

static const std::string PROP_BULK;

### PROP_CONNECT

static const std::string PROP_CONNECT;

### PROP_CONNECTOR

static const std::string PROP_CONNECTOR;

### PROP_DIRECTORY

static const std::string PROP_DIRECTORY;

### PROP_FILE

static const std::string PROP_FILE;

### PROP_FLUSH

static const std::string PROP_FLUSH;

### PROP_MAX_AGE

static const std::string PROP_MAX_AGE;

### PROP_MAX_BATCH

static const std::string PROP_MAX_BATCH;

### PROP_MAX_SQL

static const std::string PROP_MAX_SQL;

### PROP_MIN_BATCH

static const std::string PROP_MIN_BATCH;

### PROP_NAME

static const std::string PROP_NAME;

### PROP_TABLE

static const std::string PROP_TABLE;

### PROP_THROW

static const std::string PROP_THROW;

### PROP_TIMEOUT

static const std::string PROP_TIMEOUT;