如何在Azure上的特定数据库中查询? [英] How to query in a specific database on Azure?

问题描述

我正在尝试使用PHP在Azure MSSql Server上执行一个(非常)简单的查询，但是它不起作用并显示以下消息:

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:

警告:mssql_query():消息:无效的对象名称'MyTable'. (严重性16)

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

我认为底层驱动程序直接连接了master数据库，这就是为什么我的对象不可用的原因.因此，显而易见的解决方案可能是 mssql_select_db()函数，但会引发以下错误消息:

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:

警告:mssql_select_db():消息:不支持USE语句 在数据库之间切换.使用新连接连接到 不同的数据库. (严重性16)

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)

那么，你们中的任何人曾经使用PHP成功查询过MS Azure SqlServer吗?

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

其他信息: 1-连接似乎正常，没有错误. 2-我无法使用database.schema限定/前缀我的对象，否则Azure会说:

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:

警告:mssql_query():消息:对数据库和/或服务器的引用 此版本的版本不支持"myDatabase.dbo.MyTable"中的名称 SQL Server. (严重性15)

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)

常规配置为: -CentOS的 -PHP 5.3.3 -FreeTDS -Apache 2

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

/etc/freetds.conf相关部分如下:

[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

tsql输出:

# 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

最后是PHP代码:

<?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);
?>

我以前访问过的资源是: -

• why-is-my-sql-server-query-failing
• use-statement-is运行查询时不支持在数据库之间切换
• PHP怪胎

• why-is-my-sql-server-query-failing
• use-statement-is-not-supported-to-switch-between-databases-when-running-query
• PHPFreaks

推荐答案

您无法执行跨数据库查询，并且如错误消息所述，您也无法使用USE更改数据库上下文.如果要从多个Azure数据库查询，则需要使用不同的连接字符串独立地连接到它们.

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.

此外，您是否尝试过显式指定数据库(并且未连接到[...].wondows.net:

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;";

这篇关于如何在Azure上的特定数据库中查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！