Re: search on TEXT field
Mary Ishibashi wrote: > > hello All, > I'm writing a small application where I would like to search > on a TEXT field. Given Informix limitations on just that (we will not > be getting the DataBlade technology any time soon) one possibility we > are considering is using a trigger that would perform a lexical > extraction on the text field. > Does anyone know where to find such a thing? Other solutions to > searching TEXT fields welcome also. Mary, Considering that there is effectively no way to insert blobs with SQL alone you have a solution that does not require a trigger. The app should locate the text blob in memory or a flat file. When the app is ready to insert the row with the text blob, it calls a function written in C (ESQL is not necessary for this) to perform the scan. How you write the scan and how you determine which words are not considered significant is up to you, though I might recommend against using George Carlin's notorious routine as your guide ;-) This is known as a keyword search. Sorry, I do not happen to have any source code that implements this algorithm. BY the way, as SPL is currently implemented, I don't see an SQL solution for your situation. Besides the total lack of string support in SPL (string_variable[low, high] with variable subscripts ) I don't recall SPL allowing you to address the data in a TEXT blob parameter. You MUST do this in the application itself. A trigger that calls a stored procedure that invokes a separate program to do the scan - No, that won't help either. The other program would simply encounter the row lock that the main program holds on the row containing the blob. I have not exhausted the list of possible solutions; I merely think the one I mentioned (not original, BTW) is the best one. If anyone has a better solution, I'd love to hear it. -- -- Jake (In pursuit of undomesticated aquatic avians) +----------------------------------------------------------+ |Aside from that, how did you enjoy the play, Mrs. Lincoln?| +----------------------------------------------------------+