#510 Rev 7490: Error on editing database entry (PostgreSQL)

[Ralph Staudte]

Delphi 10.4 / TZQuery with TZUpdateSQL and TDataSource:

Whenever the user updates a row it results in the following error:

"Bind message supplies 0 parameters, but prepared statement requires 32"

I was able to trace it down to ZSQLStrings.pas in Rev 7490 - my code works with Rev 7489.

[EgonHugeist]

Hello Ralph,
Can you please add more details or an example? My crystal ball is silent yet (no offence).

It's related to TZUpdateSQL but more i can't figure out, sorry. What are you doing?
Regards, Michael

[Ralph Staudte]

Hello Michael,

yes of course... these crystal balls are hard to get!

The user can edit data and eventually hits "Save". The new data is written to the database:

   dmDatabase.qyKunden.ApplyUpdates;
    dmDatabase.qyKunden.CommitUpdates;

dmDatabase is a DataModule that holds pretty much all the DataSets, Queries and UpdateSQLs. For (almost) every Query there is an UpdateSQL with the SQL-Lines for "Delete", "Insert" and "Modify". An example for modifying would be:

UPDATE kunden
SET
  Suchname = :Suchname,
  Anrede = :Anrede,
  Name1 = :Name1,
  Name2 = :Name2,
  Aenderung = CURRENT_TIMESTAMP
WHERE
  KundenNr = :OLD_KundenNr

The last 22 years it worked that way, r7490 causes the UpdateSQL to fail with missing parameters, as I would call it.

There where changes in ZSQLStrings.pas in r7490 that dealt with params as far as I can tell. But the line that clears the params in ZSQLStrings.pas just moved up or down just a few lines in the source - I can't see the cause for this error.

Regards, Ralph

[Ralph Staudte]

Please don't waste too much time with this bug.

I am getting rid of all the UpdateSQLs since all I am doing with it can be done within QueryBeforePost - for example. And I am pretty sure that they will be gone completely by the end of this week.

I was using UpdateSQLs since when I was starting to code my main-application back in 1999 / 2000. I probably didn't know better back then but now I actually think I don't need them anymore.

Thanks so much for the work on Zeos!!

Ralph

[EgonHugeist]

Ralph, if you run into an issue, i made, others will run into same trap, propably.

Yet i can't reproduce it. just commited a logic fix: r7529
Steps i've done:
Add a TZTable to a Formular.
Add a TZUpdateSQL to the formular and link it with tztable.
Generate the SQL's.
Run the appclication with and without persistent Parameters of the TZUpdateSQL component.
Both do work. Wondering...

What's the creation order of the two related components ? Which one is the first in the DFM/LFM file?

[Ralph Staudte]

Hi Michael,

I left one UpdateSQL in the sources just for you ;-)

You've checked the combination of TZTable and TZUpdateSQL - but in my case it's TZQuery with TZUpdateSQL. Just in case it matters...

Here is the needed part of the dfm, I hope it helps:

  object dsKredite: TDataSource
    DataSet = qyKredite
    OnStateChange = dsKrediteStateChange
    Left = 672
    Top = 59
  end
  object qyKredite: TZQuery
    Connection = dbDataBase
    BeforePost = qyKrediteBeforePost
    UpdateObject = upKredite
    SQL.Strings = (
      'SELECT * FROM kredite')
    Params = <>
    Left = 732
    Top = 59
    object qyKreditekreditnr: TZIntegerField
      FieldName = 'kreditnr'
      Required = True
    end
    object qyKreditesuchname: TZUnicodeStringField
      FieldName = 'suchname'
      Required = True
      Size = 35
    end
    object qyKreditegeber: TZBooleanField
      FieldName = 'geber'
    end
    object qyKreditetyp: TZIntegerField
      FieldName = 'typ'
      Required = True
    end
    object qyKreditename1: TZUnicodeStringField
      FieldName = 'name1'
      Required = True
      Size = 35
    end
    object qyKreditename2: TZUnicodeStringField
      FieldName = 'name2'
      Size = 35
    end
    object qyKrediteinfo: TZUnicodeCLobField
      FieldName = 'info'
      BlobType = ftWideMemo
    end
    object qyKreditebetrag: TZBCDField
      FieldName = 'betrag'
      DisplayFormat = ',0.00'
      Precision = 9
      Size = 2
    end
    object qyKreditezinssatz: TZBCDField
      FieldName = 'zinssatz'
      DisplayFormat = ',0.00'
      Precision = 4
      Size = 2
    end
    object qyKreditebeginn: TZDateField
      FieldName = 'beginn'
    end
    object qyKrediteende: TZDateField
      FieldName = 'ende'
    end
    object qyKrediteaktiv: TZBooleanField
      FieldName = 'aktiv'
    end
    object qyKrediteerstanlage: TZDateField
      FieldName = 'erstanlage'
    end
    object qyKrediteaenderung: TZDateField
      FieldName = 'aenderung'
    end
    object qyKreditezahlung: TZBCDField
      FieldName = 'zahlung'
      DisplayFormat = ',0.00'
      Precision = 9
      Size = 2
    end
  end
  object upKredite: TZUpdateSQL
    DeleteSQL.Strings = (
      'DELETE FROM kredite'
      'WHERE'
      '  KreditNr = :OLD_KreditNr')
    InsertSQL.Strings = (
      'INSERT INTO kredite'
      '  (KreditNr, Suchname, Geber, Typ, Name1, Name2, Info,'
      '   Betrag, Zahlung, Zinssatz, Beginn, Ende, Aktiv,'
      '   Erstanlage)'
      'VALUES'
      '  (:KreditNr, :Suchname, :Geber, :Typ, :Name1, :Name2, :Info,'
      '   :Betrag, :Zahlung, :Zinssatz, :Beginn, :Ende, True,'
      '   CURRENT_TIMESTAMP)')
    ModifySQL.Strings = (
      'UPDATE kredite'
      'SET'
      '  Suchname = :Suchname,'
      '  Geber = :Geber,'
      '  Typ = :Typ,'
      '  Name1 = :Name1,'
      '  Name2 = :Name2,'
      '  Info = :Info,'
      '  Betrag = :Betrag,'
      '  Zahlung = :Zahlung,'
      '  Zinssatz = :Zinssatz,'
      '  Beginn = :Beginn,'
      '  Ende = :Ende,'
      '  Aktiv = :Aktiv,'
      '  Aenderung = CURRENT_TIMESTAMP'
      'WHERE'
      '  KreditNr = :OLD_KreditNr')
    UseSequenceFieldForRefreshSQL = False
    Left = 793
    Top = 59
    ParamData = <
      item
        Name = 'Suchname'
      end
      item
        Name = 'Geber'
      end
      item
        Name = 'Typ'
      end
      item
        Name = 'Name1'
      end
      item
        Name = 'Name2'
      end
      item
        Name = 'Info'
      end
      item
        Name = 'Betrag'
      end
      item
        Name = 'Zahlung'
      end
      item
        Name = 'Zinssatz'
      end
      item
        Name = 'Beginn'
      end
      item
        Name = 'Ende'
      end
      item
        Name = 'Aktiv'
      end
      item
        Name = 'OLD_KreditNr'
      end
      item
        Name = 'KreditNr'
      end>
  end

