Pages: 1 2

#1 Re: mORMot 1 » How open a sqlite database in android using MORMot + Delphi Berlin » 2018-10-12 18:04:09

Sapiem

Thanks a lot

#2 Re: mORMot 1 » How open a sqlite database in android using MORMot + Delphi Berlin » 2018-10-12 11:02:41

Sapiem

I'll try, thanks...

#3 Re: mORMot 1 » How open a sqlite database in android using MORMot + Delphi Berlin » 2018-10-11 23:06:00

Sapiem

What a pity !

What do you think about Cipher?

And if I use your sqlite.c (from mORMot folder) and replace the inside on SQLite_Android_Bindings.zip distribution from SEE sqlite.org? Maybe I'll obtain the appropriated aar file

I'm new on this, sorry...

#4 Re: mORMot 1 » How open a sqlite database in android using MORMot + Delphi Berlin » 2018-10-11 16:21:25

Sapiem

I need any way that let me open my sqlite database encrypted with mORMot in Delphi, in Android. I use Android Studio.

I tried Delphi Berlin, but I can't use mORMot to open a local database in device.

#5 Re: mORMot 1 » How open a sqlite database in android using MORMot + Delphi Berlin » 2018-10-11 10:48:08

Sapiem

And any way to use in Android Studio?

#6 mORMot 1 » How open a sqlite database in android using MORMot + Delphi Berlin » 2018-10-10 23:48:35

Sapiem
Replies: 11

Hello:

I have been using mORMot for a while in a Windows application using Delphi Berlin in which the database in sqlite3 is encrypted. Now I want to make an apk for Android that allows me to open the same database on the mobile.

Is it possible to do it from Android Studio?

Is it possible to do it also with Delphi Berlin?

Thanks for the help.

#7 Re: mORMot 1 » THttpApiServer doesn't work in netwrok with Win 7 » 2018-08-11 22:25:57

Sapiem
mpv wrote:

So, problem not in mormot. Try to ask on IIS or Windows forum

But IIS only works with port 80. mORMot works too with port 80 only, but no bidirectionally, only works reading from PC where IIS is configured (and turned off, of course).

I will ask in other forums. Thanks.

#8 Re: mORMot 1 » THttpApiServer doesn't work in netwrok with Win 7 » 2018-08-11 00:17:10

Sapiem
mpv wrote:

Do you have IIS installed? Just create a site in IIS control panel for some static folder. Verify it is work. Then shut down site in IIS and use the same URI for mormot without call to addUrlAuthorise.

I installed IIS. It works locally, but not with other PC, Smart TV, smartphone, etc.

Xampp works great...

#9 Re: mORMot 1 » THttpApiServer doesn't work in netwrok with Win 7 » 2018-08-10 10:30:23

Sapiem

Please, could you try my above code I tell me if it works for you? I did all that all of you said, and nothing.

#10 Re: mORMot 1 » THttpApiServer doesn't work in netwrok with Win 7 » 2018-08-07 01:56:30

Sapiem

Please, any solution? I tried a lot of variant and nothing...

When I run netsh http show urlacl I get this:

    Dirección URL reservada            : http://+:8995/MyServerRoot/
        Usuario: \Everyone
            Escuchar: Yes
            Delegar: Yes
            SDDL: D:(A;;GA;;;WD)

Please.... Why idHTTP runs ok and this not? With idHTTP I can't send to server long params... (SQL commands)

#11 Re: mORMot 1 » THttpApiServer doesn't work in netwrok with Win 7 » 2018-08-03 19:17:35

Sapiem

Working behind a proxy can it be related to this problem?

#12 Re: mORMot 1 » THttpApiServer doesn't work in netwrok with Win 7 » 2018-08-03 02:46:15

Sapiem

Ok, I check http.sys tables using:

netsh http show urlacl

And it register and then unregister URl using...

