fdo#86852 correctly recognise NULL values in query parameter values dialog

Change-Id: I3c0cf976e0a9fbafe6eee768799a2f48c2ee0ea9

5 files changed, 129 insertions, 50 deletions

diff --git a/connectivity/source/commontools/predicateinput.cxx b/connectivity/source/commontools/predicateinput.cxx
index 5d0cc9d531d9..94a4d3d77860 100644
--- a/connectivity/source/commontools/predicateinput.cxx
+++ b/connectivity/source/commontools/predicateinput.cxx
@@ -54,6 +54,7 @@ namespace dbtools
     using ::com::sun::star::i18n::LocaleData;
     using ::com::sun::star::i18n::XLocaleData;
     using ::com::sun::star::i18n::LocaleDataItem;
+    using ::com::sun::star::uno::Any;
 
     using namespace ::com::sun::star::sdbc;
     using namespace ::connectivity;
@@ -283,9 +284,9 @@ namespace dbtools
     }
 
 
-    OUString OPredicateInputController::getPredicateValue(
+    OUString OPredicateInputController::getPredicateValueStr(
         const OUString& _rPredicateValue, const Reference< XPropertySet > & _rxField,
-        bool _bForStatementUse, OUString* _pErrorMessage ) const
+        OUString* _pErrorMessage ) const
     {
         OSL_ENSURE( _rxField.is(), "OPredicateInputController::getPredicateValue: invalid params!" );
         OUString sReturn;
@@ -293,28 +294,84 @@ namespace dbtools
         {
             OUString sValue( _rPredicateValue );
 
-            // a little problem : if the field is a text field, the normalizePredicateString added two
-            // '-characters to the text. If we would give this to predicateTree this would add
-            // two  additional '-characters which we don't want. So check the field format.
-            // FS - 06.01.00 - 71532
-            bool bValidQuotedText = sValue.startsWith("'") && sValue.endsWith("'");
-                // again : as normalizePredicateString always did a conversion on the value text,
-                // bValidQuotedText == sal_True implies that we have a text field, as no other field
-                // values will be formatted with the quote characters
-            if ( bValidQuotedText )
-            {
-                sValue = sValue.copy( 1, sValue.getLength() - 2 );
-                static const char sSingleQuote[] = "'";
-                static const char sDoubleQuote[] = "''";
+            // The following is mostly stolen from the former implementation in the parameter dialog
+            // (dbaccess/source/ui/dlg/paramdialog.cxx). I do not fully understand this .....
+
+            OUString sError;
+            OSQLParseNode* pParseNode = implPredicateTree( sError, sValue, _rxField );
+            if ( _pErrorMessage )
+                *_pErrorMessage = sError;
+
+            implParseNode(pParseNode, true) >>= sReturn;
+        }
 
-                sal_Int32 nIndex = -1;
-                sal_Int32 nTemp = 0;
-                while ( -1 != ( nIndex = sValue.indexOf( sDoubleQuote,nTemp ) ) )
+        return sReturn;
+    }
+
+    OUString OPredicateInputController::getPredicateValueStr(
+        const OUString& _sField, const OUString& _rPredicateValue, OUString* _pErrorMessage ) const
+    {
+        OUString sReturn = _rPredicateValue;
+        OUString sError;
+        OUString sField = _sField;
+        sal_Int32 nIndex = 0;
+        sField = sField.getToken(0,'(',nIndex);
+        if(nIndex == -1)
+            sField = _sField;
+        sal_Int32 nType = ::connectivity::OSQLParser::getFunctionReturnType(sField,&m_aParser.getContext());
+        if ( nType == DataType::OTHER || sField.isEmpty() )
+        {
+            // first try the international version
+            OUString sSql = "SELECT * FROM x WHERE " + sField + _rPredicateValue;
+            boost::scoped_ptr<OSQLParseNode> pParseNode( const_cast< OSQLParser& >( m_aParser ).parseTree( sError, sSql, true ) );
+            nType = DataType::DOUBLE;
+            if ( pParseNode.get() )
+            {
+                OSQLParseNode* pColumnRef = pParseNode->getByRule(OSQLParseNode::column_ref);
+                if ( pColumnRef )
                 {
-                    sValue = sValue.replaceAt( nIndex, 2, sSingleQuote );
-                    nTemp = nIndex+2;
                 }
             }
+        }
+
+        Reference<XDatabaseMetaData> xMeta = m_xConnection->getMetaData();
+        parse::OParseColumn* pColumn = new parse::OParseColumn( sField,
+                                                                OUString(),
+                                                                OUString(),
+                                                                OUString(),
+                                                                ColumnValue::NULLABLE_UNKNOWN,
+                                                                0,
+                                                                0,
+                                                                nType,
+                                                                false,
+                                                                false,
+                                                                xMeta.is() && xMeta->supportsMixedCaseQuotedIdentifiers(),
+                                                                OUString(),
+                                                                OUString(),
+                                                                OUString());
+        Reference<XPropertySet> xColumn = pColumn;
+        pColumn->setFunction(true);
+        pColumn->setRealName(sField);
+
+        OSQLParseNode* pParseNode = implPredicateTree( sError, _rPredicateValue, xColumn );
+        if ( _pErrorMessage )
+            *_pErrorMessage = sError;
+        if(pParseNode)
+        {
+            implParseNode(pParseNode, true) >>= sReturn;
+        }
+        return sReturn;
+    }
+
+    Any OPredicateInputController::getPredicateValue(
+        const OUString& _rPredicateValue, const Reference< XPropertySet > & _rxField,
+        OUString* _pErrorMessage ) const
+    {
+        OSL_ENSURE( _rxField.is(), "OPredicateInputController::getPredicateValue: invalid params!" );
+
+        if ( _rxField.is() )
+        {
+            OUString sValue( _rPredicateValue );
 
             // The following is mostly stolen from the former implementation in the parameter dialog
             // (dbaccess/source/ui/dlg/paramdialog.cxx). I do not fully understand this .....
@@ -324,16 +381,15 @@ namespace dbtools
             if ( _pErrorMessage )
                 *_pErrorMessage = sError;
 
-            sReturn = implParseNode(pParseNode,_bForStatementUse);
+            return implParseNode(pParseNode, false);
         }
 
-        return sReturn;
+        return Any();
     }
 
-    OUString OPredicateInputController::getPredicateValue(
-        const OUString& _sField, const OUString& _rPredicateValue, bool _bForStatementUse, OUString* _pErrorMessage ) const
+    Any OPredicateInputController::getPredicateValue(
+        const OUString& _sField, const OUString& _rPredicateValue, OUString* _pErrorMessage ) const
     {
-        OUString sReturn = _rPredicateValue;
         OUString sError;
         OUString sField = _sField;
         sal_Int32 nIndex = 0;
@@ -378,14 +434,16 @@ namespace dbtools
         OSQLParseNode* pParseNode = implPredicateTree( sError, _rPredicateValue, xColumn );
         if ( _pErrorMessage )
             *_pErrorMessage = sError;
-        return pParseNode ? implParseNode(pParseNode,_bForStatementUse) : sReturn;
+        return pParseNode ? implParseNode(pParseNode, false) : Any();
     }
 
-    OUString OPredicateInputController::implParseNode(OSQLParseNode* pParseNode, bool _bForStatementUse) const
+    Any OPredicateInputController::implParseNode(OSQLParseNode* pParseNode, bool _bForStatementUse) const
     {
-        OUString sReturn;
-        if ( pParseNode )
+        if ( ! pParseNode )
+            return Any();
+        else
         {
+            OUString sReturn;
             boost::shared_ptr<OSQLParseNode> xTakeOwnership(pParseNode);
             OSQLParseNode* pOdbcSpec = pParseNode->getByRule( OSQLParseNode::odbc_fct_spec );
             if ( pOdbcSpec )
@@ -408,7 +466,13 @@ namespace dbtools
             }
             else
             {
-                if (pParseNode->count() >= 3)
+                if (pParseNode->getKnownRuleID() == OSQLParseNode::test_for_null )
+                {
+                    assert(pParseNode->count() == 2);
+                    return Any();
+                }
+                // LEM this seems overly permissive as test...
+                else if (pParseNode->count() >= 3)
                 {
                     OSQLParseNode* pValueNode = pParseNode->getChild(2);
                     assert(pValueNode && "OPredicateInputController::getPredicateValue: invalid node child!");
@@ -427,10 +491,13 @@ namespace dbtools
                         );
                 }
                 else
+                {
                     OSL_FAIL( "OPredicateInputController::getPredicateValue: unknown/invalid structure (noodbc)!" );
+                    return Any();
+                }
             }
+            return Any(sReturn);
         }
-        return sReturn;
     }
 
 }   // namespace dbtools
diff --git a/dbaccess/source/core/api/SingleSelectQueryComposer.cxx b/dbaccess/source/core/api/SingleSelectQueryComposer.cxx
index ef3c0e079e16..f8f8c2b93410 100644
--- a/dbaccess/source/core/api/SingleSelectQueryComposer.cxx
+++ b/dbaccess/source/core/api/SingleSelectQueryComposer.cxx
@@ -1510,11 +1510,11 @@ namespace
                     if ( i_xSelectColumns.is() && i_xSelectColumns->hasByName(sColumnName) )
                     {
                         Reference<XPropertySet> xColumn(i_xSelectColumns->getByName(sColumnName),UNO_QUERY);
-                        sValue = i_aPredicateInputController.getPredicateValue(sValue,xColumn,true);
+                        sValue = i_aPredicateInputController.getPredicateValueStr(sValue,xColumn);
                     }
                     else
                     {
-                        sValue = i_aPredicateInputController.getPredicateValue(pAndIter->Name,sValue,true);
+                        sValue = i_aPredicateInputController.getPredicateValueStr(pAndIter->Name,sValue);
                     }
                     lcl_addFilterCriteria_throw(pAndIter->Handle,sValue,sRet);
                     ++pAndIter;
