# Problem with win32 change notify, dbi and sql server

**URL:** <https://www.ruby-forum.com/t/problem-with-win32-change-notify-dbi-and-sql-server/80485>\
**Category:** Ruby\
**Created:** [December 27, 2006, 4:30pm UTC](https://www.ruby-forum.com/t/problem-with-win32-change-notify-dbi-and-sql-server/80485 "2006-12-27T16:30:48Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 27, 2006, 4:30pm UTC](https://www.ruby-forum.com/t/problem-with-win32-change-notify-dbi-and-sql-server/80485/1 "2006-12-27T16:30:48Z")

</div>

I have created a script to monitor a directory and update a database  
based on changes to the directory. I am using WinXP, Ruby 1.8.5 and  
Ruby DBI. The script is divided into a class to handle the db functions  
(class FsmDB) and a class to handle the folder monitoring (class Fsm).  
When I add several files at the same time to a folder classFsm should  
call the file\_added method for each file that was added. However, the  
script only inserts one file and stops.

Here’s the code

require ‘dbi’  
require ‘yaml’  
require ‘win32/changenotify’  
require ‘win32/file’  
include Win32  
require ‘win32/dir’

class FsmDb

attr\_accessor :server, :db, :user, :password

def initialize()

```
dbconfig = YAML::load(IO.read('config.yml'))

@server = dbconfig['server']
@db = dbconfig['db']
@user = dbconfig['user']
@password = dbconfig['password']

```

end

def tmf\_insert\_new\_file(name, length, creation\_time, directory\_name,  
extension, fullname, is\_read\_only, last\_access\_time, last\_write\_time,  
filetype, parent\_directory)

strInsert = " SET NOCOUNT ON INSERT INTO tblMasterFiles ([Name],  
[Length], [CreationTime], [DirectoryName], [Extension], [FullName],  
[IsReadOnly], [LastAccessTime], [LastWriteTime], [FileType],  
[ParentDirectory]) VALUES (’#{name}’, ‘#{length}’, ‘#{creation\_time}’,  
‘#{directory\_name}’, ‘#{extension}’, ‘#{fullname}’, ‘#{is\_read\_only}’,  
‘#{last\_access\_time}’, ‘#{last\_write\_time}’, ‘#{filetype}’,  
‘#{parent\_directory}’); SELECT @@IDENTITY As myKey"

```
myKey = ""

dbh=DBI.connect("DBI:ADO:Provider=SQLNCLI; Data

```

Source=#{@server};Database=#{@db};uid=#{@user}; pwd=#{@password};")

```
dbh.execute(strInsert) do |sth|
  myKey = sth.fetch
end

dbh.commit()

return myKey.to_s

rescue DBI::DatabaseError => e
 puts "An error occurred in tmf_insert_new_file"
 puts "Error code: #{e.err}"
 puts "Error message: #{e.errstr}"

```

end

end

class Fsm

attr\_accessor :folder\_to\_watch, :last\_created, :last\_changed,  
:excluded\_files, :excluded\_dirs

def initialize(folder)

```
@folder_to_watch = folder
@MyDb = FsmDb.new()

rescue Exception =>e
    puts "Could not initialize file monitor"
    puts e

```

end

def monitor  
filter = ChangeNotify::FILE\_NAME | ChangeNotify::DIR\_NAME |  
ChangeNotify::LAST\_WRITE

```
cn = ChangeNotify.new(@folder_to_watch, true, filter)

cn.wait{|arr|

      if not @excluded_dirs == nil and @excluded_dirs.length > 0

      fullname = @folder_to_watch + File.dirname(arr[0][1])
      myMatch = @excluded_dirs.select{|e| fullname.include?e}

      if myMatch.length == 0

      if not @excluded_files == nil and @excluded_files.length > 0

                    if not @excluded_files.include?

```

File.extname(arr[0][1])

```
                            if arr[0][0].to_s.include? "added"

                              puts "Adding file " + arr[0][1].to_s
                              file_added(arr)

                            end

                    end

              end

        end

   end

} while true

```

rescue Exception =\> e  
puts e  
end

def file\_added(arr)

```
date_format_str = "%Y%m%d %X"
filename = arr[0][1].to_s

if filename.include?"/"
  filename.gsub!("/","\\")
end

if @folder_to_watch.include?"/"
 @folder_to_watch.gsub!("/","\\")
end

fullname = @folder_to_watch + filename

stat = File::Stat.new(fullname)

file_type = File.ftype(fullname).capitalize
dirname = File.dirname(fullname)

if file_type.include? "File"

 myKey = @MyDb.tmf_insert_new_file(File.basename(filename),

```

stat.size, stat.ctime.strftime(date\_format\_str), dirname,  
File.extname(fullname), fullname,File.read\_only?(fullname),  
stat.atime.strftime(date\_format\_str),  
stat.mtime.strftime(date\_format\_str), file\_type,  
File.dirname(fullname))

