dBASE SQL Servers - Couldn't perform edit?

Author

Couldn't perform edit?

Jun S. Pomer

2005-05-11, 3:23 am

Hi,

I using Firebird datatabase, I encounter this problem?

"Database Engine Error: Couldn't perform the edit because another user
changed the record."

I m the only one is using the system/database. How come there is another
user change the record.

Please help.

Thanks.

Jun

*Lysander*

2005-05-11, 3:23 am

In article <OzAJyTeVFHA.444@news-server>, jspomer@softworks.com.ph=20
says...

> I m the only one is using the system/database. How come there is another
> user change the record.

I never saw something like this with FireBird but I think it is a=20
question of database-design how often you can see this.

I can only make a shot in the dark here: maybe you did not rollback or=20
commit one transaction? Usually, dBL is doing this automatically for=20
you, mostly using "soft commits".

This makes it impossible to use 100% of the speed of FireBird, but it=20
makes programming much easier, and you already achieve a very good speed=20
and can leave the further optimization for later.

If what you see is not indeed the result of opening two queries without=20
hard-committing them, then it must be any so called 'limbo'-transaction.

Such can be cleaned with the house-keeping tools of FireBird.
Write here again after answering the following questions, and - maybe -=20
we can solve this.

1.) Does this error also occur after you restarted the computer and=20
dBase?
2.) Did you play a bit with starting and ending transactions yourself?
3.) Did you have any system-crash or network-problems?

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~
European dBase-conference dBKonEurope on June 3rd and 4th
English spoken conference around different database-topics
::
Info here: www.dbkon.org

Jun S. Pomer

2005-05-11, 7:23 am

Hi André,

>1.) Does this error also occur after you restarted the computer and dBase?
Yes

>2.) Did you play a bit with starting and ending transactions yourself?
Is this what you mean?
Try
form.rowset.parent.database.beginTrans( )
form.rowset.parent.database.commit( )
catch ( DbException e )
form.rowset.parent.database.rollback( )
catch ( Exception e )
form.rowset.parent.database.rollback( )
endtry

But even I put a message in catch, the error message will not appear.

3.) Did you have any system-crash or network-problems?
No.

>I never saw something like this with FireBird but I think it is a
>question of database-design how often you can see this.
I dont know what you mean of database-design.

If you edit the record IB Expert, there is no problem, but in dBase even in
the navigator, the error will appear.
I am using BeginAppend(), BeginEdit(), Save(). Is this ok? How can I do one
time commit of the transaction like in IB Expert. When you edit the record
in the data, the changes will appear but you have an option to commit or
rollback.

?Such can be cleaned with the house-keeping tools of FireBird
What tools?

Thanks for your time.

Thanks.

Jun

,
"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1cebc44eae6614049897
6a@news.dbase.com...
In article <OzAJyTeVFHA.444@news-server>, jspomer@softworks.com.ph
says...

> I m the only one is using the system/database. How come there is another
> user change the record.

I never saw something like this with FireBird but I think it is a
question of database-design how often you can see this.

I can only make a shot in the dark here: maybe you did not rollback or
commit one transaction? Usually, dBL is doing this automatically for
you, mostly using "soft commits".

This makes it impossible to use 100% of the speed of FireBird, but it
makes programming much easier, and you already achieve a very good speed
and can leave the further optimization for later.

If what you see is not indeed the result of opening two queries without
hard-committing them, then it must be any so called 'limbo'-transaction.

Such can be cleaned with the house-keeping tools of FireBird.
Write here again after answering the following questions, and - maybe -
we can solve this.

1.) Does this error also occur after you restarted the computer and
dBase?
2.) Did you play a bit with starting and ending transactions yourself?
3.) Did you have any system-crash or network-problems?

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~
European dBase-conference dBKonEurope on June 3rd and 4th
English spoken conference around different database-topics
::
Info here: www.dbkon.org

*Lysander*

2005-05-11, 7:23 am

In article <gZuHezgVFHA.1188@news-server>, jspomer@softworks.com.ph=20
says...

> Is this what you mean?
> Try
> form.rowset.parent.database.beginTrans( )
> form.rowset.parent.database.commit( )
yes, but I did not mean now to catch this one error. I was meaning like=20
'in the beginning of your experiments'. Such experiments can naturally=20
leave a lot of transactions uncommitted or, as they are called in=20
FireBird 'in Limbo-State'. If such limbo-transaction has a lock on one=20
or some rows, you cannot edit them.=20
But after reading your answers I do not believe that the problem is a=20
limbo-transaction. Else you could not do the edit AND commit in=20
IBExpert.

> I dont know what you mean of database-design.
Excuse me for the misunderstanding. If you have a lot of users editing=20
the same tables all the time, then the chances for lock-conflicts are=20
higher. In such surroundings a good database design with very fine=20
normalization makes a lot of work, but it reduces lock-conflicts=20
dramatically. But this is not the source of your problem, so it does not=20
belong here :)

