4S SQL Server doesn't return control back to Apache Web Server

[J. Fink]

Using the CRUD database and files from the knowledge base link (http://
kb.4d.com/search/assetid=76084), I performed a series of SELECT
queries against the database that originated in the read.php file. The
goal is to query for all records where a field that may or may not be
populated. The field Customers.Region is not populated for every
record in the CRUD database.

The following query: $sql = 'SELECT ContactName, CompanyName, Region,
Country FROM Customers WHERE ContactTitle = \'' . $title . '\' ';
works as expected.

Whereas this query: $sql = 'SELECT CompanyName, Region FROM Customers
WHERE ContactTitle = \'' . $title . '\' '; does not. Note: the fields
ContactName and Country have been removed from the query.

Expected results: the same number of records with only the 2 columns.
Actual results: Control never returns to the Apache server from the 4D
SQL server.

4D Server Administrator on the SQL Server tab shows that a SQL
connection is made. The browser shows that the Apache server has been
requested. Execution was started at 21:39:00. Browser was still
attempting to load at 21:42:00. Connection to SQL server is only
dropped when Apache server is manually stopped. As Apache doesn’t have
control, PHP can’t implement “max_execution_time” (which is set to the
default of 30 seconds).

Apache Server Error Log:
Parent: Received shutdown signal -- Shutting down the server.
Child 3860: Exit event signaled. Child process is ending.
Child 3860: Released the start mutex
Child 3860: All worker threads have exited.
Child 3860: Child process is exiting
Parent: Child process exited successfully.
Child 1640: Waiting 30 more seconds for 1 worker threads to finish.
Child 1640: Waiting 0 more seconds for 1 worker threads to finish.
Child 1640: Terminating 1 threads that failed to exit.
Child 1640: All worker threads have exited.
Child 1640: Child process is exiting

Web server: Apache 2.2.17, PHP 5.3.3 (MSVC9 (Visual C++ 2008)) on a
Windows Server 2003 Standard Edition Service Pack 2. I'm assuming the
version of PDO is 0.3 even though a phpinfo() shows 0.1 alpha as I
obtained the .dll from .zip link at http://forums.4d.fr/Post/FR/3549301/1/3601455#3601455
4D: v12.1 build 82774 on a Windows Server 2008 Standard Edition and
Windows Seven Business Edition, 64-bit

[Alexandre Morgaut]

Hi Jennifer,

> The following query: $sql = 'SELECT ContactName, CompanyName, Region,
> Country FROM Customers WHERE ContactTitle = \'' . $title . '\' ';
> works as expected.

Just as a TIP, it is better to first call the prepare method

$query = $db->prepare('SELECT ContactName, CompanyName, Region,
Country FROM Customers WHERE ContactTitle = ?');

And then bind the filter value at requesting time

$query->execute(array($title));

This way, your are sure that the parameter is correctly escaped and
won't make the request fail
It will also prevent from some SQL Injection (even if more
verifications are usually recommended)

The Documentation doesn't actually show this method, I will ask for
updates

> Web server: Apache 2.2.17, PHP 5.3.3 (MSVC9 (Visual C++ 2008)) on a
> Windows Server 2003 Standard Edition Service Pack 2. I'm assuming the
> version of PDO is 0.3 even though a phpinfo() shows 0.1 alpha as I
> obtained the .dll from .zip link athttp://forums.4d.fr/Post/FR/3549301/1/3601455#3601455

Hum that's weird, in the 0.3 version, the phpinfo() is meant to show
correct informations.
It does for sure if built from the 0.3 package available on the PECL
repository, but as I didn't do this Windows .dll myself, I can't
guaranty it has been correctly done...

Is anyone else using this dll and could he tell if the phpinfo()
result shows the correct version ?


Once we are sure you are using the correct version, you will have to
submit this bug on the PECL bug base: http://pecl.php.net/bugs/report.php?package=PDO_4D

We would have to check why the 4D server is not responding, and why
the PDO driver didn't time out after a while
I have doubt about the version because I remember discussions with the
original developer about a timeout as you can see in the changelog of
the 0.2.0 version
http://pecl.php.net/package-changelog.php?package=PDO_4D

[J. Fink]

I continued testing against the CRUD database, table Company this morning. The code to create the table can be found in the file create.php in http://kb.4d.com/search/assetid=76084. Review of the code shows that if the Address field was populated, it was populated with a string value and not an empty string or NULL. I manually added a new record through a 4D entry form and did not enter a value for the Address field.

Here's the code:
$dsn = "4D:host=XXX.XXX.XXX.XXX;port=19812;dbname=crud;charset=UTF-8;";
$user = 'Administrator';
$pswd = 'test';
$db = new PDO($dsn, $user, $pswd);
$sql = "SELECT Name, Address FROM Company ORDER BY Name ASC";
echo '<strong>Query on Company Table with alpha fields converted to text </strong>- ' . $sql . '<br><br>';
$stmt = $db->prepare($sql);
$stmt->execute();
$aResults = $stmt->fetchAll();
echo "<table border='1'><tr><th>Company Name</th><th>Address</th></tr>";
foreach($aResults as $result)
{
  echo "<tr><td>".$result['NAME']."</td><td>".$result['ADDRESS']."</td></tr>";
}
echo "<table>";

I modified the field types in 4D Structure Inspector from Alpha to Text as this is what our production set up will be.

Test 1: All fields in table Company do not have the option "Map NULL values to blank values" checked.
Results of Test 1: Displayed as expected.

Test 2: For field Address, option "Map NULL values to blank values" checked.
Results of Test 2: Control flow does not return to Apache from 4D. SQL connection remains until Apache server stopped. etc.

Test 3: For field Name: option "Map NULL values to blank values" checked and field Address: option "Map NULL values to blank values" not checked.
Results of Test 3: Displayed as expected.

The attached pdf 3-10-2011_11-35-23 shows the table information in 4D. Only the fields ID & Name have values for each record. The Address field only has 2 records populated.
The attached pdf 3-10-2011_11-47-23 shows the results from the browser for Test 1 and Test 3.

Test 4: In 4D method, I did a check of each Address value with the method "Is field value NULL" and in the structure for field Address: option "Map NULL values to blank values" not checked.
Results of Test 4: Expected results. True for record 1, True for record 2, True for record 3, False for record 4, False for record 5 and True for record 6.

Test 5: In 4D method, I did a check of each Address value with the method "Is field value NULL" and in the structure for field Address: option "Map NULL values to blank values" checked.
Results of Test 5: Expected results. False for record 1, False for record 2, False for record 3, False for record 4, False for record 5 and False for record 6.

Both Test 4 & 5 were done for confirmation