You are not logged in.
- Topics: Active | Unanswered
#151 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-24 09:30:17
Thanx Daniel.
Arnaud today i commit my own idea about Batch insertiation with FireBird. see http://sourceforge.net/p/zeoslib/code-0/commit_browser R3275 \testing-7.2.
I added two size checks (correct me if i'm wrong): command should fit in 32KB range and whole allocated mem must fit in 64KB limit per execution.
Enabled SupportsArrayBindings indicator for Firebird in R3276.
Nice NO changes required in you framework!
I don't use fb-embedded(should perform much better) here, tested with FBCLIENT.DLL:
{
"Engine": "ZEOS Firebird",
"CreateTableTime": "137.45ms",
"NumberOfElements": 5000,
"InsertTime": "3.72s",
"InsertRate": 1342,
"InsertBatchTime": "122.62ms",
"InsertBatchRate": 40776,
"InsertTransactionTime": "354.58ms",
"InsertTransactionRate": 14101,
"InsertBatchTransactionTime": "123.99ms",
"InsertBatchTransactionRate": 40323,
"ReadOneByOneTime": "548.43ms",
"ReadOneByOneRate": 9116,
"ReadAllVirtualTime": "43.60ms",
"ReadAllVirtualRate": 114676,
"ReadAllDirectTime": "33.96ms",
"ReadAllDirectRate": 147210,
"ClientCloseTime": "4.82ms"
}As i wrote: have no other component to compare the performance. Feel free to test the results 
I would be interested to know how we pereform agains Uni/FireDac now.
OpenSource rocks! Yes it does!
Edit:
Nice!
I wonder why my implementation is more than 20% faster than your initial version.
Sounds just like a small refactoring from your initial patch.
No idea the changes are not related to the results. It looks to me if my system falls into standby mode and wakes up then... OCI is still tired than 
#152 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-21 08:36:34
Thank you, i did commit a patch R3270 \testing-7.2 (SVN).
Hope we got these little buggas now
#153 Re: mORMot 1 » HTTP Background Service » 2014-08-21 08:04:48
Arnaud,
i think we're running into UpperCase/LowerCase/MixedCse trouble again:
Table Pais should be IdentifierQuoted as "Pais" and Zeos successfully would return the indexinfo. Otherwise Zeos does LowerCase the tablename. We had SOOOOO many bugreports in the past accordingly such determinations.
MetaData.GetTables returns all Tables like the users did create them. Using as syntax like
YourStringValue := StringToUTF8(MetaData.GetIdentifierConverter.Quote(TableName));could prevent this case. What i don't know is: would thes quote chars like
´´ for MySQL
[] for MSSQL/ADO
"" for FB/PostgreSQL
....make trouble in your JSON parser?
#154 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-21 07:53:15
Daniel,
could you debug function ZDbcInterbaseUtils.pas function procedure TZParamsSQLDA.UpdatePAnsiChar(const Index: Integer; const Value: PAnsiChar; const Len: Cardinal);
which field type is determined by ISC and are my conversion function correctly (you'll see this in if function UpdateDateTime(Index, TempTimeStamp); is called).
What i see is a minor bug with my last commit, ReadFormatSettings should be replaced by WriteFormatSettings. But AFAIK should they be binary equal for mORMot.
#155 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-20 20:58:38
Sorry guys, wasn't @computer yet.
Just commited a patch R3269 \testing-7.2 (SVN). Indeed there was a conversion from string to Date/Time/DateTime-Fields missing -> i broke with "on the fly" memory reallocation for the XSQLDA record of FB/IB.
Daniel don't you think a new thread would be right in your case? Thought i do inform synopse about Zeos realted !upgrades!
Could you update Zeos and try if your issue is resolved?
I'm not sure that a library like ZDBC should have to create on the fly SQL, like EXECUTE BLOCK or multi-INSERT...
IMHO it should be best handled at caller level.
My first intention was to agree. BUT there is always the spezial CharacterSet 'NONE' case, Arnaud.
What i plan to do is:
call prepare and isc_decribe_bind for the "normal" insert stmt. This returns the SQLDA informations about the column with the expected codepages/column for the string fields. If i do not go this route i just see new tickets in a short time..
Out of time now, the lady is waiting Cheers
#156 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-20 09:31:59
About MultipleValuesInsertFirebird: the EXECUTE BLOCK statement fails to execute on ZDBC...
nasty ): I'll check it later too. Did start now a own EXECUTE BLOCK assembling idea. Here the strings your allways using do bring some advantages. Also could it be usefull for PostgresSQL which is always string based, for MySQL in emulation mode too. So the "bottleneck" could be usefull too if we use it right.
Just an unimplemented idea:
procedure TZAbstractPreparedStatement.SetDataArray(ParameterIndex: Integer;
const Value; const SQLType: TZSQLType; const VariantType: TZVariantType = vtNull);We could set the binary SQLType like stInteger as SQLType, the VariantType could differ here. So if we set the Variant type to vtUTF8String i would know on Zeos side i don't need quotes for the strings and add your already processed strings to the EXECUTE BLOCK stmt or point to the strings with postgressql. Just an idea. All we need would be an aditional indicator to know if binary arrays are prefered or not...
I've integrated your proposal, a bit rewritten and refactored.
See http://synopse.info/fossil/info/0b0f595b12
But I was not able to test it on Oracle, since I do not have any Oracle server available here yet.
Your feedback is welcome!
Compiles out of the box and performce pretty nice:
{
"Engine": "Oracle",
"CreateTableTime": "48.12ms",
"NumberOfElements": 5000,
"InsertTime": "1.49s",
"InsertRate": 3342,
"InsertBatchTime": "45.02ms",
"InsertBatchRate": 111044,
"InsertTransactionTime": "910.61ms",
"InsertTransactionRate": 5490,
"InsertBatchTransactionTime": "45.33ms",
"InsertBatchTransactionRate": 110297,
"ReadOneByOneTime": "679.41ms",
"ReadOneByOneRate": 7359,
"ReadAllVirtualTime": "33.88ms",
"ReadAllVirtualRate": 147544,
"ReadAllDirectTime": "21.65ms",
"ReadAllDirectRate": 230936,
"ClientCloseTime": "28.37ms"
}
{
"Engine": "ZEOS Oracle",
"CreateTableTime": "17.40ms",
"NumberOfElements": 5000,
"InsertTime": "1.40s",
"InsertRate": 3557,
"InsertBatchTime": "55.62ms",
"InsertBatchRate": 89895,
"InsertTransactionTime": "827.88ms",
"InsertTransactionRate": 6039,
"InsertBatchTransactionTime": "59.28ms",
"InsertBatchTransactionRate": 84335,
"ReadOneByOneTime": "687.13ms",
"ReadOneByOneRate": 7276,
"ReadAllVirtualTime": "35.29ms",
"ReadAllVirtualRate": 141679,
"ReadAllDirectTime": "26.08ms",
"ReadAllDirectRate": 191688,
"ClientCloseTime": "24.34ms"
}We need the 7.2 unstable branch. The trunk does not have yet IZDatabaseInfo.SupportsArrayBindings property, AFAIK.
That's what our users want too. The current team did agree going beta(even if i'm really not ready). The latest paches which are required for this array bindings will be merged to trunk in a periode of a week or two.
I'll notify you if i have some more plains ready for the ArrayBindings.
Thank you again, it's a pleasure to work with you.
Michael
#157 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-18 21:28:32
Wow!
Could I use your latest version of TSQLDBZEOSConnectionProperties.Create() as reference for our main version?
Of course. Did compile with 7.1.3a:
{
"Engine": "Oracle",
"CreateTableTime": "52.82ms",
"NumberOfElements": 5000,
"InsertTime": "1.57s",
"InsertRate": 3166,
"InsertBatchTime": "45.52ms",
"InsertBatchRate": 109839,
"InsertTransactionTime": "944.02ms",
"InsertTransactionRate": 5296,
"InsertBatchTransactionTime": "46.67ms",
"InsertBatchTransactionRate": 107114,
"ReadOneByOneTime": "712.43ms",
"ReadOneByOneRate": 7018,
"ReadAllVirtualTime": "35.87ms",
"ReadAllVirtualRate": 139380,
"ReadAllDirectTime": "22.26ms",
"ReadAllDirectRate": 224618,
"ClientCloseTime": "27.29ms"
}
{
"Engine": "ZEOS Oracle",
"CreateTableTime": "49.39ms",
"NumberOfElements": 5000,
"InsertTime": "1.74s",
"InsertRate": 2867,
"InsertBatchTime": "436.94ms",
"InsertBatchRate": 11443,
"InsertTransactionTime": "858.89ms",
"InsertTransactionRate": 5821,
"InsertBatchTransactionTime": "413.38ms",
"InsertBatchTransactionRate": 12095,
"ReadOneByOneTime": "705.80ms",
"ReadOneByOneRate": 7084,
"ReadAllVirtualTime": "58.20ms",
"ReadAllVirtualRate": 85897,
"ReadAllDirectTime": "43.27ms",
"ReadAllDirectRate": 115540,
"ClientCloseTime": "24.65ms"
}And with current 7.2-alphatest from SVN /testing-7.2
Where im not sure about are these commented lines now:
{if Assigned(OnBatchInsert) then begin
// ZDBC: MultipleValuesInsertFirebird is buggy, MultipleValuesInsert slower
fBatchSendingAbilities := [];
OnBatchInsert := nil;
end;}didn't compare the FB results yet. It seems it works too, while comment says "buggy"..
Accordingly my array binding:
Yes it could be more optimal. It's just a minimal solution to get it running and wait for a feedback. Take care you've localized the arrays somewhere. Zeos only references by pointers so no IntlockIncrement is called AFAIK.
Agree with "mORMotish" refactoring, thanks for your feedback.
Arnaud TSQLDBStatementWithParams supports some BindArray() overloads, are they really used somewhere? Don't you think we should make a full override for the Bind___() functions? AFAICS do you convert all arrays back to RawUTF8-Strings...
#158 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-18 19:38:33
Triple post ):
Arnaud,
did play a while unit SynDBZEOS.pas
constructor TSQLDBZEOSConnectionProperties.Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
const
PCHARS: array[0..7] of PAnsiChar = (
'ORACLE','FREETDS_MSSQL','MSSQL','INTERBASE','FIREBIRD','MYSQL','SQLITE','POSTGRESQL');
TYPES: array[-1..high(PCHARS)] of TSQLDBDefinition = (
dDefault,dOracle,dMSSQL,dMSSQL,dFirebird,dFirebird,dMySQL,dSQLite,dPostgreSQL);
// expecting Postgresql + Sybase + ASA support in TSQLDBDefinition
begin
fServerName := aServerName;
if (fServerName<>'') and (PosEx(':',fServerName)=0) then
fServerName := fServerName+':';
if not IdemPChar(Pointer(aServerName),'ZDBC:') then
fServerName := 'zdbc:'+fServerName;
fURL := TZURL.Create(UTF8ToString(fServerName));
if fURL.Database='' then
fURL.Database := UTF8ToString(aDatabaseName);
if fURL.UserName='' then
fURL.UserName := UTF8ToString(aUserID);
if fURL.Password='' then
fURL.Password := UTF8ToString(aPassWord);
StringToUTF8(fURL.Protocol,fDBMSName);
fDBMS := TYPES[IdemPCharArray(pointer(fDBMSName),PCHARS)];
inherited Create(aServerName,aDatabaseName,aUserID,aPassWord);
fURL.Properties.Add('controls_cp=CP_UTF8');
fUseCache := false; // caching is to be disabled - not found stable enough
case fDBMS of
dSQLite: begin
{$ifdef ZEOS72UP}
fUseCache := true; // statement cache has been fixed in 7.2 branch
{$ELSE}
fSQLCreateField[ftInt64] := ' BIGINT'; // SQLite3 INTEGER = 32bit for ZDBC!
{$endif}
end;
dFirebird: begin
if (fURL.HostName='') and // Firebird embedded: create db file if needed
(fURL.Database<>'') and not FileExists(fURL.Database) then
fURL.Properties.Add('createNewDatabase='+UTF8ToString(
SQLCreateDatabase(StringToUTF8(fURL.Database))));
fURL.Properties.Add('codepage=UTF8');
fUseCache := true; // caching rocks with Firebird ZDBC provider :)
{if Assigned(OnBatchInsert) then begin
// ZDBC: MultipleValuesInsertFirebird is buggy, MultipleValuesInsert slower
fBatchSendingAbilities := [];
OnBatchInsert := nil;
end;}
end;
dOracle, dPostgreSQL, dMySQL: begin
fURL.Properties.Add('codepage=UTF8');
fUseCache := true;
end;
end;
fStatementParams := TStringList.Create;
case fDBMS of
dOracle:
{$IFDEF ZEOS72UP}
begin
fBatchSendingAbilities := [cCreate, cUpdate, cDelete];
OnBatchInsert := nil;
end;
{$ENDIF}
dSQLite: begin
{$ifdef ZEOS72UP} // new since 7.2up
// Bind double values instead of ISO formated DateTime-strings
//fStatementParams.Add('BindDoubleDateTimeValues=True');
{$endif}
end;
dMySQL: begin
// use mysql real-prepared api instead of string based once
// actually it's not realy faster.. just a hint:
// http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-problems.html
//fStatementParams.Add('preferprepared=True');
end;
end;
if fDBMS in [dOracle,dPostgreSQL,dMySQL] then begin
// let's set 1024KB / chunk for synopse or more?
// retrieving/submitting lob's in chunks. Default is 4096Bytes / Chunk
// it's depending to your local network speed e.g. bad WLAN or so
// for Firebird we always using the blob-segment size
fStatementParams.Add('chunk_size=1048576');
end;
if fDBMS in [dOracle,dPostgreSQL,dFireBird] then begin
{$ifdef ZEOS72UP} // new since 7.2up
// Always load the lobs? Or just on accessing them?
// if you allways copy the data by fetching the row than it doesn't make sence.
fStatementParams.Add('cachedlob=false'); //default = False
{$endif}
end;
end;procedure TSQLDBZEOSStatement.ExecutePrepared;
var p{$ifdef ZEOS72UP}, j{$ENDIF}: integer;
Props: TSQLDBZEOSConnectionProperties;
Log: ISynLog;
blob: IZBlob;
name: string;
{$ifdef ZEOS72UP}
NullArray: array of TBooleanDynArray;
Int64Array: array of TInt64DynArray;
DoubleArray: array of TDoubleDynArray;
CurDynArray: array of TCurrencyDynArray;
DateDynArray: array of TDateTimeDynArray;
UTF8DynArray: array of TRawUTF8DynArray;
BlobDynArray: array of TInterfaceDynArray;
{$ENDIF}
begin
Log := SynDBLog.Enter(Self);
with Log.Instance do
if sllSQL in Family.Level then
LogLines(sllSQL,pointer(SQLWithInlinedParams),self,'--');
if fStatement=nil then
raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() invalid call',[fStatementClassName]);
if fResultSet<>nil then
raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() miss a Reset',[fStatementClassName]);
// 1. bind parameters in fParams[] to fQuery.Params
{$ifdef ZEOS72UP}
if (fParamsArrayCount > 0) and fStatement.GetConnection.GetMetadata.GetDatabaseInfo.SupportsArrayBindings then
begin
SetLength(NullArray, fParamCount);
SetLength(Int64Array, fParamCount);
SetLength(DoubleArray, fParamCount);
SetLength(CurDynArray, fParamCount);
SetLength(Int64Array, fParamCount);
SetLength(DateDynArray, fParamCount);
SetLength(UTF8DynArray, fParamCount);
SetLength(BlobDynArray, fParamCount);
for p := 0 to fParamCount-1 do
begin
if fParams[p].VInt64<>fParamsArrayCount then
raise Exception.CreateFmt('%s.ExecutePrepared: %d parameter expected array count %d, got %d',
[fStatementClassName,p,fParamsArrayCount,fParams[p].VInt64]);
SetLength(NullArray[p], fParamsArrayCount);
with fParams[p] do
case VType of
ftNull:
begin
for J := 0 to fParamsArrayCount -1 do
begin
NullArray[p][j] := True;
UTF8DynArray[p][j] := '';
end;
fStatement.SetDataArray(p+1,UTF8DynArray[p],stString,vtUTF8String);
end;
ftInt64:
begin
SetLength(Int64Array[p], fParamsArrayCount);
for J := 0 to fParamsArrayCount -1 do
begin
NullArray[p][j] := Varray[j] = 'null';
if not NullArray[p][j] then
SetInt64(POinter(Varray[j]), Int64Array[p][j]);
end;
fStatement.SetDataArray(p+1,Int64Array[p], stLong);
end;
ftDouble:
begin
SetLength(DoubleArray[p], fParamsArrayCount);
for J := 0 to fParamsArrayCount -1 do
begin
NullArray[p][j] := Varray[j] = 'null';
if not NullArray[p][j] then
DoubleArray[p][j] := ZFastCode.RawToFloatDef(POinter(Varray[j]), '.', 0);
end;
fStatement.SetDataArray(p+1,DoubleArray[p],stDouble);
end;
ftCurrency:
begin
SetLength(CurDynArray[p], fParamsArrayCount);
for J := 0 to fParamsArrayCount -1 do
begin
NullArray[p][j] := Varray[j] = 'null';
if not NullArray[p][j] then
CurDynArray[p][j] := StrToCurrency(POinter(Varray[j]));
end;
fStatement.SetDataArray(p+1,CurDynArray[p],stCurrency);
end;
ftDate:
begin
SetLength(DateDynArray[p], fParamsArrayCount);
for J := 0 to fParamsArrayCount -1 do
begin
NullArray[p][j] := Varray[j] = 'null';
if not NullArray[p][j] then
DateDynArray[p][j] := Iso8601ToDateTime(Varray[j]);
end;
fStatement.SetDataArray(p+1,DateDynArray[p],stTimeStamp);
end;
ftUTF8:
begin
SetLength(UTF8DynArray[p], fParamsArrayCount);
for J := 0 to fParamsArrayCount -1 do
begin
NullArray[p][j] := Varray[j] = 'null';
if NullArray[p][j] then
UTF8DynArray[p][j] := ''
else
UnQuoteSQLString(pointer(VArray[j]), UTF8DynArray[p][j]);
end;
fStatement.SetDataArray(p+1,UTF8DynArray[p],stString,vtUTF8String);
end;
ftBlob:
begin
SetLength(BlobDynArray[p], fParamsArrayCount);
for J := 0 to fParamsArrayCount -1 do
begin
NullArray[p][j] := Varray[j] = 'null';
if NullArray[p][j] then
BlobDynArray[p][j] := nil
else
BlobDynArray[p][j] := TZAbstractBlob.CreateWithData(Pointer(VData),length(VData)
{$ifndef ZEOS72UP},fStatement.GetConnection{$endif});
end;
fStatement.SetDataArray(p+1,BlobDynArray[p], stBinaryStream);
end;
else
raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared: Invalid type on bound parameter #%d',
[fStatementClassName,p]);
end;
fStatement.SetNullArray(p+1,stBoolean, NullArray[p]);
end;
end
else
{$ENDIF}
for p := 1 to fParamCount do
with fParams[p-1] do
case VType of
ftNull: fStatement.SetNull(p,stUnknown);
ftInt64: fStatement.SetLong(p,VInt64);
ftDouble: fStatement.SetDouble(p,PDouble(@VInt64)^);
ftCurrency: {$ifdef ZEOS72UP}
fStatement.SetCurrency(p,PCurrency(@VInt64)^);
{$else}
fStatement.SetBigDecimal(p,PCurrency(@VInt64)^);
{$endif}
ftDate: fStatement.SetTimestamp(p,PDateTime(@VInt64)^);
ftUTF8: {$ifdef ZEOS72UP}
fStatement.SetUTF8String(p,VData);
{$else}
{$ifdef UNICODE} // ZWideString = SynUnicode in fact
fStatement.SetString(p,UTF8ToSynUnicode(VData));
{$else}
fStatement.SetString(p,VData); // see controls_cp=CP_UTF8
{$endif}
{$endif}
ftBlob: begin
blob := TZAbstractBlob.CreateWithData(Pointer(VData),length(VData)
{$ifndef ZEOS72UP},fStatement.GetConnection{$endif});
fStatement.SetBlob(p,stBinaryStream,blob);
end;
else
raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared: Invalid type on bound parameter #%d',
[fStatementClassName,p]);
end;
// 2. Execute query
if fExpectResults then begin
fCurrentRow := -1;
fResultSet := fStatement.ExecuteQueryPrepared;
if fResultSet=nil then
raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared(%s) returned nil',
[fStatementClassName,SQLWithInlinedParams]);
fResultInfo := fResultSet.GetMetadata;
Props := fConnection.Properties as TSQLDBZEOSConnectionProperties;
fColumnCount := 0;
fColumn.ReHash;
for p := 1 to fResultInfo.GetColumnCount do begin
name := fResultInfo.GetColumnLabel(p);
if name='' then
name := fResultInfo.GetColumnName(p);
PSQLDBColumnProperty(fColumn.AddAndMakeUniqueName(
// Delphi<2009: already UTF-8 encoded due to controls_cp=CP_UTF8
{$ifdef UNICODE}StringToUTF8{$endif}(name)))^.ColumnType :=
Props.TZSQLTypeToTSQLDBFieldType(fResultInfo.GetColumnType(p));
end;
end else
fStatement.ExecutePrepared;
// 3. handle out parameters -> TODO (fStatement is IZCallableStatement)
end;It might be a good idea if you lay my suggestion over your current WC.
I don't know if my changes are best way because i think having a own OnBatchInsert proc would be best way to maintain.
A thing i don't like in my patch was the unit binding to ZFastCode.pas -> DoubleArray[p][j] := ZFastCode.RawToFloatDef(POinter(Varray[j]), '.', 0); -> maybe you've got faster conversions running but in short i didn't find them ):
Results:
{
"Engine": "Oracle",
"CreateTableTime": "620.55ms",
"NumberOfElements": 5000,
"InsertTime": "1.57s",
"InsertRate": 3180,
"InsertBatchTime": "49.16ms",
"InsertBatchRate": 101698,
"InsertTransactionTime": "991.56ms",
"InsertTransactionRate": 5042,
"InsertBatchTransactionTime": "49.63ms",
"InsertBatchTransactionRate": 100741,
"ReadOneByOneTime": "686.56ms",
"ReadOneByOneRate": 7282,
"ReadAllVirtualTime": "35.50ms",
"ReadAllVirtualRate": 140845,
"ReadAllDirectTime": "23.34ms",
"ReadAllDirectRate": 214215,
"ClientCloseTime": "26.67ms"
}
{
"Engine": "ZEOS Oracle",
"CreateTableTime": "54.61ms",
"NumberOfElements": 5000,
"InsertTime": "1.46s",
"InsertRate": 3408,
"InsertBatchTime": "77.26ms",
"InsertBatchRate": 64713,
"InsertTransactionTime": "878.46ms",
"InsertTransactionRate": 5691,
"InsertBatchTransactionTime": "75.43ms",
"InsertBatchTransactionRate": 66282,
"ReadOneByOneTime": "677.51ms",
"ReadOneByOneRate": 7379,
"ReadAllVirtualTime": "37.43ms",
"ReadAllVirtualRate": 133557,
"ReadAllDirectTime": "27.32ms",
"ReadAllDirectRate": 182982,
"ClientCloseTime": "30.23ms"
}still not as good as yours, since i have to create new arrays, an process data twice 
But looks much better inbetween. Have no other component at hand to compare them
#159 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-18 12:56:23
Arnaud,
constructor TSQLDBZEOSConnectionProperties.Create should look like:
constructor TSQLDBZEOSConnectionProperties.Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
const
PCHARS: array[0..7] of PAnsiChar = (
'ORACLE','FREETDS_MSSQL','MSSQL','INTERBASE','FIREBIRD','MYSQL','SQLITE','POSTGRESQL');
TYPES: array[-1..high(PCHARS)] of TSQLDBDefinition = (
dDefault,dOracle,dMSSQL,dMSSQL,dFirebird,dFirebird,dMySQL,dSQLite,dPostgreSQL);
// expecting Postgresql + Sybase + ASA support in TSQLDBDefinition
begin
fServerName := aServerName;
if (fServerName<>'') and (PosEx(':',fServerName)=0) then
fServerName := fServerName+':';
if not IdemPChar(Pointer(aServerName),'ZDBC:') then
fServerName := 'zdbc:'+fServerName;
fURL := TZURL.Create(UTF8ToString(fServerName));
if fURL.Database='' then
fURL.Database := UTF8ToString(aDatabaseName);
if fURL.UserName='' then
fURL.UserName := UTF8ToString(aUserID);
if fURL.Password='' then
fURL.Password := UTF8ToString(aPassWord);
StringToUTF8(fURL.Protocol,fDBMSName);
fDBMS := TYPES[IdemPCharArray(pointer(fDBMSName),PCHARS)];
inherited Create(aServerName,aDatabaseName,aUserID,aPassWord);
fURL.Properties.Add('controls_cp=CP_UTF8');
fUseCache := false; // caching is to be disabled - not found stable enough
case fDBMS of
dSQLite: begin
{$ifdef ZEOS72UP}
fUseCache := true; // statement cache has been fixed in 7.2 branch
{$ELSE}
fSQLCreateField[ftInt64] := ' BIGINT'; // SQLite3 INTEGER = 32bit for ZDBC!
{$endif}
end;
dFirebird: begin
if (fURL.HostName='') and // Firebird embedded: create db file if needed
(fURL.Database<>'') and not FileExists(fURL.Database) then
fURL.Properties.Add('createNewDatabase='+UTF8ToString(
SQLCreateDatabase(StringToUTF8(fURL.Database))));
fURL.Properties.Add('codepage=UTF8');
fUseCache := true; // caching rocks with Firebird ZDBC provider :)
if Assigned(OnBatchInsert) then begin
// ZDBC: MultipleValuesInsertFirebird is buggy, MultipleValuesInsert slower
fBatchSendingAbilities := [];
OnBatchInsert := nil;
end;
end;
dOracle, dPostgreSQL, dMySQL: begin
fURL.Properties.Add('codepage=UTF8');
fUseCache := true;
end;
end;
fStatementParams := TStringList.Create;
case fDBMS of
dOracle:
{$IFDEF ZEOS72UP}
fOnBatchInsert := nil;
{$ENDIF}
dSQLite: begin
{$ifdef ZEOS72UP} // new since 7.2up
// Bind double values instead of ISO formated DateTime-strings
//fStatementParams.Add('BindDoubleDateTimeValues=True');
{$endif}
end;
dMySQL: begin
// use mysql real-prepared api instead of string based once
// actually it's not realy faster.. just a hint:
// http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-problems.html
//fStatementParams.Add('preferprepared=True');
end;
end;
if fDBMS in [dOracle,dPostgreSQL,dMySQL] then begin
// let's set 1024KB / chunk for synopse or more?
// retrieving/submitting lob's in chunks. Default is 4096Bytes / Chunk
// it's depending to your local network speed e.g. bad WLAN or so
// for Firebird we always using the blob-segment size
fStatementParams.Add('chunk_size=1048576');
end;
if fDBMS in [dOracle,dPostgreSQL,dFireBird] then begin
{$ifdef ZEOS72UP} // new since 7.2up
// Always load the lobs? Or just on accessing them?
// if you allways copy the data by fetching the row than it doesn't make sence.
fStatementParams.Add('cachedlob=false'); //default = False
{$endif}
end;
end;As you can see i set fOnBatchInsert := nil; for Oracle.
But than your tests do fail because the arrays aren't bound. May i ask for best practice? Any suggestions?
#160 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-10 12:02:09
I suppose IZDatabaseInfo is the right place to retrieve the SetDataArray() support.
Cool idea -> done R3249 \testing-7.2 (SVN)
Added
function SupportsArrayBindings: Boolean;
To the IZDataBaseInfo. Thank you.
I know than some providers (like MS SQL or FireBird) do have a "BULK" insert API. It could be used at in SynDBZeos / ZDBC level directly via a dedicated OnBatchInsert method.
Yes i know about. AFAIK should we use the bcp_xx functions for MSSQL and the "EXECUTE BLOCK" syntax for FireBird. But i didnt had time yet.
Major issue i have Zeos originally seems not to be made for max performance. So refactoring the whole code in a more optimal way needs time, as you know propably! And Zeos is just a hobby code i maintain. Have also family and a job...
Perhaps you may try some tuning at TSQLDBZEOSConnectionProperties.Create() level, e.g. re-introduce OnBatchInsert for FireBird, or set another parameters.
For instance, should the prefetch_count=10000 be removed for dOracle in this method?
Yes if we go Beta i'll invest some more time to "optimize" your framework for ZDBC, Arnaud. But there is always a bug-tracker and users who are doing things nobody did think about before.
For instance, should the prefetch_count=10000 be removed for dOracle in this method?
It won't be ahandled any more. So removing is not required..
#161 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-10 10:03:44
BTW, how do you identify if a provider does support array binding?
We have to know this in BATCH mode, to perform individual statements binding, or use SetDataArray().
acrually i have no way to do this. Thought you can resolve this? ))):
#162 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-10 10:02:04
Thanks AB, for testing.
Which svn branch should I use?
Note \trunk is a "testet" mirror of \testing-7.2 (crrently). \trunk always adapts to the latest development branch.
I currently do not have an Oracle server available any more for testing...
But I'll try to install one Express edition in a VM.
Sade, i was able to increase the reading performance in a very high rate too! Our single insertiation in non transactional mode seems to be a bit faster than yours. Weird i can't find the issue why our transactional insertiation is so much slower than yours.
Some new StatmentParameters for OCI:
'row_prefetch_size' is set to 128KB by default like yours.
'internal_buffer_size' is set to 128KB by default also, just like yours.
And finally i broke with the 'prefetch_count' parameter for OCI it was a nasty perfromance killer...
Some more little hints for your implementation:
SQLite:
It seems like FireDac is using synchronous=0 and locking_mode=EXCLUSIVE by default. Zeos doesn't use it by default but you can activate these modes also by adding
'synchronous=0' and 'locking_mode=EXCLUSIVE' to the ZURL properties. As you know this bumps the insertation performance in a very high rate! (;
I noticed you bench also against PostgresSQL. Did you ever test MySQL too?
I still can't get the benefit of {GENERIC_INDEX}, but for code obfuscation.
Inital idea: avoid up and down processing of the Index for columns and parameters. But performance changes are not realy noticeable since CPU's are very fast novadays.
Anyway it did make more sence for the TZCachedResutlset which you don't need for your framework, AFAIR.
As i wrote IF you have time to play with zdbc, please add the ArrayBinding stuff for Zeos+Oracle.
I'll notify you if you can use it for all plains we support.
Michael
#163 Re: mORMot 1 » Zoes7.2 upgrade » 2014-08-09 22:02:29
Arnaud, may i pik up that old thread again?
Did some modifications...
BTW, there are some optimizations in your code which are not worth it.
For instance, newer compilers have inlining feature so there is no need to write "PLongInt(NativeInt(Result) - 4)^" instead of "Length(Result)", since Length() function will be inlined by the compiler.
I noticed you use this code too inbetween. Friendly hint: FPC uses SizeInt for RefCount and Length. So the type is 4/8Byte in regard of compile target.
To fix it i introduced a LengthInt, LengthIntOffSet, RefCountInt and RefcountOffSet.
A weird issue with SQLite3 current implementation.
You do not check returned error code for TZSQLiteConnection.Close, and it returns in fact SQLITE_LOCKED (5).
So the DB file is not closed - and I cannot delete the file when running my tests several times.
You have some pending statements around, I'm afraid.
This issue is resolved, i hope. And one of the results: i broke with the "ForceNativeResultSet" option. Please omit this code.
Accordingly ArrayBindings: I think i can do that. I'll check your OCI implementation about best practice..
I've added ArrayBindings in all kinds but for OCI only(actually).
Just a test case i'm using as an example:
{$WARNINGS OFF} //implizit string conversion of...
procedure TZTestDbcOracleCase.TestArrayBindings;
const
hl_id_Index = {$IFDEF GENERIC_INDEX}0{$ELSE}1{$ENDIF};
stBooleanArray_Index = {$IFDEF GENERIC_INDEX}1{$ELSE}2{$ENDIF};
stByte_Index = {$IFDEF GENERIC_INDEX}2{$ELSE}3{$ENDIF};
stShort_Index = {$IFDEF GENERIC_INDEX}3{$ELSE}4{$ENDIF};
stInteger_Index = {$IFDEF GENERIC_INDEX}4{$ELSE}5{$ENDIF};
stLong_Index = {$IFDEF GENERIC_INDEX}5{$ELSE}6{$ENDIF};
stFloat_Index = {$IFDEF GENERIC_INDEX}6{$ELSE}7{$ENDIF};
stDouble_Index = {$IFDEF GENERIC_INDEX}7{$ELSE}8{$ENDIF};
stBigDecimal_Index = {$IFDEF GENERIC_INDEX}8{$ELSE}9{$ENDIF};
stString_Index = {$IFDEF GENERIC_INDEX}9{$ELSE}10{$ENDIF};
stUnicode_Index = {$IFDEF GENERIC_INDEX}10{$ELSE}11{$ENDIF};
stBytes_Index = {$IFDEF GENERIC_INDEX}11{$ELSE}12{$ENDIF};
stDate_Index = {$IFDEF GENERIC_INDEX}12{$ELSE}13{$ENDIF};
stTime_Index = {$IFDEF GENERIC_INDEX}13{$ELSE}14{$ENDIF};
stTimeStamp_Index = {$IFDEF GENERIC_INDEX}14{$ELSE}15{$ENDIF};
stGUID_Index = {$IFDEF GENERIC_INDEX}15{$ELSE}16{$ENDIF};
stAsciiStream_Index = {$IFDEF GENERIC_INDEX}16{$ELSE}17{$ENDIF};
stUnicodeStream_Index = {$IFDEF GENERIC_INDEX}17{$ELSE}18{$ENDIF};
stBinaryStream_Index = {$IFDEF GENERIC_INDEX}18{$ELSE}19{$ENDIF};
var
PStatement: IZPreparedStatement;
hl_idArray: TIntegerDynArray;
stBooleanArray: TBooleanDynArray;
stByteArray: TByteDynArray;
stShortArray: TShortIntDynArray;
stLongArray: TInt64DynArray;
stIntegerArray: TIntegerDynArray;
stFloatArray: TSingleDynArray;
stDoubleArray: TDoubleDynArray;
stBigDecimalArray: TExtendedDynArray;
stStringArray: TRawByteStringDynArray;
stUnicodeStringArray: TUnicodeStringDynArray;
stBytesArray: TBytesDynArray;
stDateArray: TDateTimeDynArray;
stTimeArray: TDateTimeDynArray;
stTimeStampArray: TDateTimeDynArray;
stGUIDArray: TGUIDDynArray;
stAsciiStreamArray: TZCharRecDynArray;
stUnicodeStreamArray: TUTF8StringDynArray;
stBinaryStreamArray: TInterfaceDynArray;
stBooleanNullArray: array of TBooleanDynArray;
stByteNullArray: array of TByteDynArray;
stShortNullArray: array of TShortIntDynArray;
stWordNullArray: array of TWordDynArray;
stSmallNullArray: array of TSmallIntDynArray;
stLongWordNullArray: array of TLongWordDynArray;
stIntegerNullArray: array of TIntegerDynArray;
stULongNullArray: array of TUInt64DynArray;
stLongNullArray: array of TInt64DynArray;
stFloatNullArray: array of TSingleDynArray;
stDoubleNullArray: array of TDoubleDynArray;
stCurrencyNullArray: array of TCurrencyDynArray;
stBigDecimalNullArray: array of TExtendedDynArray;
stStringNullArray: array of TRawByteStringDynArray;
stUnicodeStringNullArray: array of TUnicodeStringDynArray;
I, J: Integer;
procedure PrepareSomeData;
var I: Integer;
begin
SetLength(hl_idArray, 50);
SetLength(stBooleanArray, 50);
SetLength(stByteArray, 50);
SetLength(stShortArray, 50);
SetLength(stLongArray, 50);
SetLength(stIntegerArray, 50);
SetLength(stFloatArray, 50);
SetLength(stDoubleArray, 50);
SetLength(stBigDecimalArray, 50);
SetLength(stStringArray, 50);
SetLength(stUnicodeStringArray, 50);
SetLength(stBytesArray, 50);
SetLength(stDateArray, 50);
SetLength(stTimeArray, 50);
SetLength(stTimeStampArray, 50);
SetLength(stGUIDArray, 50);
SetLength(stAsciiStreamArray, 50);
SetLength(stUnicodeStreamArray, 50);
SetLength(stBinaryStreamArray, 50);
for i := 0 to 49 do
begin
hl_idArray[i] := I;
stBooleanArray[i] := Boolean(Random(1));
stByteArray[i] := Random(255);
stShortArray[i] := I;
stLongArray[i] := I;
stIntegerArray[i] := I;
stFloatArray[i] := RandomFloat(-5000, 5000);
stDoubleArray[i] := RandomFloat(-5000, 5000);
stBigDecimalArray[i] := RandomFloat(-5000, 5000);
stStringArray[i] := RandomStr(Random(99)+1);
stUnicodeStringArray[i] := RandomStr(Random(254+1));
stBytesArray[i] := RandomBts(50);
stDateArray[i] := Trunc(Now);
stTimeArray[i] := Frac(Now);
stTimeStampArray[i] := Now;
stGUIDArray[i] := RandomGUID;
stAsciiStreamArray[i].Len := Length(stStringArray[i]);
stAsciiStreamArray[i].P := Pointer(stStringArray[i]);
stAsciiStreamArray[i].CP := Connection.GetConSettings^.ClientCodePage^.CP; {safe we're passing ASCII7 only to the raws}
stUnicodeStreamArray[i] := RandomStr(MaxPerformanceLobSize);
stBinaryStreamArray[i] := TZAbstractBlob.Create;
(stBinaryStreamArray[i] as IZBlob).SetBytes(RandomBts(MaxPerformanceLobSize));
end;
end;
begin
Connection.PrepareStatement('delete from high_load').ExecutePrepared;
PStatement := Connection.PrepareStatement(
'insert into high_load(hl_id, stBoolean, stByte, stShort, stInteger, stLong, '+
'stFloat, stDouble, stBigDecimal, stString, stUnicodeString, stBytes,'+
'stDate, stTime, stTimestamp, stGUID, stAsciiStream, stUnicodeStream, '+
'stBinaryStream) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
CheckNotNull(PStatement);
PrepareSomeData;
PStatement.SetDataArray(hl_id_Index, hl_idArray, stInteger);
PStatement.SetDataArray(stBooleanArray_Index, stBooleanArray, stBoolean);
PStatement.SetDataArray(stByte_Index, stByteArray, stByte);
PStatement.SetDataArray(stShort_Index, stShortArray, stShort);
PStatement.SetDataArray(stInteger_Index, stIntegerArray, stInteger);
PStatement.SetDataArray(stLong_Index, stLongArray, stLong);
PStatement.SetDataArray(stFloat_Index, stFloatArray, stFloat);
PStatement.SetDataArray(stDouble_Index, stDoubleArray, stDouble);
PStatement.SetDataArray(stBigDecimal_Index, stBigDecimalArray, stBigDecimal);
PStatement.SetDataArray(stString_Index, stStringArray, stString, vtRawByteString);
PStatement.SetDataArray(stUnicode_Index, stUnicodeStringArray, stUnicodeString, vtUnicodeString);
PStatement.SetDataArray(stBytes_Index, stBytesArray, stBytes);
PStatement.SetDataArray(stDate_Index, stDateArray, stDate);
PStatement.SetDataArray(stTime_Index, stTimeArray, stTime);
PStatement.SetDataArray(stTimeStamp_Index, stTimeStampArray, stTimeStamp);
PStatement.SetDataArray(stGUID_Index, stGUIDArray, stGUID);
PStatement.SetDataArray(stAsciiStream_Index, stAsciiStreamArray, stString, vtCharRec);
PStatement.SetDataArray(stUnicodeStream_Index, stUnicodeStreamArray, stString, vtUTF8String);
PStatement.SetDataArray(stBinaryStream_Index, stBinaryStreamArray, stBinaryStream);
for i := FirstDbcIndex to 19{$IFDEF GENERIC_INDEX}-1{$ENDIF} do
case TZSQLType(Random(14)+1) of
stBoolean:
begin
SetLength(stBooleanNullArray, Length(stBooleanNullArray) +1);
SetLength(stBooleanNullArray[High(stBooleanNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stBooleanNullArray[High(stBooleanNullArray)][J] := False
else
stBooleanNullArray[High(stBooleanNullArray)][J] := Boolean(Random(1));
PStatement.SetNullArray(I, stBoolean, stBooleanNullArray[High(stBooleanNullArray)]);
end;
stByte:
begin
SetLength(stByteNullArray, Length(stByteNullArray)+1);
SetLength(stByteNullArray[High(stByteNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stByteNullArray[High(stByteNullArray)][J] := Ord(False)
else
stByteNullArray[High(stByteNullArray)][J] := Random(2);
PStatement.SetNullArray(I, stByte, stByteNullArray[High(stByteNullArray)]);
end;
stShort:
begin
SetLength(stShortNullArray, Length(stShortNullArray)+1);
SetLength(stShortNullArray[High(stShortNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stShortNullArray[High(stShortNullArray)][J] := 0
else
stShortNullArray[High(stShortNullArray)][J] := Random(2);
PStatement.SetNullArray(I, stShort, stShortNullArray[High(stShortNullArray)]);
end;
stWord:
begin
SetLength(stWordNullArray, Length(stWordNullArray)+1);
SetLength(stWordNullArray[High(stWordNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stWordNullArray[High(stWordNullArray)][j] := 0
else
stWordNullArray[High(stWordNullArray)][J] := Random(2);
PStatement.SetNullArray(I, stWord, stWordNullArray[High(stWordNullArray)]);
end;
stSmall:
begin
SetLength(stSmallNullArray, Length(stSmallNullArray)+1);
SetLength(stSmallNullArray[High(stSmallNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stSmallNullArray[High(stSmallNullArray)][J] := 0
else
stSmallNullArray[High(stSmallNullArray)][J] := -Random(2);
PStatement.SetNullArray(I, stSmall, stSmallNullArray[High(stSmallNullArray)]);
end;
stLongWord:
begin
SetLength(stLongWordNullArray, Length(stLongWordNullArray)+1);
SetLength(stLongWordNullArray[High(stLongWordNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stLongWordNullArray[High(stLongWordNullArray)][J] := 0
else
stLongWordNullArray[High(stLongWordNullArray)][J] := Random(2);
PStatement.SetNullArray(I, stLongWord, stLongWordNullArray[High(stLongWordNullArray)]);
end;
stInteger:
begin
SetLength(stIntegerNullArray, Length(stIntegerNullArray)+1);
SetLength(stIntegerNullArray[High(stIntegerNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stIntegerNullArray[High(stIntegerNullArray)][J] := 0
else
stIntegerNullArray[High(stIntegerNullArray)][J] := Random(2);
PStatement.SetNullArray(I, stInteger, stIntegerNullArray[High(stIntegerNullArray)]);
end;
stULong:
begin
SetLength(stULongNullArray, Length(stULongNullArray)+1);
SetLength(stULongNullArray[High(stULongNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stULongNullArray[High(stULongNullArray)][J] := 0
else
stULongNullArray[High(stULongNullArray)][J] := Random(2);
PStatement.SetNullArray(I, stULong, stULongNullArray[High(stULongNullArray)]);
end;
stLong:
begin
SetLength(stLongNullArray, Length(stLongNullArray) +1);
SetLength(stLongNullArray[High(stLongNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stLongNullArray[High(stLongNullArray)][J] := 0
else
stLongNullArray[High(stLongNullArray)][J] := Random(2)-1;
PStatement.SetNullArray(I, stLong, stLongNullArray[High(stLongNullArray)]);
end;
stFloat:
begin
SetLength(stFloatNullArray, Length(stFloatNullArray)+1);
SetLength(stFloatNullArray[High(stFloatNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stFloatNullArray[High(stFloatNullArray)][J] := 0
else
stFloatNullArray[High(stFloatNullArray)][J] := Random(2)-1;
PStatement.SetNullArray(I, stFloat, stFloatNullArray[High(stFloatNullArray)]);
end;
stDouble:
begin
SetLength(stDoubleNullArray, Length(stDoubleNullArray)+1);
SetLength(stDoubleNullArray[high(stDoubleNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stDoubleNullArray[high(stDoubleNullArray)][J] := 0
else
stDoubleNullArray[high(stDoubleNullArray)][J] := Random(2)-1;
PStatement.SetNullArray(I, stDouble, stDoubleNullArray[high(stDoubleNullArray)]);
end;
stCurrency:
begin
SetLength(stCurrencyNullArray, Length(stCurrencyNullArray)+1);
SetLength(stCurrencyNullArray[High(stCurrencyNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stCurrencyNullArray[High(stCurrencyNullArray)][J] := 0
else
stCurrencyNullArray[High(stCurrencyNullArray)][J] := Random(2)-1;
PStatement.SetNullArray(I, stCurrency, stCurrencyNullArray[High(stCurrencyNullArray)]);
end;
stBigDecimal:
begin
SetLength(stBigDecimalNullArray, Length(stBigDecimalNullArray)+1);
SetLength(stBigDecimalNullArray[High(stBigDecimalNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stBigDecimalNullArray[High(stBigDecimalNullArray)][J] := 0
else
stBigDecimalNullArray[High(stBigDecimalNullArray)][J] := Random(2)-1;
PStatement.SetNullArray(I, stBigDecimal, stBigDecimalNullArray[High(stBigDecimalNullArray)]);
end;
{stString:
begin
SetLength(stStringNullArray, Length(stStringNullArray)+1);
SetLength(stStringNullArray[High(stStringNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stStringNullArray[High(stStringNullArray)][J] := 'FALSE'
else
if Random(2) = 0 then
stStringNullArray[High(stStringNullArray)][J] := 'FALSE'
else
stStringNullArray[High(stStringNullArray)][J] := 'TRUE';
PStatement.SetNullArray(I, stString, stStringNullArray[High(stStringNullArray)], vtRawByteString);
end;}
stUnicodeString:
begin
SetLength(stUnicodeStringNullArray, Length(stUnicodeStringNullArray)+1);
SetLength(stUnicodeStringNullArray[High(stUnicodeStringNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stUnicodeStringNullArray[High(stUnicodeStringNullArray)][J] := 'FALSE'
else
if Random(2) = 0 then
stUnicodeStringNullArray[High(stUnicodeStringNullArray)][J] := 'FALSE'
else
stUnicodeStringNullArray[High(stUnicodeStringNullArray)][J] := 'TRUE';
PStatement.SetNullArray(I, stUnicodeString, stUnicodeStringNullArray[High(stUnicodeStringNullArray)], vtUnicodeString);
end;
else
begin
SetLength(stStringNullArray, Length(stStringNullArray)+1);
SetLength(stStringNullArray[High(stStringNullArray)], 50);
for J := 0 to 49 do
if I = FirstDbcIndex then
stStringNullArray[High(stStringNullArray)][J] := 'FALSE'
else
if Random(2) = 0 then
stStringNullArray[High(stStringNullArray)][J] := 'FALSE'
else
stStringNullArray[High(stStringNullArray)][J] := 'TRUE';
PStatement.SetNullArray(I, stString, stStringNullArray[High(stStringNullArray)], vtRawByteString);
end;
{stBytes:
stGUID:
stDate:
stTime:
stTimestamp:
stArray:
stDataSet:
stAsciiStream:
stUnicodeStream:
stBinaryStream:}
end;
PStatement.ExecuteUpdatePrepared;
//SetLength(stShortNullArray, 0);
end;
{$WARNINGS ON} //implizit string conversion of...As you can see you have to set the DataArray first. You also have the possibility to add a NullIndicatorArray. If no NullIndicatorArray is given, i start from the premisse no NULL values are available.
Arnaud if you have time, please implement this part for Oracle only. I'll replay if i have a generic implementation running.
Btw:
1. On my side the Oracle performance tests do work nice.
2. Btw. Debugging an issue with mORMot and you Performance test is a bit .... i give up, why the hell i do not get a notification if something went wrong?
3. Do you notice the {GENERIC_INDEX} define? It means using index 0..High instead of current 1..Length index behavior..
Cheers, Michael
#164 Re: mORMot 1 » A Mormot, ZEOS, MySQL and a Windows service ... » 2013-12-23 12:37:18
You are right. I don't know if the units are included somewhere else..
propose to add
{$IFNDEF UNIX}
{$IFDEF ENABLE_ADO}
ZDbcAdo,
{$ENDIF}
{$ENDIF}
{$IFDEF ENABLE_DBLIB}
ZDbcDbLib,
{$ENDIF}
{$IFDEF ENABLE_MYSQL}
ZDbcMySql,
{$ENDIF}
{$IFDEF ENABLE_POSTGRESQL}
ZDbcPostgreSql,
{$ENDIF}
{$IFDEF ENABLE_INTERBASE}
ZDbcInterbase6,
{$ENDIF}
{$IFDEF ENABLE_SQLITE}
ZDbcSqLite,
{$ENDIF}
{$IFDEF ENABLE_ORACLE}
ZDbcOracle,
{$ENDIF}
{$IFDEF ENABLE_ASA}
ZDbcASA,
{$ENDIF}
{$IFDEF ENABLE_POOLED}
ZDbcPooled,
{$ENDIF}to interface uses clause of SynDBZEOS.pas
Btw. you can patch your local Zeos.inc and comment these defines to exclude plains you don't need.
Question now is, in case of a Mormot using ZEOS to access a MySQL database, does it make sense to create the driver in TSQLDBZEOSConnectionProperties.URI(dMySQL, 'libmysql.dll')?
Of course! you can also spezify another location to load your lib's with ZDbc
#165 Re: mORMot 1 » Performance Test fails » 2013-12-23 01:55:39
This is changing: 'SampleRecord' to 'samplerecord'
PostgreSQL is lowercasing all identifiers by default while the most other providers using uppercased syntax for non-quoted identifiers. That's the reason why Zeos doesn't return any data.
Removing the IdentifierQuoting could resolve your issue but could lead to pain for others again. Let's wait for reply of Aranud. He wrote TWICE the table creation is not the proplem. Of course it isn't if selecting data from these tables happen in same way (i tryed to mention before).
#166 Re: mORMot 1 » Performance Test fails » 2013-12-23 00:28:01
Moctes,
could you please replace this function by:
procedure TSQLDBZEOSConnectionProperties.GetFields(
const aTableName: RawUTF8; var Fields: TSQLDBColumnDefineDynArray);
var meta: IZDatabaseMetadata;
res: IZResultSet;
n, i: integer;
Schema, TableName: RawUTF8;
sSchema, sTableName: string;
F: TSQLDBColumnDefine;
FA: TDynArray;
begin
if GetDatabaseMetadata(meta) then begin
SQLSplitTableName(aTablename, Schema,TableName);
sSchema := UTF8ToString(Schema);
sTableName := meta.GetIdentifierConvertor.Quote(UTF8ToString(TableName));
res := meta.GetColumns('',sSchema,sTableName,'');
FA.InitSpecific(TypeInfo(TSQLDBColumnDefineDynArray),Fields,djRawUTF8,@n,true);
FillChar(F,sizeof(F),0);
while res.Next do begin
F.ColumnName := res.GetUTF8String(4));
F.ColumnTypeNative := res.GetUTF8String(6));
F.ColumnType := TZSQLTypeToTSQLDBFieldType(TZSQLType(res.GetInt(5)));
F.ColumnLength := res.GetInt(7);
F.ColumnPrecision := res.GetInt(9);
FA.Add(F);
end;
if n>0 then begin
res := meta.GetIndexInfo('',sSchema,sTableName,false,true);
while res.Next do begin
F.ColumnName := res.GetUTF8String(9));
i := FA.Find(F);
if i>=0 then
Fields[i].ColumnIndexed := true;
end;
end;
SetLength(Fields,n);
exit;
end;
inherited; // if ZDBC metadata failed -> fall back to generic SQL-based code
end;as i alredy suggested here: http://synopse.info/forum/viewtopic.php?id=1508
IMO the uppercasing makes trouble. And we finally can use faster Getters for the strings...
#167 Re: mORMot 1 » Performance Test fails » 2013-12-22 15:23:05
AB, i'm NOT @home yet.
just two quick suggestions:
We did introduce the IdentifierQuoting. If you're decomposing the CREATE command, do you quote the Identifier "Sample"? If not than we've got the trouble maker IF your selecting "Sample".
So try using:
CREATE VIRTUAL TABLE "Sample" USING External(FirstName TEXT COLLATE SYSTEMNOCASE, LastName TEXT COLLATE SYSTEMNOCASE, Amount FLOAT, BirthDate TEXT COLLATE ISO8601, LastChange INTEGER, CreatedAt INTEGER)if it doesn't break your string decomposing.
Or just keep track you're selecting a lower OR uppercased table name. Otherwise, if mixed cases are used, Zeos quotes the strings and PG doen't retrieve any meta informations.
Hope it helps.
#168 Re: mORMot 1 » Performance Test fails » 2013-12-22 09:10:38
Hi moctes,
i'm affraid too. We did add Schema+Table-Name (and it's selected columns, of course) determination from PostgreSQL via OID. The prozess is cached too. It was a patch of a Zeos-user who was running into trouble with case-in/sensitive selects. Actually i can't see more changes. So i've tryed to reproduce your issue:
CREATE VIRTUAL TABLE Sample USING External(FirstName TEXT COLLATE SYSTEMNOCASE, LastName TEXT COLLATE SYSTEMNOCASE, Amount FLOAT, BirthDate TEXT COLLATE ISO8601, LastChange INTEGER, CreatedAt INTEGER)Fails on execution. The syntax is wrong or is it something related to mormot? Is that really the table you did play with in previous episodes?
Execute fails on the next function :
function TSQLRequest.Step: integer;
begin
if Request=0 then
raise ESQLite3Exception.Create(RequestDB,SQLITE_MISUSE);
result := sqlite3_check(RequestDB,sqlite3.step(Request)); <-- Here is generating some kind of error which in turn generates a Rollback
end;
Next surprice: Are you sure about this fail? IFAICS in mormot is this a direct sqlite call. I wouldn't start from the premise this is right. AB? Am i wrong?
OK i wanna support you too. We are short of time too. So give me an exact example to play with(a clear create table for me to work with ZDBC directly). It might be possible the latest patch makes trouble with virtual tables.
#169 Re: mORMot 1 » A Mormot, ZEOS, MySQL and a Windows service ... » 2013-12-18 11:30:13
Well, first of all my TService apps are running fine with Zeos. So we've to find out whats wrong.
After reading your first post again.. the dll doesn't seem to be your issue. There is something weired with the dbc sources on your side.
Some steps to find your issue:
1. which protocol-name are you using for? "mysql"? (adapts always to the latest protocol plaindriver)
2. are you sure ZDbcMySQL is included?
3. Set a break point to:
.......
initialization
MySQLDriver := TZMySQLDriver.Create;
DriverManager.RegisterDriver(MySQLDriver);Or add a MessageDlg there (The TService is minior debugable)
This registers the TZDriver where the IZDriverManager(ZDbcIntfs.pas) gets the connection from. IF this doesn't happen than track why it happens. Is this file missing? Something wrong in you path-settings?
Or did you disable {ENABLE_MYSQL} define in Zeos.inc.
4. I'll wait for reply
#170 Re: mORMot 1 » Zoes7.2 upgrade » 2013-12-07 23:02:25
BTW, there are some optimizations in your code which are not worth it.
For instance, newer compilers have inlining feature so there is no need to write "PLongInt(NativeInt(Result) - 4)^" instead of "Length(Result)", since Length() function will be inlined by the compiler.
A good habit is to check the generated asm code with Alt-F2 in debugging mode. You will find out all calls and hidden temporary variables (and hidden try..finally), which could have performance costs. Even if you do not know asm, you can easily find out the execution process, just by looking at the calls.
For instance, you sometimes write "PAnsiChar(aAnsiString)" whereas you should better write "Pointer(aAnsiString)" if you just want to read the string buffer, to avoid a RTL call.
Thank you for such hints! You're welcome! Sade, my ASM time is 10 years ago(just on learning my job). Never used again..
AB i could grant you SVN access. I don't expect you'll develop Zeos but for small fixes your help would be welcome.. I just need your SF.Net user-name.
I'm less convinced by the inclusion of FastCode to the project.
That might be true. But loads of pards did make faster conversions (mainly for the Unicode-IDE's) with number conversion in any kind. I also wrote a set of high performance functions which you can find in ZSysUtils.pas.
Also do i use some FastCode-based code for Conversions to UInt64 f.e. while delphi just use Format('%u') ... as replacement. Anyway parts of this code really performce better. Most parts like Move/FillChar is used with defines if the IDE's already use this code.
A weird issue with SQLite3 current implementation.
You do not check returned error code for TZSQLiteConnection.Close, and it returns in fact SQLITE_LOCKED (5).
So the DB file is not closed - and I cannot delete the file when running my tests several times.
You have some pending statements around, I'm afraid.
I'm affraid too. sqlite3_close was wrong decalred. I fixed this. Than did add the check on closing the connection.
Et voila: You're right.
Found and fixed a SQLite3 longstanding bug in the TZSQLiteStatment. An issue in Zeos alltime code.
But than it took a while to have a common solution for ForceNativeResultSet (as you reported before) and without Statement.Close. I simply use a weak pointer ref to LastResultSet and a way back to inform about ResultSet-destruction.
Patch done: R2995 /testing-7.2
Confirmed?
#171 Re: mORMot 1 » Zoes7.2 upgrade » 2013-12-06 07:45:09
It is clear to me that the fact that your ZDBC layer is not based on DB.pas and its TDateSet/TField/TParam types is a HUGE BONUS.
Sure. But this will take some more time until i'm ready.
And with Delphi XE4 certainly (those tests above were with Delphi 7).
The UnicodeIDE's do also perform much better with my tests. Where i'm not sure: Is the GetUTF8String/SetUTF8String best option for these IDE's? I hope no mem-moves do happen between your RawUTF8 and the UTF8String. Otherwise we should try my suggestion with the records.
When do you expect to publish a stable release?
No idea. The current code is AS stable as the official releases are. I wouldn't have any objections BUT:
Going beta means only bugfixes are allowed. A feature freeze mill happen than. So all basic implementations, i wanna do need a stable state than.
Which means i need finilize my TZFields, start the TZParam, write a real-prepared stmt for DBLIB/FreeTDS and i need to check plains like Oracle if we could do some more accordingly performance (My impression for ZDBC: topspeed reached for MySQL, PostgresSQL, SQLite, FB, ADO as plains. The current TDataSet implementation uses also a loads of more tricks with the lobs and the performance did grow x3(cachedlobs=true)/x20(cachedlobs=false) with !OUR! Test-table "high_load").
It is a pleasure to let Open Source projects contribute and play together!
Yes it is! Thank you too, Arnaud. Your patches are always welcome. Hope some more users will join Zeos. And i really hope to get some more help (most people do only report if something is wrong ).
Accordingly ArrayBindings: I think i can do that. I'll check your OCI implementation about best practice..
(:
#172 Re: mORMot 1 » Zoes7.2 upgrade » 2013-12-04 19:53:23
Hossa!! 
That looks pretty good now.
I did commit your additional fixes. Yes of coures our current tests do NOT catch all possible issues. But they mostly grow equal to the tickets we get. I added a loads of tests inbetween.
I also fixed the SQLite 'INTEGER' problem after reading this: http://www.sqlite.org/autoinc.html
So you can disable the BIGINT improvement with the 72UP define.
Regarding the newly FieldTypes. Yes Zeos pre 7.2 did also only use a rare set of types. But i got some more Bugreports for the newer IDE's + ADO/Sybase/MySQL. Main reason they all support Unsigned integer types up to UInt64. And one of the TDataSet-performance killer is TField and TParam. So i'm working out a better implementation which fits better to Zeos72 up.
I'm not sure if we can do more... As i said we are not ready with the logging patches. And some plains like oracle need a review. For DBlib+FreeTDS i plan a read-prepared stmt which currently is the remaining driver which doesn't support such one.
I did check SynSQLITE3.pas
procedure TSQLRequest.FieldsToJSON(WR: TJSONWriter; DoNotFletchBlobs: boolean);
var i: integer;
begin
if Request=0 then
raise ESQLite3Exception.Create(RequestDB,SQLITE_MISUSE);
if WR.Expand then
WR.Add('{');
for i := 0 to FieldCount-1 do begin
if WR.Expand then
WR.AddString(WR.ColNames[i]); // '"'+ColNames[]+'":'
case sqlite3.column_type(Request,i) of // fast evaluation: type may vary
SQLITE_BLOB:
if DoNotFletchBlobs then
WR.AddShort('null') else
WR.WrBase64(sqlite3.column_blob(Request,i),
sqlite3.column_bytes(Request,i),true); // withMagic=true
SQLITE_NULL:
WR.AddNoJSONEscape(PAnsiChar('null'),4); // returned also for ""
SQLITE_INTEGER:
WR.Add(sqlite3.column_int64(Request,i));
SQLITE_FLOAT:
WR.Add(sqlite3.column_double(Request,i));
SQLITE_TEXT: begin
WR.Add('"');
WR.AddJSONEscape(sqlite3.column_text(Request,i),0);
WR.Add('"');
end;
end; // case ColTypes[]
WR.Add(',');
end;
WR.CancelLastComma; // cancel last ','
if WR.Expand then
WR.Add('}');
end;Huge sureprice: You do NOT make differences with ftDateTime? Which format are you using for?
Since 7.2 i added Connection options for reading/writing datetime-formats different from ISO-format. Reason was PostgresSQL and MSSQL.
See: TZAbstractConnection.SetDateTimeFormatProperties(DetermineFromInfo: Boolean = True);
Hum you said having a own FieldsToJSON function could perform better. Is there a generic way for an override? I don't see this declaration here.. OR where exactly should it be placed than?
Again: I don't expect to top your speed but having more Zeos-Users which hopefully help me a bit would be nice...
#173 Re: mORMot 1 » Zoes7.2 upgrade » 2013-12-03 22:06:56
Oki.
First of all: is there a csv or svn repo to be up to date?
Did you study the 7.2 implementations i made?
Just to avoid loosing my time if you know better ways to implement the FetchAllToJSON override.
And i need a little advice:
Which files and functions/procedures need to be reviewed? Hint: All patches i propose aren't testet, just a theoretical suggestion (actually) until i'll find the time to learn a bit more about the morrmots (: . As you know Mark(my second pair of eyes, coder, project-manager) and me are the "main"-maintainers of Zeos. And Zeos is ... work enough, trust me.
Don't think i wanna bet here. But i'm sure there are other users too which eventually do try something equal. So having a huge bandwidth of support wouldn't be a problem for me.
Let's go if you're ready (:
#174 Re: mORMot 1 » Zoes7.2 upgrade » 2013-12-03 21:40:21
Edit:
did commit my remaining ideas. R2979 \testing-7.2
Hmpf hope it helps? I've got some more fetching speed with the Ansi-IDE's and a bit more with the Unicode-IDE's.
Propose you patch SynDBZEOS as suggesest and refresh ZDBC. Otherwise ... I've to pass, Arnaud. I did never expect to top your performance but such differences.. ? Nope i didn't expect it...
Hands up!!!!!
#175 Re: mORMot 1 » Zoes7.2 upgrade » 2013-12-03 20:37:10
Yes!!!
Nice!
Accordingly your performance. Man is it possible? Hum have to check what i finally can do. Some remaining ideas i have:
Instead of using column_bytes i'll check how our from FastCode-project ported StrLen() is working.
And finally instead of current PlainDriver-Call i could call the binded SQLite3 function directly. Otherwise i've to pass for fetching speed(except see below).
According Insertiation speed:
As i wrote Mark currently is suppressing value logging which slows down the current performance. And after my suggestion with SetUTF8String i did the follwing patch:
ZVarinat.pas
function TZClientVariantManager.GetAsCharRec(var Value: TZVariant; const CodePage: Word): TZCharRec;
begin
Result.CP := CodePage;
case Value.VType of
vtNull:
begin
Result.P := nil;
Result.Len := 0;
end;
vtCharRec:
if ZCompatibleCodePages(CodePage, Value.VCharRec.CP) then
Result := Value.VCharRec
else
if ZCompatibleCodePages(CodePage, zCP_UTF16) then
begin
Value.VUnicodeString := Convert(Value, vtUnicodeString).VUnicodeString;
Result.P := PWideChar(Value.VUnicodeString);
Result.Len := Length(Value.VUnicodeString);
end
else
begin
Value.VRawByteString := GetAsRawByteString(Value, CodePage);
Result.P := PAnsiChar(Value.VRawByteString);
Result.Len := Length(Value.VRawByteString);
end;
vtUTF8String:
if CodePage = zCP_UTF8 then
begin
Result.P := PAnsiChar(Value.VUTF8String);
Result.Len := Length(Value.VUTF8String);
end
else
begin
Value.VRawByteString := GetAsRawByteString(Value, CodePage);
Result.P := PAnsiChar(Value.VRawByteString);
Result.Len := Length(Value.VRawByteString);
end;
else
begin
Value.VRawByteString := GetAsRawByteString(Value, CodePage);
Result.P := PAnsiChar(Value.VRawByteString);
Result.Len := Length(Value.VRawByteString);
end;
end;
end;AB is it necceassary to localize your RawUTF8 values while binding them? OR can we point to them?
Then you could use this:
procedure TSQLDBZEOSStatement.ExecutePrepared;
var i: integer;
Props: TSQLDBZEOSConnectionProperties;
Log: ISynLog;
blob: IZBlob;
name: string;
{$IFDEF ZEOS72UP}
CharRec: TZCharRec;
{$ENDIF}
begin
Log := SynDBLog.Enter(Self);
with Log.Instance do
if sllSQL in Family.Level then
LogLines(sllSQL,pointer(SQLWithInlinedParams),self,'--');
if fStatement=nil then
raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() invalid call',[ClassName]);
if fResultSet<>nil then
raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() miss a Reset',[ClassName]);
// 1. bind parameters in fParams[] to fQuery.Params
for i := 1 to fParamCount do
with fParams[i-1] do
case VType of
ftNull: fStatement.SetNull(i,stUnknown);
ftInt64: fStatement.SetLong(i,VInt64);
ftDouble: fStatement.SetDouble(i,PDouble(@VInt64)^);
ftCurrency: fStatement.SetDouble(i,PCurrency(@VInt64)^);
ftDate: fStatement.SetTimestamp(i,PDateTime(@VInt64)^);
ftUTF8: {$IFDEF ZEOS72UP}
begin
CharRec.Len := PLongInt(NativeInt(VData) - 4)^; //length of string
CharRec.CP := 65001; //utf8
CharRec.P := PAnsiChar(VData);
fStatement.SetCharRec(i,CharRec);
end;
//fStatement.SetUTF8String(i,VData);
{$ELSE}
{$ifdef UNICODE} // ZWideString = SynUnicode in fact
fStatement.SetString(i,UTF8ToSynUnicode(VData));
{$else}
fStatement.SetString(i,VData); // thanks to controls_cp=CP_UTF8
{$endif}
{$ENDIF}
......
end;
function TSQLDBZEOSStatement.ColumnUTF8(Col: Integer): RawUTF8;
{$IFDEF ZEOS72UP}
var AnsiRec: TZAnsiRec;
{$ENDIF}
begin
if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnUTF8(%d)',[Col]);
{$IFDEF ZEOS72UP}
begin
AnsiRec := fResultSet.GetAnsiRec(Col+1);
SetString(Result, AnsiRec.P, AnsiRec.Len);
end;
//result := fResultSet.GetUTF8String(Col+1); // GetRawByteString returns also UTF8 if connection charset is UTF8
{$ELSE}
{$ifdef UNICODE}
StringToUTF8(fResultSet.GetString(Col+1),result);
{$else}
result := fResultSet.GetString(Col+1); // thanks to controls_cp=CP_UTF8
{$endif}
end;Which than avoids possible string conversions of the IDE's.
Anyway i don't think i'm able to get such a top-speed running. How did you manage that? Curious. Our users are pretty happy inbetween. I'm sure we won't never reach your speed since it fits perfectly for your needs. But such a difference i didn't expect.....
#176 Re: mORMot 1 » A Mormot, ZEOS, MySQL and a Windows service ... » 2013-12-03 18:25:20
Well Zeos !actually! rasies always the same exception if the lib couldn't be loaded. It might be an idea to check
if LibExists then
raise Exception.Create('Incompatible library found! Check compile-target.. blabla")
else
raise KnownException.
Or something like this. 99% of the users have the same problem.
To your isse: Compile-Target of Service and App are equal? Dll is located in a path which is included in you environment-variables or in same directore of you executables?
You could also connect having both version located on your computer.
Just move the !32Bit! libmysql to !\Windows\SYSWOW64!
and the !64Bit! libmysql to !\Windows\System32!.
I know this is a bit confusing -> Microsoft.
Than keep track there are NO other libs available and -> have fun.
#177 Re: mORMot 1 » Zoes7.2 upgrade » 2013-12-03 17:46:50
Oh i was starting from the premisse you're updating from SVN.
Well in addition to the TZSQLiteCAPIPreparedStatement.ExecuteQueryPrepared: IZResultSet; patch you'll need to patch too.
See:
procedure TZSQLiteResultSet.FreeHandle;
var
ErrorCode: Integer;
begin
if FFreeHandle then
begin
if Assigned(FStmtHandle) then
ErrorCode := FPlainDriver.Finalize(FStmtHandle)
else
ErrorCode := SQLITE_OK;
FStmtHandle := nil;
CheckSQLiteError(FPlainDriver, FStmtHandle, ErrorCode, nil,
lcOther, 'FINALIZE SQLite VM', ConSettings);
end
else
begin
if FStmtHandle <> nil then
begin
ErrorCode := FPlainDriver.reset(FStmtHandle);
CheckSQLiteError(FPlainDriver, FStmtHandle, ErrorCode, nil, lcBindPrepStmt, 'Reset Prepared Stmt', ConSettings);
FStmtHandle := nil;
end;
FErrorCode := SQLITE_DONE;
end;
end;Sorry.
Added two testcases for your report:
procedure TZTestDbcSQLiteCase.TestReuseResultsetNative;
var
PreparedStatement: IZPreparedStatement;
ResultSet: IZResultSet;
Info: TStrings;
begin
Info := TStringList.Create;
Info.Add('ForceNativeResultSet=True');
PreparedStatement := Connection.PrepareStatementWithParams(
'SELECT * FROM PEOPLE WHERE p_id > ?', Info);
try
PreparedStatement.SetInt(1, 0); //expecting 5 rows
ResultSet := PreparedStatement.ExecuteQueryPrepared;
CheckEquals(True, ResultSet.Next); //fetch first row.
CheckEquals(1, ResultSet.GetInt(1));
CheckEquals(True, ResultSet.Next); //fetch second row.
CheckEquals(True, ResultSet.Next); //fetch third row.
CheckEquals(True, ResultSet.Next); //fetch fourth row.
{ignore last row}
PreparedStatement.SetInt(1, 1); //expecting 4 rows
ResultSet := PreparedStatement.ExecuteQueryPrepared;
CheckEquals(True, ResultSet.Next); //fetch first row.
CheckEquals(2, ResultSet.GetInt(1));
CheckEquals(True, ResultSet.Next); //fetch second row.
CheckEquals(True, ResultSet.Next); //fetch third row.
{ignore last row}
PreparedStatement.SetInt(1, 2); //expecting 3 rows
ResultSet := PreparedStatement.ExecuteQueryPrepared;
CheckEquals(True, ResultSet.Next); //fetch first row.
CheckEquals(3, ResultSet.GetInt(1));
while ResultSet.Next do; //full fetch automatically resets handle
PreparedStatement.SetInt(1, 3); //expecting 2 rows
ResultSet := PreparedStatement.ExecuteQueryPrepared;
CheckEquals(True, ResultSet.Next); //fetch first row.
CheckEquals(4, ResultSet.GetInt(1));
while ResultSet.Next do; //full fetch automatically resets handle
PreparedStatement.SetInt(1, 10); //expecting !0! rows e.g AB(synopse) -> no metadata???
ResultSet := PreparedStatement.ExecuteQueryPrepared;
CheckNotNull(ResultSet);
CheckEquals(False, ResultSet.Next); //fetch first row.
finally
Info.Free;
if Assigned(ResultSet) then
ResultSet.Close;
PreparedStatement.Close;
end;
end;
procedure TZTestDbcSQLiteCase.TestReuseResultsetCached;
var
PreparedStatement: IZPreparedStatement;
ResultSet: IZResultSet;
Info: TStrings;
begin
Info := TStringList.Create;
PreparedStatement := Connection.PrepareStatement(
'SELECT * FROM PEOPLE WHERE p_id > ?');
try
PreparedStatement.SetInt(1, 0); //expecting 5 rows
ResultSet := PreparedStatement.ExecuteQueryPrepared;
CheckEquals(True, ResultSet.Next); //fetch first row.
CheckEquals(1, ResultSet.GetInt(1));
CheckEquals(True, ResultSet.Next); //fetch second row.
CheckEquals(True, ResultSet.Next); //fetch third row.
CheckEquals(True, ResultSet.Next); //fetch fourth row.
{ignore last row}
PreparedStatement.SetInt(1, 1); //expecting 4 rows
ResultSet := PreparedStatement.ExecuteQueryPrepared;
CheckEquals(True, ResultSet.Next); //fetch first row.
CheckEquals(2, ResultSet.GetInt(1));
CheckEquals(True, ResultSet.Next); //fetch second row.
CheckEquals(True, ResultSet.Next); //fetch third row.
{ignore last row}
PreparedStatement.SetInt(1, 2); //expecting 3 rows
ResultSet := PreparedStatement.ExecuteQueryPrepared;
CheckEquals(True, ResultSet.Next); //fetch first row.
CheckEquals(3, ResultSet.GetInt(1));
while ResultSet.Next do; //full fetch automatically resets handle
PreparedStatement.SetInt(1, 3); //expecting 2 rows
ResultSet := PreparedStatement.ExecuteQueryPrepared;
CheckEquals(True, ResultSet.Next); //fetch first row.
CheckEquals(4, ResultSet.GetInt(1));
while ResultSet.Next do; //full fetch automatically resets handle
finally
Info.Free;
if Assigned(ResultSet) then
ResultSet.Close;
PreparedStatement.Close;
end;
end;On my side the tests do work well too. If i'm doing something different to your implementation: please change the sequence of my tests to get a reproducable testcase on my side too.
#178 Re: mORMot 1 » A Mormot, ZEOS, MySQL and a Windows service ... » 2013-12-03 14:48:39
Just an idea before asking me in Zeos-Forum:
Compile-Target is equal with the dll?
32Bit-App only works with 32Bit library.
64Bit-App only works witha a 64Bit lib.
Keep track you use the right one!
#179 Re: mORMot 1 » Zoes7.2 upgrade » 2013-12-03 14:34:03
Current 7.2 implementation fails to connect, whereas the 7.x trunk has no problem.
In TZOracleConnection.Open(), ConSettings^.Database='' and ConSettings^.User='' whereas it was filled as expected in 7.1 branch.
It is a show-stopper to use the 7.2 branch.
?? In 7.1 Branch these fields do not extist in the ConSettings-record. And i run daily tests with oracle too. So actually i can't confirm that. We do not have more fails or exceptions in our tests than in 7.2. Everything is fine on my side and the project manager didn't recognize any behavior changes too.
IF these fields are empty than there must be something wrong in:
ZDbcConnection.pas
constructor TZAbstractConnection.Create(const ZUrl: TZURL);
........
ConSettings^.Database := ConSettings^.ConvFuncs.ZStringToRaw(FURL.Database, ConSettings^.CTRL_CP, ConSettings^.ClientCodePage^.CP);
ConSettings^.User := ConSettings^.ConvFuncs.ZStringToRaw(FURL.UserName, ConSettings^.CTRL_CP, ConSettings^.ClientCodePage^.CP);
..As long as i can't reproduce it i can't fix it. Which compiler did you use for?
I also tested the current trunk version, but it is not consistent with the 7.1 branch: ZEOS72UP is defined, whereas some method signatures are inconsistent.
So I felt back into the latest 7.1.2 stable release, which compiles and run fine with Oracle.
That's right. trunk is a tested mirror of \testing-7.2 and will be merged weekly if 7.2 patches are older than min. a week.
But it fails in step 4 (Reset) when the statement returns some data (i.e. INSERTs are OK, but SELECT fails to re-open).
I guess there is something wrong with the re-prepare order, linked to result columns.
As i wrote my previous post i allready commited a patch for that. You're right. The reset did only happen if the fetch was done until EOF. Than the resultet did execute the reset.
i just patched the resultset and:
function TZSQLiteCAPIPreparedStatement.ExecuteQueryPrepared: IZResultSet;
begin
if Not Prepared then
Prepare;
{ after reading the last row we reset the statment. So we don't need this here }
try
if LastResultSet <> nil then
LastResultSet.Close; // reset stmt
LastResultSet := nil;
BindInParameters;
FErrorCode := FPlainDriver.Step(FStmtHandle);
CheckSQLiteError(FPlainDriver, FStmtHandle, FErrorCode, nil, lcOther,
ConSettings^.ConvFuncs.ZStringToRaw(SCanNotRetrieveResultsetData, ConSettings^.CTRL_CP, ConSettings^.ClientCodePage^.CP),
ConSettings);
if ( FErrorCode = SQLITE_ROW ) or ( FErrorCode = SQLITE_DONE) then
LastResultSet := CreateResultSet(FStmtHandle, FErrorCode);
Result := LastResultSet;
inherited ExecuteQueryPrepared;
except
raise;
end;
end;So i call resultset.close which execs the reset. Hope this helps for option fUseCache. Just update from SVN
Btw. i also cache the stmts for the Cached-Resultsets and do reuse them..
Oracle makes me wondering.. SQLite need a review for your reported issues (no resultset) too.
#180 Re: mORMot 1 » Zoes7.2 upgrade » 2013-12-01 14:06:52
It is stated in our SynDBZeos.pas unit:
...
Now, if I unable statement cache (fUseCache := true), I encounter the same error as before, i.e. "SQL error: library routine called out of sequence":
Grumbl. Nope i don't think this patch right or i am wrong.
As far as i understand SQLite:
Prepare stmt.
Bind Parameters.
Execute prepared.
loop
Reuse requires a reset (not finalze like the old code was going). Am i wrong?
So reset the stmt.
Execute Prepared
until Unprepare.
May i ask: Do you make a full fetch until EOF or do you break earlier?
So in preparetion of this case: Seond Patch commited. R2974
Did apply you MetaData-Patch.
It sounds like if SQlite3 does not allow re-use of prepared statements, whereas other provider do (e.g. Oracle, Firebird....).
Setting ForceNativeResultSet=True does not fix the issue.
Is there any needed additional parameter?
Nope not AFAIK.
Maybe my fix makes it possible?
Nice!
Yep but i'm not happy until you get you fUseCache running. And i did commit a GetUTF8String upgrade a half hour ago. Hope it will speed a little bit better. (I just do prevent a locale string before assigning the UTF8String result so the result would be minimal).
If my fix doesn't help, could you attach a little code sequence to reproduce the bugga?
Michael
#181 Re: mORMot 1 » Zoes7.2 upgrade » 2013-11-30 21:32:20
For GetTableNames() / GetFields() I suspect it won't make any difference between using GetUTF8String() or passing via an UnicodeString temporary variable, right?
Except performance, it wouldn't. GetUTF8String returns a true utf8-encoded, #0 terminated string in all cases. It doesn't matter which client encoding, MetaInformation-(Virtual)ResultSets, CachedResultSets or NativeResultSets.
You pointed me to "Zeos is doing to much String-Conversions" in the past. This case is prevented where ever it's possible. Like i wrote in http://synopse.info/forum/viewtopic.php?id=1484 we're not ready. Some small jobs like suppressing the log's etc. And a bit more powerfull GetUTF8String just for you can be done. Btw. you wrote your access is much faster. Do you have a Posgre-Wrapper?
It will make it MUCH faster.
But, some month ago, when I put fUseCache := true for Sqlite3 with Zeos, it just broke - whereas there was no problem with re-using prepared statements with other providers.
Perhaps it is fixed in newer versions.
Lorbs, i think i know the reason for the little bugga. Your fist tests have been made with 7.0 where no Real-Prepared stmt was available for SQLite. The deprecated emulated did a Reset if the fetch or update was complete. I quickly wrote the real-prepared one(3hours?), which doesn't reset the handles execpt on stmt.Close/Destroy/Uprepare and binds the values instead of building a huge string. But if it helps some tests on your side will show it.
Now you've a define for 7.2 to keep track about behavior changes.
Btw. actually i'm fixing floting and integer types for the most RDBM's acordingly stByte/stCurrency and so on. Just have a look to the numeric and decimal declaration: http://www.postgresql.org/docs/8.3/stat … meric.html
It might be possible your Double/Currency types came out of range than... Postgre-Word -> NO LIMITS?
Mark is still busy with the logging things. I'll need some time for finalizing the types and spezially for you the fastest Zeos GetUTF8Gtring. Than i would be interested to see the performance changes with your tests. I only compare Zeos 7.1down agains 7.2up and for DBC and TDataSet i was able to do a lot. Hope it fits for your needs...
Edit:
I read about the DDL BatchUpdates. Have no clue how to realize SLT yet. Could you give me hint what you do expect?
#182 Re: mORMot 1 » Zoes7.2 upgrade » 2013-11-30 20:13:54
You are writing that "I did decide to deal with Length's everywhere to avoid strlen call's where ever possible".
Are you not making a confusion between StrLen() and Length()? StrLen is a O(n) algorithm, whereas Length() is O(1), since it just return the length of the string, as stored before the data.
But I included your suggestions anyway.
What i mean is: I'm trying to localize the String-Length everywhere and use Pointers to the Strings just to prevent a Length() or StrLen() execution. And i try to prevent localized strings until a user/framework(like yours) needs a "string" which kind ever.
So it might be possible the GetAnsiRec(Col: Integer): TZAnsiRec; getter could fit best to your framework IF the IDE is doing again conversion or moves the data. In this case use something like this:
var: AnsiRec: TZAnsiRec;
begin
AnsiRec := ResultSet.GetAnsiRec(ColumnIndex);
SetString(YourRawUTF8, AnsiRec.P, AnsiRec.Len);
end;But his is working only if you truely have utf8-encoding which isn't the case for mssql. But the GetUTF8String(ColumnIndex: Integer): System.UTF8String; returns always a UTF8-encoded string. Nasty for MSSQL since there is no native way to determine a VARCHAR vs. NVARCHAR column (i did test freeTDS only). The plain is returning always the same type for both. Result is encoding checking until either UTF8 or Ansi was detected. Maybe someone else know a better way.
Back to yours.
Arnaud i'm not sure about the "fUseCache := true;" option. Can you tell me more about purpose? Just to prevent it was wrong what i did.
Accordingly the TZNativeLibraryLoader patch.
Current Zeos code:
function TZNativeLibraryLoader.ZLoadLibrary(Location: String): Boolean;
var newpath, temp: String; // AB modif
begin
if FLoaded then
Self.FreeNativeLibrary;
temp := ''; //init for FPC
FLoaded := False;
Result := False;
newpath := ExtractFilePath(Location);
// AB modif BEGIN
try
if newpath <> '' then begin
temp := GetCurrentDir;
SetCurrentDir(newpath);
end;
// AB modif END
{$IFDEF UNIX}
{$IFDEF FPC}
FHandle := LoadLibrary(PAnsiChar(Location));
{$ELSE}
FHandle := HMODULE(dlopen(PAnsiChar(Location), RTLD_GLOBAL));
{$ENDIF}
{$ELSE}
FHandle := LoadLibrary(PChar(Location));
{$ENDIF}
// AB modif BEGIN
finally
if temp<>'' then
SetCurrentDir(temp);
end;
// AB modif END
if (FHandle <> INVALID_HANDLE_VALUE) and (FHandle <> 0) then
begin
FLoaded := True;
FCurrentLocation := Location;
Result := True;
end;
end;I allready applied your previous patch and actuall i don't see real differences except your newly proposal doesn't work for FPC. Am i right or do i overlook something?
Accordingly http://synopse.info/forum/viewtopic.php?id=1467
In my patch above i did overlook something:
User delphinium did propose to spilt TableName and Schema which is right. But uppercase the tablename is WRONG.
Please remove the uppercasing line again. The IdentifierConverter is doing the job. Welcome to the postgres world.
Zeos 'em selves mostly uses Catalog.Schema.TableName for PostgreSQL. The IZDatabaseMetadata-interface can give you informations about: Are Schemas, Catalogs etc. supported.
And we use the IdentifierConverter for all 3 identifiers. PostgreSQL supports Schema=XyZ or xYZ or XYz or...... Same for Catalog and Tablename.
I don't know where your Tablename is comming from but (i'm starting from the premisse you're using the IZDatabasMetadata interface) keep track you force the users to work case sensitive as much as possible. The IdentifierConverter also checks if the Identifier allready is quoted then nothing happens.
Select * from XyZ.xyz.XYz could lead to pain if all schemas+catalogs do have a table called "XYz" and "xYZ" and XYZ. Sure you know what i mean... Just a little info @all.
Edit: I made the quick patches for you:
procedure TSQLDBZEOSConnectionProperties.GetTableNames(var Tables: TRawUTF8DynArray);
var meta: IZDatabaseMetadata;
res: IZResultSet;
TableTypes: Types.TStringDynArray;
n: integer;
begin
if GetDatabaseMetadata(meta) then begin
SetLength(TableTypes,1);
TableTypes[0] := 'TABLE';
res := meta.GetTables('','','',TableTypes);
n := 0;
while res.Next do
{$IFDEF ZEOS72UP}
AddSortedRawUTF8(Tables,n,res.GetUTF8String(3));
{$ELSE !ZEOS72UP}
AddSortedRawUTF8(Tables,n,SynUnicodeToUtf8(res.GetUnicodeString(3)));
{$ENDIF ZEOS72UP}
SetLength(Tables,n);
end else
inherited;
end;
procedure TSQLDBZEOSConnectionProperties.GetFields(
const aTableName: RawUTF8; var Fields: TSQLDBColumnDefineDynArray);
var meta: IZDatabaseMetadata;
res: IZResultSet;
n, i: integer;
TableName: string;
F: TSQLDBColumnDefine;
FA: TDynArray;
begin
if GetDatabaseMetadata(meta) then begin
TableName := meta.GetIdentifierConvertor.Quote(UTF8ToString(aTableName));
res := meta.GetColumns('','',TableName,'');
FA.Init(TypeInfo(TSQLDBColumnDefineDynArray),Fields,@n);
FA.Compare := SortDynArrayAnsiStringI; // FA.Find() case insensitive
FillChar(F,sizeof(F),0);
while res.Next do begin
{$IFDEF ZEOS72UP}
F.ColumnName := res.GetUTF8String(4);
F.ColumnTypeNative := res.GetUTF8String(6);
{$ELSE !ZEOS72UP}
F.ColumnName := SynUnicodeToUtf8(res.GetUnicodeString(4));
F.ColumnTypeNative := SynUnicodeToUtf8(res.GetUnicodeString(6));
{$ENDIF ZEOS72UP}
F.ColumnType := TZSQLTypeToTSQLDBFieldType(TZSQLType(res.GetInt(5)));
F.ColumnLength := res.GetInt(7);
F.ColumnPrecision := res.GetInt(9);
FA.Add(F);
end;
if n>0 then begin
res := meta.GetIndexInfo('','',TableName,false,true);
while res.Next do begin
{$IFDEF ZEOS72UP}
F.ColumnName := res.GetUTF8String(9);
{$ELSE !ZEOS72UP}
F.ColumnName := SynUnicodeToUtf8(res.GetUnicodeString(9));
{$ENDIF !ZEOS72UP}
i := FA.Find(F);
if i>=0 then
Fields[i].ColumnIndexed := true;
end;
end;
SetLength(Fields,n);
exit;
end;
inherited;
end;#183 mORMot 1 » Zoes7.2 upgrade » 2013-11-29 22:36:07
- EgonHugeist
- Replies: 61
Hi Arnaud,
i made some small modifications for your current ZDBC api. I added some comments. You need to bind Zeos.inc to avoid incompatibility issues. In this patch it's commented.
I used the SynDBZeos.pas of the Nighly-Snap-Shots. Hope it was the lates one?
Changes:
-Added (hopefully) better getters & setters for your framework.
-Added some Driver-speziffic options to pimp Zeos a bit
-Added the IdentifierConverter for the Table-Names
I must admit i still had no time to study mORMot so i simply did some logical changes i can see in THIS unit. Should i review more?
/// ZEOS 7.x direct access classes for SynDB units (not DB.pas based)
// - this unit is a part of the freeware Synopse framework,
// licensed under a MPL/GPL/LGPL tri-license; version 1.18
unit SynDBZEOS;
{
This file is part of Synopse framework.
Synopse framework. Copyright (C) 2013 Arnaud Bouchez
Synopse Informatique - http://synopse.info
*** BEGIN LICENSE BLOCK *****
Version: MPL 1.1/GPL 2.0/LGPL 2.1
The contents of this file are subject to the Mozilla Public License Version
1.1 (the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.mozilla.org/MPL
Software distributed under the License is distributed on an "AS IS" basis,
WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
for the specific language governing rights and limitations under the License.
The Original Code is Synopse mORMot framework.
The Initial Developer of the Original Code is Arnaud Bouchez.
Portions created by the Initial Developer are Copyright (C) 2013
the Initial Developer. All Rights Reserved.
Contributor(s):
Alternatively, the contents of this file may be used under the terms of
either the GNU General Public License Version 2 or later (the "GPL"), or
the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
in which case the provisions of the GPL or the LGPL are applicable instead
of those above. If you wish to allow use of your version of this file only
under the terms of either the GPL or the LGPL, and not to allow others to
use your version of this file under the terms of the MPL, indicate your
decision by deleting the provisions above and replace them with the notice
and other provisions required by the GPL or the LGPL. If you do not delete
the provisions above, a recipient may use your version of this file under
the terms of any one of the MPL, the GPL or the LGPL.
***** END LICENSE BLOCK *****
Version 1.18
- first public release, corresponding to mORMot framework 1.18
Note:
- if you want to work as expected with SQlite3 backend (but how would need to
do it, since it will be MUCH slower compared to SynDBSQlite3), you need
to change procedure TZSQLiteResultSet.FreeHandle in ZDbcSqLiteResultSet.pas
from ErrorCode := FPlainDriver.reset(FStmtHandle)
into ErrorCode := SQLITE_OK
(i.e. disable statement reset)
Todo:
- handle MySQL + PostgreSQL
}
{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER
{.$I Zoes.inc}
interface
uses
Windows, Types, SysUtils,
{$IFNDEF DELPHI5OROLDER}
Variants,
{$ENDIF}
Classes, Contnrs,
SynCommons,
SynDB,
ZURL, ZDbcIntfs, ZDbcResultSet;
{ -------------- ZEOS database components direct process }
type
/// Exception type associated to the ZEOS database components
ESQLDBZEOS = class(ESQLDBException);
/// implement properties shared by ZEOS connections
TSQLDBZEOSConnectionProperties = class(TSQLDBConnectionPropertiesThreadSafe)
protected
fURL: TZURL;
fDBMSName: RawUTF8;
/// initialize fForeignKeys content with all foreign keys of this DB
// - do nothing by now (ZEOS metadata may be used in the future)
procedure GetForeignKeys; override;
/// convert ZDBC field type into mORMot fieldtype
function TZSQLTypeToTSQLDBFieldType(aNativeType: TZSQLType): TSQLDBFieldType; virtual;
public
/// initialize the properties to connect to the ZEOS engine
// - aServerName shall contain the ZEOS URI, e.g:
// $ zdbc:firebird-2.0://127.0.0.1:3050/model?username=sysdba;password=masterkey
// $ sqlite
// i.e. '[zdbc:]PROTOCOL://HOST:PORT[/DATABASE][?paramname=value]'
// - you can define the TZConnection.LibraryLocation property by setting a
// '?LibLocation=...' parameter within the aServerName URL value
// - or simple use TSQLDBZEOSConnectionProperties.URI() class method
// - aDatabaseName, aUserID, aPassword are used if not already set as URI
// in aServerName value
// - you can use Protocols property to retrieve all available protocol names
constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); override;
/// finalize properties internal structures
destructor Destroy; override;
/// create a new connection
// - caller is responsible of freeing this instance
// - this overriden method will create an TSQLDBZEOSConnection instance
function NewConnection: TSQLDBConnection; override;
/// retrieve the column/field layout of a specified table
// - this overriden method will use ZDBC metadata to retrieve the information
procedure GetFields(const aTableName: RawUTF8; var Fields: TSQLDBColumnDefineDynArray); override;
/// get all table names
// - this overriden method will use ZDBC metadata to retrieve the information
// - PostgreSQL note: it was reported that some table names expects to be
// quoted for this DB engine - and ZDBC won't do it for yourself - please
// ensure you specify the correct quoted table name e.g. when you register
// the external PostgreSQL table via function VirtualTableExternalRegister()
procedure GetTableNames(var Tables: TRawUTF8DynArray); override;
/// access to the database metadata, as retrieved by ZEOS
// - returns TRUE if metadata interface has been retrieved
function GetDatabaseMetadata(out meta: IZDatabaseMetadata): boolean;
/// compute the ZEOS URI from a given database engine
// - you can set an optional full path to the client library name,
// to be completed on the left side with the executable path
// - possible use may be:
// ! PropsOracle := TSQLDBZEOSConnectionProperties.Create(
// ! TSQLDBZEOSConnectionProperties.URI(dOracle,'oci64\oci.dll'),
// ! 'tnsname','user',pass');
// ! PropsFirebird := TSQLDBZEOSConnectionProperties.Create(
// ! TSQLDBZEOSConnectionProperties.URI(dFirebird,'Firebird\fbembed.dll'),
// ! 'databasefilename','',');
class function URI(aServer: TSQLDBDefinition;
const aLibraryLocation: TFileName='';
aLibraryLocationAppendExePath: boolean=true): RawUTF8;
published
/// the remote DBMS name, as retrieved from ServerName, i.e. ZEOS URL
property DBMSName: RawUTF8 read fDBMSName;
end;
/// implements a connection via the ZEOS access layer
TSQLDBZEOSConnection = class(TSQLDBConnectionThreadSafe)
protected
fDatabase: IZConnection;
public
/// prepare a connection to a specified ZEOS database server
constructor Create(aProperties: TSQLDBConnectionProperties); override;
/// connect to the specified ZEOS server
// - should raise an ESQLDBZEOS on error
procedure Connect; override;
/// stop connection to the specified ZEOS database server
// - should raise an ESQLDBZEOS on error
procedure Disconnect; override;
/// return TRUE if Connect has been already successfully called
function IsConnected: boolean; override;
/// create a new statement instance
function NewStatement: TSQLDBStatement; override;
/// begin a Transaction for this connection
procedure StartTransaction; override;
/// commit changes of a Transaction for this connection
// - StartTransaction method must have been called before
procedure Commit; override;
/// discard changes of a Transaction for this connection
// - StartTransaction method must have been called before
procedure Rollback; override;
/// access to the associated ZEOS connection instance
property Database: IZConnection read fDatabase;
end;
/// implements a statement via a ZEOS database connection
TSQLDBZEOSStatement = class(TSQLDBStatementWithParamsAndColumns)
protected
fStatement: IZPreparedStatement;
fResultSet: IZResultSet;
fResultInfo: IZResultSetMetaData;
public
{{ Prepare an UTF-8 encoded SQL statement
- parameters marked as ? will be bound later, before ExecutePrepared call
- if ExpectResults is TRUE, then Step() and Column*() methods are available
to retrieve the data rows
- raise an ESQLDBZeos on any error }
procedure Prepare(const aSQL: RawUTF8; ExpectResults: boolean = false); overload; override;
{{ Execute a prepared SQL statement
- parameters marked as ? should have been already bound with Bind*() functions
- this implementation will also loop through all internal bound array
of values (if any), to implement BATCH mode
- this overriden method will log the SQL statement if sllSQL has been
enabled in SynDBLog.Family.Level
- raise an ESQLDBZeos on any error }
procedure ExecutePrepared; override;
{/ Reset the previous prepared statement
- this overriden implementation will reset all bindings and the cursor state
- raise an ESQLDBZeos on any error }
procedure Reset; override;
{{ Access the next or first row of data from the SQL Statement result
- return true on success, with data ready to be retrieved by Column*() methods
- return false if no more row is available (e.g. if the SQL statement
is not a SELECT but an UPDATE or INSERT command)
- if SeekFirst is TRUE, will put the cursor on the first row of results
- raise an ESQLDBZeos on any error }
function Step(SeekFirst: boolean = false): boolean; override;
{{ return a Column integer value of the current Row, first Col is 0 }
function ColumnInt(Col: Integer): Int64; override;
{{ returns TRUE if the column contains NULL }
function ColumnNull(Col: Integer): boolean; override;
{{ return a Column floating point value of the current Row, first Col is 0 }
function ColumnDouble(Col: Integer): double; override;
{{ return a Column date and time value of the current Row, first Col is 0 }
function ColumnDateTime(Col: Integer): TDateTime; override;
{{ return a Column currency value of the current Row, first Col is 0 }
function ColumnCurrency(Col: Integer): currency; override;
{{ return a Column UTF-8 encoded text value of the current Row, first Col is 0 }
function ColumnUTF8(Col: Integer): RawUTF8; override;
{{ return a Column as a blob value of the current Row, first Col is 0 }
function ColumnBlob(Col: Integer): RawByteString; override;
end;
var
/// list of all available ZEOS protocols
// - you have to call SetZEOSProtocols before using it, to update this
// global list with all initialized ZPlain*Driver units
// - to be used e.g. within ZEOS URI, as TSQLDBZEOSConnectionProperties.ServerName
ZEOSProtocols: TRawUTF8DynArray;
/// to be called in order to populate the global ZEOSProtocols list
procedure SetZEOSProtocols;
implementation
{ TSQLDBZEOSConnectionProperties }
constructor TSQLDBZEOSConnectionProperties.Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
const
PCHARS: array[0..7] of PUTF8Char = (
'ORACLE','FREETDS_MSSQL','MSSQL','INTERBASE','FIREBIRD','MYSQL','SQLITE','POSTGRESQL');
TYPES: array[-1..high(PCHARS)] of TSQLDBDefinition = (
dDefault,dOracle,dMSSQL,dMSSQL,dFirebird,dFirebird,dMySQL,dSQLite,dPostgreSQL);
// expecting Postgresql + Sybase + ASA support in TSQLDBDefinition
begin
fServerName := aServerName;
if (fServerName<>'') and (PosEx(':',fServerName)=0) then
fServerName := fServerName+':';
if not IdemPChar(Pointer(aServerName),'ZDBC:') then
fServerName := 'zdbc:'+fServerName;
fURL := TZURL.Create(UTF8ToString(fServerName));
if fURL.Database='' then
fURL.Database := UTF8ToString(aDatabaseName);
if fURL.UserName='' then
fURL.UserName := UTF8ToString(aUserID);
if fURL.Password='' then
fURL.Password := UTF8ToString(aPassWord);
fDBMSName := StringToUTF8(fURL.Protocol);
fDBMS := TYPES[IdemPCharArray(pointer(fDBMSName),PCHARS)];
inherited Create(aServerName,aDatabaseName,aUserID,aPassWord);
fURL.Properties.Add('controls_cp=CP_UTF8');
fUseCache := false; // caching is to be disabled - not found stable enough
case fDBMS of
dSQLite: begin // ZEOS support of SQLite3 is just buggy
//EH: Yes that's right. Zees !trys! to provide a common behavior as much as
//possible. So actually we won't change this part
fSQLCreateField[ftInt64] := ' BIGINT'; // SQLite3 INTEGER = 32bit for ZDBC!
{$IFDEF ZEOS72UP}
fUseCache := true; //AB, im not sure about this option. But if you set ForceNativeResultSet=True to stmt-info's you have to cache the values
{$ENDIF}
end;
dFirebird: begin
if not FileExists(fURL.Database) then
fURL.Properties.Add('createNewDatabase='+UTF8ToString(
SQLCreateDatabase(StringToUTF8(fURL.Database))));
fURL.Properties.Add('codepage=UTF8');
fUseCache := true; // caching rocks with Firebird ZDBC provider :)
end;
dOracle, dPostgreSQL: begin
fURL.Properties.Add('codepage=UTF8');
fUseCache := true;
end;
end;
end;
procedure TSQLDBZEOSConnectionProperties.GetForeignKeys;
begin
{ TODO : get FOREIGN KEYS from ZEOS metadata ? }
end;
function TSQLDBZEOSConnectionProperties.NewConnection: TSQLDBConnection;
begin
result := TSQLDBZEOSConnection.Create(self);
end;
destructor TSQLDBZEOSConnectionProperties.Destroy;
begin
fURL.Free;
inherited;
end;
procedure SetZEOSProtocols;
var List: TStringList;
i,j: integer;
Protocols: Types.TStringDynArray;
begin
List := TStringList.Create;
try
with DriverManager.GetDrivers do
for i := 0 to Count-1 do begin
Protocols := (Items[i] as IZDriver).GetSupportedProtocols;
for j := 0 to high(Protocols) do
List.Add(Protocols[j]);
end;
List.Sort;
SetLength(ZEOSProtocols,List.Count);
for i := 0 to high(ZEOSProtocols) do
ZEOSProtocols[i] := StringToUTF8(List[i]);
finally
List.Free;
end;
end;
function TSQLDBZEOSConnectionProperties.GetDatabaseMetadata(out meta: IZDatabaseMetadata): boolean;
var conn: IZConnection;
begin
conn := (MainConnection as TSQLDBZEOSConnection).fDatabase;
result := conn.UseMetadata;
if result then begin
meta := conn.GetMetadata;
meta.ClearCache; // we need to retrieve the actual metadata
end;
end;
procedure TSQLDBZEOSConnectionProperties.GetTableNames(var Tables: TRawUTF8DynArray);
var meta: IZDatabaseMetadata;
res: IZResultSet;
TableTypes: Types.TStringDynArray;
n: integer;
begin
if GetDatabaseMetadata(meta) then begin
SetLength(TableTypes,1);
TableTypes[0] := 'TABLE';
res := meta.GetTables('','','',TableTypes);
n := 0;
while res.Next do
AddSortedRawUTF8(Tables,n,SynUnicodeToUtf8(res.GetUnicodeString(3)));
SetLength(Tables,n);
end else
inherited;
end;
procedure TSQLDBZEOSConnectionProperties.GetFields(
const aTableName: RawUTF8; var Fields: TSQLDBColumnDefineDynArray);
var meta: IZDatabaseMetadata;
res: IZResultSet;
n, i: integer;
TableName: string;
F: TSQLDBColumnDefine;
FA: TDynArray;
begin
if GetDatabaseMetadata(meta) then begin
TableName := meta.GetIdentifierConvertor.Quote(UTF8ToString(UpperCase(aTableName)));
res := meta.GetColumns('','',TableName,'');
FA.Init(TypeInfo(TSQLDBColumnDefineDynArray),Fields,@n);
FA.Compare := SortDynArrayAnsiStringI; // FA.Find() case insensitive
FillChar(F,sizeof(F),0);
while res.Next do begin
F.ColumnName := SynUnicodeToUtf8(res.GetUnicodeString(4));
F.ColumnTypeNative := SynUnicodeToUtf8(res.GetUnicodeString(6));
F.ColumnType := TZSQLTypeToTSQLDBFieldType(TZSQLType(res.GetInt(5)));
F.ColumnLength := res.GetInt(7);
F.ColumnPrecision := res.GetInt(9);
FA.Add(F);
end;
if n>0 then begin
res := meta.GetIndexInfo('','',TableName,false,true);
while res.Next do begin
F.ColumnName := SynUnicodeToUtf8(res.GetUnicodeString(9));
i := FA.Find(F);
if i>=0 then
Fields[i].ColumnIndexed := true;
end;
end;
SetLength(Fields,n);
exit;
end;
inherited;
end;
function TSQLDBZEOSConnectionProperties.TZSQLTypeToTSQLDBFieldType(aNativeType: TZSQLType): TSQLDBFieldType;
begin
case aNativeType of
stBoolean, stByte, stShort, stInteger, stLong
{$IFDEF ZEOS72UP}, stSmall, stWord, stLongWord, stULong{$ENDIF}:
result := ftInt64;
stFloat, stDouble:
result := ftDouble;
stBigDecimal{$IFDEF ZEOS72UP}, stCurrency{$ENDIF}:
result := ftCurrency;
stDate, stTime, stTimestamp:
result := ftDate;
stString, stUnicodeString, stAsciiStream, stUnicodeStream:
result := ftUTF8;
stBytes, stBinaryStream:
result := ftBlob;
else raise ESQLDBZEOS.CreateFmt('Unexpected TZSQLType "%s"',
[{$ifdef PUREPASCAl}IntToStr(ord(aNativeType)){$else}
GetEnumName(Typeinfo(TZSQLType),ord(aNativeType))^{$endif}]);
end;
end;
class function TSQLDBZEOSConnectionProperties.URI(aServer: TSQLDBDefinition;
const aLibraryLocation: TFileName; aLibraryLocationAppendExePath: boolean): RawUTF8;
const
/// ZDBC provider names corresponding to SynDB recognized SQL engines
ZEOS_PROVIDER: array[TSQLDBDefinition] of RawUTF8 = (
'','','oracle','mssql','','mysql','sqlite','firebird-2.5','','postgresql-9');
//EH: Hint. I wouldn't use protocols with version prefix. Zeos always use latest protocol. But keep track for embeddeds
begin
result := ZEOS_PROVIDER[aServer];
if (result='') or (aLibraryLocation='') then
exit;
result := result+':?LibLocation=';
if aLibraryLocationAppendExePath then
result := result+StringToUTF8(ExtractFilePath(ParamStr(0)));
result := result+StringToUTF8(aLibraryLocation);
end;
{ TSQLDBZEOSConnection }
constructor TSQLDBZEOSConnection.Create(aProperties: TSQLDBConnectionProperties);
begin
inherited Create(aProperties);
fDatabase := DriverManager.GetConnectionWithParams(
(fProperties as TSQLDBZEOSConnectionProperties).fURL.URL,nil);
fDatabase.SetAutoCommit(true);
fDatabase.SetTransactionIsolation(tiNone);
end;
procedure TSQLDBZEOSConnection.Connect;
var Log: ISynLog;
begin
if fDatabase=nil then
raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSConnection.Connect() on %s failed: Database=nil',
[fProperties.ServerName]);
with (fProperties as TSQLDBZEOSConnectionProperties).fURL do
Log := SynDBLog.Enter(Self,pointer(FormatUTF8('Connect to % % for % at %:%',
[Protocol,Database,HostName,Port])),true);
try
fDatabase.Open;
Log.Log(sllDB,'Connected to % using % %',
[fProperties.ServerName,fProperties.DatabaseName,fDatabase.GetClientVersion]);
inherited Connect; // notify any re-connection
except
on E: Exception do begin
Log.Log(sllError,E);
Disconnect; // clean up on fail
raise;
end;
end;
end;
procedure TSQLDBZEOSConnection.Disconnect;
begin
try
inherited Disconnect; // flush any cached statement
finally
if (fDatabase<>nil) and not fDatabase.IsClosed then
fDatabase.Close;
end;
end;
function TSQLDBZEOSConnection.IsConnected: boolean;
begin
result := Assigned(fDatabase) and not fDatabase.IsClosed;
end;
function TSQLDBZEOSConnection.NewStatement: TSQLDBStatement;
begin
result := TSQLDBZEOSStatement.Create(self);
end;
procedure TSQLDBZEOSConnection.StartTransaction;
begin
inherited StartTransaction;
fDatabase.SetAutoCommit(false);
fDatabase.SetTransactionIsolation(tiReadCommitted);
end;
procedure TSQLDBZEOSConnection.Commit;
begin
inherited Commit;
fDatabase.Commit;
fDatabase.SetAutoCommit(true);
fDatabase.SetTransactionIsolation(tiNone);
end;
procedure TSQLDBZEOSConnection.Rollback;
begin
inherited Rollback;
fDatabase.Rollback;
fDatabase.SetAutoCommit(true);
fDatabase.SetTransactionIsolation(tiNone);
end;
{ TSQLDBZEOSStatement }
procedure TSQLDBZEOSStatement.Prepare(const aSQL: RawUTF8;
ExpectResults: boolean);
var Log: ISynLog;
Info: TStrings;
begin
Log := SynDBLog.Enter(Self);
if (fStatement<>nil) or (fResultSet<>nil) then
raise ESQLDBZEOS.CreateFmt('%s.Prepare() shall be called once',[ClassName]);
inherited Prepare(aSQL,ExpectResults); // connect if necessary
Info := TStringList.Create;
{ oracle option }
Info.Add('prefetch_count=100000'); //sets OCI_ATTR_PREFETCH_ROWS on prepare a fetch
//default = 100 on 7.1down and 1000 on 7.2up
{ sqlite option }
Info.Add('ForceNativeResultSet=True'); //get access to the native resultset. This only works sequention/forwardonly (step) and YOU have to localize the values
//new since 7.2up
{ sqlite option }
//Info.Add('BindDoubleDateTimeValues=True'); //Bind double values instead of ISO formated DataTime-strings. SQLDB is going this route
//introduced since 7.2up
{ mysql }
//Info.Add('preferprepared=True'); //use mysql real-prepared api instead of string based once
// actually it's not realy faster.. just a hint:
// http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-problems.html
{oracle, postgresql, mysql-realprepared}
Info.Add('chunk_size=1048576'); //let's set 1024KB / chunk for synopse or more?
//retrieving/submitting lob's in chunks. Default is 4096Bytes / Chunk
//it's depending to your local network speed e.g. bad WLAN or so
//for firebird we always using the blob-segment size
{oracle, mysql, postgresql, firebird}
Info.Add('cachedlob=false'); //Always load the lobs? Or just on accessing them?
//default = False
//if you allways copy the data by fetching the row than it doesn't make sence.
//introduced since 7.2up
fStatement := (fConnection as TSQLDBZEOSConnection).fDatabase.
PrepareStatementWithParams(UTF8ToString(fSQL), Info);
Info.Free;
end;
procedure TSQLDBZEOSStatement.ExecutePrepared;
var i: integer;
Props: TSQLDBZEOSConnectionProperties;
Log: ISynLog;
blob: IZBlob;
name: string;
begin
Log := SynDBLog.Enter(Self);
with Log.Instance do
if sllSQL in Family.Level then
LogLines(sllSQL,pointer(SQLWithInlinedParams),self,'--');
if fStatement=nil then
raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() invalid call',[ClassName]);
if fResultSet<>nil then
raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() miss a Reset',[ClassName]);
// 1. bind parameters in fParams[] to fQuery.Params
for i := 1 to fParamCount do
with fParams[i-1] do
case VType of
ftNull: fStatement.SetNull(i,stUnknown);
ftInt64: fStatement.SetLong(i,VInt64);
ftDouble: fStatement.SetDouble(i,PDouble(@VInt64)^);
ftCurrency: fStatement.SetDouble(i,PCurrency(@VInt64)^);
ftDate: fStatement.SetTimestamp(i,PDateTime(@VInt64)^);
ftUTF8: {$IFDEF ZEOS72UP}
fStatement.SetUTF8String(i,VData);
//EH: may i assume you RawUTF8 prevents conversions? So the UTF8-Setter is save for all codepages. Zeos is doing conversions if necessary.
//fStatement.SetRawByteString(i,VData); //RawbyteString always represents the client encoded raw-string
//you could also use SetCharRec(i, ZCompatibility.TZCharRec); and point to the values. What is VData type of?
{$ELSE}
{$ifdef UNICODE} // ZWideString = SynUnicode in fact
fStatement.SetString(i,UTF8ToSynUnicode(VData));
{$else}
fStatement.SetString(i,VData); // thanks to controls_cp=CP_UTF8
{$endif}
{$ENDIF}
{EH:
Arnaud if you would make diffences with PAnsiChars than zeos could perform much faster by using the
TZAnsiRec's (fetching), TZCharRec(update).
I did decide to deal with Length's everywhere to avoid strlen call's where ever possible
same for CLobs where you have new setters and getters with the AbstractClob}
ftBlob: begin
blob := TZAbstractBlob.CreateWithData(Pointer(VData),length(VData),
fStatement.GetConnection);
fStatement.SetBlob(i,stBinaryStream,blob);
end;
else
raise ESQLDBZEOS.CreateFmt('Invalid type on bound parameter #%d',[i]);
end;
// 2. Execute query
if fExpectResults then begin
fCurrentRow := -1;
fResultSet := fStatement.ExecuteQueryPrepared;
fResultInfo := fResultSet.GetMetadata;
Props := fConnection.Properties as TSQLDBZEOSConnectionProperties;
fColumnCount := 0;
fColumn.ReHash;
for i := 1 to fResultInfo.GetColumnCount do begin
name := fResultInfo.GetColumnLabel(i);
if name='' then
name := fResultInfo.GetColumnName(i);
PSQLDBColumnProperty(fColumn.AddAndMakeUniqueName(
// Delphi<2009: already UTF-8 encoded due to controls_cp=CP_UTF8
{$ifdef UNICODE}StringToUTF8{$endif}(name)))^.ColumnType :=
Props.TZSQLTypeToTSQLDBFieldType(fResultInfo.GetColumnType(i));
end;
end else
fStatement.ExecutePrepared;
// 3. handle out parameters -> TODO (fStatement is IZCallableStatement)
end;
procedure TSQLDBZEOSStatement.Reset;
begin
if fResultSet<>nil then begin
fResultInfo := nil;
fResultSet := nil;
end;
if fStatement<>nil then
fStatement.ClearParameters;
inherited Reset;
end;
function TSQLDBZEOSStatement.Step(SeekFirst: boolean): boolean;
begin
if fResultSet=nil then
raise ESQLDBZEOS.Create('TSQLDBZEOSStatement.Step() invalid call');
if fColumnCount=0 then // no row returned
result := false else
if SeekFirst then begin
result := fResultSet.First;
if result then
fCurrentRow := 1 else
fCurrentRow := 0;
end else begin
result := fResultSet.Next;
if result then
inc(fCurrentRow);
end;
end;
function TSQLDBZEOSStatement.ColumnBlob(Col: Integer): RawByteString;
var blob: IZBlob;
begin
if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnBlob(%d)',[Col]);
blob := fResultSet.GetBlob(Col+1);
if (blob=nil) or blob.IsEmpty then
result := '' else
Result := blob.GetString;
//SetString(result,blob.PAnsiChar(blob.GetBuffer),blob.Length);
end;
function TSQLDBZEOSStatement.ColumnCurrency(Col: Integer): currency;
begin
if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnCurrency(%d)',[Col]);
result := fResultSet.GetBigDecimal(Col+1);
end;
function TSQLDBZEOSStatement.ColumnDateTime(Col: Integer): TDateTime;
begin
if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnDateTime(%d)',[Col]);
result := fResultSet.GetTimestamp(Col+1);
end;
function TSQLDBZEOSStatement.ColumnDouble(Col: Integer): double;
begin
if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnDouble(%d)',[Col]);
result := fResultSet.GetDouble(Col+1);
end;
function TSQLDBZEOSStatement.ColumnInt(Col: Integer): Int64;
begin
if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnInt(%d)',[Col]);
result := fResultSet.GetLong(Col+1);
end;
function TSQLDBZEOSStatement.ColumnNull(Col: Integer): boolean;
begin
if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnNull(%d)',[Col]);
result := fResultSet.IsNull(Col+1);
end;
function TSQLDBZEOSStatement.ColumnUTF8(Col: Integer): RawUTF8;
begin
if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnUTF8(%d)',[Col]);
{$IFDEF ZEOS72UP}
result := fResultSet.GetUTF8String(Col+1); // GetRawByteString returns also UTF8 if connection charset is UTF8
{$ELSE}
{$ifdef UNICODE}
StringToUTF8(fResultSet.GetString(Col+1),result);
{$else}
result := fResultSet.GetString(Col+1); // thanks to controls_cp=CP_UTF8
{$endif}
end;
end.Michael
#184 Re: mORMot 1 » SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG » 2013-11-29 20:22:11
Hi AB,
i was reading this post.. Your're hardly checking against Spaces and ofther WildCards.
Another Example:
table XyZ, xyz, XYZ
if the 3 table do have differnt columns it leads to pain again. Not only spaces are required to check. You should also check mixed cases etc.
Zeos has already an automation object for the identifiers:
IZConnection.GetMetadata.GetIdentifierConvertor: IZIdentifierConvertor;
IZIdentifierConvertor = interface (IZInterface)
['{2EB07B9B-1E96-4A42-8084-6F98D9140B27}']
function IsCaseSensitive(const Value: string): Boolean;
function IsQuoted(const Value: string): Boolean;
function Quote(const Value: string): string;
function ExtractQuote(const Value: string): string;
end;That interface is able to decide if a identifer-quoting is required and you simply can use this api for ALL drivers instead of this case () checking.
Hope it helps.
Michael
#185 Re: mORMot 1 » ZEOS + PostgreSQL follow up » 2013-11-20 23:03:17
Hi Arnaud,
i'm sorry for the long delay. And I'm sorry for the promissed speed patches don 7.1. They did never happen on 7.1. I was moving house so there was no time for that.
Thanks for the feedback.
Nice speed increase, in fact!
ZDBC are realy prime host to mORMot.
But not top of data, Arnaud. I played and added a loads of code to the dbc-layer. Propose you check 7.2? Even if we're not ready yet. Mark is working on a patch to suppress the logging processes.
I changed the String-Getter&Setter and the Lob-handling is much more powerfull now. And the statments also have a newly type which should hit your goals a bit more.
What about this patch: http://synopse.info/fossil/info/c3394485b2
It should work. Zeos automatically try's to check the encoding since we had too much bugreports in this domain. Hint on 7.2 i differ between TZAbstract-BLOB and CLOB.
The Clobs do support some spezial getter & Setter equal to the newly introduced one of the ResultSets.
I suspect this will work with all branches of Zeos 7.x, and all Delphi compilers (I hope).
Nope, sorry. I changed this code because of a incomplatibility to the TField (which i'm writing Zeos-owned once to speed up the DataSets)
You'll find a {ZEOS72UP} define in Zeos.inc to chage your framework without breaking support for older Zeos-revs.
Long times ago you wrote Zeos is doing to much String-conversions. In fact you had been right. on 7.2 i've been trying to avoid this, where ever possible.
I used a loads of code of the FastCode-Project. User Miab3 did point me to this code and horray we can work with native unconverted values with the Unicode-IDE's too!
And there are some options for the Statments you create (add them to the Info strings):
'cachedlob=Boolean'
Current default=False
This means to localize all lob's on fetching the rows.
Supported protocols(actually) for non-cached lobs:
FireBird, MySQL, Oracle, PostgresSQL. For ADO, DBLib, SQLite it doesn't make sence, since the values are already fetched or we cant't scroll in the resultset-api (forward-only).
'chunk_size=Integer'
Current default=4096
This value controls the package-size for sending/retrieving lob's. It's a question about your local network speed which package size is usefull.
Supported protocols: Oracle, MySQL+Real-Prepared, PostgresSQL
FireBird is always adapting to the Blob-Segment-Size.
'prefetch_count=Integer'
Oracle only. This sets the OCI_ATTR_PREFETCH_ROWS. Current default: 1000. If you select many rows than increase this value.
'ForceNativeResultSet=Boolean'
Current default='false'
Purpose: AVOID creating cached resultsets and you get access to the native resultset.
A pure SQLite option only. which is working forward only and as long a fetch is incomplete -> table is locked.
'BindDoubleDateTimeValues=Boolean'
Current default='false'
A pure SQLite option only.
Purpose: force Zeos to bind double values instead of the ISO-DateTime-Formated strings we actually use. Since 7.2 milliseconds are added to the ISO-Format.
I'm sure my incomplete but actually very successfull refactoring should increase the performance again for the most plains. I'm not ready here an there so a stable candidate won't happen the next months, i think.
Michael
ps. open souce rocks! 
Prose you open a thread in the Zeos-Forum and i'll guid you through the changes. So everybody who is interested to use ZDBC can read it too..
#186 Re: Source Code repository » SynDBDataset - using SynDB or mORMot ORM with any database backend » 2013-02-23 13:31:28
I'm sorry for the delay. ):
Currently i've minior time for Zeos. Now it's weekend!
Very simple: just previous := getdir + chdir(blabla) + chdir(previous).
Something like that in unit ZPlainLoader:function TZNativeLibraryLoader.ZLoadLibrary(Location: String): Boolean;
var newpath, temp: TFileName; // AB modif
begin
if FLoaded then
Self.FreeNativeLibrary;
FLoaded := False;
Result := False;
newpath := ExtractFilePath(Location);
// AB modif BEGIN
try
if newpath<>'' then begin
temp := GetCurrentDir;
SetCurrentDir(newpath);
end;
// AB modif END{$IFDEF UNIX}
{$IFDEF FPC}
FHandle := LoadLibrary(PAnsiChar(Location));
{$ELSE}
FHandle := HMODULE(dlopen(PAnsiChar(Location), RTLD_GLOBAL));
{$ENDIF}
{$ELSE}
FHandle := LoadLibrary(PChar(Location));
{$ENDIF}
// AB modif BEGIN
finally
if temp<>'' then
SetCurrentDir(temp);
end;
// AB modif END
if (FHandle <> INVALID_HANDLE_VALUE) and (FHandle <> 0) then
begin
FLoaded := True;
FCurrentLocation := Location;
Result := True;
end;
end;
Patch done R2193 \testing-7.1 (SVN) Thank you.
1. Is my SetTransactionIsolation() use correct?
See http://synopse.info/fossil/info/0256f1ac15
Now performance is much better for SQlite3:
Well done!
2. How do Prepared Statements work with ZDBC?
How do you know the provider supports it?
The DbcConnection of each protocol decides if a PreparedStatment will be created. Theire are known issues with MySQL f.e. : http://dev.mysql.com/doc/refman/5.0/...-problems.html
To force creating the MySQL/PostgreSQL/SQLite RealPrepareds use
DbcConnection.PrepareStatementWithParams(const SQL: string; Info: TStrings): IZPreparedStatement;
and add 'preferprepared=True' to the Info-Strings.
BUT this dosn't solve you SQLite issue with Zeos-7.0.3. The CAPI Statement is supported on \testing-7.1 (SVN). Currently we do think about omiting the emulated Statements of Postgre and SQLite since the implementation is stable and only a case like 'select :p1' is NOT prepareable. If we decide this step than the RealPrepareds are default and no additional parameter must be used.
Results after commit http://synopse.info/fossil/info/9cc3701b27
Nice comment! But IMHO this should work equal with oracle.. Parameter-Binding is propably the fastest way ((:
So string() will make a conversion into UnicodeString? ??? :s
Yes this is right, and makes only sence for the binary cast... I've started to solve the GetBinaryString-Issue which is NOT supported for all cached-resultsets hmpf. Theire was no test available -> didn't see that, sorry. If i'm ready than NO converstions where done for adding the Strings to the heap and reading them with these methodes. All other behavior i'll not change. Hint this comming patch will be only available since 7.1 too.
Is theire a way to update your diagramms after my little Zeos-tutorial?
Michael
Btw. i had a look to SynCommon.pas! Terrific! Seems like you're the better coder and speed optimizer!
What are you doing with different system-codepages (932 chinese f.e.)? My current implementation does support all Windows/MacOS/*Nix codepages. I was starting from the premisse we should support all these possible CP's (since mainly the users did raise bugreports). Here i understand your UTF8 based framework which is neutral. I was also thinking about cases like OS-CP = 1251 and Client-CP = 1251. No convertations where done in this case except for the Unicode-IDE's.
#187 Re: Source Code repository » SynDBDataset - using SynDB or mORMot ORM with any database backend » 2013-02-18 18:27:01
No, the sub libraries (e.g. icuuc30.dll) are reported as missing, if you do not change the current library to C:\Blabla
At least with latest stable 2.5.2 I downloaded from official web site.
Should we add the extrected path to the current OS path-settings? We've the same behavior on PostgreSQL. Propose a patch (;
Our direct classes are still much efficient (SQLite3 reading speed is e.g. 26527 435995 438519).
About transactions, I'm quite sure I've missed something, at least for SQlite3.
Or perhaps the IZConnection.SetAutoCommit(true) does not start a transaction for the SQLite3 provider, whereas it seems to create a transaction for Firebird (as above results may indicate).
Our direct Sqlite3 classes writing speed is 536 525 93687 113527. So here transaction does make a huge difference!
Well i won't battle with with mORMot..
Uh yes, the Transactions do terrible slow down SQLite on insertion speed if AutoCommit = True. I can not find the thread but i made a behavior test with the PreparedStamtents + SQLite:
I hope i'm right 10.000 Inserts
Autocommit = true 10000 inserts 2min?
Autocommit = False + StartTransaction 4 secs
Autocommit = False + StartTransaction + PreparedStatment (7.1) 2 secs
I was also surpriced about the huge difference!
IZConnection.SetTransactionIsolation(tiReadCommitted);
and you would wonder what happens ((: Test it!
If I use GetBinaryString(), it just does not work, since for SQlite3 it returns always ''.
Surprise again!! Hmpf
If I'm right:
- TZRowAccessor.GetString() returns the UTF-8 encoded text in Delphi < 2009;
- TZRowAccessor.GetString() returns an UnicodeString converted from UTF-8 content for Delphi > 2009.
This is right in your case. UTF8 is optimal, but all othe AnsiString-CP are equal possible to map into the Wide/UnicodeStrings -> ZEncoding.pas no problem. But i'll check your suggstions too.
The Result := String(TempBlob.GetString); statement in line 1003 of ZDBCCache.pas sounds like an unneeded conversion to me, for Unicode versions of Delphi.
Or should I just not use "controls_cp=CP_UTF8" for Unicode version of Delphi?
Have no sources here, i'll check it. but i thing IZBlob.GetString returns allways RawByteStrings...
#188 Re: Source Code repository » SynDBDataset - using SynDB or mORMot ORM with any database backend » 2013-02-18 13:39:36
First of all let's start from the premisse i've studied you souces in less than a half hour. I didn't know anything about synopse until one user proposed to use it as OleDB replacement + FPC.
I had only issues with Unicode support - and did only fully test it for Delphi 7-2007 yet (perhaps I missed something about parameters to use with Delphi 2009+).
But our SynDB unit and all mORMot is already Unicode, since it uses UTF-8 everywhere, in fact.
I don't know you current DataSet-implementations. But i would wonder if you get WideString and WideMemo-Fields running on D7?
We support only Delphi by now. We prepared for FPC but it is not finished yet. There is a lot of low-level RTTI stuff in mORMot (not SynDB.pas by itself) which is not compatible with FPC.
I did not notice the {{ issue up to now with FPC. I could easily fix it and include the fix to mORMot.
I'm not able to understand the point of this forum post: is it to add a SynDB layer to Zeos (i.e. the reverse way?).
You have to note that ADO is base on OleDB, but is not the same. Our SynOleDB unit only implements the OleDB part, not the ADO overlay.But you are right: OleDB is deprecated.
Microsoft ask developers to switch to ODBC.
See http://blog.synopse.info/post/2012/02/2 … enjoy-ODBC!
So I guess an ODBC layer could make sense.I suppose adding ODBC support to ZDBC is not difficult.
You can use our units as start, without any problem.
mORMot is published under a tri-license scheme, i.e. GPL / LGPL / MPL.
So you can integrate some part of our code within Zeos LGPL project. Just do not forget to acknowledge the source.We used a tri-license and not only a LGPL, due to some restrictions issues.
As far as I understood, the LGPL license requires you to release all the source code of any library you link statically to.
I do not understand how it is compatible with ZeosLib to be used within closed source proprietary projects, statically linked with the executable.
Well an direct ODBC support seems the better choise for future works. The most OS's do support ODBC. Using third party souces isn't decided yet in the team. But thank you for allowing the usage in terms of your licence restriction.
You are right: SynDBZeos is limited about connection provider charset, by now.
SynDB/mORMot principle is that you use UTF-8 in the APIs, and let the DB provider to the encoding for you.
Well this works for 95% of all cases, except for some spezials like the CS 'NONE' etc. Create a FireBird DB with default Charset 'NONE', add a talbe with some spezial collations and you run into the first issues. A starting discussion and the solution: http://zeos.firmos.at/viewtopic.php?t=3 … &start=600
You can call it feature, if you want but IMHO is it a qustion of time for bug-reports.
In fact, UTF-8 is supported outside the SynDB layer, but any charset should be able to be used within the connection.
I set the UTF-8 encoding within SynDBZeos.pas up to now, because I wanted it to work with tables created by our ORM, which expects Unicode/UTF-8 encoding.
It is a first draft version. Feedback is welcome, and I won't invest much time if noone is using it!
Me too ((: Like i wrote i'll try to support you as good as i can.
With the current implementation of ZDBC, I was not able to find out the current charset encoding of the connection, in a standard way - help is needed here.
Well i wrote a DbcMetaData function GetCharacterSet(..): IZResultSet. But i had not the time to complete it for all protocols. Here our current implementation leaks if you really need it -> go on reading.
And in some places, the returned AnsiString is of the same charset than the connection - which could be diverse, by definition.
Fact. No conversations are needed except WideString/UnicodeString mapping.
The implementation of ZCompatibility and ZEncoding just sounds over-complicated to me.
No problem it was an hint how to unify the Delphi compilers with Ansi and Unicode-Support.
You can find a light and very fast charset support in our SynDBOracle direct layer, for instance.
Equals to my implementation (; Some CS aliases are missing (ZPlainOracle.pas)
ZDBC just does not work with pre Delphi 2009 versions of Delphi, if the database charset does not match the current VCL charset e.g. with Sqlite3 or Firebird UTF-8 support: this is the reason why I had to use WideString in SynDBZeos for Firebird and Sqlite3, which slows down everything. For Oracle, when used on a database matching the VCL AnsiString charset, it worked.
As far as I understood Zeos string handling, it does make a lot of unnecessary charset conversion or string allocation. Just make a test about our SynDBOracle layer speed against the ZDBC Oracle provider, and you will find out what I mean. SynOleDB or SynDBODBC handles perfect Unicode process, whatever the database charset is, since by definition OleDB connection is UTF-16 (like all other WinAPI), and ODBC provides *W() Unicode APIs, used by SynDBODBC.
My question is how the current connection charset could be retrieved, and row unconverted data accessed by the ZDBC providers? For what I've seen, the internal buffer is not available, and AnsiString process seems broken (at least for some ZDBC drivers I've read). So even if I've got the connection charset, client is not able to use it as expected, since it will be converted either to the current AnsiString code page (for AnsiString method), or to WideString, but not using the encoding, for some drivers.
Disagree. ((: D7 -> WideFields? You simply do the job twize. The Dbc-Layer uses an enum-type: TZControlsCodePage = (cCP_UTF16, cCP_UTF8, cGET_ACP)
The default for:
Ansi-Delphi is cGet_ACP
Unicode-Delphi is cCP_UTF16
FPC is cCP_UTF8.
Imagine the IZResultSet.GetString returns a String which type ever. AnsiString/UnicodeString/RawByteString(for FPC2.7.1 which is doing automated AnsiString(CP ??) conversations). 600 compiler-warnings have been gone. Convert all between Dbc and Plain-tier nowhere else.
Here i was starting from the premisse we should be able to encode the Strings in dependencies what the Controls do expect. Theire are controls like TNT which can use WideStrings or UTF-8 (with a little hack).
Add Param 'controls_cp=GET_ACP' or 'controls_cp=CP_UTF8' or 'controls_cp=CP_UTF16' to the TZURL.Properites and you have pure UTF8/Ansi/Unicode-Strings for the Ansi-Delphis or WideFields-Support. It doesn't matter which Client-Encoding Zeos does the job. Well what's faster: The Zeos is able to do ALL encodings or the Server does it? Allways think about the compiler we have and what must be done to keep the work of the users as minior as possible. Btw. Delphi calls the same function on casting AnsiString to Wide and vice versa which Zeos has allready done if you move everything up to WideStrings. Here i've the impression the same String is converted 4x until the Dataset retrieves him? Speed?
Hint for full overview: ZCompatibility.pas procedure TZCodePagedObject.SetConSettingsFromInfo(Info: TStrings);
This Enum, the current os CP and the Client-CP are the magic key. Zeos does everything for you. Another option is 'AutoEncodeStrings=ON' which simply checks the encoding end sends the Data like the Server does expect it, but slows donw the speed for updates. No exceptions like 'Invalid Byte Sequence for UTF8'... Start from the premisse a newbie is using Zeos. Everything is optional for newbies and pro's.
Zeos is able to open Latin1 connections (even if CP 1251 isn't you current), leave the String as is/convert to UTF8 or Wide..
By the way, I have at least two main issues/limitations with ZEOS:
- Firebird embedded library can not be in a sub-folder - I add to change the code and add a prev := GetDirectory / ChangeDirectory(new) / LoadLibrary / ChangeDirectory(prev) to let it work as expected;
TZURL.LibLocation := 'C:\blabla\MyLib.dll' doesn't work to load the libs named and located somewhere else?
- SQLite3 layer identifies an INTEGER column as Delphi integer, which is wrong, since in SQlite3, an INTEGER column is an Int64 - and if you use BIGINT for an ID field, it won't be shared with the internal RowID.
Thanks for the hint (: Will be done tomorrow.
#189 Re: Source Code repository » SynDBDataset - using SynDB or mORMot ORM with any database backend » 2013-02-17 09:33:22
Hi Arnaud,
nice to clearify the purpose of the diagramms again (for me personally). I had a look to your SynDBZEOS.pas and i'm pretty happy to see, you understand how to work with our Dbc-Layer!!!
If you've Zeos related questions, i'll try to support as good as i can! Don't hesitate to write me on my private eMail (; .
I had a look to your SynOLEDB.pas implementation because we're looking for an ADO replacement to get OLE support for FPC running too. Well i think we need a loads of additional {$IFDEF ..}'s. One user did point me to you implementation http://zeos.firmos.at/viewtopic.php?p=16948#16948 .
May i ask you if we can start an joint-venture in this domain? If ZeosDevMember finds the time of course..
OpenSource rocks! Keep it up!
#190 Re: Source Code repository » SynDBDataset - using SynDB or mORMot ORM with any database backend » 2013-02-15 15:31:43
Hi..
Thanks for the speed tests you made! Did you test AnyDAC too?
Which tests did you use for these Results? Are You sure you've created IZPreparedStatments? Did you test large binaries too? e.g Option "CHUNK_SIZE"..
Michael, ZeosDevTeam
Btw. Zeos7.1 allready uses Prepared Statments for SQLite. For Oracle it's already implemented. Zeos7.1.x stable will be available in the first Quarter of the year.