wiki:MapGuideRfc80

Version 7 (modified by leaf, 9 years ago) (diff)

--

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 DateAug 06, 2009
Last ModifiedLeaf Li Timestamp?
AuthorLeaf Li
RFC Statusready for review
Implementation Statuspending
Proposed Milestone2.2
Assigned PSC guide(s)Tom Fukushima
Voting History(vote date)
+1
+0
-0
-1
no vote

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 add two methods into 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 = 1;

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

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

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

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 with null parameter value.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    ///
    MgParameter (CREFSTRING name);
 
   /// \brief
    /// Constructs an MgParameter object with a boolean vlaue.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value (boolean/bool)
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, bool value);

    /// \brief
    /// Constructs an MgParameter object with a byte vlaue.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value (signed char/unsigned char/string)
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, BYTE value);

    /// \brief
    /// Constructs an MgParameter object with an MgDateTime instance.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value (MgDateTime)
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, MgDateTime* value);

    /// \brief
    /// Constructs an MgParameter object with a float value.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value (float)
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, float value);

    /// \brief
    /// Constructs an MgParameter object with a double value.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value (double)
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, double value);

    /// \brief
    /// Constructs an MgParameter object with a 16 bits integer.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value (short/int)
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, INT16 value);

    /// \brief
    /// Constructs an MgParameter object with a 32 bits integer.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value (int)
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, INT32 value);

    /// \brief
    /// Constructs an MgParameter object with a 64 bits integer.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value (long/string)
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, INT64 value);

    /// \brief
    /// Constructs an MgParameter object with a string.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value (String/string)
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, CREFSTRING value);

    /// \brief
    /// Constructs an MgParameter object with an MgGeometry instance.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value
    /// The value of the parameter
    ///
    MgParameter (CREFSTRING name, MgGeometry* value);

    /// \brief
    /// Constructs an MgParameter object with an MgByteReader instance.
    ///
    /// \param name (String/string)
    /// The name of the parameter
    /// \param value
    /// The value of the parameter
    /// \param valueType
    /// The type of parameter value. It can be one of MgPropertyType::Blob, 
    /// MgPropertyType::Clob or MgPropertyType::Geometry.
    /// 
    MgParameter (CREFSTRING name, MgByteReader* value, INT32 valueType);

    /// \brief
    /// Gets the parameter name.
    ///
    /// \return
    /// Returns the parameter name.
    ///
    STRING GetName();  /// __get, __set
    ///////////////////////////////////////////////////
    /// \brief
    /// Gets the parameter type.
    ///
    /// \return
    /// Returns the parameter type. See MgPropertyType.
    ///
    INT32 GetType() = 0;  /// __get

    /// \brief
    /// Set the parameter value to null.
    ///
    /// \return
    /// Returns nothing.
    ///
    void SetNull();

    /// \brief
    /// Test whether the parameter value is null.
    ///
    /// \return
    /// True if the parameter value is null, false Otherwise.
    ///
    bool IsNull();

    /// \brief
    /// Gets the boolean value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be
    /// of type MgPropertyType::Boolean or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the boolean value.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    bool GetBoolean();

    /// \brief
    /// Gets the byte value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be
    /// of type MgPropertyType::Byte or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the byte value.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    BYTE GetByte() = 0;

    /// \brief
    /// Gets the MgDateTime value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be
    /// of type MgPropertyType::DateTime or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns an MgDateTime object.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    MgDateTime* GetDateTime();

    /// \brief
    /// Gets the float value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be a
    /// of type MgPropertyType::Single or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the float value.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    float GetSingle() = 0;

    /// \brief
    /// Gets the double value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be a
    /// of type MgPropertyType::Double or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the double value.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    double GetDouble() = 0;

    /// \brief
    /// Gets the 16 bits integer value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore the parameter must be a
    /// of type MgPropertyType::Int16 or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the 16 bits integer value.
    ///
    /// \exception MgInvalidPropertyTypeException
    ///
    INT16 GetInt16();

    /// \brief
    /// Gets the 32 bits integer value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be a
    /// of type MgPropertyType::Int32 or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the 32 bits integer value.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    INT32 GetInt32();

    /// \brief
    /// Gets the 64 bits integer value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be a
    /// of type MgPropertyType::Int64 or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the 64 bits integer value.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    INT64 GetInt64();

    /// \brief
    /// Gets the string value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be a
    /// of type MgPropertyType::String or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the string value.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    STRING GetString();

    /// \brief
    /// Gets the geometry value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be a
    /// of type MgPropertyType::Geometry or an
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the geometry value.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    MgGeometry* GetGeometry();

    /// \brief
    /// Gets the byte reader value of the parameter.
    ///
    /// \remarks
    /// No conversion is performed. Therefore, the parameter must be a
    /// of type MgPropertyType::Blob, MgPropertyType::Clob or 
    /// MgPropertyType::Geometry. Otherwise, an 
    /// MgInvalidPropertyTypeException is thrown.
    ///
    /// \return
    /// Returns the byte reader value.
    ///
    /// \exception MgInvalidPropertyTypeException.
    ///
    MgByteReader* GetByteReader();

    /// \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;
};

Parameter Collection

Feature Service will be extended to add two methods to support parameter binding when executing an SQL statement against a feature source.

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.