/** * @param String sql * @param Number pageStart * @param Number pageSize * @param Boolean applyLimit */ public function queryPage($strSQL, $pageStart, $pageSize, $applyLimit) { if ($this->dbType == nDATABASE_MySQL) { if ($applyLimit) { $strSQL .= " limit " . ($pageStart - 1) * $pageSize . "," . $pageSize; } return $this->query($strSQL)->getQueryHandle(); } if ($this->dbType == nDATABASE_MSSQLServer || $this->dbType == nDATABASE_Access) { if ($applyLimit) { $strSQL = AddTop($strSQL, $pageStart * $pageSize); } $qResult = $this->query($strSQL); $qResult->seekPage($pageSize, $pageStart); return $qResult->getQueryHandle(); } if ($this->dbType == nDATABASE_Oracle) { if ($applyLimit) { $strSQL = AddRowNumber($strSQL, $pageStart * $pageSize); } $qResult = $this->query($strSQL); $qResult->seekPage($pageSize, $pageStart); return $qResult->getQueryHandle(); } if ($this->dbType == nDATABASE_PostgreSQL) { if ($applyLimit) { $strSQL .= " limit " . $pageSize . " offset " . ($pageStart - 1) * $pageSize; } return $this->query($strSQL)->getQueryHandle(); } if ($this->dbType == nDATABASE_DB2) { if ($applyLimit) { $strSQL = "with DB2_QUERY as (" . $strSQL . ") select * from DB2_QUERY where DB2_ROW_NUMBER between " . (($pageStart - 1) * $pageSize + 1) . " and " . $pageStart * $pageSize; } return $this->query($strSQL)->getQueryHandle(); } if ($this->dbType == nDATABASE_Informix) { if ($applyLimit) { $strSQL = AddTopIfx($strSQL, $pageStart * $pageSize); } $qResult = $this->query($strSQL); $qResult->seekPage($pageSize, $pageStart); return $qResult->getQueryHandle(); } if ($this->dbType == nDATABASE_SQLite3) { if ($applyLimit) { $strSQL .= " limit " . ($pageStart - 1) * $pageSize . "," . $pageSize; } return $this->query($strSQL)->getQueryHandle(); } $qResult = $this->query($strSQL); $qResult->seekPage($pageSize, $pageStart); return $qResult->getQueryHandle(); }
/** * Seeks recs, depending on page number etc. * * @param string $strSQL */ function seekPageInRecSet($strSQL) { $listarray = false; if ($this->eventExists("ListQuery")) { $arrFieldForSort = array(); $arrHowFieldSort = array(); for ($i = 0; $i < count($this->orderClause->fieldsList); $i++) { $arrFieldForSort[] = $this->orderClause->fieldsList[$i]->fieldIndex; $arrHowFieldSort[] = $this->orderClause->fieldsList[$i]->orderDirection; } $listarray = $this->eventsObject->ListQuery($this->searchClauseObj, $arrFieldForSort, $arrHowFieldSort, $this->masterTable, $this->masterKeysReq, null, $this->pageSize, $this->myPage, $this); } if ($listarray !== false) { $this->recSet = $listarray; } else { if ($this->dbType == nDATABASE_MySQL) { if ($this->maxPages > 1) { $strSQL .= " limit " . ($this->myPage - 1) * $this->pageSize . "," . $this->pageSize; } $this->recSet = db_query($strSQL, $this->conn); } elseif ($this->dbType == nDATABASE_MSSQLServer) { if ($this->maxPages > 1) { $strSQL = AddTop($strSQL, $this->myPage * $this->pageSize); } $this->recSet = db_query($strSQL, $this->conn); db_pageseek($this->recSet, $this->pageSize, $this->myPage); } elseif ($this->dbType == nDATABASE_Access) { if ($this->maxPages > 1) { $strSQL = AddTop($strSQL, $this->myPage * $this->pageSize); } $this->recSet = db_query($strSQL, $this->conn); db_pageseek($this->recSet, $this->pageSize, $this->myPage); } elseif ($this->dbType == nDATABASE_Oracle) { if ($this->maxPages > 1) { $strSQL = AddRowNumber($strSQL, $this->myPage * $this->pageSize); } $this->recSet = db_query($strSQL, $this->conn); db_pageseek($this->recSet, $this->pageSize, $this->myPage); } elseif ($this->dbType == nDATABASE_PostgreSQL) { if ($this->maxPages > 1) { $maxrecs = $this->pageSize; $strSQL .= " limit " . $this->pageSize . " offset " . ($this->myPage - 1) * $this->pageSize; } $this->recSet = db_query($strSQL, $this->conn); } elseif ($this->dbType == nDATABASE_DB2) { if ($this->maxPages > 1) { $strSQL = "with DB2_QUERY as (" . $strSQL . ") select * from DB2_QUERY where DB2_ROW_NUMBER between " . (($this->myPage - 1) * $this->pageSize + 1) . " and " . $this->myPage * $this->pageSize; } $this->recSet = db_query($strSQL, $this->conn); } elseif ($this->dbType == nDATABASE_Informix) { if ($this->maxPages > 1) { $strSQL = AddTopIfx($strSQL, $this->myPage * $this->pageSize); } $this->recSet = db_query($strSQL, $this->conn); db_pageseek($this->recSet, $this->pageSize, $this->myPage); } elseif ($this->dbType == nDATABASE_SQLite3) { if ($this->maxPages > 1) { $strSQL .= " limit " . ($this->myPage - 1) * $this->pageSize . "," . $this->pageSize; } $this->recSet = db_query($strSQL, $this->conn); } else { $this->recSet = db_query($strSQL, $this->conn); db_pageseek($this->recSet, $this->pageSize, $this->myPage); } } }
/** * Apply a limit to an SQL-query * @param String sql * @param Number N * @param Number dbType * @return String */ function applyDBrecordLimit($sql, $N, $dbType) { if (!strlen($dbType)) { return $sql; } if ($dbType == nDATABASE_MySQL || $dbType == nDATABASE_PostgreSQL || $dbType == nDATABASE_SQLite3) { return $sql . " LIMIT " . $N; } if ($dbType == nDATABASE_Oracle) { return AddRowNumber($sql, $N); } if ($dbType == nDATABASE_MSSQLServer || $dbType == nDATABASE_Access) { return AddTop($sql, $N); } if ($dbType == nDATABASE_Informix) { return AddTopIfx($sql, $N); } if ($dbType == nDATABASE_DB2) { return AddTopDB2($sql, $N); } return $sql; }