SQLitening Support Forum

I have a Item Master Table ITE with 100,000 items. I want all the items whose item name contains all the keywords. The number of keywords can be 1 to 5.     
Current solution is shown below (maybe something wrong in the translation from Swedish to English)
Is it possible to solve the selection in the server using LIKE or like() ?
STARTING:
    slSel "SELECT ITEM_NO, ITEM_NAMN FROM ITE ORDER BY ITEM_NO"

FETCH_NEXT_ITEM:
    IF slGetRow = %FALSE THEN GOTO ENDING
    ITE.ITEM_NO   = slF(01)
    ITE.ITEM_NAME = slF(02)
    ITEM_NAME = SHRINK$(ITEM_NAME)

SELECT_ITEM:
    IF INSTR(ITEM_NAME, KEYWORD1) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD2) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD2) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD2) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD2) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD3) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD3) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD4) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD4) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD5) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD5) = 0 THEN GOTO FETCH_NEXT_ITEM
SELECT_ITEM_END:

    PRINT ITEM_NO, ITEM_NAMN
    GOTO FETCH_NEXT_ITEM   
   
ENDING:   
     
     
Example:
KEWORD1 = "FOR"
KEWORD2 = "SS"       

Item Names
1 CHASSI FOR SWITCH    <-- Hit
2 CHASSI-STOMME
3 FORMAT-BAND     
4 KASSERAD OMFORMARE   <-- Hit

/Fim W.

After some googling, I think I have a solution:

SELECT ITEM_NO, ITEM_NAME WHERE ITEM_NAME LIKE '%FOR%' AND ITEM_NAME LIKE '%SS%'
I had difficulty imagining that LIKE %ABC% was so effective.
/Fim W.

I wouldn't think LIKE would be good enough.
Full text search like google might be a good way: http://www.sqlitetutorial.net/sqlite-full-text-search/
Fastest might be to just search a string using PowerBASIC function(s).

To me, it seems that the LIKE function is sufficient for me.
On the other hand, it is sometimes faster to solve it in PowerBasic than with LIKE in the server.
Could probably depend that the server is a quite simple box , while my client is a powerful box.

I will have a look at "Full text search"

/Fim W.

Something like this or a binary search since SQLite is not needed

FUNCTION PBMAIN () AS LONG
LOCAL sDictionary,sSearchFor,sFound AS STRING
sDictionary$ = "one,two,three,four,five,"
sSearchFor$  = "seven,six,five,one,nine,"
sFound = Search(sDictionary$,sSearchFor$)
? sFound,,USING$("Found #",TALLY(sFound,",")) 'five,one,
END FUNCTION

FUNCTION Search(sDictionary AS STRING, sSearchFor AS STRING) AS STRING
LOCAL x,NumberOfWords AS LONG,sWord,sFound AS STRING
NumberOfWords = TALLY(sSearchFor,",")
FOR x = 1 TO NumberOfWords
  sWord = PARSE$(sSearchFor,x) + ","
  IF INSTR(sDictionary,sWord) THEN sFound+=sWord
NEXT
FUNCTION = sFound
END FUNCTION                   

This is probably a good candidate for employing a SQLite custom function.

Examples:
http://sqlitening.com/support/index.php?topic=3539.msg18054#msg18054

http://www.sqlitening.com/support/index.php?topic=2936.0;nowap   (post #4 has code)

Cj, Bern,
Thank you, but I am satisfied with the current solution. Acceptable performance.
/Fim W.

Fim,
What was your solution, LIKE?
The post says "Do things in the server instead of the client".
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bern,
Is the letter file SQLiteningProcsA.bas?  (Never mind,  the SQLiteningProcs must start SQLiteningProcs + your choice of A,B, C  to match your later compiled DLL.
Is everything put into it?                       Yes or whatever you name your DLL SQLIteningProcsZ for example.   SQLiteningProcsA.bas contains samples.
Was slRunProc corrected in 2009?          Assuming it was.

Ok, getting there.   8:37 AM
1 Read everything in SQLiteningProcsA.bas
2 Read documentation on slRunProc
3 Then search this forum for slRunProc and it makes sense.
We need a library of useful server-side functions.
Note: SqliteningServerMonitor.exe is different.  It can be  shelled to if SQLiteningServer.bas is recompiled with correct flag.

Docs for slRunProc only explains  the first and last parameters, rsProcName and rsModChars
slRunProc (rsProcName String, blParm1 Long, blParm2 Long, bsParm3 String, bsParm4 String, [rsModChars String]) Long


'Unload custom functions when no longer needed
I guess this is called once at end of program, but no idea what Return1,Return2,sBuffer1,sBuffer2 values are?
slRunProc "[letter]", lReturn1, lReturn2, sBuffer1, sBuffer2, "U"

Is each function unloaded or just the name of the .DLL?

Not sure, if this registers all functions or just a single function ICF?
What are the values of Return1,Return2,sBuffer1,sBuffer2?

Is this register function or register all functions?
ICF is a single function.
...
   'Register functions and keep them persistent ...
   lResult = slRunProc( "[letter]ICF", lReturn1, lReturn2, sBuffer1, sBuffer2, "Eu")
...
   'Application Code
...
   'Unload custom functions when no longer needed
   slRunProc "[letter]", lReturn1, lReturn2, sBuffer1, sBuffer2, "U"
...

L modchar?  If used then does each function still need to be registered?
I am assuming each client must issue "U" to unload their copy of the DLL.

If a client crashes would that also cause the server to need to be unloaded?
CAUTION!! If you fail to unload it then SQLiteningServer will have to be stopped to get it unloaded.

Created a new thread on slRunProc  http://sqlitening.com/support/index.php?topic=9691.0

cj - I answered your questions in the new thread. 

Cj,
Se Post #1
/Fim