> I am using BeginAppend(), BeginEdit(), Save(). Is this ok?
I am doing this all the time, works perfect.

> How can I do one
> time commit of the transaction like in IB Expert. When you edit the recor=
d
> in the data, the changes will appear but you have an option to commit or
> rollback.
I am not sure, but because FireBird does not support the isolation-level=20
"read uncommitted changes" it must be a trick by IBExpert. Most likely=20
they are buffering the changes in a cache, or they really put everything=20
in several transactions. I did not experiment with this too much in=20
dBase, but I think that this also is not too difficult to do.

> If you edit the record IB Expert, there is no problem, but in dBase=20
even in
> the navigator, the error will appear.

sounds VERY strange.

Question:
is it a usual table? not any stored proc involved, not a trigger, not a=20
relation (foreign key)? Can you give the DDL-statement of the table here=20
(you can copy it with IBExpert very easy).

> ?Such can be cleaned with the house-keeping tools of FireBird
> What tools?

GFix. Comes with the FireBird installation. It is a command-line-tool.
But if you have IBExpert, you usually do not need GFix, because IBExpert=20
has visual wrappers for most GFix-functions.

Even if limbos are most likely not involved in this, just try to make a=20
backup of the database. (IBExpert, menu 'services', backup database,=20
choose the options for 'ignore limbo' and for 'garbage collection').

ciao,
Andr=E9

*Lysander*

2005-05-11, 7:23 am

In article <gZuHezgVFHA.1188@news-server>, jspomer@softworks.com.ph=20
says...

> If you edit the record IB Expert, there is no problem, but in dBase even =
in
> the navigator, the error will appear.

Ack!
The main difference between IBExpert's access and your dBL-Access could=20
be in the settings of the driver. They do not go ODBC and BDE.
Which isolation-level did you set in your database-object?
Which settings do you have in the DSN and in the BDE-alias?

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~
European dBase-conference dBKonEurope on June 3rd and 4th
English spoken conference around different database-topics
::
Info here: www.dbkon.org

Jun S. Pomer

2005-05-11, 9:23 am

Hi André,

>Which isolation-level did you set in your database-object?
1 - Read Committed

>Which settings do you have in the DSN and in the BDE-alias?
System DSN, is that what you mean?

When I edit the specific row In the IB Expert that cause problem in dBase,
the message appear "There is active transaction, You must commit or rollback
it before closing the form." After commiting the transaction, the problem in
dBase did not appear any more. Hopefully will find the solution to that
problem.

Thanks.

Jun

"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1cec016bf0de8a339897
6c@news.dbase.com...
In article <gZuHezgVFHA.1188@news-server>, jspomer@softworks.com.ph
says...

> If you edit the record IB Expert, there is no problem, but in dBase even
in
> the navigator, the error will appear.

Ack!
The main difference between IBExpert's access and your dBL-Access could
be in the settings of the driver. They do not go ODBC and BDE.
Which isolation-level did you set in your database-object?
Which settings do you have in the DSN and in the BDE-alias?

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~
European dBase-conference dBKonEurope on June 3rd and 4th
English spoken conference around different database-topics
::
Info here: www.dbkon.org

Jun S. Pomer

2005-05-11, 9:23 am

Hi André,

Please find below the DDL.

I am reading Firebird Book by Helen Chapter 40. "Understanding the Lock
Manager", I don't know if there is a solution out there, to many to absort,
right now.

Regards.

Jun

/ ********************
********************
********************
***************
***/
/**** Generated by IBExpert 5/11/2005 9:52:02 PM
****/
/ ********************
********************
********************
***************
***/

SET SQL DIALECT 3;

SET NAMES NONE;

/ ********************
********************
********************
***************
***/
/**** Tables
****/
/ ********************
********************
********************
***************
***/

CREATE GENERATOR ACCTNO_GEN;

