# Executing SQL Query through ruby using winole32

**URL:** <https://www.ruby-forum.com/t/executing-sql-query-through-ruby-using-winole32/137031>\
**Category:** Ruby\
**Created:** [May 8, 2008, 1:30pm UTC](https://www.ruby-forum.com/t/executing-sql-query-through-ruby-using-winole32/137031 "2008-05-08T13:30:57Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![anukulsinghal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@anukulsinghal](https://www.ruby-forum.com/u/anukulsinghal)\
**Post date:** [May 8, 2008, 1:30pm UTC](https://www.ruby-forum.com/t/executing-sql-query-through-ruby-using-winole32/137031/1 "2008-05-08T13:30:57Z")

</div>

Hi,

I used the following code to connect to SQL Server:

require ‘win32ole’  
server = WIN32OLE.new(‘SQLDMO.SQLServer’)  
server.connect(‘113.212.133.232,1423’,‘username’,‘password’)  
database = server.Databases(‘DIT\_Photon\_1007’)  
for table in database.Tables  
puts table.Name  
end

When i execute this, it successfully gives me all the table names for  
the DB (‘DIT\_Photon\_1007’)

But I want to execute a query, and for that I am using this code:

output = database.execute(‘Select \* from dbo.tLNPOrderStatus’)  
puts output

But I am getting the error like “method missing”  
(Execute)(WIN32OLERuntimeError)

Can anybody help me which method would be used to execute a query in  
win32ole?  
I even tried database.query(’…’), server.query(’…’) and  
server.execute(’…’) but no luck.

Thanks,  
Anukul

---

<div class="post-metadata">

**Author:** ![anukulsinghal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@anukulsinghal](https://www.ruby-forum.com/u/anukulsinghal)\
**Post date:** [May 8, 2008, 10:45pm UTC](https://www.ruby-forum.com/t/executing-sql-query-through-ruby-using-winole32/137031/2 "2008-05-08T22:45:27Z")

</div>

I did a little research on SQL-DMO and came up with this:

output = database.ExecuteWithResults(‘Select \* from  
dbo.tLNPOrderStatus’)

puts “There are #{output.Rows} records.”

puts

# this iterates through the first column of each record returned

# if you want a different column, change the second 1 to a different

number  
output.Rows.times { |row| puts output.GetColumnString(row + 1, 1) }

Jac

---

<div class="post-metadata">

**Author:** ![anukulsinghal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@anukulsinghal](https://www.ruby-forum.com/u/anukulsinghal)\
**Post date:** [May 8, 2008, 10:59pm UTC](https://www.ruby-forum.com/t/executing-sql-query-through-ruby-using-winole32/137031/3 "2008-05-08T22:59:35Z")

</div>

Anukul S. wrote:

> Hi,
> 
> Can anybody help me which method would be used to execute a query in  
> win32ole?  
> I even tried database.query(’…’), server.query(’…’) and  
> server.execute(’…’) but no luck.
> 
> Thanks,  
> Anukul

Just out of curiosity, is there a specific reason you’re using win32ole?  
Using something such as DBI / mysql gems are often times much easier to  
use.

IE:

require ‘mysql’

server = Mysql.new(“hostname”,“username”,“password”,“database”)

results = []

qy = server.query(“SELECT \* FROM table”)

qy.each do |x|  
x.each do |z|  
results \<\< z  
end  
end

Regards,

- Mac

---

<div class="post-metadata">

**Author:** ![anukulsinghal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@anukulsinghal](https://www.ruby-forum.com/u/anukulsinghal)\
**Post date:** [May 9, 2008, 9:00am UTC](https://www.ruby-forum.com/t/executing-sql-query-through-ruby-using-winole32/137031/4 "2008-05-09T09:00:48Z")

</div>

Michael L. wrote:

> Anukul S. wrote:
> 
> > Hi,
> > 
> > Can anybody help me which method would be used to execute a query in  
> > win32ole?  
> > I even tried database.query(’…’), server.query(’…’) and  
> > server.execute(’…’) but no luck.
> > 
> > Thanks,  
> > Anukul
> 
> Just out of curiosity, is there a specific reason you’re using win32ole?  
> Using something such as DBI / mysql gems are often times much easier to  
> use.
> 
> IE:
> 
> require ‘mysql’
> 
> server = Mysql.new(“hostname”,“username”,“password”,“database”)
> 
> results = []
> 
> qy = server.query(“SELECT \* FROM table”)
> 
> qy.each do |x|  
> x.each do |z|  
> results \<\< z  
> end  
> end
> 
> Regards,
> 
> - Mac

Mac,

Thanks for your help.

I am using SQL Server, can you help me with the corresponding code for  
that?

Thanks,  
Anukul

---

<div class="post-metadata">

**Author:** ![anukulsinghal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@anukulsinghal](https://www.ruby-forum.com/u/anukulsinghal)\
**Post date:** [May 9, 2008, 7:50pm UTC](https://www.ruby-forum.com/t/executing-sql-query-through-ruby-using-winole32/137031/5 "2008-05-09T19:50:10Z")

</div>

> I am using SQL Server, can you help me with the corresponding code for  
> that?

Which SQL server are you working with?  
Some possibilities include…

SQLite3  
PostgreSQL  
MySQL

(These are the 3 that are most commonly used with Ruby I’d say)

Let me know what server you are running and I’ll be happy to help.

Regards,

- Mac

---

<div class="post-metadata">

**Author:** ![anukulsinghal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@anukulsinghal](https://www.ruby-forum.com/u/anukulsinghal)\
**Post date:** [May 9, 2008, 11:53pm UTC](https://www.ruby-forum.com/t/executing-sql-query-through-ruby-using-winole32/137031/6 "2008-05-09T23:53:58Z")

</div>

Hello,

Anukul S. wrote:

> server.connect(‘113.212.133.232,1423’,‘username’,‘password’)  
> database = server.Databases(‘DIT\_Photon\_1007’)
> 
> Can anybody help me which method would be used to execute a query in  
> win32ole?  
> Sorry, I don’t know how to execute a query using SQLDMO.SQLServer,  
> but how about using ADODB.Connection instead of SQLDMO.SQLServer?

conn = WIN32OLE.new(‘ADODB.Connection’)  
conn.ConnectionString = ‘Provider=SQLOLEDB;’ +  
‘User ID=username;’ +  
‘Password=password;’ +  
‘Data Source=113.212.133.232;’ +  
‘Initial Catalog=DIT\_Photon\_1007’  
conn.open  
rs = conn.execute(‘Select \* from dbo.tLNPOrderStatus’)  
puts rs.fields.item(0).value

Regards,  
Masaki S.