You are not logged in.
- Topics: Active | Unanswered
#51 mORMot 1 » SynCommons compile error on XUbuntu 14.04.2 » 2015-07-15 13:22:25
- alpinistbg
- Replies: 3
Hi ab,
I'm just trying to compile Sample01 on xubuntu with latest Lazarus and mORMot (just taken from the respective repositories), but unfortunately it gives me the error: SynCommons.pas(38350,13) Error: Incompatible types: got "LongInt" expected "Boolean".
The source code looks that way:
function TSynLocker.TryLock: boolean;
begin
result := TryEnterCriticalSection(fSection);
end;What I'm doing wrong? Thanks!
#52 Re: mORMot 1 » Autoincrementing IDs and the DB backends » 2015-07-09 23:07:48
Of course, but AFAIK it is not single threaded ...
#53 mORMot 1 » Autoincrementing IDs and the DB backends » 2015-07-09 13:20:12
- alpinistbg
- Replies: 3
Since I would like to generate sequence of natural numbers using the database itself, It occured to me that I can use the ID field of some side table for the purpose. My question is: Is the atomicity guaranteed when using different DB backends?
MSSQL for example usually gives such a guarantee on single statements (when outside of transaction) and for Firebird - there is always a pending transaction, so my concern is that in the MSSQL case, between the 'SELECT MAX(ID)+1 FROM' unitil actual 'INSERT INTO' there is a slight window where things can get messy and for the Firebird - that the insert can conflict with another one.
Regards,
#54 Re: mORMot 1 » Time-sortable, 64bit UUID generation, 10 millions without collision » 2015-04-19 20:12:37
@alpinistbg
In mORMot clients, you have a safe way of retrieving the server date time at connection.
See the ServerTimeStampSynchronize method.
We use the UTC dedicated API under Windows, so that we should not be affected by DST changes.
Every timestamp is written in UTC within mORMot (e.g. autofilled TModTime and TCreateTime published fields).
And a small time shift should not be a problem, IMHO.
As long as for the uniqueness - yes, it should be OK.
But if we consider the monotonic property it may be not the case.
We're talking for disconnected nodes here, i.e. there is no server all of the time, imagine the system clock adjusted backwards for example. This will immediately result in smaller ID's. The adjustment may come even from the system itself when configured to "Intenet time" on the first synchronization with the time server. Also the hardware clock drifts - the user may have a good reason for the adjustment ... Also there is a cases when somebody wants to cheat and adjusts the time to register some action earlier than it happened ...
For the present implementation: GetServerTimeStamp->NowUTC->GetSystemTime, there is no guarantee that it will be ever-increasing. Even the client is perfectly synchronized with the server, the discontinuity can happen on the server itself.
IMHO the only time-related function suitable is, perhaps, the GetTickCount since it is a 'count' and not 'time', but it is pointless because we can just get series of integers, N, as long we're not interested in the duration between each two instants.
#55 Re: mORMot 1 » Time-sortable, 64bit UUID generation, 10 millions without collision » 2015-04-19 13:10:50
I must say I am really not good at this kind of thing, what I actually mean is that as opposed using pure random numbers for PKs, the timestamp+random PKs increase over time - in other worlds, a new record in most cases will have a greater ID value than the old ones.
Why not use Random just once for generating the NodeID instead of requiring the user to enter it (see earlier Arnaud proposal for ID's)? The chance of collision will be rather small.
I personally prefer to use much simpler scheme: per node increasing ID concatenated with the nodeID as insurance for uniqueness.
@Arnaud,
IMHO using time stamps can be quite tricky because of time zones and DST. AFAIK, Windows does not keep the system clock in UTC but makes a conversions, vice versa in Linux. Also it is prone to (potentially incorrect) adjustments from users or automatic DST. Not to even mention the leap seconds adjustment.
#56 Re: mORMot 1 » Time-sortable, 64bit UUID generation, 10 millions without collision » 2015-04-18 16:01:02
One advantage over full random number is that the UUIDs are timestamp-based and thus sort-able.
Nobody said they're not. Every number is.
Please, see that thread: http://synopse.info/forum/viewtopic.php?id=2160 and particularly that post: http://synopse.info/forum/viewtopic.php … 529#p13529
The main property of each random number generator is to have uniform distribution. In fact, I am not sure what will be the properties when you call Randomize/Random each time in pairs. May be you will get a dependence between the left 42-bits and right 22-bits because probably it gets the clock as source of entropy.
#57 mORMot 1 » Incorrect handling of sub-select » 2015-04-17 16:28:05
- alpinistbg
- Replies: 1
Hi Arnaud,
I have recently found that the where clause in form CreateAndFillPrepare(Rest, 'Col1=? AND Col2 IN (SELECT Col3 FROM OtherTable WHERE Col4=?)', [Val1, Val2]) does not work. I think the internal parser doesn't handle that particular case, just 'X IN (V1,V2,...)' case.
Regards,
#58 Re: mORMot 1 » 1M records tested, SQLite's random no. generator's really high quality » 2015-04-17 16:19:58
Using an ID with high level of entropy can literally devastate the structure of database files (or RAM cache - whichever you'd prefer). Ordering your records in the time domain will tend to load maximum number of B-pages into memory and will make meaningless all attempts to increase performance.
#59 Re: mORMot 1 » 1M records tested, SQLite's random no. generator's really high quality » 2015-04-17 15:37:42
Any one who considers arithmetical methods of producing random digits is, of course, in a state of sin.
Source: Wikiquote
#60 Re: mORMot 1 » BatchAdd and forced ID's » 2015-04-07 10:17:34
#61 Re: mORMot 1 » BatchAdd and forced ID's » 2015-04-06 21:51:33
IMHO, as long the transactions in sqlite can't be nested, they (it) shall be treated as mutual resource, i.e. through synchronization mechanism, perhaps with timed wait and some error code in case of failure.
Or may be some immediate return with result code like EWOULDBLOCK will be more appropriate?
I've tried with Database.AcquireWriteMode := amBackgroundThread; hoping it will serialize the batches, but it has no effect. Trouble is that the situation persists and the program must be restarted.
#62 Re: mORMot 1 » BatchAdd and forced ID's » 2015-04-06 14:52:00
It seems that I just can't get it right! Please help and show me where I'm wrong ... using the above code (i.e. with BatchStartAny/BatchSend) and two client computers, after some time the server starts issuing warnings "TSQLRestServerDB.TransactionBegin failed -> no transaction" and "Active Transaction -> ignore AutomaticTransactionPerRow". By my observation that happens when the client computers overlap each other with their batches. Here is a little snip of the log (long INSERTs are truncated):
17:04:29 Enter TSQLRestServerDB(000CBFD0).URI(PUT repl/Batch inlen=129869)
17:04:29 Leave 00.000.038
17:04:29 Service call TSQLRestServerDB(000CBFD0) Batch
17:04:29 Enter TSQLRestServerDB(000CBFD0).0057C56E
17:04:29 Leave 00.000.032
17:04:29 Cache TSQLDatabase(000DC418) lpr_test_project_s.db3 cache flushed
17:04:29 SQL TSQLDatabase(000DC418) 59us lpr_test_project_s.db3 BEGIN TRANSACTION;
17:04:29 SQL TSQLRestServerDB(000CBFD0) 5.53ms INSERT INTO AlprRecognition (RowID,Site,Lane,Status,PlateText,PlateConfidenceX100,CreatedOn,Features,JSONResult,Snapshot) VALUES (:(108268719):,:('USER-PC'):,:(2):,:(1):,:('G2EEN'):,:(7432):,:(135237667995):,:(''):,:('{ "version" : 2, "data_type" : "alpr_results", "epoch_time" : -1895883568, "img_width" : 640, "img_height" : 512, "processing_time_ms" : 6.33002991000000E+002, "regions_of_interest" : [{ "x" : 0, "y" : 0, "width" : 640, "height" : 512 }], "results" : [{ "plate" : "G2EEN", "confidence" : 7.43161010000000E+001, "matches_template" : 0 ....
17:04:29 SQL TSQLRestServerDB(000CBFD0) 109us returned=43026 select max(rowid) from DataJournal
17:04:29 SQL TSQLRestServerDB(000CBFD0) 824us INSERT INTO DataJournal (RowID,SiteIdentifier,TableIdentifier,RecordKey,Operation,Stamp,DonorKey,DonorSiteIdentifier) VALUES (:(43027):,:(8719):,:(2):,:(108268719):,:(0):,:(135237667995):,:(9999):,:(8719):);
17:04:29 SQL TSQLDatabase(000DC418) 494.92ms lpr_test_project_s.db3 COMMIT TRANSACTION;
17:04:29 Server TSQLRestServerDB(000CBFD0) PUT repl/Batch SOA-Method -> 200 with outlen=17 in 501271 us
17:04:30 Enter TSQLRestServerDB(000CBFD0).URI(PUT repl/Batch inlen=273589)
17:04:30 Leave 00.000.042
17:04:30 Service call TSQLRestServerDB(000CBFD0) Batch
17:04:30 Enter TSQLRestServerDB(000CBFD0).0057C56E
17:04:30 Leave 00.000.015
17:04:30 SQL TSQLDatabase(000DC418) 24us lpr_test_project_s.db3 BEGIN TRANSACTION;
17:04:30 SQL TSQLRestServerDB(000CBFD0) 6.57ms INSERT INTO AlprRecognition (RowID,Site,Lane,Status,PlateText,PlateConfidenceX100,CreatedOn,Features,JSONResult,Snapshot) VALUES (:(371134149):,:('KA'):,:(4):,:(1):,:('4BM5'):,:(8249):,:(135237668437):,:(''):,:('{ "version" : 2, "data_type" : "alpr_results", "epoch_time" : -1895469568, "img_width" : 1280, "img_height" : 1024, "processing_time_ms" : 1.35053405800000E+003, "regions_of_interest" : [{ "x" : 0, "y" : 0, "width" : 1280, "height" : 1024 }], "results" : [{ "plate" : "4BM5", "confidence" : 8.24889530000000E+001, "matches_template" : 0 ....
17:04:30 SQL TSQLRestServerDB(000CBFD0) 156us returned=43027 select max(rowid) from DataJournal
17:04:30 SQL TSQLRestServerDB(000CBFD0) 1.00ms INSERT INTO DataJournal (RowID,SiteIdentifier,TableIdentifier,RecordKey,Operation,Stamp,DonorKey,DonorSiteIdentifier) VALUES (:(43028):,:(4149):,:(2):,:(371134149):,:(0):,:(135237668438):,:(30082):,:(4149):);
17:04:30 Enter TSQLRestServerDB(000CBFD0).URI(PUT repl/Batch inlen=89064)
17:04:30 Leave 00.000.155
17:04:30 Service call TSQLRestServerDB(000CBFD0) Batch
17:04:30 Enter TSQLRestServerDB(000CBFD0).0057C56E
17:04:30 Leave 00.000.017
17:04:30 SQL TSQLDatabase(000DC418) 394.19ms lpr_test_project_s.db3 COMMIT TRANSACTION;
17:04:30 Server TSQLRestServerDB(000CBFD0) PUT repl/Batch SOA-Method -> 200 with outlen=17 in 405556 us
17:04:30 SQL TSQLDatabase(000DC418) 391.81ms lpr_test_project_s.db3 ROLLBACK TRANSACTION;
17:04:30 Warning TSQLRestServerDB(000CBFD0) TSQLRestServerDB.EngineBatchSend: TSQLRestServerDB.TransactionBegin failed -> no transaction
17:04:30 Warning TSQLRestServerDB(000CBFD0) TSQLRestServerDB.EngineBatchSend: TSQLRestServerDB.TransactionBegin failed -> no transaction
17:04:31 SQL TSQLRestServerDB(000CBFD0) 503.38ms INSERT INTO AlprRecognition (RowID,Site,Lane,Status,PlateText,PlateConfidenceX100,CreatedOn,Features,JSONResult,Snapshot) VALUES (:(108278719):,:('USER-PC'):,:(1):,:(1):,:('H32O'):,:(7204):,:(135237668103):,:(''):,:('{ "version" : 2, "data_type" : "alpr_results", "epoch_time" : -1895783568, "img_width" : 640, "img_height" : 512, "processing_time_ms" : 4.96411011000000E+002, "regions_of_interest" : [{ "x" : 0, "y" : 0, "width" : 640, "height" : 512 }], "results" : [{ "plate" : "H32O", "confidence" : 7.20362930000000E+001, "matches_template" : 0 ....
17:04:31 SQL TSQLRestServerDB(000CBFD0) 242us returned=43028 select max(rowid) from DataJournal
17:04:31 SQL TSQLRestServerDB(000CBFD0) 249.60ms INSERT INTO DataJournal (RowID,SiteIdentifier,TableIdentifier,RecordKey,Operation,Stamp,DonorKey,DonorSiteIdentifier) VALUES (:(43029):,:(8719):,:(2):,:(108278719):,:(0):,:(135237668103):,:(10000):,:(8719):);
17:04:31 Server TSQLRestServerDB(000CBFD0) PUT repl/Batch SOA-Method -> 200 with outlen=17 in 1141657 us
17:04:32 Enter TSQLRestServerDB(000CBFD0).URI(PUT repl/Batch inlen=152689)
17:04:32 Leave 00.000.044
17:04:32 Service call TSQLRestServerDB(000CBFD0) Batch
17:04:32 Enter TSQLRestServerDB(000CBFD0).0057C56E
17:04:32 Leave 00.000.026
17:04:32 Warning TSQLRestServerDB(000CBFD0) Active Transaction -> ignore AutomaticTransactionPerRow
17:04:32 SQL TSQLRestServerDB(000CBFD0) 818.33ms INSERT INTO AlprRecognition (RowID,Site,Lane,Status,PlateText,PlateConfidenceX100,CreatedOn,Features,JSONResult,Snapshot) VALUES (:(108288719):,:('USER-PC'):,:(2):,:(1):,:('293H'):,:(6663):,:(135237668114):,:(''):,:('{ "version" : 2, "data_type" : "alpr_results", "epoch_time" : -1895772568, "img_width" : 640, "img_height" : 512, "processing_time_ms" : 6.83242981000000E+002, "regions_of_interest" : [{ "x" : 0, "y" : 0, "width" : 640, "height" : 512 }], "results" : [{ "plate" : "293H", "confidence" : 6.66304930000000E+001, "matches_template" : 0 ....
17:04:32 SQL TSQLRestServerDB(000CBFD0) 320us returned=43029 select max(rowid) from DataJournal
17:04:32 SQL TSQLRestServerDB(000CBFD0) 487.90ms INSERT INTO DataJournal (RowID,SiteIdentifier,TableIdentifier,RecordKey,Operation,Stamp,DonorKey,DonorSiteIdentifier) VALUES (:(43030):,:(8719):,:(2):,:(108288719):,:(0):,:(135237668114):,:(10001):,:(8719):);
17:04:32 Server TSQLRestServerDB(000CBFD0) PUT repl/Batch SOA-Method -> 200 with outlen=17 in 559313 us#63 Re: mORMot 1 » BatchAdd and forced ID's » 2015-03-31 10:29:14
I didn't say batches don't work, may be it was unclear, sorry. Transactions don't work. I've used them as a workaround because of the previous issue with the forced ID's.
To help implementing the replication pattern, I've added the TSQLRestTempStorage "asynchronous write" class.
But from your current implementation, I'm not sure it would be what you need.
So what's the idea?
#64 Re: mORMot 1 » BatchAdd and forced ID's » 2015-03-30 22:11:19
@ab,
Since I can't figure what is the most convenient way to comment on http://synopse.info/fossil/tktview?name=490f61ec8e, I'll just post some comments on that thread.
My feedback for the fix is fairly positive - now the BatchAdd() seems to work correctly with forced ID. I'll make more tests during the next days. Currently I'm trying to implement the replication pattern discussed in http://synopse.info/forum/viewtopic.php?id=2160. Because of the BatchAdd() issue, I have tried to use TransactionBegin/Commit/Rollback as a workaround.
Consider the following snippet:
Remote := TSQLHttpClient.Create(FRemoteHost, FRemotePort, RemoteModel);
...
{$IFDEF USETRN} if not Remote.TransactionBegin(Nil, 1) then
raise Exception.Create('(Remote TRN) ' + UTF8ToString(Remote.LastErrorMessage));
{$ELSE}if not Remote.BatchStartAny(MaxInt) then
raise Exception.Create('(Remote Batch)'); {$ENDIF}
try
case Journal.Operation of
TSQLEvent.seAdd:
if Database.Retrieve(Journal.RecordKey, Rec) then
SingleError :=
{$IFDEF USETRN} Remote.Add(Rec, True, True) <> Journal.RecordKey
{$ELSE}Remote.BatchAdd(Rec, True, True) < 0; {$ENDIF}
TSQLEvent.seUpdate:
if Database.Retrieve(Journal.RecordKey, Rec) then
SingleError :=
{$IFDEF USETRN} not Remote.Update(Rec)
{$ELSE}Remote.BatchUpdate(Rec) < 0; {$ENDIF}
TSQLEvent.seDelete:
SingleError :=
{$IFDEF USETRN} not Remote.Delete(TSQLAlprRecognition, Journal.RecordKey)
{$ELSE}Remote.BatchDelete(Journal.RecordKey) < 0; {$ENDIF}
end;
if SingleError then
raise Exception.Create(UTF8ToString(Remote.LastErrorMessage));
Journal.DonorKey := Journal.ID;
Journal.DonorSiteIdentifier := localSid;
{$IFDEF USETRN} if Remote.Add(Journal, True, False) = 0 then
raise Exception.Create(UTF8ToString(Remote.LastErrorMessage));
{$ELSE}if Remote.BatchAdd(Journal, True, False) < 0 then
raise Exception.Create('Batch Add'); {$ENDIF}
if Assigned(FOnProgress) then
Synchronize(@DoProgressEvent);
{$IFDEF USETRN} Remote.Commit(1);
{$ELSE}if Remote.BatchSend(IDs) <> HTML_SUCCESS then
raise Exception.Create(UTF8ToString(Remote.LastErrorMessage));
{$ENDIF}
except
{$IFDEF USETRN} Remote.RollBack(1);{$ENDIF}
raise;
end;It can be seen that using a conditional define USETRN can switch between batches and transactions. Surprisingly for me, the transactions also *failed* to ensure the atomicity of the update.
That code was executed once in 5 minutes with a rowset of 100 records. Each of the records was between 90-180K. Everything went just OK until the site connection speed dropped (because of INet provider), then client code failed with time-out error and on the next invocation failed to progress further because of a PK violation. Looking at the server side I've found that there is a record inserted but corresponding journal entry was not there (i.e. Remote.Add(Rec, True, True) succeeded but Remote.Add(Journal, True, False) failed).
The error popped up irregularly (each time fixing DB by hand) - it seems that the slow connection speed was causing this, and sometimes the failure was in Remote.TransactionBegin(Nil, 1).
I suspect some transaction primitives mishandling at the server side in case of a dropped/timed-out connection. Or may be I'm making it in a wrong way?
#65 mORMot 1 » SynSSPIAuth.pas gets linked against kernel32.dll's GetTickCount64 » 2015-03-13 13:35:11
- alpinistbg
- Replies: 1
It seems strange for me because it uses SynCommons.pas which does it's best to overcome the issue, but it struck me hard when I tried to start the executable on Windows Server 2003 R2. Works fine with SSPIAUTH definition removed from mORMot.pas.
#66 Re: mORMot 1 » BatchAdd and forced ID's » 2015-03-12 07:54:45
#67 mORMot 1 » BatchAdd and forced ID's » 2015-03-11 19:31:46
- alpinistbg
- Replies: 13
It seems that the TSQLRestClientURI.BatchAdd(Value, SendData, ForceID) doesn't work right when ForceID=true.
For example:
Remote := TSQLHttpClient.Create('localhost', '8080', Model);
...
R.ID := X;
Remote.Add(R, True, True); // Works fine
...
Remote.BatchStart(Nil);
R.ID := Y;
Remote.BatchAdd(R, True, True);
Remote.BatchSend(IDs); // Assigns a new ID<>Y#68 Re: mORMot 1 » Sample 17, DBCommon and FPC » 2015-01-22 17:59:17
It stops at SynDBVCL.pas line 377:
result := GetTableNameFromSQL(fCommandText);#69 mORMot 1 » Sample 17, DBCommon and FPC » 2015-01-22 12:40:21
- alpinistbg
- Replies: 4
Trying to compile sample 17 gives the following error:
..\..\Synopse\SynDBVCL.pas(74,3) Fatal: Can't find unit DBCommon used by SynDBVCLI am pretty sure it was able to compile at some point in the past...
#70 mORMot 1 » More TID overflows, unfortunately » 2015-01-16 14:40:14
- alpinistbg
- Replies: 1
R := TSQLRecord.Create([], 4294967296 + 1);
Client.Add(R, true, true);
R.FillPrepare(Client);
R.FillOne;
ShowMessage(Int64ToUtf8(R.ID));Shows '1'!
Overflow occurred somewhere in the round-trip. TSQLRecordFill.Fill suspected.
Edit:
List of suspected assignments:
mORMot.pas (18361,12) PID := GetInteger(IDColumn[aP]) else
mORMot.pas (18362,12) PID := GetInteger(PPUTF8Char(PtrInt(aPP)-FieldIndexNextPtr)^);
mORMot.pas (18371,17) result := GetInteger(IDColumn[i])-PID else
mORMot.pas (18372,17) result := GetInteger(PPUTF8Char(PtrInt(CI)-FieldIndexNextPtr)^)-PID;
mORMot.pas (18381,17) result := GetInteger(IDColumn[J])-PID else
mORMot.pas (18382,17) result := GetInteger(PPUTF8Char(PtrInt(CJ)-FieldIndexNextPtr)^)-PID;
mORMot.pas (18568,18) result := GetInteger(IDColumn[A])-GetInteger(IDColumn[b]);
mORMot.pas (21898,22) Dest.ID := GetInteger(aTableRow[TableIndex]) else
mORMot.pas (21909,21) aDest.ID := GetInteger(aTableRow[TableIndex]) else
mORMot.pas (22281,14) fID := GetInteger(Value) else begin
mORMot.pas (32040,14) aID := GetInteger(U^);
mORMot.pas (34423,12) V := GetInteger(PropValue,err);
mORMot.pas (34518,14) V := GetInteger(PropValue,err);
mORMot.pas (35965,14) aID := GetInteger(pointer(fProcessRest.OneFieldValue(Table,'RowID',#71 Re: mORMot 1 » JSONRetrieveIDField() overflow » 2015-01-16 10:01:09
JSONRetrieveIDField() returned a Int64 = TID now, so I do not think there is a problem, right?
Sorry, my fault! Playing with mORMotDB, SynDBODBC and I was stuck to the previous fossil revision.
#72 Re: mORMot 1 » JSONRetrieveIDField() overflow » 2015-01-15 14:09:04
Just see the type of the InsertedID local variable in the TSQLRestStorageExternal.ExecuteFromJSON() and how it gets assigned with the call of JSONRetrieveIDField() just a few lines after. Not much different, regardless the http://synopse.info/fossil/info/95068648b4.
#73 Re: mORMot 1 » Stored procedure output parameters » 2015-01-15 13:53:17
The behavior i wrote previously is also setting-dependend.
Getting the UpdateCound/RowCount as first resultset is optional. Have to study my MSSQL book again.. Back hole in my head now. Can't remember the option yet.
I believe you're talking about SET NOCOUNT {ON|OFF}. Yes, if you put it on the first line of the SP and you don't have any SELECT then you'll get the output values without the need of calling SQLMoreResults(). Thank you for reminding me that! It solves the case with my key generator SP.
Would this be interesting?
If you mean the issue with both resultset+output params, then IMHO it's not worth the efforts. Considering the peculiarities of each provider is too much work and that is just for doing right SP calls - something that is not encouraged for the framework.
So, maybe I am the sole man who tries to call MSSQL SP through mORMot, but my problem is somewhat private ... 
Edit:
@ab
Unfortunately the problem relapses in simple INSERT statement generated from TSQLRest.Add(). When there is an AFTER INSERT trigger then the method crashes with "Cursor in inconsistent state" message. The trigger body have INSERT in some side table, which adds an additional RowCount result, I suggest.
#74 Re: mORMot 1 » Stored procedure output parameters » 2015-01-15 12:31:56
That's weird! I've tested it with 'ODBC Driver 11 for SQL Server', which I believe is the last MSSQL ODBC provider, but my local server is MSSQL 2008 R2. The Sample 15 is OK. It must be something from the server itself.
#75 Re: mORMot 1 » Stored procedure output parameters » 2015-01-14 18:16:34
Check the latest version from http://synopse.info/fossil/timeline
But I was not able to fix
function TODBCStatement.Step(SeekFirst: boolean): boolean; .. SQL_NO_DATA: repeat until not Self.MoreResults;So I left the plain "exit" yet.
Now the sample 15 works as expected, but I guess your output parameter values won't be transferred.
We need further investigation.
You forgot to apply that piece:
Index: SynDBODBC.pas
==================================================================
--- SynDBODBC.pas
+++ SynDBODBC.pas
@@ -1200,1 +1200,5 @@
- Check(self,NumResultCols(fStatement,nCols),SQL_HANDLE_STMT,fStatement);
+ repeat
+ Check(self,NumResultCols(fStatement,nCols),SQL_HANDLE_STMT,fStatement);
+ if (nCols = 0) and not Self.MoreResults then
+ Break; // no more
+ until nCols > 0;It will not harm the sample 15 (checked!) but will allow the transfer of the output parameter when the procedure have no resultset to return, i.e. with plain exit at TODBCStatement.Step and above patch the stored procedure can have output parameter(s) or resultset, but not both. I think it is a bit of improvement.
#76 Re: mORMot 1 » JSONRetrieveIDField() overflow » 2015-01-14 18:12:08
You're welcome! Ditto for:
Index: SQLite3/mORMotDB.pas
==================================================================
--- SQLite3/mORMotDB.pas
+++ SQLite3/mORMotDB.pas
@@ -175,1 +175,1 @@
- fEngineLockedLastID: integer;
+ fEngineLockedLastID: TID;
@@ -190,1 +190,2 @@
- fBatchCapacity, fBatchCount, fBatchFirstAddedID: integer;
+ fBatchCapacity, fBatchCount: integer;
+ fBatchFirstAddedID: TID;
@@ -220,1 +221,1 @@
- function EngineLockedNextID: Integer; virtual;
+ function EngineLockedNextID: TID; virtual;
@@ -242,1 +243,1 @@
- function InternalBatchAdd(const aValue: RawUTF8; aID: TID): integer;
+ function InternalBatchAdd(const aValue: RawUTF8; aID: TID): TID;
@@ -259,1 +260,1 @@
- UpdatedID: TID): integer;
+ UpdatedID: TID): TID;
@@ -907,1 +908,1 @@
-function TSQLRestStorageExternal.EngineLockedNextID: Integer;
+function TSQLRestStorageExternal.EngineLockedNextID: TID;
@@ -1090,1 +1091,1 @@
- const aValue: RawUTF8; aID: TID): integer;
+ const aValue: RawUTF8; aID: TID): TID;
@@ -1647,1 +1648,1 @@
- const SentData: RawUTF8; Occasion: TSQLOccasion; UpdatedID: TID): integer;
+ const SentData: RawUTF8; Occasion: TSQLOccasion; UpdatedID: TID): TID;
@@ -1652,1 +1653,1 @@
- InsertedID, F: integer;
+ InsertedID: TID; F: integer;
@@ -1956,1 +1957,1 @@
-end.
+end.#77 Re: mORMot 1 » Stored procedure output parameters » 2015-01-14 16:17:33
Meanwhile, I have enabled ODBCSQLITEFIREBIRD and the Sample 15 crashed at TODBCStatement.BindColumns:
if (fColumnCount>0) or (fColData<>nil) then
raise EODBCException.CreateUTF8('%.BindColumns twice',[self]);And without the:
function TODBCStatement.Step(SeekFirst: boolean): boolean;
..
SQL_NO_DATA:
repeat until not Self.MoreResults;The MSSQL ODBC Driver will not transfer it's output parameter values.
#78 Re: mORMot 1 » Stored procedure output parameters » 2015-01-14 15:20:27
I tried the patch...
But it breaks the statement re-use.
For instance, the sample 15 is not working any more.
Can you be a bit more specific? Which one of the defines I must enable to reveal the issue?
I do not have broad experience with different DBMS systems, but as long as the MSSQL is concerned, you can't just "rewind" the statement and use it again. There is simply no such call as SQLLessResults. Of course, different modifications can be made in order to stop regressions.
#79 mORMot 1 » JSONRetrieveIDField() overflow » 2015-01-14 12:47:42
- alpinistbg
- Replies: 7
JSONRetrieveIDField() calls function GetInteger(P: PUTF8Char): PtrInt which overflows badly on '{"RowID":10073450777,...}' at SynCommons.pas:19215.
#80 Re: mORMot 1 » Stored procedure output parameters » 2015-01-14 11:27:14
Proposed changes:
in TODBCStatement.Prepare() removed BindColumns() call because it works with the very first result set which may be an insert/update/delete, thus NumResultCols=0
in TODBCStatement.ExecutePrepared() called BindColumns() just after the execution to bind to the actual result sets
in TODBCStatement.BindColumns() result sets scanned until one found with NumResultCols>0
in TODBCStatement.Step() if SQL_NO_DATA then the rest of the result sets skipped with MoreResults()
The last one (4.) is needed, because the output parameters are transferred after all resultsets were consumed. Finally, I can get my @out_key value
It is a bit weird, but it turns out that if you want to receive the value of an output parameters, you should say that you're expecting results and then to call Step() until it returns false.
Here is the diff:
Index: SynDBODBC.pas
==================================================================
--- SynDBODBC.pas
+++ SynDBODBC.pas
@@ -0,0 +225,1 @@
+ function MoreResults: boolean;
@@ -0,0 +872,2 @@
+ MoreResults: function(StatementHandle: SqlHStmt): SqlReturn;
+ {$ifdef MSWINDOWS} stdcall {$else} cdecl {$endif};
@@ -961,1 +964,1 @@
- ODBC_ENTRIES: array[0..62] of PChar =
+ ODBC_ENTRIES: array[0..63] of PChar =
@@ -0,0 +977,1 @@
+ 'SQLMoreResults',
@@ -0,0 +1194,1 @@
+ status: SqlReturn;
@@ -1194,1 +1199,5 @@
- Check(self,NumResultCols(fStatement,nCols),SQL_HANDLE_STMT,fStatement);
+ repeat
+ Check(self,NumResultCols(fStatement,nCols),SQL_HANDLE_STMT,fStatement);
+ if (nCols = 0) and not Self.MoreResults then
+ Break; // no more
+ until nCols > 0;
@@ -0,0 +1555,2 @@
+ if fExpectResults then
+ BindColumns;
@@ -0,0 +1596,11 @@
+
+function TODBCStatement.MoreResults: Boolean;
+var R: SqlReturn;
+begin
+ R := ODBC.MoreResults(fStatement);
+ case R of
+ SQL_NO_DATA: Result := False; // no more results
+ SQL_SUCCESS, SQL_SUCCESS_WITH_INFO: Result := True; // got next
+ else ODBC.Check(self, R, SQL_HANDLE_STMT, fStatement); // error
+ end;
+end;
@@ -1600,2 +0,0 @@
- if fExpectResults then
- BindColumns;
@@ -1627,1 +1647,2 @@
- SQL_NO_DATA:
+ SQL_NO_DATA: begin
+ while Self.MoreResults do {consume all result sets};
@@ -0,0 +1650,1 @@
+ end;#81 Re: mORMot 1 » Stored procedure output parameters » 2015-01-14 07:53:30
@EgonHugeist
Thank for the info! Actually my troubles are not with the Zeos, just tried it to see is there a different behavior than ODBC in my particular case. I am pretty aware of MSSQL multiple resultsets feature (or trouble?) since I've used it with the raw ODBC API and juggled with Fetch/MoreResults/NumResultCols.
@ab
TODBCStatement.BindColumns() calls SQLNumResultCols/SQLDescribeCol before the statement actually executed, results fetched, etc. Due to the frivolous T-SQL procedure semantics the metadata is unavailable at that point, SQLNumResultCols returns 0. Thus consuming of the dataset is not possible and the next statement will crash => only SP's with input parameters and no resultsets can be called.
Forgetting to note that MARS_Connection=yes into the connection string, then:
TODBCStatement - TODBCLib error: [HY000] [Microsoft][ODBC Driver 11 for SQL Server]Connection is busy with results for another command (0).If the user logged have no permission to read the DB metadata, the CreateMissingTables() attempts to create the external table and fails (the user probably don't have permission to modify it, either).
Despite I haven't tried much of mORMot+ODBC+MSSQL functionality, but IMHO the combination is suitable only for simple select/insert/update statements.
The simple thing I wanted was just to reuse my good old key generation procedure :(
#82 Re: mORMot 1 » Stored procedure output parameters » 2015-01-13 18:17:08
Perhaps SQL_C_SBIGINT is not handled by the provider.
Try to find out what may be wrong in TODBCStatement.ExecutePrepared.
Have tried with the latest ODBC driver 11 for SQL server. I can't find what's wrong in TODBCStatement.ExecutePrepared. It seems to me that params were bound successfully, excluding some tricks with SQL_C_CHAR/SQL_C_SBIGINT, which I do not understand completely.
Or change your procedure to define an input+output out_key parameter.
In the T-SQL params are input or output, the latter is actually input/output.
You may try with SynOleDB.pas unit instead of ODBC.
Tried with TOleDBMSSQLConnectionProperties, also with TSQLDBZEOSConnectionProperties - same results 
.
Moreover - putting a 'select @out_key' at the end of the procedure does not bring a resultset back (?!). From the three props only ZEOS detects the resultset with Stmt.Step().
But ZEOS redirects to some TZDBLibPreparedStatementEmulated which substitutes the ? parameters textually. No way of getting output parameter values IMHO.
Regards,
#83 Re: mORMot 1 » Stored procedure output parameters » 2015-01-13 12:16:05
Hi ab,
The following code:
CREATE procedure [dbo].[dbclone_genkey]
@out_key bigint output,
@in_increment bigint = 1
as
begin
...
set @out_key = @key * 10000 + @sid;
end
Props := TODBCConnectionProperties.Create('','DRIVER=SQL Server Native Client 10.0;'+...);
...
Stmt := Props.NewThreadSafeStatementPrepared('Exec dbclone_genkey ?,?', false, true);
Stmt.Bind(1, 666, paramInOut);
Stmt.Bind(2, I, paramIn);
Stmt.ExecutePrepared;
Stmt.ParamToVariant(1, V);
Result := Int64(V);Returns always 666, which is the value from Stmt.Bind(). The procedure call is performed and the result is quite different than 666.
What I'm missing?
#84 mORMot 1 » Stored procedure output parameters » 2015-01-13 08:21:04
- alpinistbg
- Replies: 19
Hi,
Does anybody knows how a value of an output parameter can be obtained? TSQLDBConnectionProperties.Execute() takes its Params as array of const. Thanks!
#85 Re: mORMot 1 » Firebird transactions » 2014-12-03 15:01:01
@ab,
Just FYI, If you find couple of free hours, I recommend you to read about the MVCC implementation in FB. You'll be surprised how simple and elegant is. Of course, I'm biased, because I have more than a decade experience with the enormous beast known as MSSQL 
.
#86 Re: mORMot 1 » Firebird transactions » 2014-12-03 11:22:47
I guess that in most cases, the hard commit could most always take place.
I'm a mORMot newbie! The hard commits invalidates the active cursors, if you're sure that it won't invalidate anything useful, then go for 'hard_commit=true' - it is even better!
The ref-counting was proposed as a stupid way to find when such a hard commit won't affect other statements (with possibly active cursors).
#87 Re: mORMot 1 » Firebird transactions » 2014-12-03 11:06:59
Yes, as I wrote in http://synopse.info/forum/viewtopic.php … 959#p13959, Daniel's proposal is a workable one. At least when used with short-lived connections.
My humble proposal was to augment a little the connection introducing a TSQLDBZEOSStatement Create/Destroy counter and to issue a hard commit when the counter decrements from one to zero. Unfortunately, there is no way to do this in the current Zeos implementation (all commits are either soft ones or hard ones). But may be it won't be better than just simply reconnecting.
About PostgreSQL, MVVC is used on implementation level, but not at API level.
I didn't got that!
PostgreSQL is IMHO the most standard DB (it passes all the SQL standard tests much better than MS SQL or Oracle, BTW).
Not surprised at all.
#88 Re: mORMot 1 » Firebird transactions » 2014-12-03 09:42:31
@ab
First, allow me to disagree a bit - the atomicity is a logical-level property, not at SQL or UOW level
1. Is 'hard_commit=true' needed in this case?
The problem is twofold. Since the FB exploits COW semantics, and the transactions are nothing more than ever-increasing version numbers, using soft commits will have the following implications:
A hanging connection will stop the garbage collector and the file storage will grow quickly; That is because the 'interesting' version number stays the same on soft commit;
Recent connection will always tend to win update conflicts in regard to the oldest one;
At the other hand, using hard commits will:
Invalidate all active datasets, since the 'interesting' version/transaction number will change on each hard commit;
Resolve update conflicts in their natural timely order as they should be;
But using that COW semantics, the FB entirely separates readers from writers and does not use any locks. It makes demanding operations as backups (and perhaps replication) to execute entirely in background.
Sorry for that I'm adding a little bit of frustration on this, but IMHO the issue will not pass until the handling of FB transactions in SynDBZeos got somewhat aligned with the mORMot model.
2. Is there any other DB supported by ZDBC which wouldn't implement the expected "auto-commit" feature?
Somebody with experience with PostgreSQL must give opinion on that, but AFAIK it uses similar MVCC model.
Regards,
Yuliyan
#89 Re: mORMot 1 » Firebird transactions » 2014-12-01 09:27:31
IMHO, the danielkuettner proposal is workable one. The developer can just briefly disconnect from time to time in order to achieve a hard commit.
Or may be it can be improved a little bit by implementing a simple tracking of TSQLDBZEOSStatement creation/destruction and issuing a hard commit (if Zeos permits that) when no active back-end datasets exists. Those commits are with minimal penalty in FB. After all, the mORMot policy is for client to consume the back-end datasets ASAP, so it might be a quick workaround.
#90 Re: mORMot 1 » Firebird transactions » 2014-11-30 23:15:11
I'm aware of soft and hard commits in FB, I wasn't in fact aware how Zeos work with the different back-ends. Using soft commits is a drawback. But we know that in general case the developer will allocate several transaction objects and will decide (wisely perhaps ) which one to use and for what.
We should consider the fact that mORMot wants to use the RDBMS back-end only for a simple table storage ... but, at the other hand - it wants to stay highly multi-concurrent ... I agree, it won't be a trivial task to map the FB transaction model to the SQLite3 one. I guess only Arnaud has the insight where to sprinkle explicit transactions at the ORM level.
for reading: transactions with til = read-read-commited for reading operations, those don't cause harm if retaining is used
It will hold the lowest interesting transaction number and will stop the sweeper. It may be a problem in update-intensive applications.
#91 Re: mORMot 1 » Firebird transactions » 2014-11-30 14:53:48
As far as I understand, Arnaud wants the following behaviour:
Group of SQL statements can be enclosed into explicit transaction, i.e. Begin, Stmt1,...,StmtN, Commit/Rollback;
Outside such explicit transactions, each SQL statement must be enclosed in its own implicit transaction, i.e. to be committed immediately;
Although the SQLite3, MSSQL and PostgreSQL works exactly that way, the Firebird works differently. It does not insert implicit Commits until somebody tell him to do so (isn't it for that AutoCommit?).
I thought we have amMainThread (e.g. for Fb). Wasn't this your suggestion for Fb? Then only MainConnection will be used by the threads.
IMHO, the amMainThread is to be used for the embedded FB. No reason to use the full FB server in such a crippled way. Furthermore, I don't think thats even necessary, because the embedded FB is claimed to be thread-safe.
#92 Re: mORMot 1 » Firebird transactions » 2014-11-29 16:55:30
I can confirm in the ZEOS/Firebird case this is the exact behaviour when AutoCommit := true and TransactionIsolation := tiReadCommited (although I don't know the isolation of SQLite3 - ReadCommited or Serializable). There is also no need of resetting/setting AutoCommit between StartTransaction/Commit - it is not in effect during the explicit transaction.
#93 Re: mORMot 1 » Firebird transactions » 2014-11-27 12:17:56
No, it is clearly stated in the documentation.
Either you fix a single TSQLRecord class, or you just set nil to BatchStart() parameter, and then you can add any kind of TSQLRecord to the Batch*() commands.
I apologize for my oversight for the nil parameter value, I think it would be good to mention that in the SAD with a brief example, because it is somewhat obscured into the TSQLRestBatch.Create() description.
Using the TSQLRestBatch and TSQLRest.BatchSend() goes again to the TSQLRest.TransactionBegin(), which in turn doesn't call TSQLDBZEOSConnection.StartTransaction(). IMHO there is no transaction support on nothing but SQLite3.
#94 Re: mORMot 1 » Firebird transactions » 2014-11-27 10:20:24
1. You should better use Batch mode + automatic transaction instead of manual transaction.
Okay, perhaps I should use the Unit-Of-Work pattern (SAD 11.3.3) but I can't realize from the documentation/sources/examples how to make it work with heterogeneous updates, i.e. updates on different TSQLRecord descendants in one unit-of-work (batch). We don't expect to have just a single entity in our business model, right?
#95 Re: mORMot 1 » Firebird transactions » 2014-11-27 08:21:19
Do we expect TSQLDBZEOSConnection.StartTransaction() to be invoked from TSQLRest.TransactionBegin()? Because it isn't.
aProps := TSQLDBZEOSConnectionProperties.Create('zdbc:firebird-2.5://127.0.0.1:3050',
'C:\AI\TMP\trymormot.fdb', 'SYSDBA', 'masterkey');
VirtualTableExternalRegisterAll(aModel, aProps);
Database := TSQLRestServerDB.Create(aModel, ':memory:', false);
TSQLRestServerDB(Database).CreateMissingTables;
...
Database.TransactionBegin(TSQLSampleRecord,1);
...Or maybe I am missing something?
#96 Re: mORMot 1 » Firebird transactions » 2014-11-26 21:42:38
I can see the same tiNone constant in procedures TSQLDBZEOSConnection.Commit/Rollback; may be they should be changed too. But I am still not sure exactly how to benefit most of the Firebird features.
#97 Re: mORMot 1 » Firebird transactions » 2014-11-26 16:54:17
@miab3,
Perhaps you wanted to put this link: http://www.firebirdsql.org/manual/ufb-cs-embedded.html, instead of a link related to metadata security
And a litle excerpt from the page:
You can have multiple embedded servers running at the same time, and you can have multiple apps connecting to the same embedded server. Having a regular server already running isn't a problem either. However, an embedded server locks a database file for its own exclusive use after successful connection. This means that you cannot access the same database from multiple embedded server processes simultaneously (or from any other servers, once an embedded server has locked the file).
But I don't think it is worth to discuss the embedded case.
BTW, I absolutely agree with your post, http://synopse.info/forum/viewtopic.php … 878#p13878, i.e. the transaction isolation level used in mORMot is inappropriate.
P.S. Found that changing the tiNone to tiReadCommited in SynDBZEOS.pas resolves completely the problem described at the beginning of the thread:
constructor TSQLDBZEOSConnection.Create(aProperties: TSQLDBConnectionProperties);
begin
inherited Create(aProperties);
fDatabase := DriverManager.GetConnectionWithParams(
(fProperties as TSQLDBZEOSConnectionProperties).fURL.URL,nil);
fDatabase.SetAutoCommit(true);
fDatabase.SetTransactionIsolation({tiNone}tiReadCommitted);
end;#98 Re: mORMot 1 » Firebird transactions » 2014-11-26 16:11:13
@miab3,
Not sure what do you mean by 'multi-user', but the embedded engine is at least multi-thread-capable: http://www.firebirdsql.org/en/firebird- … scription/
@ab,
Perhaps here I should give up
IMHO multi-version concurrency of the Firebird has the true potential for a record-breaking performance levels. To confine it in just one thread is akin to removing the main features of Firebird.
#99 Re: mORMot 1 » Firebird transactions » 2014-11-26 14:28:53
What restrictions have Firebird on updates?
My point was to show the simplest way to confuse the mORMot/Firebird. That was the quickest way to make two connections to the same database. Probably I can write Client/Server example to do this with just one server (as recommended) and 2+ clients to show the same behavior, but is it needed?
excerpt from: http://synopse.info/forum/viewtopic.php … 221#p12221
In short:
- mORMot.pas by default, lock all write access to the DB with a critical section;
- SynDBZeos.pas does create one connection to the DB per thread.
Perhaps the multi-thread abilities of Firebird client has some problems to scale at the mORMot pace.
#100 mORMot 1 » Firebird transactions » 2014-11-26 11:47:09
- alpinistbg
- Replies: 109
I've made a simple test with sample '01 - In Memory ORM'. Modified a little to use a Firebird DB and added a 3-rd button with:
procedure TForm1.UpdateButtonClick(Sender: TObject);
var Rec: TSQLSampleRecord;
begin
Rec := TSQLSampleRecord.Create(Database, OldID);
try
Rec.Question := StringToUTF8(QuestionMemo.Text);
if not Database.Update(Rec) then
ShowMessage('Error updating the data');
finally
Rec.Free;
end;
end;Here, OldID is the ID remembered from the previous TForm1.FindButtonClick.
When just one instance of the Project01.exe started, everything is fine - the record is updated as expected. When second instance started and record modified, the first instance of the program can't update any more:
---------------------------
Debugger Exception Notification
---------------------------
Project Project01.exe raised exception class EZSQLException with message 'SQL Error: deadlock update conflicts with concurrent update concurrent transaction number is 46139. Error Code: -913. deadlock The SQL: update SampleRecord set TIME1=?,NAME=?,QUESTION=? where ID=?; '. Process stopped. Use Step or Run to continue.
Enclosing the statements with:
Database.TransactionBegin(TSQLSampleRecord,1);
...
DataBase.Commit(1);doesn't help as I expected, see http://synopse.info/forum/viewtopic.php … 858#p13858. Any ideas?