Connector Framework Server 24.1

# 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` or `DRECONTENT` fields. Specify the columns to contain these values using the `dreReferenceColumnName` and `dreContentColumnName` 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 `columnNames`. You can specify the following types: SHORT INT UNSIGNED_INT BIG_INT FLOAT DOUBLE CHAR DATE_TIME RAW | 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

Copyright 2023 Open Text
Version 24.1 | Last updated December 2023

Send documentation feedback

Close

Send documentation feedback

To send feedback using your default email client, open an email window.

Otherwise, send your feedback and the information below to MFI-swpdl.idoldocsfeedback@opentext.com.

We appreciate your feedback!

Product: IDOL Connector Framework Server 24.1

Topic Title: write_sql