Posts by VojkoCendak

VojkoCendak · mORMot 1

superbe.

Where can I get these files ? Tried in downloads but there are no changes in there.

VojkoCendak · mORMot 1

this is great. We're using already in production with success: easy, small and fast . Thank you for your great work.

- Is it possible to implement general retry mechanism on the level of interfaces,
so that it will always be ready to reconnect if possible not just do access violation.

Let's say: we use a lot in app:

  // this impl needs constant chekcing and recreating ===
  // initialization start
   FMySvc := nil;
   FClient:= TSQLite3HttpClientWinSock.Create(Host,IntToStr(Port),lmodel); // don't put connect method here, otherwise we have to
     // constantly recreate FClient. IMHO it's not needed. Open could be when first interface call is attempted.
   FClient.Services.Info(TypeInfo(IMySvc)).Get(FMySvc); // we just initialize at one point
   // this impl needs constant checingk and recreating ===

...
  // we want to use FMySvc interface all over application:
  FMySvc.MyMethod; // this statement should be able to ALWAYS try to reconnect in case of network failure
  // that way IMHO the client is rock solid

At current implementation we now have to have function where we always check whether the service
is available, or if something gets wrong, so that we are able to reconnect our selves.
1. This is messy
2. we can do it only one function for one service
3. we have to kill the client and recreate it again and again in case of failure

For example: I looked at the pipe client server sample and it didn't reconnect when the server was restarted in between.

VojkoCendak · mORMot 1

maybe I'm missing something., (Delphi2009ent)

We try to use TCrtSocket.
It is said is faster that Indy and blcksock, but the use of latter are running ok in
case of network disruption.

1. Issue: I cannot call close (it's private), if I want to -> I need to recreate object.

2. serious issue

    FTcp.OpenBind(Host,IntToStr(Port),false);  // this one works ok , it says cannot open socket ....
    // this one is strange
    FTcp.Open(Host,IntToStr(Port)); // after we have access violation ????

How can this be?
We just try to call same FTCP.Open(...) over and over untill it really opens and why AV comes in ?

please note that we used succesfully blcksock in same scenario and in production.

3. Could OpenBind have shorter timeout delay (no need to be so long) ?

VojkoCendak · mORMot 1

You can easily use your application-level session, then use it on server side using it as parameter to your services.

we already did that, our auth on client and that's it.
But we miss serverside statistics, eg. how many clients, which clients, ...

Or you can use the user id to identify the session.

I still don't understand how can we use this serverside?

I'm new to morm, so any code helpers would be nice.

VojkoCendak · mORMot 1

It was security at the URL RESTful level, not about the server-side itself.

ok. I undestrand. Thank you.

2. So in current implementation there is no way to have common session, right?

Is there any way I could manage several sessions serverside:
like my own ID for application which has several SetUser('',''), but knows they belong to same group ?

VojkoCendak · mORMot 1

It' really annoying:

1. beacuse we need to search a lot through the forum to find solutions
2. if I misspell I have to wait
3. you can buy almost anything but not time

thank you,
Vojko Cendak

VojkoCendak · mORMot 1

Is it really necessary to share the same exact session on multiple threads at the client side?

We use at least several threads to perform background tasks, which
need "same session data" serverside to control access to.
This could be ok, but we want to see how many users are in our case it would be at least 2 same logged users,
one normal and others in thread.

Is there a way to pass some addon data and attach it to session on login ?
Maybe then we could manage to group several connections to one group of sessions?

Client-Server authentication in mORMot was designed to be very secure, using a "challenge" pattern, with integrated token time out, and strong mathematical encryption of the password.
It tries to avoid most used attacks, like man-in-the-middle, replay, zombi sessions or brute force.
I think RO does not have such features, so can re-use an existing GUID - so IMHO it is a security issue, not a feature.

What about security editing would be more flexible, if the developer wants to?
Because if developer makes addon service methods to access Tauth tables, then
security is breached anyway.

VojkoCendak · mORMot 1

Hi,

why did you choose to open channel on create?

IMHO it's better to open it when we first call one method.

constructor TSQLite3HttpClientWinSock.Create(const aServer, aPort: AnsiString;
  aModel: TSQLModel);
begin
  inherited Create(aModel);
  fSocket := THttpClientSocket.Open(aServer,aPort); ===> WHY open here? why not when you first call it?
{$ifdef USETCPPREFIX}
  fSocket.TCPPrefix := 'magic';
{$endif}
  KeepAliveMS := 20000; // 20 seconds connection keep alive by default

and also you could build if there is network error (which IS normal) and each time user tries to execute it tries to reconnect.

Because THttpClientSocket.Open(aServer,aPort) is in contructor now, on error
the client gets destroyed, BUT we don't know

  aClient := TSQLite3HttpClientWinSock.Create(Host,IntToStr(Port),Model);
  except
    aClient:= nil;
    raise;
  end;

.... and code
  if aClient<>nil then ==> unnecessary, why? aClient should be always ready to work ;)
  ...

IMHO it's more clean:

aClient := TSQLite3HttpClientWinSock.Create(Host,IntToStr(Port),Model).
aClient.Services['Potopne_Svc'].Get(CN); ==> or at least here
try
 CN.MyMethod; ==> here you open the channel
except
  ...
end;

VojkoCendak · mORMot 1

Hi,

How can we use TSQLite3HttpClientWinSock clients in several threads?
- we login in main thread. How can we "transfer authorisation" from main thread client to clients in threads, which poll in background?
In RO there is neat way to make each client same with GUID. we just copied same GUID to threads and that's it.

VojkoCendak · mORMot 1

I think we went of track:
Usage serverside:
If the service method is synchronized and called within main thread then aClient's .Get(myIntf) -> myIntf.mymethod gets stuck. ==> here's error (serverside).
aClient is TSQLite3HttpClientWinSock for the time being.
It's beacuse of this error we are forced to use FDB (serverside) and with IFDEFS.
In RO server is just activated and that's it and then we use "clients" serverside and clientside normally.

2. also FDB stays create till the end but aClient:=TSQLite3HttpClientWinSock.Create(Host,IntToStr(Port),Model) can:
a. be instantained on demand
b. because of network problems, we must recreate aClient again and again

I wish my lack of undestranding of framework could be better.
IMHO documentation could be extended with precise and clear practical usage and at the end the detailed theory,
comparisons, blogs, documents, opinions. Help copied form code sucks or initial learning form source also. Last one is last resort.

VojkoCendak · mORMot 1

Yes if our client would run only on server .
I know it's faster to use direct connection on server and on client like TCPIP, HTTP.
We used to have serveral protocols: in server, clients on server computer and clients over network,
but it's much more complicated instead of using just one property like Host and that's it.
Speed optimization is done in other areas...

thank you

VojkoCendak · mORMot 1

=== What I still can't understand is that the synchronization take place at the server level only,

