It is currently Sun May 19, 2019 9:39 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: SetDatabaseString cannot seem to work enclosed in parameters
PostPosted: Thu May 16, 2019 6:28 am 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4422
Location: Not Sydney!!! (Bad water, no goats)
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:
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"

_________________
Amateur Radio, D-STAR/VK3HAF


Top
 Profile  
Reply with quote  
 Post subject: Re: SetDatabaseString cannot seem to work enclosed in parame
PostPosted: Thu May 16, 2019 7:14 am 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4192
Location: Germany
This works:
Code:
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


Top
 Profile  
Reply with quote  
 Post subject: Re: SetDatabaseString cannot seem to work enclosed in parame
PostPosted: Thu May 16, 2019 7:39 am 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4422
Location: Not Sydney!!! (Bad water, no goats)
infratec wrote:
This works:
Code:
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.

_________________
Amateur Radio, D-STAR/VK3HAF


Top
 Profile  
Reply with quote  
 Post subject: Re: SetDatabaseString cannot seem to work enclosed in parame
PostPosted: Thu May 16, 2019 8:08 am 
Offline
Enthusiast
Enthusiast

Joined: Sat Feb 08, 2014 3:26 pm
Posts: 597
Location: France
Do not encapsulate the placeholder: ?

Don't do
Code:
... 0, "Stargate")
...
... LIKE '%?%' ...

Do
Code:
... 0, "%Stargate%")
   or
... 0, "%" + "Stargate" + "%")
...
... LIKE ? ...

_________________
(English is not my native language, I use an online translator)


Top
 Profile  
Reply with quote  
 Post subject: Re: SetDatabaseString cannot seem to work enclosed in parame
PostPosted: Thu May 16, 2019 9:26 am 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4422
Location: Not Sydney!!! (Bad water, no goats)
Marc56us wrote:
Do not encapsulate the placeholder: ?

Don't do
Code:
... 0, "Stargate")
...
... LIKE '%?%' ...

Do
Code:
... 0, "%Stargate%")
   or
... 0, "%" + "Stargate" + "%")
...
... LIKE ? ...


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

_________________
Amateur Radio, D-STAR/VK3HAF


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: Azias and 8 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye