SetDatabaseString cannot seem to work enclosed in parameters

[Fangbeast]

If I use the SetDatabaseString as I have been to enter strings (This time for a search), then I cannot seem to use it inside percentage symbols later. It generates no errors but returns no data either?

Code: Select all

SetDatabaseString(Program\DatabaseHandle, 0, "Stargate")

DatabaseQuery(DatabaseHandle.i,  "SELECT title, recordid FROM db_movies WHERE title LIKE '%?%' ORDER BY title ASC")

Do any of you database fiends know how to get around this? I know there is data and it is being returned by:

"SELECT title, recordid FROM db_movies ORDER BY title ASC"

[infratec]

This works:

Code: Select all

UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)

If DB
  
  DatabaseUpdate(DB, "Create Table db_movies (title text, recordid integer)")
  
  For Counter = 1 To 5
    DatabaseUpdate(DB, "Insert Into db_movies Values ('Hello World '," + Str(counter) + ")")
  Next
  
  For Counter = 6 To 10
    DatabaseUpdate(DB, "Insert Into db_movies Values (' Stargate " + Str(counter) + "'," + Str(counter) + ")")
  Next
  
  For Counter = 11 To 15
    DatabaseUpdate(DB, "Insert Into db_movies Values ('Hello World '," + Str(counter) + ")")
  Next
  
  
  SetDatabaseString(DB, 0, "%Stargate%")
  
  If DatabaseQuery(DB,  "SELECT title, recordid FROM db_movies WHERE title LIKE ? ORDER BY title ASC")
    
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0)
    Wend
    
    FinishDatabaseQuery(DB)
  Else
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(DB)
  
EndIf

Bernd

[Fangbeast]

This works:

Code: Select all

UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)

If DB
  
  DatabaseUpdate(DB, "Create Table db_movies (title text, recordid integer)")
  
  For Counter = 1 To 5
    DatabaseUpdate(DB, "Insert Into db_movies Values ('Hello World '," + Str(counter) + ")")
  Next
  
  For Counter = 6 To 10
    DatabaseUpdate(DB, "Insert Into db_movies Values (' Stargate " + Str(counter) + "'," + Str(counter) + ")")
  Next
  
  For Counter = 11 To 15
    DatabaseUpdate(DB, "Insert Into db_movies Values ('Hello World '," + Str(counter) + ")")
  Next
  
  
  SetDatabaseString(DB, 0, "%Stargate%")
  
  If DatabaseQuery(DB,  "SELECT title, recordid FROM db_movies WHERE title LIKE ? ORDER BY title ASC")
    
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0)
    Wend
    
    FinishDatabaseQuery(DB)
  Else
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(DB)
  
EndIf

Bernd

Oh, I see/ Thanks Bernd, didn't know how it worked with prepared statements that way and didn't know how to test it with my sqlite tool.

I have been working on a standalone version of my movie database before I integrate that into my main program (11 database and counting, what a job!)

I will release this steaming pile of hamsters when most of the bits are working.

[Marc56us]

Do not encapsulate the placeholder: ?

Don't do

Code: Select all

... 0, "Stargate")
...
... LIKE '%?%' ...

Do

Code: Select all

... 0, "%Stargate%")
   or 
... 0, "%" + "Stargate" + "%")
...
... LIKE ? ...

[Fangbeast]

Do not encapsulate the placeholder: ?

Don't do

Code: Select all

... 0, "Stargate")
...
... LIKE '%?%' ...

Do

Code: Select all

... 0, "%Stargate%")
   or 
... 0, "%" + "Stargate" + "%")
...
... LIKE ? ...

Bernd knows what he is doing and it works. tested.