Find Record(s) in Whole Database

[slh]

Hi everyone,

Any tools that I can use to find a record (Eg:Staff Name-Robert) in the whole database?

I understand if a select statement can achieve to search within a table, now the scenario as such that I want to know what are the tables contains records with Robert (Given that not all are under same field name)

Any tools (within/outside) Progress to do it ?

Regards

 

[TomBascom]

You could dump the entire database to ascii and then use "grep".

 

[slh]

You could dump the entire database to ascii and then use "grep".

Hi Tom,

Thats sounds new and interesting, I dont get the second part grep. May I ask you to elaborate please?

Regards

 

[rzr]

http://en.wikipedia.org/wiki/Grep

 

[TomBascom]

If you are running Windows the equivalent to "grep" is "find" or "findstr".

 

[slh]

If you are running Windows the equivalent to "grep" is "find" or "findstr".

Hi Tom,

A million thanks!

 

[fisherdah]

You could dump the entire database to ascii and then use "grep".

Tom-

Quick question, how do i do the "dump the entire database to ascii" part of this? The grep part I get! I am looking for a simple way to dump the entire DB and then search out results. Grep is all I need, but I can only get BINARY data files out using the protutil dump command.


PROGRESS Version 9.1D
Linux

Thanks-

 

[Rob Fitzpatrick]

If you have access to the Data Dictionary, go to the Admin Menu, then Dump Data and Definitions, then Table Contents (.d file).

You will get a UI where you can select some or all of the tables to dump. Choose "Select Some" and then enter a * (i.e. all tables) and OK. Then select OK again and select your destination directory, preferably an empty directory. Select OK. Once you do that all of your application tables will be dumped, each to an ASCII file called <tabledumpname>.d.

Now that you have your application data in flat files you can grep for the text you're looking for.

 

[fisherdah]

If you have access to the Data Dictionary, go to the Admin Menu, then Dump Data and Definitions, then Table Contents (.d file).

You will get a UI where you can select some or all of the tables to dump. Choose "Select Some" and then enter a * (i.e. all tables) and OK. Then select OK again and select your destination directory, preferably an empty directory. Select OK. Once you do that all of your application tables will be dumped, each to an ASCII file called <tabledumpname>.d.

Now that you have your application data in flat files you can grep for the text you're looking for.

Hello Rob,

Thanks for the quick reply. Unfortunately, I either don't know how to access or don't have access to Data Dictionary. How do I find that out? If I don't is there some way to get an ASCII file from command line without learning 4GL!

Thanks!

-Jon

 

[Rob Fitzpatrick]

I assume you know the location of your database, and that the Progress binaries are in your PATH. If you type pro /fully/qualified/path/to/db/dbname -p _dict.r you will be in the Data Dictionary. Obviously, substitute the path and name of your database after the pro command.

You would use this command if the database is offline. If it is still online, use the "mpro" command instead of "pro".

 

[tommartinson]

Though grep would work, it would be pretty darn slow to run. (if the database is big)

You might want to look into Apache Solr or Lucene and progress triggers, if this is something your are going to need to do regularly.

 

[TomBascom]

Much like the Supreme Court I make no judgment about whether or not using "grep" is a good idea, or one which will perform well. Just one that would work

Actually I'm kind of wondering what the use-case behind this requirement is?

 

[fisherdah]

Much like the Supreme Court I make no judgment about whether or not using "grep" is a good idea, or one which will perform well. Just one that would work

Actually I'm kind of wondering what the use-case behind this requirement is?

I dunno if my longer reply got posted or not!

Anyhow, thanks Rob, your instructions helped a lot. All I had to do was force my term to look like it was 1999 and it worked well!

Thanks again!

 

[GregTomkins]

The grep idea is perfect if you are looking to do something every now and then and want maximum flexibility (egrep supports regular expressions, which in the right hands, can do almost anything).

But if you have 'normal users' who want to do this regularly, you might want to check out dynamic queries; they are kind of like dynamic SQL and allow you to iterate through all the tables (as named in the _file table) and search each character field.

It would be a lot more work, though, that's for sure, in both human and computing terms.

 

[fisherdah]

Greg- totally limited use. Might use it 10 more times while debugging and then never again. I am transitioning away from progress but in the meantime needed to check the integrity of the existing data. This'll do that nicely.

Thanks! I'll be back with more questions I am sure.