PDA

View Full Version : UPDATING SYSTEM FILES VIA SQL



IAN VAGG
13-Jul-2005, 02:58 AM
I need to update Pervasive files created and used by DF3.1d & vdf 10.1, from another application using SQL. How do I ensure that counters kept in a
System file are correctly updated. As far as I can see (I am far from an expert at SQL) while it is possible to increment the counter, you can't
control record locking and can't be sure whether the new value of the counter is yours or was created almost immediately afterwards by another user.
Any help would be greatly appreciated.

Ian Vagg

A Paul Anthony
13-Jul-2005, 06:34 AM
You can control the transactions, which is effectively table locking as
other users would be put on hold until you've finished.

I'm afraid I can never remember if the value stored in system tables is
the "last used" or "next available" (I have a feeling its "last used"
but by no means sure) but the statement as a whole would look something
like this. Increment the counter in the system table and use its value
in your insert statement.


BEGIN TRANSACTION

UPDATE Sysfile SET Counter = (Counter + 1)

INSERT INTO DataFile ("ID", "Field2", "Field3", "Field4")
SELECT "Counter", 'Data2', 'Data3', 'Data4' FROM Sysfile

COMMIT //alternatively, ROLLBACK


A Paul Anthony

Bernhard Mandl
13-Jul-2005, 08:20 AM
every SQL statement is guaranteed to be atomic (all changes done or none)
and all necessary locks are handled automatically.

If you do proper locking on the DF side you will have no problems.

Bernhard

"IAN VAGG" <ian@hitechdist.com.au> schrieb im Newsbeitrag
news:73i9d1tgtfm6t72pr0atj83s3710cfpnbq@4ax.com...
>I need to update Pervasive files created and used by DF3.1d & vdf 10.1,
>from another application using SQL. How do I ensure that counters kept in
>a
> System file are correctly updated. As far as I can see (I am far from an
> expert at SQL) while it is possible to increment the counter, you can't
> control record locking and can't be sure whether the new value of the
> counter is yours or was created almost immediately afterwards by another
> user.
> Any help would be greatly appreciated.
>
> Ian Vagg
>

A Paul Anthony
13-Jul-2005, 10:07 AM
I assume Ian Vagg is referring to embedded SQL, in which case A
statement is indeed atomic. However, if you wish to mimic the
Auto-Increment facility offered by DDs then TWO statements would be
needed, one to insert the new record and another to increment the ID
counter. It is possible (though unlikely) that another transaction
could slip in-between your statements, before the system file update,
and attempt to create a record using an ID that is now no longer
available.

Transaction offer the kind of control required to prevents this scenario
from occurring by going a step beyond simply being atomic with actions
made upon a table by batching actions together. DataFlex supports
transaction for the record-by-record interface and a similar method of
batching actions together without interference can be used in SQL which
I'd be very suprised if Pervasive doesn't support.

A Paul Anthony

Marco
13-Jul-2005, 07:30 PM
Hi,

My 2 cents worth:

I understand that an external application that 'speaks' SQL, needs to
update records. These records are also used in a DF application and
therefore need to be updated in a DF like manner.

In SQL, there is a Unit of work (UOW). This is what DF calls a
transaction.

If you send to the SQL backend a statement like:

"Update sysfile set counter = counter + 1: Insert into MyTable
(Recnum,MyVal) values ((Select counter from sysfile),'This is a
Value'"

Both (sub?)statements will be executed or none. Offcourse after every
update statement the application needs to check if it really took
place but that is besides this discussion.

The system will:
Lock the sysfile record for update
Make the change in the sysfile (only readable by UR connections and
itself)
Insert a record in MyTable, with lock (only UR and self)
Check if no errors were generated and either commit or rollback

The commit or rollback, makes the changes readable by all and unlocks
the records.

Now, the catch...
When you go down to theoretical situations, most CK's by default are
reading the data as UR (check for your CK). UR stands for 'Uncommitted
read'. This was done as this is the closest to the 'normal' DataFlex
way of locking, where saved record are already readable by other
programs prior to the transcation being completed in the originating
program (the transaction is not commited).
So, In the mini micro seconds between the two transactions, a UR
application (DF), could read the new syscounter value, while the
transaction could get rolled back in a micro second later. This could
cause a gap in the numbering.

The other way around is a bit different, when an SQL application is
set to UR (not likely) you always read the uncommitted value (the DF
way), otherwise it will wait until the updated records are all
unlocked. So in this case it will only be able to start until the
while transaction is finished.
This could wait a while if your DF code is messy with locks / unlocks.
SQL applications always have to be able to cope with timeouts and
retry for some times.

Hope the above is useful.
Cheers,
Marco Kuipers

IAN VAGG
14-Jul-2005, 01:17 AM
On Thu, 14 Jul 2005 10:00:12 +0930, Marco Kuipers <marco.kuipers@nci.com.au> wrote:

>Hi,
>
>My 2 cents worth:
>
>I understand that an external application that 'speaks' SQL, needs to
>update records. These records are also used in a DF application and
>therefore need to be updated in a DF like manner.
>
>In SQL, there is a Unit of work (UOW). This is what DF calls a
>transaction.
>
>If you send to the SQL backend a statement like:
>
>"Update sysfile set counter = counter + 1: Insert into MyTable
>(Recnum,MyVal) values ((Select counter from sysfile),'This is a
>Value'"
>
>Both (sub?)statements will be executed or none. Offcourse after every
>update statement the application needs to check if it really took
>place but that is besides this discussion.
>
>The system will:
>Lock the sysfile record for update
>Make the change in the sysfile (only readable by UR connections and
>itself)
>Insert a record in MyTable, with lock (only UR and self)
>Check if no errors were generated and either commit or rollback
>
>The commit or rollback, makes the changes readable by all and unlocks
>the records.
>
>Now, the catch...
>When you go down to theoretical situations, most CK's by default are
>reading the data as UR (check for your CK). UR stands for 'Uncommitted
>read'. This was done as this is the closest to the 'normal' DataFlex
>way of locking, where saved record are already readable by other
>programs prior to the transcation being completed in the originating
>program (the transaction is not commited).
>So, In the mini micro seconds between the two transactions, a UR
>application (DF), could read the new syscounter value, while the
>transaction could get rolled back in a micro second later. This could
>cause a gap in the numbering.
>
>The other way around is a bit different, when an SQL application is
>set to UR (not likely) you always read the uncommitted value (the DF
>way), otherwise it will wait until the updated records are all
>unlocked. So in this case it will only be able to start until the
>while transaction is finished.
>This could wait a while if your DF code is messy with locks / unlocks.
>SQL applications always have to be able to cope with timeouts and
>retry for some times.
>
>Hope the above is useful.
>Cheers,
>Marco Kuipers

Thanks Marco,
That's a great explanation.

It would be good to hear from DAW on this point since they must be doing this in a number of their CK's (MSSQL, DB2 & ODBC) and would therefore have
developed a "standard" method.

Ian Vagg

Ben Weijers
14-Jul-2005, 01:56 AM
If you want to update multiple tables as one unit of work make sure it is
one transaction. Most SQL syntaxes support a statement to begin, commit and
rollback a transaction. Pervasive does not support such commands it only
supports calling an ODBC function to define transactions, so you will either
have to use ODBC or you'll have to offer the statementes as one "batch",
thus making it a unit of work.

The issue about the isolation level used is valid but it is no different
from what you would get if you would use the Embedded Database. If you make
sure you update the counter before creating the record, there should be no
problem.

Regards,

Ben Weijers
Data Access Worldwide