sendCsvExport
This procedure exports the contents of a table into a text file and sends it to specified recipients.
Parameters
Parameter | Description |
---|---|
sourceSchema | Source schema in the CData Virtuality Server; mandatory |
sourceTable | Source table in the CData Virtuality Server; mandatory |
Recipients | Comma-separated list of recipients; mandatory |
Subject | Subject of the email |
Body | Body text of the email |
fileName | Name of the file in the email. If omitted, name will be created: sourceSchema_sourceTable.csv |
delimiter | If not specified, comma(, ) is used by default |
quote | If not specified, double quotes(" ) are used by default |
header | If specified, the result contains the header row as the first line; the header line will be present even if there are no rows in a group |
orderBy | An ORDER BY clause to sort the output |
encoding | Encoding for the created file; the default is the system's default encoding |
Usage
SQL
CALL "UTILS.sendCsvExport" (
sourceSchema => 'string_sourceSchema'
,sourceTable => 'string_sourceTable'
,Recipients => 'string_Recipients'
,Subject => 'string_Subject'
,Body => 'clob_Body'
,fileName => 'string_fileName'
,delimiter => 'string_delimiter'
,quote => 'string_quote'
,header => boolean_header
,orderBy => 'string_orderBy'
,encoding => 'string_encoding'
);;
Definition
SQL
CREATE VIRTUAL PROCEDURE sendCsvExport
(
IN sourceSchema string NOT NULL OPTIONS (ANNOTATION 'The source schema in DataVirtuality Server.')
, IN sourceTable string NOT NULL OPTIONS (ANNOTATION 'The source table in DataVirtuality Server.')
, IN Recipients string NOT NULL OPTIONS (ANNOTATION 'Comma separated list of recipients.')
, IN Subject string OPTIONS (ANNOTATION 'Subject of the email')
, IN Body clob OPTIONS (ANNOTATION 'Body text of the email')
, IN fileName string OPTIONS (ANNOTATION 'The name of the file in the email. If omitted, name will be created: sourceSchema_sourceTable.csv.')
, IN delimiter string OPTIONS (ANNOTATION 'When DELIMITER is not specified, by default comma(,) is used as delimiter.')
, IN quote string OPTIONS (ANNOTATION 'Double quotes(") is the default quote character. Use QUOTE to specify a different value. All values will be quoted.')
, IN header boolean OPTIONS (ANNOTATION 'If HEADER is specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group.')
, IN orderBy string OPTIONS (ANNOTATION 'An ORDER BY-clause may be used to sort the output.')
, IN encoding string OPTIONS (ANNOTATION 'Encoding for the created file. Default is the systems default encoding.')
)
OPTIONS (ANNOTATION 'Exports content of a table into a textfile.')
AS
BEGIN
DECLARE string variables.source_schema = LCASE(REPLACE(sourceSchema, '"', ''));
DECLARE string variables.source_table = LCASE(REPLACE(sourceTable, '"', ''));
DECLARE string variables.target_file = fileName;
INSERT into #__LOCAL__csvdata
SELECT result FROM (
CALL csvExport(
sourceSchema => sourceSchema
, sourceTable => sourceTable
, delimiter => delimiter
, quote => quote
, header => header
, orderBy => orderBy
, encoding => encoding
, returnBlob => true
))e;
CALL "UTILS.sendMail"(
"Recipients" => Recipients,
"Subject" => Subject,
"Body" => Body,
"AttachmentNames" => ARRAY(variables.target_file),
"Attachments" => ARRAY((select result from #__LOCAL__csvdata)),
"AttachmentMimeTypes" => ARRAY('text/csv')
);
END;
Example
SQL
CALL "views.sendCsvExport"(
sourceSchema => 'dwh'
,sourceTable => 'Fetch_list_of_notes'
,Recipients => 'user@example.com'
,Subject => 'test send csv'
,Body => 'test body'
,fileName => 'test_send_file.csv'
,delimiter => ','
,quote => '"'
,header => null
,orderBy => null
,encoding => null
);;