Solved

"Lock file has grown too large" - error (Paradox database)

Posted on 2004-09-17
24
2,212 Views
Last Modified: 2007-12-19
This is a continuation of this q: http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21132623.html.

The application is working fine on my computer (development pc), but when deployed I get the error "Lock file has grown too large". I don't understand this.

The network is a basic server/client. All data (Paradox tables) is located on the server, the lock file as well.

Anybody?

D'Mzz!
RoverM
0
Comment
Question by:roverm
  • 10
  • 8
  • 3
  • +1
24 Comments
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12089176
Paradox sucks at client/server stuff. It's even worse than Access. I'd suggest to move the project from the BDE/Paradox to ADO/Access but this is probably too time-consuming.
0
 
LVL 12

Author Comment

by:roverm
ID: 12090763
@Workshop_Alex:
This application is a couple of years old, so moving to Access (or any other db) is too time-consuming and just not worth the effort.
Instead of flaming at Paradox (which is a good database...) can you please provide some usefull information?

D'Mzz!
RoverM
0
 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 500 total points
ID: 12091069
Are your PrivateDir and NetFileDir set correctly?

Extract from http://www.bdesupport.com/errors.htm
==================================
"Lock file has grown too large" ($2517)
Problem: There are several causes for this error.
Solution: The most common cause of this error is having the table you are accessing located in the same directory as the executable itself. Also set the NetFileDir and PrivateDir properties (of the Session object) to point to different directories. The PrivateDir property should be a directory on the local system's hard drive for maximum performance and stability. Read this Borland Technical Information Document for more information about this error, its causes, and solutions.

More specific information on the "lock file has grown too large" error (i.e. the link referred to "this Borland Technical Information" above) can be found at http://community.borland.com/article/0,1410,15256,00.html


Kind regards
Pierre
0
 
LVL 12

Author Comment

by:roverm
ID: 12091094
@PierreC:
I've tried all 4 suggestions at that site, but none of them worked.

FYI: In the BDE Administrator I've set the localshare to TRUE (since the data is shared via the network) and the NET DIR is set to a shared network folder where the lockfile is stored. This is not the same as the applicationfolder or the datafolder.

When I use these:
    Session.NetFileDir:=ExtractFilePath(ParamStr(0)) + 'NET';
    Session.PrivateDir := ExtractFilePath(ParamStr(0)) + 'PRIV';

I get an error. But this makes sense because these settings try to set the NetFiledir and PrivateDr to my localpath + NET/PRIV, which are not valid folders.

Any other suggestions?

Thanks so far!

D'Mzz!
RoverM
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12091141
There is a better solution to your previous needs.

You can use the datasource property of the TQuery to link the 2 datasets. I explain with this example:

Let's say you have a customer table and an orders table. The orders table is linked to the customer table with the CustNumber field. We can now link 2 queries in a master-detail fashion by using the datasource property.

...
var
  q1,q2: TQuery; //q1 = master and q2 = detail
  src: TDataSource;
begin
  q1:= TQuery.Create(nil);
  q2:= TQuery.Create(nil);
  src:= TDataSet.Create(nil);
  try
    src.DataSet:= q1;
    q1.SQL.Text:= 'SELECT CustNumber, Surname, FirstName, TelNum, etc '+
                  'FROM Customers';
    q2.SQL.Text:= 'SELECT OrderNum, OrderDate, etc '+
                  'FROM Orders '+
                  'WHERE CustNumber = :CustNumber';
    with q2.ParamByName('CustNumber') do
    begin
      ParamType:= ptInput;
      DataType:= ftString;
    end;
    q2.DataSource:= Src;

    q1.Open;
    q2.Open;
    While not q1.Eof do
    begin
      //Do what you want with the field values
      while not q2.Eof do
      begin
        //do what you want with the field values
        q2.Next;
      end;
      q1.Next;
    end;

  finally
    q1.Close; q1.Free;
    q2.Close; q2.Free;
    src.Free;
  end;