procedure TForm16.SetServerBtnClick(Sender: TObject);
begin
  if SetServerBtn.Caption = 'Activar' then
    begin
      FileListBox1.Enabled := false;
      SetServerBtn.Caption := 'Desactivar';
      Props := TSQLDBSQLite3ConnectionProperties.Create(FileListBox1.FileName, '', '', 'password');
      try
        if CheckBox1.Checked then                                                                                      <----- This added
          THttpApiServer.AddUrlAuthorize('MyServerRoot', PortTxt.Text, false, '+');
        fProps := Props;
        Conn := fProps.ThreadSafeConnection;
        if not Conn.Connected then
          Conn.Connect;
        FServer := THttpApiServer.Create(false);
        FServer.AddUrl('MyServerRoot', PortTxt.Text, false, '+', true);
        FServer.RegisterCompress(CompressDeflate);
        FServer.OnRequest := Process;
        FServer.Clone(31);

        try
          Memo1.Lines.Add('Servidor activo en http://' + ServerURLTxt.Text + ':' + PortTxt.Text + '/MyServerRoot/' + NL +
            'Base de datos disponible: ' + ExpandFileName(UTF8ToString(Props.ServerName)));
        except
          // error
          Memo1.Lines.Add('Ocurrió un error al mostrar datos de conexión.');
          FileListBox1.Enabled := true;
          SetServerBtn.Caption := 'Activar';
        end;
      except
        // error
        Memo1.Lines.Add('Ocurrió un error al intentar conectar.');
        FileListBox1.Enabled := true;
        SetServerBtn.Caption := 'Activar';
      end;

    end
  else
    begin
      if CheckBox1.Checked then                                                                                      <----- This added
        THttpApiServer.AddUrlAuthorize('MyServerRoot', PortTxt.Text, false, '+', true);
      FileListBox1.Enabled := true;
      SetServerBtn.Caption := 'Activar';
      FServer.Free;
      Memo1.Lines.Add('Desconectado');
    end;
end;

And neither work....

#13 Re: mORMot 1 » THttpApiServer doesn't work in netwrok with Win 7 » 2018-08-03 02:23:49

Sapiem

ping works
Firewall are disabled or allowed both app.

Indy10 TidHTTP works ok always.

Any lack in my code?

#14 Re: mORMot 1 » THttpApiServer doesn't work in netwrok with Win 7 » 2018-08-02 19:43:45

Sapiem

This network don't use DNS.
How check http.sys registration tables?

#15 mORMot 1 » THttpApiServer doesn't work in netwrok with Win 7 » 2018-08-02 18:13:21

Sapiem
Replies: 14

Hello:

I'm trying to connect 3 PC with Win 7 between them. One will have the server and will work as client too like the others.

Trying to use example with THttpApiServer, it work great in Win 10. In the Pc with Server/Client functions, it works ok too, but the others two doesn't see the server.

This is the Server code

procedure TForm16.SetServerBtnClick(Sender: TObject);
begin
  if SetServerBtn.Caption = 'Activar' then
    begin
      FileListBox1.Enabled := false;
      SetServerBtn.Caption := 'Desactivar';
      Props := TSQLDBSQLite3ConnectionProperties.Create(FileListBox1.FileName, '', '', 'password');
      try
        fProps := Props;
        Conn := fProps.ThreadSafeConnection;
        if not Conn.Connected then
          Conn.Connect;
        FServer := THttpApiServer.Create(false);
        FServer.AddUrl('', PortTxt.Text, false, '+', true);
        FServer.RegisterCompress(CompressDeflate);
        FServer.OnRequest := Process;
        FServer.Clone(31);

        try
          Memo1.Lines.Add('Servidor activo en http://' + ServerURLTxt.Text + ':' + PortTxt.Text + '/' + NL +
            'Base de datos disponible: ' + ExpandFileName(UTF8ToString(Props.ServerName)));
        except
          // error
          Memo1.Lines.Add('Ocurrió un error al mostrar datos de conexión.');
          FileListBox1.Enabled := true;
          SetServerBtn.Caption := 'Activar';
        end;
      except
        // error
        Memo1.Lines.Add('Ocurrió un error al intentar conectar.');
        FileListBox1.Enabled := true;
        SetServerBtn.Caption := 'Activar';
      end;

    end
  else
    begin
      FileListBox1.Enabled := true;
      SetServerBtn.Caption := 'Activar';
      FServer.Free;
      Memo1.Lines.Add('Desconectado');
    end;
end;

This is the Client code

function TForm16.BuscarSQL(const SQL: RawUTF8): RawUTF8;
var
  Http: THttpClientSocket;
begin
  Http := OpenHttp(ServerURLTxt.Text, PortTxt.Text);
  if Http <> nil then
    try
      Http.Post('', SQL, TEXT_CONTENT_TYPE);
      Result := Http.Content;
    finally
      Http.Free;
    end
  else
    Result := '';
end;

Locally works great, even using localhost as unique IP.

#16 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-07-09 02:28:11

Sapiem

ToClientDataSet return Unkown FieldType with string fields with more than 10000 characters....

