[oracle] Fix date insertion

Values are not converted anymore to strings before binding, so that QDate values are written as dates. Fixes #27087
qgis · Oct 31, 2019 · dda4966 · dda4966
1 parent 0b04c34
commit dda4966
Show file tree

Hide file tree

Showing 3 changed files with 49 additions and 36 deletions.
diff --git a/src/providers/oracle/qgsoracleprovider.cpp b/src/providers/oracle/qgsoracleprovider.cpp
@@ -1187,27 +1187,22 @@ QVariant QgsOracleProvider::defaultValue( int fieldId ) const
   return mDefaultValues.value( fieldId, QVariant() );
 }
 
-QString QgsOracleProvider::paramValue( QString fieldValue, const QString &defaultValue ) const
+QVariant QgsOracleProvider::evaluateDefaultExpression( const QString &value, const QVariant::Type &fieldType ) const
 {
-  if ( fieldValue.isNull() )
+  if ( value.isEmpty() )
   {
-    return QString();
+    return QVariant( fieldType );
   }
-  else if ( fieldValue == defaultValue && !defaultValue.isNull() )
-  {
-    QgsOracleConn *conn = connectionRO();
-    QSqlQuery qry( *conn );
-    if ( !exec( qry, QString( "SELECT %1 FROM dual" ).arg( defaultValue ), QVariantList() ) || !qry.next() )
-    {
-      throw OracleException( tr( "Evaluation of default value failed" ), qry );
-    }
 
-    return qry.value( 0 ).toString();
-  }
-  else
+  QgsOracleConn *conn = connectionRO();
+  QSqlQuery qry( *conn );
+  if ( !exec( qry, QString( "SELECT %1 FROM dual" ).arg( value ), QVariantList() ) || !qry.next() )
   {
-    return fieldValue;
+    throw OracleException( tr( "Evaluation of default value failed" ), qry );
   }
+
+  // return the evaluated value
+  return convertValue( fieldType, qry.value( 0 ).toString() );
 }
 
 
@@ -1336,29 +1331,16 @@ bool QgsOracleProvider::addFeatures( QgsFeatureList &flist, QgsFeatureSink::Flag
       {
         QVariant value = attributevec.value( fieldId[i], QVariant() );
 
-        QString v;
-        if ( value.isNull() )
+        QgsField fld = field( fieldId[i] );
+        if ( ( value.isNull() && mPrimaryKeyAttrs.contains( i ) && !defaultValues.at( i ).isEmpty() ) ||
+             ( value.toString() == defaultValues[i] ) )
         {
-          if ( mPrimaryKeyAttrs.contains( i ) && !defaultValues.at( i ).isEmpty() )
-          {
-            QgsField fld = field( fieldId[i] );
-            v = paramValue( defaultValues[i], defaultValues[i] );
-            features->setAttribute( fieldId[i], convertValue( fld.type(), v ) );
-          }
-        }
-        else
-        {
-          v = paramValue( value.toString(), defaultValues[i] );
-
-          if ( v != value.toString() )
-          {
-            QgsField fld = field( fieldId[i] );
-            features->setAttribute( fieldId[i], convertValue( fld.type(), v ) );
-          }
+          value = evaluateDefaultExpression( defaultValues[i], fld.type() );
         }
+        features->setAttribute( fieldId[i], value );
 
-        QgsDebugMsgLevel( QStringLiteral( "addBindValue: %1" ).arg( v ), 4 );
-        ins.addBindValue( v );
+        QgsDebugMsgLevel( QStringLiteral( "addBindValue: %1" ).arg( value.toString() ), 4 );
+        ins.addBindValue( value );
       }
 
       if ( !ins.exec() )

diff --git a/src/providers/oracle/qgsoracleprovider.h b/src/providers/oracle/qgsoracleprovider.h
@@ -181,7 +181,11 @@ class QgsOracleProvider : public QgsVectorDataProvider
   private:
     QString whereClause( QgsFeatureId featureId, QVariantList &args ) const;
     QString pkParamWhereClause() const;
-    QString paramValue( QString fieldvalue, const QString &defaultValue ) const;
+
+    /**
+     * Evaluates the given expression string server-side and convert the result to the given type
+     */
+    QVariant evaluateDefaultExpression( const QString &value, const QVariant::Type &fieldType ) const;
     void appendGeomParam( const QgsGeometry &geom, QSqlQuery &qry ) const;
     void appendPkParams( QgsFeatureId fid, QSqlQuery &qry ) const;
 

diff --git a/tests/src/python/test_provider_oracle.py b/tests/src/python/test_provider_oracle.py
@@ -79,6 +79,7 @@ def getSource(self):
         self.execSQLCommand('ALTER TABLE "QGIS"."EDIT_DATA" MODIFY "pk" DROP IDENTITY', ignore_errors=True)
         self.execSQLCommand('DROP TABLE "QGIS"."EDIT_DATA"', ignore_errors=True)
         self.execSQLCommand("""CREATE TABLE QGIS.EDIT_DATA ("pk" INTEGER GENERATED by default ON null as IDENTITY(START WITH 1 INCREMENT BY 1) PRIMARY KEY, "cnt" INTEGER, "name" VARCHAR2(100), "name2" VARCHAR2(100), "num_char" VARCHAR2(100), GEOM SDO_GEOMETRY)""")
+        self.execSQLCommand("""DELETE FROM user_sdo_geom_metadata  where TABLE_NAME = 'EDIT_DATA'""")
         self.execSQLCommand(
             """INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'EDIT_DATA', 'GEOM', sdo_dim_array(sdo_dim_element('X',-75,-55,0.005),sdo_dim_element('Y',65,85,0.005)),4326)""", ignore_errors=True)
         self.execSQLCommand("""CREATE INDEX edit_data_spatial_idx ON QGIS.EDIT_DATA(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX""")
@@ -190,6 +191,32 @@ def testDateTimeTypes(self):
         self.assertEqual(f.attributes()[datetime_idx], QDateTime(
             QDate(2004, 3, 4), QTime(13, 41, 52)))
 
+    def testDateInsertion(self):
+        # fix for https://github.com/qgis/QGIS/issues/27087
+        vl = QgsVectorLayer('%s table="QGIS"."DATE_TIMES" sql=' %
+                            (self.dbconn), "testdatetimes", "oracle")
+        self.assertTrue(vl.isValid())
+
+        for f in vl.getFeatures():
+            pass
+
+        new_id = 2
+
+        # delete the feature #2 if it is already present
+        vl.dataProvider().deleteFeatures([new_id])
+
+        # add a new feature
+        newf = QgsFeature(f.fields(), new_id)
+        date_idx = vl.fields().lookupField('date_field')
+        dt = QDate(2019, 10, 15)
+        newf.setAttribute(0, new_id)
+        newf.setAttribute(date_idx, dt)
+        success, featureAdded = vl.dataProvider().addFeatures([newf])
+        self.assertTrue(success)
+
+        # clean up
+        vl.dataProvider().deleteFeatures([new_id])
+
     def testDefaultValue(self):
         self.assertEqual(self.source.defaultValue(1), NULL)
         self.assertEqual(self.source.defaultValue(2), "'qgis'")