end;
...

Regards
Pierre
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12091174
>When I use these:
>    Session.NetFileDir:=ExtractFilePath(ParamStr(0)) + 'NET';
>    Session.PrivateDir := ExtractFilePath(ParamStr(0)) + 'PRIV';

The privateDir should be on the local machine to improve performance. Just a note: the above does not ensure that it is on the local machine, but merely that it is in the same dir as the exe. if you are running the exe from somewhere on the network e.g. \\foo\yourapp.exe, then privateDir would be \\foo\priv. You could easily check for "C:\" in the ParamStr(0) though.

The NetFileDir should not be a shared directory for all users of your application. (like you say you do)


What I normally do is (Just from head):
- Ensure that my app is running from the local machine by checking for "C:\" in ParamStr(0).

- I don't like using the default session. I always create my own and then set the dirs to something like
     //Let's say my file is in C:\Foo\Foo.exe
    //I also have a shared network directory \\fooServer\Data
    //I also have a shared network directory \\fooServer\Data\Net

     MySession.PrivateDir:= ExtractFilePath(ParamStr(0));
     MySession.NetFileDir:= GetAliasPath('SomeAlias')+Net;  //see below for GetAliasPath
//  A NOTE: It is important that the data path in the BDE administrator is set exactly the same for ALL machines
//  I always use the UNC format e.g. instead of C:\Foo\Data\  I use \\FooServer\Data

function GetAliasPath(AliasName: string): string;
var
  p: TStringList;
begin
  p:= TStringList.Create;
  MySession.GetAliasParams(s, p);
  result:= p.Values['PATH'];
  p.Free;
end;
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12091181
regarding my post on Date: 09/18/2004 03:36AM PDT I forgot to mention something:

Parameters that have the same name as fields in the other dataset are filled with the field values. It is therfor important that in my example the parameter name (CustNumber) of the detail TQuery is a field in the master TQuery.
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12091190
SORRY, Typing error in post of Date: 09/18/2004 03:51AM PDT:

>The NetFileDir should not be a shared directory for all users of your application. (like you say you do)

...IT SHOULD BE  A SHARED...

Instead of ...IT SHOULD not...
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12091196
BTW

The neFileDir is not normally where the lock files are stored. It stores the PARADOX.NET file.

The lock files are normally in your app dir and the data dir. (which should NOT be the same dir)
0
 
LVL 12

Author Comment

by:roverm
ID: 12091755
@PierreC:
So, if I understand correct, I NEED to set the PRIV dir to a different folder than where my exe is located.
Ok, I've done that and will try it in the network.

Thanks again!
0
 
LVL 12

Author Comment

by:roverm
ID: 12091762
btw: You are correct about the NET DIR, it only holds the PDOXUSRS.NET file...
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12092848
> Instead of flaming at Paradox (which is a good database...) can you please provide some usefull information?
I know Paradox is a good database. I even used the real deal in the past as part of the Borland Office product. It's a good database, and the Paradox product included an interesting, object-oriented programming language and good reporting options. Paradox is now owned by Corel but it still lacks a good multi-user support.

I assume you're smart enough to set the netfiledir/privatedir to a shared folder. It is also important that users get full read/write access to this shared file. Thus the file should NOT be readonly. PierreC is giving good advise here. You could put the netfiledir/privatedir to the same folder of your executable IF the application is located on a network share. Otherwise, it must be set up to look at some shared folder. Since your data files are located on a shared folder anyway, you could, technically, put these files in the same folder as the database.

I used to like the BDE since it provided a very good database support. However, these days there are much better alternatives that have less quirks than the BDE. In general, I therefore prefer to advise people to move to a less-quirky database if possible. In the long run, it will be a big relieve since the BDE support from Borland won't be much anymore in the future. ADO and DBExpress are the newer alternatives that Borland is providing.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Author Comment

by:roverm
ID: 12092871
@Workshop_Alex:
Thanks for your input.

