David A5310

Member since: Monday, 21 December 2015
Last login: 3 years ago
Profile viewed: 963 views

No Rank
Points: 0

David A5310 created a new topic ' Oracle, Array DML and cursors' in the forum. 3 years ago

Hi,
I have an application that works against an Oracle 11G database, using firedac.
So normally the app calls a storedproc, that has next parameters in the database (I show the header of the Oracle function):

CREATE OR REPLACE PROCEDURE Tratador   (User   IN PLS_INTEGER,
                                        Data IN varchar2,
                                        OutputData IN OUT SYS_REFCURSOR)

In Delphi code, it's expected after the execution of the storedproc, a cursor as output, and this data is extracted this way:
      FDStoredProc1.ExecProc;
      FDStoredProc1.Open;
      FDStoredProc1.First;
      while not FDStoredProc1.EOF do
      begin
        LTmp := FDStoredProc1.Fields[0].AsString;
        memo1.Lines.Add(LTmp);
        FDStoredProc1.Next;
      end;

In the previous example, we write in a memo the output of the cursor (a table with one column of varchar type of some size
This works fine.

Now, I'm trying to optimize this code, and I would like to convert it to an array dml code.
It's easy to convert the filling of input parameters and the call itself, the problem is the extraction of the output cursors, but I don't know how to do it. If I use the same technique than before converting to array dml, I obtain the data of the first cursor, but I don't know how to access to next cursors.
Next I show the code for the filling of params and the call to ths toredproc:
  FDConnection1.StartTransaction;
  FDStoredProc1.Prepare;
  FDStoredProc1.FetchOptions.AutoClose := False;
  FDStoredProc1.Params.ArraySize := 5;
  for I := 0 to 4 do
  begin
    FDStoredProc1.Params.ParamByName('User').AsIntegers[I] := I;
    FDStoredProc1.Params.ParamByName('Data').AsStrings[I] := '00002800000000_' + I.ToString;
  end;
  FDStoredProc1.Execute(5, 0);
  FDConnection1.Commit;

Now, attempts of how to extract the data:
1) As before, I only extract the output data of the execution of the first storedpr.

2) I imagine that maybe a solution is to use the param someway, and cast it to some type so I can browse the data, but I don't know what can I do here.
  for I := 0 to 4 do
  begin
    MiDataSet := FDStoredProc1.Params.ParamByName('OutputData ').AsDataSets[I] as TFDDataSet;
  end;

Previous code is an example of a fail attemp, the 'MiDataset' variable is always nil
I'm blocked at this point and don't know how to solve the problem. Any idea?

Read More...

Hi David, the wellcome page would be more practice (at least for us as we manage many projects), if the 'favorite projects' section could manage at least one level of folders, in order to sort the different kind of projects by different criteria. So

David A5310 replied to the topic 'SQLite, table locked (Firedac, Delphi Berling)' in the forum. 5 years ago

Hi all,
the problem seems to be that until all the rows of the query are fetched (so you must move in the dbgrid until the end, or provoque the fetchall), the database mantains locked.
So we have done some kind of pagination to see the results. We have tried the pagination with the fetchoptions of the query, using the properties RecsMax and RecsSkip, but the last one does not work so you can't navegate properly (a bug in component?), so we paginate inserting in the query the sqlite grammar for this objetive: 'select * from table limit offset, pagesize'. So the query is fast this way, and the problem is minimized. But, not sure if this is the best solution...

Read More...

David A5310 created a new topic ' SQLite, table locked (Firedac, Delphi Berling)' in the forum. 5 years ago

Hi,
After not much time of execution next error is raised: database table is locked
We want to use a sqlite database and from the same process we want to insert rows to a table of the database, and also see in a dbgrid the rows inserted, filter them,...etc...
Then, the architecture of the process is:

    • one thread is doing inserts to the database (to the same table) when needed. At the beggining of the execution, clears the table

    • from main thread (ui), we have a ttimer that periodically executes a query (the firedac connection to the bd is different that the one used in the thread). So in the form we have a dbgrid that is linked to this connection with a datasource. The query only executes 'select' statements

    • In testing environment, the thread is inserting 5 rows per second, and the timer is refreshing data every 5 seconds

    • After a while, the thread starts raising the errors, and once starts raising the errors it is raising them very very often

    • Looks like the query blocks the database, because we stop the querys and the main thread continues working with no problems (the errors dissapear).

We have set next parameters to the connections:

[The 'writing' connection in the thread]
FConexionBD.DriverName := 'SQLite';
FConexionBD.Params.Add('Database=.\BD_PaneldeMando.db');
FConexionBD.Params.Add('OpenMode=CreateUTF8');
FConexionBD.Params.Add('LockingMode=Normal');
FConexionBD.Params.Add('Synchronous=Normal');
FConexionBD.Params.Add('SharedCache=true'); //we have tested also with false, and the same
FConexionBD.Params.Add('UpdateOptions.LockWait=True');
FConexionBD.Params.Add('BusyTimeout=10000');

[The 'reading' connection in the main UI]
con1.DriverName := 'SQLite';
con1.Params.Add('Database=.\BD_PaneldeMando.db');
con1.Params.Add('OpenMode=ReadOnly');
con1.Params.Add('LockingMode=Normal');
con1.Params.Add('Synchronous=Normal');
con1.Params.Add('SharedCache=true'); //we have tested also with false, and the same
con1.Params.Add('UpdateOptions.LockWait=True');
con1.Params.Add('BusyTimeout=10000');

So, any idea what we are doing wrong? As we have read about sqlite, we should have no problems for doing this.
Thanks in advance

Read More...

David A5310 replied to the topic 'Timeout in connection - Firedac and Oracle' in the forum. 5 years ago

We think we have found the solution.
From another thread we check if the thread that makes the operations is stopped (freezy), and if so we launch a ping 'conection.Ping' and this way the problem is discovered, and the exceptions are launched in the 'operations thread'.
I wonder if this kind of operations (checks) should be done internally by the TFDConnection component itself...

Regards

Read More...

David A5310 created a new topic ' Timeout in connection - Firedac and Oracle' in the forum. 5 years ago

Hi,
we are experimenting a extrange problem in the connection to a BD.
The Oracle bd is placed in another computer, with a connection with problems, there are frecuent disconnections to that BD due to problems in the network.
We have set up the timeouts for the commands, and they work fine, the timeouts are there.
After the detection of a timeout, we disconnect from the remote BD and try to reconnect again. In this process we destroy the TFDConnection object and create it again. But sometimes we are experimenting problems in the connection, we execute the command 'Conection.Connected := True;' in order to connect, and the thread stops there (freezy) for ever (and no, there is no exception, we catch all of them).
Exists the possibility to configure the timeout in the connection process? (we don't found anything similar) or maybe there is another parameter to help in this issue?
Regards

Read More...

DavidI is friends with David A5310

David A5310 created a new topic ' Roadmap update, 2016' in the forum. 6 years ago

Hi!
Not sure where to post this topic, so sorry if it's not the best place :)
Are there any news about the roadmap? Is there a planned date for the publication of the roadmap?
Regards

Read More...