New TZParam/TZParams enabled

[EgonHugeist]

Hello,
i added the new TZParam component and it TZParams collection.

This is done to make my promisse true, that the z8-Series should also support Array BATCH-DML bindings and Component-Layer, which is suported by DBC since years.
The TZParam offers all native types Zeos supports, and doesn't care about the DataSet-FieldTypes.
A list of Properites copied from the TZDatasetParam unit:
It's similar to http://docwiki.embarcadero.com/Librarie ... Properties, even is some of the props are missing(Streams, XML, DataSet ..)

Code: Select all

    // <summary>Represents the size of a TZParam object array.<summary>
    //commented yet: setting array size should happen by TZParams only
    //property ArraySize: Cardinal read fArraySize write SetArraySize;
    {$IFNDEF NO_ANSISTRING}
    /// <summary>Represents the value of a TZParam object as an AnsiString.</summary>
    property AsAnsiString: AnsiString read GetAsAnsiString write SetAsAnsiString;
    /// <summary>Represents an array of AnsiString values.</summary>
    property AsAnsiStrings[Index: Cardinal]: AnsiString read GetAsAnsiStrings write SetAsAnsiStrings;
    {$ENDIF NO_ANSISTRING}
    /// <summary>Represents the value of a TZParam as a binary-coded decimal
    ///  (BCD/System.Currency)</summary>
    property AsBCD: Currency read GetAsCurrency write SetAsCurrency;
    /// <summary>Represents an array of binary-coded decimal (BCD/System.Currency) values.</summary>
    property AsBCDs[Index: Cardinal]: Currency read GetAsCurrencys write SetAsCurrencys;
    /// <summary>Specifies the value of a TZParam when it represents a
    ///  binary large object (BLOB).</summary>
    property AsBlob: TBlobData read {$IFDEF TBLOBDATA_IS_TBYTES}GetAsBytes{$ELSE}GetAsAnsiString{$ENDIF}write SetAsBlob;
    /// <summary>Represents an array of binary large object (BLOB) values.</summary>
    property AsBlobs[Index: Cardinal]: TBlobData read {$IFDEF TBLOBDATA_IS_TBYTES}GetAsBytesArray{$ELSE}GetAsAnsiStrings{$ENDIF} write SetAsBlobs;
    /// <summary>Specifies the value of a TZParam when it represents a Boolean.</summary>
    property AsBoolean: Boolean read GetAsBoolean write SetAsBoolean;
    /// <summary>Represents an array of Boolean values.</summary>
    property AsBooleans[Index: Cardinal]: Boolean read GetAsBooleans write SetAsBooleans;
    /// <summary>Specifies the value of a TZParam when it represents a Byte.</summary>
    property AsByte: Byte read GetAsByte write SetAsByte;
    /// <summary>Represents an array of Byte values.</summary>
    property AsByteArray[Index: Cardinal]: Byte read GetAsByteArray write SetAsByteArray;
    /// <summary>Specifies the value of a TZParam when it represents a Byte array.</summary>
    property AsBytes: {$IFDEF WITH_GENERICS_TFIELD_ASBYTES}TArray<Byte>{$ELSE}TBytes{$ENDIF} read GetAsBytes write SetAsBytes;
    /// <summary>Represents an array of Byte values.</summary>
    property AsBytesArray[Index: Cardinal]: {$IFDEF WITH_GENERICS_TFIELD_ASBYTES}TArray<Byte>{$ELSE}TBytes{$ENDIF} read GetAsBytesArray write SetAsBytesArray;
    /// <summary>Specifies the value of a TZParam when it represents a Cardinal</summary>
    property AsCardinal: Cardinal read GetAsCardinal write SetAsCardinal;
    /// <summary>Represents an array of Cardinal values.</summary>
    property AsCardinals[Index: Cardinal]: Cardinal read GetAsCardinals write SetAsCardinals;
    /// <summary>Specifies the value of a TZParam when it represents a Currency</summary>
    property AsCurrency: Currency read GetAsCurrency write SetAsCurrency;
    /// <summary>Represents an array of Currency values.</summary>
    property AsCurrencys[Index: Cardinal]: Currency read GetAsCurrencys write SetAsCurrencys;
    /// <summary>Specifies the value of a TZParam when it represents a Double</summary>
    property AsDouble: Double read GetAsDouble write SetAsDouble;
    /// <summary>Represents an array of Double values.</summary>
    property AsDoubles[Index: Cardinal]: Double read GetAsDoubles write SetAsDoubles;
    /// <summary>Specifies the value of a TZParam when it represents a TDate</summary>
    property AsDate: TDate read GetAsDate write SetAsDate;
    /// <summary>Represents an array of TDate values.</summary>
    property AsDates[Index: Cardinal]: TDate read GetAsDates write SetAsDates;
    /// <summary>Specifies the value of a TZParam when it represents a TDateTime</summary>
    property AsDateTime: TDateTime read GetAsDateTime write SetAsDateTime;
    /// <summary>Represents an array of TDateTime values.</summary>
    property AsDateTimes[Index: Cardinal]: TDateTime read GetAsDateTimes write SetAsDateTimes;
    /// <summary>Specifies the value of a TZParam when it represents a Float</summary>
    property AsFloat: Double read GetAsDouble write SetAsDouble;
    /// <summary>Represents an array of Float values.</summary>
    property AsFloats[Index: Cardinal]: Double read GetAsDoubles write SetAsDoubles;
    /// <summary>Specifies the value of a TZParam when it represents a TBcd</summary>
    property AsFmtBCD: TBCD read GetAsFmtBCD write SetAsFmtBCD;
    /// <summary>Represents an array of TBcd values.</summary>
    property AsFmtBCDs[Index: Cardinal]: TBCD read GetAsFmtBCDs write SetAsFmtBCDs;
    /// <summary>Specifies the value of a TZParam when it represents a TGUID</summary>
    property AsGUID: TGUID read GetAsGUID write SetAsGUID;
    /// <summary>Represents an array of TGUID values.</summary>
    property AsGUIDs[Index: Cardinal]: TGUID read GetAsGUIDs write SetAsGUIDs;
    /// <summary>Specifies the value of a TZParam when it represents a 32Bit Integer.</summary>
    property AsInteger: Integer read GetAsInteger write SetAsInteger;
    /// <summary>Represents an array of 32Bit Integer values.</summary>
    property AsIntegers[Index: Cardinal]: Integer read GetAsIntegers write SetAsIntegers;
    /// <summary>Specifies the value of a TZParam when it represents a 64Bit Integer.</summary>
    property AsInt64: Int64 read GetAsInt64 write SetAsInt64;
    /// <summary>Represents an array of 64Bit Integer values.</summary>
    property AsInt64s[Index: Cardinal]: Int64 read GetAsInt64s write SetAsInt64s;
    /// <summary>Specifies the value of a TZParam when it represents a LargeInt.</summary>
    property AsLargeInt: LargeInt read GetAsInt64 write SetAsInt64;
    /// <summary>Represents an array of LargeInt values.</summary>
    property AsLargeInts[Index: Cardinal]: Int64 read GetAsInt64s write SetAsInt64s;
    /// <summary>Specifies the value of a TZParam when it represents a 32Bit LongWord.</summary>
    property AsLongword: Cardinal read GetAsCardinal write SetAsCardinal;
    /// <summary>Represents an array of 32Bit LongWord values.</summary>
    property AsLongwords[Index: Cardinal]: Cardinal read GetAsCardinals write SetAsCardinals;
    /// <summary>Specifies the value of a TZParam when it represents a Memo.</summary>
    property AsMemo: String read {$IFDEF UNICODE}GetAsUnicodeString{$ELSE}GetAsString{$ENDIF} write SetAsMemo;
    /// <summary>Represents an array of Memo values.</summary>
    property AsMemos[Index: Cardinal]: String read {$IFDEF UNICODE}GetAsUnicodeStrings{$ELSE}GetAsStrings{$ENDIF} write SetAsMemos;
    /// <summary>Specifies the value of a TZParam when it represents a 8Bit Integer.</summary>
    property AsShortInt: ShortInt read GetAsShortInt write SetAsShortInt;
    /// <summary>Represents an array of 8Bit signed integer values.</summary>
    property AsShortInts[Index: Cardinal]: ShortInt read GetAsShortInts write SetAsShortInts;
    /// <summary>Specifies the value of a TZParam when it represents a Single.</summary>
    property AsSingle: Single read GetAsSingle write SetAsSingle;
    /// <summary>Represents an array of Single values.</summary>
    property AsSingles[Index: Cardinal]: Single read GetAsSingles write SetAsSingles;
    /// <summary>Specifies the value of a TZParam when it represents a 16Bit singned Integer.</summary>
    property AsSmallInt: SmallInt read GetAsSmallInt write SetAsSmallInt;
    /// <summary>Represents an array of 16Bit signed integer values.</summary>
    property AsSmallInts[Index: Cardinal]: SmallInt read GetAsSmallInts write SetAsSmallInts;
    /// <summary>Specifies the value of a TZParam when it represents a String.</summary>
    property AsString: String read {$IFDEF UNICODE}GetAsUnicodeString write SetAsUnicodeString{$ELSE}GetAsString write SetAsString{$ENDIF};
    /// <summary>Represents an array of String values.</summary>
    property AsStrings[Index: Cardinal]: String read {$IFDEF UNICODE}GetAsUnicodeStrings write SetAsUnicodeStrings{$ELSE}GetAsStrings write SetAsStrings{$ENDIF};
    /// <summary>Specifies the value of a TZParam when it represents a TTime.</summary>
    property AsTime: TTime read GetAsTime write SetAsTime;
    /// <summary>Represents an array of TTime values.</summary>
    property AsTimes[Index: Cardinal]: TTime read GetAsTimes write SetAsTimes;
    /// <summary>Specifies the value of a TZParam when it represents a UInt64.</summary>
    property AsUInt64: UInt64 read GetAsUInt64 write SetAsUInt64;
    /// <summary>Represents an array of UInt64 values.</summary>
    property AsUInt64s[Index: Cardinal]: UInt64 read GetAsUInt64s write SetAsUInt64s;
    /// <summary>Specifies the value of a TZParam when it represents a UnicodeString.</summary>
    property AsUnicodeMemo: UnicodeString read GetAsUnicodeString write SetAsWideMemo;
    /// <summary>Represents an array of UnicodeString values.</summary>
    property AsUnicodeMemos[Index: Cardinal]: UnicodeString read GetAsUnicodeStrings write SetAsWideMemos;
    /// <summary>Specifies the value of a TZParam when it represents a UnicodeString.</summary>
    property AsUnicodeString: UnicodeString read GetAsUnicodeString write SetAsUnicodeString;
    /// <summary>Represents an array of UnicodeString values.</summary>
    property AsUnicodeStrings[Index: Cardinal]: UnicodeString read GetAsUnicodeStrings write SetAsUnicodeStrings;
    /// <summary>Specifies the value of a TZParam when it represents a UTF8String.</summary>
    property AsUTF8String: UTF8String read GetAsUTF8String write SetAsUTF8String;
    /// <summary>Represents an array of UTF8String values.</summary>
    property AsUTF8Strings[Index: Cardinal]: UTF8String read GetAsUTF8Strings write SetAsUTF8Strings;
    /// <summary>Specifies the value of a TZParam when it represents a UnicodeString.</summary>
    property AsWideMemo: UnicodeString read GetAsUnicodeString write SetAsWideMemo;
    /// <summary>Represents an array of UnicodeString values.</summary>
    property AsWideMemos[Index: Cardinal]: UnicodeString read GetAsUnicodeStrings write SetAsWideMemos;
    /// <summary>Specifies the value of a TZParam when it represents a UnicodeString.</summary>
    property AsWideString: UnicodeString read GetAsUnicodeString write SetAsUnicodeString;
    /// <summary>Represents an array of UnicodeString values.</summary>
    property AsWideStrings[Index: Cardinal]: UnicodeString read GetAsUnicodeStrings write SetAsUnicodeStrings;
    /// <summary>Specifies the value of a TZParam when it represents a 16Bit unsingned Integer.</summary>
    property AsWord: Word read GetAsWord write SetAsWord;
    /// <summary>Represents an array of 16Bit unsigned integer values.</summary>
    property AsWords[Index: Cardinal]: Word read GetAsWords write SetAsWords;
    /// <summary>Specifies the value of a TZParam when it represents a TZDate.</summary>
    property AsZDate: TZDate read GetAsZDate write SetAsZDate;
    /// <summary>Represents an array of TZDate values.</summary>
    property AsZDates[Index: Cardinal]: TZDate read GetAsZDates write SetAsZDates;
    /// <summary>Specifies the value of a TZParam when it represents a TZTime.</summary>
    property AsZTime: TZTime read GetAsZTime write SetAsZTime;
    /// <summary>Represents an array of TZTime values.</summary>
    property AsZTimes[Index: Cardinal]: TZTime read GetAsZTimes write SetAsZTimes;
    /// <summary>Specifies the value of a TZParam when it represents a TZTimestamp.</summary>
    property AsZTimestamp: TZTimestamp read GetAsZTimestamp write SetAsZTimestamp;
    /// <summary>Represents an array of TZTimestamp values.</summary>
    property AsZTimestamps[Index: Cardinal]: TZTimestamp read GetAsZTimestamps write SetAsZTimestamps;
    /// <summary>Indicates whether a value (NULL or otherwise) has been assigned
    ///  to the parameter.</summary>
    property Bound: Boolean read FBound;
     /// <summary>Indicates whether a TZParam value is NULL.</summary>
    property IsNull: Boolean read GetIsNull write SetIsNull;
    /// <summary>Indicates whether a TZParam array value is NULL.</summary>
    property IsNulls[Index: Cardinal]: Boolean read GetIsNulls write SetIsNulls;
    property NativeStr: String read FNativeStr write FNativeStr;
  published
    /// <summary>Indicates the type of field whose value the TZParam represents.</summary>
    property DataType: TFieldType read FDataType write SetDataType default ftUnknown;
    /// <summary>Indicates the name of a TZParam object.</summary>
    property Name: String read FName write FName;
    /// <summary>Specifies the number of decimal places for a TZParam object.</summary>
    property NumericScale: Integer read FNumericScale write FNumericScale  default 0;
    /// <summary>Indicates the type of the parameter the TZParam represents.</summary>
    property ParamType: TParamType read FParamType write FParamType default ptUnknown;
    /// <summary>Specifies the number of digits allowed for a numeric parameter.</summary>
    property Precision: Integer read FPrecision write FPrecision default 0;
    /// <summary>Specifies the number of characters in a string-typed parameter.</summary>
    property Size: Integer read FSize write FSize default 0;
    /// <summary>Specifies the exact SQLType for a TZParam object.</summary>
    property SQLType: TZSQLType read FSQLType write SetSQLType default stUnknown;
    /// <summary>Specifies the value of a TZParam when it represents a TZDate.</summary>
    property Value: Variant read GetAsVariant write SetAsVariant stored IsParamStored;

