mORMot Open Source

gjrivero

Member

Registered: 2014-02-05

Posts: 3

Firedac MSSQL

Hello!

I new in mORMot.

My tables were defined by code first in TSQLrecord especifications. Not by sql script
I have a simple Firedac MSSQL server like this:

procedure setVirtualTables( aModel: TSQLModel;
                            aExternalDB: TSQLDBConnectionPropertiesThreadSafe);
Begin
  VirtualTableExternalRegister(aModel,TSQLUSerGroups, aExternalDB,'dbo.UserGroups');
  VirtualTableExternalRegister(aModel,TSQLUsers,      aExternalDB,'dbo.Users');
  VirtualTableExternalRegister(aModel,TSQLAddress,    aExternalDB,'dbo.Address');
  VirtualTableExternalRegister(aModel,TSQLPerson,     aExternalDB,'dbo.Person');
  VirtualTableExternalRegister(aModel,TSQLConfigSys,  aExternalDB,'dbo.ConfigSys');
  VirtualTableExternalRegister(aModel,TSQLCorrelative,aExternalDB,'dbo.Correlative');
  VirtualTableExternalRegister(aModel,TSQLCustomers,  aExternalDB,'dbo.Customers');

  VirtualTableExternalRegisterAll(aModel,aExternalDB,[regDoNotRegisterUserGroupTables,
                                                      regMapAutoKeywordFields]);
End;

var aModel: TSQLModel;
    aExternalDB: TSQLDBConnectionPropertiesThreadSafe;
    aServer: TSQLRestServerDB;
    aHTTPServer: TSQLHttpServer;
    FDPhysMSSQLDriverLink: TFDPhysMSSQLDriverLink;
