Board index » delphi » Help!MS SQL Server 6.5 and data-entry form

Help!MS SQL Server 6.5 and data-entry form

Thank you for reading this.

I am programer from China.I am working with delphi3 c/s and MS SQL Server
6.5.I create a data-entry form with TTable,TDataSource,TDBNavigator and a
lot of TDBEdit components.I find a serious page lock and blocking
problem.Someone tell me that I should use TQuery instead of TTable,but I do
not know what is the right way to do this work.
Could you show me a example source?

Sorry for my English.

Thanks.

               Y.H. Chang

 

Re:Help!MS SQL Server 6.5 and data-entry form


You absolutely MUST use queries with SQL Server!  You can only use tables
for the most trivial things.  Here's why.  When you open a [Delphi] table it
places a  table and page lock on the underlying [SQL Server] table.  So,
when you try to update the underlying table, with an update query say, it
won't be able to (because it's already locked by the Delphi table).  Your
system will hangup until the request times out in SQL Server (default 5
minutes!)  By that time you'll think the system has crashed and will be
rebooting.
A general rule is: only ever use tables if the there is only one
data-enabled control using it and the table is relatively small.
It's quite complex but as a "quick and dirty" solution you should find that
using queries exactly as you would tables helps a lot.  For example: if you
have a table called MyStock which is looking at the SQL table STOCK and it
has an index of ProductCode.  You could create a Query like this
SELECT * FROM Stock ORDER BY ProductCode
Use it as you would a table and you'll be better off
(Please understand that this is a VERY trivial treatment of a very complex
topic.  You have some reading to do, I fear)
Good luck!
-Pete
Quote
cxf wrote in message <01bdd0fa$75775c40$0a01a8c0@hp01>...
>Thank you for reading this.

>I am programer from China.I am working with delphi3 c/s and MS SQL Server
>6.5.I create a data-entry form with TTable,TDataSource,TDBNavigator and a
>lot of TDBEdit components.I find a serious page lock and blocking
>problem.Someone tell me that I should use TQuery instead of TTable,but I do
>not know what is the right way to do this work.
>Could you show me a example source?

>Sorry for my English.

>Thanks.

>               Y.H. Chang

Re:Help!MS SQL Server 6.5 and data-entry form


Re:Help!MS SQL Server 6.5 and data-entry form


About this topic,I've use SQL Server sample table [pubs] with table component to
edit it ,it can work normally,
and no hangup at all,but when I created a database and a table in SQL Server,use
table
to edit it ,when delete ,refresh,system hangup,when append record ,no hangup,
why pubs no hangup?
       n.c.q
     Shanghai.China

Peter McCall

Quote
> You absolutely MUST use queries with SQL Server!  You can only use tables
> for the most trivial things.  Here's why.  When you open a [Delphi] table it
> places a  table and page lock on the underlying [SQL Server] table.  So,
> when you try to update the underlying table, with an update query say, it
> won't be able to (because it's already locked by the Delphi table).  Your
> system will hangup until the request times out in SQL Server (default 5
> minutes!)  By that time you'll think the system has crashed and will be
> rebooting.
> A general rule is: only ever use tables if the there is only one
> data-enabled control using it and the table is relatively small.
> It's quite complex but as a "quick and dirty" solution you should find that
> using queries exactly as you would tables helps a lot.  For example: if you
> have a table called MyStock which is looking at the SQL table STOCK and it
> has an index of ProductCode.  You could create a Query like this
> SELECT * FROM Stock ORDER BY ProductCode
> Use it as you would a table and you'll be better off
> (Please understand that this is a VERY trivial treatment of a very complex
> topic.  You have some reading to do, I fear)
> Good luck!
> -Pete

> cxf wrote in message <01bdd0fa$75775c40$0a01a8c0@hp01>...
> >Thank you for reading this.

> >I am programer from China.I am working with delphi3 c/s and MS SQL Server
> >6.5.I create a data-entry form with TTable,TDataSource,TDBNavigator and a
> >lot of TDBEdit components.I find a serious page lock and blocking
> >problem.Someone tell me that I should use TQuery instead of TTable,but I do
> >not know what is the right way to do this work.
> >Could you show me a example source?

> >Sorry for my English.

> >Thanks.

> >               Y.H. Chang

Re:Help!MS SQL Server 6.5 and data-entry form


