Jump to content

Hans J. Ellingsgaard

Members
 View Profile  See their activity

  • Content Count

    84

  • Joined

  • Last visited

Posts posted by Hans J. Ellingsgaard

  2. Posted

    If you make a select count query on an indexed field, it should only take a ms to execute. The resultset will only have one record. 

    If your table only have 1500 records, the dataset.RecordCount should also respond quckly (not as quick as the record count thoug). 

    There must be something else that goes wrong.

     

    What database are you using?

    Have you tried to run the queries directly in a db manager?

  9. Posted

    8 hours ago, pieomy00 said:

    try       BitmapListAnimation1.AnimationBitmap.LoadFromFile(MyFile);     finally     end;

    I'm no expert in threads, but I'm shure that a LoadFromFile should be synchronized. As stated above the try/finally is pointless. 

     

    If you only have these two commands in the thread, and both will be synchronized, there will be no point in putting them in a thread. It will probably just slow your program down.

  11. Posted

    3 hours ago, Mark Williams said:

    They don't have to be. But with large amounts of data it I find it is much faster to load the data from a local file and refresh it from the server in the background. 

    But you would get rid of all the trouble of sync‘ing the data, if you kept the data on the server.

    With a REST service you will probably be able to load the data much faster, and save a lot of bandwith - at least if you are on a slow network.

    • Like 1

  13. Posted

    On 12/19/2019 at 1:50 PM, Yaron said:

     

    The view number is read earlier in the code, I actually replaced it with a more reliable SQL command to increase the value instead of setting it: 

    
procedure ClubHouseDB_IncCampaignViewCount(sDebugFile,sGalleryUID : String);
var
  dbConn     : TFDConnection;
  dbQuery    : TFDQuery;
  deadLocked : Boolean;
begin
  deadLocked := False;
  dbConn  := TFDConnection.Create(nil);  dbConn.ConnectionDefName := dbPoolName;
  dbQuery := TFDQuery.Create(nil);       dbQuery.Connection       := dbConn;
  Try
    dbQuery.SQL.Text   := 'UPDATE GALLERY_TABLE SET VIEW_COUNT=VIEW_COUNT+1 WHERE GALLERY_UID=:galleryuid;';
    Try
      dbQuery.Prepare;
      dbQuery.ParamByName('galleryuid').AsString := sGalleryUID;
      dbQuery.ExecSQL;
    Except
      on E : Exception do If Pos('deadlock',Lowercase(E.Message)) > 0 then deadLocked := True;
    End;
  Finally
    dbQuery.Free;
    dbConn.Free;
  End;
  If deadLocked = True then ClubHouseDB_IncCampaignViewCount(sDebugFile,sGalleryUID);
end;

    As you can see from the updated code, I'm catching the deadlock by looking at the returned exception message, I'm not sure it's the best approach, but I haven't been able to find another way to identify the deadlock.

     

    I don't have a problem increase the deadlock timeout, but I couldn't find anything on how to do that, I'm not even sure if it's part of the Firebird SQL statement or if I need to specify it through the Firedac components.

     

    You can minimize the deadlock problem, if you call starttransaction just before the ExecSQL, and call CommitTransaction right after ExecSQL. Then you also can make a controlled RollBack in case of an error.

    • Like 1

  16. Posted

    You could have just one FDConnection, and let all the other datamodules use that connection. You would then have just one place to set up all your connection logic,

     

    And by the way... Some SQL servers have licens restrictions on how many simultanious connections you can have, and if your are using one connection for each datamodule, you could easily end up being unable to connect to the SQL server.

  18. Posted

    If you can call the Generator directly, like in Interbase/Firebird and other db's, I would prefer
    that approach. I can't find any documentation that this is possible with Pervasive db's, but you can
    check it out.

     

    Create a query that calls the Generator and wrap it in a function like this. (The example is made in
    Interbase syntax).

     

    function GetGenID(GeneratorName: string): integer;
    begin
        with FDQGetGenID do
        begin
            SQL.Clear;
            SQL.ADD('SELECT GEN_ID ('+GeneratorName+', 1) from rdb$database');
            Open;        
            Result := Fields[0].AsInteger;
            Close;
        end;
    end;

    Then when you insert new data you will call the GetGenID function.

    FDQuery.insert;
    FDQueryID.Value:=GetGenID('GeneratorName');
    FDQueryMASTERDATA.AsString:= 'Some value';
    FDQuery.Post;

     

    You can also place the call to GetGenID in the FDQuery events, like OnNewRecord or BeforeInsert.

     

    ---
    If this approach is not possible with Pervasive, then this approach could work. It is at least working with Interbase.
    In this example I have used a DBGrid and A DataSource component to connect to the FDQuery. And the query has two fields
    called "ID" and "MASTERDATA".

     

    Set the AutoGenerator parameter value of ID field to arAutoInc (if you've not already done so).

     

    In the OnDataChange Event of the DataSource put the following code:

     

    if (FDQMasterID.Value < 0) and (FDQMaster.State in [dsInsert]) and not FDQMasterMASTERDATA.IsNull then
    begin
       FDQMaster.Post;
       FDQMaster.Refresh;
    end;

     

    You need to check if at least one of the fields has a value apart from the ID field, or you will get
    an error on the Post command, if the user adds to empty records.
     

  20. Posted

    On 4/1/2019 at 2:11 PM, GreatDayDan said:

    Better yet, How do I an in-memory db instead of an on-disk db? I have tried setting the FDConnection filename to :memory: but that did not work.

    You can use FDMemTables, they can load and save from streams, files and other datasets. 

  21. Posted

    Yes, I can see that from your link. It came as a surprice to me, as I am mostly used to work with IB and Firebird databases.

     

    There is at NOLOCK isolationlevel, that will minimize risk of locking, but it will not be suitable, if there is a risk of data being changed during the execution of the query. Another thing to do is to make sure that all the fields in the where clause and the joins are indexed to avoid tablescans.

  24. Posted

    You can do it with two queries. Make one query that select's all the fields that you want in the grid (don't use "select *", use the wanted fieldnames instead). Then make another query with a parameter that select's the clob field for one record only. Like this:

     

    select [clob field] 

    from [table name]

    where [Primary Field ID] = :ParameterName 

     

    Then you open the second query after the first query has entered the wanted record. The parameter will get it's value from the first query.  There might be an event on the grid you can use - I don't know the DevEx grid. You can also use the AfterScroll event of the dataset to open the second query.

     

    If you use the second approach, you will need to have a delay on the opening of the second query, or it will open after all scrolled records. You can use a timer to have a small delay. Disable it on the BeforeScroll event and enable it on the AfterScroll event. The delay should just be long enough to make shure that it is not triggering while the user is scrolling.

  25. Posted

    If your sql server is not on the same machine as your client, it might be that you don't have the right sql driver verison. Look into the ODBC Data Source Administrator for what version of sql driver you have. E.g for a SQL server 2008 you need at least a version 10.00. I'm not sure wich version you need for server 2012, but it is probably at bit higher than that. You can download the correct version from Microsoft.

×