#17 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-07-08 14:52:25

Sapiem

Well, I found this solution, is that right? This returns more than 29.000 records with 32 fields in only 3-4 seconds, ordered or not.

function TForm4.ExecuteSQL(SQL_CMD: RawUTF8; var DTS: TDataSource; var Conn: TSQLDBConnectionProperties): Integer;
var
  LockExecuteSQL: TSynLocker;
  SQL_TEMP, fJSONBuffer, fJSONBuffer_Temp: RawUTF8;
  Rows: ISQLDBRows;
  AContinue: boolean;
  ALastCod, TotRec: Integer;
  Tabl: TSQLTable;
begin
  LockExecuteSQL.Init;
  LockExecuteSQL.Lock;

  AContinue := True;
  ALastCod := 0;
  TotRec := 0;

  try
    try
      DTS.DataSet.Free;
      while AContinue do
        begin
          SQL_TEMP := SQL_CMD + NL + ' LIMIT 1000 OFFSET ' + IntToStr(ALastCod);
          Rows := Conn.Execute(SQL_TEMP, [], nil, false);
          fJSONBuffer_Temp := Rows.FetchAllAsJSON(True, @TotRec, false);
          fJSONBuffer := StringReplace(trim(fJSONBuffer) + trim(fJSONBuffer_Temp), '}][{', '},{', []);
          Inc(ALastCod, 1000);
          AContinue := Button1.Visible and (TotRec > 0);
          Application.ProcessMessages;
        end;
      Tabl := TSynSQLTableDataSet.CreateFromJSON(nil, fJSONBuffer).Table;
      if CheckBox1.Checked then  // Select if I want to order or not
        Tabl.SortFields([Tabl.FieldIndex('Nombre'), Tabl.FieldIndex('Fecha')], [], []);
      DTS.DataSet := ToClientDataSet(self, Tabl);
      DTS.DataSet.First;
      result := DTS.DataSet.RecordCount;
    except
      result := -1;
    end;
  finally
    LockExecuteSQL.UnLock;
  end;
  LockExecuteSQL.Done;

end;

And yes, I use index...

#18 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-07-08 04:21:10

Sapiem

I don't use any server, I open all databases locally or over local network as single file. At the end, I load all records, no paging, but I can break at any time. TClientDataSet uses a lot of memory, TDataSource.DataSet not.
If I found any way to order this DataSet when I have all records in hand, all I need will be resolved.

All tables in the database have an index.

Thanks, really thanks for all your attentions...

#19 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-07-07 16:55:23

Sapiem

But IMHO this is a design flaw of your query/database.

Why? Please help me...

I studied all of your recommendations and try this:

procedure TForm1.Button3Click(Sender: TObject);
var
  proxy: TSQLDBConnectionProperties;
  stmt: TSQLDBStatement;
  Timer: TPrecisionTimer;
  SQL_PEOPLE: RawUTF8;

  AContinue: Boolean;
  ALastCod, TotRec: Integer;
  CDS1: TClientDataSet;
begin
  AContinue := true;
  ALastCod := 0;
  TotRec := 0;

  Timer.Start;
  ds1.DataSet.Free;
  CDS1 := TClientDataSet.Create(self);
  CDS1.IndexDefs.Add('Nombre-Fecha','Nombre;Fecha',[ixDescending]);
  CDS1.IndexName:= 'Nombre-Fecha';

  fDBFileName := 'database.extdb';
  ConnProps := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(fDBFileName), '', '', StringToUTF8(pwd));

  proxy := ConnProps;
  try
    stmt := proxy.NewThreadSafeStatement;
    try
          while AContinue do
            begin
              SQL_PEOPLE := Memo1.Lines.Text + ' LIMIT 1000 OFFSET ' +
                IntToStr(ALastCod);
              stmt.Execute(SQL_PEOPLE, true);
              Inc(ALastCod, 1000);
              if not CDS1.Active then
                ToClientDataSet(CDS1, stmt, 0, cdsNew, false)
              else
                ToClientDataSet(CDS1, stmt, 0, cdsAppend, false);
              AContinue := TotRec <> CDS1.RecordCount;
              TotRec := CDS1.RecordCount;
            end;
          dbgrdData.DataSource.DataSet := CDS1;
          Caption := IntToStr(dbgrdData.DataSource.DataSet.RecordCount);
    finally
      stmt.Free;
    end;
  finally
    if proxy <> ConnProps then
      proxy.Free;
  end;
  lblTiming.Caption := 'Processed in ' + Ansi7ToString(Timer.Stop) + ' | Total: ' + IntToStr(TotRec);
