Question for IBX developers lazarus / 16-Databases ---------------------------------------------------------------------- ttomas · 2021-09-13 17:42 In FB Pascal API, in file FB30Statement.pas (also in FB25Statement) in function TFB30Statement.InternalExecute, line 1393, also in InternalOpenCursor line 1439, there is a code Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   if (FSQLParams.FTransactionSeqNo < (FTransactionIntf as TFB30transaction).TransactionSeqNo) then    IBError(ibxeInterfaceOutofDate,[nil]); Why is this necessary? Any problem without this code? You can prepare statement in one transaction and execute same statement with another transactions. For example for bulk inserts, prepare statement and loop with commit/starttransaction every X rows. I'm working on mormot2 Firebird driver using your FB Pascal API to have fine control of transactions. In mORMot I implement Prepare and Execute interface of mormot db layer, but mormot cache statements and call only Execute for cached statements. This call can arrive with different transaction (I hard commit every statement execution if explicit transaction is not started). I comment this lines in my code and no problems found with testing. Can this line be removed or some how configure to use/not use in FB Pascal API? ---------------------------------------------------------------------- ttomas · 2021-09-14 01:30 More details and source of mormot ibx interface at https://synopse.info/forum/viewtopic.php?pid=35743#p35743 ---------------------------------------------------------------------- tonyw · 2021-09-14 08:58 Will IBX work without this check - the answer is yes it will. This is really an internal consistency check to avoid stale references. When an SQL Statement is prepared, the data areas created to hold the statement parameters and results are tagged with the internal sequence number of the transaction used to prepare the query. Later on, if you try and execute the query/open the cursor and there is a mis-match between the current transaction sequence number and the sql parameters transaction sequence number then an exception is raised. The same check is applied when closing a cursor. The check was put there out of concerns that a COM interface to an SQL Param or result may get out of step with the underlying query. Has this ever happened in practice? I don't recall seeing the error since early testing. Perhaps it could be removed - but then without it there may be, at some time in the future, a hard to diagnose error when setting a param or reading a result - so there seems to be no good reason to remove this check. ---------------------------------------------------------------------- ttomas · 2021-09-14 21:41 Thanks tonyw for your response. I understand your concern to remove this code. Please, can you consider to add some property or method in IAttachment for ex. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i att.DisableInterfaceOutOfDateCheck := True;Default will be False and no problem for existing and future code. And change if statement to: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   if (not FAttachmentIntf.DisableInterfaceOutOfDateCheck) and     (FSQLParams.FTransactionSeqNo < (FTransactionIntf as TFB30transaction).TransactionSeqNo) then    IBError(ibxeInterfaceOutofDate,[nil]); mORMot2 IBX driver is already in trunk as beta https://github.com/synopse/mORMot2/blob/master/src/db/mormot.db.sql.ib x.pas and this changes is very important for future users to not have to patch next versions of fbintf. It is not possible to workaround about this problem in mormot code. I'm very happy with quality and performance of your code. Now I work to implement IBatch interface, current solution use execute block for Batch operations. Thanks in advance ---------------------------------------------------------------------- tonyw · 2021-09-16 18:56 Perhaps you could explain why this check is a problem for you. ---------------------------------------------------------------------- ttomas · 2021-09-17 14:06 tonyw, I need to run the code like this Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   att: IAttachment;  trans, trans2: ITransaction;  stmt: IStatement;  ...  trans := att.StartTransaction(fInternalTPB, TACommit);   stmt := att.Prepare(trans, 'insert into ...' );  MyFillAllParams1;  stmt.Execute(trans);  //OK  trans.Commit;  trans.Start(TACommit);   MyFillAllParams2;  stmt.Execute(trans);  // raise Exception  trans.Commit;   // also some times I need  trans2 := att.StartTransaction(fInternalTPB, TACommit);   stmt.Execute(trans2);  // raise Exception  trans2.Commit;   In Firebird Statement can be executed with different transaction then prepare transaction. This protection in IBX is unique compared with other Firebird libraries (ZeosLib, IBObjects, UIB, FireDAC, UniDac, IBPP (C), etc). Just checked some old code running for years with UIB Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   // pseudo code for big import  StartTransaction;  Prepare;  for i:=1 to largeN do  begin     FillParams;     Execute;     if (i mod 100)=99 then     begin        Commit;        sleep(1000);  // give some time to other users        StartTransaction;     end;  end; For mORMot2 integration, client code like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   for i:=1 to 1000 do   begin     R.YearOfBirth := n;     Client.Add(R, true);  end; This code send 1000 http request to mormot server to insert R (TOrm Object). Only first call on server side call Prepare then Execute. After first call statement is cached and next calls (999) calls only Execute method of DB driver. There is no problem with logic with any database SQLite, Oracle, PosgreSQL, MySQL, MS SQL or Firebird. I trace and audit calls from ZeosLib/Firebird and I see Driver only once Prepare statement and for all other calls only execute Execute with different transaction then Prepare transaction (First call Prepare and Execute use same transaction). If explicit transaction is not started in mORMot IBX driver I Start before and Commit after every execution of statement (software auto commit). Preparing every execution will kill performance and stress memory with allocation and free of Data/Param buffers/metadata. This check (FSQLParams.FTransactionSeqNo < (FTransactionIntf as TFB30transaction).TransactionSeqNo) disable running Execute with different transaction from prepare used transaction! I understand your concern to remove this code. Please, can you consider to add some property to Disable this check in statements execute and opencursor methods? ---------------------------------------------------------------------- tonyw · 2021-09-17 16:01 The exception at line 12 is exactly what should happen when using Firebird. The statement was prepared using the transaction 'trans' at line 6 and then committed at line 9. When you commit thr transaction the prepare context is lost, and you will get an exception if you try and execute the statement without re-preparing the transaction. The exception is telling you explicitly that you have lost your transaction context. You have three ways to avoid this happening. 1. Prepare the statement again after line 10, or 2. Use Commit Retaining at line 9, or 3. Use a different transaction to execute the query from the one used to prepare the query. In this case, the transaction used to prepare the query is kept active as long as you need access to the statement, The transaction used to execute the query can be committed and restarted (e.g.) after every execute. Note that the API is intentionally low level and does not aautomatially prepare unprepared queries. A higher level database driver, such as IBX will "hide" the problem from you by automatically re-preparing the statement if it was in the unprepared state when you came to filling in the parameters. ---------------------------------------------------------------------- ttomas · 2021-09-17 17:30 Quote from: tonyw on September 17, 2021, 06:01:34 pmThe exception at line 12 is exactly what should happen when using Firebird. The statement was prepared using the transaction 'trans' at line 6 and then committed at line 9. When you commit thr transaction the prepare context is lost, and you will get an exception if you try and execute the statement without re-preparing the transaction. The exception is telling you explicitly that you have lost your transaction context. Don't agree, line 12, not a Firebird exception, this is fbintf raise exception. re-preparing is not needed to execute. In my code this check/raise is //commented and everything works fine. Other Fb libs don't raise exception. Quote from: tonyw on September 17, 2021, 06:01:34 pm1. Prepare the statement again after line 10, orThis will kill performance Quote from: tonyw on September 17, 2021, 06:01:34 pm2. Use Commit Retaining at line 9, orZeosLib use CommitRetaining and me and other users complained by running 24x7 server and FB garbage collection, old active transaction and etc. This is one goal and inspiration for me to write mormot IBX driver with hard Commit. Quote from: tonyw on September 17, 2021, 06:01:34 pm3. Use a different transaction to execute the query from the one used to prepare the query. In this case, the transaction used to prepare the query is kept active as long as you need access to the statement, The transaction used to execute the query can be committed and restarted (e.g.) after every execute.Not true, first call of statement start transaction, prepare, execute and commit transaction. Next call start transaction, execute and commit. In mormot db driver you can explicitly start transaction BeginTransaction and every executed statement will be executed in this transaction context. At the end Commit or Rollback. In other case, if you don't call BeginTransaction every statement is atomic and I start and commit at end of execution. In IBX mormot db driver I have two transaction objects. One main transaction owned by connection object used for explicit BeginTransaction, and second internal transaction owned by Statement object used for software emulated auto commit when no explicit transaction is started. Same Statement can be executed with any of this two transactions. First call of statement execute Prepare and Execute in any of this two transaction, next calls only Execute this already prepared statement. Default mormot server use 32 thread connection pool and per thread DB connection/attachment. Monitoring Fb for mvc-blog example, after some time "select * from MON$ATTACHMENTS" show 32 active connections. "select * from MON$TRANSACTIONS" shows 0 transactions (ZeosLib 32 active transaction). "select * from MON$STATEMENTS" shows <100 cached statements, column MON$STATE is 0 - idle. This idle statements exists on server as long as IStatement object exists and can be used to execute. MON$STATE can be: 0 - idle, 1 - active, 2 - stalled. As I note early, everything work nice without this check/raise. Just ask for solution for users to not have to patch next versions of fbintf. ---------------------------------------------------------------------- tonyw · 2021-09-19 15:47 The problem with your approach is that you run the risk of the statement BLR becoming out-of-date. If you are keeping a statement prepared over a long time - which you seem to suggest that you are - then this could be a serious problem. My recommended approach in this situation is to have a separate transaction for the prepare and the execute phases. The former you keep open for as long as you want to keep using the same statement BLR and it acts as a guard against a ddl change. The cost of do this is at most one extra transaction start/commit and I don't see why that  should be a big problem. On the other hand, and in another universe, I am very critical of the Gnome 3/gtk3 devs for ignoring user complaints and enforcing their preferred style on everyone. The result was inevitably a fork of Gnome 2 (MATE) and that gtk2 remains in widespread use (e.g. with Lazarus). I thus do not want to go down the road of ignoring requests that I don't like. Given that adding an option to suspend stale reference checks will only affect the users that use it, I am willing to add  to IStatement procedure SetStaleReferenceChecks(Enable:boolean); function GetStaleReferenceChecks: boolean; with the defeult being "enable=true". However, expect the documentation to include some pointed comments about those who choose to use it. ---------------------------------------------------------------------- ttomas · 2021-09-19 18:33 Thank you very much for adding Set/Get methods in IStatement interface. Quote from: tonyw on September 19, 2021, 05:47:47 pmThe problem with your approach is that you run the risk of the statement BLR becoming out-of-date. If you are keeping a statement prepared over a long time - which you seem to suggest that you are - then this could be a serious problem.Creating web services with Db is not a easy task, where Db layer is main а bottleneck for performance. Risk of keeping prepared statements for long period of time is a real concern for me too. One solution is using ConnectionTimeOutMinutes property of Db connection pull and destroy all connections and associated statements after some period of inactivity. For some systems for ex. data logger service where 24x7 serve hundreds of automation devices and never reach ConnectionTimeOutMinutes some periodic destroy of connection pull will be used. Quote from: tonyw on September 19, 2021, 05:47:47 pmMy recommended approach in this situation is to have a separate transaction for the prepare and the execute phases. The former you keep open for as long as you want to keep using the same statement BLR and it acts as a guard against a ddl change. The cost of do this is at most one extra transaction start/commit and I don't see why that  should be a big problem.DDL change on working production system is no option to resolve at runtime. Usually mormot server on startup in main thread/connection do all ddl changes, before start of connection pull. I have several systems developed with mormot/ZeosLib/Firebird and monitoring, trace and audit is very hard to do, to find some bugs. Having a system were nobody is connected and you have open >32 transactions, monitoring MON$TRANSACTIONS is no go for me. This is main inspiration for me to create Ibx mormot driver. Now I have 0 transactions when nobody is connected and if some transaction or active statement exists then I know some bad things happen. Thank you again for everything ---------------------------------------------------------------------- tonyw · 2021-09-20 22:14 I have now implemented the requested feature. You can checkout a development snapshot using svn co https://svn.mwasoftware.co.uk/public/ibx/trunk ibx or just go to https://svn.mwasoftware.co.uk/ibx to view the update and download a tarball. Disabling stale reference checks is now included in the test suite as part of Test 16.