As you can see there are loads more exact properties available.
Just name some Delphi unsupported types:

Code: Select all

AsGUID, AsUInt64/AsQWord

And for the FPC:

Code: Select all

AsGUID, AsUInt64/AsQWord, AsByte, AsShortInt, AsSingle..

Exact time/timestamp with nanosecond fractions values can be used by the AsZTime/AsZTimestamp properties.

As you can see each singular property has a plural property.
I wrote a Test which shows the purpose and how to use it:

Code: Select all

procedure TZTestBatchDML.InternalTestArrayBinding(Query: TZQuery;
  FirstID, ArrayLen, LastFieldIndex: Integer);
var
  I, J: Integer;
  {$IFNDEF TBLOBDATA_IS_TBYTES}
  Bts: TBytes;
  BlobData: TBlobData;
  {$ENDIF}
begin
  CheckNotNull(Query);
  Query.Params.BatchDMLCount := ArrayLen;
  for i := 0 to ArrayLen-1 do begin
    Query.Params[0].AsIntegers[i] := FirstID+I;
    for J := 1 to LastFieldIndex do
      case hlTypeArray[j] of
        stBoolean:      Query.Params[J].AsBooleans[I] := Boolean(Random(1));
        stByte:         Query.Params[J].AsByteArray[I] := Random(High(Byte));
        stShort:        Query.Params[J].AsShortInts[I] := Random(High(Byte))+Low(ShortInt);
        stWord:         Query.Params[J].AsWords[I] := Random(High(Word));
        stSmall:        Query.Params[J].AsSmallInts[I] := Random(High(Word))+Low(SmallInt);
        stInteger:      Query.Params[J].AsIntegers[I] := Random(High(Word))+Low(SmallInt);
        stLongWord:     Query.Params[J].AsCardinals[I] := Random(High(Word));
        stLong:         Query.Params[J].AsIntegers[I] := Random(High(Word))+Low(SmallInt);
        stULong:        Query.Params[J].AsCardinals[I] := Random(High(Word));
        stFloat:        Query.Params[J].AsSingles[I] := RandomFloat(-5000, 5000);
        stDouble:       Query.Params[J].AsDoubles[I] := RandomFloat(-5000, 5000);
        stCurrency:     Query.Params[J].AsCurrencys[I] := RandomFloat(-5000, 5000);
        stBigDecimal:   Query.Params[J].AsFmtBCDs[I] := DoubleToBCD(RandomFloat(-5000, 5000));
        stTime:         Query.Params[J].AsTimes[I] := Now;
        stDate:         Query.Params[J].AsDates[I] := Now;
        stTimeStamp:    Query.Params[J].AsDateTimes[I] := Now;
        stGUID:         Query.Params[J].AsGUIDs[i] := RandomGUID;
        stString:       Query.Params[J].AsStrings[i] := RandomStr(Random(99)+1);
        stUnicodeString:Query.Params[J].AsUnicodeStrings[i] := {$IFNDEF UNICODE}Ascii7ToUnicodeString{$ENDIF}(RandomStr(Random(99)+1));
        stBytes:        Query.Params[J].AsBytesArray[i] := RandomBts(ArrayLen);
        stAsciiStream:  Query.Params[J].AsMemos[i] := RandomStr(Random(99)+1);
        stUnicodeStream:Query.Params[J].AsUnicodeMemos[i] := {$IFNDEF UNICODE}Ascii7ToUnicodeString{$ENDIF}(RandomStr(Random(99)+1));
        {$IFDEF TBLOBDATA_IS_TBYTES}
        stBinaryStream: Query.Params[J].AsBlobs[i] := RandomBts(ArrayLen);
        {$ELSE !TBLOBDATA_IS_TBYTES}
        stBinaryStream: begin
                          Bts := RandomBts(ArrayLen);
                          BlobData := '';
                          System.SetString(Blobdata, PAnsiChar(Bts), ArrayLen);
                          Query.Params[J].AsBlobs[i] := BlobData;
                        end;
        {$ENDIF !TBLOBDATA_IS_TBYTES}
        {$IFDEF WITH_CASE_WARNING}else ;{$ENDIF}
      end;
  end;
  Query.ExecSQL;
  CheckEquals(ArrayLen, Query.RowsAffected);
