From: Robert Klemme <shortcutter@...>
Date: 2007-07-31T23:42:51+09:00
Subject: Re: parse xml file, put results in mysql db

2007/7/31, Hans Meier <mailboxoderso@gmx.net>:
> Robert Klemme wrote:
> > 2006/11/16, Kathy Simmons <kathys39@hotmail.com>:
> >> doc = Document.new(scanfile)
> >> doc.elements.each("nmaprun/scaninfo") { |element|
> >>         puts element.attributes["status state"] }
> >>
> >> # database insert
> >> dbname="nmap"
> >> m = Mysql.new("localhost", "root", "")
> >> dbh=DBI.connect("dbi:Mysql:nmap:localhost", "root", "")
> >> m.select_db(dbname)
> >> sth=dbh.prepare("INSERT INTO rawdata
> >> (file,tool,arguments,startime,version) VALUES (?,?,?,?,?)")
> >> sth.execute("scanfile.xml", "nmap", "${args}", "#{timeofscan}",
> >> "${version}"")
> >
> > You have a scoping problem: you set timeofscan etc. inside the block
> > when traversing the XML document.  But they are not visible outside
> > the block and thus you likely get a NameError.
> >
> > If you want to insert multiple values into the DB you need to traverse
> > the XML doc and then execute for each iteration.
> >
> > I'd also use the block forms of your DB connection methods and file
> > handling in order to make sure connections are properly closed etc.
> > It's good to start that habit early. :-)
> >
> > Sample:
> >
> > doc = File.open('scanfile.xml', 'rb') {|scanfile| Document.new(scanfile)
> > }
> >
> > Kind regards
> >
> > robert
>
> Ok, thank you ! this way the code works:
>
>
>     model = {}
>     mobils.elements.each("MobileDevices/MobileDevice") { |element| puts
> element.attributes["model"]
>       model = element.attributes["model"] }
>
>     manufacturer = {}
>     mobils.elements.each("MobileDevices/MobileDevice") { |element| puts
> element.attributes["manufacturer"]
>       manufacturer = element.attributes["manufacturer"] }
>
>     mobil_device_id = {}
>     mobils.elements.each("MobileDevices/MobileDevice") { |element| puts
> element.attributes["id"]
>       mobil_device_id = element.attributes["id"] }
>
>
>     # db insert
>     dbname="abo_development"
>     m = Mysql.new("localhost", "root", "")
>     dbh=DBI.connect("dbi:Mysql:abo_development:localhost", "root", "")
>     m.select_db(dbname)
>
>     sth=dbh.prepare("INSERT INTO mobile_devices (model, manufacturer,
> mobil_device_id)
>                    VALUES
>                      (?,?,?)")
>     sth.execute("#{model}", "#{manufacturer}", "#{mobil_device_id}")
>
>
>     sth.finish
>     dbh.disconnect
>
>
>
> But there is one problem: it writes only one row in the database. Can
> anyone say why?

Yes. You did not follow my directions. :-)  Think a moment about the
logic you are employing and it should be immediately clear why you
only get one record in the DB.

robert