Sqlite using LIKE % with SetDatabaseString

[Fig]

Hi,

I would like to use the LIKE %? with SetDatabaseString()
Is it possible ?

Code: Select all

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
      Debug DatabaseError()
   EndIf
   
   ProcedureReturn Result
EndProcedure

DatabaseFile$ = GetTemporaryDirectory()+"Database.sqlite"

If CreateFile(0, DatabaseFile$)
   CloseFile(0)
   
   If OpenDatabase(0, DatabaseFile$, "", "")
   
      CheckDatabaseUpdate(0, "CREATE TABLE food (name Text)")

      CheckDatabaseUpdate(0, "INSERT INTO food (name) VALUES ("+"'test'"+")")
      CheckDatabaseUpdate(0, "INSERT INTO food (name) VALUES ("+"'test test lol'"+")")
      CheckDatabaseUpdate(0, "INSERT INTO food (name) VALUES ("+"'amha test test lol'"+")")
      text.s="test"
      SetDatabaseString(0,0,text)
      
      If DatabaseQuery(0, "SELECT * FROM food WHERE name LIKE ? ;")
         While NextDatabaseRow(0)
            Debug "First "+GetDatabaseString(0, 0)
         Wend
         FinishDatabaseQuery(0)
      EndIf
      SetDatabaseString(0,0,text)
      If DatabaseQuery(0, "SELECT * FROM food WHERE name LIKE ? % ;")
      	While NextDatabaseRow(0)
      		Debug "Second "+GetDatabaseString(0, 0)
      	Wend
      	FinishDatabaseQuery(0)
      EndIf
      CloseDatabase(0)
   Else
      Debug "Can't open database !"
   EndIf
Else
   Debug "Can't create the database file !"
EndIf

[Starwolf20]

Sql right syntax = SELECT * FROM food WHERE name LIKE '%' ;

[kpeters58]

This can easily be done - have a look below.

Code: Select all

EnableExplicit

UseSQLiteDatabase()


Procedure.s QuotedStr(Str.s)
  ProcedureReturn "'" + ReplaceString(Str, "'", "''") + "'"
EndProcedure  

Procedure CheckDatabaseUpdate(Database, Query.s)
  Protected result = DatabaseUpdate(Database, Query.s)
  
   If result = 0
      Debug DatabaseError()
   EndIf
   
   ProcedureReturn result
EndProcedure

Define DatabaseFile.s = GetTemporaryDirectory() + "Database.sqlite"

If CreateFile(0, DatabaseFile)
   CloseFile(0)
   
   If OpenDatabase(0, DatabaseFile, "", "")
   
      CheckDatabaseUpdate(0, "CREATE TABLE food (name Text)")
      ;
      CheckDatabaseUpdate(0, "INSERT INTO food (name) VALUES (" + QuotedStr("red plum")    + ")")
      CheckDatabaseUpdate(0, "INSERT INTO food (name) VALUES (" + QuotedStr("purple plum") + ")")
      CheckDatabaseUpdate(0, "INSERT INTO food (name) VALUES (" + QuotedStr("yellow plum") + ")")
      CheckDatabaseUpdate(0, "INSERT INTO food (name) VALUES (" + QuotedStr("yellow apple") + ")")
      ;
;     Define text.s = "%rpl%"   ; = instring search
      Define text.s = "yellow%" ; = begins with search
;     Define text.s = "%le"     ; = ends in search
      SetDatabaseString(0, 0, text)
      If DatabaseQuery(0, "SELECT * FROM food WHERE name LIKE ?;")
         While NextDatabaseRow(0)
            Debug "Found: " + GetDatabaseString(0, 0)
         Wend
         FinishDatabaseQuery(0)
       EndIf
      CloseDatabase(0)
   Else
      Debug "Can't open database!"
   EndIf
Else
   Debug "Can't create the database file!"
EndIf

[mk-soft]

Small tip for create test sqlite database

Code: Select all

Define DatabaseFile.s = ":memory:"

If #True
   If OpenDatabase(0, DatabaseFile, "", "")

[Fig]

Nice tips !

Thank you all, it's clearer for me now !