Edit report at https://bugs.php.net/bug.php?id=24522&edit=1
ID: 24522
Comment by: cjrogala at gmail dot com
Reported by: cdcr440 at hotmail dot com
Summary: MSSQL: "Changed database context to" error when
running query
Status: Bogus
Type: Bug
Package: MSSQL related
Operating System: WinNT
PHP Version: 4.3.1
Block user comment: N
Private report: N
New Comment:
You may want to reopen this issue. I am inserting data into MS SQL Server 2008r2 and using PHP 5.2;
and the same error occurs. The positive is that the error did not prevent the data from being
inserted into the database, but it did display the same error message. I did not attempt to change
the timeout since this is a much later version. I also noticed this issue in a few different
location on the web. I am logging into the database as the sa and like I said, the data is making
into the database; but the error still appears when validating the result of the query. Unlike the
previous post, I will not provide a credentials, but I know it's not a connection error since
the data is getting into the database. Here is my code:
$tsql = "USE littleliam
INSERT INTO [littleliam].[dbo].[tbl_blogPosts]
([postText]
,[created]
,[createdBy]
,[approved])
VALUES ('" .$postText ."', GETDATE(), " .$author .", "
.$approvalStatus .")";
//Used to validate the query by running it in SQL Server Management studios
echo $tsql;
//Prepare and execute the statement.
mssql_select_db('littleliam');
$insertReview = mssql_query($tsql, $msServerLink);
if (!$res) {
print("SQL statement failed with error:\n");
print(" ".mssql_get_last_message()."\n");
} else {
print("One data row inserted.\n");
}
mssql_close($msServerLink);
My connection script is:
$msServerLink = mssql_connect($db_server, $db_user, $db_pass);
The only thing I could think of is selecting the database in the connect function. Can a database
be selected in the mssql_connect function?
Previous Comments:
------------------------------------------------------------------------
[2003-07-13 10:48:25] sniper@php.net
Temporarily closing. :)
------------------------------------------------------------------------
[2003-07-10 04:03:09] cdcr440 at hotmail dot com
Thank you very much for that. Unfortunately, I can't check that this fixes the problem because
it's gone today and I didn't manage to find a query that fails. As I said it's
unpredictable and appears/disappears regularly. I've increased the timeout and I'll see if
the problem comes back again.
Sorry to have wasted your time, I think you can temporarily close this bug report.
------------------------------------------------------------------------
[2003-07-09 17:19:58] fmk@php.net
This sounds like a timeout porblem.
You can use two php.ini settings to control the timeouts.
mssql.connect_timeout = 5
mssql.timeout = 60
These are default values in seconds. try to increase the second timeout value. (I'll make sure
thes values makes it into the distributed versions of php.ini).
------------------------------------------------------------------------
[2003-07-09 07:52:15] cdcr440 at hotmail dot com
Thanks for looking into this.
The access rights should be sufficient because I tried several users including the sa user and the
problem occured for all.
The database name doesn't include white spaces.
mssql.min_message_severity = 10 in my php.ini as I had attached but still see the message you say I
should only see with mssql.min_message_severity = 0, maybe that's related to the problem. Could
it be the result of the MSSQL module getting confused?
Even though "Changed database context to 'XXX'" isn't an error message, it
appears when the query I try to run fails and the subsequent database selections fail with the same
error.
Here is the shortest example that produces the error, sorry for the biggish query, this one is one
that I know generates an error. It connects with the sa user that can access everything without
restrictions:
<?
$config["sqlserver"]="192.168.101.3";
$config["sqluser"]="sa";
$config["sqlpassword"]="x";
$config["sqldatabase"]="XXX";
$theQuery="select top 15 ni.* from NewsItem as ni, NewsItemStatus as nis, NewsStatus as ns
where nis.NewsItemID=ni.ID
and ns.ID=nis.NewsStatusID
and lower(ns.Status) = lower('live') and (ni.ID in (select ni1.ID from NewsItem ni1
JOIN NewsItemNewsTagTable as nintt1 ON nintt1.NewsItemID=ni1.ID where ni1.[Date] >=
'09-Jun-2003' and nintt1.[Date] >= '09-Jun-2003' and ni1.[Date] <=
'09-Jul-2003' and nintt1.[Date] <= '09-Jul-2003' and ( (lower(cast (Text as
varchar(8000))) like '%chuan%' )or (lower(cast (BassoeComment as varchar(8000))) like
'%chuan%' )or (lower(cast (FirstParagraph as varchar(8000))) like '%chuan%' )or
(lower(cast (Title as varchar(8000))) like '%chuan%' )or (lower(cast (nintt1.TagsCache as
varchar(8000))) like '%chuan%' ))) and ni.ID in (select ni1.ID from NewsItem ni1 JOIN
NewsItemNewsTagTable as nintt1 ON nintt1.NewsItemID=ni1.ID where ni1.[Date] >=
'09-Jun-2003' and nintt1.[Date] >= '09-Jun-2003' and ni1.[Date] <=
'09-Jul-2003' and nintt1.[Date] <= '09-Jul-2003' and ( (lower(cast (Text as
varchar(8000))) like '%hup%' )or (lower(cast (BassoeComment as varchar(8000))) like
'%hup%' )or (lower(cast (FirstParagraph as varchar(8000))) like '%hup%' )or
(lower(cast (Title as varchar(8000))) like '%hup%' )or (lower(cast (nintt1.TagsCache as
varchar(8000))) like '%hup%' )))) and ( ni.NewsTypeID=4 ) and ni.[Date] >=
'09-Jun-2003' and ni.[Date] <= '09-Jul-2003' order by ni.[Date] desc,
ni.[Timestamp] desc ";
print("Connecting to DB server... ");
$dbID = MSSQL_CONNECT($config["sqlserver"], $config["sqluser"],
$config["sqlpassword"]);
if (!$dbID) {
print("Error connecting to DB server.<br>\n");
} else {
print("done.<br>\n");
}
print("Selecting database... ");
if (!mssql_select_db($config["sqldatabase"], $dbID)) {
print("Error selecting database.<br>\n");
} else {
print("done.<br>\n");
}
print("Running query... ");
$theResults = mssql_query($theQuery, $dbID);
if (!$theResults) {
print("Unable to run query:\n");
print(mssql_get_last_message()."<br>\n");
} else {
print("done.<br>\n");
}
?>
The output is as follows:
Connecting to DB server... done.
Selecting database... done.
Running query... Unable to run query: Changed database context to 'XXX'.
However, the query runs fine query analyzer with the same user (sa!), although slowly, so we can
rule out a purely SQL problem.
However, if the query is reduced a bit, it doesn't fail:
select top 15 ni.* from NewsItem as ni, NewsItemStatus as nis, NewsStatus as ns
where nis.NewsItemID=ni.ID
and ns.ID=nis.NewsStatusID
and lower(ns.Status) = lower('live') and (ni.ID in (select ni1.ID from NewsItem ni1
JOIN NewsItemNewsTagTable as nintt1 ON nintt1.NewsItemID=ni1.ID where ni1.[Date] >=
'09-Jun-2003' and nintt1.[Date] >= '09-Jun-2003' and ni1.[Date] <=
'09-Jul-2003' and nintt1.[Date] <= '09-Jul-2003' and ( (lower(cast (Text as
varchar(8000))) like '%chuan%' )or (lower(cast (BassoeComment as varchar(8000))) like
'%chuan%' )or (lower(cast (FirstParagraph as varchar(8000))) like '%chuan%' )or
(lower(cast (Title as varchar(8000))) like '%chuan%' )or (lower(cast (nintt1.TagsCache as
varchar(8000))) like '%chuan%' )))) and ( ni.NewsTypeID=4 ) and ni.[Date] >=
'09-Jun-2003' and ni.[Date] <= '09-Jul-2003' order by ni.[Date] desc,
ni.[Timestamp] desc
So although it looks like a query problem, I remind you that both queries run fine through both
query analyzer and the enterprise manager. Could it be a kind of time out problem since the second
query (that succeeds) runs faster? I don't see timeout settings in the MSSQL configuration in
php.ini. The script itself doesn't timeout and continues executing after the error.
The log I attached the other day was also showing that subsequent attempts to select the database to
run another query would fail.
Does this help?
------------------------------------------------------------------------
[2003-07-07 16:14:38] fmk@php.net
Please provide a full script that reproduces the problem. The log file indicates that the
mssql_select_db() failed. This could be caused by insufficient access or a bad database name. If the
database name includes white space it should be included in [].
"Changed database context to 'XXX'". is not an error but a message. You should
only see this message if mssql.min_message_severity = 0.
mssql.min_message_severity can be specified in php.ini or at runtime with the ini_set() command.
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=24522
--
Edit this bug report at https://bugs.php.net/bug.php?id=24522&edit=1