CREATE TABLE MEMBER (
ACCTNO SMALLINT NOT NULL,
ATYPE CHAR(1) DEFAULT 'R' NOT NULL,
APPTYPE CHAR(1) NOT NULL,
CARDNO VARCHAR(8),
BCODE VARCHAR(8) NOT NULL,
FNAME VARCHAR(15) NOT NULL,
MNAME VARCHAR(1) DEFAULT ' ',
LNAME VARCHAR(15) NOT NULL,
MCAT CHAR(1) DEFAULT '1',
BDATE DATE,
BDMM CHAR(2),
BDDD CHAR(2),
BDYY CHAR(4),
MAGE VARCHAR(3),
GENDER CHAR(1) DEFAULT 'M' NOT NULL,
HACODE VARCHAR(4),
HPHONE VARCHAR(7),
MOBILENO VARCHAR(11),
HADDR1 VARCHAR(50),
HADDR2 VARCHAR(50),
HADDR3 VARCHAR(50),
PCODE VARCHAR(10),
EMAIL VARCHAR(45),
CITIZEN CHAR(1),
OCITIZEN VARCHAR(15),
NOCHLD VARCHAR(3),
CSTAT CHAR(1),
WORKSTAT VARCHAR(1),
COMPNAME VARCHAR(40),
OPACODE VARCHAR(4),
OPHONE VARCHAR(15),
OFACODE VARCHAR(10),
OFAX VARCHAR(15),
OPOSITION VARCHAR(26),
LS VARCHAR(2),
HHC VARCHAR(1),
ORG1 VARCHAR(25),
ORG2 VARCHAR(25),
ORG3 VARCHAR(25),
MHI VARCHAR(1),
HPMM NUMERIC(2,0),
WDSM1 VARCHAR(1),
WDSM2 VARCHAR(1),
WDSM3 VARCHAR(1),
WDSM4 VARCHAR(1),
WDSG1 VARCHAR(1),
WDSG2 VARCHAR(1),
WDSG3 VARCHAR(1),
WDSG4 VARCHAR(1),
WDSG5 VARCHAR(1),
MFVS1 VARCHAR(20),
MFVS2 VARCHAR(20),
MFVS3 VARCHAR(20),
DCAH CHAR(1),
DCAHO VARCHAR(2),
DCAHF CHAR(1),
IDATE DATE,
ITIME TIME,
APPDATE DATE,
MSTAT VARCHAR(3) DEFAULT 'EC',
TE CHAR(1) DEFAULT 'N',
TES CHAR(1),
TED DATE,
TET TIME,
TEREF VARCHAR(15) DEFAULT 'N',
TD CHAR(1) DEFAULT 'N',
TDD DATE,
TDT TIME,
TR CHAR(1) DEFAULT 'N',
TRD DATE,
TRT TIME,
USERID VARCHAR(10),
QC1D DATE,
QC1T TIME,
QC1UID VARCHAR(10),
EMS CHAR(1) DEFAULT ' ',
EMD DATE,
EMT TIME,
EMUID VARCHAR(10),
EMP CHAR(1) DEFAULT 'N',
MP CHAR(1) DEFAULT 'N',
MPUID VARCHAR(10),
MPD DATE,
MPT TIME,
QC2D DATE,
QC2T TIME,
QC2UID VARCHAR(10),
DR2D DATE,
DR2T TIME,
DR2UID VARCHAR(10),
DR2REF VARCHAR(15)
);

/ ********************
********************
********************
***************
***/
/**** Primary Keys
****/
/ ********************
********************
********************
***************
***/

ALTER TABLE MEMBER ADD CONSTRAINT PK_MEMBER PRIMARY KEY (ACCTNO)
USING INDEX MEMBER_IDXACCTNO;

/ ********************
********************
********************
***************
***/
/**** Indices
****/
/ ********************
********************
********************
***************
***/

CREATE INDEX MEMBER_IDXBCODE ON MEMBER (BCODE);
CREATE INDEX MEMBER_IDXCARDNO ON MEMBER (CARDNO);
CREATE INDEX MEMBER_IDXFNAME ON MEMBER (FNAME);
CREATE INDEX MEMBER_IDXLNAME ON MEMBER (LNAME);
CREATE INDEX MEMBER_IDXMNAME ON MEMBER (LNAME, FNAME);
CREATE INDEX MEMBER_IDXPCODE ON MEMBER (PCODE);
CREATE INDEX MEMBER_IDXTEREF ON MEMBER (TEREF);
CREATE INDEX MEMBER_IDXUSERID ON MEMBER (USERID);

/ ********************
********************
********************
***************
***/
/**** Triggers
****/
/ ********************
********************
********************
***************
***/

SET TERM ^ ;

/ ********************
********************
********************
***************
***/
/**** Triggers for tables
****/
/ ********************
********************
********************
***************
***/

/* Trigger: SET_ACCTNO */
CREATE TRIGGER SET_ACCTNO FOR MEMBER
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* Trigger text */
if (new.acctno is null) then
new.acctno = gen_id(acctno_gen, 1);
end
^

SET TERM ; ^

/* Fields descriptions */

/ ********************
********************
********************
***************
***/
/**** Privileges
****/
/ ********************
********************
********************
***************
***/

/* Privileges of users */
GRANT ALL ON MEMBER TO MVP_TSI WITH GRANT OPTION;
"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1cebff589a433e599897
6b@news.dbase.com...
In article <gZuHezgVFHA.1188@news-server>, jspomer@softworks.com.ph
says...

> Is this what you mean?
> Try
> form.rowset.parent.database.beginTrans( )
> form.rowset.parent.database.commit( )
yes, but I did not mean now to catch this one error. I was meaning like
'in the beginning of your experiments'. Such experiments can naturally
leave a lot of transactions uncommitted or, as they are called in
FireBird 'in Limbo-State'. If such limbo-transaction has a lock on one
or some rows, you cannot edit them.
But after reading your answers I do not believe that the problem is a
limbo-transaction. Else you could not do the edit AND commit in
IBExpert.