diff --git a/dbaccess/source/ui/dlg/paramdialog.cxx b/dbaccess/source/ui/dlg/paramdialog.cxx
index f4fdc07c1ec9..d39d86fb623a 100644
--- a/dbaccess/source/ui/dlg/paramdialog.cxx
+++ b/dbaccess/source/ui/dlg/paramdialog.cxx
@@ -95,12 +95,6 @@ namespace dbaui
                 pValues->Name = ::comphelper::getString(xParamAsSet->getPropertyValue(PROPERTY_NAME));
                 m_pAllParams->InsertEntry(pValues->Name);
 
-                if (!pValues->Value.hasValue())
-                    // it won't have a value, 'cause it's default constructed. But may be later we support
-                    // initializing this dialog with values
-                    pValues->Value = makeAny( OUString() );
-                    // default the values to an empty string
-
                 m_aVisitedParams.push_back(0);
                     // not visited, not dirty
             }
@@ -239,7 +233,7 @@ namespace dbaui
 
                         OUString sValue;
                         pValues->Value >>= sValue;
-                        pValues->Value <<= OUString( m_aPredicateInput.getPredicateValue( sValue, xParamAsSet, false ) );
+                        pValues->Value <<= m_aPredicateInput.getPredicateValue( sValue, xParamAsSet );
                     }
                 }
                 catch(Exception&)