We use same client code serverside and on the client side (let's say some diagnostic forms, ....).
We don't want to use in the server like from TSQLRestServerDB.Create(FDBModel,MyModulePath+'\data\'+MyExeName+'.db3',False);) -> and call methods directly
and then on the client same call with TSQLite3HttpClientWinSock.Create(Host,IntToStr(Port),Model) -> call same methods from actual client.

=== so I do not find out why there is an issue depending on the client side...

So if we use TSQLite3HttpClientWinSock.Create(Host,IntToStr(Port),Model) SERVERSIDE and call it from main thread , the call is stuck, meaning
it doesn't get called on server, where as if the method is not syncronised with ExecuteInMainThread('..') it's ok.
I guess -> call (mainthr.) --> http--> dispatch call thread--> ... --> wants to synronise again with main thread and gets locked? I'm not sure.

We have this implementation we can use serverside and client side:

   
{$IFDEF SERVER} // code used on server
FDB := TSQLRestServerDB.Create(FDBModel,MyModulePath+'\data\'+MyExeName+'.db3',False);
{$ELSE} // code used on client ===> we want tu use this one also on the server
Server:= TSQLite3HttpClientWinSock.Create(Host,IntToStr(Port),Model);
{$ENDIF}


// actual implementation
{$IFDEF SERVER}
// call on server in same form
FDB.services['Potopne_Svc'].Get(CN);
{$ELSE}
// call on client 
Server.Services['Potopne_Svc'].Get(CN)
{$ENDIF}
CN.mymethod

But it's kind of ugly and we have to maintain defines ...
Thank you for your time

VojkoCendak · mORMot 1

- no, it was meant to show it is TSQLite3HttpClientWinSock class only

- means ...
sorry I should be more clear.
"same" call from thread works but NOT from mainthread. Maybe it's synhronization issue.

VojkoCendak · mORMot 1

=== What gets stuck? The client or the server side?
ServerSide with a ServerSide implementation.
1. MainThread
2. TSQLite3HttpClientWinSock(Server).Services['mymainthreadservice'].Get(ImySvc)
3. call to ImySvc.mymethod ==> stuck here

SAME call from TThread.Execute --> OK;

VojkoCendak · mORMot 1

Yes it's working very good.

Just another possible improvement:
If I call with TSQLite3HttpClientWinSock from the main thread it gets stuck.
If I call it from anothen client app's main thread works ok. Also works ok on the server called form a thread.

Could you make a check whether it was called from the main thread and then not use the syncronise ?

VojkoCendak · mORMot 1

Super, Can't wait to see it working in object properties.

VojkoCendak · mORMot 1

They handle one-dimensional array of variants max.

VojkoCendak · mORMot 1

Thank you for reply.

Ok I see.

How can we make something like this:

class procedure TCustomTagsSer.FVClassWriter(...)
....
// write a complete object ?
aSerializer.WriteObject(V);  // this doesn't work because of callback. any idea how to hack this ?
// and then or between .... ?
// unpublished properties ...
aSerializer.AddCustomProperties(['PropName1', myvalue1, ....]);
....

class procedure TCustomTagsSer.FVClassReader(...)
....
// write a complete object ?
DecodeObject(V);
// and then or between .... ?
// unpublished properties ...
DecodeCustomProperties(['PropName1', myvalue1, ....]);
....

The thing is we have a lot of different ancestors.

We could implelement one dummy published string property and assign it in the service when we poll the service (not in the streamer),
and then decode it on the client, but it's ugly ...

Any ideas appretiated.

thank you,
Vojko

VojkoCendak · mORMot 1

It works with normal properties like integer, string, but not (yet ) with classes.
Delphi has GetObjectProp() if TTypeKind = tkClass and get set procedure is <> nil.

thank you,
Vojko

VojkoCendak · mORMot 1

Hi,

I don't quite understand this RegisterCustomSerializer yet.
It seems that it completely overrides the original serializer.
Is this true?

It would be VERY nice to implement possibility to add/remove
some selected properties.

thank you,
Vojko

VojkoCendak · mORMot 1

Hi, Delphi20009ent

We have TInterfacedCollection class with internal TPersistent objects.
If we leave plain syntax without getScale and setScale it's ok. Is this intended?

TCustomScale=Class(TPersistent);


myObject(TInterfacedCollection)
published
....
  property Scaling           : TCustomScale read FScale Write FScale; // this is OK
...

but if I use

....
  property Scaling           : TCustomScale read getScale Write setScale; // access violation
...

Eureka Log:

2.1 Date : Wed, 31 Oct 2012 17:59:17 +0100
2.2 Address : 004D7617
2.3 Module Name : EMRASvr.exe
2.4 Module Version:
2.5 Type : EAccessViolation
2.6 Message : Access violation at address 004D7617 in module 'EMRASvr.exe'. Read of address 00000000.
2.7 ID : 3917
2.8 Count : 1
2.9 Status : New
2.10 Note :

Address Module Unit Class Procedure/Method Line Rel. line
004CEB52 EMRASvr.exe SQLite3Commons.pas JSONToObject 21634 3
004CEB18 EMRASvr.exe SQLite3Commons.pas JSONToObject 21631 0
004CF34D EMRASvr.exe SQLite3Commons.pas JSONToObject 21884 253
004CEB18 EMRASvr.exe SQLite3Commons.pas JSONToObject 21631 0
004CEE0C EMRASvr.exe SQLite3Commons.pas JSONToObject 21718 87
004CEB18 EMRASvr.exe SQLite3Commons.pas JSONToObject 21631 0

if (Value=nil) or (PPointer(Value)^=nil) then
exit;
..
// will handle '[TCollection...' '[TStrings...' '{TObject...'
From := JSONToObject(PObject(P^.GetFieldAddr(Value))^,From,NestedValid);

CollItem := Coll.Add;
From := JSONToObject(CollItem,From,NestedValid); <=== access violation (wants to update Scaling object property)

Thank you,
Vojko

VojkoCendak · mORMot 1

thank you.

In RO and Kbm there is one dimensional streaming, meaning
pure single value variant or max one dimensional array in variant.

VojkoCendak · mORMot 1

Hi, (Delphi2009ent)

1. Is there a way to use variant properties in services?
We have a TCollectionItem with variant property and it doesn't stream over ...

2. Can we implement streamer for a particular datatype in general?

thank you,
Vojko Cendak

VojkoCendak · mORMot 1

Maybe just a global setting or when HumanReadable=False ?.
Defined IS part of Delphi's component structure.
IMHO it's better to get accurate object snapshot.

VojkoCendak · mORMot 1

I'm just informing yout that the code with mainthread synchronisation is working in production full time for several weeks.
Than you

VojkoCendak · mORMot 1

I made a mistake in copying the text:
This is the code:

    result := TSQLite3HttpClientWinSock.Create(FHost,IntToStr(FPort),Fmodel);
    try
       result.SetUser('User','synopse');
       result.ServiceRegister([TypeInfo(IEMRAsvr_tags)],sicShared);
  except
    // ===> on except (no connection to server) actual result is undefined (debugger)
    // in debugger it (TSQLite3HttpClientWinSock) destroys !? How is that possible ?
    raise;
  end;

When I step through the debugger and I don't call Free to destroy the instance.
Destroy is called before raise. Maybe I'm missing something

VojkoCendak · mORMot 1

Hi, (D2009 ent)

Strange behaviour, when there is no server:

    result := TSQLite3HttpClientWinSock.Create(FHost,IntToStr(FPort),Fmodel);
    try
       result := TSQLite3HttpClientWinSock.Create(FHost,IntToStr(FPort),Fmodel);
       result.SetUser('User','synopse');
       result.ServiceRegister([TypeInfo(IEMRAsvr_tags)],sicShared);
  except
    // ===> on except (no connection to server) actual result is undefined (debugger)
    // in debugger it (TSQLite3HttpClientWinSock) destroys !? How is that possible ?
    raise;
  end;

Possible troubled source:

{ TSQLite3HttpClientWinSock }

constructor TSQLite3HttpClientWinSock.Create(const aServer, aPort: AnsiString;
  aModel: TSQLModel);
begin
  inherited Create(aModel);
  fSocket := THttpClientSocket.Open(aServer,aPort);  //===>
{$ifdef USETCPPREFIX}
  fSocket.TCPPrefix := 'magic';
{$endif}
  KeepAliveMS := 20000; // 20 seconds connection keep alive by default
{$ifdef COMPRESSSYNLZ}
   // SynLZ is very fast and efficient, perfect for a Delphi Client
   fSocket.RegisterCompress(CompressSynLZ);
{$endif}
{$ifdef COMPRESSDEFLATE}
   fSocket.RegisterCompress(CompressDeflate);
{$endif}
end;

destructor TSQLite3HttpClientWinSock.Destroy;
begin
   // ===> if THttpClientSocket.Open doesn't succeed fSocket remains nil
   // so here would be trouble
  fSocket.Free;
  inherited Destroy;
end;

VojkoCendak · mORMot 1

Hi, (Delphi2009 ent)

I noticed that DefineProperties are not stored by default in the streamer?
Was this intended?
We wanted to use in TComponent save/restore.

thank you,
Vojko

VojkoCendak · mORMot 1

Hi,

If it is possible:
Can you please advise us how to use your fast hash search on our existing TCollection?
We need to find some key strings as fast as possible.

thank you,
Vojko

VojkoCendak · mORMot 1

hi, (Delphi2009 ent)

After installing version 1.17:

we tried "01 - In Memory ORM", but retrieving doesn't work.
And possible others ...
1. I save one element
2. retrieve under the same name -> Not found

thank you,
Vojko

VojkoCendak · mORMot 1

My mistake. There was difference between db and actual definiton.

I tried to use table with ID but with no primary key and no not null (just timestamped data) to be faster.
That's why these mistakes .

Thank you

VojkoCendak · mORMot 1

Thank you,

  FMSSQL := TOleDBMSSQLConnectionProperties.Create('192.168.2.2\DELLVOJKO','sxSCADA', 'emrasql', 'emraop1');
    VirtualTableExternalRegister(FDBModel,TSQLTrendRecord,FMSSQL,'TrendRecord');
    VirtualTableExternalRegister(FDBModel,TSQLALARMI,FMSSQL,'Alarmi');

  fClient := TSQLRestClientDB.Create(FDBModel,nil,'test.db3',TSQLRestServerDB);

1. TSQLTRENDRECORD

  ltrend := TSQLTRENDRECORD.Create;
  ltrend.TAGNAME := 'tag11';
  ltrend.TIMESTAMP := Now;
  ltrend.TIMESTAMPDBL := Now;
  ltrend.QUALITY := 193;
  ltrend.DATASTR := '';
  fClient.TransactionBegin(TSQLTrendRecord);
  fClient.Add(ltrend,True);
  fClient.Add(ltrend,True);
  fClient.Add(ltrend,True);
  fClient.Commit();

=================== LOG for TSQLTRENDRECORD=======================

C:\Documents and Settings\Basic\My Documents\RAD Studio\Projects\Dom za Ostarele Celje\Delphi\bin\EMRASvr.exe 0.0.0.0 (2012-09-14 07:11:19)
Host=XPDELPHI2009 User=Basic CPU=1*0-6-10759 OS=2.3=5.1.2600 Wow64=0 Freq=3579545
TSynLog 1.17 2012-09-14T07:21:50

20120914 07215031 EXC   EIdHostRequired ("") at 00249160 IdException.EIdException.Toss (179)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 00004958 System.@HandleOnException 
20120914 07215055 EXC   Exception (": Dolžina Config stringa ni ustrezna. Število parametrov mora biti 4 do 5.") at 001F52B8 dvSynaSer.TdvSynaSer.ParseComportConnStr (392)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 000047D0 System.@HandleAnyException 0009E31B ExceptionLog.Call_HookedRaise (15678) 001F52B8 dvSynaSer.TdvSynaSer.ParseComportConnStr (392) 001F7EE6 DriverUtils.ComPortValid (415) 0023DC86 Driver_Modbus.TMod_Channel.setConnectionString (303) 00223168 CustomDriver.TCustomChannels.AddAChannel (1771) 0022E643 CustomDriver.TceEMRADriver.setDriverID (5856) 0002A726 TypInfo.SetUnicodeStrProp 002928ED NativeXmlObjectStorage.TsdXmlObjectReader.ReadProperty (1338) 002919A2 NativeXmlObjectStorage.TsdXmlObjectReader.ReadObject (1051) 00291661 NativeXmlObjectStorage.TsdXmlObjectReader.ReadComponent (957) 0058AFAF FORM_OPCSvrU.TFORM_OPCSvr.LoadConfig (1079) 0058A02C FORM_OPCSvrU.TFORM_OPCSvr.FormCreate (867) 000F7A8F Forms.TCustomForm.DoCreate 000F76D7 Forms.TCustomForm.AfterConstruction 000F76AC Forms.TCustomForm.Create 0010187D Forms.TApplication.CreateForm 
20120914 07215113 EXC   Exception (": Dolžina Config stringa ni ustrezna. Število parametrov mora biti 4 do 5.") at 001F52B8 dvSynaSer.TdvSynaSer.ParseComportConnStr (392)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 000047D0 System.@HandleAnyException 0009E31B ExceptionLog.Call_HookedRaise (15678) 001F52B8 dvSynaSer.TdvSynaSer.ParseComportConnStr (392) 001F7EE6 DriverUtils.ComPortValid (415) 0023DC86 Driver_Modbus.TMod_Channel.setConnectionString (303) 00223168 CustomDriver.TCustomChannels.AddAChannel (1771) 0022E643 CustomDriver.TceEMRADriver.setDriverID (5856) 0002A726 TypInfo.SetUnicodeStrProp 002928ED NativeXmlObjectStorage.TsdXmlObjectReader.ReadProperty (1338) 002919A2 NativeXmlObjectStorage.TsdXmlObjectReader.ReadObject (1051) 00291661 NativeXmlObjectStorage.TsdXmlObjectReader.ReadComponent (957) 0058AFAF FORM_OPCSvrU.TFORM_OPCSvr.LoadConfig (1079) 0058A02C FORM_OPCSvrU.TFORM_OPCSvr.FormCreate (867) 000F7A8F Forms.TCustomForm.DoCreate 000F76D7 Forms.TCustomForm.AfterConstruction 000F76AC Forms.TCustomForm.Create 0010187D Forms.TApplication.CreateForm 
20120914 07215317  +    SynOleDB.TOleDBConnection(0377C238).0045D5BA SynOleDB.TOleDBConnection.Create (1822) 
20120914 07215317 info  	null
20120914 07215317  -    SynOleDB.TOleDBConnection(0377C238).0045D663 SynOleDB.TOleDBConnection.Create (1830)  00.000.008
20120914 07215317  +    SynOleDB.TOleDBConnection(0377C238).0045D314 SynOleDB.TOleDBConnection.Connect (1786) 
20120914 07215327  -    SynOleDB.TOleDBConnection(0377C238).0045D4E1 SynOleDB.TOleDBConnection.Connect (1817)  00.153.254
20120914 07215327  +    00294AED SynSQLite3.TSQLDatabase.DBOpen (3804) 
20120914 07215327  -    00294F83 SynSQLite3.TSQLDatabase.DBOpen (3877)  00.000.271
20120914 07215327  +    TSQLDatabase(033E8240).002942EC SynSQLite3.TSQLDatabase.Execute (3469) 
20120914 07215327 SQL   	BEGIN TRANSACTION;
20120914 07215327  -    TSQLDatabase(033E8240).0029435C SynSQLite3.TSQLDatabase.Execute (3478)  00.000.054
20120914 07215327 SQL   TSQLRestServerDB(01C30480) INSERT INTO TRENDRECORD (TIMESTAMP,TIMESTAMPDBL,TAGNAME,VALUE,QUALITY,DATASTR,ISDIGITAL) VALUES (:('2012-09-14T07:21:53'):,:(41166.3068684259):,:('tag11'):,:(0):,:(193):,:(''):,:(0):); prepared as INSERT INTO TRENDRECORD (TIMESTAMP,TIMESTAMPDBL,TAGNAME,VALUE,QUALITY,DATASTR,ISDIGITAL) VALUES (?,?,?,?,?,?,?); with 7 param
20120914 07215327  +    SynOleDB.TOleDBConnection(0135B238).0045D5BA SynOleDB.TOleDBConnection.Create (1822) 
20120914 07215327 info  	null
20120914 07215327  -    SynOleDB.TOleDBConnection(0135B238).0045D663 SynOleDB.TOleDBConnection.Create (1830)  00.000.239
20120914 07215327  +    SynOleDB.TOleDBConnection(0135B238).0045D314 SynOleDB.TOleDBConnection.Connect (1786) 
20120914 07215327  -    SynOleDB.TOleDBConnection(0135B238).0045D4E1 SynOleDB.TOleDBConnection.Connect (1817)  00.004.931
20120914 07215327  +    TOleDBStatement(01BF13B0).0045BCD3 SynOleDB.TOleDBStatement.ExecutePrepared (1416) 
20120914 07215327 SQL   	TOleDBStatement(01BF13B0) select GETDATE()
20120914 07215327  -    TOleDBStatement(01BF13B0).0045C272 SynOleDB.TOleDBStatement.ExecutePrepared (1519)  00.000.517
20120914 07215327  +    TOleDBStatement(01BF13B0).CreateAccessor
20120914 07215327  -    TOleDBStatement(01BF13B0).CreateAccessor 00.000.081
20120914 07215327  +    TOleDBStatement(01BF13B0).0045C8DD SynOleDB.TOleDBStatement.Destroy (1601) 
20120914 07215327 DB    	TOleDBStatement(01BF13B0) Total rows = 1
20120914 07215327  -    TOleDBStatement(01BF13B0).0045C963 SynOleDB.TOleDBStatement.Destroy (1611)  00.000.023
20120914 07215327  +    TOleDBStatement(01BF13B0).0045BCD3 SynOleDB.TOleDBStatement.ExecutePrepared (1416) 
20120914 07215327 SQL   	TOleDBStatement(01BF13B0) select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, 0 INDEX_COUNT from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'EMRASQL' and TABLE_NAME = 'TRENDRECORD'
20120914 07215327  -    TOleDBStatement(01BF13B0).0045C272 SynOleDB.TOleDBStatement.ExecutePrepared (1519)  00.000.620
20120914 07215327  +    TOleDBStatement(01BF13B0).CreateAccessor
20120914 07215327  -    TOleDBStatement(01BF13B0).CreateAccessor 00.000.032
20120914 07215327  +    TOleDBStatement(01BF13B0).0045C8DD SynOleDB.TOleDBStatement.Destroy (1601) 
20120914 07215327 DB    	TOleDBStatement(01BF13B0) Total rows = 0
20120914 07215327  -    TOleDBStatement(01BF13B0).0045C963 SynOleDB.TOleDBStatement.Destroy (1611)  00.000.017
20120914 07215327  +    TOleDBStatement(01BF13B0).CreateAccessor
20120914 07215327  -    TOleDBStatement(01BF13B0).CreateAccessor 00.000.048
20120914 07215327  +    TOleDBStatement(01BF13B0).0045C8DD SynOleDB.TOleDBStatement.Destroy (1601) 
20120914 07215327 DB    	TOleDBStatement(01BF13B0) Total rows = 7
20120914 07215327  -    TOleDBStatement(01BF13B0).0045C963 SynOleDB.TOleDBStatement.Destroy (1611)  00.000.005
20120914 07215327  +    TOleDBStatement(01BF13B0).CreateAccessor
20120914 07215327  -    TOleDBStatement(01BF13B0).CreateAccessor 00.000.002
20120914 07215327  +    TOleDBStatement(01BF13B0).0045C8DD SynOleDB.TOleDBStatement.Destroy (1601) 
20120914 07215327 DB    	TOleDBStatement(01BF13B0) Total rows = 1
20120914 07215327  -    TOleDBStatement(01BF13B0).0045C963 SynOleDB.TOleDBStatement.Destroy (1611)  00.000.058
20120914 07215327  +    TOleDBStatement(01BF1480).0045BCD3 SynOleDB.TOleDBStatement.ExecutePrepared (1416) 
20120914 07215327 SQL   	TOleDBStatement(01BF1480) select max(ID) from TrendRecord
20120914 07215327  -    TOleDBStatement(01BF1480).0045C272 SynOleDB.TOleDBStatement.ExecutePrepared (1519)  00.000.288
20120914 07215327  +    TOleDBStatement(01BF1480).CreateAccessor
20120914 07215327  -    TOleDBStatement(01BF1480).CreateAccessor 00.000.019
20120914 07215327  +    TOleDBStatement(01BF1480).0045C8DD SynOleDB.TOleDBStatement.Destroy (1601) 
20120914 07215327 DB    	TOleDBStatement(01BF1480) Total rows = 1
20120914 07215327  -    TOleDBStatement(01BF1480).0045C963 SynOleDB.TOleDBStatement.Destroy (1611)  00.000.015
20120914 07215327  +    TOleDBStatement(01BF1480).0045BCD3 SynOleDB.TOleDBStatement.ExecutePrepared (1416) 
20120914 07215327 SQL   	TOleDBStatement(01BF1480) insert into TrendRecord (TIMESTAMP,TIMESTAMPDBL,TAGNAME,VALUE,QUALITY,DATASTR,ISDIGITAL,ID) values ('2012-09-14 07:21:53',41166.3068684259,'tag11',0,193,NULL,0,1)
20120914 07215327 ERROR 	"EOleDBException(01337620)":"OLEDB Error 80040E14 -  (line 1): Must declare the scalar variable \"@P6\".\r\n (line 1): Incorrect syntax near ','.\r\n" stack trace API 0045DB7D SynOleDB.EnhancedTest (1917) 0045DCAA SynOleDB.TOleDBConnection.OleDBCheck (1922) 0045C200 SynOleDB.TOleDBStatement.ExecutePrepared (1512) 004622F5 SQLite3DB.TSQLRestServerStaticExternal.ExecuteDirectVarData (1024) 004633D5 SQLite3DB.TSQLVirtualTableExternal.Insert (1343) 002FA6A0 SQLite3.vt_Update (1643) 002B93C5 SynSQLite3.sqlite3_value_numeric_type (4641) 002B2002 SynSQLite3.sqlite3_result_value (4641) 002B211B SynSQLite3.sqlite3_step (4641) 00295DF6 SynSQLite3.TSQLRequest.Step (4414) 002F87EA SQLite3.TSQLRestServerDB.EngineExecute (864) 002F7D02 SQLite3.TSQLRestServerDB.EngineAdd (673) 0019CA53 SQLite3Commons.TSQLRestServer.URI (18547) 002F99FE SQLite3.TSQLRestClientDB.InternalURI (1353) 001982AC SQLite3Commons.TSQLRestClientURI.URI (16801) 0019883E SQLite3Commons.TSQLRestClientURI.EngineAdd (17067) 001A1110 SQLite3Commons.TSQLRestClient.Add (20907) 0058BBCA FORM_OPCSvrU.TFORM_OPCSvr.Timer3Timer (1331) 000EFF5E ExtCtrls.TTimer.Timer 
20120914 07215331 EXC   	EOleDBException ("OLEDB Error 80040E14 -  (line 1): Must declare the scalar variable \"@P6\".\r\n (line 1): Incorrect syntax near ','.\r\n") at 0045DB85 SynOleDB.EnhancedTest (1918)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 00004958 System.@HandleOnException 
20120914 07215331 ERROR 	"EOleDBException(01337620)":"OLEDB Error 80040E14 -  (line 1): Must declare the scalar variable \"@P6\".\r\n (line 1): Incorrect syntax near ','.\r\n" stack trace API 0045C22B SynOleDB.TOleDBStatement.ExecutePrepared (1516) 004622F5 SQLite3DB.TSQLRestServerStaticExternal.ExecuteDirectVarData (1024) 004633D5 SQLite3DB.TSQLVirtualTableExternal.Insert (1343) 002FA6A0 SQLite3.vt_Update (1643) 002B93C5 SynSQLite3.sqlite3_value_numeric_type (4641) 002B2002 SynSQLite3.sqlite3_result_value (4641) 002B211B SynSQLite3.sqlite3_step (4641) 00295DF6 SynSQLite3.TSQLRequest.Step (4414) 002F87EA SQLite3.TSQLRestServerDB.EngineExecute (864) 002F7D02 SQLite3.TSQLRestServerDB.EngineAdd (673) 0019CA53 SQLite3Commons.TSQLRestServer.URI (18547) 002F99FE SQLite3.TSQLRestClientDB.InternalURI (1353) 001982AC SQLite3Commons.TSQLRestClientURI.URI (16801) 0019883E SQLite3Commons.TSQLRestClientURI.EngineAdd (17067) 001A1110 SQLite3Commons.TSQLRestClient.Add (20907) 0058BBCA FORM_OPCSvrU.TFORM_OPCSvr.Timer3Timer (1331) 000EFF5E ExtCtrls.TTimer.Timer 0003DF3A Classes.StdWndProc 
20120914 07215331 EXC   	EOleDBException ("OLEDB Error 80040E14 -  (line 1): Must declare the scalar variable \"@P6\".\r\n (line 1): Incorrect syntax near ','.\r\n") at 0045DB85 SynOleDB.EnhancedTest (1918)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 00004958 System.@HandleOnException 
20120914 07215331  -    TOleDBStatement(01BF1480).0045C272 SynOleDB.TOleDBStatement.ExecutePrepared (1519)  00.059.352
20120914 07215331  +    TOleDBStatement(01BF1480).0045C8DD SynOleDB.TOleDBStatement.Destroy (1601) 
20120914 07215331 DB    	TOleDBStatement(01BF1480) Total rows = 0
20120914 07215331  -    TOleDBStatement(01BF1480).0045C963 SynOleDB.TOleDBStatement.Destroy (1611)  00.000.026
20120914 07215333 EXC   ESQLite3Exception ("SQL logic error or missing database") at 0029621F SynSQLite3.sqlite3_check (4485)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 00004958 System.@HandleOnException 
20120914 07215333 ERROR TSQLRestServerDB(01C30480) ESQLite3Exception(SQL logic error or missing database) for INSERT INTO TRENDRECORD (TIMESTAMP,TIMESTAMPDBL,TAGNAME,VALUE,QUALITY,DATASTR,ISDIGITAL) VALUES (:('2012-09-14T07:21:53'):,:(41166.3068684259):,:('tag11'):,:(0):,:(193):,:(''):,:(0):); stack trace API 002F88D7 SQLite3.TSQLRestServerDB.EngineExecute (886) 002F7D02 SQLite3.TSQLRestServerDB.EngineAdd (673) 0019CA53 SQLite3Commons.TSQLRestServer.URI (18547) 002F99FE SQLite3.TSQLRestClientDB.InternalURI (1353) 001982AC SQLite3Commons.TSQLRestClientURI.URI (16801) 0019883E SQLite3Commons.TSQLRestClientURI.EngineAdd (17067) 001A1110 SQLite3Commons.TSQLRestClient.Add (20907) 0058BBCA FORM_OPCSvrU.TFORM_OPCSvr.Timer3Timer (1331) 000EFF5E ExtCtrls.TTimer.Timer 0003DF3A Classes.StdWndProc 001016B2 Forms.TApplication.ProcessMessage 
20120914 07215333  +    TOleDBStatement(01BF1480).0045BCD3 SynOleDB.TOleDBStatement.ExecutePrepared (1416) 
20120914 07215333 SQL   	TOleDBStatement(01BF1480) select max(ID) from TrendRecord
20120914 07215333  -    TOleDBStatement(01BF1480).0045C272 SynOleDB.TOleDBStatement.ExecutePrepared (1519)  00.000.699
20120914 07215333  +    TOleDBStatement(01BF1480).CreateAccessor
20120914 07215333  -    TOleDBStatement(01BF1480).CreateAccessor 00.000.022
20120914 07215333  +    TOleDBStatement(01BF1480).0045C8DD SynOleDB.TOleDBStatement.Destroy (1601) 
20120914 07215333 DB    	TOleDBStatement(01BF1480) Total rows = 1
20120914 07215333  -    TOleDBStatement(01BF1480).0045C963 SynOleDB.TOleDBStatement.Destroy (1611)  00.000.019
20120914 07215333  +    TOleDBStatement(01BF1480).0045BCD3 SynOleDB.TOleDBStatement.ExecutePrepared (1416) 
20120914 07215333 SQL   	TOleDBStatement(01BF1480) select max(ID) from TrendRecord
20120914 07215333  -    TOleDBStatement(01BF1480).0045C272 SynOleDB.TOleDBStatement.ExecutePrepared (1519)  00.000.281
20120914 07215333  +    TOleDBStatement(01BF1480).CreateAccessor
20120914 07215333  -    TOleDBStatement(01BF1480).CreateAccessor 00.000.016
20120914 07215333  +    TOleDBStatement(01BF1480).0045C8DD SynOleDB.TOleDBStatement.Destroy (1601) 
20120914 07215333 DB    	TOleDBStatement(01BF1480) Total rows = 1
20120914 07215333  -    TOleDBStatement(01BF1480).0045C963 SynOleDB.TOleDBStatement.Destroy (1611)  00.000.015
20120914 07215333  +    TSQLDatabase(033E8240).002942EC SynSQLite3.TSQLDatabase.Execute (3469) 
20120914 07215333 SQL   	COMMIT TRANSACTION;
20120914 07215333  -    TSQLDatabase(033E8240).0029435C SynSQLite3.TSQLDatabase.Execute (3478)  00.000.042
20120914 07215333  +    SynOleDB.TOleDBConnection(0135B238).0045DCE9 SynOleDB.TOleDBConnection.Commit (1931) 
20120914 07215336 EXC   	ESQLDBException ("Invalid TOleDBConnection.Commit call") at 001AE7A4 SynDB.TSQLDBConnection.Commit (2455)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 00004958 System.@HandleOnException

2. TSQLAlarmi

  lrec := TSQLAlarmi.Create;
  lrec.TimeStamp := Now;
  lrec.TagName := 'tag1';
  lrec.ALARMTIP := 'ALARM';
  lrec.LOKACIJA := '';
  lrec.OPERATER := '';
  fClient.TransactionBegin(TSQLAlarmi);
  fClient.Add(lrec,True);
  fClient.Add(lrec,True);
  fClient.Add(lrec,True);
  fClient.Commit();

=================== LOG for TSQLAlarmi =======================

C:\Documents and Settings\Basic\My Documents\RAD Studio\Projects\Dom za Ostarele Celje\Delphi\bin\EMRASvr.exe 0.0.0.0 (2012-09-14 07:24:55)
Host=XPDELPHI2009 User=Basic CPU=1*0-6-10759 OS=2.3=5.1.2600 Wow64=0 Freq=3579545
TSynLog 1.17 2012-09-14T07:25:00

20120914 07250013 EXC   EIdHostRequired ("") at 00249160 IdException.EIdException.Toss (179)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 00004958 System.@HandleOnException 
20120914 07250030 EXC   Exception (": Dolžina Config stringa ni ustrezna. Število parametrov mora biti 4 do 5.") at 001F52B8 dvSynaSer.TdvSynaSer.ParseComportConnStr (392)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 000047D0 System.@HandleAnyException 0009E31B ExceptionLog.Call_HookedRaise (15678) 001F52B8 dvSynaSer.TdvSynaSer.ParseComportConnStr (392) 001F7EE6 DriverUtils.ComPortValid (415) 0023DC86 Driver_Modbus.TMod_Channel.setConnectionString (303) 00223168 CustomDriver.TCustomChannels.AddAChannel (1771) 0022E643 CustomDriver.TceEMRADriver.setDriverID (5856) 0002A726 TypInfo.SetUnicodeStrProp 002928ED NativeXmlObjectStorage.TsdXmlObjectReader.ReadProperty (1338) 002919A2 NativeXmlObjectStorage.TsdXmlObjectReader.ReadObject (1051) 00291661 NativeXmlObjectStorage.TsdXmlObjectReader.ReadComponent (957) 0058AFAF FORM_OPCSvrU.TFORM_OPCSvr.LoadConfig (1079) 0058A02C FORM_OPCSvrU.TFORM_OPCSvr.FormCreate (867) 000F7A8F Forms.TCustomForm.DoCreate 000F76D7 Forms.TCustomForm.AfterConstruction 000F76AC Forms.TCustomForm.Create 0010187D Forms.TApplication.CreateForm 
20120914 07250052 EXC   Exception (": Dolžina Config stringa ni ustrezna. Število parametrov mora biti 4 do 5.") at 001F52B8 dvSynaSer.TdvSynaSer.ParseComportConnStr (392)  stack trace API 00184D84 SynCommons.SynRtlUnwind (29392) 000047D0 System.@HandleAnyException 0009E31B ExceptionLog.Call_HookedRaise (15678) 001F52B8 dvSynaSer.TdvSynaSer.ParseComportConnStr (392) 001F7EE6 DriverUtils.ComPortValid (415) 0023DC86 Driver_Modbus.TMod_Channel.setConnectionString (303) 00223168 CustomDriver.TCustomChannels.AddAChannel (1771) 0022E643 CustomDriver.TceEMRADriver.setDriverID (5856) 0002A726 TypInfo.SetUnicodeStrProp 002928ED NativeXmlObjectStorage.TsdXmlObjectReader.ReadProperty (1338) 002919A2 NativeXmlObjectStorage.TsdXmlObjectReader.ReadObject (1051) 00291661 NativeXmlObjectStorage.TsdXmlObjectReader.ReadComponent (957) 0058AFAF FORM_OPCSvrU.TFORM_OPCSvr.LoadConfig (1079) 0058A02C FORM_OPCSvrU.TFORM_OPCSvr.FormCreate (867) 000F7A8F Forms.TCustomForm.DoCreate 000F76D7 Forms.TCustomForm.AfterConstruction 000F76AC Forms.TCustomForm.Create 0010187D Forms.TApplication.CreateForm 
20120914 07250255  +    SynOleDB.TOleDBConnection(0372E918).0045D5BA SynOleDB.TOleDBConnection.Create (1822) 
20120914 07250255 info  	null
20120914 07250255  -    SynOleDB.TOleDBConnection(0372E918).0045D663 SynOleDB.TOleDBConnection.Create (1830)  00.000.006
20120914 07250255  +    SynOleDB.TOleDBConnection(0372E918).0045D314 SynOleDB.TOleDBConnection.Connect (1786) 
20120914 07250304  -    SynOleDB.TOleDBConnection(0372E918).0045D4E1 SynOleDB.TOleDBConnection.Connect (1817)  00.185.265
20120914 07250307  +    00294AED SynSQLite3.TSQLDatabase.DBOpen (3804) 
20120914 07250308  -    00294F83 SynSQLite3.TSQLDatabase.DBOpen (3877)  00.011.634
20120914 07250309  +    TSQLDatabase(0135B160).002942EC SynSQLite3.TSQLDatabase.Execute (3469) 
20120914 07250309 SQL   	BEGIN TRANSACTION;
20120914 07250309  -    TSQLDatabase(0135B160).0029435C SynSQLite3.TSQLDatabase.Execute (3478)  00.001.482
20120914 07250309 SQL   TSQLRestServerDB(01C30100) INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (:('2012-09-14T07:25:03'):,:('tag1'):,:(0):,:('ALARM'):,:(''):,:(''):,:(''):); prepared as INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (?,?,?,?,?,?,?); with 7 param
20120914 07250311 ERROR TSQLRestServerDB(01C30100) ESQLite3Exception(no such table: ALARMI) for INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (:('2012-09-14T07:25:03'):,:('tag1'):,:(0):,:('ALARM'):,:(''):,:(''):,:(''):); stack trace API 002F88D7 SQLite3.TSQLRestServerDB.EngineExecute (886) 002F7D02 SQLite3.TSQLRestServerDB.EngineAdd (673) 0019CA53 SQLite3Commons.TSQLRestServer.URI (18547) 002F99FE SQLite3.TSQLRestClientDB.InternalURI (1353) 001982AC SQLite3Commons.TSQLRestClientURI.URI (16801) 0019883E SQLite3Commons.TSQLRestClientURI.EngineAdd (17067) 001A1110 SQLite3Commons.TSQLRestClient.Add (20907) 0058BBCF FORM_OPCSvrU.TFORM_OPCSvr.Timer3Timer (1344) 000EFF5E ExtCtrls.TTimer.Timer 0003DF3A Classes.StdWndProc 001016B2 Forms.TApplication.ProcessMessage 
20120914 07250311 SQL   TSQLRestServerDB(01C30100) INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (:('2012-09-14T07:25:03'):,:('tag1'):,:(0):,:('ALARM'):,:(''):,:(''):,:(''):); prepared as INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (?,?,?,?,?,?,?); with 7 param
20120914 07250312 ERROR TSQLRestServerDB(01C30100) ESQLite3Exception(no such table: ALARMI) for INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (:('2012-09-14T07:25:03'):,:('tag1'):,:(0):,:('ALARM'):,:(''):,:(''):,:(''):); stack trace API 002F88D7 SQLite3.TSQLRestServerDB.EngineExecute (886) 002F7D02 SQLite3.TSQLRestServerDB.EngineAdd (673) 0019CA53 SQLite3Commons.TSQLRestServer.URI (18547) 002F99FE SQLite3.TSQLRestClientDB.InternalURI (1353) 001982AC SQLite3Commons.TSQLRestClientURI.URI (16801) 0019883E SQLite3Commons.TSQLRestClientURI.EngineAdd (17067) 001A1110 SQLite3Commons.TSQLRestClient.Add (20907) 0058BBE4 FORM_OPCSvrU.TFORM_OPCSvr.Timer3Timer (1345) 000EFF5E ExtCtrls.TTimer.Timer 0003DF3A Classes.StdWndProc 001016B2 Forms.TApplication.ProcessMessage 
20120914 07250312 SQL   TSQLRestServerDB(01C30100) INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (:('2012-09-14T07:25:03'):,:('tag1'):,:(0):,:('ALARM'):,:(''):,:(''):,:(''):); prepared as INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (?,?,?,?,?,?,?); with 7 param
20120914 07250312 ERROR TSQLRestServerDB(01C30100) ESQLite3Exception(no such table: ALARMI) for INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (:('2012-09-14T07:25:03'):,:('tag1'):,:(0):,:('ALARM'):,:(''):,:(''):,:(''):); stack trace API 002F88D7 SQLite3.TSQLRestServerDB.EngineExecute (886) 002F7D02 SQLite3.TSQLRestServerDB.EngineAdd (673) 0019CA53 SQLite3Commons.TSQLRestServer.URI (18547) 002F99FE SQLite3.TSQLRestClientDB.InternalURI (1353) 001982AC SQLite3Commons.TSQLRestClientURI.URI (16801) 0019883E SQLite3

no such table: ALARMI ==> but table exists in MSSQL !?

VojkoCendak · mORMot 1

I use:
with TSQLLog.Family do begin
Level := LOG_VERBOSE;
//Level := [sllException,sllExceptionOS];
//PerThreadLog := true;
//HighResolutionTimeStamp := true;
//AutoFlushTimeOut := 5;
OnArchive := EventArchiveSynLZ;
//OnArchive := EventArchiveZip;
ArchiveAfterDays := 1; // archive after one day
end;

Where is SynDBLog ?

VojkoCendak · mORMot 1

Simpler test:

  TSQLTRENDRECORD = class(TSQLRecord)
  private
  published
    property TIMESTAMP: TDateTime read fTime write fTime;
    property TIMESTAMPDBL: Double read FTimeStamp write FTimeStamp;
    property TAGNAME: RawUTF8 index 100 read fTagName write fTagName;
    property VALUE: Double read fValue write fValue;
    property QUALITY: Integer read fQuality write fQuality;
    property DATASTR: RawUTF8 index 200 read fDataStr write fDataStr;
  end;

  FMSSQL := TOleDBMSSQLConnectionProperties.Create('192.168.7.127\DELLVOJKO','sxSCADA', 'emrasql', 'emraop1');
  if Assigned(FMSSQL) then begin
    VirtualTableExternalRegister(FDBModel,TSQLTrendRecord,FMSSQL,'TrendRecord');
    VirtualTableExternalRegister(FDBModel,TSQLALARMI,FMSSQL,'Alarmi');
  end;
  FMSSQL.NewConnection.Connect;
// 
  ltrend := TSQLTRENDRECORD.Create;
  ltrend.TAGNAME := 'tag11';
  ltrend.TIMESTAMP := Now;
  ltrend.TIMESTAMPDBL := Now;
  ltrend.QUALITY := 193;
  fClient.TransactionBegin(TSQLTrendRecord);
  fClient.Add(ltrend,True); // NOT  SAVED

=======
C:\Documents and Settings\Basic\My Documents\RAD Studio\Projects\Dom za Ostarele Celje\Delphi\bin\EMRASvr.exe 0.0.0.0 (2012-09-12 21:39:13)
Host=XPDELPHI2009 User=Basic CPU=1*0-6-10759 OS=2.3=5.1.2600 Wow64=0 Freq=3579545
TSQLLog 1.17 2012-09-12T21:42:31

20120912 21423156 + TSQLite3HttpServer(01237300).002F6DA8 SQLite3HttpServer.TSQLite3HttpServer.Create (295)
20120912 21423204 info TSQLite3HttpServer(01237300) THttpApiServer(0121D2B0) initialized
20120912 21423204 - TSQLite3HttpServer(01237300).002F716C SQLite3HttpServer.TSQLite3HttpServer.Create (367) 00.174.523
20120912 21423208 + TSQLRestClientDB(01ABAA20).TimeStamp
20120912 21423208 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21423208 + TSQLRestServerDB(01A60C20).TimeStamp
20120912 21423208 - TSQLRestServerDB(01A60C20).TimeStamp 00.000.003
20120912 21423208 srvr TSQLRestServerDB(01A60C20) GET root/TimeStamp -> 200
20120912 21423208 - 00197A15 SQLite3Commons.TSQLRestServer.URI (18690) 00.000.014
20120912 21423208 ret 135058119328
20120912 21423208 - TSQLRestClientDB(01ABAA20).TimeStamp 00.000.037
20120912 21423208 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21423208 srvr TSQLRestServerDB(01A60C20) BEGIN root/TRENDRECORD -> 200
20120912 21423208 - 00197A15 SQLite3Commons.TSQLRestServer.URI (18690) 00.000.021
20120912 21423513 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21423517 EXC EOleDBException ("OLEDB Error 80040E14 - (line 1): Must declare the scalar variable \"@P6\".\r\n (line 1): Incorrect syntax near ','.\r\n") at 00469915 SynOleDB.EnhancedTest (1918) stack trace API 0017F5B8 SynCommons.SynRtlUnwind (29392) 00004950 System.@HandleOnException
20120912 21423517 EXC EOleDBException ("OLEDB Error 80040E14 - (line 1): Must declare the scalar variable \"@P6\".\r\n (line 1): Incorrect syntax near ','.\r\n") at 00469915 SynOleDB.EnhancedTest (1918) stack trace API 0017F5B8 SynCommons.SynRtlUnwind (29392) 00004950 System.@HandleOnException
20120912 21423517 EXC ESQLite3Exception ("SQL logic error or missing database") at 0028A3F3 SynSQLite3.sqlite3_check (4485) stack trace API 0017F5B8 SynCommons.SynRtlUnwind (29392) 00004950 System.@HandleOnException

=======

  fClient.Add(ltrend,True); // ADDED in db !
  fClient.Add(ltrend,True); // ADDED in db !

  fClient.Commit();

==============================
20120912 21445731 srvr TSQLRestServerDB(01A60F20) POST root/TRENDRECORD ERROR=400 (Bad Request)
20120912 21445731 - 00197A15 SQLite3Commons.TSQLRestServer.URI (18690) 00.056.477
20120912 21450910 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21450911 srvr TSQLRestServerDB(01A60F20) POST root/TRENDRECORD -> 201
20120912 21450911 - 00197A15 SQLite3Commons.TSQLRestServer.URI (18690) 00.014.318
20120912 21451511 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21451512 srvr TSQLRestServerDB(01A60F20) POST root/TRENDRECORD -> 201
20120912 21451512 - 00197A15 SQLite3Commons.TSQLRestServer.URI (18690) 00.001.736
20120912 21451919 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21451920 EXC ESQLDBException ("Invalid TOleDBConnection.Commit call") at 00460C5C SynDB.TSQLDBConnection.Commit (2455) stack trace API 0017F5B8 SynCommons.SynRtlUnwind (29392) 00004950 System.@HandleOnException

==============================

// second table

  lrec := TSQLAlarmi.Create;
  lrec.TimeStamp := Now;
  lrec.TagName := 'tag1';
  lrec.ALARMTIP := 'ALARM';
//  lrec.ID := 3;
  fClient.TransactionBegin(TSQLAlarmi);
  fClient.Add(lrec,True); // NOT ADDED

================
20120912 21344735 srvr TSQLRestServerDB(01A60960) END root -> 200
20120912 21344735 - 00197A15 SQLite3Commons.TSQLRestServer.URI (18690) 00.047.168
20120912 21351154 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21351154 srvr TSQLRestServerDB(01A60960) BEGIN root/ALARMI -> 200
20120912 21351154 - 00197A15 SQLite3Commons.TSQLRestServer.URI (18690) 00.000.063
20120912 21351154 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21351155 EXC ESQLite3Exception ("no such table: ALARMI") at 0028A3F3 SynSQLite3.sqlite3_check (4485) stack trace API 0017F5B8 SynCommons.SynRtlUnwind (29392) 00004950 System.@HandleOnException
================

  fClient.Add(lrec,True); // NOT ADDED

================
20120912 21463514 srvr TSQLRestServerDB(01A60F20) POST root/ALARMI ERROR=400 (Bad Request)
20120912 21463514 - 00197A15 SQLite3Commons.TSQLRestServer.URI (18690) 00.002.569
20120912 21470525 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21470525 EXC ESQLite3Exception ("no such table: ALARMI") at 00289F7A SynSQLite3.TSQLRequest.Reset (4407) stack trace API 0017F5B8 SynCommons.SynRtlUnwind (29392) 00004950 System.@HandleOnException
================

  fClient.Add(lrec,True); // NOT ADDED

================
20120912 21470525 srvr TSQLRestServerDB(01A60F20) POST root/ALARMI ERROR=400 (Bad Request)
20120912 21470525 - 00197A15 SQLite3Commons.TSQLRestServer.URI (18690) 00.003.925
20120912 21475603 + 0019641F SQLite3Commons.TSQLRestServer.URI (18257)
20120912 21475604 EXC ESQLite3Exception ("no such table: ALARMI") at 00289F7A SynSQLite3.TSQLRequest.Reset (4407) stack trace API 0017F5B8 SynCommons.SynRtlUnwind (29392) 00004950 System.@HandleOnException
================

  fClient.Commit(); // nothing happens: no exception

thank you,
Vojko

VojkoCendak · mORMot 1

Hi (delphi2009ent)

We have MSSQL Express 2008 installed and we have following model:

  TSQLALARMI = class(TSQLRecord)
  private
   ...
  published
    // text v db
    property TIMESTAMP: TDateTime read fTime write fTime;
    property TAGNAME: RawUTF8 index 100 read fTagName write fTagName;
    property VALUE: Double read fValue write fValue;
    property ALARMTIP: RawUTF8 index 10 read fAlarmTip write fAlarmTip;
    property OPIS: RawUTF8 index 200 read fDataStr write fDataStr;
    property LOKACIJA: RawUTF8 index 200 read fLokacijaStr write fLokacijaStr;
    property OPERATER: RawUTF8 index 200 read fOperater write fOperater;
  end;


  FMSSQL := TOleDBMSSQLConnectionProperties.Create('192.168.7.127\DELLVOJKO','sxSCADA', 'emrasql', 'emraop1');
  if Assigned(FMSSQL) then begin
    VirtualTableExternalRegister(FDBModel,TSQLTrendRecord,FMSSQL,'TrendRecord');
    VirtualTableExternalRegister(FDBModel,TSQLALARMI,FMSSQL,'Alarmi');
  end;

  fClient := TSQLRestClientDB.Create(FDBModel,nil,'test.db3',TSQLRestServerDB);


==> Create Table works :), but insert doesn't:

// instert statement
  const aAlarmStatus: TdrvAlarmStatus);
