SQlite: Qurey within Query

[LuaDev]

Hi guys

this usually is not a problem, but im forced to use PB's database lib and im not doing very well

i need to run a query while running a query, end the second query and carry on with the 1st, but PB wont let me

heres a code snippet, can anyone (A) see what im doing wrong, (B) tell me if its possible to do with PB's database lib

Code: Select all

  ; start query
  If DatabaseQuery(0, "SELECT * FROM User WHERE ID="+Str(UserID))
    If FirstDatabaseRow(0)
    
      Name.s=GetDatabaseString(0,1)
      Email.s=GetDatabaseString(0,2)
      Notes.s=GetDatabaseString(0,3)
      Ban.s=GetDatabaseString(0,4)
  
      SetGadgetText(#Text_UserName, Name)
      SetGadgetText(#Text_UserEmail, Email)
      SetGadgetText(#Text_UserNotes, Notes)
    
      FinishDatabaseQuery(0)
      
      ; find all keys for this user
      If DatabaseQuery(0, "SELECT * FROM Key WHERE UserID="+Str(UserID))
  
        While NextDatabaseRow(0) 
        
          KeyUserID.l=GetDatabaseLong(0,1)
          KeyProgramID.l=GetDatabaseLong(0,2)
          Key.s=GetDatabaseString(0,3)
          Ban.s=GetDatabaseString(0,4)   
          Program.s="ERROR"
          
          ; get the name for this programID       
          If DatabaseQuery(0, "SELECT Name FROM Program WHERE ID="+Str(KeyProgramID))
            If FirstDatabaseRow(0)
              Program = GetDatabaseString(0,2)
            EndIf
            ;FinishDatabaseQuery(0)
          Else
            Debug DatabaseError()
          EndIf
          
          Text.s=Program+Chr(10)+Key+Chr(10)+Ban
          AddGadgetItem(#ListIcon_UsersKeys,-1,Text)
          
        Wend
        
      EndIf
    EndIf
    FinishDatabaseQuery(0)
  EndIf

first, i select a user, then i want to list all keys for that user, in the key table there is a programID which i need to get the name of the program for each key, thats where its going wrong

can i only perform a single query at a time?

[Joakim Christiansen]

can i only perform a single query at a time?

That's how it currently works, yes.

How to solve?

You could save the result in a structured linked list and then do a ForEach on that list afterwards and then do the other query inside that ForEach.

Or you could open the same database two times:

Code: Select all

OpenDatabase(0...
OpenDatabase(1...
DatabaseQuery(0...
DatabaseQuery(1...

[Mahan]

I'm not sure if this applies here (since i don't know the DB layout) but if this is a single database you might be able to write just one select-statement that includes a join between the two tables. Google "sql join" for details.

If a join is possible you'll probably get much faster and better results this way than to cache one resultset in RAM to compare with another resultset later.

example (purely hypotetic) SQL:

Code: Select all

protected sql.s = "Select Program.name from User, Key, Program
Where User.ID =  Key.UserId
And Key.ProgramId = Program.ID
And User.ID = " + Str(UserID)

edit: added pseudo example

[LuaDev]

well i ended up going the linklist way, but its slow and cumbersome, i dont know if a join is possible and i can not alter the DB, the app is for an already existing database thats used by another bigger application, this just uses a small part of the info

i'll search out sql joins and see if i can get that working

Thanks for the info guys

[USCode]

I haven't tried it but I wonder if you could use 2 Open database connections? As long as both aren't doing updates/deletes you should be OK? Something like that would be ideal when you have an outer SELECT loop and you want to do UPDATEs or DELETEs inside that loop.
Then you could issue your GetDatabaseString on 2 different #Database connections?
Worth a try?

[LuaDev]

Worth a try?

after pounding my head off a wall for 2 days, i went with your idea and it works, no problems so far

[Num3]

Maybe something like this:

Code: Select all

sql.s="Select distinct USER.NAME, USER.EMAIL, USER.NOTES,USER.BAN, KEY.KEYUSERID, KEY.KEYPROGRAMID, KEY.KEY, KEY.BAN, PROGRAM.NAME from USER left join KEY on USER.ID=KEY.USERID left join PROGRAM on KEY.KEYUSER=PROGRAM.ID where USER.ID='"+Str(UserID)+"'; "

Note: I don't have the database to test it, so it's from the top of my head, and might not be 100% correct!

But the concept is there... just dig around mysql left join syntax

The above code should produce 1 line, complete with all data you need for each PROGRAM.ID.

Sure most of it is duplicated, but you'll be able to use just one query!