Empty catalog after connect to Azure SQL server using ODBC

[ahjulstad]

I can connect to my server using

conn = DBInterface.connect(FunSQL.DB{ODBC.Connection}, 
    "Driver={ODBC Driver 18 for SQL Server};server=xxxx.database.windows.net;database=xxxx;UID=xxxx;Authentication=ActiveDirectoryInteractive;Encrypt=yes;", 
    dialect = :sqlserver)

, and can query the database OK with handwritten sql.

However, the FunSQL catalog object is not populated with any tables; so From(:xxsymbol) just throws a FunSQL.ReferenceError

All the table names in my database have a dot in them, I wonder if that has any impact?

[xitology]

Do the tables belong to the schema other than dbo? The schema is the component of the table name before the .. The default schema in MS SQL Server is called dbo, but if you use a fully qualified table name, it is likely that you use a non-default schema. If so, you need to specify the schema explicitly by passing it as a parameter to DBInterface.connect.

[ahjulstad]

Thank you for a quick response. I did not realise they were separate schemas, and not just part of the table name. When specifying schema it works as expected. However, if I specify schema when establishing the connection, how would I then do join across schemas?

[xitology]

You can use FunSQL with tables across several schemas, however it will require some extra work. You will need to manually introspect the structure of the database to build a SQLCatalog instance.

To generate correct SQL, FunSQL needs to know available SQL tables and their columns. This information is stored in a SQLCatalog instance. If you are only interested in tables in a single schema, FunSQL can build the catalog automatically, using the reflect method (which is invoked from DBInterface.connect). But if you need to use tables across different schemas, you must generate the catalog yourself, following the implementation of the reflect method. One challenge is when different schemas have tables with the same name. Because FunSQL itself addresses tables using single non-hierarchical names, you will have to change the FunSQL name of those tables to make them unambiguous.