0

I'm trying to execute a (very) simple query on Azure MSSql Server using PHP, but it does not work and prints the follow message:

Warning: mssql_query(): message: Invalid object name 'MyTable'. (severity 16)

I believe that the underlying driver is connecting directly the master database and that's why my objects are not available. So the obvious solution could be mssql_select_db() function, but it raises the follow error message:

Warning: mssql_select_db(): message: USE statement is not supported to switch between databases. Use a new connection to connect to a different Database. (severity 16)

So, any of you guys have ever queried successfuly the MS Azure SqlServer using PHP?

Aditional info: 1 - The connection appears to be OK, with no errors. 2 - I can't qualify/prefix my objects with database.schema, otherwise Azure says:

Warning: mssql_query(): message: Reference to database and/or server name in 'myDatabase.dbo.MyTable' is not supported in this version of SQL Server. (severity 15)

The General config is: - CentOS - PHP 5.3.3 - FreeTDS - Apache 2

/etc/freetds.conf relevant part is like follows:

[global]
#TDS protocol version
; tds version = 4.2

[MyServerAtAzure]
host = mydatabase.database.windows.net
port = 1433
tds version = 8.0
database = MyDatabase
client_charset = UTF-8

The tsql output's:

# tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

And finally, the PHP code:

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

# Older FreeTDS installations need the FREETDSCONF Environment variable
putenv('FREETDSCONF=/etc/freetds.conf');
# Current release of FreeTDS uses the FREETDS environment variable. So we set both to be sure
putenv('FREETDS=/etc/freetds.conf');

$link = mssql_connect('MyServerAtAzure', 'user@mydatabase', 'password');


if ( !$link ) die('<br>Oops! CannotConnect');

//mssql_select_db('MyDatabase', $link);    # FAILS because you can't use "USE" statement

$sql = "SELECT * FROM dbo.MyTable";

$rs = mssql_query($sql, $link);
?>

The previous resources that I've already visited are: -

Community
  • 1
  • 1
Miguel Pragier
  • 135
  • 2
  • 13
  • 2
    Why are you using TDS 4.2? Isn't the current version 7 point something? Maybe that's part of the problem... – Aaron Bertrand Aug 26 '13 at 21:34
  • links for reference http://www.idevelopment.info/data/SQLServer/DBA_tips/Programming/PROG_6.shtml#Install FreeTDS http://stackoverflow.com/questions/10889294/what-is-tds-protocol-version-8-0-and-why-should-i-use-it – swasheck Aug 26 '13 at 21:43
  • Thank you again, Aaron. Now I'll try to upgrade freeTds without break anything else. :-). Working or not, I'll let you know. – Miguel Pragier Sep 05 '13 at 21:44

1 Answers1

0

You can't perform cross-database queries and, like the error message says, you also can't change database context using USE. If you want to query from multiple Azure databases, you need to connect to them independently with different connection strings.

Also, did you try specifying the database explicitly (and not connecting to [...].wondows.net:

[MyServerAtAzure]
host = mydatabase.database.windows.net
port = 1433
Database = myDatabase
tds version = 8.0
client_charset = UTF-8

And also properly prefixing your table with its schema?

$sql = "SELECT * FROM dbo.MyTable;";
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Hello, @Aaron. Is not my intention to query cross-database. The case is that freeTDS ( as I believe ) is connecting directly to [master] database, and that is not the database I want to work. I want to connect and query [MyDatabase] instead [master]. I've tried your good sugestion of including "database" between settings and prefix the query objects with dbo., but the result is still tha same. – Miguel Pragier Aug 26 '13 at 21:22
  • Have you verified that it is connecting to master? `SELECT DB_NAME();` – Aaron Bertrand Aug 26 '13 at 21:26
  • @Miguel also see [this MSDN thread](http://social.msdn.microsoft.com/Forums/windowsazure/en-US/8e27a1d7-8a70-4084-9c19-66ed2b6a407f/cannot-connect-with-freetds) which suggests you need to tell ODBC what database to connect to. – Aaron Bertrand Aug 26 '13 at 21:29
  • Yes, I did. "master". – Miguel Pragier Aug 26 '13 at 21:30
  • So, for example, see the recommendation for the ODBC.ini file here: https://code.google.com/p/django-pyodbc/wiki/FreeTDS – Aaron Bertrand Aug 26 '13 at 21:31
  • I made the same odbc.ini that they're sugesting, but without news. In that same post, @jbondc says: _italic_(You will need to use odbc to connect to a specific database, mssql_select_db () does not currently work with Azure.)_italic_. But the post is from 2009, and I hope there's some solution for it. :-) – Miguel Pragier Aug 26 '13 at 21:56
  • I don't know much about freetds. I believe that there is some way to config its default database ( the one I want to use ), or set Azure to deny [master] as default database. BTW, thank you very much for your effort. – Miguel Pragier Aug 26 '13 at 22:00