write_sql
The write_sql
function writes document metadata and content to disk in the form of a SQL “insert” statement. You can use the SQL statement to insert data from the document into a database.
The named parameter fieldNames
specifies the document fields that you want to insert into the database.
- You do not need to specify the
DREREFERENCE
orDRECONTENT
fields. Specify the columns to contain these values using thedreReferenceColumnName
anddreContentColumnName
named parameters. - To specify a field, type the field name. For example:
"MyField"
. - To specify a sub-field, type the field name, followed by a forward slash, followed by the name of the sub-field. For example:
"MyField/SubField"
. - To specify a field attribute, type the field name, followed by a forward slash, followed by the name of the attribute. Prefix the attribute name with the '
@
' character. For example"MyField/@attribute"
. - If you specify a field, subfield, or attribute that has multiple values, only the first value is included in the output.
Syntax
write_sql( doc , params )
Arguments
Argument | Description |
---|---|
doc
|
(LuaDocument) The document to write to the file. |
params
|
(table) Additional named parameters that configure the output. The table maps parameter names (String) to parameter values. For information about the parameters that you can set, see the following table. For information about how to use named parameters refer to the Connector Framework Server Administration Guide. |
Named Parameters
Named Parameter | Description | Configuration Parameter |
---|---|---|
section
|
(string) The name of a section in the CFS configuration file. If you set this then any parameters not set in the parameters table are read from this section of the configuration file. | |
outputFilename
|
(string) The name for the output SQL file. | SqlWriterFilename |
tableName
|
(string) The table name to use in the INSERT statement. For example, if you specify MyTable, the statement begins with 'INSERT into MyTable...' | SqlWriterTableName |
dreReferenceColumnName
|
(string) The name of the table column to contain the document’s reference (DREREFERENCE ). |
SqlWriterDreReferenceColumnName |
dreContentColumnName
|
(string) The name of the table column to contain the document's content (DRECONTENT ). |
SqlWriterDreContentColumnName |
fieldNames
|
(table of strings) The names of the document fields that you want to write to the database table. | SqlWriterFieldNamesN |
columnNames
|
(table of strings) The names of the columns to contain the data from the document fields specified by fieldNames . |
SqlWriterColumnNamesN |
dataTypes
|
(table of strings) The data types for the corresponding columns specified by
|
SqlWriterDataTypesN |
useNullForMissingFields
|
(Boolean) Specifies whether to insert the value null when a field specified by fieldNames does not exist in the document. If you set this parameter to false , and a field specified by fieldNames is not present in the document, the task fails. |
SqlWriterUseNullForMissingFields |
dateFormats
|
(table of strings) The date formats that are used in your document. When you write a date value from a document field to a table column, the SqlWriter attempts to match the date against these formats. You can specify any of the standard IDOL date formats. |
SqlWriterDateFormatsN |
archiveDirectory
|
(string) The path to the directory where output files are archived when they exceed the size specified by maxSizeKbs . |
SqlWriterArchiveDirectory |
maxSizeKbs
|
(integer) The maximum size of output files, in kilobytes. When the file specified by outputFilename exceeds this size it is moved to the archive directory specified by archiveDirectory . A timestamp is added to the file name so that it has a unique name. |
SqlWriterMaxSizeKBs |
Returns
Boolean. Returns true
if the file was created successfully, and false
otherwise.
Examples
The following example reads configuration settings for the task from the [SQLWriter0]
section of the CFS configuration file:
function handler(document) write_sql(document, {section="SQLWriter0"} ) return true end
The following example shows how to override some of the task settings in the configuration:
function handler(document) write_sql(document, {section="SQLWriter0" , outputFilename="output.sql", dreContentColumnName="content", fieldNames={"A_STRING","A_NUMBER","A_DATE"}, columnNames={"String", "Number", "Date"}, dataTypes={"CHAR", "INT", "DATE_TIME" }, dateFormats={"DD/MM/YYYY", "YYYY/MM/DD"} maxSizeKbs=1024 } ) return true end