var
  fClient:TSQLRestClientDB;
  lrec: TSQLALARMI;
begin
  fClient := TSQLRestClientDB.Create(FDBModel,nil,'test.db3',TSQLRestServerDB);
  try
    fClient.TransactionBegin(TSQLALARMI);
    try
    lrec := TSQLALARMI.Create;
    try
      lrec. ... := ...;
      fClient.Add(lrec,True);
    finally
      lrec.Free
    end;
    fClient.Commit(); // error ---> 'INSERT INTO ALARMI (TIMESTAMP,TAGNAME,VALUE,ALARMTIP,OPIS,LOKACIJA,OPERATER) VALUES (:('2012-09-12T15:13:42'):,:('BOJLERJI.iGr_bim'):,:(1):,:('ALARM'):,:(''):,:(''):,:(''):);'
    except
      fClient.RollBack();
    end;
  finally
    fClient.Free
  end;
end;

thank you,
Vojko

VojkoCendak · mORMot 1

=> DCOM - maintainance nightmare and what happens when network disrrupts (wait > 3minutes )

=> cache definetely

=> With publish subscribe, what will be impact on server and transport if
server would invoke each item separately, say 2000 times instead of invoking whole group :
1. server.Clients.InvokeClientCallback(MyTag1) 2000 times in a row
This is easier to implement, only I don't know what impact will be on the server and CPU.

