Forum: Ruby Problem with win32 change notify, dbi and sql server

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
C5b0a0cf40eb23497068889c8fb20a18?d=identicon&s=25 lrlebron@gmail.com (Guest)
on 2006-12-27 16:30
(Received via mailing list)
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
97550977337c9f0a0e1a9553e55bfaa0?d=identicon&s=25 Jan Svitok (Guest)
on 2006-12-28 11:19
(Received via mailing list)
On 12/27/06, lrlebron@gmail.com <lrlebron@gmail.com> 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
97550977337c9f0a0e1a9553e55bfaa0?d=identicon&s=25 Jan Svitok (Guest)
on 2006-12-28 11:19
(Received via mailing list)
On 12/28/06, Jan Svitok <jan.svitok@gmail.com> 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?
C5b0a0cf40eb23497068889c8fb20a18?d=identicon&s=25 lrlebron@gmail.com (Guest)
on 2006-12-28 15:21
(Received via mailing list)
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.
This topic is locked and can not be replied to.