Questions on DBI::ADO stored procedures and inserts mssql se

Ruby
1

I am writing a class to deal with a mssql server database. I have a
couple of questions on using DBI::ADO

  1. How does one run a stored procedure with input parameters using
    DBI::ADO? I’ve searched for documentation but have not found nothing
    specific

  2. When I insert an item I need to get the primary key of the item
    inserted. In vb.net I run a “Select @@Scope_Identity” after the insert.
    How would I do this with DBI::ADO?

thanks,

Luis

2

I figured out how to solve these two issues with Ruby DBI::ADO

Let’s say you have a stored procedure you normally call like this

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_RenamePaths]
@OldPath = N’C:\ruby’,
@NewPath = N’C:\rubynew’

SELECT ‘Return Value’ = @return_value

You could define a method like this to run it

def tmf_sp_rename_paths(old_path, new_path)
sql = “DECLARE @return_value int exec @return_value =
sp_RenamePaths @OldPath = N’#{old_path}’, @NewPath = N’#{new_path}’
SELECT ‘Return Value’ = @return_value”
dbh=DBI.connect(“DBI:ADO:Provider=SQLNCLI; Data
Source=localhost\sqlexpress;Database=Mydb;uid=MyUser; pwd=MyPass;”)
dbh.doc(sql)
dbh.commit()
end

If you need to get the @@Identity of an insert you can do something
like this

def tmf_insert_new_file(name, length, creation_time, directory_name,
extension, fullname, is_read_only, last_access_time, last_write_time,
filetype, parent_directory)
strInsert = " SET NOCOUNT ON INSERT INTO tblMasterFiles ([Name],
[Length], [CreationTime], [DirectoryName], [Extension], [FullName],
[IsReadOnly], [LastAccessTime], [LastWriteTime], [FileType],
[ParentDirectory]) VALUES (’#{name}’, ‘#{length}’, ‘#{creation_time}’,
‘#{directory_name}’, ‘#{extension}’, ‘#{fullname}’, ‘#{is_read_only}’,
‘#{last_access_time}’, ‘#{last_write_time}’, ‘#{filetype}’,
‘#{parent_directory}’); SELECT @@IDENTITY As myKey"

myKey = “”

dbh=DBI.connect("DBI:ADO:Provider=SQLNCLI; Data

Source=localhost\sqlexpress;Database=Mydb;uid=MyUser; pwd=MyPass;")

dbh.execute(strInsert) do |sth|
  myKey = sth.fetch
 end

 dbh.commit()

return myKey.to_s

end