end;

const
  LastFieldIndices: array[0..2] of Integer = (hl_Unicode_Index, hl_Date_Index, hl_BinaryStream_Index);
  HighLoadFields: array[hl_id_Index..hl_BinaryStream_Index] of String = (
    'hl_id', 'stBoolean', 'stByte', 'stShort', 'stInteger', 'stLong', ''+
      'stFloat', 'stDouble', 'stBigDecimal', 'stString', 'stUnicodeString', 'stBytes',
      'stDate', 'stTime', 'stTimestamp', 'stGUID', 'stAsciiStream', 'stUnicodeStream',
      'stBinaryStream');

procedure TZTestBatchDML.TestBatchDMLArrayBindings;
var
  Query:  TZQuery;
  I, j: Integer;
  SQL: String;
begin
  Connection.Connect;
  Check(Connection.Connected);
  if Connection.DbcConnection.GetMetadata.GetDatabaseInfo.SupportsArrayBindings then begin
    Query := CreateQuery;
    try
      for i := low(LastFieldIndices) to high(LastFieldIndices) do begin
        Connection.ExecuteDirect('delete from high_load');
        SQL := 'insert into high_load(';
        for j := hl_id_Index to LastFieldIndices[i] do
          SQL := SQL+HighLoadFields[j]+',';
        SQL[Length(SQL)] := ')';
        SQL := SQL + ' values (';
        for j := hl_id_Index to LastFieldIndices[i] do
          SQL := SQL+':'+SysUtils.IntToStr(j+1)+',';
        SQL[Length(SQL)] := ')';
        Query.SQL.Text := SQL;
        InternalTestArrayBinding(Query, 0, 50, LastFieldIndices[i]);
        InternalTestArrayBinding(Query, 50, 20, LastFieldIndices[i]);
        InternalTestArrayBinding(Query, 70, 10, LastFieldIndices[i]);
        Query.Params.BatchDMLCount := 0;
      end;
    finally
      FreeAndNil(Query);
      Connection.ExecuteDirect('delete from high_load');
    end;
  end;