[Ralph Staudte]

Hi Michael,

things are getting strange:

I just found another UpdateSQL outside my DataModule. ****And this one works!

Things that are different - the ones I found - are:

• TDataSource, TZQuery and TZUpdateSQL are placed on a form (not a DataModule)
• The TZQuery's Connection is in the DataModule
• This Query does not have a "BeforePost"
• There is no CURRENT_TIMESTAMP in the InsertSQL- or ModifySQL-lines

Here is the part of the dfm:

object dsBasiszins: TDataSource
    DataSet = qyBasiszins
    OnStateChange = dsBasiszinsStateChange
    Left = 192
    Top = 8
  end
  object qyBasiszins: TZQuery
    Connection = dmDatabase.dbDataBase
    UpdateObject = upBasiszins
    SQL.Strings = (
      'SELECT * FROM basiszins'
      'ORDER BY datum DESC')
    Params = <>
    Left = 224
    Top = 8
    object qyBasiszinsdatum: TZDateField
      FieldName = 'datum'
    end
    object qyBasiszinswert: TZBCDField
      FieldName = 'wert'
      DisplayFormat = ',0.00'
      Size = 2
    end
    object qyBasiszinsdatumbis: TZDateField
      FieldName = 'datumbis'
    end
  end
  object upBasiszins: TZUpdateSQL
    DeleteSQL.Strings = (
      'DELETE FROM basiszins'
      'WHERE'
      '  Datum = :OLD_Datum')
    InsertSQL.Strings = (
      'INSERT INTO basiszins'
      '  (Datum, Wert, DatumBis)'
      'VALUES'
      '  (:Datum, :Wert, :DatumBis)')
    ModifySQL.Strings = (
      'UPDATE basiszins'
      'SET'
      '  DatumBis = :DatumBis,'
      '  Wert = :Wert'
      'WHERE'
      '  Datum = :OLD_Datum')
    UseSequenceFieldForRefreshSQL = False
    Left = 223
    Top = 39
    ParamData = <
      item
        Name = 'DatumBis'
      end
      item
        Name = 'Wert'
      end
      item
        Name = 'OLD_Datum'
      end
      item
        Name = 'Datum'
      end>
  end

[marsupilami79]

Hello Ralph,

could you please try to create a small sample application that demonstrates the problem? It only needs to be a form with a button that will do the necessary calls. Also a create script for the database would be good. Otherwise it will get really hard to debug the problem.

Best regards,

Jan

[Artyom]

I confirm that there is a huge oddity here. I was doing a simple project where I used TZUpdateSQL, TZQuery and TZConnection. I put them in one DataModule. When adding a record I get the error:

"Bind message supplies 0 parameters, but prepared statement requires 8"

And once I moved TZConnection to another DataModule, everything worked for me. The error disappeared and records began to be added

I am using Embarcadero® Delphi® 2010 Version 14.0.3593.25826
Zeos: 8.0.0-stable build at 2024-04-07 12:29:02