end;

This works great (a lot of memory but work) and I can interrupt the code changing the value of AContinue. But, this code, as I wrote a long time before, raise an error when I use SQL code like this:

SELECT 'This is a test' Title FROM AnyTable;  // Create a new Field with string value as SQLite can do

raise GetFieldData ColumnType=Unknown error, that's why I can't use ToClientDataSet.

in the other hand, this works better (30,000 records / 4 sec, I can break, etc), but I can't use Order By command (works but very very very slow then)

function TForm4.ExecuteSQL(SQL_CMD: RawUTF8; var DTS: TDataSource; var Conn: TSQLDBConnectionProperties): Integer;
var
  LockExecuteSQL: TSynLocker;
  SQL_TEMP, fJSONBuffer, fJSONBuffer_Temp: RawUTF8;
  Rows: ISQLDBRows;
  AContinue: boolean;
  ALastCod, TotRec, T: Integer;
begin
  LockExecuteSQL.Init;
  LockExecuteSQL.Lock;

  AContinue := True;
  ALastCod := 0;
  TotRec := 0;

  try
    try
      DTS.DataSet.Free;
      while AContinue do
        begin
          SQL_TEMP := SQL_CMD + NL + ' LIMIT 1000 OFFSET ' + IntToStr(ALastCod);
          Rows := Conn.Execute(SQL_TEMP, [], nil, false);
          fJSONBuffer_Temp := Rows.FetchAllAsJSON(True, @TotRec, false);
          fJSONBuffer := StringReplace(trim(fJSONBuffer) + trim(fJSONBuffer_Temp), '}][{', '},{', []);
          Inc(ALastCod, 1000);
          AContinue := Button1.Visible and (TotRec > 0);
          Application.ProcessMessages;
        end;
      DTS.DataSet := TSynSQLTableDataSet.CreateFromJSON(nil, fJSONBuffer);
      DTS.DataSet.First;
      result := DTS.DataSet.RecordCount;
    except
      result := -1;
    end;
  finally
    LockExecuteSQL.UnLock;
  end;
  LockExecuteSQL.Done;

end;

thanks

#20 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-07-01 19:58:03

Sapiem

No, no. I have a database with more than 10 years of medical records. When I need that I talking about is because I request records with specifics datas and then I show all like plain tables. I use "union", Hex commands, etc. The results are rows and columns that I can "translate" then to plain table.

example this simple query:

