mORMot Open Source

Ok,
for other distracted as me : I have to use Iso8601ToTimePUTF8Char !!!
by

Ok,

I've modified as you suggested.

Tx very much

Tx AB,
now it works fine.

My problem was that, with default behavior,  string values containing only digits inserted with asCSV, were returned by initfromcsv with a #13 added at the end. 

1 was returned as 1#13.

TX AB,

Sorry but I don't understand what you are telling me. I imagine how busy you are, but could you be so kind to show me an example ?

I can't use a tSQLRestBatch because sometimes  , in the same transaction, i need to know the ID of an inserted record to fill a property of another class before inserting it (I've tried to define it as tSQLRecord, but in some circumstances i can't do it). If I have well undertstood the batchsend operates a commit, so, using batch,  it's impossible to grant that all data are persisted togheter.

Sounds a very good reason.

I have added a control of StatementLastException after the createandfillprepare nested in transaction : now seems to work ok.

tx very much AB.

Anhoter question please :

on server side , what object, instead of tMySQLRestServerDB, I should better use to access data by ORM?

I've tried to put breackpoints in syndbodbc.starttransaction , commit and rollback : they are never called  by fserver.transactionbegin or commit.
I searched for examples without success : please help me.
tx

Hi AB,

sorry for delay and tx very much.

Now it works, but i had to upgrade to last revision. Nothing of bad , except that now i'm encountering another problem in tSQLRestServerDB.Executelist .

This is the line that doesn't work no more on postgres DB via ODBC :

        rows := DataBase.ExecuteList([tSQLCD_Params],'select data from ' + tSQLCD_Params.SQLTableName); 

the error reported is : TODBCStatement.GetCol: "data" column had Indicator=1376

data is a blob and , debugging, i've found that the error is fired because of a modification in function GetDiagField of unit SynDbODBC, line 1848.

Before it was  :

 
     if ODBC.GetDiagRecW(SQL_HANDLE_STMT,StatementHandle,1,
       Sqlstate,NativeError,MessageText,1024,StringLength) = 0 then
     begin
          i := 0;
          while sqlstate[i]> #0 do
          begin
               result := result + sqlstate[i];
               inc(i);
          end;
     end;

GetDiagRecW returned 0.
Now it is :

  if ODBC.GetDiagFieldA(SQL_HANDLE_STMT,StatementHandle,1,SQL_DIAG_SQLSTATE,
     @Status,sizeof(Status),StringLength)=0 then
    SetString(result,PAnsiChar(@Status),StringLength) else
    result := '';
  

The GetDiagFieldA doesn't return 0.

Hi,

i solved using transaction only with tsqlrestserverdb.transactionbegin : the complex query are needed only in select statements.

tx.

Hi AB,

i'm having trouble using a connection to access database (else sqlite or external).

Certainly i'm doing something of wrong, but cannot resolve the issue, so i've reproduced this little example in which I show how i'm defininig a TSQLDBSQLite3ConnectionProperties, used to access data when queries are too complex  and a TSQLRestServerDB for ORM

Till when I don't fire a select (i.e. till when i don't use directly the connection) , all works fine, but, after the usage of a dbConn.execute, accessing data with ORM results in a unpredictable error : in this example , trying to update a blob field (only a blob) via ORM results in this error : Error SQLITE_BUSY (5) [Step] using 3.13.0 - database is locked, extended errcode=5.
Using ODBC also i've similar problems.

Coud you tell me how i should correctly define this scenario ?
tx very much.

unit Unit1;

interface

uses
  {$ifdef MSWINDOWS}
  Windows,
  Messages,
  Graphics,
  {$endif}
  Classes, SysUtils, Forms, Controls, Dialogs, StdCtrls,
  SynCommons,
  SynDB,
  mORMot,
  mormotsqlite3,
  synDBsqlite3;

type

  { TForm1 }

  TForm1 = class(TForm)
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
         DBConn : tSQLDBConnectionProperties;
         fConn : TSQLDBSQLite3Connection;
    { private declarations }
  public
    { public declarations }
    Database: TSQLRestServerDB;
    Model: TSQLModel;
  end;

  TSQLSampleRecord = class(TSQLRecord)
  private
    fData     : RawUtf8;
    fBlobData : RAwByteString;
  published
    property Data : Rawutf8 read fData write fData;
    property BlobData : RawByteString read fBlobData write fBlobData;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var Model : tSQLModel;
    s : tSQLSampleRecord;
    rows : isqldbrows;
    V : Variant;
    data : RawByteString;
