iOSSQLiteRecordset and duplicate field names

Targets iOS
1

I have an iOSSQLiteRecordset which is the result of a LEFT JOIN of two tables. The tables have a field with a common name (i.e. the “Reference” field exists in both tables). Usually in SQL you use TableName.FieldName to refer to fields with the same name as the result of a join however the iOSSQLiteRecordset seems to return two fields with the same name (i.e. I have to fields called “Reference” being returned) and I can’t work out how to distinguish between them other than by their ordinal position which seems like asking for trouble down the track if the tables ever change.

2

Don’t know if it’s a bug in Xojo or not but you could try using an SQL alias.
That will temporarily rename the column that is returned from your select.
http://www.tutorialspoint.com/sqlite/sqlite_alias_syntax.htm

Maybe that could work?

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
3

Thanks. That could work. However to move past it I just renamed the field. But I think somebody will need a solution for this at some point.

4

There is no difference between iOSSQLiteRecordset and SQLiteRecordset in that regard AFAIK. Where does this problem arise, meaning where are you prohibited to use TableName.FieldName?

Maybe when reading the values from the RecordSet? But it has never worked that way:

Dim valueB As String = rst.Field("TableB.Column2").StringValue   // Won't work in record sets at all

The solution is to rename the column in the select statement:

SELECT table1.column13, table1.reference AS ref1, table2.column21, table2.reference As ref2 FROM ...
5

Ahh. Ok, makes sense.

6