```
   elsif file_type.include? "Directory"

  myKey = @MyDb.tmf_insert_new_file(File.basename(filename), 0,

```

stat.ctime.strftime(date\_format\_str), fullname, ‘None’, fullname,  
File.read\_only?(fullname), stat.atime.strftime(date\_format\_str),  
stat.mtime.strftime(date\_format\_str), “Directory”, dirname)

```
end

 rescue Exception =>e
  puts "Error in file_added method."
  puts e

```

end

def files\_to\_exclude(arr)  
@excluded\_files = arr  
end

def dirs\_to\_exclude(arr)  
@excluded\_dirs = arr  
end

end

MyFSM = Fsm.new(‘C:\’)  
file\_arr = [".moztmp", “.LOG”, “.log”,".bak", “.tmp”, “.pf”, “.dat”,  
“.TMP”, “.DAT”]  
dir\_arr = [Dir::LOCAL\_APPDATA, Dir::APPDATA, Dir::WINDOWS + “\Temp”,  
Dir::COMMON\_APPDATA, Dir::INTERNET\_CACHE, Dir::SYSTEM, Dir::WINDOWS +  
“\Prefetch” ]  
MyFSM.files\_to\_exclude(file\_arr)  
MyFSM.dirs\_to\_exclude(dir\_arr)  
MyFSM.monitor()

Any ideas would be greatly appreciated.

thanks,

Luis

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 28, 2006, 11:19am UTC](https://www.ruby-forum.com/t/problem-with-win32-change-notify-dbi-and-sql-server/80485/2 "2006-12-28T11:19:30Z")

</div>

On 12/27/06, removed\_email\_address@domain.invalid [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I have created a script to monitor a directory and update a database

Hi, don’t know the solution, just a small off-topic hint:

you can write this

> ```
> myMatch = @excluded_dirs.select{|e| fullname.include?e}
> if myMatch.length == 0
> 
> ```

as  
unless @excluded\_dirs.any?{|e| fullname.include?e}

and this

> ```
> if not @excluded_files == nil and @excluded_files.length > 0
> 
> ```

```
          if not @excluded_files.nil? and not @excluded_files.empty?

```

or

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 28, 2006, 11:19am UTC](https://www.ruby-forum.com/t/problem-with-win32-change-notify-dbi-and-sql-server/80485/3 "2006-12-28T11:19:30Z")

</div>

On 12/28/06, Jan S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:  
Sorry, my kbd went wrong ☹

> as  
> unless @excluded\_dirs.any?{|e| fullname.include?e}
> 
> and this
> 
> > ```
> > if not @excluded_files == nil and @excluded_files.length > 0
> > 
> > ```
> 
> ```
> if not @excluded_files.nil? and not @excluded_files.empty?
> 
> ```
> 
> or  
> unless @excluded\_files.nil? or @excluded\_files.empty?

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 28, 2006, 3:21pm UTC](https://www.ruby-forum.com/t/problem-with-win32-change-notify-dbi-and-sql-server/80485/4 "2006-12-28T15:21:51Z")

</div>

Thanks for the tips. I think I’ve narrowed down my problem to a DBI:ADO  
issue and this bit of code

def tmf\_insert\_new\_file(name, length, creation\_time, directory\_name,  
extension, fullname, is\_read\_only, last\_access\_time, last\_write\_time,  
filetype, parent\_directory)

strInsert = " SET NOCOUNT ON INSERT INTO tblMasterFiles ([Name],  
[Length], [CreationTime], [DirectoryName], [Extension], [FullName],  
[IsReadOnly], [LastAccessTime], [LastWriteTime], [FileType],  
[ParentDirectory]) VALUES (’#{name}’, ‘#{length}’, ‘#{creation\_time}’,  
‘#{directory\_name}’, ‘#{extension}’, ‘#{fullname}’, ‘#{is\_read\_only}’,  
‘#{last\_access\_time}’, ‘#{last\_write\_time}’, ‘#{filetype}’,  
‘#{parent\_directory}’); SELECT @@IDENTITY As myKey"

```
myKey = ""

 @dbh.execute(strInsert) do |sth|
    myKey = sth.fetch_all
 end

@dbh.commit()

return myKey.to_s

rescue DBI::DatabaseError => e
 puts "An error occurred in tmf_insert_new_file"
 puts "Error code: #{e.err}"
 puts "Error message: #{e.errstr}"

```

end

I was able to get some improvements by initializing the database handle  
when the class is instantiated as an instance variable and setting  
NonBlocking to true in the connection string. But I am still missing  
inserts. For example, I dragged a folder with 218 file/directories and  
got 180 inserted in the db. So we are still missing 38. But it is  
better than just getting 1 inserted.