dbuse() doesn't work with Azure

[GeoffMontee]

When working with Azure, dbuse() results in messages like the following:

NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'master'., Server: ms, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: ms, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 40508, Msg state: 1, Msg: USE statement is not supported to switch between databases. Use a new connection to connect to a different database., Server: ms, Process: , Line: 1, Level: 16
ERROR:  DB-Library error: DB #: 40508, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: (null), Level: 16

dbuse() calls USE behind the scenes. This is not supported with Azure:

http://stackoverflow.com/a/14167073/1732980

Is there a way to supply the initial database name to dbopen()? If so, a potential fix would be to add an option to specify whether to use dbopen() or dbuse() to connect to a specific database.

[filiprem]

I have observed this bug while connecting to Azure SQL .

• tds_fdw v 1.0.1,
• freetds v0.91,
• PostgreSQL v. 9.3.5

I can connect to MyApp database from command line with tsql:

tsql -H gsnza2xxxx.database.windows.net -U 'uuuu@gsnza2xxxx' -D MyApp -P 'xxxx' -p 1433

But on SQL level, with TDS FDW I always get connected to "master" database.

I cannot modify default login database in Azure.

Is there a way to force an equivalent of -D MyApp on tds_fdw?

Should it be possible to specify database name on CREATE SERVER stage, not on CREATE FOREIGN TABLE stage?

Is it much work to do? Maybe it would be possible to add database option in tdsSetupConnection?

[GeoffMontee]

I have started a discussion with the FreeTDS developers about this problem:

http://lists.ibiblio.org/pipermail/freetds/2015q2/029353.html

[GeoffMontee]

The FreeTDS developer said that DBSETLDBNAME() can be used to add the connecting database to the LOGINREC prior to calling dbopen(). This macro/function appears to be undocumented in the current FreeTDS Doxygen reference manual, but I see that the macro at least exists in sybdb.h included in the version of FreeTDS in CentOS 7.

I'm thinking that I should make the DBSETLDBNAME() behavior the default. Maybe the dbuse() behavior could be turned on via a foreign server or table option.

[GeoffMontee]

Hi @filiprem,

Can you please try the latest commit? I've added database and dbuse options for foreign servers. To support Azure, dbuse has to be set to 0 (the default).

Commit: c534888

e.g. with this commit, you can provide more options to CREATE SERVER:

CREATE SERVER mssql_svr
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername '192.168.1.101', port '1433', 
    language 'us_english', character_set 'UTF-8', 
    database 'tds_fdw_test', tds_version '7.1');

[GeoffMontee]

Direct links to archives of the commit:

• .zip
• .tar.gz

[GeoffMontee]

Fixed in version 1.0.2.

[filiprem]

Thank you Geoff! This works fine now.