SQL

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.

what kind of database is this querying ?

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

I think you want LIKE not MATCH

or just plain =

should return ALL the rows, not just the “first” one

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

I’ll try =
FTS table?

full text search

its the only time I’ve used the MATCH keyword

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

From SQLite.org:

[quote]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.
[/quote]

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

Dave S has already answered your question.

Your original question shows:

The correct syntax for SQLite is:

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

That should work.

No errors, but it doesn’t get to the “IdxField” bit - goes straight to wend

maybe it’s working then - lemme check

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…

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

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

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

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…

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

hence the part of my original answer