[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

Ruby/MySQL SELECT results

Paul Willis

3/26/2007 1:19:00 PM

Hi

I am playing with Ruby/MySQL, I have everything installed ok. I can
INSERT into my database but I am having trouble with SELECT.

I want to see if the text in the field 'title' has been used before and
if it has return the id of that row. If I run...

require "mysql"
my = Mysql::new("hostname", "username", "password", "db")
hasid = my.query("SELECT id FROM table WHERE title = 'Here is a title'")
puts hasid

I get a result like...

#<Mysql::Result:0x55c74c>

I am expecting just the number in the id field. Any ideas?

Paul

--
Posted via http://www.ruby-....
4 Answers

Phillip Gawlowski

3/26/2007 1:53:00 PM

0

Paul Willis wrote:
> Hi
>
> I am playing with Ruby/MySQL, I have everything installed ok. I can
> INSERT into my database but I am having trouble with SELECT.
>
> I want to see if the text in the field 'title' has been used before and
> if it has return the id of that row. If I run...
>
> require "mysql"
> my = Mysql::new("hostname", "username", "password", "db")
> hasid = my.query("SELECT id FROM table WHERE title = 'Here is a title'")
> puts hasid
>
> I get a result like...
>
> #<Mysql::Result:0x55c74c>
>
> I am expecting just the number in the id field. Any ideas?

hasid.each do |row|
puts row
end

Keep in mind, that Ruby/MySQL doesn't "rewind" after using an iterator.

I've circumvented that by doing the following:

array = Array.new
hasid.each do |row|
array.push(row)
end


I haven't tested the code, but it mirrors the code I use, sans
implementation details special to my program.

--
Phillip "CynicalRyan" Gawlowski

Rule of Open-Source Programming #9:

Give me refactoring or give me death!

Paul Willis

3/26/2007 9:11:00 PM

0

>> hasid = my.query("SELECT id FROM table WHERE title = 'Here is a title'")
>> puts hasid
>>
>> I get a result like...
>>
>> #<Mysql::Result:0x55c74c>
>>
>> I am expecting just the number in the id field. Any ideas?
>
> hasid.each do |row|
> puts row
> end
>
> Keep in mind, that Ruby/MySQL doesn't "rewind" after using an iterator.
>
> I've circumvented that by doing the following:
>
> array = Array.new
> hasid.each do |row|
> array.push(row)
> end

I'm having trouble getting my id out of the array to use as a variable
in an if/then/else statement

I want to get the id (there will only be one in the MySQL database that
matches my query, and then do something like...

if hasid > 0
then
puts "already here with id " +hasid
else
puts "this will be a new item"
end

--
Posted via http://www.ruby-....

Phillip Gawlowski

3/26/2007 9:44:00 PM

0

Paul Willis wrote:

>
> I'm having trouble getting my id out of the array to use as a variable
> in an if/then/else statement
>
> I want to get the id (there will only be one in the MySQL database that
> matches my query, and then do something like...
>
> if hasid > 0
> then
> puts "already here with id " +hasid
> else
> puts "this will be a new item"
> end

Try the Mysql::Result#fetch_field_direct method. This should do what you
want. At least it did when I played with it in irb.

--
Phillip "CynicalRyan" Gawlowski

Rule of Open-Source Programming #6:

The user is always right unless proven otherwise by the developer.

Ken Bloom

3/27/2007 8:38:00 PM

0

On Mon, 26 Mar 2007 22:19:04 +0900, Paul Willis wrote:

> Hi
>
> I am playing with Ruby/MySQL, I have everything installed ok. I can
> INSERT into my database but I am having trouble with SELECT.
>
> I want to see if the text in the field 'title' has been used before and
> if it has return the id of that row. If I run...
>
> require "mysql"
> my = Mysql::new("hostname", "username", "password", "db")
> hasid = my.query("SELECT id FROM table WHERE
> title = 'Here is a title'")
> puts hasid
>
> I get a result like...
>
> #<Mysql::Result:0x55c74c>
>
> I am expecting just the number in the id field. Any ideas?
>
> Paul
>
hasid,=my.query("SELECT id FROM table WHERE title = 'Here is a
title'").fetch_row

fetch_row gives you an array with the row in it.

by putting several variables with commas between them, you can unpack the
array into those variables -- if there's only one element, you can
achieve the same behavior by specifying one variable to assign to,
followed by a comma.

This approach leaves a MySQL::Result object lying around waiting to be
garbage collected before it is closed. This may be wasteful --
personally, I prefer to use DBI instead of MySQL directly, and DBI has a
good way to take care of this cleanup automatically:

require 'dbi'
dbh=DBI.connect('DBI:mysql:db:hostname','username','password')
hasid,=dbh.select_one("SELECT id FROM table WHERE title = 'Here is a
title'")
puts hasid

--
Ken Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu...