Find a value in the database

[Anupama]

I want to find a particular string stored in any field / table in the database? Can you please help?

If I try to run a query as below, it says unknown or ambiguous table. How can I check for the data stored in the fields?

for each _file no-lock,
each _field of _file where _data-type = "character" no-lock:
for each _file-name no-lock:
disp _field-name.
end.
end.

 

[RealHeavyDude]

There is no way you can achieve this with static buffers. _field-name is a field of the _Field table and no static table reference.

You need to work with dynamic buffers and queries in order to achieve this.

HTH, RealHeavyDude.

 

[Casper]

I want to find a particular string stored in any field / table in the database? Can you please help?

I cant see any valid reason for doing this. Do you realise that going through all records of all tables in a database to look for a specific string is going to take lots and lots of time if the database has any meaning full size?

This is why they invented tables and indexes so you can use a relational model and store your data on specific logical places and not random in the database

Casper.

 

[RealHeavyDude]

On top of my head I remember something with the proutil utitility:

• Before changing the code page of a database you should do an analysis whether the conversion will be smooth or not because there might be characters present in the current code page which might not be present in the desired code page.
• The proutil convchar has a charscan option which will produce such a report - it does that in searching every character field for the occurrence of the specified characters and gives you, AFAIK, a list containing the table & field name plus the RECID.

HTH, RealHeavyDude.

 

[tamhas]

Given how inefficient this is going to be in the 4GL, have you considered just dumping the data from any suspect tables and using grep?

I'm not entirely kidding....

 

[kartik@jktech]

Hi man Try this...

- Kartikeya Sharma
http://www.jktech.com

[/COLOR][/SIZE][/COLOR][/SIZE][/COLOR][/SIZE][/COLOR][/SIZE]
&SCOPED-DEFINE StringCheck "1"
DEFINE VARIABLE qhnd AS HANDLE NO-UNDO.
DEFINE VARIABLE icount AS INTEGER NO-UNDO.
DEFINE VARIABLE bfhnd AS HANDLE NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE ii AS INTEGER NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE TEMP-TABLE tt NO-UNDO
 FIELD tableName AS CHAR FORMAT "X(22)" LABEL "TABLE NAME"
 FIELD fieldName AS CHAR FORMAT "X(22)" LABEL "FIELD NAME"
 FIELD fieldValue AS CHAR FORMAT "X(22)".
CREATE QUERY qhnd.
FOR EACH _file WHERE _tbl-type = "t":
  iCount = 0.
  CREATE BUFFER bfhnd FOR TABLE _file._file-name.
  qhnd:SET-BUFFERS(bfhnd). 
  qhnd:QUERY-PREPARE("for each " + _file._file-name).
  qhnd:QUERY-OPEN().
  
  REPEAT:
    qhnd:GET-NEXT().
    IF qhnd:QUERY-OFF-END THEN LEAVE.
    DO i = 1 TO bfhnd:NUM-FIELDS:
      hField = bfhnd:BUFFER-FIELD(i).
      IF NOT hField:EXTENT <> 0 AND hField:BUFFER-VALUE MATCHES ("*" + {&StringCheck} + "*") THEN
      DO:
        CREATE tt.
        ASSIGN tableName = _file._file-name
               fieldName = hField:LABEL
               fieldValue = hField:BUFFER-VALUE.
      END.
      ELSE IF hField:EXTENT > 0 THEN
      DO:
        DO ii = 1 TO hField:EXTENT:
           IF hField:BUFFER-VALUE[ii] MATCHES ("*" + {&StringCheck} + "*") THEN
           DO:
              CREATE tt.
              ASSIGN tableName = _file._file-name
                     fieldName = hField:LABEL + '[' + string(ii) + ']'
                     fieldValue = hField:BUFFER-VALUE[ii].
           END.
        END.
      END.
    END.
  END.
END.
FOR EACH tt:
    i = i + 1.
END.
DISP "SERACH FOR " {&StringCheck}SKIP "NUMBER OF HITS " STRING(i) WITH FRAME a CENTERED WIDTH 74.
FOR EACH tt WITH FRAM b CENTERED:
  DISP tt.
END.
 
[SIZE=2][COLOR=#0080ff][SIZE=2][COLOR=#0080ff]

​