Database payload

The database can also be optionally used as a repository for both incoming and outgoing payload.

Note: Only a direct JDBC driver can be used for database payload; an ODBC connection cannot be used because it does not support streaming. Also, the database in use must support Binary Large OBject (BLOB) data types.

VLOptions database table

There is one and only one row in this table.
All of this can be configured either by using the Cleo Harmony, Cleo VLTrader, or Cleo LexiCom UI or by modifying the database directly.

Column Name	Data Type	Description
Maximum BlobSize	INTEGER	The maximum BLOB size supported by the database (incoming and outgoing payload will be stored in a BLOB data type) The JDBC interface limits this size to 2³¹-1 (2,147,483,647) bytes. Default: `65535 bytes`
Outgoing PollingInterval	INTEGER	The frequency at which VersaLex will check for new outgoing payload (`VLSend` and `VLOutgoing` tables) Default: `5 seconds`
Outgoing Timeout	INTEGER	For abnormally terminated or unresponsive sends, the timeout at which the send will be retried by either a parallel or restarted VersaLex Default: `30` minutes
ClearSuccessful Sends	BIT	Indicates whether successfully sent payload (`VLSend` and `VLOutgoing` tables) should be automatically cleared by VersaLex Default: `1` (`True`)
Maximum Attempts	INTEGER	Indicates maximum number of failed outgoing payload send attempts before retries are halted. Default: `0` (Indicates no limit)
Maximum Concurrent Sends	INTEGER	Maximum number of concurrent outgoing database payload actions that can be active at any given time overall. If the limit is reached and more outgoing payload is found, it is put on hold until one of the current outgoing database payload actions completes. Default: `50`
Max Concur Sends Per Mailbox	INTEGER	Maximum number of concurrent outgoing database payload actions that can be active at any given time for any given mailbox. If the limit is reached and more outgoing payload is found for a mailbox, it is put on hold until one of the current outgoing database payload actions for that mailbox completes. Default: 5
Bundle Same Mailbox Sends	BIT	At each polling interval, indicates to bundle payload for the same mailbox together and send one-by-one using just one mailbox session. Default: `0` (`False`)
Maximum Bundle Size	INTEGER	If bundling same mailbox sends, maximum bundle size allowed for one mailbox session. Default: `5`
Connection Poolsize	INTEGER	Indicates the number of database connections immediately obtained and continually reused. These connections are used strictly for database payload. Default: `20`
Include User Inbox Subdirs	BIT	Indicates whether files stored by a user in a subdirectory of their configured inbox should be inserted into the database. Default: `0` (`False`)
Database Payload Suspended	BIT	Indicates whether the database payload feature has been temporarily put on hold by a user Default: 0 (`False`)
AlwaysAll Mailboxes Receive	BIT	`True` if all incoming mailboxes should be used for database payload. Default: `0` (`False`)
Maximum Connections	INTEGER	The absolute maximum number of allowed database connections (including poolsize) for database payload Default: `0`
ReservedForIncoming	INTEGER	Percentage of the maximum number of database connections to reserve for incoming requests. Default: `33` (percent)
IncludeUserOutboxSubdirs	BIT	Indicates whether files stored by a connected HTTP, FTP, or SSH FTP client in a subdirectory of their configured inbox should be inserted into the database. Default: `0` (`False`)
IncomingStreamDirect	BIT	Indicates whether incoming payload should be streamed directly into the database or through a temporary file. Default: `1` (`True`)

VLMailboxes database table

The number of rows and the Host and Mailbox columns are maintained automatically by VersaLex.
The ReceiveIncoming column can be configured either via the Cleo Harmony, Cleo VLTrader, or Cleo LexiCom UI at Configure > Options > Transfers > Configure or modified directly in the database.

Column Name	Data Type	Length	Description
Host	VARCHAR	50	Active Cleo Harmony, Cleo VLTrader, or Cleo LexiCom host
Mailbox	VARCHAR	50	Active Cleo Harmony, Cleo VLTrader, or Cleo LexiCom mailbox
Receive Incoming	BIT		For this trading partner (host\mailbox), indicates whether Cleo Harmony, Cleo VLTrader, or Cleo LexiCom should insert incoming payload into the database (`VLIncoming` table) rather than write to the file system Default: `0` (`False`)

VLSend database table

Used in conjunction with VLOutgoing table to send outgoing payload from the database. See Sending database payload for more information.

