[SOLVED] Help with connection/setup

macfly · 2016-08-20 14:37:45

Hi Friends,

I'm trying to start a project with the Framework.
It is compatible with Access (.mdb) database?

I'm not getting set properly.

type
  TSQLtest = class(TSQLRecord)
  protected
    fName: RawUTF8;
  published
    property Name: RawUTF8 index 255 read fName write fName;
  end;


(...)

var
  AConnString : String;
  AModel : TSQLModel;
  AProp : TOleDBJetConnectionProperties;
  AConn : TOleDBConnection; //to test connection only
  ATest : TSQLtest;
  ARow: Variant;
  Database : TSQLRest;

(...)
  AConnString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\tests\mormot\db\db.mdb;Persist Security Info=False;Jet OLEDB:Database Password=mypass';

  AProp := TOleDBJetConnectionProperties.Create(AConnString,'','','mypass');
  AProp.ConnectionString := AConnString;   //It does not work if ConnectionString are not setted here

  //Testing a connection. Always connect successfully 
  AConn := TOleDBConnection.Create(AProp);
  AConn.Connect;
  Memo1.Lines.Add('Connected: ' + IntToStr(Ord(AConn.Connected)));

  //Testing a SELECT. This are OK too.

  with AProp.Execute('SELECT * FROM test',[],@ARow) do
    while Step do
      Memo1.Lines.Add('Nome:' + ARow.Name);


  //Now the ORM

  AModel := TSQLModel.Create([TSQLtest]);

  VirtualTableExternalRegisterAll(AModel,AProp);
  
  //Also tried
  //VirtualTableExternalRegister(AModel,TSQLteste,AProp,'test');

  Database := TSQLRestServerDB.Create(AModel, ':memory:' , True); 

  ATest := TSQLtest.Create;
  try
    Database.Retrieve('Name = "Jhon"', ATest);
  finally
    ATest.Free;
  end;

Database.Retrieve... causes this error

Error SQLITE_ERROR (1) [SELECT RowID, Name FROM test WHERE Name = "Jhon"] using 3.14.1- no such table: test, extended_errcode=1

Don't locate table test, and I do not understand why are using RowID instead of ID.

If i omit VirtualTableExternalRegisterAll(AModel,AProp) then he uses ID and error was:

Error SQLITE_ERROR (1) [SELECT ID, Name FROM test WHERE Name = "Jhon"] using 3.14.1- no such table: test, extended_errcode=1

Last edited by macfly (2016-08-20 18:51:18)

macfly · 2016-08-20 18:50:40

Solved.
I forgot TSQLRestServerDB(DataBase).CreateMissingTables();

esmondb · 2016-08-22 11:37:14

I'm also puzzled by the difference between 'ID' and 'RowID'.

If I use TSQLTableDB.getJSONValues(true) it returns ID

but with TSQLRecord.getJSONValues(true,true,soSelect) it returns RowID for the record number.

Can anyone explain the difference between ID and RowID?

Thanks,
Esmond

ab · 2016-08-22 12:14:50

ID and RowID are the same, at SQlite3 level, and at ORM/ODM level.
See https://www.sqlite.org/lang_createtable.html#rowid

esmondb · 2016-08-22 14:11:20

Thanks,

Just realised that I need to add the jwoAsJsonNotAsString option to get ID instead of rowID.

mORMot Open Source

#1 2016-08-20 14:37:45

[SOLVED] Help with connection/setup

#2 2016-08-20 18:50:40

Re: [SOLVED] Help with connection/setup

#3 2016-08-22 11:37:14

Re: [SOLVED] Help with connection/setup

#4 2016-08-22 12:14:50

Re: [SOLVED] Help with connection/setup

#5 2016-08-22 14:11:20

Re: [SOLVED] Help with connection/setup

Board footer