Ruby and MySQl

Ruby
1

UI.menu(“Plugins”).add_item(‘Condition Ratingjhj’) { Jeny.paint }

module Jeny
def self.paint
require ‘mysql’
dbh = Mysql.real_connect(“localhost”, “root”, “***”, “test”,3306)

dbh.query(“drop table if exists condition_rating”)
dbh.query(“create table condition_rating(component varchar(20), ID
int(4), rating int(1))”)
dbh.query(“insert into condition_rating
values(‘deck’,2345,1),(‘Substructure’,2349,2),(‘Superstructure’,2353,3)”)
#printf “%d rows were inserted\n”,dbh.affected_rows

res = dbh.query(“SELECT component, ID, rating FROM condition_rating
where rating IN (1,2,3)”)
while row = res.fetch_row do
row.collect! { |v| v.nil? ? “NULL” : v }
x=row[0]
b=row[1]
c=row[2]

printf “%s,%s, %s\n”, row[0], row[1], row[2]
end

Sketchup.active_model.entities.each {|entity|
if entity.entityID == 2349 and c==‘2’
entity.material = “green”
break
end
}

end

end

I am using this script for Google Sketchup. My problem is that whenever
I tried to execute the script, it is successful only for the last column
i.e.
(‘Superstructure’,2353,3). The execution fails for other columns.

I know there is a problem in this part.
res = dbh.query(“SELECT component, ID, rating FROM condition_rating
where rating IN (1,2,3)”)
while row = res.fetch_row do

I would like to know what is the appropriate code to be get successful
result.

2

blue orchid wrote in post #1025776:

I am using this script for Google Sketchup. My problem is that whenever
I tried to execute the script, it is successful only for the last column
i.e.
(‘Superstructure’,2353,3). The execution fails for other columns.

What do you mean by “execution fails”? Do you get an exception? If so,
copy-paste it into your message. If you don’t, then in what way does it
fail?

I know there is a problem in this part.
res = dbh.query(“SELECT component, ID, rating FROM condition_rating
where rating IN (1,2,3)”)
while row = res.fetch_row do

How do you know there is a problem in that part? Again, do you see an
error? Does it run, but not behave in the way you expected? If so, how
did it behave, and how did you expect it to behave instead?

I would like to know what is the appropriate code to be get successful
result.

What’s a successful result?

I suggest you read this for some really good advice on how to frame
questions in a way which will help us help you:
http://www.catb.org/~esr/faqs/smart-questions.html#intro

3

Sketchup.active_model.entities.each {|entity|
if entity.entityID == 2349 and c==‘2’
entity.material = “green”
break
end
}

if entity.entityID == 2349 and c==‘3’, then that entity in google
sketchup changes to color green.

But if I use c==2 or c==1 instead of c==3, then the entity does not
change its color. The reason may be that it failed to identify all the
data in the table ‘condition_rating’. It only works for last column i.e.
(‘Superstructure’,2353,3).

So I would like to know what is the appropriate script so that it
selects all data from table.

4

blue orchid wrote in post #1025823:

Sketchup.active_model.entities.each {|entity|
if entity.entityID == 2349 and c==‘2’
entity.material = “green”
break
end
}

if entity.entityID == 2349 and c==‘3’, then that entity in google
sketchup changes to color green.

But previously, you asserted that the problem was in the dbh.query line
which did a select query - which could not have updated the database and
therefore is completely independent from the above code.

Now you’re saying it’s a problem with Sketchup, which I guess is some
sort of third-party library.

So if you are having a problem using dbh to insert or select into Mysql
tables, then show it here. Otherwise I suggest you take your query to
the Sketchup mailing list, if there is one.

But if I use c==2 or c==1 instead of c==3, then the entity does not
change its color.

Note that there is a difference between 2 and “2” in Ruby. Try using
‘inspect’ to show this. e.g.

puts row[0].inspect
puts row[1].inspect
etc.

5

