.NET Data Provider for Teradata: Creating Function Where the Source Resides On the Client Machine

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

The Teradata Database supports source files that can reside on the client or server machine that contains Data Definition Language (DDL) to create a function (e.g. stored procedure, user defined function, or user defined type).  The DDL syntax specifies where the file resides and the path to the file.  An example of a DDL to create a stored procedure from a file that resides on the Teradata Database Server is as follows:

CREATE PROCEDURE provider_sp(INOUT region VARCHAR(64)) 
LANGUAGE C NO SQL EXTERNAL NAME 'SS!provider_sp!/usr/teradata/xsp.c!F!provider_sp'
PARAMETER STYLE SQL

The .Net Data Provider for Teradata 14.10 and earlier releases only supported source files that resided on the server.  An exception is thrown if the DDL specified that the source file resided on the client machine.

This changes with the 14.11 release of the Teradata Provider.  Beginning with this release the Teradata Provider can support  DDL statements that specify the source file resides on the client machine.  A DDL statement that specifies this is similar to the following:

CREATE PROCEDURE provider_sp(INOUT region VARCHAR(64)) 
LANGUAGE C NO SQL EXTERNAL NAME 'CS!provider_sp!c:\xsp.c!F!provider_sp'
PARAMETER STYLE SQL

In order to support this feature several new types have been added to the Teradata Provider:

TdOpenFileEventHandler

Delegate that is used to register to the TdConnection.OpenFile event.

TdCloseFileEventHandler

Delegate that is used to register to the TdConnection.CloseFile event.

TdConnection.OpenFile

Event that is raised when the Teradata Database requests the source of the function.   A delegate must be registered with this event if a DDL is executed that specifies that the source of the function resides on the client's machine.  Only one delegate can be registered with this event.

TdConnection.CloseFile

Event that is raised when the contents of the source file has been sent to Teradata.  Registration of this event is optional.  Only one delegate can be registered with this event.

 TdFileEventArgs

Class that contains information about the source file.   The TdOpenFileEventHandler  and TdCloseFileEventHandler delegates requires a parameter declared as this type.

When the DDL is executed, the Teradata Provider will send the source code of the function to the Teradata Database by performing the following tasks:

  1. Raise the TdConnection.OpenFile event. 

    The TdFileEventArgs parameter of the delegate that is invoked by this event will contain the name of the file that was specified in the DDL statement.  It is the responsibility of the application to retrieve the name of the file from the TdFileEventArgs.FileName property and open a System.IO.Stream to the file.  The application must also set the TdFileEventArgs.SourceFile property to the stream.

  2. Retrieve the Stream object from the TdFileEventArgs.SourceFile property. 

    The Teradata Provider will read the source from the Stream and send it to the Teradata Database.

  3. Raise the TdConnection.CloseFile event after all the source has been read and sent to Teradata.

    It is the application's responsibility to perform any cleanup tasks (e.g. closing the Stream) in the delegate that was registered with this event.

The following coding example shows how to use the new types when executing a DDL to create a function and specifies that the source file resides on the client machine:

/// <summary>        
/// Class that contains the File Handlers that are invoked when the Teradata Provider
/// requests the source file of the function and completes processing the source code.
/// </summary>
public class ExternalFileHander
{
    /// <summary>
    /// Delegate that is invoked when the TdConnection.OpenFile
    /// event gets raised by the Teradata Provider
    ///</summary>
    public void OnFile(Object sender, TdFileEventArgs eventArgs)
    {
        // retrieving the file name from the TdFileEventArgs parameter
        String FileName = eventArgs.FileName;
      
         // creating stream and sending stream back to Teradata Provider
         eventArgs.ExternalFileObject =  new FileStream(FileName, FileMode.Open);
     }
       
    /// <summary>
    /// Delegate that is invoked when the TdConnection.CloseFile
    /// event gets invoked by the Teradata Provider.
    /// </summary>
    public void OnCloseFile(Object sender, TdFileEventArgs eventArgs)
    {
        Stream sourceStream = eventArgs.ExternalFileObject;

        sourceStream.Close();
    }
}

/// <summary>
/// Method that executes a DDL to create a stored procedure.  It demonstrates how to
/// use the new objects to create a stored procedure where the source file resides on the
/// client machine.
/// </summary>
public void CreateExternalProcedure(TdConnection cn)
{
    TdCommand cmd = cn.CreateCommand();
 
    // Creating the handler that contains the delegates that will be used by the
    // ExternalFile and ExternalFileReadCompleted events
    ExternalFileHandler fh = new ExternalFileHandler();
          
    cmd.CommandText = "CREATE PROCEDURE provider_sp(INOUT region VARCHAR(64)) " +
        "LANGUAGE C NO SQL EXTERNAL NAME 'CS!provider_sp!c:\xsp.c!F!provider_sp' " +
        "PARAMETER STYLE SQL";
           
    // creating delegates for the OpenFile and CloseFile events
    TdOpenFileEventHandler extFile = new TdOpenFileEventHandler(fh.OnFile);

    TdCloseFileEventHandler extReadCompleted =
        new TdCloseFileEventHandler(fh.OnCloseFile);
       
    // this event will get raised when Teradata requests the source code of the
    // stored procedure.  Registration to this event is required if a DDL is executed
    // that specifies data is to be read from a source file that resides on the client
    // machine.
    cn.OpenFile += extFile;
             
    // this event will get raised after Teradata has received all the source code
    // for the stored procedure.  Registering with this event is optional.
    cn.CloseFile += extReadCompleted;
            
    try
    {
         // going to read through the compiler messages sent from Teradata
        using (TdDataReader dr = cmd.ExecuteReader())
        {
            String result;

            while (dr.Read() == true)
            {
                result = dr.GetString(0);
                    
                Console.WriteLine(result);
            }
        }
    }
    finally
    {
        // need to unregister from the events.
        cnOpenFile -= extFile;
        cn.CloseFile -= extReadCompleted;
    }
}