Recent

Author Topic: Is transaction necessary for prepare?  (Read 335 times)

egsuh

  • Hero Member
  • *****
  • Posts: 504
Is transaction necessary for prepare?
« on: July 18, 2020, 07:26:26 am »
Hi, I used to use like :

Code: Pascal  [Select][+][-]
  1. query.sql.text := 'select * from mytable where afield=:afield';
  2. prepare;  
  3. query.params[0].asstring := 'this value';
  4.  
  5. transaction.active := True;              // Start transaction here
  6. query.Open;
  7. ....

This code used to work fine, but since some time (not sure. Probably Lazarus 2.0.8), this does not work. I should activate transaction before preparing.
Where there any change of the program installation, or some kind of option (like autostart transaction, etc.)?

devEric69

  • Sr. Member
  • ****
  • Posts: 384
Re: Is transaction necessary for prepare?
« Reply #1 on: July 19, 2020, 12:21:20 pm »
Hello,

first of all, preparing a dynamic query - a.k.a. query with parameters - is useful especially when the said query is executed several times in a loop!!!

Then, what I know about preparing a dynamic request is that:
1°) PREPARE is a SQL statement! It's a word of the SQL grammar. So, it can be called natively, with "direct SQL" .
2°) but, the call to PREPARE can also be done by remote server-API.

(By the way, with a monitoring tool, i've already seen "Preparing..." - although i've never even called it :P ??! - and explicit "PREPARE SQL FOR...")

Besides, PREPAREing what?
It can be called to tell the server to load in its global heap\memory, a procedure already stored on the server (stored procedure), or a "SQL pattern" that will hold several parameters coming soon from the network. I would say - personally, i don't have an omniscient answer for all the SQL servers, sorry - that if the PREPARE call is made with "direct SQL" for some coming soon SQL parameterized query statements from the network, it makes sense that it's in a transactional batch of SQL queries...


For information, one parameter that optimizes, too, the speed of usage of SQL queries is UniDirectional (if present). If the query is used to consolidate data (like a statistics report, for example, based on a snapshot query) by reading its fetched records from BoF to EoF (without having to go backwards, as it is the case with a TDBGrid), then the UniDirectional property speeds up the processing on the client side, because in theory, there is no need to allocate all the pointers machinery to create a bi-directional client-cursor that won't do anything.
« Last Edit: July 19, 2020, 01:04:19 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

 

TinyPortal © 2005-2018