If I access an SQL table (one record, one longint field) from Paradox via an
OBDC connection with a tcursor
and if I can lock the record I grab the value, increment it by one and then
post and unlock the record.

While I have the record locked (tc.lockrecord() returns true) is there any
sort of SQL or other mechanism that prevents any other non-Paradox app from
accessing the table and grabbing and incrementing at the same time?

These are order numbers. Twice in 4 months (125,000 orders) we have seen
duplicate order numbers retrieved by the Paradox app and by another
non-Paradox app getting order numbers from the same source.

--
Roger Engdahl
651 438 5931
612 819 7088
roger.pdxdev (AT) att (DOT) net

Roger Engdahl wrote:

that "lock" is only respected within the scope of other BDE apps..


--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
Corel CTech Paradox - http://www.diamondsg.com - Support/Downloads/Links
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
Upgrade/Downgrade versions? http://www.diamondsg.com/upgrade.htm
-------------------------------------------------------------------------

Roger,

firstly, take a look at the FAQ on this stuff - it's called
"TIP:PdoxWin:Incrementing Primary Keys:2001.05.26" on the
Paradox-faqs newsgroup and on the Paradox Community site, and
"Incrementing Values (Keys)" in the FAQs on my site
(http://mirwin.homestead.com). Either way, you should get a
download of a set of files to illustrate how to achieve things
with Paradox tables.

"But I'm using SQL" you protest. Well, a bit of explanation
never hurt ! Anyhow, you haven't specified which server you're
using.

Let's assume that you're using Microsoft SQL Server, which has
Transact-SQL for a language. When you are using MS Sql Server
you should, really, be using an autoincrement field within the
SQL Server table.

Alternatively, it's fairly simple to write a stored procedure on
MS SQL Server to increment a value in a table and return the
result to you.

For example, let's say that you have several tables, each of
which has an Integer (32 bit) key. You'll want to provide the SP
with the table name and receive back the new value ...

create proc incrementValue(@tableName varchar(40),
@NewVal integer OUTPUT)
as
select @NewVal = Value
from CoordInf
where TableName = @tableName
@NewVal = @ NewVal + 1
update CoordInf
set value = @NewVal
where TableName = @tableName


and execute the SP as needed. So long as you always use the SP
to add new records you shouldn't have any problems.

Finally, you can add a trigger to your SQL Server table, that
fires after insertion of a new record, and which does the same
thing as the code above, in that it goes and gets a value and
uses that. In this way an "auto" field in SQL Server is like a
postAction trigger.

I don't have an instance of SQL Server here in front of me to
try things out on, so I'll check it tonight and post again then

hth

Mike

In article <3ef9d205 (AT) jedstarmail (DOT) psfloan.com>,
roger.pdxdev (AT) worldnet (DOT) att.net says...

Roger Engdahl wrote:

You fundamentally cannot rely on "record locks" in an SQL database like you
can with a native Paradox table. But you can come pretty darned close with
something like this:

repeat
SELECT next_number FROM next_number_table .. into "n"
UPDATE next_number_table SET next_number = {n+1}
WHERE next_number = {n}
until the update works

Mind you, what I've written above is pseudocode .. not real code .. just to
describe the technique.

If your SQL engine provides a way to coin a new number, use it.


----------------------------------
Fast automatic table repair at a click of a mouse!
http://www.sundialservices.com/products/chimneysweep

Sundial Services wrote:

What do you mean by that? You may not always be able to rely on OPal
lockRecord() to actually lock the record, but you can explicitly lock
records on most client/server databases I've dealt with. For sure you can
with SQL Server 7 as Mike and I showed.

If you modified the above code to explicitly begin a transaction with the
proper isolation level, and specify the locking hint UPDLOCK, the above
would work.

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources

As you probably saw, while the code was conceptually correct, it
doesn't work ! I tested one out and posted it at

http://mirwin.homestead.com/files/tips/SQLSnippets.html

in section 4.

hth

Mike

In article <MPG.1963b292f34cc6c49896c8
@pnews.thedbcommunity.com>, mirwin (AT) paradoxcommunity (DOT) com says...

Mike Irwin [CTech] wrote:

Your code jumps through some hoops to get the temp table, populate, then
have Paradox go back and get the value from another SELECT.

Why not have the stored procedure return the value directly to the
tCursor?

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources