Pages: 1

#1 2014-04-29 09:49:39

Sunny
Member
Registered: 2014-04-29
Posts: 7

mORMot + MS SQL Server + FireDAC + JSON

Would you show me a demo sample for the subject matter?  I have tried to code a program below but always show error message "Project ORM_MTT.exe raised exception class ESQLite3Exception with message 'no such table: LTN_DTL'." in debug mode when I connect to server with browser url="http://127.0.0.1/root/LTN_DTL".  The content in browser only shows below lines:

{
"ErrorCode":400,
"ErrorText":"Bad Request"
}

But I have tested that the server itself could query the table records from my MS SQL server with TSQLDBFireDACConnectionProperties and export them to a JSON file sucessfully.

Below is my source code:

-----------------------------------------------------------------------------------------------------------------------
unit Unit2;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ShellAPI,
  SynCommons, SynZip, SynCrtSock,
  mORMot, mORMotSQLite3, SynSQLite3Static,
  mORMotDB, SynDB, mORMotHttpServer,
  SynDBFireDac,
  FireDAC.Stan.Intf, FireDAC.Phys,
  FireDAC.Phys.ODBCBase, FireDAC.Phys.MSSQL,
  SynSQLite3;

type
  TSQLLTN_DTL = class(TSQLRecord)
  public
    fNO: RawUTF8;
    fLAT: real;
  published
    property NO: RawUTF8 read fNO write fNO;
    property LAT: real read fLATITUDE write fLAT;
  end;

  TForm1 = class(TForm)
    Label1: TLabel;
    btnQuit: TButton;
    Label2: TLabel;
    btnShowLogs: TButton;
    btnOpenBrowser: TButton;
    procedure btnQuitClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private

  public
    Props: TSQLDBFireDACConnectionProperties;
    Model: TSQLModel;
    DBServer: TSQLRestServerDB;
    HttpServer: TSQLHttpServer;

    //Conn: TSQLDBConnection;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}


{ TForm1 }

procedure TForm1.btnQuitClick(Sender: TObject);
begin
  Close;
end;

procedure TForm1.FormCreate(Sender: TObject);
var download: RawByteString;
begin

  Props := TSQLDBFireDACConnectionProperties.Create(FIREDAC_PROVIDER[dMSSQL]+'?CreateDatabase=No;Server=tcp:192.168.1.4,1433','sunnyORM','sa','1111'); 

  Model := TSQLModel.Create([TSQLLTN_DTL],'root');

  VirtualTableExternalRegister(Model,TSQLLTN_DTL,Props,'LTN_DTL');

  DBServer := TSQLRestServerDB.Create(Model,':memory:',false);

  HttpServer := TSQLHttpServer.Create('80',[DBServer],'+',useHttpApiRegisteringURI);

  HttpServer.AccessControlAllowOrigin := '*'; 
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  HttpServer.Free;
  DBServer.Free;
  Model.Free;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  if DBServer=nil then
    Close;
end;

end.
-----------------------------------------------------------------------------------------------------------------------

Please help !

Thanks in advance

Sunny

Offline

#2 2014-04-29 21:14:03

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

Re: mORMot + MS SQL Server + FireDAC + JSON

I suppose a good call to DBServer.CreateMissingTable just after DBServer initialization would do the trick.
(as stated by the doc and the samples)

AFAIK a "Props.Free" may be also missing in your OnDestroy event.

And do not use "real" as type in Delphi.
This is deprecated since Turbo Pascal days.
Use directly "double" instead.

Offline

#3 2014-04-30 03:29:56

Sunny
Member
Registered: 2014-04-29
Posts: 7

Re: mORMot + MS SQL Server + FireDAC + JSON

Thanks for your fast reply,

I have followed your steps but Mormot give me the following result:


TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NO - SQL="ALTER TABLE dbo.LTN_DTL ADD NO nvarchar(max)"


I don't know how to solve this case, my solution is MSSQL(back-end server)  + Mormot(App Server)(JSON) + Web browser to retrieve JSON data(Client side), it is common for REST application, I believe Mormot must be able do that, please point me to success !

Sunny

Offline

#4 2014-04-30 07:19:16

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

Re: mORMot + MS SQL Server + FireDAC + JSON

I suppose because NO is a SQL reserved word.
So it can't be used as a column name.

Offline

#5 2014-04-30 07:56:41

Sunny
Member
Registered: 2014-04-29
Posts: 7