As I wrote, the application is a couple of years old and it is used in a client/server environment:
Each application is run from the local harddrive, the data is located in a shared folder on the server, the lockfolder is another shared folder on the server and (now) the private folder is located locally next to the bin folder (subdir: priv).

I don't know the result yet of the addition of the private folder. It will be tested soon.
Thanks!

D'Mzz!
RoverM

Ps: FYI: I use SQL Server or Oracle now... :)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 12099274
just to say, if u use paradox, using ttables would be more performant,
and using its exclusive-property set to true will avoid any record-locking

just as suggestion

meikl ;-)
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12099696
Kretzmar,

RoverM has mentioned a couple of times that it is an old multi-user app, so setting exclusive property to true will result in only one user being able to access the table at a time. Also, TTable is less condusive to using in a client-server environment. best to break up the data into smaller chunks with TQuery to minimise network traffic.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 12099735
>Also, TTable is less condusive to using in a client-server environment

this may true in true sql-enviroments but in paradox a query causes a copy
of the queried table(s) into the privatedir, using a ttable this acts directly on
on the datafile.

the lock file is currently growing, because all transactions on the wueried table
are cached until the queries are closed, using a ttable only one lock is created
on the current record and released just after the post, even if the exclusive
property is not set to true

i recommend in case of paradox to use ttables for mass-updates rather than tqueries.

meikl ;-)
0
 
LVL 12

Author Comment

by:roverm
ID: 12099842
Hi meikl,
Thanks for you input. If all else fails I will try to use TTables. But I don't want to do this because it's a lot of work to change it all... :(

I am still waiting for the testresults so I am keeping my fingers crossed. :)

D'Mzz!
RoverM
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12100290
Meikl is right. I hope you're using TTable's with Paradox instead of TQuery components. Because as Meikl says, TQuery's eat up resources with Paradox. With other database systems, the Query components are better than Table components, but not with Paradox. It's a nasty BDE quirk...
But if changing everything to TTable components is the last option, consider rewriting the project with e.g. ADO and SQL Server or Oracle. (Or Access.) This too means replacing the TQuery components with other components but it allows you to keep the same queries. Basically, you'd be replacing the BDE dependancy into ADO dependancy.

About 3 years ago I made the same bold statement that the BDE sucks to my employer. I also explained him why, because it really didn't perform well in multi-user/multi-system environments. I also showed him that replacing the BDE-related components with ADO-related components isn't such a big problem anyway. All that needed to be done was convert the Paradox tables to some other database system.
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12100759
> this may true in true sql-enviroments but in paradox a query causes a copy
> of the queried table(s) into the privatedir, using a ttable this acts directly on
> on the datafile.

