On Tue, 2010-11-30 at 07:29 +0000, Mark Morgan Lloyd wrote: > Joost van der Sluis wrote: > > >> PostgreSQL gets "locked", I mean, the tables being used by sqldb can't > >> be modified (new columns, new constraints, etc) while the sqldb > >> connection is active, even if it's a read-only SELECT. If someone is > >> testing a heavy report the tables affected can't be changed by the DBA > >> -- it's a major headache. > > > > Thing is, that TSQLQuery by default fetches only the first 10 records, > > and then keeps the cursor open, so when you need more records, those are > > fetched too. Setting PacketRecords to -1 will make it fetch the complete > > table at once. > > > > But then still, you have the data fetched in memory, so if you want to > > change the data, and apply changes, you have to keep the transaction > > open. (Well, it's up to you. But think through what you're doing) > > > > And I assume that Postgres (don't remember exactly) can set the behavior > > of transactions. Apparently they are blocking by default. > > Various parameters to begin transaction, set transaction and lock. It's > a few years since I've fiddled with these and I only did it by hand, > i.e. fed explicit commands through BDE to the server. > > If I could ask: where can I find a description of how TSQLQuery etc. > should be used for non-trivial applications? For example, if I want to > set up an explicit transaction, insert a few hundred rows of data, and > then make an explicit decision on whether I commit or rollback?
transaction.create sqlquery.transaction := just created transaction. sqlquery.sql := 'insert/update table ....' for i := 0 to few hundred do sqlquery.execsql transaction.commit/rollback > I can see TSQLQuery.InsertSQL etc., but how should they be used? If I > want to force a lock on a table how should I do it? In most cases you don't need them. The TSQLQuery is a TBufDataset. This dataset fetches data from a source. (In DBExpress this source is a dataprovider. In sqldb the source can be defined by a descendant, in this case the TSQLQuery. But other sources are possible. It would also be possible to create a descendant which uses a dataprovider-like component) The dataset is kept/cached in memory. The changes are kept in an update-buffer. When you call ApplyUpdates, all changes in the update-buffer are pushed to the underlying dataset. (Again, this is handled in a descendant, Delphi uses the dataprovider) What TSqlQuery does when it has to push changes to the database-server, is creating update/delete/insert sql-statements for each change. Well, in fact it parses the provided SQL to see if it can create this statements (using parameters) automatically. There is a way however to provide these update/insert/delete statements manually, that's where the .InsertSQL etc properties are for. In fpc 2.4.2 and further the TBufDataset has one build-in mechanism of data retrieval, which is streaming to a binary format. Or, when you add the xmlstreaming unit, to the same xml format as TClientDataset uses. Joost. -- _______________________________________________ Lazarus mailing list [email protected] http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
