Page 1 of 1
SetDatabaseString cannot seem to work enclosed in parameters
Posted: Thu May 16, 2019 6:28 am
by 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"
Re: SetDatabaseString cannot seem to work enclosed in parame
Posted: Thu May 16, 2019 7:14 am
by 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
Re: SetDatabaseString cannot seem to work enclosed in parame
Posted: Thu May 16, 2019 7:39 am
by Fangbeast
infratec wrote: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.
Re: SetDatabaseString cannot seem to work enclosed in parame
Posted: Thu May 16, 2019 8:08 am
by 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 ? ...
Re: SetDatabaseString cannot seem to work enclosed in parame
Posted: Thu May 16, 2019 9:26 am
by Fangbeast
Marc56us wrote: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.