Issue 114648

Summary: Enable Parameter Name Substitution
Product: Base Reporter: siriogala <sirio.gala>
Component: codeAssignee: AOO issues mailing list <issues>
Status: UNCONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues
Version: OOo 3.2.1   
Target Milestone: ---   
Hardware: Unknown   
OS: Windows XP   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---

Description siriogala 2010-09-21 13:53:49 UTC
Hi!
While i tried to use the variables (:X)i get this error.
Reading around i arrived to this page:
http://dba.openoffice.org/howto/param_subst.html

I've created the macro, runned, and when the macro asked me the name of the data
source i typed the name i've called the connection, but i got another error:

"There is no data source named I24" (I24 is how i called the connection)
I tried ODBC also, and every reference i can find...

Could someone help?
Thanks a lot,
D.Z.
Comment 1 r4zoli 2010-09-21 14:09:08 UTC
Please give us more detais, the linked page is outdated, as warns work with OOo
prior 2.0.

What macro you tried, when you get the error message? 
What database and what version you used and, what ODBC driver you used?
Comment 2 siriogala 2010-09-21 14:22:35 UTC
Hi!
Thanks for the answer.

Here are the infos you need:
-Used Macro:

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 

----

- I simply tried to use a variable in the WHEN statement ([...] WHERE [...]
"MOVMAG"."IDFORPAR" = :x [...]).

I got the error after inserting the "x" Value (char o numerical, both mode)

The exact error is: 

-SQLSTATE=07009 -Invalid Descriptor Index 

You tried to set a parameter at position 1 but there is/are only 0 parameter(s)
allowed. One reason may be that the property "ParameterNameSubstitution" is not
set to TRUE in the data source. 

I'm using Ooffice 3.2.1, Ms SQL 2000 and the odbc driver on the client are the
version 2000.85.1132.00.

Thanks again.