end;

Note the

Code: Select all

Query.Params.BatchDMLCount := ArrayLen;

line. This tests inserts 80 rows total, splitted by 50 rows, 20rows, 10 rows.

Yet supported dbc-drivers for the BATCH API are:
Firebird (using Execute-block/IBatch for FB4+)
MariaDB 10.4up (native)
SQLServer using ODBC/OleDB (native api)
Postgres (inserts/deletes supported but no batch updates yet)
Oracle (native)

Just to write some numbers:
I'm able to move a 44Gb SQLServer db to postgres in ~22min and vice versa in ~45min including creating indices, constaints and foregn key's after the move is complete. The postgres fetching time is just the half of SQLServer(problem of libpg not the server it's selve) but the PG insertiation time (no indices, no contraints) is duplicate as fast as SQLServer's. That rocks! Honestly we're using DBC only for this move the circumvent the DB.pas TDataset bottleneck. But the new Param should also be very FAST for the Dataset users!

Guy's if you're in trouble with persitent params, open Zeos.inc, uncomment the DISABLE_ZPARAM define. OTH just make a string replace in your dfm's.

Rules to deal with the array params:

Internaly i implemented DynArrays, the Variant as datastorage is replaced, It's to slow!
If you're using the arrays the DataType/SQLType per param is locked. No inconsitent mix is allowed, or Zeos wil try todo a conversion. If it fails you'll get an exception.
Means forbitten code is:

Code: Select all

Query.Params[0].AsBooleans[1] := True;
Query.Params[0].AsTimes[2] := Now;

also new LoadFromFile/LoadFromStream overloads has been added. and the old ones are tagged as deprecated(encoding inconsistent). Reason was to help uses with all the db encoding quirk. Such as viewtopic.php?f=48&t=127148
Each of the new overloads do support a CodePage. CP(1200) means UTF16, 65001 is UTF8 .... See ZEncoding.pas for a full list of codepages. If the CP = 0/zero we essume it's a binary File/Stream.

I was not able to make the change optional. The DB.pas params have no virtual methods... JFYI.

Feedback is welcome! Have fun!

[aehimself]

Michael,

ZDataSetParams are thowing some warnings in Delphi, I sent a fix on GitHub. I don't have FPC so maybe we need a conditional directive around the AnsiStrings unit in the uses clause.

[EgonHugeist]

I don't have a XE10.4.
Can you list/publish all warnings please?

[aehimself]

"Something was not expanded because System.AnsiStrings is not in the uses list".
That's all what my commit does, it adds the AnsiStrings unit to the uses clause. I don't know if this exists on FPC, though.

[aehimself]

Is there a conditional directive somewhere to force TZQueries to use standard TParams instead of TZParams...? There is a significant difference in how these are handling their values and TZParam sometimes leaks a UnicodeString in my application. I simply could not make a minimalistic test case, when attempting to put it in a new application everything works fine...

The solution would be to change the COMPLETE way I'm saving parameter values or to roll back to standard TParams... I guess rollback is easier...?

[EgonHugeist]

There is a significant difference in how these are handling their values and TZParam sometimes leaks a UnicodeString in my application.

Hmm my tests passing fine without memoryleaks.

he solution would be to change the COMPLETE way I'm saving parameter values or to roll back to standard TParams...

Give me a chance to fix it, that would fair. How are you doing that? Are your still using the TBytes? Or is it related to persistent params and their values?

[aehimself]

@Michael,

I don't think it's a bug in TZParams - as I said I simply can not reproduce the issue outside of my application (however my test was done with 32 bit only, though).
I have an application where the user can enter a query and execute it against a database. Parameters will be asked for in a separate window and for convience are preserved between sessions. The mechanic is not that straightforward.

I'm passing the TZQuery.Params to my own class, which is enumerating and creating an input panel (TFrame descendant) for each TZParam. Upon the editing component change, I'm updating the TZParam.AsString / .AsInteger / .AsDateTime property.
When the input window is closed, I'm storing all TZParam.Value (the variant) in a TDictionary, so the next time I can preinitialize the value of the param with the same value.

Sometimes the leak occures in the frame (_param.AsString := EditBox.Text), sometimes at the end, when I'm storing the .Value in a Dictionary. Leaked "object" is always a UnicodeString.

It's possible that I'm the one mishandling Variants... I don't like them, I rarely use them. YesterdayI scrapped this whole logic and started to re-write it without variants and eliminating the passing of TZParam 3-4 levels deep.
It'll take a couple of days to finish when I can check whether it works or not...

[EgonHugeist]

@aehimself,

it was my fault. Initializing a Variant for a function result using FillChar() did produce the leaks. Using VarClear() instead did fix it. Can you confirm it? Also did i shorten the code by 1000 lines.

[aehimself]

I saw the commit and I am really surprised. It doesn't matter what I did, I simply could not get the leak to appear in a minimalistic case; which is usually a sign that the issue is in my code.
Unfortunately the commit is not yet visible in Git so I have to wait until the next synchronization before I can test it. In the mean time I stashed my changes about the param saving to the original state, so I'm ready to try it out. Hope it works, as most of my attempts were dead ends until now, anyway... :)

Edit: 1000 line reduction... nice! My biggest achievement was a slow refactoring process at work, where I managed to throw half of a ~70k helper unit to the garbage. That however took me about a week to accomplish... so yeah, nice job!

[aehimself]

Michael,

Your fix worked. The memory leak did indeed disappear!

[EgonHugeist]

Thanks for confirmation.

Note: If YOU did'nt change anything in your code which may produce a leak, usually the problem is part of an update. I do my best to finalize Zeos8 but as you know: changing a line (in your case 6.000) sometimes an mistake is shipped whith. Only if i and others would stop the forward development you should find the leaks on your side. So rolling forward/backward unsing the components should help you not getting "paranoid"

Anyway i just want to find out if there are more regressions. Of cource a feedback using the array param in practice would be welcome...

[aehimself]

Michael,

I might be wrong, but I faced an issue which might be related to the new TZParams...?

Consider using Oracle, Given a simple query:

Code: Select all

INSERT INTO MYTABLE (MYDATETIMEFIELD) VALUES (:pDateTimeNow)

MyDateTimeField is a datetime field, not required. I have a logic, which sometimes has to send "null":

Code: Select all

If SomeCondition Then ZQuery.ParamByName('pDateTimeNow').AsDateTime := Now
  Else ZQuery.ParamByName('pDateTimeNow').Clear;

And the following error is thrown:
Execute prepared statement failed ORA-00932: inconsistent datatypes: expected DATE got NUMBER (#932)

There are some strange logs too, from the real applicaton:

Code: Select all

[2020.12.16 00:10:25.454] Create new container: Prepare Statement 12 : INSERT INTO T_FIR2KONTENER (ObfuscatedField1, ObfuscatedField2, ObfuscatedField3, ObfuscatedField4, ObfuscatedField5, ObfuscatedField6, ObfuscatedField7, ObfuscatedField8, ObfuscatedField9, ObfuscatedField10, ObfuscatedField11, ObfuscatedField12) VALUES (GETNEXTGLOBALID(), :pObfuscatedParam1, :pObfuscatedParam2, :pObfuscatedParam3, :pObfuscatedParam4, :pObfuscatedParam5, :pObfuscatedParam6, :pObfuscatedParam7, :pObfuscatedParam8, :pObfuscatedParam9, :pObfuscatedParam10, :pObfuscatedParam11)
[2020.12.16 00:10:25.454] Create new container: Bind prepared Statement 12 : (NULL)0,'T',175083477,749,304012,1216001025,'MATRICA - 2020. 12. 16. 0:10:25 (AEMS)',,,,'F'

ObfuscatedParam8 - 10 has NOTHING in it, was never assigned - it should be null. The ONLY datetime field in the query is ObfuscatedField11, belonging to ObfuscatedParam9.

Now for some testing, if I change my logic to:

Code: Select all

  ZQuery.ParamByName('pDateTimeNow').AsDateTime := Now;
If Not SomeCondition Then
  ZQuery.ParamByName('pDateTimeNow').Clear;

It works like a charm... but the same error is thrown on an other, previously unset number parameter.

All words in one... unset parameters behave differently than set parameters, even if .Clear is called on them.

[EgonHugeist]

IMO all parameters need to be typed.
You can achieve a "typed" param by setting the type. Example:

Code: Select all

TZParam(MyParam).SQLType := stBoolean
or
MyParam.DataType := ftBoolean;

If a param is untyped then the first assignment like your

Code: Select all

ZQuery.ParamByName('pDateTimeNow').AsDateTime := Now;

sets the Type to ftTimeStamp.

unset parameters behave differently than set parameters, even if .Clear is called on them.

That's expected an logically, isn't it? Zeos can't know the type if not specified. Drivers like Oracle, Sybase, SQLite, MySQL/MariaDB can't describe the paramters. Firebird in contrast is doing the job perfectly. SQLServer(OleDB/ODBC) is getting better in this domain too.

Ae, just to confirm i did not break exitsting code, do that advice:

Guy's if you're in trouble with persitent params, open Zeos.inc, uncomment the DISABLE_ZPARAM define. OTH just make a string replace in your dfm's.

This will enable the RTL-Shipped parameters again. If the behavior is equal i don't think were talking about a problem of the TZParam. Can you check this?

[aehimself]

IMO all parameters need to be typed.

Ah, I honestly thought a parameter is just a variant, where null equals null if it's a number or a DateTime. Having to have types makes sense though, I'll check it and report back.

[aehimself]

@ Michael,
You are right as ever: if I initialize the parameter types, they work perfectly.

It was a confusion on my side. As I said I always thought a parameter is just a variant, which automatically gets converted as needed (like myString := myVariant or myInteger := myVariant) even on the SQL server side. Therefore it was logical that a null String is equal to a null Integer...
Well, the more you know.

Thank you for clearing this up for me!