Column Name	Data Type	Length	Description
SendID	INTEGER		Unique send ID (sequence identifier)
Host	VARCHAR	50	Host in `VLMailboxes` table to be used for sending
Mailbox	VARCHAR	50	Mailbox in `VLMailboxes` table to be used for sending
InsertedDT	DATETIME		Date/time outgoing payload initially inserted into database
SendingDT	DATETIME		Initially `NULL`. Date/time Cleo Harmony, Cleo VLTrader, or Cleo LexiCom started sending. Set back to NULL when send attempt either succeeds or fails.
PendingDT	DATETIME		If not `NULL`, this is the Date/time to wait for before sending
VLSerial	VARCHAR	6	Initially `NULL`. `VLSerial` of Cleo Harmony, Cleo VLTrader, or Cleo LexiCom sending. Set back to NULL if send attempt fails.
LastAttemptDT	DATETIME		Initially `NULL`. Date/time Cleo Harmony, Cleo VLTrader, or Cleo LexiCom finished last send attempt.
LastFailed Attempt ResultText	VARCHAR	150	Result text from last send attempt that failed.
Retries	INTEGER		Defaults to `0`. Current number of retries.
TotalAttempts	INTEGER		Defaults to `0`. Total number of send attempts.
SentDT	DATETIME		Initially `NULL`. Date/time Cleo Harmony, Cleo VLTrader, or Cleo LexiCom successfully finished sending.
FinalAttemptDT	DATETIME		Initially `NULL`. Date/time Cleo Harmony, Cleo VLTrader, or Cleo LexiCom halted retries (based on `VLOptions.MaximumAttempts`).

VLOutgoing database table

Used in conjunction with VLSend table to send outgoing payload from the database. See Sending database payload for more information.

Column Name	Data Type	Length	Description
SendID	INTEGER		`SendID` in VLSend table
Fileindex	INTEGER		Unique index for each payload to be grouped together in a single message (with same `SendID`)
ExternalID	VARCHAR	50	Optional; if present, logged along with `TransferID` in VersaLex system log file and in VLTransfers table
Payload	BLOB		Outgoing content
Filename	VARCHAR	100	Optional; if present, forwarded to trading partner
ContentType	VARCHAR	100	Optional; can be set to `application/edi-x12`, `application/xml`, and so on. Can include charset= parameter. If not present, content type detected by software
Filesize	INTEGER		Optional. Content size or `-1` if not known. Default: `-1`
TransferID	VARCHAR	30	Initially NULL. `TransferID` in VLTransfers table
VLSerial	VARCHAR	6	Initially NULL. `VLSerial` in VLTransfers table
MessageID	VARCHAR	100	Initially NULL. Protocol-specific message ID

VLOutgoingProperties database table

Optionally used in conjunction with VLSend and VLOutgoing tables to send outgoing payload from the database. See Sending database payload for more information.

Column Name	Data Type	Length	Description
SendID	INTEGER		`SendID` in VLSend table
Fileindex	INTEGER		Unique index of payload or `-1` if property applies to outgoing payload as a whole. Default: `-1`
Name	VARCHAR	50	Payload property (for example, `Content-Disposition`) - or - PUT command parameter or header name, for example, `Subject`. See specific protocol documentation for possible PUT command parameters/headers
Value	VARCHAR	300	Payload property value, or example, `inline`. - or - PUT command parameter or header value

VLIncoming database table

Used to receive incoming payload. See Receiving database payload for more information.

Column Name	Data Type	Length	Description
TransferID	VARCHAR	30	`TransferID` in VLTransfers table
VLSerial	VARCHAR	6	`VLSerial` in VLTransfers table
MessageID	VARCHAR	100	Protocol-specific message ID
Fileindex	INTEGER		Sequential index of each payload grouped together in a single message (with same `MessageID`)
Payload	BLOB		Incoming content
Filename	VARCHAR	255	If present in message
ContentType	VARCHAR	50	If present in message
Filesize	INTEGER		Content size
Host	VARCHAR	50	`Host` in VLMailboxes table that received payload
Mailbox	VARCHAR	50	`Mailbox` in VLMailboxes table that received payload
InsertedDT	DATETIME		Date and time at which Cleo Harmony, Cleo VLTrader, or Cleo LexiCom finished receiving content.
RetrievedDT	DATETIME		Initially NULL. Can be set by end user application to indicate payload has been processed.

VLIncomingProperties database table

Optionally used in conjunction with VLIncoming table to receive incoming payload. See Receiving database payload for more information.

Column Name	Data Type	Length	Description
TransferID	VARCHAR	30	`TransferID` in VLTransfers table
VLSerial	VARCHAR	6	`VLSerial` in VLTransfers table
Name	VARCHAR	50	Additional payload parameter/header name, for example, `Subject`.
Value	VARCHAR	300	Additional payload parameter/header value