MSSQL - Can read but not write

Crave

Hi,

I'm having a very strange problem. I am currently running PHP v4.3.10 on Windows 2000 SP4, SQL 2000 SP3. I just upgraded PHP from 4.3.4 to 4.3.10. Everything was working just fine prior to this. Now, I can read no problem from my MSSQL database, but when I try to update a record, it doesn't work.

Here is my connection info:

$connection = mssql_connect("servername", "username", "password") or die ("Couldn't connect to the database");
$db_name = "dbname";
$db = @mssql_select_db($db_name, $connection) or die ("db2 connection no work");

The code for updating the DB is:

$sql = "update tso_tools set name='Tool 3', description='Tool number three', state_id=2, date_made='Jan 10 2005', mat_id=1, created_by=233, airplane_id=5 where tool_id=7";
mssql_query($sql, $connection) or die ("SQL: $sql<br> Couldn't update database with tool information - " . mssql_get_last_message());

When it executes, this is the output on the screen:
Couldn't update database with tool information - Changed database context to 'dbname'.

However, if execute the following, everyting works properly:

$sql = "select * from tso_tools";
$result = mssql_query($sql, $connection);
while ($row = mssql_fetch_array($result)) {
     echo $row['tool_id'] . "<br>";
}

I have looked and looked but have not been able to find any solution to this. Any and all help is greatly appreciated.

Thanks!

Craig

jerdo

I noticed that you don't have

$result =

in the code you are having trouble with, that could be part of the issue.

Crave

I have never used

$result=

when adding something to a database. How would you put that to use when updating a database? I don't see how there would be a result from the query. I'm a novice, so maybe I'm missing something, but the code worked prior to the upgrade to PHP 4.3.10.

Also, I checked the php.ini files, and they are the same.

jerdo

In the code you said works you have

$result = mssql_query($sql, $connection);

Where as in the code you said doesn't work you have

mssql_query($sql, $connection) or die ("SQL: $sql<br> Couldn't update database with tool information - " . mssql_get_last_message());

Try putting "$result =" equal in front of the mssql_query line in the code that isn't working see if that helps.

Roger_Ramjet

mssql_query still returns a result for action queries ie success or failure. Generally, one would want to know this and deal with it accordingly.

Crave

Ok, I changed the code as follows:

$result = mssql_query($sql, $connection); 
echo "result: $result<br>";
echo "SQL Error: " . mssql_get_last_message() . "<br>";

and this is the result I get:

result:
SQL Error: Changed database context to 'dbname'.

So adding the result= made no difference. Also, if I copy and paste the SQL query into MS Enterprise manager, it executes properly updating the table.

Roger_Ramjet

So at least you are getting an error message now. That's an improvement.

Now, what could that error message possibly mean?

Crave

Actually, it's the same error message I received before by using the "or die" and mssql_get_last_message(). As far as I understand, it's not really an error message, but just a message in general. Actually, if I arrange it like this:

echo "SQL Error: " . mssql_get_last_message() . "<br>";
$result = mssql_query($sql, $connection);
echo "result: $result<br>";
echo "SQL Error: " . mssql_get_last_message() . "<br>";
exit;

I get the same message before and after mssql_query.

Actually, when trying it, I just noticed something. The query actually does execute properly and update the DB, yet if I use "or die" after the query it does die even though it shouldn't.

Stupid computers. Now I'm gonna go read up on "or die";

tsinka

Hi,

that seems to be an issue with PHP 4.3.10:

http://www.manucorp.com/archives/php-bugs/200501/msg00574.php

Thomas

Crave

Cool, I will try that and post back with the results.