2. server.Clients.InvokeClientCallback(Client[].GroupObjectWithTags[]) once a second or so ...

=> With in memory DataSet, how fast is Query on tags with 5000 rows for several hundred items
with index on TagName (string) ?
Or is better just to retreive whole table ?

Another question: What is the fastest way to implement TCollection or TList and to update it and then serialise it?

VojkoCendak · mORMot 1

Hi, (Delphi 2009ent)
We have a several TCollections of some Objects, which mainly are small in size like:
TOPCtag=Class
published
property Value:variant;
property TagName:String;
property Quality:WORD;
property TimeStamp:TDateTime;
....
end;

So clients subscribe to objects dynamicaly (not all) in groups, like
Client.Connect(['Tag1','Tag2',...]) and then server notifies Clients on DataChange with the whole Goup.

So we had problems with RTC, when we polled tags in goups around 2000 tags the CPU got too high almost 100%.

The scenaio has to be lightweight and responsive ideally . It does good job with COM as OPC server but only locally,
DCOM ... (I don't wanna go there ).

What would be the best implemetation for this scenario? Any suggestions appretiated.

VojkoCendak · mORMot 1

Delphi2009 ent,

function TSQLRestServerDB.EngineRetrieveBlob(Table: TSQLRecordClass;
aID: integer; BlobField: PPropInfo; out BlobData: TSQLRawBlob): boolean;
var SQL: RawUTF8;
Req: PSQLRequest;
begin
result := false;
if (self=nil) or (DB=nil) or (aID<=0) or (Table=nil) or not BlobField^.IsBlob then <=== [DCC Error] SQLite3.pas(1014): E2008 Incompatible types (it's BlobFiled...)
exit;

and

function TSQLRestServerStaticExternal.EngineRetrieveBlob(
Table: TSQLRecordClass; aID: integer; BlobField: PPropInfo;
out BlobData: TSQLRawBlob): boolean;
var Rows: ISQLDBRows;
begin
result := false;
if (self=nil) or (Table<>fStoredClass) or (aID<=0) or not BlobField^.IsBlob then
exit;

thynk you,
Vojko

VojkoCendak · mORMot 1

(Delphi 2009 ent)
We found that when we Save to db field TModTime with Iso8601FromDateTime(Now+OneSec),actual seconds do not increment in db field. It's kind of constant ?
And reverse Iso8601ToDateTime is not working. I mean DateTime is not equal.

thank you,
Vojko

VojkoCendak · mORMot 1

Hi, (Delphi2009 Ent).

We have several questions:
We'd like to use SQLite for several reasons, especially because it is simple and no installation and unlimited size...
Main concern is write lock on the db serverside.

1. help using SQLite3 db scenario:
FDB := TSQLRestServerDB.Create(FDBModel,MyModulePath+'\data\'+MyExeName+'.db3',False);
FDB.CreateMissingTables(0);
FDBServer := TSQLite3HttpServer.Create('8081',[FDB]);
<--- can I apply some write lock on table ?

regarding client taht appends records in server I use this one:
<-- can I use something directly?
ldbclient := TSQLite3HttpClient.Create(aHost,'8081',CreateTrendModel);
try
// ldbclient.Server.DB.Synchronous := smOff;
ldbclient.TransactionBegin(TSQLTrendRecord);
ldbclient.BatchStart(TSQLTrendRecord);
lrec := TSQLTrendRecord.Create;
try
while lvals.Count>0 do
begin
try
lrec.TagName := lvals[0].TagName;
ldbclient.BatchAdd(lrec,True);
finally
lvals.Delete(0);
end;
end;
finally
lrec.Free
end;
ldbclient.BatchSend(ida);
ldbclient.Commit();
Finally
ldbclient .Free
end;

2. Where can we apply or find smOff (for Synchronous) in this scenario?

3. Deleting data with Query: How?

lsql := 'Delete From TrendRecord Where TimeStampDbl <= '+FloatToStr(dtDo);
FDB.EngineExecuteAll(lsql);
But we find that it doesn't always delete data?

thank you for your patience and help,
Vojko

VojkoCendak · mORMot 1

In our case we don't deal only with COM clients per thread, but one server instance (like OPC server) per mORMot server.
I believe, OleDB is actuall client and not server.
So if we want to use COM server instance we need to synchronize all calls to it (COM does that automatically)
from (internal) clients like mORMot or RO in same server application.

VojkoCendak · mORMot 1

sure I totally understand.
We deal a lot with COM server objects, which are all in "main" thread and if we need then we do it with Thread.Synchronize.
I believe, the scenario is basically the same as with Shared Object with critical sections, except here is Synchronize.

It would be "nice" if framework could do it behind the scenes. We had the same problem with RO (Remote Objects), but
there we hacked it with special simple invoke object, which was kind of general for our situation.

The .NET solution is Invoke(...) (I think)

VojkoCendak · mORMot 1

Is it possible to automatically synronise preselected method with main thread?
It would be nice to do something like SynhronizedWithMainThread(['MyMethod1','MyMethod2',...]);
If not, is there a way to implement this somehow? Is there some central calling finction that could be
syncronized?

Thank you and all the best,
Vojko Cendak

mORMot Open Source

#51 Re: mORMot 1 » TSQLite3HttpClientWinSock open channel on create » 2012-12-03 22:16:23

#52 Re: mORMot 1 » TSQLite3HttpClientWinSock open channel on create » 2012-12-03 08:12:48

#53 Re: mORMot 1 » TSQLite3HttpClientWinSock open channel on create » 2012-12-02 01:09:50

#54 Re: mORMot 1 » Using several clients in threads and authorisation » 2012-12-01 08:11:30

#55 Re: mORMot 1 » Using several clients in threads and authorisation » 2012-11-20 17:14:20

#56 mORMot 1 » Is it possible to reduce waiting time for reapeating search » 2012-11-19 13:27:30

#57 Re: mORMot 1 » Using several clients in threads and authorisation » 2012-11-19 12:47:56

#58 mORMot 1 » TSQLite3HttpClientWinSock open channel on create » 2012-11-17 02:03:49

#59 mORMot 1 » Using several clients in threads and authorisation » 2012-11-17 01:49:54

#60 Re: mORMot 1 » Service method(s) synronization » 2012-11-17 01:41:07

#61 Re: mORMot 1 » Service method(s) synronization » 2012-11-16 15:52:02

#62 Re: mORMot 1 » Service method(s) synronization » 2012-11-15 22:24:48

#63 Re: mORMot 1 » Service method(s) synronization » 2012-11-15 13:30:46

#64 Re: mORMot 1 » Service method(s) synronization » 2012-11-15 09:44:33

#65 Re: mORMot 1 » Service method(s) synronization » 2012-11-14 20:48:00

#66 Re: mORMot 1 » Using variant properties in objects » 2012-11-12 08:26:23

#67 Re: mORMot 1 » Using variant properties in objects » 2012-11-01 13:41:55

#68 Re: mORMot 1 » Possibilty to just add to RegisterCustomSerializer? » 2012-11-01 13:37:22

#69 Re: mORMot 1 » Error ObjectToJSON with published object with getter/setter properties » 2012-11-01 13:23:18

#70 mORMot 1 » Possibilty to just add to RegisterCustomSerializer? » 2012-10-31 22:55:54

#71 mORMot 1 » Error ObjectToJSON with published object with getter/setter properties » 2012-10-31 17:07:22

#72 Re: mORMot 1 » Using variant properties in objects » 2012-10-30 16:56:57

#73 mORMot 1 » Using variant properties in objects » 2012-10-30 13:05:30

#74 Re: mORMot 1 » Saving defined properties » 2012-10-30 12:50:45

#75 Re: mORMot 1 » Service method(s) synronization » 2012-10-08 06:10:27

#76 Re: mORMot 1 » Issues with TSQLite3HttpClientWinSock client with no actual connection » 2012-09-22 00:37:39

#77 mORMot 1 » Issues with TSQLite3HttpClientWinSock client with no actual connection » 2012-09-18 20:39:48

#78 mORMot 1 » Saving defined properties » 2012-09-17 22:38:08

#79 mORMot 1 » Implementing your fast hash search on TCollection » 2012-09-17 22:35:03

#80 mORMot 1 » ver 1.17 "01 - In Memory ORM" does not work find » 2012-09-17 08:49:24

#81 Re: mORMot 1 » MSSQL problem » 2012-09-15 09:21:07

#82 Re: mORMot 1 » MSSQL problem » 2012-09-14 05:30:45

#83 Re: mORMot 1 » MSSQL problem » 2012-09-13 20:43:22

#84 Re: mORMot 1 » MSSQL problem » 2012-09-12 19:51:09

#85 mORMot 1 » MSSQL problem » 2012-09-12 13:17:32

#86 Re: mORMot 1 » Help with implementation Publish/Subscribe with lots of objects » 2012-09-10 13:16:41

#87 mORMot 1 » Help with implementation Publish/Subscribe with lots of objects » 2012-09-09 21:13:25

#88 Re: mORMot 1 » Service method(s) synronization » 2012-09-06 21:23:10

#89 mORMot 1 » SQLite3 DB TModTime problem » 2012-09-04 14:06:46

#90 mORMot 1 » SQLite Journal and smOff » 2012-09-04 14:01:07

#91 Re: mORMot 1 » Service method(s) synronization » 2012-09-03 13:33:43

#92 Re: mORMot 1 » Service method(s) synronization » 2012-09-02 20:15:54

#93 mORMot 1 » Service method(s) synronization » 2012-09-02 09:55:12

Board footer