1. 5 weeks ago

    Gary D

    Mar 18 London
    Edited 5 weeks ago by Gary D

    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. Norman P

    Mar 18 Xojo Inc

    what kind of database is this querying ?

  3. Gary D

    Mar 18 London
    Edited 5 weeks ago by Gary D

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

  4. Dave S

    Mar 18 San Diego, California USA
    Edited 5 weeks ago by Dave S

    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. Norman P

    Mar 18 Xojo Inc
    Edited 5 weeks 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
  6. Gary D

    Mar 18 London

    I'll try =
    FTS table?

  7. Norman P

    Mar 18 Xojo Inc

    full text search

    its the only time I've used the MATCH keyword

  8. Gary D

    Mar 18 London

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

  9. Simon B

    Mar 19 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. Gary D

    Mar 20 London
    Edited 5 weeks 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

  11. Simon B

    Mar 20 Europe (Wiltshire, UK)
    Edited 5 weeks 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.

  12. Gary D

    Mar 20 London

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

  13. Gary D

    Mar 20 London

    maybe it's working then - lemme check

  14. Gary D

    Mar 20 London

    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

    Mar 20 Xojo Inc Somewhere near Raleigh, NC

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

  16. Gary D

    Mar 20 London

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

  17. Tobias B

    Mar 20 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. Gary D

    Mar 20 London

    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. Deleted 5 weeks ago by Gary D
  20. Gary D

    Mar 20 Answer London
    Edited 5 weeks 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. )

  21. Newer ›

or Sign Up to reply!