select "EDS" Dato, count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01')
union
select "   "||"Hp" Dato,count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and (muestras <>"")
union
select "   "||"SM" Dato,count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SM")
union
select "   "||"SH" Dato,count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SH")
union
select "   "||"SP" Dato,count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SP")
union
select "   "||"CE" Dato,count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and (origen = "CE")
union
select "   "||"OS" Dato,count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and (origen = "Ot1")
union
select "   "||"Di1" Dato,count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' = 0
union
select "   "||"Te1" Dato,count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' != 0
union
select "COL" Dato, count() Total from COL where (fecha between '2007-01-01' and '2019-01-01')
union
select "   "||"SM" Dato,count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SM")
union
select "   "||"SH" Dato,count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SH")
union
select "   "||"SP" Dato,count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SP")
union
select "   "||"CE" Dato,count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and (origen = "CE")
union
select "   "||"OS" Dato,count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and (origen = "Ot1")
union
select "   "||"Di1" Dato,count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' = 0
union
select "   "||"Te1" Dato,count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' != 0
union
select "ENT" Dato, count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01')
union
select "   "||"SM" Dato,count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SM")
union
select "   "||"SH" Dato,count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SH")
union
select "   "||"SP" Dato,count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SP")
union
select "   "||"CE" Dato,count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and (origen = "CE")
union
select "   "||"OS" Dato,count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and (origen = "Ot1")
union
select "   "||"Di1" Dato,count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' = 0
union
select "   "||"Te1" Dato,count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' != 0
union
select "CPR" Dato, count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01')
union
select "   "||"SM" Dato,count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SM")
union
select "   "||"SH" Dato,count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SH")
union
select "   "||"SP" Dato,count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SP")
union
select "   "||"CE" Dato,count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and (origen = "CE")
union
select "   "||"OS" Dato,count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and (origen = "Ot1")
union
select "   "||"Di1" Dato,count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' = 0
union
select "   "||"Te1" Dato,count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' != 0
union
select "LAP" Dato, count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01')
union
select "   "||"SM" Dato,count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SM")
union
select "   "||"SH" Dato,count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SH")
union
select "   "||"SP" Dato,count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and (origen = "SP")
union
select "   "||"CE" Dato,count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and (origen = "CE")
union
select "   "||"OS" Dato,count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and (origen = "Ot1")
union
select "   "||"Di1" Dato,count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' = 0
union
select "   "||"Te1" Dato,count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and hex(Ter1)+'' != 0
union
select "ANE (EDS)" Dato, count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and ((d1||es1||u1||es2 LIKE "%Ane1%") or (d1||es1||u1||es2 LIKE "%Ane2%") or (d1||es1||u1||es2 LIKE "%Ane3%"))
union
select "   "||"Ane1" Dato, count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and ((d1||es1||u1||es2 LIKE "%Ane1%"))
union
select "   "||"Ane2" Dato, count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and ((d1||es1||u1||es2 LIKE "%Ane2%"))
union
select "   "||"Ane3" Dato, count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and ((d1||es1||u1||es2 LIKE "%Ane3%"))
union
select "ANE (COL)" Dato, count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and ((Descrip LIKE "%Ane1%") or (Descrip LIKE "%Ane2%") or (Descrip LIKE "%Ane3%"))
union
select "   "||"Ane1" Dato, count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and ((Descrip LIKE "%Ane1%"))
union
select "   "||"Ane2" Dato, count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and ((Descrip LIKE "%Ane2%"))
union
select "   "||"Ane3" Dato, count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and ((Descrip LIKE "%Ane3%"))
union
select "ANE (ENT)" Dato, count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and ((Ante||Retr LIKE "%Ane1%") or (Ante||Retr LIKE "%Ane2%") or (Ante||Retr LIKE "%Ane3%"))
union
select "   "||"Ane1" Dato, count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and ((Ante||Retr LIKE "%Ane1%"))
union
select "   "||"Ane2" Dato, count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and ((Ante||Retr LIKE "%Ane2%"))
union
select "   "||"Ane3" Dato, count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and ((Ante||Retr LIKE "%Ane3%"))
union
select "ANE (CPR)" Dato, count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and ((Descrip LIKE "%Ane1%") or (Descrip LIKE "%Ane2%") or (Descrip LIKE "%Ane3%"))
union
select "   "||"Ane1" Dato, count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and ((Descrip LIKE "%Ane1%"))
union
select "   "||"Ane2" Dato, count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and ((Descrip LIKE "%Ane2%"))
union
select "   "||"Ane3" Dato, count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and ((Descrip LIKE "%Ane3%"))
union
select "ANE (LAP)" Dato, count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and ((h1||v1||b1||g1||Ap1||Ot1 LIKE "%Ane1%") or (h1||v1||b1||g1||Ap1||Ot1 LIKE "%Ane2%") or (h1||v1||b1||g1||Ap1||Ot1 LIKE "%Ane3%"))
union
select "   "||"Ane1" Dato, count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and ((h1||v1||b1||g1||Ap1||Ot1 LIKE "%Ane1%"))
union
select "   "||"Ane2" Dato, count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and ((h1||v1||b1||g1||Ap1||Ot1 LIKE "%Ane2%"))
union
select "   "||"Ane3" Dato, count() Total from LAP where (fecha between '2007-01-01' and '2019-01-01') and ((h1||v1||b1||g1||Ap1||Ot1 LIKE "%Ane3%"))
union
select "Colocación de PB" Dato, count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and ((Ter1 LIKE "%Colocación de %") or (Ter1 LIKE "%Cambio de %"))
union
select "Polip con ASA realizadas" Dato, sum(T1) Total from (
select count() T1 from EDS where (fecha between '2007-01-01' and '2019-01-01') and (Ter1 LIKE "%ASA%")
union
select count() T1 from COL where (fecha between '2007-01-01' and '2019-01-01') and (Ter1 LIKE "%ASA%")
union
select count() T1 from ENT where (fecha between '2007-01-01' and '2019-01-01') and (Ter1 LIKE "%ASA%")
)
union
select "   "||"EDS" Dato, count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and (Ter1 LIKE "%ASA%")
union
select "   "||"COL" Dato, count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and (Ter1 LIKE "%ASA%")
union
select "   "||"ENT" Dato, count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and (Ter1 LIKE "%ASA%")
union
select "Bx realizadas" Dato, sum(T1) Total from (
select count() T1 from EDS where (fecha between '2007-01-01' and '2019-01-01') and (Bx <> "")
union
select count() T1 from COL where (fecha between '2007-01-01' and '2019-01-01') and (Bx <> "")
union
select count() T1 from ENT where (fecha between '2007-01-01' and '2019-01-01') and (Bx <> "")
union
select count() T1 from CPR where (fecha between '2007-01-01' and '2019-01-01') and (Bx <> "")
)
union
select "   "||"EDS" Dato, count() Total from EDS where (fecha between '2007-01-01' and '2019-01-01') and (Bx <> "")
union
select "   "||"COL" Dato, count() Total from COL where (fecha between '2007-01-01' and '2019-01-01') and (Bx <> "")
union
select "   "||"ENT" Dato, count() Total from ENT where (fecha between '2007-01-01' and '2019-01-01') and (Bx <> "")
union
select "   "||"CPR" Dato, count() Total from CPR where (fecha between '2007-01-01' and '2019-01-01') and (Bx <> "")

#21 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-07-01 00:20:55

Sapiem

Sorry, but I need all data from json in dataset. How can I join all this data from limit option. For example, if I retrieve whit limit for example 15 times, how can I join all this 15 groups of records in the same dataset?

igors233: I don't create DataSet in thread, I pass a DataSet as var. This work great.

Is there any way that I can stop a created thread? If the user want to wait for all result, ok, but if it want to stop or abort the SQL, so, abort without result. That's I want or hope.

Thanks

#22 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-06-30 05:12:49

Sapiem

All works great since I use mORMot, thanks.

But I have another question: is there any way to stop FetchAllAsJSON() function when it delay a lot? I use it to get sometimes information from 10 years of records and it takes a lot of time. I want to put an option that the user can abort this.

I put the code inside a thread, all works great, but a I can stop it externally...

Thanks again

function TMain.ExecuteSQL(SQL_CMD: RawUTF8; var DTS: TDataSource; var Conn: TSQLDBConnectionProperties): Integer;
var
  fJSONBuffer: RawUTF8;
  Rows: ISQLDBRows;
  LockExecuteSQL: TSynLocker;
  ExecSQL: TExecuteSQL;
begin

  LockExecuteSQL.Init;
  LockExecuteSQL.Lock;

  if Screen.Cursor = 0 then
    Screen.Cursor := AnimCur;

  try
    ExecSQL := TExecuteSQL.Create(SQL_CMD, DTS, Conn);
    ExecSQL.Execute;
    Result := ExecSQL.WaitFor;
    ExecSQL.Free;

  finally
    LockExecuteSQL.UnLock;
  end;
  LockExecuteSQL.Done;

  Screen.Cursor := 0;

end;

constructor TExecuteSQL.Create(SQL_CMD: RawUTF8; var DTS: TDataSource; var Conn: TSQLDBConnectionProperties);
begin
  inherited Create(true);
  SQL_CMD_1 := SQL_CMD;
  DTS_1 := DTS;
  Conn_1 := Conn;
  Resume;
end;

procedure TExecuteSQL.Execute;
var
  fJSONBuffer: RawUTF8;
  Rows: ISQLDBRows;
begin
  try
    DTS_1.DataSet.Free;
    Rows := Conn_1.Execute(SQL_CMD_1, [], nil, false);
    fJSONBuffer := Rows.FetchAllAsJSON(false);
    DTS_1.DataSet := TSynSQLTableDataSet.CreateFromJSON(nil, fJSONBuffer);
    DTS_1.DataSet.First;
    ReturnValue := DTS_1.DataSet.RecordCount;
  except
    ReturnValue := -1;
  end;

  Terminate;
end;

#23 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-04-15 02:44:56

Sapiem

Using private copy of TSQLTableJSON constructor without using pointer(fJSONBUffer) fix all problems

Thanks a lot....!!!!!!!!!!!!!

#24 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-04-13 19:38:40

Sapiem

And about the problem with drawgrid?

#25 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-04-13 12:12:29

Sapiem

Ok, I studied and extract from sample 12- SynDB Explorer all code. It open ok all my databases, but when I copy this code inside my principal app, it doesn't work properly. Look the pictures.

Just loaded (ok)
J5sxS8p.png

After click on DrawGrid
LkIoWJy.png

In the other hand. When I create JSONBuffer at first time, all goes ok, but when I try to reuse fJSONBuffer variable, it doesn't work, so I need to Fetch again. Why?

