Pages: 1

#1 2012-09-12 13:17:32

VojkoCendak
Member
From: Celje Slovenia
Registered: 2012-09-02
Posts: 90

MSSQL problem

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

Last edited by VojkoCendak (2012-09-12 19:53:43)

Offline

#2 2012-09-12 14:21:49

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,603
Website

Re: MSSQL problem

Your general Model / TSQLRecord definitions sounds fine.

But in order to find out what is the issue, some more details are needed.

What is the exact error / exception?

On which source code line?

Offline

#3 2012-09-12 19:51:09

VojkoCendak
Member
From: Celje Slovenia
Registered: 2012-09-02
Posts: 90

Re: MSSQL problem

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

Last edited by VojkoCendak (2012-09-12 19:56:21)

Offline

#4 2012-09-13 16:25:35

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,603
Website

Re: MSSQL problem

We do not have the SQL statements in the log.

Please add 

SynDBLog.Family.Level := LOG_VERBOSE;

Offline

#5 2012-09-13 20:43:22

VojkoCendak
Member
From: Celje Slovenia
Registered: 2012-09-02
Posts: 90

Re: MSSQL problem

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 ?

Offline

#6 2012-09-13 21:00:06

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,570
Website

Re: MSSQL problem

SynDBLog is a global variable in SynDB unit. There is very good tool for Delphi called GExperts http://www.gexperts.org/ - it allow to search content of all files in directory for some keyword, for example mORMot source directory for keyword SynDBLog. Or, in new IDE, use Ctrl+Shift+S

Last edited by mpv (2012-09-13 21:01:23)

Offline

#7 2012-09-14 05:30:45

VojkoCendak
Member
From: Celje Slovenia
Registered: 2012-09-02
Posts: 90

Re: MSSQL problem

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 !?

Offline

#8 2012-09-14 06:21:17

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,603
Website

Re: MSSQL problem

ESQLite3Exception(no such table: ALARMI) error is at SQLite3 / ORM level, not at MS SQL level.
It means that either your model does not have the corresponding class, or that no CreateMissingTables method call occured.

First drop the existing databases on disk.
And check the schema / user you are using at MS SQL level.

"Must declare the scalar variable" error sounds like a stored proc issue or a wrong parameter name.
I do not understand from where this @P6 parameter comes. There is no such @P6 parameter in the INSERT statement: we use position-based binding, with ? as identifier.

Offline

#9 2012-09-15 09:21:07

VojkoCendak
Member
From: Celje Slovenia
Registered: 2012-09-02
Posts: 90

Re: MSSQL problem

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 wink.

Thank you

Offline

Pages: 1

Board footer

Powered by FluxBB