Sqlite 3.xx question...

[Fangbeast]

I'm using the below routine to return matches in an SqLite 3.xx database and have struck a funny problem. (Which also causes another routine some agony).

program\searchfield = the field in which to search such as 'itemname'
program\searchtext = the text to search for

If the text to be found contained a "c", all items in the collumn name 'itemname' with the "c" in it are retrieved correctly.

If I type in a "ca", all items in the collumn name 'itemname' with the "ca" in it are retrieved correctly.

If I type in a "can", absolutely nothing is retrieved despite their being many occurences of words with "can" in them.

I've checked all through the Sqlite home page and the LIKE keyword is case insensitive and seems to have no arbitrary limits. Has anyone else had this problem? I cannot see what to do here.

(cannot post all source to the forum as it usually barfs when I try it)

Code: Select all

;============================================================================================================================
; Open the find inventory window
;============================================================================================================================

Procedure FindInventory()

  If EventType() = #PB_EventType_Change
  
    ClearGadgetItemList(#Gadget_inventory_items)
    
    program\searchfield = GetGadgetText(#Gadget_inventory_searchfield)
    program\searchtext  = GetGadgetText(#Gadget_inventory_searchbox)
    
    Select program\searchfield
    
      Case "all collumns"  : program\query = "SELECT * FROM inventory WHERE itemname LIKE '%" + program\searchtext + "%'"
      
        program\query + " OR  supplier LIKE '%"     + program\searchtext + "%'"
        program\query + " OR  manufacturer LIKE '%" + program\searchtext + "%'"
        program\query + " OR  modelno LIKE '%"      + program\searchtext + "%'"
        program\query + " OR  serialno LIKE '%"     + program\searchtext + "%'"
        program\query + " OR  category LIKE '%"     + program\searchtext + "%'"
        program\query + " OR  boughton LIKE '%"     + program\searchtext + "%'"
        program\query + " OR  cost LIKE '%"         + program\searchtext + "%'"
        program\query + " OR  paidby LIKE '%"       + program\searchtext + "%'"
        program\query + " OR  resale LIKE '%"       + program\searchtext + "%'"
        program\query + " OR  warranty LIKE '%"     + program\searchtext + "%'"
        program\query + " OR  location LIKE '%"     + program\searchtext + "%'"
        program\query + " OR  owner LIKE '%"        + program\searchtext + "%'"
        program\query + " OR  condition LIKE '%"    + program\searchtext + "%'"
        program\query + " OR  comment LIKE '%"      + program\searchtext + "%'"
        program\query + " OR  firstentry LIKE '%"   + program\searchtext + "%'"
        program\query + " OR  lastedit LIKE '%"     + program\searchtext + "%'"
        program\query + " OR  record LIKE '%"       + program\searchtext + "%'"
              
      Default     : program\query = "SELECT * FROM inventory WHERE " + program\searchfield + " LIKE '%" + program\searchtext + "%'"
      
    EndSelect

    If SQL3GetTable(program\query, @myRows, @myCols, program\dbhandle)
  
      program\dcounter = 0
            
      If CountList(SqlData.s()) <> 0                                              ; Check if any data was returned even if query worked
    
        ForEach SqlData.s()

          ClearStructure()
          
          inventory\itemname      = StringField(SqlData.s(),  1, "|")
          inventory\supplier      = StringField(SqlData.s(),  2, "|")
          inventory\manufacturer  = StringField(SqlData.s(),  3, "|")
          inventory\modelno       = StringField(SqlData.s(),  4, "|")
          inventory\serialno      = StringField(SqlData.s(),  5, "|")
          inventory\category      = StringField(SqlData.s(),  6, "|")
          inventory\boughton      = StringField(SqlData.s(),  7, "|")
          inventory\cost          = StringField(SqlData.s(),  8, "|")
          inventory\paidby        = StringField(SqlData.s(),  9, "|")
          inventory\resale        = StringField(SqlData.s(), 10, "|")
          inventory\warranty      = StringField(SqlData.s(), 11, "|")
          inventory\location      = StringField(SqlData.s(), 12, "|")
          inventory\owner         = StringField(SqlData.s(), 13, "|")
          inventory\condition     = StringField(SqlData.s(), 14, "|")
          inventory\picture       = StringField(SqlData.s(), 15, "|")
          inventory\comment       = StringField(SqlData.s(), 16, "|")
          inventory\firstentry    = StringField(SqlData.s(), 17, "|")
          inventory\lastedit      = StringField(SqlData.s(), 18, "|")
          inventory\record        = StringField(SqlData.s(), 19, "|")
          
          DisplayData()
          
          program\dcounter + 1
          
          LastLine(#Gadget_inventory_items, program\dcounter - 1)                   ; Make sure the current line is visible
          
         Next
        
      EndIf
      
    EndIf

    MessageHandler(#StatusBar_inventory_messages, "GetDataToStructure", "Found all records in the [ " + program\searchfield + " ] field containing the string [ " + program\searchtext + " ]", 1)
    
  EndIf

  program\query = ""

EndProcedure

[dagcrack]

...Hi,

Code: Select all

...
...
WHERE NAME LIKE '"can%"; 

You should place only one % at the end of your search criteria in this case.

If you want to find "can" within an entire string you should do what you're already doing, use % on both sides of the criteria.

But:

Remember to keep your strings in your database enclosed with chr(34).. and if you can, use the quote() routine in sqlite3 instead, to process your strings before commiting them.

So:

For saving a few headaches, you should try your queries in a tool like SQLiteSpy. This way prototyping is a piece of cake, no trouble.

And while in code, if results arent equal, then always keep an eye in the errors.


By the way, your query looks a little bulky.

[Fangbeast]

Been trying to get sqlitespy from the site for the last 2 hours, just keeps timing out on me. any other source?

[oldBear]

Got it right away here -
http://www.delphipages.com/result.cfm?TD=20

or

http://www.yunqa.de/delphi/sqlitespy/
cheers

[Fangbeast]

This is weird. I posted a reply 1 hour ago and now it's gone???

@oldBear thanks, I got the file finally.

[Fangbeast]

@DagCrack Didn't know about the quote() routine but I always enclose my literal text in quotes, have for the last 5 years.

As to the fatness of the query, I don't know how to query all columns using the WHERE clause yet or I'd try that. Still looking in a load of manuals for anything that makes sense to me.

I tried the exact same query in Sqlitespy that I tried in my code. In Sqlitespy, it works properly. In my code, it quits working after 2 characters. Weird and no idea why.

Back to the drawing board.

[Fangbeast]

If I do this in PB and debug the hell out of everything, I get the right variables.

Item is "Altec Lansing"
Field name is "itemname"

SELECT * FROM inventory WHERE itemname LIKE '%a%' Shows all with "a"
SELECT * FROM inventory WHERE itemname LIKE '%al%' Shows all with "al"
SELECT * FROM inventory WHERE itemname LIKE '%alt%' Shows nothing

The query seems to die after 2 characters.

If I do the same in SqliteSpy (or any of the other sqlite tools) I get all matches, no matter how many (or how few) characters I type. grrrr

[Fangbeast]

Just did more tests. This LIKE seems to be very selective with its' characters allowed.

If I test for 'alt', it cannot find anything with that in it, despite lots of things with 'alt' in it.

If I test for 'can' it can find all things with 'can' in it.

If I test for 'gr', it finds GRAndcell and GRAecross.
If I test for 'gra', it only finds GRAndcell.

If I test for 'st', if finds lots of things.
If I test for 'stu', it can't find 'STUrdee'

What is going on with that keyword?

[Rings]

'Like' is wrong spelled,
better you use :

'perhaps like'

[Fangbeast]

Hohoho, everyone is a comedian.

I've found the problem, it's a bug in PB4.x's ForEach command

According to the manual, ForEach is supposed to go through ALL elements of a linked list. It doesn't.

I've been debugging for days and have found that if there is only 1 element in a linked list, ForEach fails to iterate it.

I've found out the hard way by setting my list with FirstElement and debugging it, finding that there was a returned element (even if only one) and then trying to get ForEach to return it and it doesn't.

Ever.

Okay, I will yell in the bug section after tea.

[Fangbeast]

I was wrong about ForEach.

Using the above code to get the data from the structure, I use a routine called DisplayData (which a lot of my program re-uses) to display the data.

Code: Select all

Procedure DisplayData()
  If inventory\cost <> ""
    cost.s = "$" + inventory\cost
    flag.l = 1
  Else
    cost.s = inventory\cost
    flag.l = 0
  EndIf
  Debug inventory\itemname + " - " + inventory\boughton + " - " + inventory\supplier + " - " + cost.s + " - " + inventory\record
  AddGadgetItem(#Gadget_inventory_items, -1, inventory\itemname + Chr(10) + inventory\boughton + Chr(10) + inventory\supplier + Chr(10) + cost.s + Chr(10) + inventory\record)
  ChangeIcon(#Gadget_inventory_items,   program\dcounter -1, 0, form\itemname)
  If inventory\boughton <> ""
    ChangeIcon(#Gadget_inventory_items, program\dcounter -1, 1, form\boughton)
  EndIf
  
  If inventory\supplier <> ""
    ChangeIcon(#Gadget_inventory_items, program\dcounter -1, 2, form\supplier)
  EndIf
  If flag.l
    ChangeIcon(#Gadget_inventory_items, program\dcounter -1, 3, form\cost)
  EndIf
EndProcedure

I am using debug above to check that I am getting something back and each time, I am.

However, if there is only one record in the structure, "AddGadgetItem" fails to add it for some reason. And I can reproduce this every time.

The only thing I can think of that might be the problem (I will have to test this) is that my subclassing the ListIconGadget to allow header colours is somehow interfering with the adding to the gadget.

[Fangbeast]

No, that's not it. Turned off all subclassing, just raw gadget usage via "AddGadgetItem(#MyGadget, -1, " etc, etc.

As long as there is only one item returned, it fails to display it.