X-SQLT OLE Automation Interface

When using some scripting language for transformation, any X-SQLT processor should expose some OLE Automation interface, that will allow developers to access currently processed script, its source, destination and other elements.

The following specification describes an OLE interface implemented in X-SQLT Command-line Processor.

XSQLT Interface Syntax

    
  XSQLT
    function ParamCount: Integer 
    function ParamValues(Name: WideString): OleVariant
    function ParamNames(Index: Integer): WideString
    
    Source
      function GetStream: IStream readonly
      Encoding: WideString readonly
      Kind: WideString readonly
      Name: WideString readonly
      Text: WideString readonly
      Value: OleVariant readonly
      
    Destination
      function GetStream: IStream read/write
      Kind: WideString readonly
      Name: WideString readonly
      Text: WideString read/write
      Value: OleVariant read/write
      
    Transform
      Encoding: WideString readonly
      Delimiter: WideString readonly
      Quote: WideString readonly
      HeaderRow: Boolean readonly
      RecordTag: WideString readonly 
      Namespaces: WideString readonly
      SelNamespaces: WideString readonly 
      IncludeParams: Boolean readonly
      FlatText: Boolean readonly
      Indent: Boolean readonly

Example

    
  <?xml version="1.0" ?>
  <xsqlt>
    <script>
     <source kind="file">input.txt</source>
      <transform kind="VBScript">                 
        <![CDATA[
          sData = XSQLT.Source.Text
          XSQLT.Destination.Text = LCase(sData)
        ]]>        
      </transform>
     <destination kind="file">output.txt</destination>      
    </script>
  </xsqlt>

Description

As you can see, XSQLT interface follows the structure of X-SQLT script element by providing the Source, Transform and Destination objects, that correspond to the subordinate elements of the script. Also individual properties of these objects have got the same names as attributes declared in appropriate elements.

Xsql object is missing in XSQLT interface, because when xsql element is used, the transformation is applied on SQL processing results (XML-based stream), so in this context the Xsql object is not needed and the Source object represents results of SQL processing (not the database source options).

There are several scenarios how to use this interface. It depends mainly on what kind of source and destination is used in the current script. If you are handling with files or databases, than you can completely ommit the Text and Value properties of XSQLT interface, and use the scripting language features to handle the files or databases (ie. Scripting.FileSystemObject, ADODB, etc), which pathes (resp. connection strings) are available through XSQLT.Source.Name and XSQLT.Destination.Name properties. But if there is a named stream specified as a source or destination, you should use Text or Value properties (or getStream function) to read from that stream or to write to that stream.

Objects and properties

XSQLT: Represents the currently processed script. Allows access to Source, Transform and Destination elements, as well as access to project global parameters.
XSQLT.ParamCount: Returns a number of global parameters of current X-SQLT project.
XSQLT.ParamValues: Returns the value of a given global parameter.
XSQLT.ParamNames: Returns the name of parameter with a given index.
XSQLT.Source: Represents the source element of currently processed script, namely its options and data.
XSQLT.Source.Kind: Returns the kind of source element (as a string). This can be a "file", "stream", "oledb", or any other kind that is supported by the source element.
XSQLT.Source.Name: Returns the filename, stream identifier or database connection string of the source.
XSQLT.Source.Encoding: Returns an encoding that is defined for source file or stream.
XSQLT.Source.Text: Returns the content of source file (or stream) as a string. For database-like sources the return value is always an empty string.
XSQLT.Source.Value: Returns the content of source file (or stream) as OleVariant (more precisely, as a stream stored into OleVariant). For database-like sources the return value is always "null".
XSQLT.Source.getStream: Instantiates an IStream interface for source file or stream. For database-like sources the return value is always "nil" (or null, or undefined, depending on used scripting language).
XSQLT.Destination: Represents the destination element of currently processed script, namely its options and data.
XSQLT.Destination.Kind: Returns the kind of destination element (as a string). This can be a "file", "stream", "oledb", or any other kind that is supported by the destination element.
XSQLT.Destination.Name: Returns the filename, stream identifier or database connection string of the destination.
XSQLT.Destination.Text: Reads or writes the content of destination file (or stream) as a string. For database-like destinations the return value is always an empty string, and writing to that property is ignored.
XSQLT.Destination.Value: Reads or writes the content of destination file (or stream) as OleVariant (more precisely, as a stream packed into OleVariant). For database-like destinations the return value is always "null", and writing to that property is ignored.
XSQLT.Destination.getStream: Instantiates an IStream interface for destination file or stream. For database-like destinations the return value is always "nil" (or null, or undefined, depending on used scripting language).
XSQLT.Transform: Represents the transform element of currently processed script. For description of individual properties, please refer to the transform element help topic.

See also: