MapGuide RFC 80 - Parameter Binding

This page contains an change request (RFC) for the MapGuide Open Source project. More MapGuide RFCs can be found on the RFCs page.

Status

RFC Template Version	(1.0)
Submission Date	Aug 06, 2009
Last Modified	Leaf Li Timestamp
Author	Leaf Li
RFC Status	adopted
Implementation Status	implemented
Proposed Milestone	2.2
Assigned PSC guide(s)	Tom Fukushima
Voting History	August 20, 2009
+1	Trevor, Kenneth, Andy, Bruce, Tom
+0	Paul, Jason
-0
-1
no vote	Bob, Harris

Overview

This proposal is to extend MapGuide Feature Service to support parameter binding when executing an SQL statement against a feature source.

Motivation

Currently FDO interface supports parameter binding when executing an FdoISQLCommand. Method GetParameterValues() returns a parameters value collection and users can set value for each named parameter. However, MapGuide Feature Service doesn’t provide this capability. This RFC will extend Feature Service API to eliminate this limitation.

Proposed Solution

Parameter Direction

The MgParameterDirection defines integer constants used to signify the direction in which command will utilize the parameter upon execution. Parameter direction values are typically Input, Output, InputOutput and Return, with the default value is typically Input.

/// \brief
/// The MgParameterDirection defines integer constants used to signify the 
/// direction in which a Parameter value will be used within the context of a 
/// FDO Command.
class MgParameterDirection
{
PUBLISHED_API:
    /// \brief
    /// Specifies that the parameter is an input parameter.
    ///
    static const INT32 Input = 0;

    /// \brief
    /// Specifies that the parameter is an output parameter.
    ///
    static const INT32 Output = 1;

    /// \brief
    /// Specifies that the parameter is an input & output parameter.
    ///
    static const INT32 InputOutput = 2;

    /// \brief
    /// Specifies that the parameter is a return parameter.
    ///
    static const INT32 Return = 3;
};

Parameter Value

The MgParameter class specifies a value for a particular parameter. Instances of this class are used to specify a value to bind to a parameter when a command is executed. Typically, it is used to bind parameters to the SQL command.

/// \brief
/// The MgParameter class specifies a value for a particular parameter.
/// Instances of this class are used to specify a value to bind to a parameter
/// when a command is executed. Typically, it is used to bind parameters to
/// the SQL command.
class MgParameter : public MgNamedSerializable
{
PUBLISHED_API:
    ///////////////////////////////////////////////////////////////////
    /// \brief
    /// Constructs an MgParameter object. The default parameter direction
    /// is input.
    ///
    /// \param name (MgNullableProperty)
    /// An MgNullableProperty instance which contains 
    /// the name and value of the parameter.
    ///
    MgParameter(MgNullableProperty* prop);

    /// \brief
    /// Constructs an MgParameter object.
    ///
    /// \param name (MgNullableProperty)
    /// An MgNullableProperty instance which contains 
    /// the name and value of the parameter
    /// \param value (int)
    /// The function direction of the parameter value
    ///
    MgParameter(MgNullableProperty* prop, INT32 direction);

    /// \brief
    /// Sets the name and value of the parameter.
    /// 
    /// \param value 
    /// Input an MgNullableProperty instance which contains 
    /// the name and value of the parameter.
    /// 
    /// \return
    /// Returns nothing
    /// 
    void SetProperty(MgNullableProperty* prop);

    /// \brief
    /// Gets the name and value of the parameter.
    /// 
    /// \return
    /// Returns an MgNullableProperty instance which contains 
    /// the name and value of the parameter.
    /// 
    MgNullableProperty* GetProperty();

    /// \brief
    /// Sets the function direction of the parameter value, which
    /// is defined in MgParameterDirection.
    /// 
    /// \param value 
    /// Input the direction value
    /// 
    /// \return
    /// Returns nothing
    /// 
    void SetDirection(INT32 value);

    /// \brief
    /// Gets the function direction value of the the command parameter, which
    /// is defined in MgParameterDirection.
    /// 
    /// \return
    /// Returns the direction value defined in MgParameterDirection.
    /// 
    INT32 GetDirection();
};

Parameter Collection

MgParameterCollection represents a collection of MgParameter.

/// \brief
/// MgParameterCollection represents a collection of parameters.
/// All parameters contained in an instance of a parameter
/// collection are objects whose base class is MgParameter.
/// A call to a parameter object's GetType() method returns one
/// of the integer constants defined in class MgPropertyType. 
/// This collection does not allow duplicate key names. 
class MgParameterCollection : public MgCollection
{
PUBLISHED_API:
    /// \brief
    /// Constructs an MgParameterCollection. The collection is initially empty.
    ///
    MgParameterCollection();

    /// \brief
    /// Sets the item in the collection at the specified index to 
    /// the specified value.
    ///
    /// \param index (int)
    /// Input index
    /// \param value (MgParameter)
    /// Input value
    ///
    /// \return
    /// Returns nothing.
    ///
    /// \exception MgIndexOutOfRangeException if the index is out of range.
    /// \exception MgDuplicateObjectException if it is a duplicate.
    ///
    virtual void SetItem(INT32 index, MgParameter* value);

    /// \brief
    /// Adds the specified item to the end of the collection.
    ///
    /// \param value (MgParameter)
    /// Input value
    ///
    /// \return
    /// Returns nothing.
    ///
    /// \exception MgDuplicateObjectException if the index is a duplicate.
    ///
    virtual void Add(MgParameter* value);

    /// \brief
    /// Inserts the specified item at the specified index within the collection.
    /// Items following the insertion point are moved down to accommodate
    /// the new item.
    ///
    /// \param index (int)
    /// Input index
    /// \param value (MgParameter)
    /// Input value
    ///
    /// \return
    /// Returns nothing.
    ///
    /// \exception MgIndexOutOfRangeException if the index is out of range.
    /// \exception MgDuplicateObjectException if it is a duplicate.
    ///
    virtual void Insert(INT32 index, MgParameter* value);

    /// \brief
    /// Removes the specified item from the collection.
    ///
    /// \param value (MgParameter)
    /// Input value
    ///
    /// \return
    /// Returns true if removal was successful.
    ///
    virtual bool Remove(MgParameter* value);

    /// \brief
    /// Gets the item in the collection at the specified index.
    ///
    /// \param index (int)
    /// Input index
    ///
    /// \return
    /// Returns the item in the collection at the specified index.
    ///
    /// \exception MgIndexOutOfRangeException if the index is out of range.
    ///
    virtual MgParameter* GetItem(INT32 index) const;

    /// \brief
    /// Gets the item in the collection with the specified name.
    ///
    /// \param name (String/string)
    /// Input item name
    ///
    /// \return
    /// Returns the item in the collection with the specified name.
    ///
    /// \exception MgObjectNotFoundException if the item does not exist 
    /// within the collection.
    ///
    virtual MgParameter* GetItem(CREFSTRING name);

    /// \brief
    /// Gets the number of items in the collection.
    ///
    /// \return
    /// Returns the number of items in the collection.
    ///
    virtual INT32 GetCount() const;

    /// \brief
    /// Removes all items from the collection.
    ///
    /// \return
    /// Returns nothing.
    ///
    virtual void Clear();

    /// \brief
    /// Removes the specified item from the collection.
    ///
    /// \param index (int)
    /// Input index
    ///
    /// \return
    /// Returns nothing.
    ///
    /// \exception MgIndexOutOfRangeException
    ///
    virtual void RemoveAt(INT32 index);

    /// \brief
    /// Returns true if the collection contains the specified item, 
    /// false otherwise.
    ///
    /// \param value (MgParameter)
    /// Input value
    ///
    /// \return
    /// Returns true if the collection contains the specified item, 
    /// false otherwise.
    ///
    virtual bool Contains(const MgParameter* value);

    /// \brief
    /// Returns true if the collection contains the specified item, 
    /// false otherwise.
    ///
    /// \param name (String/string)
    /// Input the item name
    ///
    /// \return
    /// Returns true if the collection contains the specified item, 
    /// false otherwise.
    ///
    virtual bool Contains(CREFSTRING name);

    /// \brief
    /// Returns the index of the specified item in the collection or -1 
    /// if the item does not exist.
    ///
    /// \param value (MgParameter)
    /// Input value
    ///
    /// \return
    /// Returns the index of the specified item in the collection or -1 
    /// if the item does not exist.
    ///
    virtual INT32 IndexOf(const MgParameter* value) const;

    /// \brief
    /// Returns the index of the specified item (by name) in the collection 
    /// or -1 if the item does not exist.
    ///
    /// \param name (String/string)
    /// Input the item name
    ///
    /// \return
    /// Returns the index of the specified item in the collection or -1
    /// if the item does not exist.
    ///
    virtual INT32 IndexOf(CREFSTRING name) const;
};

Changes to Feature Service

In MapGuide RFC 78, two overloaded methods ExecuteSqlQuery(...) and ExecuteSqlNonQuery(...) are introduced to add support to transaction. In this RFC, those two methods will be extended to add a parameter MgParameterCollection to support parameter binding.

class MgFeatureService : public MgService 
{
PUBLISHED_API:
    //////////////////////////////////////////////////////////////
    /// \brief
    /// Executes the SQL SELECT statement with the specified parameters
    /// on the specified feature source.
    ///
    /// \param resource (MgResourceIdentifier)
    /// A resource identifier referring to a feature source.
    /// \param sqlStatement (String/string)
    /// The SQL SELECT statement.
    /// \param params (MgParameterCollection)
    /// Parameters binded to the SQL statement.
    /// \param params (MgTransaction)
    /// Transaction to exectue the SQL statement.
    ///
    /// \return
    /// Returns an MgSqlDataReader instance (or NULL).
    ///
    virtual MgSqlDataReader* ExecuteSqlQuery( MgResourceIdentifier* resource,
                                              CREFSTRING sqlStatement,                                              
                                              MgParameterCollection* params,
                                              MgTransaction* transaction ) = 0;

    ///////////////////////////////////////////////////////
    /// \brief
    /// Executes SQL statements NOT including SELECT statements
    /// with the specified parameters.
    ///
    /// \param resource (MgResourceIdentifier)
    /// A resource identifier for a feature source.
    /// \param sqlNonSelectStatement (String/string)
    /// The SQL statement that is NOT a SELECT statement.
    /// \param params (MgParameterCollection)
    /// Parameters binded to the SQL statement.
    /// \param params (MgTransaction)
    /// Transaction to exectue the SQL statement.
    ///
    /// \return
    /// Returns a positive integer value indicating how many
    /// instances (rows) have been affected.
    ///
    virtual INT32 ExecuteSqlNonQuery( MgResourceIdentifier* resource,
                                      CREFSTRING sqlNonSelectStatement,
                                      MgParameterCollection* params,
                                      MgTransaction* transaction ) = 0;
};

Implications

This RFC depends on FDO RFC 33 (​http://trac.osgeo.org/fdo/wiki/FDORfc33), which improves parameter binding to support specifying parameter direction. If FDO RFC 33 can’t get approved, we will remove class MgParameterDirection and method MgParameter::SetDirection(...) and MgParameter::GetDirection() in this RFC.

Test Plan

This is new API only. There will be no effect on existing applications. However, API documentation need to be updated. Moreover, we will add unit tests for those two newly added methods into the existing Feature Service unit tests.

Funding/Resources

Supplied by Autodesk.