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

[lrlebron]

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

 

[lrlebron]

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:ADOrovider=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:ADOrovider=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