Re: Database path from DSN (MSDE) (2nd. attempt)
- From: Peter Scholl <delete.this.pscholl@xxxxxxxxxxx>
- Date: Thu, 23 Jun 2005 18:23:13 +0200
Tim,
Thank you very much for taking the time for such a detailed reply.
Using CDatabase, it is not possible to open Access in exclusive mode.
Or have I missed something elementary here? This is the reason for the
"Lockfile".
In an earlier implementation, I did, in fact, make use of a table with
a lock field using MsAccess (I didn't continue testing with SQL
Server). However, this led to problems if the user switched off the pc
in the middle of the "End-of-Day" (pretty common practice!). The
record remained locked (like you said - every lock requires an unlock)
- and I had telephone calls at 4 AM in the morning! After using a
separate "lockfile" in the DB path these problems disappeared. The
most important jobs are launched by the most inexperienced uses
(basically no IT experience), so an Admin basically doesn't exist.
Also, during working hours, there is usually no Admin, so internal
security (computer-wise) really doesn't exist. I know it's bad, but
there's no way my clients would ever be willing to pay an admin, even
on an hourly base - the application must just run, and more than a
couple of hours of support per *year* just aren't accepted. Potential
clients just want everything for free - which I can't do!
Because of the complexity of the application (all sorts of different
jobs to do, including possibly updating/reading external PBX data,
reading external POS data, etc.), I have thousands of
BeginTrans/CommitTrans pairs during the EOD. The "internal date" plays
an essential role in the complete application. Most of the data is
"internal date" dependant and this is bumped during the EOD, usually
around 2-4 AM, but can be any time of day. Luckily the fraud problems
aren't too bad because the amounts we are dealing with just aren't
that large (compared with a bank). Also, there are many places where
the data can be, and is, automatically cross-checked on a daily base.
I don't like the "lockfile" idea much myself, but I haven't found a
better solution. In the case of a computer "switch-off" I must have a
solution to *automatically* clear the lock. I did, in fact, even use a
table with a field which identified the Client PC using the IP
address. Easy (I thought). If the operator restarts the PC and the
application finds the lock set for that PC then just continue the EOD.
I've seen everything (at 4 AM!) - the operator then tries to continue
the EOD from another client PC, because he thinks his PC is broken
(for example), and of course he get's an error telling him that the
table is locked. He doesn't understand the message: 1. maybe because
he's an IT novice or 2. he doesn't understand the language that well.
These are the sort of problems I had before I used the "lockfile"
method. The "lockfile" method (usually) works because the OS clears
the lock automatically on a disconnect.
Since the internal date is a vital part of the application I must,
somehow, be able to prevent data being posted during the EOD. The EOD
does automated postings for the internal date, updates statistics for
the internal date, prints all sorts of lists for the internal date,
deletes specific records for the internal date, cross-checks data etc.
and finally bumps the date. It would be disastrous if a second user or
external POS or PBX etc. were to post data using the internal date
during the EOD.
I'm sorry that was so lengthy, but maybe it has shed a bit more light
on the problem.
So, please don't fall off your chair, this lock method still has it's
uses (I think)! BTW, I believe that all similar applications to mine
(from my competitors) also use a db lock mechanism of some sort for
the EOD. I would love to find another solution though, and will reread
what you wrote a few more times.
Thank you for you patience - and - are you sure you can open Access97+
with CDatabase in exclusive mode? That would solve a lot of my
problems!
Peter
On Thu, 23 Jun 2005 10:28:30 +1200, "Tim" <Tim@NoSpam> wrote:
>In access, open the database exclusively. If the open exclusive fails you
>know someone else is in still and they should log off.
>
>In SQL Server, have a table that has a field that indicates all uses should
>log off / no users can log on - noly the Admin can logon to do one thing in
>this state -> daily rollover & reset the flag. Whenever a user logs on to
>the application, if they are not logging on as Admin, it denies the logon by
>checking this table. If a user is logged on, if the open a new firm to try
>to start new work when the flag is set, they are told to finish their work
>and log off. Perhaps use 2 fields in the table: LogoffRequired and
>DailyRolloverInProgress.
>
>Personally, I think you should design out the processes you have and the
>need for the rollover concept. This kind of processing should have died out
>by 1990. Having a DB Lock function also requires a DB unlock function for
>admins in the application & this is prone to misuse.
>
>I do appreciate that some systems do require the abillity to for example
>edit data once entered to correct anomalies before it is posted. I have seen
>banking systems that do this and nearly fell off my chair when it was
>demonstrated as a good thing only recently. A better and different
>philosophy is to post all transactions and require Reversals and Adjustment
>postings so never - this leaves and audit trail in place and reducing the
>chance for fraud. At the same time educating the people paying you can be
>risky - if they are used to doing something that way and have done so for
>years they often will not listen to the voice of sense and reason... unless
>you talk about fraud.
>
>What sort of path did you expect? The files are on the server so should be
>relative to the server. In terms of access time, using a table in the
>database (SQL Server) will be exceedingly faster and more secure.
>
>The only conceivable time you might normally want to lock an Access DB is
>when you take an automated backup - whatever you do, make sure you use the
>Compact to backup file step first, *do not* copy the file then do a compact
>and replace the original as the copy will often work if there is a user
>still updating data in Access resulting in a backup file that is corrupt
>(the mdb file is open for shared write, shared read). Doing the Compact
>first requires exclusive access to the DB so guarantees the data has
>integrity. You do not need to lock a SQL Server DB ever under normal
>operation even for a backup operation - again, design out this requirement.
>
>Post back if you need more input.
>
>HTH
>- Tim
>
>
>
>"Peter Scholl" <delete.this.pscholl@xxxxxxxxxxx> wrote in message
>news:vtkib19tmce9kj8l7dhe37hbvufgvjfdur@xxxxxxxxxx
>> Tim,
>> Unfortunately only the local path of the db on the server is returned
>> (d:\databases\TestDB.mdf), I don't get multiple paths. However, if I
>> put the name of the server obtained from the registry (or other APIs)
>> in front, and remove the ":" I can construct the path. I'll experiment
>> with this idea and am thankful for your reply.
>>
>>>Now comes the question: Why on earth would you want to know that in a
>>>client
>>>application?
>> Well, maybe there's a better solution, but my application has to be
>> *extremely* simple from the maintenance, installation and operation
>> point of view. i.e. 100% maintenance free and I need to lock the DB -
>> here's a rather lengthy explanation why.
>> An "End-of-Day" is selected manually every day (very often by someone
>> with absolutely no IT experience) and the DB must be locked for about
>> 5-15 minutes for the automated daily postings and internal date change
>> etc. Since there's no way to manually lock the DB (I use ODBC, no DAO)
>> I use the old "lockfile" trick with a file located in the same path as
>> the DB. This is opened in exclusive mode during the EOD and shared
>> mode in all other cases. This is why I need the full path to the DB.
>> If you know of a better solution that would work on Access97+ *and*
>> MSDE I'm open to ideas.
>> BTW, the application runs on Win98 upwards. It is *not* client/server
>> but all workstations communicate real-time with each other using
>> socket connections. Works on single pc, peer-to-peer or domain.
>>
>> Thanks again for your reply,
>> Peter
>>
>> On Wed, 22 Jun 2005 20:01:35 +1200, "Tim" <Tim@NoSpam> wrote:
>>
>>>The path is in the master database... There can be multiple paths...
>>>
>>>try running a query as follows:
>>>
>>>select filename from master..sysdatabases where name = 'your db name'
>>>
>>>Now comes the question: Why on earth would you want to know that in a
>>>client
>>>application? This tends to indicate to me that you are possibly not using
>>>the right tool for the right job. Nothing on the client side needs to know
>>>this and should not know this for normal applications (security) ==> not a
>>>normal application.
>>>
>>>- Tim
>>>
>>>
>>>
>>>"Peter Scholl" <delete.this.pscholl@xxxxxxxxxxx> wrote in message
>>>news:9v1ib1dh7ogt3059pr9sn8nhh3j1v1k2mb@xxxxxxxxxx
>>>> I'm trying to get the full path of my .mdf database from a DSN on a
>>>> shared WinXP drive (running MSDE).
>>>> I've tried the following: (using MFC6, SP6)
>>>>
>>>> SQLGetInfo(g_db.m_hdbc, SQL_DATABASE_NAME, &sMyDatabase, lSize,
>>>> &bytesreturned);
>>>> SQLGetConnectAttr(g_db.m_hdbc, SQL_CURRENT_QUALIFIER, &sMyDatabase,
>>>> lSize, &lLengthReturned);
>>>> SQLGetConnectAttr(g_db.m_hdbc, SQL_ATTR_CURRENT_CATALOG,
>>>> &sMyDatabase, lSize, &lLengthReturned);
>>>>
>>>> All the functions return the name of the database but not the path.
>>>> The path is not in the registry so I can't use those functions either.
>>>> Any help would be very much appreciated
>>>
>>
>
.
- References:
- Database path from DSN (MSDE) (2nd. attempt)
- From: Peter Scholl
- Re: Database path from DSN (MSDE) (2nd. attempt)
- From: Tim
- Re: Database path from DSN (MSDE) (2nd. attempt)
- From: Peter Scholl
- Re: Database path from DSN (MSDE) (2nd. attempt)
- From: Tim
- Database path from DSN (MSDE) (2nd. attempt)
- Prev by Date: Re: how to enable window XP look and feel for VC++ 6.0 MFC ActiveX control(.ocx file)
- Next by Date: Re: Getting memory usage stats
- Previous by thread: Re: Database path from DSN (MSDE) (2nd. attempt)
- Next by thread: Q: writing to STDOUT/STDERR from MFC application ?
- Index(es):
Relevant Pages
|
