Hi Folks,

I was wondering if anyone was able to put together some SQL that would search and replace text over the whole repository (or over all the fex files).

We found some code (see below) that will search for text which is very helpful, but I'd like to do a search and replace.

OR... Is there something else you folks use to edit/create your procedures? I read that VS Code is something that some people use, is that a good alternative? I'm new to WebFOCUS, so please let me know.

use YOUR_IBI_DATABASE_THAT_HAS_YOUR_FEXES

DECLARE @SEARCH_TEXT varchar(300);
DECLARE @FIND_ME varchar(300);
DECLARE @TXT_START int;

SET @FIND_ME = 'TICKET_TYPE_ID'; -- Change this, this is your search value.  The search is case INsensitIVE
SET @SEARCH_TEXT = '% ' + @FIND_ME + ' %'; --You may choose to change this too... this just puts spaces before and after the text you are searching for.  This is not always necessary/desired

PRINT 'Searched all FEX files for:'
PRINT @SEARCH_TEXT

SELECT 
              RO.PRT_PATH AS 'FEX Path'
              ,RO.OBJNAME AS 'FEX File'
			  ,CASE 
				WHEN(-20 + CHARINDEX(UPPER(@FIND_ME), CAST(UPPER(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max))) AS nTEXT))) > 0 
				THEN(-20 + CHARINDEX(UPPER(@FIND_ME), CAST(UPPER(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max))) AS nTEXT))) 
				ELSE 0
			   END AS 'TXT_START'
			  ,CASE 
				WHEN(-20 + CHARINDEX(UPPER(@FIND_ME), CAST(UPPER(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max))) AS nTEXT))) > 0 
				THEN(20 + LEN(@FIND_ME) + CHARINDEX(UPPER(@FIND_ME), CAST(UPPER(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max))) AS nTEXT))) 
				ELSE 20 + LEN(@FIND_ME)
			   END AS 'TXT_END'
			  ,SUBSTRING(
			       CAST(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max)) AS nTEXT), 
				   CASE 
					WHEN(-20 + CHARINDEX(UPPER(@FIND_ME), CAST(UPPER(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max))) AS nTEXT))) > 0 
					THEN(-20 + CHARINDEX(UPPER(@FIND_ME), CAST(UPPER(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max))) AS nTEXT))) 
					ELSE 0
				   END,
				   CASE 
					WHEN(-20 + CHARINDEX(UPPER(@FIND_ME), CAST(UPPER(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max))) AS nTEXT))) > 0 
					THEN(20 + LEN(@FIND_ME) + CHARINDEX(UPPER(@FIND_ME), CAST(UPPER(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max))) AS nTEXT))) 
					ELSE 20 + LEN(@FIND_ME)
				   END) AS 'Excerpt 1'
		      ,@SEARCH_TEXT AS 'Searched Text'
              ,RO.[LASTMODON]
			  ,CAST(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max)) AS nTEXT) AS 'FEX Code'
			  --,CR.OBJ_HANDLE
			  ,CR.[CREATEDBY]
              ,CR.[CREATEDON]
			  ,RO.[LASTACCESSBY]
			  --,RO.OBJTYPE
FROM [WF_CONTENT_REVS] CR
JOIN WF_REPOSOBJ RO ON RO.HANDLE = CR.OBJ_HANDLE
where CAST(UPPER(cast(cast(CR.BCONTENT as varbinary(max)) as varchar(max))) AS nTEXT) like UPPER(@SEARCH_TEXT)
order by RO.PRT_PATH, RO.OBJNAME  

This message has been edited. Last edited by: FP Mod Chuck,

Pretty sure that procedures are stored in SQL as binary objects and you wouldn't be able to easily search through the text that you see in the dev tools.

But, there is a way to search through your procedures in plain text. When you export a Change Management scenario where you'll find both the compiled and raw code. in the output. (In 8009 you had to set this up in Admin Console by turning on RAW output, maybe in 8201m, not sure but in 8202 nothing to set up).

The two outputs are stored in these export folders;
Compiled - c:\ibi\WebFOCUS82\cm\export\BU_Financials\root\WFC\Repository
RAW - c:\ibi\WebFOCUS82\cm\export\BU_Financials\root_content\WFC\Repository


This has been a huge time-saver for us as we look for the impact of changing code that is several places.

NOTE: Part of our Chg Mgt strategy is that ALL content/domain folders shall have an export folder named; BU_DomainName. Before a Chg Mgt scenario is imported, Chg Mgrs edit the domain and refresh the BU_file. These are the files we rely on for our searches. So far it's working out well and saved a bunch of time for creating punch lists for changes.

Hope this helps.

Right... thanks... I'd rather not create a CM package to search and replace though. But thank you, it is an option.

Have a look at that code I posted up there (its for SQL Server btw). It will search all fex and htm files in the repository. It works and really changed how I work.

As for search AND REPLACE... I'm just not confident in doing any UPDATE statements which could compromise the IBI database where all the FEXes are stored.

There is SQL blob search capability. A very rudimentary example was provided by Information Builders, and slightly improved by yours truly.

[SHARING] Search for string in Repository object code

I don't think it can be adapted to update the blobs.

quote:

Originally posted by Francis Mariani:
There is SQL blob search capability. A very rudimentary example was provided by Information Builders, and slightly improved by yours truly.

[SHARING] Search for string in Repository object code

I don't think it can be adapted to update the blobs.

Thanks again... but I still prefer the SQL version I posted up there. I just have a little more confidence in SQL. Also, that SQL version I have up there is case insENsiTIvE... . The three fields that show an excerpt (TXT_START, TXT_END, and EXCERPT_1)of the code are not efficient, but whateves... they aren't too helpful so you could blow 'em away if you prefer.

This message has been edited. Last edited by: Shingles,

My personal opinion is that this is too risky.

Hope you are backing up the table beforehand.

I totally agree with Waz, you may corrupt the Repository and then techsupport will not be able to help.

Can we 'FEX' it, instead of 'SQL'?

Or, Can't we do this in WebFOCUS (CODE)?