Why do you say that? I was under the impression that opening a query (or ttable) for that matter retrieves all the records in memory and that the difference is in what (and how) they do with those records subsequently. From my past research various sources (e.g. Bob Swart - http://www.drbob42.com/delphi/perform.htm), It has always been recommended to rather use TQueries instead of TTable where possible.

There are a number of reasons why using a TQuery is better than a TTable (Adapted from: http://bdn.borland.com/article/0,1410,28160,00.html):
1. When opening a table, many queries are sent to the database to get all the metadata for
    fields and indexes in the selected table in order to provide
    you with a selection of these (only Live TQuerys do this).

2. TTables request all fields even if you only need one or two fields.
    With TQueries you can specify what you want (except if RequestLive is true)

3. TTables request all records upon opening even if you only need a subset of them.
    With TQuery you limit this with a WHERE clause

4. Using Locate or FindKey or RecordCount forces all records to be fetched because such
    searching / counting has to be done on the client side. This can be eased by using a good
    filter (in the Filter property, not the OnFilter event) to limit the records that need to
    be fetched (Filters are turned into SQL where clauses by the BDE).

5. If used in a grid, TTable must frequently execute multiple queries to fill the grid whenever
    you change record positions, resulting in slow data access.

6. Tables only see physical tables, whereas you can write TQuerys to select any relationships
    between any number of tables and get only exactly the data you need.

In RoverM's case, however, based on what I saw in the previous question it doesn't matter whether a TTable or TQuery is used, unless I misunderstood. My understanding is that he is merely trying to loop through various records and export them to a text file. This could possibly be done with TTables set up with master-detail relationships in his example with but probably better would be using TQuery as illustrated in my post of Date: 09/18/2004 03:36AM PDT. This way only 2 TQuery components would be needed and the detail query's SQL can be set to whatever before opening based on the values of the first. The datasource link will take care of the msater-detail relationships as long as the param names are valid fields in the master query.

What I don't understand is why the records are locked in the first place if he is merely looping through a SELECT Sql statement's returned dataset. With TTable record locks would be done in the background. But records only get locked when specifically requested when using queries or whilst the BDE is executing update/insert/delete sql's.

If my understanding is correct, then I think the problem is as described in 3a of http://community.borland.com/article/0,1410,15256,00.html which states:

    Description:
    Lock file has grown too large, Decimal:9495 Hex:2517
    ----------------------------------------------------
    This problem is specific to Paradox tables and can be caused
    in any BDE (16 or 32 Bit) application that meet some or all
    of the following criteria:

    ...
    3a) Delphi: Having a TTable open on a paradox table and
        then performing multiple TQuery operations.


RoverM:
- Clarification on your needs, please. Maybe post your core code where the problem exists
- are any of the tables you are using in the sql's open when opening/executing the TQuery?
  If so, try closing them before running your code
- Are you making any updates/edits to tables whilst running the problem code and if yes, how (e.g. using TTable)?



0
 
LVL 12

Author Comment

by:roverm
ID: 12100830
Hi PierreC,
Wow, much information... :)

I think you are on to something here. ALL tables are opened because I am using a datamodule.
When the application starts the first thing that is done is opening all tables...

However, I can't change this. Like I said, it's an old application and if I need to rewrite all code... nah...

If the PRIV folder thing doesn't work (I am still waiting for the testresults...) then I will try to close all tables and then fire the queries. For that function I can change it without any problem.

I am not making any updates or edits while looping.

Thanks again!

D'Mzz!
RoverM
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 12100857
>> this may true in true sql-enviroments but in paradox a query causes a copy
>> of the queried table(s) into the privatedir, using a ttable this acts directly on
>> on the datafile.

>Why do you say that?

hope you read it carefully
>but in paradox a query causes a copy
>of the queried table(s) into the privatedir

usual it is correct to use query-object rather than table-objects, but
not in case of paradox and dbase
just to repeat
not in case of paradox and dbase

these desktop-databases do not have anything about a real sql-database,
the sql u use is overheaded on the bde, the bde itself translates the sql

just to say that the bde is the native driver for paradox and dbase

to the points you give, all are true for real sql-databases, and as most
uses real sql-databases its said as general (also drBob), but using
paradox or dbase its a difference

the differences about your points:

1. just inbuild into the bde (reads just the headerInfo of the table)
2. thats true, but all records with the two needed fields are copied into the privatedir -> traffic and timeconsuming
3. not true, just what fits into the bde cache is loaded
4. locate yes, finkey no, because only the indexfile (much smaller)is fetched until find
5. not true, if index is given the bde pages through the indexfile, by no index through the recordnumber (invisible part of the table)
6. thats why bde supports sql, whereas qbe is quite better for this

meikl ;-)
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12100868
Rover,

Can you post your core problem code (or is it still as per your previous question)?
0
 
LVL 12

Author Comment

by:roverm
ID: 12110389
Hi all,
Well, the problem is solved.
I've added the Session.PrivateDir = PRIV to my datamodule. If the folder does not exist it's created.
Quite straightforward stuff, but it works great.

Thanks PierreC for pointing me into this direction.

D'Mzz!
RoverM
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12110393
Glad I could help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now