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.


write_sql( doc , params )


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

(table of strings) The data types for the corresponding columns specified by columnNames. You can specify the following types:

  • INT
  • CHAR
  • RAW
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


Boolean. Returns true if the file was created successfully, and false otherwise.


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

The following example shows how to override some of the task settings in the configuration:

function handler(document)
      {section="SQLWriter0" ,
       columnNames={"String", "Number", "Date"},
       dataTypes={"CHAR", "INT", "DATE_TIME" },
       dateFormats={"DD/MM/YYYY", "YYYY/MM/DD"}
       maxSizeKbs=1024 } )
   return true