On Sun, Oct 9, 2011 at 7:13 PM, Brian C. [email protected]
wrote:

Now you’re saying it’s a problem with Sketchup, which I guess is some
sort of third-party library.

It’s 3d modelling software, using Ruby as (one of?) its scripting
language(s):

http://sketchup.google.com/

–
Phillip G.

gplus.to/phgaw | twitter.com/phgaw

A method of solution is perfect if we can forsee from the start,
and even prove, that following that method we shall attain our aim.
– Leibniz

6

puts x.inspect
puts b.inspect
puts c.inspect

If I use above code, then the result is
“Superstructure”
“2353”
“3”

That’s why i think there’s a problem in the dbh query.

7

Hi,

Following is the program for database connection

require ‘rubygems’
require ‘dbi’

dbh = DBI.connect(‘DBI:mysql:ruby’, ‘root’, ‘root’)
if dbh
puts “database connected successfully”
else
puts “database should not connect”
end
dbh.disconnect

When I try to run the above code for database connection i got errors
which i have attached.

please help me to overcome this problem.

8

That’s why i think there’s a problem in the dbh query.

So now you need to take a divide-and-conquer approach to the problem.

What do you see if you go into the mysql command prompt and type

select * from condition_rating;
select * from condition_rating where id in (1,2,3);

If both these show the three rows you expect, then the data has been
inserted correctly into your database. So you should be able to
replicate your problem with a smaller program:

require ‘mysql’
dbh = Mysql.real_connect(“localhost”, “root”, “***”, “test”,3306)

res = dbh.query(“SELECT component, ID, rating FROM condition_rating
where rating IN (1,2,3)”)
while row = res.fetch_row do
puts row.inspect
end

Now, if that small program only shows one row, then clearly there is a
problem in your use of the mysql API (and this means you have eliminated
Sketchup from the problem).

If the above program doesn’t show the error, then you need to find out
what other bit of your original program is causing the problem. A good
way to do this can be to start with your original program and keep
trimming bits out until the problem goes away. The aim is to find the
smallest possible program which reproduces the problem.

What you’ve written looks OK to me according to this article:
http://www.kitebird.com/articles/ruby-mysql.html

So you’ll need to continue to dig like this until you can find what it
is specifically that causes the problem. And if not, then you have a
small test case which demonstrates the problem which you can take to the
author or the mailing list specific to ruby mysql.

You also need to report:

  • what operating system you’re using
  • what version of ruby you’re running
  • what version of ruby mysql you’re using, and where you installed it
    from (e.g. from rubygems? from your system package manager?)
  • what version of the mysql client library you’re using
  • what version of the mysql server you’re talking to

Regards,

Brian.

9

here is a short program i use to look up stuff on a mysql database using
windows 7

require ‘mysql’

host = ‘localhost’
username = ‘root’
password = ‘tomsfiles’
database = ‘phnmsg’
dbquery = “show columns from encounter”

begin

wa = Array.new
p “–0”
conn = Mysql.new(host, username, password, database)
rs = conn.query(dbquery)
p rs.class

 rs.each_hash {|r| wa.push(r)}

 wa.each {|x| p x}
 wa.each {|x| p x["Field"]}

rescue Mysql::Error => e
puts “Error! #{e.to_s}”
ensure
conn.close if conn
end

======================================

10

It looks like there’s a problem here:

where rating IN (1,2,3)")
while row = res.fetch_row do
row.collect! { |v| v.nil? ? “NULL” : v }
x=row[0]
b=row[1]
c=row[2]
…
end

Because you’re in a loop with ‘while row … do’, and then
you’re using the same variables for each row, ie: x, b, and c, this
means that x, b and c will get redefined after every iteration of the
loop. When the loop ends, x, b, and c will then be the last values for
the last row.

I think you’d be better off using Resultset#each_hash method, and then
storing all the hashes in an array.

a = []
res.each_hash do |h|
a << h
end

Something like that, maybe?

Hope this helps!

-Luke