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.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?
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
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...
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('SharedCache=true'); //we have tested also with false, and the same
[The 'reading' connection in the main UI]
con1.DriverName := 'SQLite';
con1.Params.Add('SharedCache=true'); //we have tested also with false, and the same
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
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...
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?