Re: mORMot + MS SQL Server + FireDAC + JSON

Hi,

I  have change the field name to "NUMBER" yet, but still the same error message:

TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL="ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(max)"

May I send you the project with database for your reference?

Thanks!


Sunny  sad

Offline

#6 2014-04-30 08:39:50

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

Re: mORMot + MS SQL Server + FireDAC + JSON

I'm not a MS SQL expert.
Did the CREATE TABLE work as expected?
I wonder why the ALTER TABLE did not work.
Could you try to find out the expected syntax? Perhaps there is something particular with MS SQL.

You should better also specify a maximum column size for the RawUTF8 field, using "index 40" for instance if your number is 40 characters wide at most.
By default, a plain RawUTF8 create a nvarchar(max) which is a CLOB which will be much slower to access than a nvarchar(40) for instance.

Offline

#7 2014-04-30 09:10:13

Sunny
Member
Registered: 2014-04-29
Posts: 7

Re: mORMot + MS SQL Server + FireDAC + JSON

Thanks for your reply.

Sorry, I mean I have a stable table sitting in MS SQL server.  Thus I have remarked this line "DBServer.CreateMissingTables(0);".

Now, the server program starts up with showing logs on console.  But when I connect to the server progrm with browser with URL= http://127.0.0.1/root/LTN_DTL.  The below error persists on console:

EXC    ESQLite3Exception ("no such table: LTN_DTL") at stack trace API 00614AC8 0040A44C
res    null
srvr    GET root/LTN_TL ERROR=400 (Bad Request)


