tclientdataset applyupdates exception - invalid parameter

[KMarb 5]

Delphi 11 using DataSnap, although the communication between client and server seems fine, so datasnap I don't think is relevant. Problem is with DBExpress and readonly dataset, I think.

 

I don't know a short way to ask for help on this, but here goes:

 

On my datasnap server I'm using TSQLConnection to access SQL Server. I use TSQLQuery to run a query that gets an empty dataset from the SQL Server. The TSQLQuery is connected to a dataset provider which in turn is connected to a TClientDataset. here is some of my code... this code is called after to datasnap client calls the server method to add 1 or more rows to tables on the server:

 

    function copyDataToServer (tblName   : string;
                               dataIndex : integer) : integer;
    var
      i : integer;
      s : string;
      s2 : string;
    begin
      result := 0;

 

      // tmpCDS is a TClientDataset that has the data to be added to the server... the client passes a clientDataset.Data to the server using a Variant array.

      tmpCDS.Data := transData [dataIndex];

 

      //  build a query to pull empty dataset from server

          s := '';

          for i := 0 to tmpCDS.FieldCount - 1 do
            if s = '' then
              s := tmpCDS.Fields .FieldName
            else
              s := s + ', ' + tmpCDS.Fields .FieldName;

          s := 'select ' + s + ' from ' + tblName + 'Transfer where RegID = ' + intToStr (RegID);

 

          // QSv is a TSQLQuery

          QSv.SQL.Add (s);  
          QSv.Prepared := true;

 

           // CDSSv is the TClientDataset connected to datasetprovider, which in turn is connected to QSv

          CDSSv.Open;  // this successfully gets an empty dataset for the table in question - after the open, CDSSv.CanModify is true, Readonly is false, recordCount is 0... all as they should be.

 

          tmpCDS.First;  // this is the dataset from the datasnap client

          while not tmpCDS.EOF do
            begin             

              CDSSv.Insert;

 

              // not all fields might be present, and order of fields might change...

              for i := 0 to tmpCDS.FieldCount - 1 do
                 CDSSv.FieldByName (tmpCDS.Fields .FieldName).Value := tmpCDS.Fields .Value;

 

              CDSSv.Post;  // this works, new row is saved in the clientdataset

 

              tmpCDS.Next;
            end;

 

           // at this point 100 +/- rows are in the clientdataset, CDSSv

          try
            result := CDSSv.ApplyUpdates (-1);    // save recs to the transfer table - THIS BLOWS UP!!!
          except on e:exception do
            begin
              writeDbugS ('result := CDSSave.ApplyUpdates (-1) exception - ' + e.Message;); // exception message is Invalid parameter
            end;
          end;
 

Any help is much appreciated... The above code has worked for 5 years. I'm updating things to try to keep up with android versions (for the client side of the datasnap) and in the process needed to update the datasnap server, but I've done something to make the above code stop working and I havn't been able to track it down.

 

Edited November 19, 2022 by KMarb

[KMarb 5]

I added some debugging output and have determined a few things:

 

After CDSSv.open... CDSSv.CanModify is TRUE, cdsSv.ReadOnly is FALSE

 

And I loop through all fields in CDSSv and each field also has CanModify = TRUE and ReadOnly = FALSE

 

So I don't think it is a problem that the dataset or any field is read only.

 

I have searched for why TClientDataSet.ApplyUpdates might generate and exception "Invalid Parameter" but I haven't found anything.

[KMarb 5]

One more thing... I've read that for certain field types, you will get the "invalid parameter" error, particularly with strings and GUID if datasize is not specified...

 

Here's something I'm seeing that might be the problem, but I don't know how to fix.

 

After CDSSv.open (which opens the blank dataset on the server), I loop through all fields to see that fieldtype, datasize, etc are...

 

I have several fields in the underlying SQL table defined as datetime fields (8 bytes).

 

In Delphi, after cdsSv.open, these fields show datatype as ftTimeStamp and datasize as 16 (not ftDateTime and size 8). Why would that happen? And how to fix?

 

To summarize:

 

My SQL is like this: select RegID, myDate from table;

myDate in the sQL table is datetime

After I open the dataset, the field type of myDate is ftTimestamp, not ftDatetime. That seems like it might cause the invalid parameter error when I apply updates.

 

Thanks for looking through all this.

[ByteJuggler 45]

