SELECTing from multiple tables with FMDB in Swift

Question

I'm looking for a way to print out four specific colums from 7 different tables with FMDB in Swift. I've tried selecting them with dot notation, but this doesn't seem to work.

The DB is looking like this:

• Artist: [ArtistId], [Name]
• Track: [TrackId], [AlbumId], [Name]
• Album: [AlbumId], [Name], [ArtistId]
• Genre: [GenreId], [Name]
• Invoice: [CustomerId], [InvoiceId]
• InvoiceLine: [InvoiceId]
• Customer: [CustomerId], [FirstName], [LastName], [City]

.

import Foundation

let database = FMDatabase(path: " /path to database.sqlite/ ")

database.open()

let rs = try database.executeQuery("SELECT Artist.Name, Track.Name, Album.Title, Genre.Name 
                                    FROM Invoice, Customer, InvoiceLine, Track, Album, Artist, Genre
                                    WHERE Customer.FirstName = 'Michelle'
                                    AND Customer.LastName = 'Brooks' 
                                    AND Customer.City = 'New York'
                                    AND Invoice.CustomerId=Customer.CustomerId 
                                    AND InvoiceLine.InvoiceId = Invoice.InvoiceId 
                                    AND Track.TrackId = InvoiceLine.TrackId 
                                    AND Album.AlbumId = Track.AlbumId 
                                    AND Artist.ArtistId = Album.ArtistId
                                    AND Genre.GenreId = Track.GenreId ", values: nil)

while rs.next() {
    let artistName = rs.stringForColumn("Artist.Name")
    let trackName = rs.stringForColumn("Track.Name")
    let albumTitle = rs.stringForColumn("Album.Title")
    let genre = rs.stringForColumn("Genre.Name")

print (artistName + " " + trackName + " " + albumTitle + " " + genre)
}

Answer 1

The issue is that the stringForColumn (which uses sqlite_column_name) function is using the name of the column of the underlying table and disregards the name of the table. Worse, if you have two columns of the same name, it has no way to know from which table you wanted to get results.

So, there are two work-arounds:

1. You can give the columns unique names:

   let rs = try database.executeQuery("SELECT Artist.Name AS ArtistName, Track.Name AS TrackName, Album.Title, Genre.Name AS Genre.Name ..."
   

   and then

   let artistName = rs.stringForColumn("ArtistName")
   let trackName = rs.stringForColumn("TrackName")
   let albumTitle = rs.stringForColumn("Title")
   let genre = rs.stringForColumn("GenreName")
   

   or

2. Use column numbers:

   let artistName = rs.stringForColumnIndex(0)
   let trackName = rs.stringForColumnIndex(1)
   let albumTitle = rs.stringForColumnIndex(2)
   let genre = rs.stringForColumnIndex(3)