lrlebron@gmail.com
12/19/2006 5:42:00 PM
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
lrlebron@gmail.com wrote:
> 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