Executing SQL Query through ruby using winole32

[Anukul Singhal]

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

 

[Jac Clapp]

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

 

[Michael Linfield]

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

 

[Anukul Singhal]

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

 

[Michael Linfield]

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

 

[Masaki Suketa]

Hello,

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 Suketa