PearDatabase library based on PDO [not verified]

The connection between the application and the database has been improved in YetiForce 2.1. This improvement has been achieved by updating one of the libraries responsible for the communication. The previous versions connected to the database via ADODB, and then used MySQL drivers. The current version of PearDatabase connects directly to PDO that is built-in to PHP so it will always be up-to-date.

Connection to the database

  1. Standard (connects to the default database):
    • $db = PearDatabase::getInstance();
  2. Advanced (connects to any database):
    • $db = new PearDatabase($db_type, $db_hostname, $db_name, $db_username, $db_password);

Debugging

File config\debug.php driver SQL_DIE_ON_ERROR - Stops the application when it finds a wrong query and it displays an error message. 
Database ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'searchlabesl' in 'field list'

#0 PearDatabase->checkError() called at [C:\www\YetiForceCRM\include\database\PearDatabase.php:306]

#1 PearDatabase->pquery() called at [C:\www\YetiForceCRM\vtlib\Vtiger\Functions.php:245]

#2 Vtiger_Functions::getCRMRecordMetadata() called at [C:\www\YetiForceCRM\vtlib\Vtiger\Functions.php:269]

#3 Vtiger_Functions::getCRMRecordLabel() called at [C:\www\YetiForceCRM\modules\Vtiger\models\Menu.php:82]

#4 Vtiger_Menu_Model::getBreadcrumbs() called at [C:\www\YetiForceCRM\cache\templates_c\vlayout\2e3b24f53a6fa7b73fe91e9ef7050f26857fa9d9.file.CommonActions.tpl.php:113]

#5 content_557e7fd8edf954_89212780() called at [C:\www\YetiForceCRM\libraries\Smarty\libs\sysplugins\smarty_internal_templatebase.php:188]

#6 Smarty_Internal_TemplateBase->fetch() called at [C:\www\YetiForceCRM\libraries\Smarty\libs\sysplugins\smarty_internal_template.php:304]

#7 Smarty_Internal_Template->getSubTemplate() called at [C:\www\YetiForceCRM\cache\templates_c\vlayout\af7d5c37f878a084bcedc2c729eafdef9b042191.file.BasicHeader.tpl.php:24]

#8 content_557e7fd8a14f38_27662380() called at [C:\www\YetiForceCRM\libraries\Smarty\libs\sysplugins\smarty_internal_templatebase.php:188]

#9 Smarty_Internal_TemplateBase->fetch() called at [C:\www\YetiForceCRM\libraries\Smarty\libs\sysplugins\smarty_internal_template.php:304]

#10 Smarty_Internal_Template->getSubTemplate() called at [C:\www\YetiForceCRM\cache\templates_c\vlayout\58d2208bab530ab02000de6d14631bb5289359db.file.DetailViewPreProcess.tpl.php:31]

#11 content_557e9e07d359e3_34804255() called at [C:\www\YetiForceCRM\libraries\Smarty\libs\sysplugins\smarty_internal_templatebase.php:188]

#12 Smarty_Internal_TemplateBase->fetch() called at [C:\www\YetiForceCRM\libraries\Smarty\libs\sysplugins\smarty_internal_templatebase.php:394]

#13 Smarty_Internal_TemplateBase->display() called at [C:\www\YetiForceCRM\include\runtime\Viewer.php:182]

#14 Vtiger_Viewer->view() called at [C:\www\YetiForceCRM\include\runtime\Controller.php:176]

#15 Vtiger_View_Controller->preProcessDisplay() called at [C:\www\YetiForceCRM\modules\Vtiger\views\Detail.php:126]

#16 Vtiger_Detail_View->preProcess() called at [C:\www\YetiForceCRM\modules\Leads\views\Detail.php:18]

#17 Leads_Detail_View->preProcess() called at [C:\www\YetiForceCRM\include\main\WebUI.php:78]

#18 Vtiger_WebUI->triggerPreProcess() called at [C:\www\YetiForceCRM\include\main\WebUI.php:203]

#19 Vtiger_WebUI->process() called at [C:\www\YetiForceCRM\index.php:27]

The most important examples of code using the database 

$result = $db->query('SELECT * FROM vtiger_leadstatus');
 
while($row = $db->fetchByAssoc($result)){
 $pick_val = $row[$fieldName];
}
 
$resultUsers = $db->query('SELECT id FROM vtiger_users;');
 
while ($userID = $db->getSingleValue($resultUsers)) {
 echo $userID;
}
 
$resultUsers = $db->query('SELECT id FROM vtiger_users;');
$userID = $db->getSingleValue($resultUsers);

The differences in listing of data 

Before

