[dba-SQLServer] Opening a log and keeping it open (cross-postedto dba-sql)

Thanks! I think I'll change the number to 254 for safety but nice concise
code.

A.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: July 10, 2005 3:25 PM
To: dba-sqlserver at databaseadvisors.com; accessd at databaseadvisors.com
Subject: Re: [dba-SQLServer] Opening a log and keeping it open
(cross-postedto dba-sql)

On 10 Jul 2005 at 13:11, Arthur Fuller wrote:

> Suppose that I
> revise the code such that the first time ExcuteCommand is called it opens
> the file, creating it if necessary, and leaves it open. Every subsequent
> visit to Execute Command finds the file open and simply appends its SQL
> statement, just as it would to the debug window... but with the advantage
> that I could have several hours' worth of statements recorded to the file,
> rather than merely the last N lines of the debug buffer.

The problem with leaving the file open is that the OS is likely to keep a 
lock on it if your app crashes. Keeping it open may also prevent you from 
accessing the log info while your app is running.   Depending on how 
frequently you will be logging,  try opening the file, appending the log 
entry and then closing it again.

Although slightly slower,  it is much safer.

> 
> 1. Would the app suffer in terms of performance if I did this?

Writing out to  a text file is pretty fast.  It all depends on how often 
you are expecting ExecuteCommand to Execute.  I wouldn't expect any 
noticable performance drop in normal circumstances.

> 2. Assuming that I opened the file in the usual way (as #1 or whatever),
and
> assuming that somewhere in the app I also open another text file, should I
> protect myself by opening it as #98765 or somesuch, 

You have to use file numbers in the range 1-255, inclusive for files not 
accessible to other applications or file numbers in the range 256-511 for 
files accessible from other applications.

> so that if you drop said
> module into one of your apps, it would be guaranteed to work even if your
> app opens a dozen other text files at various points?

Use FreeFile or FreeFile(0) to get the next available handle in the range 1 
- 255 or FreeFile(1) to get the next available handle in the range 256 - 
a511. 

> 3. Presumably the code would also need a "close the file" chunk, but in
the
> ideal world I wouldn't want you to have to code any special thing into
your
> "close app" procedure. So perhaps this might be better dealt with as a
> class? But I seem to recall from Shamil's writing on classes that there is
> an issue regarding the proper termination and garbage disposal of a class.
> 
> Any suggestions, anyone?

Here's a simple shell for the above ideas without an error handling

Function Log(strLogInfo as String)

Dim intLogFileNunber as Integer
Dim strLogFile as String
strLogFile = "Logfile.txt"

intLogFileNumber = Freefile
Open strLogFile For Append As #intLogFileNumber
Print#1, strLogInfo
Close $intLogFileNumber

End Function


-- 
Stuart