fJSONBuffer := Rows.FetchAllAsJSON(false);
Table := TSQLTableJSON.Create('',pointer(fJSONBuffer),length(fJSONBuffer));

I noticed that after TSQLTableJSON. Create, fJSONBuffer variable changes from:

'{"fieldCount":4,"values":["Nombre","HC","CI","ID","Lino Francisco Blanco Luis","20182","",4,...

to this:

'{"fieldCount":4,"values":["Nombre'#0#0'"HC'#0#0'"CI'#0#0'"ID'#0#0'"Lino Francisco Blanco Luis'#0#0'"20182'#0#0'"'#0#0'4'#0'...

may be this is the problem that avoid reuse fJSONBuffer. My intention is load fJSONBuffer only one time, and after reuse it to filter data that will be loaded in DrawGrid

Thanks

#26 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-03-24 14:22:32

Sapiem

Well, I try, but I load all records directly to a DBGrid in this case (just names and medical #). So, if I use this pagination, I need to load echa block to a Grid cell by cell, no?

DBGrid load al dataset ance time, not by blocks, no?

This may be help, I'll try

#27 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-03-23 17:35:29

Sapiem

Thanks, I'll try it too

#28 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-03-22 12:01:59

Sapiem

This what I do and works great. It's ok?

function TForm9.ExecuteSQL(SQL_CMD: RawUTF8; var DTS: TDataSource): integer;
var
  fJSONBuffer: RawUTF8;
  Rows: ISQLDBRows;
  Table: TSynSQLTableDataSet;
begin
  try
    BDFileName:= OpenDialog1.FileName;
    MainConnProps:= TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(BDFileName),'','',StringToUTF8(pwd));
    Rows:= MainConnProps.Execute(SQL_CMD,[],nil,true);
    fJSONBuffer:= Rows.FetchAllAsJSON(false);
    Table:= TSynSQLTableDataSet.CreateFromJSON(nil,fJSONBuffer);
    Table.First;
    Result:= Table.RecordCount;
    Caption:= IntToStr(Result);
    Application.ProcessMessages;
    DTS.DataSet:= Table;
  except
    Result:= -1;
  end;
  if MainConnProps.MainConnection.IsConnected then
    MainConnProps.Free;
end;

I only use a grid one time. Most time I use dataset directly.

#29 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-03-21 19:49:36

Sapiem

I only need read data. Write data I use mORMot with Execute directly. Can use it?

#30 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-03-21 18:41:29

Sapiem

Yes, we need all records when we need to save all reports in pdf with fastreport. Doesn't matter if the databse is from medical records, I know, sorry.

Now I was looking for in this forum I see TSynSQLTableDataSet, It will help?

Please, I'm a doctor and I love delphi programming, but not specialist jaja

#31 Re: mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-03-21 18:25:30

Sapiem

No, I said that when I use LIMIT 500 it works great, but when I need all records (is a medical database), it raise Out of Memory.

Using sample "17 - TClientDataset use" it returns the same error using ToClientDataSet, no ToDataSet, wich return corrupted data.

#32 mORMot 1 » ToClientDataSet "Out of Memory" with 32 fields and 29000 records » 2018-03-21 17:34:15

Sapiem
Replies: 34

Hello:

I'm working a lot migrating from ZeosLib to mORMot with sqlite3 database, and after longs days, now I notice that using ToClientDataSet function in my database with 32 fields (most Memos, not more than 1000 words each) and 29000 records, it raise  a Out of Memory error. If I use LIMIT to 500 for example, it works ok.

