Re: Searching values all-over the database

Hi to all! thanks for your ideas and advices! I really liked the export one best! ;-) Now we got our solution, not smart, just straight forward. We search only _relevant_ tables and columns (based on the systems catalog) like that Example for Strings: SELECT <primary key>, <tabname> FROM <table1> WHERE (LOWER(field1) MATCHES "*<criteria>*" OR ... OR LOWER(fieldn) MATCHES "*<criteria>*" ) AND <other criteria> Depending on database and other criteria response is normally in between 1 and 30 seconds, which is OK for that kind of query! I only want to say, a requirement of searching a database like a search machine cannot be dump, because this is the way nearly the whole world searches information (eg. Google). My "personal organizer" is based on a real multiuser database that holds all appointments, contact data, project data and document management data of our small company since 15 years. A global search takes never more than 5 seconds! Kind regards Joachim Engel. "bozon" <curtis@crowson1.com> schrieb im Newsbeitrag news:1158073558.049198.182580@e63g2000cwd.googlegroups.com... > Are you trying to search every field in every table in the database or > do you just want to search text fields in some tables in some columns? > > "A" way to do this would be to write a procedure in your language of > choice that queries the systables and syscolumns tables. Use this > information to construct queries like > > select <table>, <primary-key-info>, col from <table> where col like > "%search_value%"; > > You could also just create your own table of where to look and walk > that table for your searches similar to the above but you manage what > you want the user to search just in case you have internal text columns > that you don't want the user exposing. > > It is open for debate if there is a smart way to do this. (some people > believe there is no smart way to do a dumb thing. I'll let the > philosophers in the group argue about that. Of course some ways of > doing dumb things are dumber than others. ;-) ) > > As for your pocket organizer searching "everything" it really is only > searching user entry fields and not its internal structure fields. > > Which is why I asked what you were trying to do. > > Joachim Engel wrote: > > Hi, > > > > is there a smart way to search any value all-over the database > > regardless in which column it is stored? > > > > We are using IDS 7.31. > > > > TIA, > > Joachim Engel. >

Joachim Engel wrote: > Hi to all! Joachim, No one said an overall, server-wide, search was a 'dumb idea'. Depending on your application and need it's probably a real nice thing to be able to do. We just said that such a search is NOT in the realm of responsibilities of the database engine. Yes, GOOGLE and YAHOO and your PDA do such searching and yes, the information is stored in a database, but, a) the search is NOT part of the database functionality of your PDA but of it's search software written explicitely to search the several fixed format databases and b) GOOGLE and YAHOO and the other WEB search engines use proprietary text search software looking at a unified database of pure text. The data being searched is gleaned from multiple sources, yes, but it is stored in a single searchable format. You can do this also in Informix using, as some have suggested, a text search engine datablade like Excalibur after making a text copy of all of your data in searchable LVARCHAR or CLOB columns. However, again, that's not a function of an RDBMS but of the software running over (or in the case of a datablade, within) it. Glad you found a solution. Art S. Kagel > thanks for your ideas and advices! > I really liked the export one best! ;-) > > Now we got our solution, not smart, just straight forward. > We search only _relevant_ tables and columns (based on the systems catalog) > like that > > Example for Strings: > SELECT <primary key>, <tabname> > FROM <table1> > WHERE (LOWER(field1) MATCHES "*<criteria>*" OR > ... OR > LOWER(fieldn) MATCHES "*<criteria>*" ) AND > <other criteria> > > Depending on database and other criteria response is normally > in between 1 and 30 seconds, which is OK for that kind of query! > > I only want to say, a requirement of searching a database like a search > machine cannot be dump, > because this is the way nearly the whole world searches information (eg. > Google). > > My "personal organizer" is based on a real multiuser database that holds all > appointments, > contact data, project data and document management data of our small company > since 15 years. > A global search takes never more than 5 seconds! > > > Kind regards > Joachim Engel. > > > "bozon" <curtis@crowson1.com> schrieb im Newsbeitrag > news:1158073558.049198.182580@e63g2000cwd.googlegroups.com... > >>Are you trying to search every field in every table in the database or >>do you just want to search text fields in some tables in some columns? >> >>"A" way to do this would be to write a procedure in your language of >>choice that queries the systables and syscolumns tables. Use this >>information to construct queries like >> >>select <table>, <primary-key-info>, col from <table> where col like >>"%search_value%"; >> >>You could also just create your own table of where to look and walk >>that table for your searches similar to the above but you manage what >>you want the user to search just in case you have internal text columns >>that you don't want the user exposing. >> >>It is open for debate if there is a smart way to do this. (some people >>believe there is no smart way to do a dumb thing. I'll let the >>philosophers in the group argue about that. Of course some ways of >>doing dumb things are dumber than others. ;-) ) >> >>As for your pocket organizer searching "everything" it really is only >>searching user entry fields and not its internal structure fields. >> >>Which is why I asked what you were trying to do. >> >>Joachim Engel wrote: >> >>>Hi, >>> >>>is there a smart way to search any value all-over the database >>>regardless in which column it is stored? >>> >>>We are using IDS 7.31. >>> >>>TIA, >>>Joachim Engel. >> > >