From: Alex Gutteridge <alexg@...>
Date: 2007-08-01T18:25:16+09:00
Subject: Re: parse xml file, put results in mysql db

On 1 Aug 2007, at 17:12, Hans Meier wrote:

> Ok, thanks. now i know why. my code looks this way now:
>
>    mobils = REXML::Document.new(f)
>     root = mobils.root
>
>
>     models = []
>     manufacturers = []
>     mobil_device_ids = []
>
>     mobils.elements.each("MobileDevices/MobileDevice") { |element|  
> puts
> element.attributes["model"]
>         models.push element.attributes["model"] }
>
>     mobils.elements.each("MobileDevices/MobileDevice") { |element|  
> puts
> element.attributes["manufacturer"]
>         manufacturers.push element.attributes["manufacturer"] }
>
>     mobils.elements.each("MobileDevices/MobileDevice") { |element|  
> puts
> element.attributes["id"]
>         mobil_device_ids.push element.attributes["id"] }
>
>     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 mobil_devices (model, manufacturer,
> mobil_device_id,)
>                    VALUES (?,?,?)")
>     models.each_index do |index|
>         model = models[index]
>         manufacturer = manufacturers[index]
>         mobil_device_id = mobil_device_ids[index]
>         sth.execute("mobils.xml", "abo_development", "#{model}",
> "#{manufacturer}", "#{mobil_device_id}")
>     end
>
>
> but i get unfortunattely this failure message:
> RuntimeError: Too many SQL parameters
>         from /usr/local/lib/ruby/site_ruby/1.8/dbi/sql.rb:192:in  
> `bind'
>         from /usr/local/lib/ruby/site_ruby/1.8/DBD/Mysql/Mysql.rb: 
> 569:in
> `execute'
>         from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:617:in `execute'
>         from
> ./script/../config/../config/../lib/rails_maintainer.rb:57:in
> `importContentDelivery'
>         from
> ./script/../config/../config/../lib/rails_maintainer.rb:53:in
> `each_index'
>         from
> ./script/../config/../config/../lib/rails_maintainer.rb:53:in
> `importContentDelivery'
>         from (irb):1

This line:

>     sth=dbh.prepare("INSERT INTO mobil_devices (model, manufacturer,
> mobil_device_id,)
>                    VALUES (?,?,?)")

prepares an SQL statement expecting three values (model  
manufacturer,mobil_device_id).

This line:

>         sth.execute("mobils.xml", "abo_development", "#{model}",
> "#{manufacturer}", "#{mobil_device_id}")

attempts to execute the SQL with 5 values ("mobils.xml",  
"abo_development", model, manufacturer, mobil_device_id). Hence the  
complaint:

> RuntimeError: Too many SQL parameters

Alex Gutteridge

Bioinformatics Center
Kyoto University