diff --git a/dbaccess/source/ui/dlg/queryfilter.cxx b/dbaccess/source/ui/dlg/queryfilter.cxx
index f8ad536b0f3c..7c3ad2591b37 100644
--- a/dbaccess/source/ui/dlg/queryfilter.cxx
+++ b/dbaccess/source/ui/dlg/queryfilter.cxx
@@ -346,7 +346,8 @@ bool DlgFilterCrit::getCondition(const ListBox& _rField,const ListBox& _rComp,co
     _rFilter.Handle = GetOSQLPredicateType( _rComp.GetSelectEntry() );
     if ( SQLFilterOperator::SQLNULL != _rFilter.Handle && _rFilter.Handle != SQLFilterOperator::NOT_SQLNULL )
     {
-        OUString sPredicateValue = m_aPredicateInput.getPredicateValue( _rValue.GetText(), getMatchingColumn( _rValue ), false );
+        OUString sPredicateValue;
+        m_aPredicateInput.getPredicateValue( _rValue.GetText(), getMatchingColumn( _rValue ) ) >>= sPredicateValue;
         if ( _rFilter.Handle == SQLFilterOperator::LIKE ||
              _rFilter.Handle == SQLFilterOperator::NOT_LIKE )
             ::Replace_OS_PlaceHolder( sPredicateValue );
diff --git a/include/connectivity/predicateinput.hxx b/include/connectivity/predicateinput.hxx
index 45b20987a1f8..549824ec3708 100644
--- a/include/connectivity/predicateinput.hxx
+++ b/include/connectivity/predicateinput.hxx
@@ -27,6 +27,7 @@
 #include <com/sun/star/i18n/XLocaleData4.hpp>
 #include <connectivity/sqlparse.hxx>
 #include <connectivity/dbtoolsdllapi.hxx>
+#include <com/sun/star/uno/Any.hxx>
 
 
 namespace dbtools
@@ -74,30 +75,46 @@ namespace dbtools
             OUString* _pErrorMessage = NULL
         ) const;
 
-        /** get's a value of the predicate which can be used in a WHERE clause.
+        /** get the value of the predicate, as a string to be used in a WHERE clause
         @param _rPredicateValue
             the value which has been normalized using normalizePredicateString
         @param _rxField
             is the field for which a predicate is to be entered
-        @param _bForStatementUse
-            If <TRUE/>, the returned value can be used in an SQL statement. If <FALSE/>, it can be used
-            for instance for setting parameter values.
         @param _pErrorMessage
             If not <NULL/>, and a parsing error occurs, the error message will be copied to the string the argument
             points to.
         @see normalizePredicateString
         */
-        OUString getPredicateValue(
+        OUString getPredicateValueStr(
             const OUString& _rPredicateValue,
             const ::com::sun::star::uno::Reference< ::com::sun::star::beans::XPropertySet > & _rxField,
-            bool _bForStatementUse,
             OUString* _pErrorMessage = NULL
         ) const;
 
-        OUString getPredicateValue(
+        OUString getPredicateValueStr(
+            const OUString& _sField
+            , const OUString& _rPredicateValue
+            , OUString* _pErrorMessage = NULL) const;
+
+        /** get the value of the predicate, either as an empty or as a string
+        @param _rPredicateValue
+            the value which has been normalized using normalizePredicateString
+        @param _rxField
+            is the field for which a predicate is to be entered
+        @param _pErrorMessage
+            If not <NULL/>, and a parsing error occurs, the error message will be copied to the string the argument
+            points to.
+        @see normalizePredicateString
+        */
+        ::com::sun::star::uno::Any getPredicateValue(
+            const OUString& _rPredicateValue,
+            const ::com::sun::star::uno::Reference< ::com::sun::star::beans::XPropertySet > & _rxField,
+            OUString* _pErrorMessage = NULL
+        ) const;
+
+        ::com::sun::star::uno::Any getPredicateValue(
             const OUString& _sField
             , const OUString& _rPredicateValue
-            , bool _bForStatementUse
             , OUString* _pErrorMessage = NULL) const;
 
     private:
@@ -113,7 +130,7 @@ namespace dbtools
             sal_Unicode& _rThdSep
         ) const;
 
-        OUString implParseNode(::connectivity::OSQLParseNode* pParseNode, bool _bForStatementUse) const;
+        ::com::sun::star::uno::Any implParseNode(::connectivity::OSQLParseNode* pParseNode, bool _bForStatementUse) const;
     };