The Count The Count - 4 months ago 18x
Swift Question

SELECTing from multiple tables with FMDB in Swift

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/ ")

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 {
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)

Rob Rob

Actually, when you posted this on FMDB's issues database, I dismissed your question as being a simple SQLite issue and suggested you post here. In actuality, this is an underlying issue in FMDB, that the stringForColumn 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")


  2. Use column numbers:

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