Beginner question: MS SQL Server

andrey · 2013-02-06 16:19:32

Hello! I am trying to create a very simple code to write data in a database on my local MS SQL Server:

type
  TSQLSampleRecord = class(TSQLRecord)
  private
    fName: RawUTF8;
  published
    property Name: RawUTF8 read fName write fName;
  end;

var Props: TOleDBConnectionProperties;
     Model: TSQLModel;
     Database: TSQLRestServerDB; //???
     Rec: TSQLSampleRecord;

begin
  Props := TOleDBMSSQLConnectionProperties.Create('.\SQLEXPRESS','TestDatabase','','');
  Model := TSQLModel.Create([TSQLSampleRecord],'root');
  VirtualTableExternalRegister(Model,TSQLSampleRecord,Props,'');

  Database := TSQLRestServerDB.Create(Model,'application.db'); //???

  //Adding data
  Rec := TSQLSampleRecord.Create;
  Rec.Name := 'Test';
  Database.Add(Rec,true);
  Rec.Free;

The idea is that mORMot should create a table "SampleRecord" and write down this Rec row, but it doesn't ;)

I really could not find such a simple example in this forum, manual or in working examples.
I guess I choose wrong database type (TSQLRestServerDB) and wrong create procedure? (TSQLRestServerDB.Create)

ab · 2013-02-06 16:42:16

Do not forget to call TSQLRestServerDB.CreateMissingTables (e.g. in your overriden TSQLRestServerDB.Create constructor), to create the missing tables.

See the samples about that, and the documentation.

For an external database, it is always better to specify the field length, using e.g:

 property Name: RawUTF8 read fName write fName index 50; // will create a more efficient NVARCHAR(50) and not a CLOB

andrey · 2013-02-07 13:40:59

Thanks so much!

Here is my code that could give an idea for other beginners

program Project_mORMot;

uses
  SynCommons,
  SysUtils,
  SQLite3Commons,
  SQLite3,
  SQLite3DB,
  SynDB,
  SynOleDB;

{$APPTYPE CONSOLE}

type
  TSQLSampleRecord = class(TSQLRecord)
  private
    fName: RawUTF8;
  published
    property Name: RawUTF8 index 50 read fName write fName;
  end;

var Props: TOleDBConnectionProperties;
     Model: TSQLModel;
     Database: TSQLRestServerDB;
     Rec: TSQLSampleRecord;

begin
  Props := TOleDBMSSQLConnectionProperties.Create('.\SQLEXPRESS','TestDatabase','','');
  Model := TSQLModel.Create([TSQLSampleRecord],'root');
  VirtualTableExternalRegister(Model,TSQLSampleRecord,Props,'');

  Database := TSQLRestServerDB.Create(Model,'application.db');
  Database.CreateMissingTables();

  //Adding data
  Rec := TSQLSampleRecord.Create;
  Rec.Name := 'Test';
  Database.Add(Rec,true);
  Rec.Free;
end.

Last edited by andrey (2013-02-07 16:59:25)

ab · 2013-02-07 17:20:42

... thanks for sharing!

You can use ':memory:' instead of 'application.db' if all your tables are externals.

And do not forget to release the internal used instances (Props/Model/Database) - otherwise your pedagogical code would leak memory.

andrey · 2013-02-07 17:38:18

Very nice it is!

One more small question that already took us one day

The manual says: "there is no difference with using the local SQLite3 engine or a remote database engine. From the Client point of view, you just call the usual RESTful methods (i.e. Add / Retrieve / Update/ UnLock / Delete)"

We want to find the record that was just saved in the code above. If you have "Database: TSQLRest" then it works fine to search like this:

  Rec := TSQLSampleArticle.Create(Database,'Name=?',['Test']);
  writeln( 'Name: ' + Rec.Name);

But it doesn't work with "Database: TSQLRestServerDB", so we tried this:

    Rec := TSQLSampleArticle.CreateAndFillPrepare(Database,'Name LIKE ?',['Test']);

    try
      while Rec.FillOne do
        writeln(Rec.Name);
    finally
      Rec.Free;
    end;

and got: OLEDB Error 80040E14 - Invalid column name 'RowID'.

Do you know what procedure should one use to search records in TSQLRestServerDB?

Last edited by andrey (2013-02-07 17:40:06)

ab · 2013-02-07 22:46:23

Are you using the latest unstable 1.18 revision of the sources?
See http://synopse.info/fossil/wiki?name=Get+the+source

A lot of fixes are included, since last 1.17 official release.

andrey · 2013-02-08 11:54:38

Yes, I was using 1.17!

I see you changed all SQLite3* names to mORMot* in 1.18

Unfortunately I could not make even this example work: (SAD - Global Architecture - Rev. 1.18 Page 127)

"Here is some working sample program, using our SynOleDB unit to connect to a local MS SQL Server 2008 R2 Express edition"

program TestOleDB;
{$APPTYPE CONSOLE}
uses
SysUtils,
Classes,
SynCommons,
SynOleDB;
var Props: TOleDBConnectionProperties;
Conn: TSQLDBConnection;
Query: TSQLDBStatement;
F: TFileStream;
begin
with OleDBSynLogClass.Family do begin
Level := LOG_VERBOSE;
AutoFlushTimeOut := 10;
end;
Props := TOleDBMSSQLConnectionProperties.Create('.\SQLEXPRESS','AdventureWorks2008R2','','');
try
//Props.ConnectionStringDialogExecute;
Conn := Props.NewConnection;
try
Query := Conn.NewStatement;
try
Query.Execute('select * from Person.Address',true,[]);
F := TFileStream.Create(ChangeFileExt(paramstr(0),'.json'),fmCreate);
try
Query.FetchAllToJSON(F,false);
finally
F.Free;
end;
finally
Query.Free;
end;
finally
Conn.Free;
end;
finally
Props.Free;
end;
end.

Delphi complains about undeclared identifier "OleDBSynLogClass" and "TSQLDBConnection". If you add "SynDB" to uses so could Delphi find "TSQLDBConnection".

ab · 2013-02-08 13:21:07

This part of the SAD was (sadly?) deprecated about this sample.

The TestOleDB.dpr file available in the source code tree (SQLIte3 sub folder) does compile and work.
Use the source, luke!

mORMot Open Source

#1 2013-02-06 16:19:32

Beginner question: MS SQL Server

#2 2013-02-06 16:42:16

Re: Beginner question: MS SQL Server

#3 2013-02-07 13:40:59

Re: Beginner question: MS SQL Server

#4 2013-02-07 17:20:42

Re: Beginner question: MS SQL Server

#5 2013-02-07 17:38:18

Re: Beginner question: MS SQL Server

#6 2013-02-07 22:46:23

Re: Beginner question: MS SQL Server

#7 2013-02-08 11:54:38

Re: Beginner question: MS SQL Server

#8 2013-02-08 13:21:07

Re: Beginner question: MS SQL Server

Board footer