> I dont know what you mean of database-design.
Excuse me for the misunderstanding. If you have a lot of users editing
the same tables all the time, then the chances for lock-conflicts are
higher. In such surroundings a good database design with very fine
normalization makes a lot of work, but it reduces lock-conflicts
dramatically. But this is not the source of your problem, so it does not
belong here :)

> I am using BeginAppend(), BeginEdit(), Save(). Is this ok?
I am doing this all the time, works perfect.

> How can I do one
> time commit of the transaction like in IB Expert. When you edit the record
> in the data, the changes will appear but you have an option to commit or
> rollback.
I am not sure, but because FireBird does not support the isolation-level
"read uncommitted changes" it must be a trick by IBExpert. Most likely
they are buffering the changes in a cache, or they really put everything
in several transactions. I did not experiment with this too much in
dBase, but I think that this also is not too difficult to do.

> If you edit the record IB Expert, there is no problem, but in dBase
even in
> the navigator, the error will appear.

sounds VERY strange.

Question:
is it a usual table? not any stored proc involved, not a trigger, not a
relation (foreign key)? Can you give the DDL-statement of the table here
(you can copy it with IBExpert very easy).

> ?Such can be cleaned with the house-keeping tools of FireBird
> What tools?

GFix. Comes with the FireBird installation. It is a command-line-tool.
But if you have IBExpert, you usually do not need GFix, because IBExpert
has visual wrappers for most GFix-functions.

Even if limbos are most likely not involved in this, just try to make a
backup of the database. (IBExpert, menu 'services', backup database,
choose the options for 'ignore limbo' and for 'garbage collection').

ciao,
André

*Lysander*

2005-05-11, 11:23 am

In article <EAWGICjVFHA.1768@news-server>, jspomer@softworks.com.ph=20
says...
> When I edit the specific row In the IB Expert that cause problem in=20
dBase,
> the message appear "There is active transaction, You must commit or rollb=
ack
> it before closing the form." After commiting the transaction, the problem=
in
> dBase did not appear any more. Hopefully will find the solution to that
> problem.

Fine!!!
So before that you did not try to "hard"-commit the transaction on this=20
row. Then it WAS a limbo transaction, or a still open and long-running=20
one.
I am glad that it was something like that.

Do not worry too much!
Most likely this transaction-error occurred by coincidence while you was=20
testing something. That is absolutely normal in the beginning. Mostly it=20
does not have any effect on the rows, but just sometimes this can lock a=20
part of the table.

For any case you can look into your BDE for the setting "shared=20
autocommit" for "SQLpassthroughmode". If this is "shared noautocommit"=20
you MUST for every transaction first get a Transaction ID and actively=20
commit or rollback everything. For the beginning, "shared autocommit" is=20
a good setting.

The cleanup-option "backup with cleaning limbo and garbage collection"=20
as I described will help in this cases. This should in any case be done=20
regularly, depending on the size of the databases and how frequently=20
they are used by how many users.

~~~

I was looking at your DDL, even though it is not important now after the=20
error went away. You could only still check if you have another table=20
with a foreign key related to the primary key of this table.

But you have a lot of fields in one table.

I can recommend to you to make good use of the statements "select=20
field1, field2, ...." from dBase-language.

With *.dbf-tables it is not a big advantage to chose only several fields=20
from a table, so a "select * from table1" is very much okay.
But with FireBird your operations will be much faster when you only=20
select those fields which you need at the moment.

Here is one 'gotcha' with dBase(BDE) and FireBird.
"Select * from table1" will automatically use the primary index.
"Select field1, field2 (...) from table1" will make it necessary that=20
you specify an index also.
Don't ask me why, but this one gave me a good puzzle in the beginning.

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

Jun S. Pomer

2005-05-11, 11:23 am

Hi André,

Good to hear that it is normal and there is a solution.

>If this is "shared noautocommit" you MUST for every transaction first get a
Transaction ID and actively
commit or rollback everything.

This is interesting, can you show it how to do that. I think that it is a
MUST or that is the recommended for programming in dBase and Firebird.

Thanks.

Jun

"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1cec3ed0971e2d6f9897
6e@news.dbase.com...
In article <EAWGICjVFHA.1768@news-server>, jspomer@softworks.com.ph
says...
> When I edit the specific row In the IB Expert that cause problem in
dBase,
> the message appear "There is active transaction, You must commit or
rollback
> it before closing the form." After commiting the transaction, the problem
in
> dBase did not appear any more. Hopefully will find the solution to that
> problem.

Fine!!!
So before that you did not try to "hard"-commit the transaction on this
row. Then it WAS a limbo transaction, or a still open and long-running
one.
I am glad that it was something like that.

Do not worry too much!
Most likely this transaction-error occurred by coincidence while you was
testing something. That is absolutely normal in the beginning. Mostly it
does not have any effect on the rows, but just sometimes this can lock a
part of the table.

