search

MechanicalRabbit / FunSQL.jl

Julia library for compositional construction of SQL queries
https://mechanicalrabbit.github.io/FunSQL.jl
Other
143 stars 5 forks source link

Empty catalog after connect to Azure SQL server using ODBC #44

Open ahjulstad opened 9 months ago

ahjulstad commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

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.