<< Ack, so if we are using 4.29 Build 2, and still see this issue a lot ,
then what? >>

Then something is still wrong with your setup/environment/anti-virus.
"Access denied" errors are *operating system* errors, and indicate a
fundamental issue with two different processes trying to open the same file
with conflicting share modes.

--
Tim Young
Elevate Software
www.elevatesoft.com

Philip,

<< We're also seeing this when there is a lot of database activity on 4.30
Build 5. This is on a laptop where there is no Antivirus software running.
>>

Do you have your session's private directory (TDBISAMSession.PrivateDir) set
to a valid temporary files directory ?

Please see here for more information:

http://www.elevatesoft.com/supportfaq?action=view&category=dbisam&question=alter_table_access_denied

As mentioned in my last message, this is an operating system error that
DBISAM simply surfaces.  It is *not* a failure with DBISAM, so it is
*always* caused by the OS/environment, and not DBISAM itself.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Yes we are setting the PrivateDir of the Session although it's my understanding that the current release sets that automagically if it's left blank.

The setup I have it's 5 or 6 non windows machines sending plain text SQL queries to the (custom) DB Server. The DB Server then creates a new thread. In this thread it creates a temporary Database and (stLocal) Session and then executes the incoming query. Occasionally this process fails with an Access denied error.

It seems to me that a possible problem could be the way that DBISAM is creating temporary tables for canned queries using multiple threads. Is it possible that because I've got potentially a hundred threads all trying to access the database and each one needs to create a temporary table that occassionally there's a hiccup and DBISAM allocates a temporary tablename to a canned query that is already in use?

Is there another way that I can code my requirement?

Philip


I don't know how Tim generates the names of the temporary tables so I suppose that it could be doing what you think, but I doubt it because then Tim's own server software would be likely to exhibit the same problem as multiple clients connect to a server.

However, IMO the way you are handling things isn't the best approach anyway. On the information provided what I'd recommend is one of two approaches:

1. Set up a thread pool and allocate the incoming queries to the first available thread
2. My preferred option - set up a thread for each attached machine on its first SQL request and leave the thread there to service that machine. Possibly have a disconnect and remove thread if no requests for a time.

In both cases you'd need a mechanism to receive the SQL requests and allocate to the appropriate thread.

You then avoid the overheads of constantly creating threads and all the bits and pieces.

Roy Lambert

Philip,

<< Yes we are setting the PrivateDir of the Session although it's my
understanding that the current release sets that automagically if it's left
blank. >>

I think we're on the same page here, but just to confirm - the latest
releases will automatically default the PrivateDir property to the user's
temporary files directory when the TDBISAMSession is first created, and
that's it.  It will not do anything else with the property if you set the
property to be blank ('').

<< The setup I have it's 5 or 6 non windows machines sending plain text SQL
queries to the (custom) DB Server. The DB Server then creates a new thread.
In this thread it creates a temporary Database and (stLocal) Session and
then executes the incoming query. Occasionally this process fails with an
Access denied error.

It seems to me that a possible problem could be the way that DBISAM is
creating temporary tables for canned queries using multiple threads. Is it
possible that because I've got potentially a hundred threads all trying to
access the database and each one needs to create a temporary table that
occassionally there's a hiccup and DBISAM allocates a temporary tablename to
a canned query that is already in use? >>

No, DBISAM uses the thread ID and an incrementing number (tested against
existing file names in the private directory) to create temporary file
names.  The "incrementing number" part is unnecessary, but still in there as
a fail-safe measure that will never increment more than once.

If you want to send me your project, I can take a look and tell you if
there's anything wrong with your threading code.  It sounds to me like there
may be an issue with the same session being used by more than one thread.

--
Tim Young
Elevate Software
www.elevatesoft.com

FWIW, I have an application that runs as a service and which can have hundreds of
threads, all accessing the same database doing queries on the same table, with some
threads updating the table. All works swimmingly. I'm not on the latest build but I
doubt that matters.

I recommend giving your complete source to Tim. I did once have an issue and Tim
took my "very complex application" and tore it to pieces to get to the bare
essentials, and then I modified it to ensure it was showing the issue, and then he
identified the problem. Excellent service, and I keep buying maintenance because of
it. (In this case, it was an obscure DBISAM issue that was fixed years ago now).

/Matthew Jones/