For any case you can look into your BDE for the setting "shared
autocommit" for "SQLpassthroughmode". If this is "shared noautocommit"
you MUST for every transaction first get a Transaction ID and actively
commit or rollback everything. For the beginning, "shared autocommit" is
a good setting.

The cleanup-option "backup with cleaning limbo and garbage collection"
as I described will help in this cases. This should in any case be done
regularly, depending on the size of the databases and how frequently
they are used by how many users.

~~~

I was looking at your DDL, even though it is not important now after the
error went away. You could only still check if you have another table
with a foreign key related to the primary key of this table.

But you have a lot of fields in one table.

I can recommend to you to make good use of the statements "select
field1, field2, ...." from dBase-language.

With *.dbf-tables it is not a big advantage to chose only several fields
from a table, so a "select * from table1" is very much okay.
But with FireBird your operations will be much faster when you only
select those fields which you need at the moment.

Here is one 'gotcha' with dBase(BDE) and FireBird.
"Select * from table1" will automatically use the primary index.
"Select field1, field2 (...) from table1" will make it necessary that
you specify an index also.
Don't ask me why, but this one gave me a good puzzle in the beginning.

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

Jun S. Pomer

2005-05-11, 11:23 am

André,

>Here is one 'gotcha' with dBase(BDE) and FireBird.
>"Select * from table1" will automatically use the primary index.
>"Select field1, field2 (...) from table1" will make it necessary that
>you specify an index also.
>Don't ask me why, but this one gave me a good puzzle in the beginning.

I use select field1, field2() from table, but the index is not available.
When you use select * from table, all index is visible. Do you encountered
the same?

Thanks.

Jun

"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1cec3ed0971e2d6f9897
6e@news.dbase.com...
In article <EAWGICjVFHA.1768@news-server>, jspomer@softworks.com.ph
says...
> When I edit the specific row In the IB Expert that cause problem in
dBase,
> the message appear "There is active transaction, You must commit or
rollback
> it before closing the form." After commiting the transaction, the problem
in
> dBase did not appear any more. Hopefully will find the solution to that
> problem.

Fine!!!
So before that you did not try to "hard"-commit the transaction on this
row. Then it WAS a limbo transaction, or a still open and long-running
one.
I am glad that it was something like that.

Do not worry too much!
Most likely this transaction-error occurred by coincidence while you was
testing something. That is absolutely normal in the beginning. Mostly it
does not have any effect on the rows, but just sometimes this can lock a
part of the table.

For any case you can look into your BDE for the setting "shared
autocommit" for "SQLpassthroughmode". If this is "shared noautocommit"
you MUST for every transaction first get a Transaction ID and actively
commit or rollback everything. For the beginning, "shared autocommit" is
a good setting.

The cleanup-option "backup with cleaning limbo and garbage collection"
as I described will help in this cases. This should in any case be done
regularly, depending on the size of the databases and how frequently
they are used by how many users.

~~~

I was looking at your DDL, even though it is not important now after the
error went away. You could only still check if you have another table
with a foreign key related to the primary key of this table.

But you have a lot of fields in one table.

I can recommend to you to make good use of the statements "select
field1, field2, ...." from dBase-language.

With *.dbf-tables it is not a big advantage to chose only several fields
from a table, so a "select * from table1" is very much okay.
But with FireBird your operations will be much faster when you only
select those fields which you need at the moment.

Here is one 'gotcha' with dBase(BDE) and FireBird.
"Select * from table1" will automatically use the primary index.
"Select field1, field2 (...) from table1" will make it necessary that
you specify an index also.
Don't ask me why, but this one gave me a good puzzle in the beginning.

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

*Lysander*

2005-05-11, 1:23 pm

In article <5k6$yBkVFHA.1528@news-server>, jspomer@softworks.com.ph=20
says...

> This is interesting, can you show it how to do that. I think that it is a
> MUST or that is the recommended for programming in dBase and Firebird.

Unfortunately, I cannot show you too much of this.
There are 3 settings possible in BDE for dBase-applications.
"Not shared", "Shared NoAutocommit", "Shared Autocommit".

Shared NoAutocommit is not 100% working as designed. Everybody must make=20
his own experience, but according to _my_ tests it was only bringing=20
more problems, but not more benefits.

"Not shared" is working as I would expect it according to description in=20
BDE. But this will definitely make problems as soon as you are using=20
more than one database, or joined tables.

"Shared Autocommit" is working very good for me.
You can decide yourself if you want to specifically open transactions or=20
not.

example 1 (with shared autocommit)=20
=3D=3D=3D=3D=3D=3D=3
D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3
D=3D=3D
do
form.query1.rowset.delete()
until form.query1.rowset.count() =3D 0

this will delete all records and use 2 transactions for every record
changes can not be rolled back, that means another user who has "read=20
committed" as isolation level will see how the table is becoming smaller=20
line by line. This operation uses less RAM but causes more network=20
traffic.

