Read INI files from stored procedure

,

Ever wanted to look at some ini setting from a stored procedure? I tried several forums and found that none of them has a script to read ini files from stored procedures.
So here is my script, works in SQL2000. For SQL7, replace table variables with temporary tables.

/*
-- Procedure : lsp_ReadIniFile
-- Description : Returns a value for a specified key in a section in the given ini file
-- Parameters :
--	IN	@sIniFile	-- The full path of the ini file
--	IN	@sSection	-- The section in the ini file
--	IN	@sKey		-- The key in the ini file
-- 	OUT	@sValue		-- The value specified for the given key.
--
-- Note : If any of the input parameters is incorrect/invalid, the output value is a '' string.
--
*/
IF (OBJECT_ID('dbo.lsp_ReadIniFile') IS NOT NULL)
	DROP PROCEDURE dbo.lsp_ReadIniFile
GO
create procedure dbo.lsp_ReadIniFile
	(
		@sIniFile	varchar(1000),
		@sSection	varchar(1000),
		@sKey		varchar(1000),
		@sValue		varchar(1000) OUTPUT
	)
as
begin -- lsp_ReadIniFile
	set nocount on
	--
	set @sValue = ''
	--
	declare	@return	int
	--
	set @return = 1
	--
	-- Create temporary tables and table variables for storing the ini file data
	--
	create table #tmpIniFile(
		ini_text	varchar(2000)
	)
	--
	declare	@tblSection table(
		section_id	integer identity(1,1),
		section_name	varchar(1000),
		primary key (section_id)
	)
	--
	declare	@tblDetails table(
		section_id	integer,
		detail_id	integer identity(1,1),
		key_name	varchar(1000),
		value		varchar(1000),
		primary key (section_id, detail_id)
	)
	--
	-- Read INI file into the temporary table
	-- Note : xp_readerrorlog can be used as well
	--
	declare	@sCommand	varchar(2000)
	set @sCommand = 'type ' + @sIniFile
	insert into #tmpIniFile (ini_text)
	exec master..xp_cmdshell @sCommand
	--
	-- Now remove blanks and comments
	--
	update #tmpIniFile
		set	ini_text = ltrim( rtrim( ini_text ) )
	--
	delete from #tmpIniFile
	where	( ini_text is null )
	or	( ini_text = '' )
	or	( substring( ini_text, 1, 1 ) = ';' )
	--
	-- Get the ini data lines in a cursor
	--
	declare	curIniText cursor for
		select	ini_text
		from	#tmpIniFile
	declare	@nSectionId	integer,
		@sText		varchar(1000),
		@sTemp		varchar(1000)
	declare
		@nOffset	integer,
		@nLength	varchar(1000),
		@sKeyName	varchar(1000),
		@sKeyValue	varchar(1000),
		@sSubStr	varchar(1)
	--
	set @nSectionId = 0
	--
	open curIniText
	fetch from curIniText into @sText
	while @@fetch_status = 0
	begin
		--
		-- Read section
		--
		if substring( @sText, 1, 1 ) = '['
		begin
			set @sTemp = substring( @sText, 2, len( @sText ) - 2 )
			insert into @tblSection
			( section_name )
			values
			( @sTemp )
			--
			set @nSectionId = @@identity
			--
		end -- read section
		--
		-- Read section keys/values
		else
		begin
			--
			set @nLength = len( @sText )
			set @nOffset = 1
			--
			set @sKeyName = ''
			--
			while @nOffset <= @nLength
			begin
				set @sSubStr = substring( @sText, @nOffset, 1 )
				if @sSubStr <> '='
					set @sKeyName = @sKeyName + @sSubStr
				else
					break 
				--
				set @nOffset = @nOffset + 1
			end
			--
			set @sKeyValue = substring( @sText, @nOffset + 1, len( @sText ) )
			--
			set @sKeyName = ltrim( rtrim( @sKeyName ) )
			set @sKeyValue = ltrim( rtrim( @sKeyValue ) )
			--
			if ( @sKeyName <> '' )
			begin
				insert into @tblDetails
				( section_id, key_name, value )
				values
				( @nSectionId, @sKeyName, @sKeyValue )
			end
		end -- read section keys/values
		--
		fetch next from curIniText into @sText
	end
	close curIniText
	deallocate curIniText
	--
	-- Now get the value for the given section and key
	--
	select	@sValue = b.value
	from	@tblSection a
		join @tblDetails b
		on	b.section_id = a.section_id
	where	a.section_name = @sSection
	and	b.key_name = @sKey
	--
	--
	-- Now drop the temporary tables
	--
	drop table #tmpIniFile	
	--
	return @return
end -- lsp_ReadIniFile
go
--
-- Stub to run the stored proc
--
begin
	declare	@sValue varchar(1000)
	--
	exec dbo.lsp_ReadIniFile
		'C:\WINNT\ODBC.INI', 'ODBC 32 bit Data Sources', 'MS Access Database', @sValue OUTPUT
	print @sValue
end
go

Rate

5 (1)

Share

Share

Rate

5 (1)