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