Can't Insert data to SQL 2005 from PHP web page

Question

Server 2003 Running SQL 2005. PHP 5.3 installed. Using SQLSVR_53_nts_vc9.

I can connect and retrieve records from SQL via my PHP web pages, so I know my connection string is working, however when I try to insert data from my PHP web forms I get a HTTP 500 error. Is the SQL server not configured with the correct permissions or some other driver configuration problem?

Answer 1

PHP is probably crashing during a function call, which is usually an error in an extension. 

Is there any insert statement that works or all insert statements cause error 500?

If you are certain it's happening during a call to the driver (a sqlsrv_... call) , could you let us see the SQL statement that's causing a problem, and the PHP code that's calling it?

Also, it's worth checking the PHP error log for clues - although it's common that no errors will be logged before the crash, there's still a possibility.

---

Rob

Answer 2

Hi Rob

Yes all insert statements seem to have the same effect. I even went down to as simple as copying the 'adventureworks' basic insert statement and same thing. That PHP was as follows:

<?php

$serverName = "Server\SQLExpress";
$connectionInfo = array( "Database"=>"db01", "UID"=>"user", "PWD"=>"pass" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false )
{
     echo "Connection could not be established.\n";
     die( print_r( sqlsrv_errors(), true));
}

$tsql1 = "INSERT INTO tbl.test 
(test_1,
test_2)
VALUES
(?,?)";

$params1 = array( 1, 1 );

$query = sqlsvr_query( $conn, $tsql1, $params1);

if( $query )
{
     echo "Row successfully inserted.\n";
}
else
{
     echo "Row insertion failed.\n";
     die( print_r( sqlsrv_errors(), true));
}

sqlsvr_close($conn);

/* header('Location: thanks.htm');
exit(); */

?>

I reversed the connection check statement to acknowledge a connection, which then displayed 'connected', so I figured it's simply getting the data in that's the problem. So maybe a SQL setup problem?? I don't see any PHP errors. The only error message that could be relevant is:

Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 22/06/2012
Time: 10:58:36 AM
User: N/A
Computer: Server
Description:
The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Thanks

Daryn

---

Dazza

Answer 3

Hi Daryn

I didn't notice your 2nd post because the thread is marked as answered.  Could you unmark the answer please?

The error you have found is from the Windows Event Log.  This isn't the same as the PHP error log.  You can find the location of your PHP error log file from your php.ini file.  If you don't have any of these settings, set them:

// example lines from php.ini:
log_errors = On
error_reporting = E_ALL 
error_log = "C:\PHP\errors.log"

Your problem could be caused by loading the wrong version of the driver, so let's make sure that's not it:

1. Create a php page in your web site containing the following code, and load the page in your browser:
   <?php phpinfo(); ?>
   
   If you are not building a web site, run this command from your console (dos prompt/command window) from your PHP folder:
       PHP -i | more
   The first page contains the information needed in the next steps.
   
   
2. You should see a large page of PHP settings.  Near the top is a setting called 'Thread Safety'.  It is set as either 'enabled' or 'disabled'.
   
   • enabled = TS
   • disabled = NTS
     
     
3. There is another setting near the top named 'Compiler', and it will be either MSVC6, MSVC8, or MSVC9.  If this is MSVC8 you have the wrong version of PHP, you need to do more work.  Hopefully it is one of these:
   • MSVC9 = VC9
   • MSVC6 = VC6
     
     
4. Use the answers to 2. and 3. above to find the correct driver version for your system:
       php_sqlsrv_53_   [answer to 2.... TS / NTS]   _   [answer to 3....   VC9 / VC6]   .dll

---

Rob

Answer 4

Note that the SQLSRV 3.0 driver no longer supports VC6, so please make sure you use an Apache build which supports VC9.

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 5

Thanks Jonathan - also worth noting that driver version 3.0 doesn't work on Windows Server 2003.

---

Rob

Answer 6

Good point!

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 7

Hi Rob

Sorry for the delayed response, been sick for a week.

As noted in my initial post the driver is NTS VC9. This matches the PHP requirements as specified in phpinfo.php. I included the windows error log just in case it meant something; because as you suggested there's no PHP errors. Also, I am using the 2.0 driver for Server 2003.

Given that I can retrieve data, I was thinking it was more of a 'permissions' problem in writing to the database?? This is just for a basic web app for some of our sales team. I have working version of the site on our ISP's server, but that is Linux with MYSQL, which I have no problem with. I wanted to bring this in-house onto a spare server we have, but of course it's Windows with SQLExpress. I'm half tempted to install MYSQL, but that could open another can of worms; appreciate your thoughts. My normal work is networking, this PHP is not my thing.

Thanks

Daryn

---

Dazza

Answer 8

Did you switch your PHP into development mode? You can do that if you look into the directory for the different ini file, and then rename the development one to 'php.ini'. That should set a couple of things such that the server crashes not to a 500 error code, but with the actual error trace on the screen.

Cheers,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 9

Ah brilliant!! Now I can see what's going on...

Was just a problem with my script, a date that was being brought in from the web page was in the wrong format and the change to development mode allowed me to see see the actual error as opposed to the 500 error code.

Thanks Jonathan

---

Dazza

Answer 10

Glad you worked it out :) For the people that happen to come across this thread, would you mind describing the mistake you made so that they can learn from it? :)

Thanks!

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 11

Possibly the PHP DateTime constructor threw an exception which was not caught:

$date = new DateTime(0);
// throws an exception

I would guess that Daryn did not have any logging switched on, because the exception would have appeared in the PHP log.

Good analysis Jonathan - I assumed incorrectly that the 500 error was due to a Windows exception, beecause we don't see error 500 in development.

---

Rob