Our existing table structure (with Data) as shown below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LTN_DTL](
    [NUMBER] [nvarchar](10) NULL,
    [LX] [float] NULL,
    [LY] [float] NULL,
    [TE] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


Thanks!

Sunny

Offline

#8 2014-04-30 10:16:19

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

Re: mORMot + MS SQL Server + FireDAC + JSON

Could you see in the logs (enable them first if needed) what are the SQL Statements sent to MS SQL?

Offline

#9 2014-04-30 10:35:54

Sunny
Member
Registered: 2014-04-29
Posts: 7

Re: mORMot + MS SQL Server + FireDAC + JSON

Thank for your reply

below is the log but cannot find any SQL statement inside.

C:\Project\TEST mORMot\ORM_MobileATT\ORM_MobileATT.exe 1.0.0.0 (2014-04-30 18:34:21)
Host=WINXP-SUNNY User=Administrator CPU=2*0-6-5898 OS=2.3=5.1.2600 Wow64=0 Freq=2848170000
Environment variables=ALLUSERSPROFILE=C:\Documents and Settings\All Users    APPDATA=C:\Documents and Settings\Administrator\Application Data    BDS=c:\program files\embarcadero\rad studio\12.0    BDSAppDataBaseDir=BDS    BDSBIN=c:\program files\embarcadero\rad studio\12.0\bin    BDSCOMMONDIR=C:\Documents and Settings\All Users\Documents\RAD Studio\12.0    BDSINCLUDE=c:\program files\embarcadero\rad studio\12.0\include    BDSLIB=c:\program files\embarcadero\rad studio\12.0\lib    BDSPLATFORMSDKSDIR=C:\Documents and Settings\Administrator\My Documents\RAD Studio\SDKs    BDSPROFILESDIR=C:\Documents and Settings\Administrator\My Documents\RAD Studio\Profiles    BDSPROJECTSDIR=C:\Documents and Settings\Administrator\My Documents\RAD Studio\Projects    BDSUSERDIR=C:\Documents and Settings\Administrator\My Documents\RAD Studio\12.0    CommonProgramFiles=C:\Program Files\Common Files    COMPUTERNAME=WINXP-SUNNY    ComSpec=C:\WINDOWS\system32\cmd.exe    DELPHI=c:\program files\embarcadero\rad studio\12.0    DEMOSDIR=C:\Documents and Settings\All Users\Documents\RAD Studio\12.0\Samples\    FP_NO_HOST_CHECK=NO    HOMEDRIVE=C:    HOMEPATH=\Documents and Settings\Administrator    IBREDISTDIR=C:\Documents and Settings\All Users\Documents\InterBase\redist\InterBaseXE3    IB_Protocol=gds_db    InterBase=C:\Program Files\Embarcadero\RAD Studio\12.0\InterBaseXE3    IWCGJQCompsFiles=C:\Program Files\Embarcadero\RAD Studio\12.0\3rdParty\CGDevTools\IWCGJQComps\Files    J2D_D3D=false    LOGONSERVER=\\WINXP-SUNNY    NUMBER_OF_PROCESSORS=2    OS=Windows_NT    Path=C:\Documents and Settings\All Users\Documents\InterBase\redist\InterBaseXE3\win32_togo;C:\Documents and Settings\All Users\Documents\InterBase\redist\InterBaseXE3\win64_togo;C:\Program Files\CollabNet;C:\Program Files\Embarcadero\RAD Studio\12.0\bin;C:\Documents and Settings\All Users\Documents\RAD Studio\12.0\Bpl;C:\Program Files\Embarcadero\RAD Studio\12.0\bin64;C:\Documents and Settings\All Users\Documents\RAD Studio\12.0\Bpl\Win64;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Intel\DMIX;C:\Program Files\BTI\Win32\BIN;C:\Program Files\nodejs\;d:\Program Files\Embarcadero\HTML5 Builder\6.0\apache-ant-1.8.2\bin;d:\Program Files\Embarcadero\HTML5 Builder\6.0\apache-ant-1.8.2\bin;C:\Program Files\Tesseract-OCR;C:\Program Files\Embarcadero\RAD Studio\12.0\3rdParty\CGDevTools\RS19\lib\win32\release;C:\Program Files\CGDevTools\RS19\lib\win32\release;C:\Documents and Settings\Administrator\Application Data\npm    PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH    PROCESSOR_ARCHITECTURE=x86    PROCESSOR_IDENTIFIER=x86 Family 6 Model 23 Stepping 10, GenuineIntel    PROCESSOR_LEVEL=6    PROCESSOR_REVISION=170a    ProductVersion=12.0    ProgramFiles=C:\Program Files    SESSIONNAME=Console    SystemDrive=C:    SystemRoot=C:\WINDOWS    TEMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp    TESSDATA_PREFIX=C:\Program Files\Tesseract-OCR\    TMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp    USERDOMAIN=WINXP-SUNNY    USERNAME=Administrator    USERPROFILE=C:\Documents and Settings\Administrator    windir=C:\WINDOWS
TSQLLog 1.18 2014-04-30T18:34:22

00000000000003D1  +    0069A3C3
00000000056371AE  +        TSQLDatabase(00FE3850).00699482
00000000056422F4 SQL           PRAGMA cache_size=10000
00000000062DCFC9  -        00.004.656
00000000062E49CF  -    00.036.407
0000000006760B77  +    TSQLDatabase(00FE3850).00699990
000000000676D403 SQL       SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
0000000006802D07  -    00.000.233
0000000006805732 debug TSQLDatabase(00FE3850) TableNames=[]
0000000006813E1F  +    TSQLDatabase(00FE3850).00699482
000000000681480D SQL       BEGIN TRANSACTION;
00000000068205D7  -    00.000.017
00000000068302AE  +    TSQLDatabase(00FE3850).00699482
0000000006830B0C SQL       CREATE VIRTUAL TABLE LTN_DTL USING External(NUMBER TEXT COLLATE SYSTEMNOCASE);
000000001457A302  +        TSQLDBFireDACStatement(0143B980).007727A9
000000001459A3BB  +            TSQLDBFireDACConnection(00FCDD88).Connect to DriverID=MSSQL Database=sunnyORM
000000003980C271 DB                Connected to MSSQL (sunnyORM)
000000003981993B  -            00.218.868
0000000039B83F5F  -        00.220.172
0000000039BA78A4  +        TSQLDBFireDACStatement(0143B980).00772991
0000000039BB5348 SQL           TSQLDBFireDACStatement(0143B980) select GETDATE()
000000003A6D1FB8  -        00.004.110
000000003B4F3954  +        TSQLDBFireDACStatement(0143BA40).007727A9
000000003B50B436  -        00.000.034
000000003B50E8F8  +        TSQLDBFireDACStatement(0143BA40).00772991
000000003B5119A5 SQL           TSQLDBFireDACStatement(0143BA40) CREATE TABLE dbo.LTN_DTL (ID  int NOT NULL PRIMARY KEY,NUMBER nvarchar(10))
000000003B8E8758 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003B97C7A8 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A2C4 7C90327A 7C90E48A 007A368E 0087071F 0087079C 0087FD11 00863497 008635CB 008644E3 0086308E 0083AFB3 0085E4B6 008646F0 0082640D 008266D1 0082B566 0083D19E 00772BB4
000000003B98825B EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003B98FF33 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003B99BA1A EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A2C4 7C90327A 7C90E48A 007A368E 0087071F 0087079C 0087FD11 00863497 008635CB 008644E3 0086308E 0083AFB3 0085E4B6 008646F0 0082640D 008266D1 0082B566 0083D19E 00772BB4
000000003B9A698C EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003B9AFABE EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003B9CBCA5 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003B9D3788  -        00.001.755
000000003B9FA61F  +        TSQLDBFireDACStatement(0143BA40).007727A9
000000003BA05C1C  -        00.000.016
000000003BA069B9  +        TSQLDBFireDACStatement(0143BA40).00772991
000000003BA07877 SQL           TSQLDBFireDACStatement(0143BA40) ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)
000000003BBE3022 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003BBF5C4A EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A2C4 7C90327A 7C90E48A 007A368E 0087071F 0087079C 0087FD11 00863497 008635CB 008644E3 0086308E 0083AFB3 0085E4B6 008646F0 0082640D 008266D1 0082B566 0083D19E 00772BB4
000000003BC016DB EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003BC17EF2 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003BC25075 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A2C4 7C90327A 7C90E48A 007A368E 0087071F 0087079C 0087FD11 00863497 008635CB 008644E3 0086308E 0083AFB3 0085E4B6 008646F0 0082640D 008266D1 0082B566 0083D19E 00772BB4
000000003BC3135C EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003BC3AB32 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003BC63E72 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000003BC6C625  -        00.000.882
000000003BC91C80 EXC       EORMException ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0071F8AC  stack trace API 00614AC8 0040A2C4 7C90327A 7C90E48A 0071F8AC 006868AC 006A0FB8 006E916A 006E9507 006C76B5 006C06C9 006C07EF 0069C99D 0069B766 0069B813 006994C5 0069EA77 0088B742 005C6E3F 005C6A5F
000000003BCB3B1B EXC       EORMException ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0071F8AC  stack trace API 00614AC8 0040A2C4 7C90327A 7C90E48A 0071F8AC 006868AC 006A0FB8 006E916A 006E9507 006C76B5 006C06C9 006C07EF 0069C99D 0069B766 0069B813 006994C5 0069EA77 0088B742 005C6E3F 005C6A5F
000000003BCBF722 EXC       EORMException ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0071F8AC  stack trace API 00614AC8 0040A44C
000000003BCF909F EXC       ESQLite3Exception ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0069CED3  stack trace API 00614AC8 0040A44C
000000003BD06266  -    00.313.978
000000003BD0889D  +    TSQLDatabase(00FE3850).00699482
000000003BD0AE66 SQL       ROLLBACK TRANSACTION;
000000003BD4A288  -    00.000.094
000000003BD4DC12 EXC   ESQLite3Exception ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0069CED3  stack trace API 00614AC8 0040A2C4 7C90327A 7C90E48A 0069CED3 0069C9AA 0069B766 0069B813 006994C5 0069EA77 0088B742 005C6E3F 005C6A5F 005C6A10 005D1955 0089776C 7C816037



