Executing SQL Query through ruby using winole32

ruby-talk
1

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

···

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

2

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

···

On Thu, May 8, 2008 at 7:30 AM, Anukul Singhal <anukul.singhal@gmail.com> wrote:

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
--
Posted via http://www.ruby-forum.com/.

3

Anukul Singhal 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

···

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

4

Hello,

Anukul Singhal 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 Suketa

5

Michael Linfield wrote:

Anukul Singhal 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

···

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

6

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

···

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