Parameter Name Substitution for ODBC data sources

Note: The below information is outdated, it applies only to OpenOffice.org versions prior to 2.0.

The Problem

When working with parametrized statements, OpenOffice.org usually uses named parameters such as in "SELECT * FROM <table> WHERE <field> = :param" Here ":param" is a so-called named parameter. However, some databases do not allow such named parameters, but only unnamed ones. The statement then would be "SELECT * FROM <table> WHERE <field> = ?". Such databases usually reject statements with named parameters.

The Solution

The OpenOffice.org ODBC-SDBC bridge (more sloppy: OOo's ODBC driver) features substituting named parameters with unnamed ones before sending statements to the system driver.

Parameter name substitution can be enabled on a per-data-source basis. For this, the "Info" property of a data source should contain a name-value-pair with
Name: ParameterNameSubstituion
Value: TRUE

Unfortunately, there is no user interface, yet, for doing so. There is an issuezilla bug requesting this UI, but as long as it's not fixed, you could use the Basic macro provided below, which adds the setting for a data source of your choice.

Note that in OpenOffice.org 1.0.x, this feature is available for ODBC drivers only. In later versions (notably in the upcoming 1.1, but also in the intermediate developer snapshots), it has been implemented for JDBC drivers, too.

The Macro

The following macro enables parameter name substitution for a data source of your choice. You can also download this macro in the downloads section.

REM ***** BASIC *****

Option Explicit

Sub Main
   Dim sDataSourceName as String
   sDataSourceName = InputBox( "Please enter the name of the data source:" )
    EnableParameterNameSubstitution(sDataSourceName )
End Sub

Sub EnableParameterNameSubstitution( sDataSourceName as String )
    ' the data source context (ehm - the service name is historical :)
    Dim aContext as Object
    aContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )

    If ( Not aContext.hasByName( sDataSourceName ) ) Then
        MsgBox "There is no data source named " + sDataSourceName + "!"
        Exit Sub
    End If

    ' the data source
    Dim aDataSource as Object
    aDataSource = aContext.getByName( sDataSourceName )

    ' append the new ParameterNameSubstitution flag
    Dim bFlag as Boolean
    bFlag = TRUE
    Dim aInfo as Variant
    aInfo = aDataSource.Info
    aInfo = AddInfo( aInfo, "ParameterNameSubstitution", bFlag )

    ' and write back
    aDataSource.Info = aInfo
    ' flush (not really necessary, but to be on the safe side :)
    aDataSource.flush
End Sub

Function AddInfo( aOldInfo() as new com.sun.star.beans.PropertyValue,sSettingsName as String, aSettingsValue as Variant ) as Variant
    Dim nLower as Integer
    Dim nUpper as Integer
    nLower = LBound( aOldInfo() )
    nUpper = UBound( aOldInfo() )

    ' look if the setting is already present
    Dim bNeedAdd as Boolean
    bNeedAdd = TRUE

    Dim i As Integer
    For i = nLower To nUpper
        If ( aOldInfo( i ).Name = sSettingsName ) Then
            aOldInfo( i ).Value = aSettingsValue
            bNeedAdd = FALSE
        End If
    Next i

    ' allocate the new array
    Dim nNewSize as Integer
    nNewSize = ( nUpper - nLower )
    If bNeedAdd Then nNewSize = nNewSize + 1
    Dim aNewInfo( nNewSize ) as new com.sun.star.beans.PropertyValue

    ' copy the elements (a simply copy does not work in Basic)
    For i = nLower To nUpper
        aNewInfo( i ) = aOldInfo( i )
    Next i

    ' append the new setting, if necessary
    If ( bNeedAdd ) Then
        aNewInfo( nUpper + 1 ).Name = sSettingsName
        aNewInfo( nUpper + 1 ).Value = aSettingsValue
    End If

    AddInfo = aNewInfo()
End Function