function &DBselect($query, $limit = 'NO')
{
global $DB;
//COpt::savesqlrequest($query);
$result = false;
if (isset($DB['DB']) && !empty($DB['DB'])) {
//SDI('SQL: '.$query);
$DB['SELECT_COUNT']++;
switch ($DB['TYPE']) {
case 'MYSQL':
if (zbx_numeric($limit)) {
$query .= ' limit ' . intval($limit);
}
$result = mysql_query($query, $DB['DB']);
if (!$result) {
error('Error in query [' . $query . '] [' . mysql_error() . ']');
}
break;
case 'POSTGRESQL':
if (zbx_numeric($limit)) {
$query .= ' limit ' . intval($limit);
}
$result = pg_query($DB['DB'], $query);
if (!$result) {
error('Error in query [' . $query . '] [' . pg_last_error() . ']');
}
break;
case 'ORACLE':
if (zbx_numeric($limit)) {
$query = 'select * from (' . $query . ') where rownum<=' . intval($limit);
}
$result = DBexecute($query);
if (!$result) {
$e = ocierror($result);
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
break;
case 'SQLITE3':
if (!$DB['TRANSACTIONS']) {
lock_db_access();
}
if (!($result = sqlite3_query($DB['DB'], $query))) {
error('Error in query [' . $query . '] [' . sqlite3_error($DB['DB']) . ']');
} else {
$data = array();
while ($row = sqlite3_fetch_array($result)) {
foreach ($row as $id => $name) {
if (!zbx_strstr($id, '.')) {
continue;
}
$ids = explode('.', $id);
$row[array_pop($ids)] = $row[$id];
unset($row[$id]);
}
$data[] = $row;
}
sqlite3_query_close($result);
$result =& $data;
}
if (!$DB['TRANSACTIONS']) {
unlock_db_access();
}
break;
}
if ($DB['TRANSACTIONS'] && !$result) {
$DB['TRANSACTION_STATE'] &= $result;
// SDI($query);
// SDI($DB['TRANSACTION_STATE']);
}
}
return $result;
}
/**
* free the specified result.
*
* @param resource $result the query resource result
*
* @return bool DB_OK
*
*/
function freeResult($result)
{
sqlite3_query_close($result);
return DB_OK;
/* always sucessful ! */
}
function alter_col($tablename, $colname, $type)
{
// Ensure we're connected to the database.
if ($this->dbhandle == null) {
if (!($this->dbhandle = $this->sql_database_connect())) {
$this->debug('SEVERE: Unable to connect to database.', 1);
return false;
}
}
switch ($this->db) {
case "mysql":
return $this->sql("ALTER TABLE `{$tablename}` CHANGE `{$colname}` `{$colname}` {$type}");
case "sqlite":
case "sqlite3":
// As per remove_col - SQLite doesn't support ALTER TABLE properly. We have to work
// around it's limitations.
if ($this->db == "sqlite3") {
$res = sqlite3_query($this->dbhandle, "select `tbl_name`,`sql` from `sqlite_master` where `tbl_name`='{$tablename}'");
$sqlarr = sqlite3_fetch_array($res);
sqlite3_query_close($res);
} else {
// We're using the SQLite3 class, which works normally.
$res = $this->sql("select `tbl_name`,`sql` from `sqlite_master` where `tbl_name`='{$tablename}'", "ASSOC", true);
$sqlarr = $res[0];
}
$sqlCreate = $sqlarr['sql'];
// Extract the col types for all the cols in the $sqlCreate string
preg_match_all('/\\n\\s+`(.+)`\\s(.+)[,?$]/', $sqlCreate, $arrNewTableInfo);
// Which loads the col NAMES into $arr[1] and col TYPES into $arr[2]
// For ease of use, we'll just make it assocative.
$i = 0;
foreach ($arrNewTableInfo[1] as $name) {
$arrAssocTypes[$name] = $arrNewTableInfo[2][$i++];
}
print_r($arrAssocTypes);
// and NOW we know what the types are for each name. Lets make sure
// that the col you want to change actually exists.
if (defined($arrAssocTypes[$colname])) {
$this->debug("SQL Error - Tried to change col {$colname}, but it doesn't exist", 2);
print_r($arrAssocTypes);
return false;
}
// Now we need to replace the type of the col in $sqlCreate with the correct one.
$strOld = "`{$colname}` " . $arrAssocTypes[$colname];
$strNew = "`{$colname}` " . $type;
$sqlNewCreate = str_replace($strOld, $strNew, $sqlCreate);
// Right. So we've got the new table definition in $sqlNewCreate, now all we need
// to do is move the old table out of the way, create the new table, and copy
// everything across.
$this->rename_table($tablename, "{$tablename}_temp");
$this->sql($sqlNewCreate, "NONE", true);
// Create the list of cols to use on the import.
$strAllCols = implode(",", $arrNewTableInfo[1]);
// Copy everything from the old table to the new
$sql = "INSERT INTO `{$tablename}` SELECT {$strAllCols} FROM {$tablename}_temp";
if (!$this->sql($sql, "NONE", true)) {
$this->debug("SQL Command Failed: {$sql}\n" . $this->errstr . "\n");
}
break;
default:
$this->debug("SEVERE: Database type '" . $this->db . "' NOT SUPPORTED (escape)", 0);
return false;
}
}
print "\ttable created<br>\n";
}
print "Inserting values:<br>\n";
sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (1,'michael',32)");
sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (2,'bob',27)");
sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (3,'martin',12)");
/*
* Create a query
*/
print "SQL query:<br>\n";
$query = sqlite3_query($db, "SELECT * FROM test ORDER BY age DESC");
if (!$query) {
die(sqlite3_error($db));
}
/*
* sqlite3_fetch_array() returns an associative array
* for each row in the result set. Key indexes are
* the columns names.
*
*/
while ($row = sqlite3_fetch_array($query)) {
printf("\t%-20s %u<br>\n", $row['name'], $row['age']);
}
/*
* do not forget to release all handles !
*
*/
print "Closing:<br>\n";
sqlite3_query_close($query);
sqlite3_close($db);
print "</html>\n";
/**
* perform a query on the database
* @param string $Q_str
* @return result id or bool depend on the query type| FALSE
*/
function query($Q_str)
{
if (!$this->db) {
if (!(db::$autoconnect && $this->open())) {
return FALSE;
}
}
$this->verbose($Q_str, __FUNCTION__, 2);
if ($this->last_qres) {
#- close unclosed previous qres
sqlite3_query_close($this->last_qres);
$this->last_qres = null;
}
if (preg_match('!^\\s*select!i', $Q_str)) {
$this->last_qres = sqlite3_query($this->db, $Q_str);
$res = $this->last_qres;
} else {
$res = sqlite3_exec($this->db, $Q_str);
}
if (!$res) {
$this->set_error(__FUNCTION__);
}
return $res;
}
}
$res = sqlite3_query($db, "insert into test (a,b,c, d) VALUES (?, ?, ?, ?)");
if (!$res) {
die(sqlite3_error($db));
}
if (!sqlite3_bind_int($res, 1, 10)) {
die(sqlite3_error($db));
}
if (!sqlite3_bind_text($res, 2, "bob")) {
die(sqlite3_error($db));
}
if (!sqlite3_bind_double($res, 3, 3.1415)) {
die(sqlite3_error($db));
}
if (!sqlite3_bind_blob($res, 4, file_get_contents("/bin/sh"))) {
die(sqlite3_error($db));
}
if (!sqlite3_query_exec($res, TRUE)) {
/* TRUE: delete the resource after the execution */
die(sqlite3_error($db));
}
$res = sqlite3_query($db, "SELECT * from test");
if (!$res) {
die(sqlite3_error($db));
}
$a_row = sqlite3_fetch_array($res);
for ($n = 0; $n < sqlite3_column_count($res); $n++) {
echo "column {$n}: type " . $col_types[sqlite3_column_type($res, $n)] . "\n";
}
sqlite3_query_close($res);
sqlite3_close($db);
function &DBselect($query, $limit = 'NO', $offset = 0)
{
global $DB;
$time_start = microtime(true);
$result = false;
if (isset($DB['DB']) && !empty($DB['DB'])) {
$DB['SELECT_COUNT']++;
//SDI('SQL['.$DB['SELECT_COUNT'].']: '.$query);
switch ($DB['TYPE']) {
case 'MYSQL':
if (zbx_ctype_digit($limit)) {
$query .= ' LIMIT ' . intval($limit) . ' OFFSET ' . intval($offset);
}
$result = mysql_query($query, $DB['DB']);
if (!$result) {
error('Error in query [' . $query . '] [' . mysql_error() . ']');
}
break;
case 'POSTGRESQL':
if (zbx_ctype_digit($limit)) {
$query .= ' LIMIT ' . intval($limit) . ' OFFSET ' . intval($offset);
}
$result = pg_query($DB['DB'], $query);
if (!$result) {
error('Error in query [' . $query . '] [' . pg_last_error() . ']');
}
break;
case 'ORACLE':
if (zbx_ctype_digit($limit)) {
$till = $offset + $limit;
$query = 'SELECT * FROM (' . $query . ') WHERE rownum BETWEEN ' . intval($offset) . ' AND ' . intval($till);
}
$result = OCIParse($DB['DB'], $query);
if (!$result) {
$e = @ocierror();
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
} else {
if (!@OCIExecute($result, $DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS)) {
$e = ocierror($result);
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
}
break;
case 'IBM_DB2':
if (zbx_ctype_digit($limit)) {
$till = $offset + $limit;
$query = 'SELECT * FROM (' . $query . ') WHERE rownum BETWEEN ' . intval($offset) . ' AND ' . intval($till);
}
$options = array();
if ($DB['TRANSACTIONS']) {
$options['autocommit'] = DB2_AUTOCOMMIT_OFF;
}
if (!($result = db2_prepare($DB['DB'], $query))) {
$e = @db2_stmt_errormsg($result);
error('SQL error [' . $query . '] in [' . $e . ']');
} else {
if (true !== @db2_execute($result, $options)) {
$e = @db2_stmt_errormsg($result);
error('SQL error [' . $query . '] in [' . $e . ']');
$result = false;
}
}
break;
case 'SQLITE3':
if (!$DB['TRANSACTIONS']) {
lock_db_access();
}
if (zbx_ctype_digit($limit)) {
$query .= ' LIMIT ' . intval($limit) . ' OFFSET ' . intval($offset);
}
if (!($result = sqlite3_query($DB['DB'], $query))) {
error('Error in query [' . $query . '] [' . sqlite3_error($DB['DB']) . ']');
} else {
$data = array();
while ($row = sqlite3_fetch_array($result)) {
foreach ($row as $id => $name) {
if (!zbx_strstr($id, '.')) {
continue;
}
$ids = explode('.', $id);
$row[array_pop($ids)] = $row[$id];
unset($row[$id]);
}
$data[] = $row;
}
sqlite3_query_close($result);
$result =& $data;
}
if (!$DB['TRANSACTIONS']) {
unlock_db_access();
}
break;
}
if ($DB['TRANSACTIONS'] && !$result) {
$DB['TRANSACTION_STATE'] &= $result;
}
}
COpt::savesqlrequest(microtime(true) - $time_start, $query);
return $result;
}
public function finalize()
{
return sqlite3_query_close($this->query);
}