example 2 (with shared autocommit)
=3D=3D=3D=3D=3D=3D=3
D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3
D=3D=3D
form.database1.begintrans()
do
form.query1.rowset.delete()
until form.query1.rowset.count() =3D 0
form.database1.commit()
<OR>
form.database1.rollback()

this will also delete all records and use 1 transaction in total
changes can be rolled back, that means that another user who has "read=20
committed" as isolation level will see the complete table, and only when=20
you issue commit() he will suddenly see nothing. This operation uses=20
more RAM but causes far less network traffic.

You - as the programmer - must decide which method is better. For long=20
batch operations, like deleting several thousands of rows, or inserting=20
as many, can be better to do with autocommit after every operation (that=20
means without using begintrans() and commit()).
It causes more network traffic, but lowers the risk of lock-conflicts.=20

There are so many different scenarios that there is no recommendation=20
possible. You can for every task decide which option to use.
If you decide to decide nothing, autocommit is a fine thing, and I am=20
very glad that it works so very good from inside dBase.

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

*Lysander*

2005-05-11, 1:23 pm

In article <6AwW$QkVFHA.1528@news-server>, jspomer@softworks.com.ph=20
says...

> I use select field1, field2() from table, but the index is not available.
> When you use select * from table, all index is visible. Do you encountere=
d
> the same?

Yes, and I do not know exactly why.
But it was a problem only in the beginning. I am really not needing so=20
many indexes anymore like I did before with *.dbf-tables.

Just make yourself a list: for what do you need the "rowset.indexname =3D=
=20
blabla" in dBaseLanguage?
You will find out, that in most cases this is only needed for *.dbf, and=20
not any more for FireBird-tables.

There are, by the way, indexes available when you use "select field1,=20
field2..." instead of "select *". Only you must use them in another way=20
than before.

Findkey() and findkeynearest() for example are not needed any more when=20
using FireBird. You can change them to "select ... where" with=20
parameters, or respectively to "select ... where ... LIKE ..%". Works at=20
least as reliable as findkey() in dBase-tables. Concerning (null)-values=20
it even works better.

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

Jun S. Pomer

2005-05-11, 8:23 pm

Hi André,

>You will find out, that in most cases this is only needed for *.dbf, and
>not any more for FireBird-tables

Your right, I do not need any index in some modules, but in parent child
relationship, like grid1(parent) and grid2(child), I am not able to make it
work. When I design in the datamodule it require the index for linking in
the parent. Is this possible in Firebird, creating a view with parent and
child and drag it to the form? Do you have solution for this?

>Findkey() and findkeynearest() for example are not needed any more when
>using FireBird. You can change them to "select ... where" with
>parameters, or respectively to "select ... where ... LIKE ..%". Works at
>least as reliable as findkey() in dBase-tables. Concerning (null)-values
>it even works better.

I use applyLocate() for searching the record.

Thanks.

Jun

"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1cec69afedb486a09897
70@news.dbase.com...
In article <6AwW$QkVFHA.1528@news-server>, jspomer@softworks.com.ph
says...

> I use select field1, field2() from table, but the index is not available.
> When you use select * from table, all index is visible. Do you encountered
> the same?

Yes, and I do not know exactly why.
But it was a problem only in the beginning. I am really not needing so
many indexes anymore like I did before with *.dbf-tables.

Just make yourself a list: for what do you need the "rowset.indexname =
blabla" in dBaseLanguage?
You will find out, that in most cases this is only needed for *.dbf, and
not any more for FireBird-tables.

There are, by the way, indexes available when you use "select field1,
field2..." instead of "select *". Only you must use them in another way
than before.

Findkey() and findkeynearest() for example are not needed any more when
using FireBird. You can change them to "select ... where" with
parameters, or respectively to "select ... where ... LIKE ..%". Works at
least as reliable as findkey() in dBase-tables. Concerning (null)-values
it even works better.

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

Jun S. Pomer

2005-05-11, 8:23 pm

Hi André,

Thanks for sharing you expiriences.

Jun

"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1cec6852fc39a27b9897
6f@news.dbase.com...
In article <5k6$yBkVFHA.1528@news-server>, jspomer@softworks.com.ph
says...

> This is interesting, can you show it how to do that. I think that it is a
> MUST or that is the recommended for programming in dBase and Firebird.

Unfortunately, I cannot show you too much of this.
There are 3 settings possible in BDE for dBase-applications.
"Not shared", "Shared NoAutocommit", "Shared Autocommit".

Shared NoAutocommit is not 100% working as designed. Everybody must make
his own experience, but according to _my_ tests it was only bringing
more problems, but not more benefits.

"Not shared" is working as I would expect it according to description in
BDE. But this will definitely make problems as soon as you are using
more than one database, or joined tables.

"Shared Autocommit" is working very good for me.
You can decide yourself if you want to specifically open transactions or
not.

example 1 (with shared autocommit)
====================
==============
do
form.query1.rowset.delete()
until form.query1.rowset.count() = 0

this will delete all records and use 2 transactions for every record
changes can not be rolled back, that means another user who has "read
committed" as isolation level will see how the table is becoming smaller
line by line. This operation uses less RAM but causes more network
traffic.

example 2 (with shared autocommit)
====================
==============
form.database1.begintrans()
do
form.query1.rowset.delete()
until form.query1.rowset.count() = 0
form.database1.commit()
<OR>
form.database1.rollback()

this will also delete all records and use 1 transaction in total
changes can be rolled back, that means that another user who has "read
committed" as isolation level will see the complete table, and only when
you issue commit() he will suddenly see nothing. This operation uses
more RAM but causes far less network traffic.

You - as the programmer - must decide which method is better. For long
batch operations, like deleting several thousands of rows, or inserting
as many, can be better to do with autocommit after every operation (that
means without using begintrans() and commit()).
It causes more network traffic, but lowers the risk of lock-conflicts.

There are so many different scenarios that there is no recommendation
possible. You can for every task decide which option to use.
If you decide to decide nothing, autocommit is a fine thing, and I am
very glad that it works so very good from inside dBase.

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

*Lysander*

2005-05-12, 3:23 am

In article <$Wus2LpVFHA.1188@news-server>, jspomer@softworks.com.ph=20
says...
> Thanks for sharing you expiriences.

Glad that I can help somebody after getting help for years in NG :)

Else something:
Before 'batch'-operations (especially deleting a lot of rows) set the=20
indexes of that table to 'inactive'. This can be done by=20
form.database1.executeSQL("<command>").
After finishing, set the index active again.

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

*Lysander*

2005-05-12, 3:23 am

In article <OoYmVLpVFHA.1188@news-server>, jspomer@softworks.com.ph=20
says...
> Is this possible in Firebird, creating a view with parent and
> child and drag it to the form? Do you have solution for this?

I am not using datamodules at all, and never was. It is easier to write=20
the code, but much more difficult to handle lock-conflicts in network-
applications.
I can therefore not seriously tell, whether datamodules are good to use=20
with FireBird or not. If you need to create indexes, which you would=20
normally not need, ONLY to make datamodules work, then they are=20
definitely not good :)

Parent-Child relations are very easy to manage by either of the=20
following:
- create a view
- create a selectable stored proc
- use an SQL-statement with a direct join of tables
(select
t1.f1,
t1.f2,
t2.f3,
t2.f4
from
t1
join
t2
on t1.f1 =3D t2.f1)

All of those options are a bit different from using usual dBase-tables,=20
but when using FireBird you in any case must get used to have different=20
queries for viewing data and for making changes and posting data.
In other languages, you even should have different transactions for=20
reading and writing.
But with BDE and ODBC it is only possible to have ONE concurrent=20
transaction running per session.

> I use applyLocate() for searching the record.
a parametrized query using the WHERE-clause is much faster, at least in=20
all of my tests with big tables.

Example for findkey() (requires unique index on the server to be=20
successful)
=3D=3D=3D=3D=3D=3D=3
D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3
D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D

form.query1.sql =3D "select
=09t1.f1,
=09=09 =09t1.f2
=09=09 from
=09=09=09t1
=09=09 where
=09=09=09t1.f3 =3D :myparam"
form.query1.params["myparam"] =3D "---"
form.query1.active =3D .F.

function OnChangeEntryFieldXY

local sValue

sValue =3D "---"

form.query1.params[1] =3D ltrim(rtrim(this.value))
form.query1.active =3D .t.
do case
case not form.query1.rowset.endofset =3D .t.
sValue =3D form.query1.rowset.fields[2].value
otherwise
sValue =3D "no match found"
endcase
=20
return (sValue)

Example for findkeynearest or applylocate()
=3D=3D=3D=3D=3D=3D=3
D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3
D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D
form.query1.sql =3D "select
=09t1.f1,
=09=09 =09t1.f2
=09=09 from
=09=09=09t1
=09=09 where
=09=09=09t1.f3 LIKE :myparam"
form.query1.params["myparam"] =3D "---"
form.query1.active =3D .F.

function OnChangeEntryFieldXY

local sValue

sValue =3D "---"

form.query1.params[1] =3D ltrim(rtrim(this.value))+"%"
form.query1.active =3D .t.
do case
case not form.query1.rowset.endofset =3D .t.
form.query1.rowset.first()
do=20
// do something with all matching records
until not form.query1.rowset.next()
otherwise
sValue =3D msgbox("no match found")
endcase

sValue =3D null

release sValue
=20
return

remarks:
=3D=3D=3D=3D=3D=3D=3
D=3D
the '+"%"' is needed, it will turn the LIKE-statement into a STARTING=20
WITH-statement automatically and that uses all available indexes.

if you want a real LIKE-statement you must set the parameter to=20
"%"+ltrim(rtrim(this.value))+"%". But this will not use the available=20
indexes and it is as slow as applylocate().

