1. ‹ Older
  2. 7 months ago
    Edited 7 months ago by Norman P

    Is this an FTS table ?
    If not then you probably want LIKE , or =
    MATCH is used with FTS tables
    http://sqlite.org/fts5.html#full_text_query_syntax

    That said I dont see that error here using a non-FTS table

    dim micadtextfile as new SQLiteDatabase
    		
    if micadtextfile.connect() then
    				
      micadtextfile.sqlexecute("create table micadtable( buzzword )")
    				
      dim sql as string = "SELECT buzzword FROM micadtable WHERE buzzword MATCH 'Klee';"
      dim rs as recordset = micadtextfile.SQLSelect(sql)
      If rs <> Nil and not micadtextfile.error Then
        While Not rs.EOF
    	'Get all Data
    	dim DIM_record_number as string = rs.IdxField(1).StringValue
    	dim DIM_buzzword as string = rs.IdxField(2).StringValue
    								
        wend
    						
      end if
    				
    end if
  3. I'll try =
    FTS table?

  4. full text search

    its the only time I've used the MATCH keyword

  5. Thanks Norman - something's up and I haven't got the hang of it yet.

  6. Simon B

    19 Mar 2017 Europe (Wiltshire, UK)

    From SQLite.org:

    The MATCH operator is a special syntax for the match() application-defined function. The default match() function implementation raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.

  7. Edited 7 months ago by Gary D

    I'm seeing the database - the code below works fine. Just can't use = or Match to search the database. I've tried quite a few things. (The database is full of info already typed in. Do I really need to 'create table' ?)

    sql = "SELECT * FROM micadtable"
    rs = micadtextfile.SQLSelect(sql)
    If rs <> Nil and not micadtextfile.error Then
    While Not rs.EOF
    DIM_record_number = rs.IdxField(1).StringValue
    DIM_buzzword = rs.IdxField(2).StringValue
    'MESSAGE BOX
    MsgBox( DIM_record_number +DIM_buzzword )
    rs.MoveNext
    wend
    rs.Close
    else
    MsgBox("Error: " + micadtextfile.ErrorMessage)
    End If

  8. Simon B

    20 Mar 2017 Europe (Wiltshire, UK)
    Edited 7 months ago by Simon B

    Dave Sisemore has already answered your question.

    Your original question shows:

    sql = "SELECT buzzword FROM micadtable WHERE buzzword MATCH 'Klee';"

    The correct syntax for SQLite is:

    SELECT buzzword FROM micadtable WHERE buzzword like '%Klee%';"

    That should work.

  9. No errors, but it doesn't get to the "IdxField" bit - goes straight to wend

  10. maybe it's working then - lemme check

  11. No - it doesn't get the the "IdxField" part. Hmmm I might have to get someone to fix this. I'm probably biting off more than I can chew...

  12. Greg O

    20 Mar 2017 Xojo Inc Somewhere near Raleigh, NC

    Step once more when it goes to Wend. The debugger does this on while loops sometimes.

  13. I can step through or Run the app - same outcome.
    Not sure what you mean? Is it another line of code?

  14. Tobias B

    20 Mar 2017 Pre-Release Testers, Xojo Pro Bern, Switzerland

    this sounds like the Recordset is empty (rs.EOF is true), so there are simply no rows that match the query buzzword like '%Klee%'

  15. This:
    sql = "SELECT * FROM micadtable ORDER BY ""record number"""

    works perfectly - I can see it looping round the IdxField. Problem is using "WHERE"

    No worries - thanks all - I'll use trial and error from the Xojo examples....

  16. Deleted 7 months ago by Gary D
  17. Gary D

    20 Mar 2017 Answer London
    Edited 7 months ago by Gary D

    Jesus - it worked! (didn't need the first buzzword )

    sql = "SELECT * FROM micadtable WHERE buzzword = ""PAUL KLEE"""

    THANKS ALL, for help and support!
    (Maybe it could be that my database (from years ago) is all set up wrongly. )

  18. Dave S

    20 Mar 2017 San Diego, California USA

    hence the part of my original answer

    or just plain =

  19. = was good Dave! what didn't work for me was:
    sql = "SELECT buzzword FROM micadtable WHERE buzzword = ""PAUL KLEE"""
    just needed "SELECT * FROM...."

  20. Just a note on the syntax: SQLite accepts the mix of quotes:

    sql = "SELECT buzzword FROM micadtable WHERE buzzword = 'PAUL KLEE';"

    it's just a little more readable
    jjc_Mtl

  21. Dave S

    1 Apr 2017 San Diego, California USA

    Actually it is good practice to always use SINGLE QUOTES for literal string values
    as SOME database engines (ie. Oracle) give a special meaning to DOUBLE QUOTES, and they cannot be interchanged.

or Sign Up to reply!