for($i = 0; $i < $db->num_rows($listResult); $i++) { $row = $db->fetch_row($listResult, $i);

Now

while($row = $db->fetchByAssoc($listResult)){

A list of functions

  • bind($param, $value, $type = null) - adds parameters to a query http://php.net/manual/en/pdostatement.bindvalue.php;
  • checkError($message, $dieOn-Error = false) - logs errors;
  • columnMeta(&$result, $col) - information about a column in results;
  • completeTransaction() - transaction completed successfully;
  • concat($list) - creates concat value for mysql from an arry;
  • connect() - connects to the database;
  • delete($table, $where = '', $params = []) - deletes data;
  • disconnect() - disconnects the database;
  • ErrorMsg() - error massage for a query http://php.net/manual/en/pdostatement.errorinfo.php;
  • execute - executes a query  http://php.net/manual/en/pdostatement.execute.php;
  • fetch_array($result) - returns an associative array with all results http://php.net/manual/en/pdostatement.fetch.php;
  • fetchByAssoc(&$result, $rowNum = -1, $encode = true) - returns an associative array, first line by default or a line with a particular number;
  • flatten_array($input, $output = null) - flattens a multilevel array to one level;
  • formatDate($datetime, $strip_quotes = false) - not supported at the moment;
  • generateQuestionMarks($items) - creates a string with "?" and there are as many question marks as array elements, e.g. "?,?,?,?";
  • getAffectedRowCount($result) - the amount of data for which a query was executed, for selects it's the number of records and for updates it's the amount of updated data http://php.net/manual/en/pdostatement.rowcount.php;
  • getColumnNames($tablename) - an array with column names;
  • getColumnsMeta($tablename) - an array with objects that contain detailed information about columns in an array;
  • getEmptyBlob() - a null sign;
  • getFieldsArray($result)- an array with columns from results http://php.net/manual/en/pdostatement.getcolumnmeta.php;
  • getFieldsDefinition($result) - an array with objects that contain detailed information about columns in an array;
  • getInstance() - creates a new PearDatabase instance;
  • getLastInsertID($seqname = '') - id of the last insert http://php.net/manual/en/pdo.lastinsertid.php;
  • getOne($sql, $dieOn-Error = false, $msg = '') - downloads single value from results;
  • getRowCount($result) - the number of records;
  • getSingleValue - downloads single value from results; http://php.net/manual/en/pdostatement.fetchcolumn.php;
  • getUniqueID($seqname) - downloads a subsequent ID for an array;
  • hasFailedTransaction() - is there an error in transaction?;
  • insert($table, $data) - adds data to the database (data - associative array);
  • isMySQL() - is it MySQL database?;
  • isOracle() - is it Oracle database?;
  • isPostgres() - is it Postgres database?;
  • log($message, $type = 'info') - saves database logs;
  • logSqlTime($startat, $endat, $sql, $params = false) - an advanced save of database logs (under development);
  • num_fields($result) - the number of columns in results http://php.net/manual/en/pdostatement.columncount.php;
  • num_rows($result) - the number of lines in results;
  • pquery($query, $params = [], $dieOn-Error = false, $msg = '') - executes a query with parameters;
  • prepare($query) - executes a query http://php.net/manual/en/pdo.prepare.php;
  • println($msg) - saves database logs;
  • query($query, $dieOn-Error = false, $msg = '') - executes a query without parameters http://php.net/manual/en/pdo.query.php;
  • query_result(&$result, $row, $col = 0) - downloads data;
  • query_result_raw(&$result, $row = 0) - downloads data;
  • query_result_rowdata($result, $row = 0) - downloads data;
  • raw_query_result_rowdata(&$result, $row = 0) - downloads data;
  • quote($input, $quote = true, $type = null) - protects value in sql query  http://php.net/manual/en/pdo.quote.php;
  • requirePsSingleResult($sql, $params, $dieOn-Error = false, $msg = '', $encode = true) - number of lines in results has to be equal to 1, otherwise it returns false; an option with parameters;
  • requireSingleResult($sql, $dieOn-Error = false, $msg = '', $encode = true) - number of lines in results has to be equal to 1, otherwise it returns false; an option without parameters;
  • resetSettings($dbtype, $host, $dbname, $username, $passwd) - changes data to connect to the database, performing connect() is required afterwards
  • rollbackTransaction() - reverse changes performed in a transaction;
  • setAttribute(func_get_args()) - sets attributes for a database connection http://php.net/manual/en/pdo.setattribute.php;
  • setDatabaseHost($host) - saves host name;
  • setDatabaseName($dbname) - save database name;
  • setDatabaseType($type) - saves connection;
  • setDieOnError($bool) - sets whether a script should be stopped when an error appears;
  • setUserName($name) - saves host name;
  • setUserPassword($pass) - saves host name;
  • sql_escape_string($str) -protects values in sql query;
  • sqlExprDatalist($array) - creates a string ('44','66','777') from a array with protected values;
  • startTransaction() - ends transaction;
  • updateBlob($table, $column, $val, $where) - updates data.
  • Friday, 11 August 2017