mORMot Open Source

reg4app

Member

Registered: 2016-05-20

Posts: 2

MSSQL RESTserver question

Try to test sample 28 to connect MSsql server

RESTserver

/// minimal REST server for a list of Persons stored on PostgreSQL
program RESTserver;

// see http://synopse.info/forum/viewtopic.php?pid=10882#p10882

{$APPTYPE CONSOLE}

uses
  {$I SynDprUses.inc}  // use FastMM4 on older Delphi, or set FPC threads
  SynCommons,          // framework core
  SynLog,              // logging features
  mORMot,              // RESTful server & ORM
  mORMotSQLite3,       // SQLite3 engine as ORM core
  SynSQLite3Static,    // staticaly linked SQLite3 engine
  mORMotDB,          // ORM using external DB
  SynOleDB,
  mORMotHttpServer,    // HTTP server for RESTful server
  SynDB,               // external DB core
  SynDBODBC,           // external DB access via ODBC
  RESTModel,           // data model unit, shared between server and client
  SynSQLite3;

var
  aModel: TSQLModel;
  aProps: TSQLDBConnectionProperties;
  aRestServer: TSQLRestServerDB;
  aHttpServer: TSQLHttpServer;
begin
  // set logging abilities
  SQLite3Log.Family.Level := LOG_VERBOSE;
  //SQLite3Log.Family.EchoToConsole := LOG_VERBOSE;
  SQLite3Log.Family.PerThreadLog := ptIdentifiedInOnFile;
  // ODBC driver e.g. from http://ftp.postgresql.org/pub/odbc/versions/msi
  aProps := TOleDBMSSQLConnectionProperties.Create('192.168.1.190','LJInfo','sa','12345');
  try
    // get the shared data model
    aModel := DataModel;
    // use PostgreSQL database for all tables
    VirtualTableExternalRegister(aModel,TSQLLJ_AutoNoCount, aProps,'dbo.Base_Batch');

    VirtualTableExternalRegisterAll(aModel,aProps);

    try
      // create the main mORMot server
      aRestServer := TSQLRestServerDB.Create(aModel,':memory:',false); // authentication=false
      try
        // optionally execute all PostgreSQL requests in a single thread
        aRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
        aRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;

        aRestServer.DB.Synchronous:=smNormal;
        aRestServer.DB.LockingMode:=lmExclusive;

        // create tables or fields if missing
        aRestServer.CreateMissingTables;
        // serve aRestServer data over HTTP
        aHttpServer := TSQLHttpServer.Create(SERVER_PORT,[aRestServer],'+',useHttpApiRegisteringURI);
        try
          aHttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
          writeln('Background server is running.'#10);
          write('Press [Enter] to close the server.');
          readln;
        finally
          aHttpServer.Free;
        end;
      finally
        aRestServer.Free;
      end;
    finally
      aModel.Free;
    end;
  finally
    aProps.Free;
  end;
end.

RESTModel

unit RESTModel;

interface

uses
  SynCommons,
  mORMot;

type
  TSQLLJ_AutoNoCount = class(TSQLRecord) // TSQLRecord has already ID: integer primary key
  private
    fNoType: RawUTF8;
  published
    /// ORM will create a NAME VARCHAR(80) column
    property NoType: RawUTF8 index 80 read fNoType write fNoType;
  end;

function DataModel: TSQLModel;

const
  SERVER_ROOT = 'root';
  SERVER_PORT = '888';


implementation

function DataModel: TSQLModel;
begin
  result := TSQLModel.Create([TSQLLJ_AutoNoCount],SERVER_ROOT);
  //TPerson.AddFilterOrValidate('Name',TSynValidateText.Create); // ensure exists
end;


end.

I get some questions
1.in log file ,there is always this error messaage(see below),but the server seems work...

20160621 01390131  !  -    01.238.477
20160621 01390131  ! SQL   SynSQLite3.TSQLDatabase(0152C158) 1.23s  CREATE VIRTUAL TABLE LJ_AutoNoCount USING External(NoType TEXT COLLATE SYSTEMNOCASE);
20160621 01390131  ! DB    SynOleDB.TOleDBStatement(01410130) Prepare 35us select top(1) ID from dbo.LJ_AutoNoCount
20160621 01390137  ! ERROR "EOleDBException(01509200)":{"EOleDBException(01509200)":{"Message":"TOleDBConnection: OLEDB Error 80040E14 -   (line 1): 第 1 行: '(' 附近有语法错误。\r\n"}} stack trace API 004B06C0 00620EC7 0062105F 0061F09C 00615DB5 00614B5B 0052C4E0 005790F4 006496EE 75923C45 771B37F5 771B37C8
20160621 01390137  ! EXC   EOleDBException {"Message":"TOleDBConnection: OLEDB Error 80040E14 -   (line 1): Incorrect syntax near '(' \r\n"} at 00620ECF  stack trace API 004AE3C4 004AE3EC
20160621 01390137  ! EXC   EOleDBException {"Message":"TOleDBConnection: OLEDB Error 80040E14 -   (line 1): Incorrect syntax near '(' \r\n"} at 00620ECF  stack trace API 004AE3C4 004AE3EC
20160621 01390137  ! ERROR "EOleDBException(01509200)":{"EOleDBException(01509200)":{"Message":"TOleDBConnection: OLEDB Error 80040E14 -   (line 1): 第 1 行: '(' 附近有语法错误。\r\n"}} stack trace API 004B06C0 0061F2D0 00615DB5 00614B5B 0052C4E0 005790F4 006496EE 75923C45 771B37F5 771B37C8
20160621 01390137  ! EXC   EOleDBException {"Message":"TOleDBConnection: OLEDB Error 80040E14 -   (line 1): Incorrect syntax near '(' \r\n"} at 00620ECF  stack trace API 004AE3C4 004AE3EC
20160621 01390137  ! SQL   SynSQLite3.TSQLDatabase(0152C158) 35us  COMMIT TRANSACTION;
20160621 01390137  !  +    mORMotHttpServer.TSQLHttpServer(01499320).Create useHttpApiRegisteringURI (secNone) on port 888
20160621 01390141  ! http      mORMotHttpServer.TSQLHttpServer(01499320) http.sys registration of http://+:888/root

2. do my ervery mssql table need a field name ID???

reg4app

Member

Registered: 2016-05-20

Posts: 2

Re: MSSQL RESTserver question

because some error messages are chinese ,i changed them to english...^_^