Scan a db for a string

[TomBascom]

This morning a customer asked for a way to scan a (smallish) database for any occurrences of a certain string. He was not terribly inclined to write a program for each table and was hoping for a simple utility.

So I cobbled this together:

/* scan.p
*
*/

define variable tbl as character no-undo.

define variable q as handle no-undo. /* query */
define variable b as handle no-undo. /* buffer */
define variable f as handle no-undo. /* field */
define variable t as character no-undo.

define variable i as integer no-undo.
define variable j as integer no-undo.
define variable k as integer no-undo.

update t label "target" with side-labels.

create query q.

for each _file no-lock where _tbl-type = "t":

display _file-name.

create buffer b for table _file-name.

q:set-buffers( b ).

q:query-prepare( "for each " + _file-name + " no-lock" ).
q:query-open.
q:get-next( no-lock ).

do while q:query-off-end = false:

do i = 1 to b:num-fields:
f = b:buffer-field( i ).
if f:data-type = "character" and f:buffer-value() matches t then
do:
display
b:recid format ">>>>>>>>>>>9" label "recid"
_file-name format "x(20)" label "table"
f:name format "x(20)" label "field name"
f:buffer-value format "x(30)" label "value"
.
down 1.
end.
end.

q:get-next( no-lock ).

end.

delete object b.

end.

 

[Rob Fitzpatrick]

I've had to do this in the past. Is this smallish DB on *nix?

 

[TomBascom]

Alas, and alack... no

 

[Rob Fitzpatrick]

Sometimes "dumb" methods work well. Like a text editor with a "find in files" feature. Or dictionary dump and grep the dump files. Or just grep the DB extents. That can narrow down the search to a particular area. The text editor approach, if it's a good editor, can yield file and offset, which, with some massaging, is a dbkey with which you can narrow the search to a table (in Type II anyway). So just dump that table if the whole DB is too large to dump, or at least limit your dynamic queries to that table. Certainly not as elegant (or customer-friendly) as your approach but it's worked for me.

 

[TomBascom]

Yes, I almost headed down that road (again). But then the allure of writing a nice little 4gl utility struck me and there was no holding back

That plus the "smallish"ness of the database.

 

[Rob Fitzpatrick]

I admit I'm curious to know how a customer could want to know if a string is in a DB without having any clue of which field or even table it might reside in.

 

[TomBascom]

Years of accretion. Configuration stuffed here, there and everywhere according to the whim du jour...

 

[tamhas]

Did you find it?

 

[TomBascom]

I have no idea.

This is one of those customers whose communications tend to be "focused".

 

[RealHeavyDude]

Don't know if it is relevant anymore, but I did write something similar in the past. Just in case there are database fiels of type character with extents ( one might never know ) your logic won't catch them.

You would need to check the EXTENT attribute and loop through them querying the individual extents like BUFFER-VALUE ( extentNumber ).

Heavy Regards, RealHeavyDude.

 

[TomBascom]

In my particular case that wasn't necessary (no arrays). But it's a good point.