There is a lot of conflicting information about this topic.  I too have
used tables  for multiuser apps with sql 6.5 and have not yet
experienced any page locking problems.  Having said that, I have
experimented with both tables and queries and for more complicated apps,
queries are a better solution simply because it is easier to control the
result set.
Quote
ncqtreec wrote:

> About this topic,I've use SQL Server sample table [pubs] with table component to
> edit it ,it can work normally,
> and no hangup at all,but when I created a database and a table in SQL Server,use
> table
> to edit it ,when delete ,refresh,system hangup,when append record ,no hangup,
> why pubs no hangup?
>        n.c.q
>      Shanghai.China

> Peter McCall D?o

> > You absolutely MUST use queries with SQL Server!  You can only use tables
> > for the most trivial things.  Here's why.  When you open a [Delphi] table it
> > places a  table and page lock on the underlying [SQL Server] table.  So,
> > when you try to update the underlying table, with an update query say, it
> > won't be able to (because it's already locked by the Delphi table).  Your
> > system will hangup until the request times out in SQL Server (default 5
> > minutes!)  By that time you'll think the system has crashed and will be
> > rebooting.
> > A general rule is: only ever use tables if the there is only one
> > data-enabled control using it and the table is relatively small.
> > It's quite complex but as a "quick and dirty" solution you should find that
> > using queries exactly as you would tables helps a lot.  For example: if you
> > have a table called MyStock which is looking at the SQL table STOCK and it
> > has an index of ProductCode.  You could create a Query like this
> > SELECT * FROM Stock ORDER BY ProductCode
> > Use it as you would a table and you'll be better off
> > (Please understand that this is a VERY trivial treatment of a very complex
> > topic.  You have some reading to do, I fear)
> > Good luck!
> > -Pete

> > cxf wrote in message <01bdd0fa$75775c40$0a01a8c0@hp01>...
> > >Thank you for reading this.

> > >I am programer from China.I am working with delphi3 c/s and MS SQL Server
> > >6.5.I create a data-entry form with TTable,TDataSource,TDBNavigator and a
> > >lot of TDBEdit components.I find a serious page lock and blocking
> > >problem.Someone tell me that I should use TQuery instead of TTable,but I do
> > >not know what is the right way to do this work.
> > >Could you show me a example source?

> > >Sorry for my English.

> > >Thanks.

> > >               Y.H. Chang

Re:Help!MS SQL Server 6.5 and data-entry form


I've experienced these locking problems a lot.  Perhaps it has something to
do with using Identity columns?  Most of my tables use Identity columns as
the
primary key and I'm using a non-clustered index for the primary key
(because I thought it would prevent page locking when records were
appended - however I have since learnt that with SQL 6.5 inserts use a
row-level lock, the only case of row-level locking in SQL, everything else
is
page-level locking).  Would this cause the locking?  Peter, is this what you
are actually doing?  Frank, I guess you are not using Identity columns and
are using clustered indexes?

Cheers,
Kevin.

Quote
Frank Cooley wrote in message <35F5116C.453CD...@dayton.dncx.com>...
>There is a lot of conflicting information about this topic.  I too have
>used tables  for multiuser apps with sql 6.5 and have not yet
>experienced any page locking problems.  Having said that, I have
>experimented with both tables and queries and for more complicated apps,
>queries are a better solution simply because it is easier to control the
>result set.

>ncqtreec wrote:

>> About this topic,I've use SQL Server sample table [pubs] with table
component to
>> edit it ,it can work normally,
>> and no hangup at all,but when I created a database and a table in SQL
Server,use
>> table
>> to edit it ,when delete ,refresh,system hangup,when append record ,no
hangup,
>> why pubs no hangup?
>>        n.c.q
>>      Shanghai.China

>> Peter McCall D?o

Re:Help!MS SQL Server 6.5 and data-entry form


On Thu, 10 Sep 1998 08:38:12, "Kevin" <k.be...@transmedit.co.za>
wrote:

Quote
> I've experienced these locking problems a lot.  Perhaps it has something to
> do with using Identity columns?  Most of my tables use Identity columns as
> the
> primary key and I'm using a non-clustered index for the primary key
> (because I thought it would prevent page locking when records were
> appended - however I have since learnt that with SQL 6.5 inserts use a
> row-level lock, the only case of row-level locking in SQL, everything else
> is
> page-level locking).

My understanding is that it only uses insert row-level locking if you
turn it on using DBCC (IIRC). You can turn it on for all tables, but
if you add any tables, then you have to turn it on for those tables.

-----------------------------------------
Simon Thompson
Christchurch
New Zealand

Other Threads