| <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> |
| <html> |
| <head> |
| <meta http-equiv="content-type" |
| content="text/html; charset=ISO-8859-1"> |
| <title>Parameter Name Substitution</title> |
| </head> |
| <body> |
| <h1>Parameter Name Substitution for ODBC data sources</h1> |
| <span style="font-size:xx-large;font-weight:bold">Note:</span> <em>The below information |
| is outdated, it applies only to OpenOffice.org versions prior to 2.0.</em> |
| <h2>The Problem</h2> |
| When working with parametrized statements, OpenOffice.org usually uses |
| named parameters such as in "<span style="font-family: monospace;">SELECT |
| * FROM <table> WHERE <field> = :param</span>" Here "<span |
| style="font-family: monospace;">:param</span>" is a so-called <span |
| style="font-style: italic;">named parameter</span>. However, some |
| databases do not allow such named parameters, but only unnamed ones. The |
| statement then would be "<span style="font-family: monospace;">SELECT * |
| FROM <table> WHERE <field> = ?</span>". Such databases |
| usually reject statements with named parameters.<br> |
| <h2>The Solution</h2> |
| 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.<br> |
| <br> |
| Parameter name substitution can be enabled on a per-data-source basis. |
| For this, the "Info" property of a <a |
| href="http://api.openoffice.org/docs/common/ref/com/sun/star/sdb/DataSource.html">data |
| source</a> should contain a name-value-pair with<br> |
| <span style="font-family: monospace;">Name</span>: <span |
| style="font-family: monospace;">ParameterNameSubstituion</span><br> |
| <span style="font-family: monospace;">Value</span>: <span |
| style="font-family: monospace;">TRUE</span><br> |
| <br> |
| Unfortunately, there is no user interface, yet, for doing so. There is |
| an <a href="http://www.openoffice.org/issues/show_bug.cgi?id=2794">issuezilla |
| bug requesting this UI</a>, 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.<br> |
| <br> |
| 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. |
| <h2>The Macro</h2> |
| The following macro enables parameter name substitution for a data |
| source of your choice. You can also download this macro in the <a |
| href="../downloads/">downloads section</a>.<br> |
| <br> |
| <span style="font-family: monospace; color: rgb(102, 102, 102);">REM |
| ***** BASIC *****</span><br style="font-family: monospace;"> |
| <br style="font-family: monospace;"> |
| <span style="font-family: monospace;"><span |
| style="color: rgb(0, 0, 128);">Option Explicit</span><br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">Sub</span> <span |
| style="color: rgb(0, 128, 0);">Main</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> <span |
| style="color: rgb(0, 0, 128);">as String</span><br> |
| <span style="color: rgb(0, 128, 0);">sDataSourceName</span> |
| = <span style="color: rgb(0, 128, 0);">InputBox</span>( <span |
| style="color: rgb(255, 0, 0);">"Please enter the name of the data |
| source:"</span> )<br> |
| <span style="color: rgb(0, 128, 0);">EnableParameterNameSubstitution</span>(<span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> )<br> |
| <span style="color: rgb(0, 0, 128);">End Sub</span><br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">Sub</span> <span |
| style="color: rgb(0, 128, 0);">EnableParameterNameSubstitution</span>( <span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> <span |
| style="color: rgb(0, 0, 128);">as String</span> )<br> |
| <span style="color: rgb(102, 102, 102);">' the data |
| source context (ehm - the service name is historical :)</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">aContext</span> <span |
| style="color: rgb(0, 0, 128);">as Object</span><br> |
| <span style="color: rgb(0, 128, 0);">aContext</span> |
| = <span style="color: rgb(0, 128, 0);">createUnoService</span>( <span |
| style="color: rgb(255, 0, 0);">"com.sun.star.sdb.DatabaseContext"</span> |
| )<br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">If</span> ( <span |
| style="color: rgb(0, 0, 128);">Not</span> <span |
| style="color: rgb(0, 128, 0);">aContext</span>.<span |
| style="color: rgb(0, 128, 0);">hasByName</span>( <span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> ) ) <span |
| style="color: rgb(0, 0, 128);">Then</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">MsgBox</span> <span |
| style="color: rgb(255, 0, 0);">"There is no data source named "</span> |
| + <span style="color: rgb(0, 128, 0);">sDataSourceName</span> + <span |
| style="color: rgb(255, 0, 0);">"!"</span><br> |
| <span |
| style="color: rgb(0, 0, 128);">Exit Sub</span><br> |
| <span style="color: rgb(0, 0, 128);">End If</span><br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' the data |
| source</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">aDataSource</span> <span |
| style="color: rgb(0, 0, 128);">as Object</span><br> |
| <span style="color: rgb(0, 128, 0);">aDataSource</span> |
| = <span style="color: rgb(0, 128, 0);">aContext</span>.<span |
| style="color: rgb(0, 128, 0);">getByName</span>( <span |
| style="color: rgb(0, 128, 0);">sDataSourceName</span> )<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' append |
| the new ParameterNameSubstitution flag</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">bFlag</span> <span |
| style="color: rgb(0, 0, 128);">as Boolean</span><br> |
| <span style="color: rgb(0, 128, 0);">bFlag</span> = <span |
| style="color: rgb(0, 128, 0);">TRUE</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">aInfo</span> <span |
| style="color: rgb(0, 0, 128);">as Variant</span><br> |
| <span style="color: rgb(0, 128, 0);">aInfo</span> = <span |
| style="color: rgb(0, 128, 0);">aDataSource</span>.<span |
| style="color: rgb(0, 128, 0);">Info</span><br> |
| <span style="color: rgb(0, 128, 0);">aInfo</span> = <span |
| style="color: rgb(0, 128, 0);">AddInfo</span>( <span |
| style="color: rgb(0, 128, 0);">aInfo</span>, <span |
| style="color: rgb(255, 0, 0);">"ParameterNameSubstitution"</span>, <span |
| style="color: rgb(0, 128, 0);">bFlag</span> )<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' and |
| write back</span><br> |
| <span style="color: rgb(0, 128, 0);">aDataSource</span>.<span |
| style="color: rgb(0, 128, 0);">Info</span> = <span |
| style="color: rgb(0, 128, 0);">aInfo</span><br> |
| <span style="color: rgb(102, 102, 102);">' flush |
| (not really necessary, but to be on the safe side :)</span><br> |
| <span style="color: rgb(0, 128, 0);">aDataSource</span>.<span |
| style="color: rgb(0, 128, 0);">flush</span><br> |
| <span style="color: rgb(0, 0, 128);">End Sub</span><br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">Function</span> <span |
| style="color: rgb(0, 128, 0);">AddInfo</span>( <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>() <span |
| style="color: rgb(0, 0, 128);">as new</span> <span |
| style="color: rgb(0, 128, 0);">com.sun.star.beans.PropertyValue</span>,<span |
| style="color: rgb(0, 128, 0);">sSettingsName</span> <span |
| style="color: rgb(0, 0, 128);">as String</span>, <span |
| style="color: rgb(0, 128, 0);">aSettingsValue</span> <span |
| style="color: rgb(0, 0, 128);">as Variant</span> ) <span |
| style="color: rgb(0, 0, 128);">as Variant</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">nLower</span> <span |
| style="color: rgb(0, 0, 128);">as Integer</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">nUpper</span> <span |
| style="color: rgb(0, 0, 128);">as Integer</span><br> |
| <span style="color: rgb(0, 128, 0);">nLower</span> = <span |
| style="color: rgb(0, 128, 0);">LBound</span>( <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>() )<br> |
| <span style="color: rgb(0, 128, 0);">nUpper</span> = <span |
| style="color: rgb(0, 128, 0);">UBound</span>( <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>() )<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' look if |
| the setting is already present</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">bNeedAdd</span> <span |
| style="color: rgb(0, 0, 128);">as Boolean</span><br> |
| <span style="color: rgb(0, 128, 0);">bNeedAdd</span> |
| = <span style="color: rgb(0, 128, 0);">TRUE</span><br> |
| <br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">i</span> <span |
| style="color: rgb(0, 0, 128);">As Integer</span><br> |
| <span style="color: rgb(0, 0, 128);">For</span> <span |
| style="color: rgb(0, 128, 0);">i</span> = <span |
| style="color: rgb(0, 128, 0);">nLower</span> <span |
| style="color: rgb(0, 0, 128);">To</span> <span |
| style="color: rgb(0, 128, 0);">nUpper</span><br> |
| <span |
| style="color: rgb(0, 0, 128);">If</span> ( <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>( <span |
| style="color: rgb(0, 128, 0);">i</span> ).<span |
| style="color: rgb(0, 0, 128);">Name</span> = <span |
| style="color: rgb(0, 128, 0);">sSettingsName</span> ) <span |
| style="color: rgb(0, 0, 128);">Then</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>( <span |
| style="color: rgb(0, 128, 0);">i</span> ).<span |
| style="color: rgb(0, 128, 0);">Value</span> = <span |
| style="color: rgb(0, 128, 0);">aSettingsValue</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">bNeedAdd</span> = <span |
| style="color: rgb(0, 128, 0);">FALSE</span><br> |
| <span |
| style="color: rgb(0, 0, 128);">End If</span><br> |
| <span style="color: rgb(0, 0, 128);">Next</span> i<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' allocate |
| the new array</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">nNewSize</span> <span |
| style="color: rgb(0, 0, 128);">as Integer</span><br> |
| <span style="color: rgb(0, 128, 0);">nNewSize</span> |
| = ( <span style="color: rgb(0, 128, 0);">nUpper</span> - <span |
| style="color: rgb(0, 128, 0);">nLower</span> )<br> |
| <span style="color: rgb(0, 0, 128);">If</span> <span |
| style="color: rgb(0, 128, 0);">bNeedAdd</span> <span |
| style="color: rgb(0, 0, 128);">Then</span> <span |
| style="color: rgb(0, 128, 0);">nNewSize</span> = <span |
| style="color: rgb(0, 128, 0);">nNewSize</span> + <span |
| style="color: rgb(255, 0, 0);">1</span><br> |
| <span style="color: rgb(0, 0, 128);">Dim</span> <span |
| style="color: rgb(0, 128, 0);">aNewInfo</span>( <span |
| style="color: rgb(0, 128, 0);">nNewSize</span> ) <span |
| style="color: rgb(0, 0, 128);">as new</span> <span |
| style="color: rgb(0, 128, 0);">com.sun.star.beans.PropertyValue</span><br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' copy the |
| elements (a simply copy does not work in Basic)</span><br> |
| <span style="color: rgb(0, 0, 128);">For</span> <span |
| style="color: rgb(0, 128, 0);">i</span> = <span |
| style="color: rgb(0, 128, 0);">nLower</span> <span |
| style="color: rgb(0, 0, 128);">To</span> <span |
| style="color: rgb(0, 128, 0);">nUpper</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">aNewInfo</span>( <span |
| style="color: rgb(0, 128, 0);">i</span> ) = <span |
| style="color: rgb(0, 128, 0);">aOldInfo</span>( <span |
| style="color: rgb(0, 128, 0);">i</span> )<br> |
| <span style="color: rgb(0, 0, 128);">Next</span> i<br> |
| <br> |
| <span style="color: rgb(102, 102, 102);">' append |
| the new setting, if necessary</span><br> |
| <span style="color: rgb(0, 0, 128);">If</span> ( <span |
| style="color: rgb(0, 128, 0);">bNeedAdd</span> ) <span |
| style="color: rgb(0, 0, 128);">Then</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">aNewInfo</span>( <span |
| style="color: rgb(0, 128, 0);">nUpper</span> + <span |
| style="color: rgb(255, 0, 0);">1</span> ).<span |
| style="color: rgb(0, 0, 128);">Name</span> = <span |
| style="color: rgb(0, 128, 0);">sSettingsName</span><br> |
| <span |
| style="color: rgb(0, 128, 0);">aNewInfo</span>( <span |
| style="color: rgb(0, 128, 0);">nUpper</span> + <span |
| style="color: rgb(255, 0, 0);">1</span> ).<span |
| style="color: rgb(0, 128, 0);">Value</span> = <span |
| style="color: rgb(0, 128, 0);">aSettingsValue</span><br> |
| <span style="color: rgb(0, 0, 128);">End If</span><br> |
| <br> |
| <span style="color: rgb(0, 128, 0);">AddInfo</span> |
| = <span style="color: rgb(0, 128, 0);">aNewInfo</span>()<br> |
| <span style="color: rgb(0, 0, 128);">End Function</span></span> |
| <h2></h2> |
| </body> |
| </html> |