1. last year
    Edited last year

    Hi
    I'm looking up a database. I put this code in a Method. I'm getting "unable to use function MATCH in the requested context"
    (But it works fine without MATCH, for example this works: sql = "SELECT * FROM micadtable" and it shows the first row fine.)

    sql = "SELECT buzzword FROM micadtable WHERE buzzword MATCH 'Klee';"
    rs = micadtextfile.SQLSelect(sql)
    If rs <> Nil and not micadtextfile.error Then
    While Not rs.EOF
    'Get all Data
    DIM_record_number = rs.IdxField(1).StringValue
    DIM_buzzword = rs.IdxField(2).StringValue
    etc. etc.

    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. )

  2. what kind of database is this querying ?

  3. Edited last year

    sqlite. It connects and works fine. Have a problem with using MATCH in the sql

  4. Dave S

    18 Mar 2017 San Diego, California USA
    Edited last year

    I think you want LIKE not MATCH

    or just plain =

    @Gary D "SELECT * FROM micadtable" and it shows the first row fine.)

    should return ALL the rows, not just the "first" one

  5. Edited last year

    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
  6. I'll try =
    FTS table?

  7. full text search

    its the only time I've used the MATCH keyword

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

  9. 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.

  10. Edited last year

    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

  11. Simon B

    20 Mar 2017 Europe (Wiltshire, UK)
    Edited last year

    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.

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

  13. maybe it's working then - lemme check

  14. 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...

  15. 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.

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

  17. 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%'

  18. 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....

  19. Gary D

    20 Mar 2017 Answer London
    Edited last year

    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. )

  20. Newer ›

or Sign Up to reply!