Thanks!

Sunny

Offline

#10 2014-04-30 11:11:46

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

Re: mORMot + MS SQL Server + FireDAC + JSON

Could you try with SynOleDB or SYnOdbc instead of Firedac?

Are you using latest nightly 1.18 version of mORMot?

Offline

#11 2014-05-02 03:27:07

Sunny
Member
Registered: 2014-04-29
Posts: 7

Re: mORMot + MS SQL Server + FireDAC + JSON

Thank for your reply.

Environment:
OS: WinXP SP3
Database: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)  (Build 3790: Service Pack 2)
Mormot version: nightly 1.18 @27 Apr 2014 7:42PM
EMB RAD: XE5
Method: FireDac

I have tested again with above source code and got the below result.

Result:

---------------------------
Orm_mobileatt
---------------------------
TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL="ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)"
---------------------------
OK   
---------------------------

D:\ORM_MobileATT.exe 1.0.0.0 (2014-05-02 11:13:50)Host=IVANDEMO User=Administrator CPU=4*9-6-5898 OS=5.2=5.2.3790 Wow64=1 Freq=2833000000Environment variables=ALLUSERSPROFILE=C:\Documents and Settings\All Users    APPDATA=C:\Documents and Settings\Administrator\Application Data    ClusterLog=C:\WINDOWS\Cluster\cluster.log    CommonProgramFiles=C:\Program Files (x86)\Common Files    CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files    CommonProgramW6432=C:\Program Files\Common Files    COMPUTERNAME=IVANDEMO    ComSpec=C:\WINDOWS\system32\cmd.exe    FP_NO_HOST_CHECK=NO    HOMEDRIVE=C:    HOMEPATH=\Documents and Settings\Administrator    LOGONSERVER=\\IVANDEMO    NUMBER_OF_PROCESSORS=4    OS=Windows_NT    Path=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\system32\WindowsPowerShell\v1.0;C:\Program Files (x86)\Common Files\Ulead Systems\MPEG;C:\Program Files (x86)\Pinnacle\Shared Files\;C:\Program Files (x86)\Pinnacle\Shared Files\Filter\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\    PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.PSC1    PROCESSOR_ARCHITECTURE=x86    PROCESSOR_ARCHITEW6432=AMD64    PROCESSOR_IDENTIFIER=EM64T Family 6 Model 23 Stepping 10, GenuineIntel    PROCESSOR_LEVEL=6    PROCESSOR_REVISION=170a    ProgramFiles=C:\Program Files (x86)    ProgramFiles(x86)=C:\Program Files (x86)    ProgramW6432=C:\Program Files    SESSIONNAME=Console    SystemDrive=C:    SystemRoot=C:\WINDOWS    TEMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp    TMP=C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp    USERDOMAIN=IVANDEMO    USERNAME=Administrator    USERPROFILE=C:\Documents and Settings\Administrator    windir=C:\WINDOWSTSQLLog 1.18 2014-05-02T11:16:02000000000000039F  +    0069A3C3
000000000002D45C  +        TSQLDatabase(02701FF0).00699482
000000000002E193 SQL           PRAGMA cache_size=10000
000000000006EC6B  -        00.000.094
000000000006F782  -    00.000.160
000000000007C3C6  +    TSQLDatabase(02701FF0).00699990
000000000007CC57 SQL       SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
0000000000092E51  -    00.000.032
000000000009388B debug TSQLDatabase(02701FF0) TableNames=[]
00000000000950FB  +    TSQLDatabase(02701FF0).00699482
00000000000958C0 SQL       BEGIN TRANSACTION;
0000000000099B97  -    00.000.006
000000000009D172  +    TSQLDatabase(02701FF0).00699482
000000000009D8AF SQL       CREATE VIRTUAL TABLE LTN_DTL USING External(NUMBER TEXT COLLATE SYSTEMNOCASE, LX FLOAT, LY FLOAT, TE TEXT COLLATE ISO8601);
000000000012255A  +        TSQLDBFireDACStatement(02A19A50).007727A9
000000000012402E  +            TSQLDBFireDACConnection(026EC3E8).Connect to DriverID=MSSQL Database=sunnyORM
000000001A1D0D67 DB                Connected to MSSQL (sunnyORM)
000000001A1D45B7  -            00.154.228
000000001A3D723F  -        00.154.975
000000001A3DB549  +        TSQLDBFireDACStatement(02A19A50).00772991
000000001A3DDB9A SQL           TSQLDBFireDACStatement(02A19A50) select GETDATE()
000000001A526870  -        00.000.478
000000001AB4E147  +        TSQLDBFireDACStatement(02A19B10).007727A9
000000001AB5ACE1  -        00.000.018
000000001AB5BC49  +        TSQLDBFireDACStatement(02A19B10).00772991
000000001AB5D070 SQL           TSQLDBFireDACStatement(02A19B10) CREATE TABLE dbo.LTN_DTL (ID  int NOT NULL PRIMARY KEY,NUMBER nvarchar(10),LX float,LY float,TE datetime)
000000001AD5AD0E EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001ADA5E25 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A2C4 7D61EC1B 7D61EB34 7D4E21B6 007A368E 0087071F 0087079C 0087FD11 00863497 008635CB 008644E3 0086308E 0083AFB3 0085E4B6 008646F0 0082640D 008266D1 0082B566 0083D19E
000000001E9A829A EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001E9B6C27 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001E9C4F98 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A2C4 7D61EC1B 7D61EB34 7D4E21B6 007A368E 0087071F 0087079C 0087FD11 00863497 008635CB 008644E3 0086308E 0083AFB3 0085E4B6 008646F0 0082640D 008266D1 0082B566 0083D19E
000000001E9D8176 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001E9EB2F7 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001EA5159F EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]There is already an object named 'LTN_DTL' in the database.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001EA5784D  -        00.023.311
000000001EA99B48  +        TSQLDBFireDACStatement(02A19B10).007727A9
000000001EAA8AC6  -        00.000.021
000000001EAAA130  +        TSQLDBFireDACStatement(02A19B10).00772991
000000001EAABB8D SQL           TSQLDBFireDACStatement(02A19B10) ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)
000000001EDA4AE7 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001EDB0097 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A2C4 7D61EC1B 7D61EB34 7D4E21B6 007A368E 0087071F 0087079C 0087FD11 00863497 008635CB 008644E3 0086308E 0083AFB3 0085E4B6 008646F0 0082640D 008266D1 0082B566 0083D19E
000000001EDBA462 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001EDC085B EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001EDC8E90 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A2C4 7D61EC1B 7D61EB34 7D4E21B6 007A368E 0087071F 0087079C 0087FD11 00863497 008635CB 008644E3 0086308E 0083AFB3 0085E4B6 008646F0 0082640D 008266D1 0082B566 0083D19E
000000001EDD55D9 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001EDE64C9 EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001EE1703B EXC           EMSSQLNativeException ("[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]Column names in each table must be unique. Column name 'NUMBER' in table 'dbo.LTN_DTL' is specified more than once.") at 007A368E  stack trace API 00614AC8 0040A44C
000000001EE1C9E9  -        00.001.275
000000001EE77C86 EXC       EORMException ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0071F8AC  stack trace API 00614AC8 0040A2C4 7D61EC1B 7D61EB34 7D4E21B6 0071F8AC 006868AC 006A0FB8 006E916A 006E9507 006C76B5 006C06C9 006C07EF 0069C99D 0069B766 0069B813 006994C5 0069EA77 0088B7DA 005C6E3F
000000001EEA6FD4 EXC       EORMException ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0071F8AC  stack trace API 00614AC8 0040A2C4 7D61EC1B 7D61EB34 7D4E21B6 0071F8AC 006868AC 006A0FB8 006E916A 006E9507 006C76B5 006C06C9 006C07EF 0069C99D 0069B766 0069B813 006994C5 0069EA77 0088B7DA 005C6E3F
000000001EEB73E9 EXC       EORMException ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0071F8AC  stack trace API 00614AC8 0040A44C
000000001EEFACF8 EXC       ESQLite3Exception ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0069CED3  stack trace API 00614AC8 0040A44C
000000001EF01A46  -    00.182.989
000000001EF031C8  +    TSQLDatabase(02701FF0).00699482
000000001EF03BE9 SQL       ROLLBACK TRANSACTION;
000000001EF17416  -    00.000.029
000000001EF26DDF EXC   ESQLite3Exception ("TSQLLTN_DTL: unable to create external missing field dbo.LTN_DTL.NUMBER - SQL=\"ALTER TABLE dbo.LTN_DTL ADD NUMBER nvarchar(10)\"") at 0069CED3  stack trace API 00614AC8 0040A2C4 7D61EC1B 7D61EB34 7D4E21B6 0069CED3 0069C9AA 0069B766 0069B813 006994C5 0069EA77 0088B7DA 005C6E3F 005C6A5F 005C6A10 005D1955 0089776C 7D4E7CC2

Offline

#12 2014-05-02 06:57:03

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

Re: mORMot + MS SQL Server + FireDAC + JSON

Please, provide sources for TSQLLTN_DTL. Is any field named Number inside? Number is Reserved word - newer give property nemes equal to reserved words. Hope it help.

Offline

#13 2014-05-02 07:00:43

Sunny
Member
Registered: 2014-04-29
Posts: 7

Re: mORMot + MS SQL Server + FireDAC + JSON

The source for TSQLLTN_DTL is as follows, no field is named as NUMBER.

  TSQLLTN_DTL = class(TSQLRecord)
  public
    fNO: RawUTF8;
    fLAT: real;
  published
    property NO: RawUTF8 read fNO write fNO;
    property LAT: real read fLATITUDE write fLAT;
  end;

Offline

Pages: 1

Board footer

Powered by FluxBB