So Close I Can Almost Taste It
lazarus / 16-Databases

----------------------------------------------------------------------
DougNettleton · 2007-06-13 19:52

Hi everyone:

Okay, with the insights of Marc G who's never actually done it with
Lazarus, and further "googling", I found ...

SET TRANSACTION WAIT ISOLATION LEVEL READ COMMITTED ;

If I connect to my database in two Command Line Interface windows,
using Firebird ISQL Tool and in each window, execute the command
above.  The Update / Commit in one window becomes visible with the
next select in the other window.

Now, how to execute this command from within Lazarus using the SQLdb
components?

Ive tried ...

  IBConnection1.StartTransaction;
  IBConnection1.ExecuteDirect(
    'SET TRANSACTION WAIT ISOLATION LEVEL READ COMMITTED');
  IBConnection1.EndTransaction;

result: -invalid transaction handle (expecting explicit transaction
start)

and ...

  IBConnection1.Connected := True;
  SQLTransaction1.Active := True;
  SQLUpdateQuery.SQL.Clear;
  SQLUpdateQuery.SQL.Add(
    'SET TRANSACTION WAIT ISOLATION LEVEL READ COMMITTED');
  SQLUpdateQuery.ExecSQL;

same result.

Any ideas? Anyone?

TIA,

Doug

P.S. When I execute the SET TRANSACTION ... command above in the
Firebird ISQL Tool, it prompts for whether to commit or not.  So I
also tried the above code with the SQLTransaction1, Action property
set to caCommit, but that didn't help either.

----------------------------------------------------------------------
Marc_G · 2007-06-14 11:22

Doug, are you sure there is not a property or some params in your
SQLTransaction to set isolation_level ?.

If you have any link to some doc with SQLDB components and properties,
I can look at them.

----------------------------------------------------------------------
DougNettleton · 2007-06-14 14:23

Marc G:

Thanks, for your continued interest in helping me.  Both IBConnection
and SQLTransaction components have a Params property with type
TStringList.  Inside the source code of IBConnection there's a
function ...

function TIBConnection.StartDBTransaction(trans : TSQLHandle;AParams :
String) : boolean;

This function parses AParams looking for ...

    s := ExtractSubStr(AParams,i,stdWordDelims);
    while s <> '' do
      begin
      if s='isc_tpb_write' then TPB := TPB + chr(isc_tpb_write)
      else if s='isc_tpb_read' then TPB := TPB + chr(isc_tpb_read)
      else if s='isc_tpb_consistency' then TPB := TPB +
chr(isc_tpb_consistency)
      else if s='isc_tpb_concurrency' then TPB := TPB +
chr(isc_tpb_concurrency)
      else if s='isc_tpb_read_committed' then TPB := TPB +
chr(isc_tpb_read_committed)
      else if s='isc_tpb_rec_version' then TPB := TPB +
chr(isc_tpb_rec_version)
      else if s='isc_tpb_no_rec_version' then TPB := TPB +
chr(isc_tpb_no_rec_version)
      else if s='isc_tpb_wait' then TPB := TPB + chr(isc_tpb_wait)
      else if s='isc_tpb_nowait' then TPB := TPB + chr(isc_tpb_nowait)
      else if s='isc_tpb_shared' then TPB := TPB + chr(isc_tpb_shared)
      else if s='isc_tpb_protected' then TPB := TPB +
chr(isc_tpb_protected)
      else if s='isc_tpb_exclusive' then TPB := TPB +
chr(isc_tpb_exclusive)
      else if s='isc_tpb_lock_read' then TPB := TPB +
chr(isc_tpb_lock_read)
      else if s='isc_tpb_lock_write' then TPB := TPB +
chr(isc_tpb_lock_write)
      else if s='isc_tpb_verb_time' then TPB := TPB +
chr(isc_tpb_verb_time)
      else if s='isc_tpb_commit_time' then TPB := TPB +
chr(isc_tpb_commit_time)
      else if s='isc_tpb_ignore_limbo' then TPB := TPB +
chr(isc_tpb_ignore_limbo)
      else if s='isc_tpb_autocommit' then TPB := TPB +
chr(isc_tpb_autocommit)
      else if s='isc_tpb_restart_requests' then TPB := TPB +
chr(isc_tpb_restart_requests)
      else if s='isc_tpb_no_auto_undo' then TPB := TPB +
