Searching records in FileMaker Custom Web Publishing(CWP) using PDO(PHP Data Objects) can become pretty frustrating as a table grows much bigger, say the table has one million records.
A table with one million records is not THAT huge when it is of a MySQL table, but it is obviously too much to handle for FileMaker if you want to do some CWP using PDO.
How would you navigate records in a found set?
You may find some different ways to navigate records in a found set if you google it.
However, when it comes to FileMaker CWP, your options are quite limited after you consider those don't's we have already explained above.
Method 1: Use PDO scrollable cursors
Here is a sample php script for retrieving the last record in a found set using a PDO scrollable cursor.
<?php
//sets up a connection
try {
$dsn= "odbc:Driver={FileMaker ODBC};host=127.0.0.1;Database=test";
$pdo = new PDO( $dsn, "cgi", "pwd" );
$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$pdo->setAttribute( PDO::ATTR_CASE, PDO::CASE_NATURAL );
} catch ( PDOException $e ) {
exit( "Database connection failed.". $e->getMessage() );
}
//builds a query string
$sql = "select recId, invoiceNo from invoice";
//prepares a PDO statement and runs it
$stmt = $pdo->prepare( $sql, array( PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL ) );
$stmt->execute();
//retrieves the last record in a found set
$record = $stmt->fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_LAST );
//script goes on using $record....
?>
PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL defines a scrollable cursor, and
PDO::FETCH_ORI_LAST moves the cursor to the last row in a found set.
You may also consider using the following predefined PDO constants:
PDO::FETCH_ORI_FIRST --- moves the cursor to the first row in a found set.
PDO::FETCH_ORI_ABS --- moves the cursor to the specified row in a found set. The absolute position is specified in the third argument.
Example:
$record = $stmt->fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS,$absPosi);
For more information, please visit
http://php.net/manual/en/pdo.constants.php
Method 2: Use FileMaker's OFFSET n ROWS and FETCH FIRST n ROWS Clauses
Here is a sample php script for retrieving the
5000th record in a found set using FileMaker's
OFFSET n ROWS and
FETCH FIRST n ROWS clauses.
<?php
//sets up a connection
try {
$dsn= "odbc:Driver={FileMaker ODBC};host=127.0.0.1;Database=test";
$pdo = new PDO( $dsn, "cgi", "pwd" );
$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$pdo->setAttribute( PDO::ATTR_CASE, PDO::CASE_NATURAL );
} catch ( PDOException $e ) {
exit( "Database connection failed.". $e->getMessage() );
}
//defines the offset value of 4999
$offset = 4999;
//builds a query string
$sql = "select recId, invoiceNo from invoice OFFSET ".$offset." ROWS";
$sql .= " FETCH FIRST 1 ROWS ONLY";
//prepares a PDO statement and runs it
$stmt = $pdo->prepare( $sql );
$stmt->execute();
//retrieves a record
$record = $stmt->fetch( );
//script goes on using $record....
?>
$offset = 4999 defines to go to the
4999th row in a found set.
FETCH FIRST 1 ROWS ONLY defines to retrieve
one row.
Thus, the
5000th row will be set to
$record variable.
The Response is SO Slow Whatever the Method is....
Practically speaking, requiring five seconds to retrieve JUST one record is simply ridiculous.
But unfortunately this happens with FileMaker CWP when tables get bigger.
We have created sample scripts using those two data retrieving methods to navigate records in a found set, in order to show you how slow the FileMaker server response gets.
Method 1: Using PDO scrollable cursors
First button
The response is fairly good since the query does not have a WHERE clause.
Next button
It took 5.3859 seconds just to show the second record out of 939,623 records.
This outcome is pretty disappointing but this happens when the query has a
WHERE clause and the found set is huge.
In this example, cRecId > 17538 results in 922,084 records and FileMaker Server's response goes slow, even though cRecId field is indexed.
Previous button
It took 5.4028 seconds when clicking Previous button to retrieve the previous record from the very last position.
This is another disappointing outcome because this huge found set caused the slow response.
In this example, cRecId < 957160 results in 939,622 records and FileMaker Server's response goes slow, even though cRecId field is indexed.
Last button
The response is alright, since the query does not require a WHERE clause.
Method 2: Using FileMaker's OFFSET n ROWS and FETCH FIRST n ROWS Clauses
This method requires two separate queries:
- A query to get the record count from a table with SELECT clause.
- A query to set the starting position (offset) in a found set and fetch the specified number of rows of records.
Performing two queries does not appear to be a very smart, however, there is no better way to know how many records there are in a table(especially when you want to know the total record count), and then perform OFFSET and FETCH ONLY n ROWS clauses.
First button
It took 1.0043 seconds to get the first row.
Please note the first query (QUERY1 in the figure below) does not have a WHERE clause.
Next button
It took 5.7659 seconds just to show the second record out of 939,623 records.
In this example, the first query's (QUERY1 in the figure below) cRecId > 17538 results in 922,084 records and FileMaker Server's response goes slow, even though cRecId field is indexed.
Previous button
It took 6.854 seconds when clicking Previous button to retrieve the previous record from the very last position.
In this example, the first query's (QUERY1 in the figure below) cRecId < 957160 results in 939,622 records and FileMaker Server's response goes slow, even though cRecId field is indexed.
Last button
It took 2.0451 seconds to get the very last row.
The first query (QUERY1 in the figure below) does not have a WHERE clause, so the response is faster than the Next and Previous buttons.
(亀/turtle)
