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
(
@sIniFilevarchar(1000),
@sSectionvarchar(1000),
@sKeyvarchar(1000),
@sValuevarchar(1000) OUTPUT
)
as
begin -- lsp_ReadIniFile
set nocount on
--
set @sValue = ''
--
declare@returnint
--
set @return = 1
--
-- Create temporary tables and table variables for storing the ini file data
--
create table #tmpIniFile(
ini_textvarchar(2000)
)
--
declare@tblSection table(
section_idinteger identity(1,1),
section_namevarchar(1000),
primary key (section_id)
)
--
declare@tblDetails table(
section_idinteger,
detail_idinteger identity(1,1),
key_namevarchar(1000),
valuevarchar(1000),
primary key (section_id, detail_id)
)
--
-- Read INI file into the temporary table
-- Note : xp_readerrorlog can be used as well
--
declare@sCommandvarchar(2000)
set @sCommand = 'type ' + @sIniFile
insert into #tmpIniFile (ini_text)
exec master..xp_cmdshell @sCommand
--
-- Now remove blanks and comments
--
update #tmpIniFile
setini_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
--
declarecurIniText cursor for
selectini_text
from#tmpIniFile
declare@nSectionIdinteger,
@sTextvarchar(1000),
@sTempvarchar(1000)
declare
@nOffsetinteger,
@nLengthvarchar(1000),
@sKeyNamevarchar(1000),
@sKeyValuevarchar(1000),
@sSubStrvarchar(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
onb.section_id = a.section_id
wherea.section_name = @sSection
andb.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)