Activity - sqlResultsToCSV

Activity Type: Relational

Description

Convert the results of an SQL query or a stored procedure into CSV (Comma Separated Value) format.

Unfortunately, there is currently no such thing as CSV format. Flexible format used here conforms to the following guidelines:

• fields are separated by delimiter which can be any single char except double quote,
• each record is located on a separate line, delimited by a line break which can be set to CR, LF or CRLF
• last record may or may not have an ending line break,
• there may be an optional header line included as a first line with the same format as normal record lines. Header will contain names corresponding to the record fields (column names),
• within the header and each record there must be the same number of fields,
• fields with embedded delimiters must be enclosed in double quotes,
• fields that contain double quotes must be enclosed in double quotes and the embedded double quotes must each be escaped by preceding it with another double quote,
• fields with embedded line breaks must be surrounded by double quotes,
• fields may always be enclosed in double quotes.

• When results of this activity are included in a XML document (for example in a SOAP message) XML parsers will normalise all line breaks to CR (\n). This also affects any line breaks that were in the data extracted from the database. To ensure correct parsing on the client side set lineBreak to CR (default) when using XML based delivery mechanisms.

Example

<sqlResultsToCSV name="results">
 <resultSet from="sqlStatement" />
 <csvOutput name="CSV" />
 <delimiter value="," />
 <lineBreak value="LFCR" />
 <nullDataStr value="NULL" />
 <includeHeader value="true" />
 <escapeFields value="true" />
</sqlResultsToCSV>

Example output

"id","name","address","phone"
1,"Ally Antonioletti","826 Hume Crescent, Southampton","01670061244"
2,"Amy Atkinson","583 Atkinson Drive, Southampton","06312054624"

Specification

Element sqlResultsToCSV:

• Attribute name - unique name for the activity within the scope of a request.
• Element resultSet (required) - input stream.
  • Attribute from - name of the output from another activity which provides the SQL results.
• Element csvOutput (required) - output stream.
  • Attribute name - name of the activity's output stream.
• Element delimiter (zero or one) - delimiter.
  • Attribute value - character to be used as delimiter.
    • Any single character can be used except double quote ( " ).
    • If not provided then the delimiter defaults to comma ( , ).
• Element lineBreak (zero or one) - line break sequence.
  • Attribute value - line break sequence used to delimit records in CSV file.
    • Three values are possible:
      • CR for \n (UNIX)
      • LF for \r (Mac)
      • LFCR for \r\n (Windows)
    • If not provided then lineBreak defaults to CR.
• Element nullDataStr (zero or one) - name used to represent null value fields.
  • Attribute value - string to represent null fields.
    • Any string, including empty string that does not include double quote characters can be used.
    • When CSV is going to be converted to ResultSet it is advised to use non empty strings to avoid possible ambiguities in situation where database contains fields that are empty strings.
    • If not provided then the nullDataStr defaults to NULL.
• Element includeHeader (zero or one) - indicates if first line of the CSV file should include column names.
  • Attribute value - (true/ false).
    • Set to true to include header false otherwise.
    • If not provided then the includeHeader defaults to true.
• Element escapeFields (zero or one) - indicates if textual fields should be surrounded by double quotes.
  • Attribute value - (true/ false).
    • Setting to false may speed up conversion, but should be used with care - only when user is sure that the database stores data that will not require special treatment (for example only numerical data). If unsure set to true.
    • If not provided then the includeHeader defaults to true.

Input and Output Types

• Input: resultSet - java.sql.ResultSet
• Output: csvOutput - java.lang.String in a CSV format

Data Resource Accessor

This activity can operate with any data resource accessor.

XML Schema

OGSA-DAI/schema/ogsadai/xsd/activities/sql_results_to_csv.xsd

Class

uk.org.ogsadai.activity.sql.SQLResultsToCSVActivity

Client Toolkit Class

uk.org.ogsadai.client.toolkit.activity.sql.CSV