begin
  model := tSQLModel.Create([tSQLSampleRecord]);
  DataBase := tSQLRestServerDB.Create(model,'test.db3');
  DataBase.CreateMissingTables(0);
  s := tSQLSampleRecord.Create(Database, 1);
  s.Data := 'data';
  s.BlobData := 'blobdata';
  if s.ID = 0 then Database.Add(s, true);
  s.Free;
  dbConn := TSQLDBSQLite3ConnectionProperties.Create('test.db3','','','');
  dbconn.MainConnection.Connect;
end;




procedure TForm1.FormDestroy(Sender: TObject);
var Model : tSQLModel;
    s : tSQLSampleRecord;
    rows : isqldbrows;
    V : Variant;
    d : Rawutf8;
    blobd : rawbytestring;
begin
     // here the select (if not executed, updateblobfields works fine.
     rows := dbconn.execute('select data from ' + tSQLSampleRecord.SQLTableName, [], @V);
     if rows.Step then
        d := rows.ColumnUTF8(0);
     rows := nil;

     s := tSQLSampleRecord.Create(Database, 1);
     s.data := 'òaiosufvbnsliughjwrpt90rp35';
     s.blobdata := 'òaifgdffosufvbnsliughjwrpt90rp35';
     database.update(s);
     database.UpdateBlobFields(s);
     s.free;
     database.Free;
     model.free;
     dbconn.Free;
end;

end.

ok ab,

as we know , the documentation of this big framework is BIG.
What about raise an axception on b.A=A.AstSqlRecord if A.ID is greater than maxint? It could avoid any misunderstanding (now , in the db field,  is stored 2147483648!).

yes but, the unnamend threads end after one minute, non so do the httpserver worker that stay alive forever : i haven't killed the last process and they are still running.

In thread status  panel stay these threads in state runnable (while destroying form they are in statu stopped)

Main thread :
tSQLHttpServer worker : 32 threads.

sometimes there are other 3 unnnamed  threads (only id).

objects destroy order is :

Client
HttpServer
Server

indeed was very simple! tx

Hi AB,

sorry but i can't retrieve the data.

After a long period  i'm yet a beginner, so,  i've used a sample project : Sample 02 -Embedded Sqlite3 ORM with these changes :

in Sampledata :

  TSQLSampleRecord = class(TSQLRecord)
  private
    fQuestion: RawUTF8;
    fName: RawUTF8;
    fTime: TModTime;

            fOwner : tSQLSampleRecord;   

  published
    property Time: TModTime read fTime write fTime;
    property Name: RawUTF8 read fName write fName;
    property Question: RawUTF8 read fQuestion write fQuestion;

    	property Owner : tSQLSampleRecord read fOwner write fOwner;

  end;

In Unit1 :

procedure TForm1.AddButtonClick(Sender: TObject);
var Rec [ins], Rec1[/ins]: TSQLSampleRecord;
[ins]    Ok : Boolean;[/ins]
begin
  Rec := TSQLSampleRecord.Create;
  try
    // we use explicit StringToUTF8() for conversion below
    // a real application should use TLanguageFile.StringToUTF8() in mORMoti18n
    Rec.Name := StringToUTF8(NameEdit.Text);
    Rec.Question := StringToUTF8(QuestionMemo.Text);
    if Database.Add(Rec,true)=0 then
      ShowMessage('Error adding the data') else begin


             Rec.Owner := Rec;
             Database.Update(Rec, 'Owner');
             Rec.Free;
             Rec := tSQLSampleRecord.Create(Database, 1);
             Rec1 := tSQLSampleRecord.CreateAndFillPrepare(Database, 'Owner=?',[Rec]);
             Ok := Rec1.Fillone;


      NameEdit.Text := '';
      QuestionMemo.Text := '';
      NameEdit.SetFocus;
    end;
  finally
    Rec.Free;
  end;
end;

Well : this are fields values after  Database.Update :

ID = 1 , Owner = 33247792 : I think  this is normal owing to refrerences and so on…..but , deleting database and reexecuting the code , Owner's value changes everytime, also inserting same values in name and question (???).

However : Fillone returns Ok = False.

The Sql generated by ORM is :

Select ID, Time, Name, Question, Owner from SampeRecord where Owner=:(''tSQLSampleRecord''):

So I've changed query with

Rec1 := tSQLSampleRecord.CreateAndFillPrepare(Database, 'Owner=?',[Rec.ID]);

and deleted database.

Now ID=1 and Owner = 35869232

Fillone returns OK = False.

The Sql generated by ORM is :

Select ID, Time, Name, Question, Owner from SampeRecord where Owner=:(''1''):

AB, please, don't say me to read you huge documentation or posts : at this point  we can assume that  I'm not able to understand  them !

My you correct the lines i've changed to let them  to work as expected ?

Tx very much.

Hy Ab,

you are right !
I hope it's due to this oppressive heat and not to age....
Tx
Mario

tx a lot AB !!!