From: "lrlebron@..." <lrlebron@...>
Date: 2006-12-20T02:45:06+09:00
Subject: Solved Re: Questions on DBI::ADO stored procedures and inserts mssql server

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