New TZParam/TZParams enabled
Posted: 03.12.2020, 07:08
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 ..)
As you can see there are loads more exact properties available.
Just name some Delphi unsupported types:
And for the FPC:
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:
Note the 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:
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!
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;
Just name some Delphi unsupported types:
Code: Select all
AsGUID, AsUInt64/AsQWordCode: Select all
AsGUID, AsUInt64/AsQWord, AsByte, AsShortInt, AsSingle..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;
Code: Select all
Query.Params.BatchDMLCount := ArrayLen;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;
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!
Of cource a feedback using the array param in practice would be welcome...