With Zeos, it doesn't happend. I'm migrating because I need use this sqlite3 database in network (I know that it's a big risk) and mORMot now even load it over the net.

Please, help...

#33 Re: mORMot 1 » SELECT return error data » 2018-01-25 12:35:36

Sapiem

Using Table (sample 12 - SynDB Explorer) plus ToDataSet function solve the problem.

#34 Re: mORMot 1 » SELECT return error data » 2018-01-17 04:34:53

Sapiem

I try but the mORMotVCL.pas unit never si used by code.

#35 Re: mORMot 1 » SELECT return error data » 2018-01-16 13:45:31

Sapiem

Ok, I'll check at home. Sorry, but I'm doctor and I have a lot of professional work. Thanks for all.

#36 Re: mORMot 1 » SELECT return error data » 2018-01-16 12:56:04

Sapiem

Look at the Caption bar. This is the result of showing the content of the field that is really empty, but it shows me the union of the previous field next to the next one. Look at the Grid: I am showing the field "Equipo" and it shows me the union of the "CI" field next to the following "DatosClin".

LwQH4YO.png

#37 Re: mORMot 1 » SELECT return error data » 2018-01-15 21:06:24

Sapiem

Hello?

#38 Re: mORMot 1 » SELECT return error data » 2018-01-11 03:26:26

Sapiem

How can I send you a screenshot from my PC?

#39 Re: mORMot 1 » SELECT return error data » 2018-01-10 03:16:06

Sapiem

Screenshot?

#40 Re: mORMot 1 » SELECT return error data » 2018-01-09 17:22:52

Sapiem

I don't understand you. I use the same code of example # 17. It use this datasource (ds1: TDataSource;)

#41 Re: mORMot 1 » SELECT return error data » 2018-01-09 11:37:50

Sapiem

The code is the same of example # 17, the only change is the database that I open. I still working with example # 17 until I hace all that I need and then migrate all my original code to mORMot. I'll try to extract some piece of databse and try to reproduce this error. I use Delphi Berlin.

#42 mORMot 1 » SELECT return error data » 2018-01-08 18:36:58

Sapiem
Replies: 14

I am trying to recode my app, migrating the SQLite usage code from ZeosLib to mORMot. All right, until I realize that sometimes, when there are empty fields, the SQL SELECT returns the value of that empty field, with the value of the following fields, example:

Suppose:
Field1 = 'Value 1'
Field2 = ''
Field3 = 34
Field4 = 'Blue'

SELECT Field1, Field2, Field3, Field4 FROM Table 1

Field1 = 'Value 1'
Field2 = '34Blue'
Field3 = 34
Field4 = 'Blue'

Sometimes, when the number of records returned by the SELECT is greater than one, the empty fields remain correctly empty.

#43 Re: mORMot 1 » Help for new user » 2018-01-04 19:45:43

Sapiem

Any answer? Please....

#44 Re: mORMot 1 » Help for new user » 2017-12-28 17:10:32

Sapiem

With ZeosLib I hve this:

Table Persons -> Name TEXT field (only this)

SQL:= SELECT 'Blue' Color, Name FROM Persons;

Return

Color    Name
Blue     Wilson
Blue     Patric

etc


Ok, I create a new Field by SQL in the fly, but with mORMot this return:
Color                                               Name
??Blue?(+ symols: arrows, squares etc)     Wilson
Blue(+ symols)                                  Patric

Why?

#45 Re: mORMot 1 » Help for new user » 2017-12-20 20:45:51

Sapiem

I don't use Zeos connection. I use code TSQLDBSQLite3ConnectionProperties (example 17 - TClientDataSet use)

I only change database name with my DB name, uncrypted.

#46 Re: mORMot 1 » Help for new user » 2017-12-20 18:39:11

Sapiem

My database is UTF8. When I load with example # 17, it shows in DataSet incomplete text. NO warning in compilation.

I have ZeosLib 7.2 version. Any recent version?

Recreate the database?

#47 Re: mORMot 1 » Help for new user » 2017-12-20 17:53:05

Sapiem

Hello:

Well, now I can use mORMot even with FastReport, but I've another trouble that I can't solve yet. My old database was encoded with UTF8 and I have been using Delphi 2006. Now I want to upgrade all code to Delphi 10.1 Berlin, and when I query some data, mORMot it responds with incomplete text. Trying it the DB with ZeosLib as before in Delphi 2006, I can see that the problems is that controls (TDataSet, TDBMemo, etc) in Delphi 10.1 load data in UTF16, when mORMot work with UTF8, right?

How can I fix this?

Thanks

#48 Re: mORMot 1 » Help for new user » 2017-12-14 00:15:57

Sapiem

Well, I try now the same code but in Delphi 10 Berlin and it work perfectly... I use Delphi 2006. I will try to migrate all my code to Delphi Berlin (unicode)

So, the problem is (almost) in Delphi 2006

#49 Re: mORMot 1 » Help for new user » 2017-12-12 18:13:52

Sapiem

Yes I download the latest. I always download mORMotNightlyBuild.zip and sqlite3obj.7z. I's that ok?

#50 Re: mORMot 1 » Help for new user » 2017-12-12 11:17:10

Sapiem

Even using it. Example 17 show error. First siad json error trying load onCreate code. Raise error and never enter in test.db3 loading.

Changing this part, it load, but show ole error too.

Pages: 1 2

Board footer

Powered by FluxBB