DatabaseQuery

[ClueLess]

I need to retrive data from two tables in a single database using this query:

Code: Select all

"SELECT Clients.*, History.ClientName FROM Clients, History WHERE History.ClientID = Clients.ClientID"

How do I retrive the data?

ThankYou

[srod]

Take a look at the "database.db" demo in the PB help manual.

[ClueLess]

Code: Select all

;
; ------------------------------------------------------------
;
;   PureBasic - Database example file
;
;    (c) Fantaisie Software
;
; ------------------------------------------------------------
;

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 CHAR(50), weight INT)")

      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('apple', '10')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('pear', '5')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banana', '20')")
      
      If DatabaseQuery(0, "SELECT * FROM food WHERE weight > 7")
      
         While NextDatabaseRow(0)
            Debug 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

This is the code from the example in the help. don't see a query to multiple columns....

[srod]

Look closer...

Code: Select all

      If DatabaseQuery(0, "SELECT * FROM food WHERE weight > 7")
      
         While NextDatabaseRow(0)
            Debug GetDatabaseString(0, 0)
         Wend
      
         FinishDatabaseQuery(0)
      EndIf

Okay this is displaying one column only, but multiple columns have been retrieved. Take a good look at GetDatabaseString(), GetDatabaseLong() in the manual.

[ClueLess]

This is working with one table only. I need to get data from two TABLES, not COLUMNS using only one query. Look at the query sintax

The manual was the first place I looked before I post... And the desciption osf the GetDatabaseString() does not say much.

[srod]

The query syntax makes no difference whatsoever. Everything you need is in that demo code, as I have already stated. Issue a select query spanning 58 tables and... nothing changes. You access the data retrieved in exactly the same way...

Code: Select all

SQL$ = "SELECT Clients.*, History.ClientName FROM Clients, History WHERE History.ClientID = Clients.ClientID"

If OpenDatabase(0, DatabaseFile$, "", "")
  If DatabaseQuery(0, SQL$)
    While NextDatabaseRow(0)
      Debug GetDatabaseString(0, 0)
    Wend
    FinishDatabaseQuery(0)
  EndIf
  CloseDatabase(0)
Else
  Debug "Can't open database !"
EndIf

Once you have used DatabaseQuery() successfully, you iterate through the retrieved 'recordset' by using NextDatabaseRow() regardless of how many fields you have retrieved and how complex the SQL statement used and regardless of how many tables the data is taken from.

Once you have your recordset, GetDatabaseString(0, 0) will then retrieve the first field from the current record. GetDatabaseString(0, 1) retrieves the second field, GetDatabaseString(0, 2) retrieves the third field... Switch GetDatabaseString() for GetDatabaseLong() and so on as appropriate. That is it. GetDatabaseString(0, 0) and GetDatabaseString(0, 1) may well be taking data from different tables depending upon your SQL string etc.

Now before you repeat yourself about multiple tables, I will simply add that this all works fine, it is tried and tested and I use it just about every day. At the moment I am using similar code to pull data from 5 tables in an SQLite database and I am having no problems.

[jamba]

so you don't know the order of the columns?

use databasecolumnname() to get the list of columns, and use that to get the index for the value you want to retrieve

would that work for what you are trying to do?


just saw srod's post...I think he nailed it

[ClueLess]

@ srod
You mean the column in the GetDatabaseString ( #Database , Column) means the column in the result and not in the table?????

If so, your posts make sense

Thanks

[srod]

@ srod
You mean the column in the GetDatabaseString ( #Database , Column) means the column in the result and not in the table?????

If so, your posts make sense

Thanks

Yes absolutely. When the help manual talks about 'database columns' it is typically referring to 'recordset' columns (as opposed to table columns) and of course a recordset may represent a single table or a bunch of records resulting from multiple joins across multiple tables etc.

[ClueLess]

It WORKS!!!!!!