Please always post the actual error message you're getting.  Has there been compiler upgrades?  When did this stop working?  Does this happen in an older version of Delphi? Any other changes?  (SQL Server driver changes?) If this is Delphi version related then it smells like some kind of datatype mapping change/issue in Datasnap.  You might try constructing the CDSSv first by hand, to control the datatypes explicitly, rather than relying on the provider machinery + query to essentially create it for you.  You might also experiment with different versions of midas.dll (or whatever it's called nowadays, assuming it still exists.  -- Not currently using Delph and/or familiar with Delphi 11 changes.)

[KMarb 5]

The exception reported by CDS.applyUpdates is "Invalid parameter."

 

I do not think the datasnap part of this is relevant... maybe I should not have mentioned that at all, but was trying to be complete.

 

With a TSQLQuery (named QSv), if I run a select statement like select fld1, fld2 from tbl where 1=0, and fld2 is a datetime field in SQL Server, the QSv.fields [1].datatype is not ftDatetime, it is ftTimestamp (16 bits long), and I think that is the source of the failure when applyUpdates tries to add rows back to the database table.

[KMarb 5]

I tested a simple case and have found that even in very simple example (3 integer fields), I get the "invalid parameter" error. So the problem or concern I have about the ftTimestamp versus ftDatetime may or may not be relevant.

 

Here is my test code:

 

    procedure testCDS;
    var
      i : integer;
      s : string;
    begin
      resetCDS (CDSSv);  // the reset calls clear fields and parameters from the clientdataset and query
      resetQ (QSv);

 

      s := 'select fld1, fld2, fld3 from testTbl where 1=0';  // I have other code that builds this select statement, but allowing the select from database to build the dataset fields in memory has many advantages (works with multiple tables, for exampe)

      QSv.SQL.Add (s);
      CDSSv.Open; // open empty dataset, all fields should be available now

 

      // attempt to add 3 rows to the table (brand new table, no indexes, just for testing)

      for i := 1 to 3 do
        begin
          CDSSv.Insert;
          CDSSv.fields [0].Value := i;
          CDSSv.fields [1].Value := i;
          CDSSv.fields [2].Value := i;
          CDSSv.Post;  // no error yet... rows are successfully added to the CDS
        end;

 

      try
        CDSSv.ApplyUpdates (-1);    // This is the statement that throws the invalid parameter exception...
      except on e:exception do
        begin
{$IFDEF DBUG} DMServerContainer.writeDbugS ('testCDS - exception - ' + e.Message);  {$ENDIF}
        end;
      end;
    end;

Also... if i go a different route and build an SQL Insert statement and then execute it, the rows are saved to the SQL database as expected... IOW, the code below works, so there is something broken with TSQLQuery -> TDatasetProvider -> TClientDataset

 

s := 'insert testTbl (fld1, fld2, fld3) values (1, 1, 1), (2, 2, 2), (3, 3, 3)';

QSv.SQL.Add (s);

QSv.ExecSQL;

 

So... I can rewrite my code, but then I need to handle strings, datetimes and blob/photo fields which is more work or not possible with an SQL statement (how to insert a photo into image field using an insert statement?).

[KMarb 5]

For anyone who reads through this post, I never did figure out why CDSSv.applyUpdates (-1); throws the "invalid parameter" exception

 

The thought I had related to ftDatetime versus ftTimestamp was a false lead.

 

I'm rewriting my server to use FIredac rather than DBExpress to access the server database. 

[Florian Sterk 0]

I had the same error after migrating our code from Delphi XE2 to 11.2. After calling ApplyUpdates(-1) on a TClientDataSet I received an "invalid parameters" exception. Under Delphi XE2 the same code was executed without any errors. 

 

In our case the problem was an old version of midas.dll (dated from 2012) in the C:\Windows\SysWow64 directory. After copying the midas.dll from C:\Program Files (x86)\Embarcadero\Studio\22.0\bin\ that had come with Delphi 11.2 (file date 2022/08/22) to C:\Windows\SysWow64 the error disappeared and everything worked fine.

 

It seems like the error message is misleading and the error was resulting from a library incompatibility.

[gcrombie 0]

I agree with Florian, had this happen when I had an older version of the midas.dll on my system - from Delphi XE, and my Delphi 11.2 client was picking it up, getting the same error on ApplyUpdates.  I think the best way to remedy is to take away the chance of it picking up an outdated DLL and add midaslib to your projects primary uses clause.