Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL to search AND REPLACE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] SQL to search AND REPLACE
 Login/Join
 
Platinum Member
posted
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,


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 190 | Registered: May 19, 2017Report This Post
Guru
posted Hide Post
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.


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Report This Post
Platinum Member
posted Hide Post
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.


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 190 | Registered: May 19, 2017Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
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... Wink. 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,


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 190 | Registered: May 19, 2017Report This Post
Expert
posted Hide Post
My personal opinion is that this is too risky.

Hope you are backing up the table beforehand.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
I totally agree with Waz, you may corrupt the Repository and then techsupport will not be able to help.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Expert
posted Hide Post
Can we 'FEX' it, instead of 'SQL'?

Or, Can't we do this in WebFOCUS (CODE)?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL to search AND REPLACE

Copyright © 1996-2020 Information Builders