chr(isc_tpb_no_auto_undo);
      s := ExtractSubStr(AParams,i,stdWordDelims);

      end;

So presumably either the Transaction Params or the Connection Params
should be set to something like ...

isc_tpb_read_committed isc_tpb_wait

Anyways, I don't have firebird installed here so if no one has any
further suggestions or actually knows which one to set or if this
speculation is even correct, I'll try each tonight.

TIA,

Doug

----------------------------------------------------------------------
DougNettleton · 2007-06-15 21:21

Hi Everyone:

And the test results are ...

stick

isc_tpb_read_committed
isc_tpb_wait

into the Params property of the SQLTransaction component and multi-
user updates are visible to each other.  Sadly, SQLQuery.Refresh still
doesn't make them visible, so as was suggested by Marc G in another
thread, the other user updates only become visible after ...

SQLQuery.Close;
SQLQuery.Open;

I hope the preceeding and the other thread about my experiences with
Lazarus / MySQL 5.0 and Firebird 2.0 help any others out there
struggling with these tools.

Doug

----------------------------------------------------------------------
chrgra · 2007-06-16 15:27

DougNettleton,

Maybe it is an idea to exactly ask what you want in this post.

Is it something that deals with updating in one application and see
the updates in another, while they are both connected to a firebird
database?

Regards Chris

----------------------------------------------------------------------
DougNettleton · 2007-06-17 03:25

Chris:

Thanks for your "reply", but I think you're a little late to the
party, the thread started with how to achieve...

SET TRANSACTION WAIT ISOLATION LEVEL READ COMMITTED ;

within Lazarus, including the three things I tried and what happened.

Marc G suggested a parameter on SQLTransaction.  After reading some of
the source code I thought, if it would work at all it would be a
value(s) in the Params property of SQLTransaction or IBConnection
components.  After getting it to work with Delphi 6 and the Interbase
components, I figured it out in Lazarus.

My final post was to let others know what I had discovered, so they
wouldn't waste the time I did. 

I guess it's not as good as the Delphi solution since there is no
"RefreshSQL" property like in the IBDataSet and IBUpdateSQL
components, so as, I also said, it appears the only way to make
another user's update visible, is to close and re-open your query. 

If you have another way to do this, I would be delighted to hear it.
 In case what "to do this" is not clear, consider the following,
simple case:

You have a database with ...

Table     Primary Key
====   ========
vendor   vendno
invhdr    invno
invdet    invno, lineno
chkhdr   chkno
chkdet   chkno, lineno

representing Vendors, Invoice Headers, Invoice Details, Check Headers
and Check Details

and you have five SQLQuery compnents with the following SQL selects...

1) select * from vender order by vendname

2) select * from invhdr where vendno = :vendno order by invno

3) select * from invdet where invno = :invno order by lineno

4) select * from chkhdr where vendno = :vendno order by chkno

5) select * from chkdet where chkno = :chkno order by lineno

select 2 and 4 above have a datsource set to the Vendor table and 3
and 5 have a datasource set to Invhdr and chkhdr respectively.

Now I'm browsing through the Vendor table and within the currently
selected Vendor, I'm looking at the 23rd invoice and 10th check
payment for that Vendor and within these the 5th Invoice detail line
and the 3 Check payment detail.

Another user may have updated the Vendor record I'm looking at, so
before I attempt to update it, I want to refresh the view I have of
the data.  Wouldn't it be nice to do this ...

SQLQuery1.Refresh; or press the Refresh button on a DBNavigator
component.

Instead as far as I can tell, I have to close and re-open the query
and all the related "Parameterized Queries" - in this case only four,
then I have to go back to the records I was at in each of the queries,
which means I have to keep track of these before I close the queries.

Now imagine the much worse scenario where your application is a
"multi-company" accounting system and you're now browsing a List of
companies and within the currently selected company, a list of vendors
etc - now vendor number, check numbers and invoice numbers are only
unique within a company so you now have compno added to each of the
"primary keys" above.

Now this thread has been viewed 80 times minus the five times it was
me and no one has jumped in and said "Silly fool Doug, what you want
to do is easy! Here's how you do it ..."

Even more 168 views of my other thread on my experiences with these
products, so I must conclude these things can't be done yet or nobody
reading these threads has tried anything non-trivial on the multi-user
database front.

Anyways, as I said I'd be delighted to hear about a simple solution to
this problem.

TIA,

Doug