~~~

testing for form.query1.rowset.count() > 0 is very popular in dBase.
Do NOT do it with FireBird if the table has more than a few dozen=20
records. Testing with form.query1.rowset.endofset is okay. The best=20
method though is to test with a special FireBird-function. Look in=20
Helen's book for "exists".

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

Jun S. Pomer

2005-05-15, 11:23 am

Hi André,

Sorry for late reply. So far I am able to deploy my application in live
environment. With three concurrent users and almost 1500 records, as of now,
I dont ecounter any problems. I consider FB for other future project
development. Right now I continue to study FB and your suggestion for more
efficient application and I know along the way I will encounter other
problem.

Thanks.

Jun

"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1ced22bef056b0fa9897
73@news.dbase.com...
In article <OoYmVLpVFHA.1188@news-server>, jspomer@softworks.com.ph
says...
> Is this possible in Firebird, creating a view with parent and
> child and drag it to the form? Do you have solution for this?

I am not using datamodules at all, and never was. It is easier to write
the code, but much more difficult to handle lock-conflicts in network-
applications.
I can therefore not seriously tell, whether datamodules are good to use
with FireBird or not. If you need to create indexes, which you would
normally not need, ONLY to make datamodules work, then they are
definitely not good :)

Parent-Child relations are very easy to manage by either of the
following:
- create a view
- create a selectable stored proc
- use an SQL-statement with a direct join of tables
(select
t1.f1,
t1.f2,
t2.f3,
t2.f4
from
t1
join
t2
on t1.f1 = t2.f1)

All of those options are a bit different from using usual dBase-tables,
but when using FireBird you in any case must get used to have different
queries for viewing data and for making changes and posting data.
In other languages, you even should have different transactions for
reading and writing.
But with BDE and ODBC it is only possible to have ONE concurrent
transaction running per session.

> I use applyLocate() for searching the record.
a parametrized query using the WHERE-clause is much faster, at least in
all of my tests with big tables.

Example for findkey() (requires unique index on the server to be
successful)
====================
====================
=====
form.query1.sql = "select
t1.f1,
t1.f2
from
t1
where
t1.f3 = :myparam"
form.query1.params["myparam"] = "---"
form.query1.active = .F.

function OnChangeEntryFieldXY

local sValue

sValue = "---"

form.query1.params[1] = ltrim(rtrim(this.value))
form.query1.active = .t.
do case
case not form.query1.rowset.endofset = .t.
sValue = form.query1.rowset.fields[2].value
otherwise
sValue = "no match found"
endcase

return (sValue)

Example for findkeynearest or applylocate()
====================
====================
===
form.query1.sql = "select
t1.f1,
t1.f2
from
t1
where
t1.f3 LIKE :myparam"
form.query1.params["myparam"] = "---"
form.query1.active = .F.

function OnChangeEntryFieldXY

local sValue

sValue = "---"

form.query1.params[1] = ltrim(rtrim(this.value))+"%"
form.query1.active = .t.
do case
case not form.query1.rowset.endofset = .t.
form.query1.rowset.first()
do
// do something with all matching records
until not form.query1.rowset.next()
otherwise
sValue = msgbox("no match found")
endcase

sValue = null

release sValue

return

remarks:
========
the '+"%"' is needed, it will turn the LIKE-statement into a STARTING
WITH-statement automatically and that uses all available indexes.

if you want a real LIKE-statement you must set the parameter to
"%"+ltrim(rtrim(this.value))+"%". But this will not use the available
indexes and it is as slow as applylocate().

~~~

testing for form.query1.rowset.count() > 0 is very popular in dBase.
Do NOT do it with FireBird if the table has more than a few dozen
records. Testing with form.query1.rowset.endofset is okay. The best
method though is to test with a special FireBird-function. Look in
Helen's book for "exists".

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

*Lysander*

2005-05-17, 7:23 am

In article <1AeireWWFHA.320@news-server>, jspomer@softworks.com.ph=20
says...

> Sorry for late reply. So far I am able to deploy my application in=20
live
> environment. With three concurrent users and almost 1500 records, as of n=
ow,
> I dont ecounter any problems. I consider FB for other future project
> development. Right now I continue to study FB and your suggestion for mor=
e
> efficient application and I know along the way I will encounter other
> problem.

Fine.

After rereading my post I think I have to make something clear, also for=20
others who might read this thread:

I was writing:
>But with BDE and ODBC it is only possible to have ONE concurrent
>transaction running per session.

This is true, word by word.
Because if you are using 2 or more session objects (and the appropriate=20
number of database-objects) it _IS_ possible to have 2 or more=20
transactions running from the same client/same application. This is a=20
bit more difficult to design, but in some situations helps you to use=20
the FULL power of SQL-server from within dBase.

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
European dBase-conference dBKonEurope :::: C A N C E L L E D ::::
English spoken conference around different database-topics

Sponsored Links