begin
  SynDBLog.Family.Level := LOG_VERBOSE;
  SynDBLog.Family.PerThreadLog:= ptIdentifiedInOnFile;
  FDPhysMSSQLDriverLink:= TFDPhysMSSQLDriverLink.Create(Nil);
  aModel := DataModel;
  // ! Create('MSSQL?Server=127.0.0.1\SQLEXPRESS','Northwind','User','Password');
  // ! Create('MSSQL?Server=.\SQLEXPRESS;OSAuthent=Yes','','','');
  aExternalDB := TSQLDBFireDACConnectionProperties.Create(
                   'MSSQL?Server=127.0.0.1','Elevated','sa','12345');
  try
    aExternalDB.ThreadingMode := tmMainConnection; // force SINGLE connection
    setVirtualTables(aModel,aExternalDB);
    aServer:=TSQLRestServerDB.Create(aModel,SQLITE_MEMORY_DATABASE_NAME);
    try
      aServer.AcquireExecutionMode[execORMGet  ]:=amBackgroundThread;
      aServer.AcquireExecutionMode[execORMWrite]:=amBackgroundThread;
      aServer.DB.Synchronous:=smNormal;
      aServer.DB.LockingMode:=lmExclusive;
      aServer.CreateMissingTables;
      aHTTPServer:=TSQLHttpServer.Create(SERVER_PORT,aServer,'+',useHttpApiRegisteringURI);
      aHttpServer.AccessControlAllowOrigin:='*'; // allow cross-site AJAX queries
      try
        writeln('Server launched on port '+SERVER_PORT+' using ',aHttpServer.HttpServer.ClassName);
        writeln(#10'Press [Enter] to close the server.'#10);
        readln;
---------------
---------------
The server is ok!

But in Client applicattion I can't add any record

The log file in the server is:

(038381E0).URI(GET root/TimeStamp inlen=0)
20160125 17002144  A call      mORMotSQLite3.TSQLRestServerDB(038381E0) TimeStamp
20160125 17002144  A srvr      mORMotSQLite3.TSQLRestServerDB(038381E0)   GET root/TimeStamp SOA-Method -> 200 with outlen=12 in 54 us
20160125 17002144  A ret       mORMotSQLite3.TSQLRestServerDB(038381E0) 135294685205
20160125 17002144  A  -    00.000.059
20160125 17003555  "  +    mORMotSQLite3.TSQLRestServerDB(038381E0).URI(POST root/Address inlen=109)
20160125 17003555  B trace mORMotSQLite3.TSQLRestServerDB(038381E0) BeginCurrentThread(TSynBackgroundThreadMethod) ThreadID=00003F44 ThreadCount=33
20160125 17003555  B  +    SynDBFireDAC.TSQLDBFireDACStatement(037B7D60).0065357B
20160125 17003555  B  -    00.000.029
20160125 17003555  B  +    SynDBFireDAC.TSQLDBFireDACStatement(037B7D60).006536ED
20160125 17003555  B SQL       SynDBFireDAC.TSQLDBFireDACStatement(037B7D60) select max(ID) from dbo.Address
20160125 17003558  B  -    00.046.530
20160125 17003558  B  +    SynDBFireDAC.TSQLDBFireDACStatement(037B7E20).0065357B
20160125 17003558  B  -    00.000.069
20160125 17003558  B  +    SynDBFireDAC.TSQLDBFireDACStatement(037B7E20).006536ED
20160125 17003558  B SQL       SynDBFireDAC.TSQLDBFireDACStatement(037B7E20) insert into dbo.Address (ID,Descrip,Country,State_,Locality,Address1,Address2,zipcode,Phones) values (1,'Casa',NULL,NULL,NULL,NULL,NULL,NULL,NULL)
20160125 17003558  B EXC       EFDException ("[FireDAC][Phys][MSSQL]-335. Parameter [CA] data type is unknown. Hint: specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call") at 00656C52  stack trace API 00562F44 00562F6C 0040AB08
20160125 17003558  B EXC       EFDException ("[FireDAC][Phys][MSSQL]-335. Parameter [CA] data type is unknown. Hint: specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call") at 00656C52  stack trace API 00562F44 00562F6C 0040A980 7780B534 777F8DAF 00656C52 00658C18 00801403 008248B3 00825841 008363D7 006F0E27 006F1181 006F16BE 006BE14F 0070A6A6 00653953 007DA644 007D7E67 00788FB2 007B89C0 0055CBB6 0055C657 004BF2D8 0040B42E 759038F4 777E56C3 777E568E
20160125 17003558  B EXC       EFDException ("[FireDAC][Phys][MSSQL]-335. Parameter [CA] data type is unknown. Hint: specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call") at 00656C52  stack trace API 00562F44 00562F6C 0040A980 7780B534 777F8DAF 00656C52 00658C18 00801403 008248B3 00825841 008363D7 006F0E27 006F1181 006F16BE 006BE14F 0070A6A6 00653953 007DA644 007D7E67 00788FB2 007B89C0 0055CBB6 0055C657 004BF2D8 0040B42E 759038F4 777E56C3 777E568E
20160125 17003558  B EXC       EFDException ("[FireDAC][Phys][MSSQL]-335. Parameter [CA] data type is unknown. Hint: specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call") at 00656C52  stack trace API 00562F44 00562F6C 0040A980 7780B534 777F8DAF 00656C52 00658C18 00801403 008248B3 00825841 008363D7 006F0E27 006F1181 006F16BE 006BE14F 0070A6A6 00653953 007DA644 007D7E67 00788FB2 007B89C0 0055CBB6 0055C657 004BF2D8 0040B42E 759038F4 777E56C3 777E568E
20160125 17003558  B EXC       EFDException ("[FireDAC][Phys][MSSQL]-335. Parameter [CA] data type is unknown. Hint: specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call") at 00656C52  stack trace API 00562F44 00562F6C 0040AB08
20160125 17003558  B EXC       EFDException ("[FireDAC][Phys][MSSQL]-335. Parameter [CA] data type is unknown. Hint: specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call") at 00656C52  stack trace API 00562F44 00562F6C

Thanks a lot for any help!

Guillermo

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Firedac MSSQL

Welcome here!

Which version of mORMot are you using?
Which version of FireDAC are you using?
Which version of Delphi are you using?
Why do you use FireDAC to connect to MSSQL, whereas a native OleDB connection via SynDBOleDB would give better performance?

Why are there all those NULL values inserted? Because the record is mostly void?
Try to change the StoreVoidStringAsNull field of the TSQLDBFireDACConnectionProperties used.