Beispiel #1
3
 public function query($sql)
 {
     $rs = odbc_exec($this->dbConnection(), $sql);
     if (is_resource($rs)) {
         while ($result[] = odbc_fetch_array($rs)) {
         }
         odbc_free_result($rs);
         $this->close();
         return $result;
     } else {
         $this->halt('Database query error', $sql);
     }
 }
Beispiel #2
0
 /**
  * Execute a query and return the results
  * @param string $query
  * @param array $params
  * @param bool $fetchResult Return a ressource or a an array
  * @return resource|array
  * @throws Exception
  */
 public function query($query, $params = null, $fetchResult = false)
 {
     $this->checkConnection(true);
     $this->log('Query: ' . $query);
     if (!empty($params)) {
         $this->log('Params: ' . print_r($params, true));
     }
     $start = microtime(true);
     if (empty($params)) {
         $res = $this->executeQuery($query);
     } else {
         $res = $this->executePreparedStatement($query, $params);
     }
     $end = microtime(true);
     $this->log("Execution time: " . ($end - $start) . " seconds");
     if ($fetchResult) {
         $this->log('Num Rows: ' . odbc_num_rows($res));
         $resutlSet = $this->getRows($res);
         odbc_free_result($res);
         $res = $resutlSet;
         $resultSet = null;
         $fetch = microtime(true);
         $this->log("Fetch time: " . ($fetch - $end) . " seconds");
     }
     return $res;
 }
Beispiel #3
0
    function _import_db()
    {
        //
        $sql = 'SELECT *
			FROM provedor
			ORDER BY nit';
        $result = $this->query($sql);
        while ($row = odbc_fetch_array($result)) {
            $this->p[] = $row;
        }
        odbc_free_result($result);
        unset($row);
        //
        $sql = 'SELECT *
			FROM constancia
			ORDER BY exencion';
        $result = $this->query($sql);
        while ($row = odbc_fetch_array($result)) {
            $this->e[] = $row;
        }
        odbc_free_result($result);
        unset($row);
        //
        $sql = 'SELECT *
			FROM factura
			ORDER BY exencion, factura';
        $result = $this->query($sql);
        while ($row = odbc_fetch_array($result)) {
            $this->f[] = $row;
        }
        odbc_free_result($result);
        //
        return;
    }
 function free_result($res)
 {
     if (odbc_free_result($res)) {
         return true;
     } else {
         return false;
     }
 }
Beispiel #5
0
 /**
  * @see DatabaseInfo::initTables()
  */
 protected function initTables()
 {
     include_once 'creole/drivers/odbc/metadata/ODBCTableInfo.php';
     $result = @odbc_tables($this->conn->getResource());
     if (!$result) {
         throw new SQLException('Could not list tables', $this->conn->nativeError());
     }
     while (odbc_fetch_row($result)) {
         $tablename = strtoupper(odbc_result($result, 'TABLE_NAME'));
         $this->tables[$tablename] = new ODBCTableInfo($this, $tablename);
     }
     @odbc_free_result($result);
 }
 public function index()
 {
     //test the dynamic connection with other databases seems successfull
     $systems = System::all();
     $res = [];
     foreach ($systems as $system) {
         $query = "SELECT [states].eq_id, [states].time, [states].[state_OK],[states].[state_MaintRQ], [states].[state_InMaint], [states].[state_Fault], [equipment].eq_id, CAST(CAST([equipment].eq_name AS VARBINARY) AS VARCHAR) as eq_name FROM [states] LEFT JOIN [equipment] on states.eq_id = [equipment].eq_id";
         if ($system['dbversion'] == '2000') {
             $port = '1434';
             try {
                 $connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=" . $system['host'] . "," . $port . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
             } catch (ErrorException $e) {
                 $system->status = 'default';
                 $system->save();
                 $res[$system['name']] = ['error' => $e];
             }
             if ($conn) {
                 $results = odbc_exec($connection, $query);
                 $realData = [];
                 $i = 0;
                 while ($row = json_decode(json_encode(odbc_fetch_object($results)), true)) {
                     foreach ($row as $key => $item) {
                         if ($key == "eq_name" && is_string($item)) {
                             $row[$key] = iconv('UCS-2LE', 'UTF-8', $item);
                         }
                     }
                     $realData[$i] = $row;
                     $i++;
                 }
                 $res[$system['name']] = $realData;
                 odbc_free_result($results);
                 odbc_close($connection);
             }
         } else {
             try {
                 $conn = new PDO("sqlsrv:Server=" . $system['host'] . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
             } catch (PDOException $e) {
                 $system->status = 'default';
                 $system->save();
                 $res[$system['name']] = ['error' => $e];
             }
             if ($conn) {
                 $sql = $conn->prepare($query);
                 $sql->execute();
                 $res[$system['name']] = $sql->fetchAll();
                 $conn = null;
             }
         }
     }
     return $res;
 }
function odbc_fila($sql, $fila)
{
    $cnn_odbc = odbc_connect("ctw", "", "");
    $rs = odbc_exec($cnn_odbc, $sql);
    $value = "NO_EXISTE";
    $value = odbc_fetch_array($rs, 1);
    odbc_free_result($rs);
    odbc_close($cnn_odbc);
    //if(!$rs){
    //	return odbc_error($rs);
    //} else {
    return $value[$fila];
    //}
}
 /**
  * Internal function to call native ODBC prepare/execute functions.
  */
 protected function _execute($sql, $params, $fetchmode, $isupdate)
 {
     // Set any params passed directly
     if ($params) {
         for ($i = 0, $cnt = count($params); $i < $cnt; $i++) {
             $this->set($i + 1, $params[$i]);
         }
     }
     // Trim surrounding quotes added from default set methods.
     // Exception: for LOB-based parameters, odbc_execute() will
     // accept a filename surrounded by single-quotes.
     foreach ($this->boundInVars as $idx => $var) {
         if ($var instanceof Lob) {
             $file = $isupdate ? $var->getInputFile() : $var->getOutputFile();
             $this->boundInVars[$idx] = "'{$file}'";
         } else {
             if (is_string($var)) {
                 $this->boundInVars[$idx] = trim($var, "\"\\'");
             }
         }
     }
     if ($this->resultSet) {
         $this->resultSet->close();
         $this->resultSet = null;
     }
     $this->updateCount = null;
     $stmt = @odbc_prepare($this->conn->getResource(), $sql);
     if ($stmt === FALSE) {
         throw new SQLException('Could not prepare query', $this->conn->nativeError(), $sql);
     }
     $ret = @odbc_execute($stmt, $this->boundInVars);
     if ($ret === FALSE) {
         @odbc_free_result($stmt);
         throw new SQLException('Could not execute query', $this->conn->nativeError(), $sql);
     }
     return $this->conn->createResultSet(new ODBCResultResource($stmt), $fetchmode);
 }
Beispiel #9
0
 /**
  * Get the rows returned from a SELECT query.
  *
  * @param  resource		The query result pointer
  * @param  ?integer		Whether to start reading from (NULL: irrelevant for this forum driver)
  * @return array			A list of row maps
  */
 function db_get_query_rows($results, $start = NULL)
 {
     $out = array();
     $i = 0;
     $num_fields = odbc_num_fields($results);
     $types = array();
     $names = array();
     for ($x = 1; $x <= $num_fields; $x++) {
         $types[$x] = odbc_field_type($results, $x);
         $names[$x] = odbc_field_name($results, $x);
     }
     while (odbc_fetch_row($results)) {
         if (is_null($start) || $i >= $start) {
             $newrow = array();
             for ($j = 1; $j <= $num_fields; $j++) {
                 $v = odbc_result($results, $j);
                 $type = $types[$j];
                 $name = strtolower($names[$j]);
                 if ($type == 'INTEGER' || $type == 'SMALLINT' || $type == 'UINTEGER') {
                     if (!is_null($v)) {
                         $newrow[$name] = intval($v);
                     } else {
                         $newrow[$name] = NULL;
                     }
                 } else {
                     $newrow[$name] = $v;
                 }
             }
             $out[] = $newrow;
         }
         $i++;
     }
     odbc_free_result($results);
     //	echo '<p>End '.microtime(false);
     return $out;
 }
Beispiel #10
0
 function freeResult($result)
 {
     if (is_resource($result)) {
         // Always return true
         return odbc_free_result($result);
     }
     if (!isset($this->prepare_tokens[(int) $result])) {
         return false;
     }
     unset($this->prepare_tokens[(int) $result]);
     unset($this->prepare_types[(int) $result]);
     return true;
 }
Beispiel #11
0
 function db_free($oStmt)
 {
     return odbc_free_result($oStmt);
 }
Beispiel #12
0
 function freeResult($result)
 {
     unset($this->row[(int) $result]);
     return @odbc_free_result($result);
 }
 /**
  * Free the result
  *
  * @return	void
  */
 public function free_result()
 {
     if (is_resource($this->result_id)) {
         odbc_free_result($this->result_id);
         $this->result_id = FALSE;
     }
 }
Beispiel #14
0
 /**
  * This will not return anything, and will free resources correctly.
  */
 function execute($sql, $ignore_message = null)
 {
     $query = @odbc_exec($this->con, $sql);
     if ($query === false) {
         if (odbc_errormsg() !== $ignore_message) {
             throw new Exception("Query error: " . odbc_error() . ": " . odbc_errormsg() . "\nSQL:\n" . $sql);
         }
     } else {
         odbc_free_result($query);
     }
 }
Beispiel #15
0
 /**
  * Will grab the auto incremented value from the last query (if one exists)
  * 
  * @param  fResult $result  The result object for the query
  * @return void
  */
 private function handleAutoIncrementedValue($result)
 {
     if (!preg_match('#^\\s*INSERT\\s+INTO\\s+(?:`|"|\\[)?(\\w+)(?:`|"|\\])?#i', $result->getSQL(), $table_match)) {
         $result->setAutoIncrementedValue(NULL);
         return;
     }
     $table = strtolower($table_match[1]);
     $insert_id = NULL;
     if ($this->type == 'oracle') {
         if (!isset($this->schema_info['sequences'])) {
             $sql = "SELECT\r\n\t\t\t\t\t\t\t\tTABLE_NAME,\r\n\t\t\t\t\t\t\t\tTRIGGER_BODY\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\tUSER_TRIGGERS\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tTRIGGERING_EVENT = 'INSERT' AND\r\n\t\t\t\t\t\t\t\tSTATUS = 'ENABLED' AND\r\n\t\t\t\t\t\t\t\tTRIGGER_NAME NOT LIKE 'BIN\$%'";
             $this->schema_info['sequences'] = array();
             foreach ($this->query($sql) as $row) {
                 if (preg_match('#SELECT\\s+(\\w+).nextval\\s+INTO\\s+:new\\.(\\w+)\\s+FROM\\s+dual#i', $row['trigger_body'], $matches)) {
                     $this->schema_info['sequences'][strtolower($row['table_name'])] = array('sequence' => $matches[1], 'column' => $matches[2]);
                 }
             }
             if ($this->cache) {
                 $this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
             }
         }
         if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+' . preg_quote($table, '#') . '\\s+\\([^\\)]*?\\b' . preg_quote($this->schema_info['sequences'][$table]['column'], '#') . '\\b#i', $result->getSQL())) {
             return;
         }
         $insert_id_sql = "SELECT " . $this->schema_info['sequences'][$table]['sequence'] . ".currval AS INSERT_ID FROM dual";
     }
     if ($this->type == 'postgresql') {
         if (!isset($this->schema_info['sequences'])) {
             $sql = "SELECT\r\n\t\t\t\t\t\t\t\tpg_class.relname AS table_name,\r\n\t\t\t\t\t\t\t\tpg_attribute.attname AS column\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\tpg_attribute INNER JOIN\r\n\t\t\t\t\t\t\t\tpg_class ON pg_attribute.attrelid = pg_class.oid INNER JOIN\r\n\t\t\t\t\t\t\t\tpg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tNOT pg_attribute.attisdropped AND\r\n\t\t\t\t\t\t\t\tpg_attrdef.adsrc LIKE 'nextval(%'";
             $this->schema_info['sequences'] = array();
             foreach ($this->query($sql) as $row) {
                 $this->schema_info['sequences'][strtolower($row['table_name'])] = $row['column'];
             }
             if ($this->cache) {
                 $this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
             }
         }
         if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+' . preg_quote($table, '#') . '\\s+\\([^\\)]*?\\b' . preg_quote($this->schema_info['sequences'][$table], '#') . '\\b#i', $result->getSQL())) {
             return;
         }
     }
     if ($this->extension == 'mssql') {
         $insert_id_res = mssql_query("SELECT @@IDENTITY AS insert_id", $this->connection);
         $insert_id = mssql_result($insert_id_res, 0, 'insert_id');
         mssql_free_result($insert_id_res);
     } elseif ($this->extension == 'mysql') {
         $insert_id = mysql_insert_id($this->connection);
     } elseif ($this->extension == 'mysqli') {
         $insert_id = mysqli_insert_id($this->connection);
     } elseif ($this->extension == 'oci8') {
         $oci_statement = oci_parse($this->connection, $insert_id_sql);
         oci_execute($oci_statement);
         $insert_id_row = oci_fetch_array($oci_statement, OCI_ASSOC);
         $insert_id = $insert_id_row['INSERT_ID'];
         oci_free_statement($oci_statement);
     } elseif ($this->extension == 'odbc' && $this->type == 'mssql') {
         $insert_id_res = odbc_exec($this->connection, "SELECT @@IDENTITY AS insert_id");
         $insert_id = odbc_result($insert_id_res, 'insert_id');
         odbc_free_result($insert_id_res);
     } elseif ($this->extension == 'odbc' && $this->type == 'oracle') {
         $insert_id_res = odbc_exec($this->connection, $insert_id_sql);
         $insert_id = odbc_result($insert_id_res, 'insert_id');
         odbc_free_result($insert_id_res);
     } elseif ($this->extension == 'pgsql') {
         $insert_id_res = pg_query($this->connection, "SELECT lastval()");
         $insert_id_row = pg_fetch_assoc($insert_id_res);
         $insert_id = array_shift($insert_id_row);
         pg_free_result($insert_id_res);
     } elseif ($this->extension == 'sqlite') {
         $insert_id = sqlite_last_insert_rowid($this->connection);
     } elseif ($this->extension == 'sqlsrv') {
         $insert_id_res = sqlsrv_query($this->connection, "SELECT @@IDENTITY AS insert_id");
         $insert_id_row = sqlsrv_fetch_array($insert_id_res, SQLSRV_FETCH_ASSOC);
         $insert_id = $insert_id_row['insert_id'];
         sqlsrv_free_stmt($insert_id_res);
     } elseif ($this->extension == 'pdo') {
         switch ($this->type) {
             case 'mssql':
                 try {
                     $insert_id_statement = $this->connection->query("SELECT @@IDENTITY AS insert_id");
                     if (!$insert_id_statement) {
                         throw new Exception();
                     }
                     $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
                     $insert_id = array_shift($insert_id_row);
                 } catch (Exception $e) {
                     // If there was an error we don't have an insert id
                 }
                 break;
             case 'oracle':
                 try {
                     $insert_id_statement = $this->connection->query($insert_id_sql);
                     if (!$insert_id_statement) {
                         throw new Exception();
                     }
                     $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
                     $insert_id = array_shift($insert_id_row);
                 } catch (Exception $e) {
                     // If there was an error we don't have an insert id
                 }
                 break;
             case 'postgresql':
                 $insert_id_statement = $this->connection->query("SELECT lastval()");
                 $insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
                 $insert_id = array_shift($insert_id_row);
                 $insert_id_statement->closeCursor();
                 unset($insert_id_statement);
                 break;
             case 'mysql':
                 $insert_id = $this->connection->lastInsertId();
                 break;
             case 'sqlite':
                 $insert_id = $this->connection->lastInsertId();
                 break;
         }
     }
     $result->setAutoIncrementedValue($insert_id);
 }
Beispiel #16
0
 /**
  * Free the memory used by the result resource
  *
  * @return void
  */
 public function free()
 {
     switch ($this->mode) {
         case "mysql":
             $this->result->free();
             break;
         case "postgres":
         case "redshift":
             pg_free_result($this->result);
             break;
         case "odbc":
             odbc_free_result($this->result);
             break;
         case "sqlite":
             $this->result->finalize();
             break;
         case "mssql":
             mssql_free_result($this->result);
             break;
     }
 }
Beispiel #17
0
 /**
  * @return	bool
  */
 function FreeResult()
 {
     if ($this->_resultId) {
         if (!@odbc_free_result($this->_resultId)) {
             $this->_setSqlError();
             return false;
         } else {
             $this->_resultId = null;
         }
         return true;
     } else {
         return true;
     }
 }
function sql_free_result($res)
{
    global $dbtype;
    switch ($dbtype) {
        case "MySQL":
            $row = mysql_free_result($res);
            return $row;
            break;
        case "mSQL":
            $row = msql_free_result($res);
            return $row;
            break;
        case "postgres":
        case "postgres_local":
            $rows = pg_FreeResult($res->get_result());
            return $rows;
            break;
        case "ODBC":
        case "ODBC_Adabas":
            $rows = odbc_free_result($res);
            return $rows;
            break;
        case "Interbase":
            echo "<BR>Error! PHP dosen't support ibase_numrows!<BR>";
            return $rows;
            break;
        case "Sybase":
            $rows = sybase_free_result($res);
            return $rows;
            break;
    }
}
Beispiel #19
0
 function sql_freeresult($query_id = 0)
 {
     if (!$query_id) {
         $query_id = $this->query_result;
     }
     if ($query_id) {
         $result = @odbc_free_result($query_id);
         return $result;
     } else {
         return false;
     }
 }
 /**
  * Build db-specific report
  * @access private
  */
 function _sql_report($mode, $query = '')
 {
     switch ($mode) {
         case 'start':
             break;
         case 'fromcache':
             $endtime = explode(' ', microtime());
             $endtime = $endtime[0] + $endtime[1];
             $result = @odbc_exec($this->db_connect_id, $query);
             while ($void = @odbc_fetch_array($result)) {
                 // Take the time spent on parsing rows into account
             }
             @odbc_free_result($result);
             $splittime = explode(' ', microtime());
             $splittime = $splittime[0] + $splittime[1];
             $this->sql_report('record_fromcache', $query, $endtime, $splittime);
             break;
     }
 }
 function sql_query($query = "", $transaction = FALSE)
 {
     if ($query != "") {
         $this->num_queries++;
         if ($transaction == BEGIN_TRANSACTION && !$this->in_transaction) {
             if (!odbc_autocommit($this->db_connect_id, false)) {
                 return false;
             }
             $this->in_transaction = TRUE;
         }
         if (preg_match("/^SELECT(.*?)(LIMIT ([0-9]+)[, ]*([0-9]+)*)?\$/s", $query, $limits)) {
             $query = $limits[1];
             if (!empty($limits[2])) {
                 $row_offset = $limits[4] ? $limits[3] : "";
                 $num_rows = $limits[4] ? $limits[4] : $limits[3];
                 $query = "TOP " . ($row_offset + $num_rows) . $query;
             }
             $this->result = odbc_exec($this->db_connect_id, "SELECT {$query}");
             if ($this->result) {
                 if (empty($this->field_names[$this->result])) {
                     for ($i = 1; $i < odbc_num_fields($this->result) + 1; $i++) {
                         $this->field_names[$this->result][] = odbc_field_name($this->result, $i);
                         $this->field_types[$this->result][] = odbc_field_type($this->result, $i);
                     }
                 }
                 $this->current_row[$this->result] = 0;
                 $this->result_rowset[$this->result] = array();
                 $row_outer = isset($row_offset) ? $row_offset + 1 : 1;
                 $row_outer_max = isset($num_rows) ? $row_offset + $num_rows + 1 : 1000000000.0;
                 $row_inner = 0;
                 while (odbc_fetch_row($this->result, $row_outer) && $row_outer < $row_outer_max) {
                     for ($j = 0; $j < count($this->field_names[$this->result]); $j++) {
                         $this->result_rowset[$this->result][$row_inner][$this->field_names[$this->result][$j]] = stripslashes(odbc_result($this->result, $j + 1));
                     }
                     $row_outer++;
                     $row_inner++;
                 }
                 $this->num_rows[$this->result] = count($this->result_rowset[$this->result]);
             }
         } else {
             if (eregi("^INSERT ", $query)) {
                 $this->result = odbc_exec($this->db_connect_id, $query);
                 if ($this->result) {
                     $result_id = odbc_exec($this->db_connect_id, "SELECT @@IDENTITY");
                     if ($result_id) {
                         if (odbc_fetch_row($result_id)) {
                             $this->next_id[$this->db_connect_id] = odbc_result($result_id, 1);
                             $this->affected_rows[$this->db_connect_id] = odbc_num_rows($this->result);
                         }
                     }
                 }
             } else {
                 $this->result = odbc_exec($this->db_connect_id, $query);
                 if ($this->result) {
                     $this->affected_rows[$this->db_connect_id] = odbc_num_rows($this->result);
                 }
             }
         }
         if (!$this->result) {
             if ($this->in_transaction) {
                 odbc_rollback($this->db_connect_id);
                 odbc_autocommit($this->db_connect_id, true);
                 $this->in_transaction = FALSE;
             }
             return false;
         }
         if ($transaction == END_TRANSACTION && $this->in_transaction) {
             $this->in_transaction = FALSE;
             if (!odbc_commit($this->db_connect_id)) {
                 odbc_rollback($this->db_connect_id);
                 odbc_autocommit($this->db_connect_id, true);
                 return false;
             }
             odbc_autocommit($this->db_connect_id, true);
         }
         odbc_free_result($this->result);
         return $this->result;
     } else {
         if ($transaction == END_TRANSACTION && $this->in_transaction) {
             $this->in_transaction = FALSE;
             if (!@odbc_commit($this->db_connect_id)) {
                 odbc_rollback($this->db_connect_id);
                 odbc_autocommit($this->db_connect_id, true);
                 return false;
             }
             odbc_autocommit($this->db_connect_id, true);
         }
         return true;
     }
 }
 /**
  * @see TableInfo::initForeignKeys()
  */
 protected function initForeignKeys()
 {
     // columns have to be loaded first
     if (!$this->colsLoaded) {
         $this->initColumns();
     }
     $result = @odbc_foreignkeys($this->dblink, '', '', '', $this->dbname, '', $this->name);
     while (odbc_fetch_row($result)) {
         $name = odbc_result($result, 'COLUMN_NAME');
         $ftbl = odbc_result($result, 'FKTABLE_NAME');
         $fcol = odbc_result($result, 'FKCOLUMN_NAME');
         if (!isset($this->foreignKeys[$name])) {
             $this->foreignKeys[$name] = new ForeignKeyInfo($name);
             if (($foreignTable = $this->database->getTable($ftbl)) === null) {
                 $foreignTable = new TableInfo($ltbl);
                 $this->database->addTable($foreignTable);
             }
             if (($foreignCol = $foreignTable->getColumn($name)) === null) {
                 $foreignCol = new ColumnInfo($foreignTable, $name);
                 $foreignTable->addColumn($foreignCol);
             }
             $this->foreignKeys[$name]->addReference($this->columns[$name], $foreignCol);
         }
     }
     @odbc_free_result($result);
     $this->fksLoaded = true;
 }
Beispiel #23
0
 function _close()
 {
     return @odbc_free_result($this->_queryID);
 }
 private function getSytemIinfo($id)
 {
     $system = System::find($id);
     $queryMeas = "SELECT [meas].param_id, [meas].time, [meas].value, [param].param_name, [param].param_unit, [limit].ad_limit FROM [meas] LEFT JOIN [param] on meas.param_id = [param].param_id LEFT JOIN [limit] on meas.param_id = [limit].param_id";
     $queryEquipment2000 = "SELECT [time] ,st.[eq_id],[state_OK],[state_MaintRQ],[state_InMaint],[state_Fault] ,CAST(CAST([eq_name] AS VARBINARY) AS VARCHAR) as eq_name FROM [states] st JOIN [equipment] eq ON  st.eq_id = eq.eq_id";
     $queryEquipment = "SELECT [time] ,st.[eq_id],[state_OK],[state_MaintRQ],[state_InMaint],[state_Fault], [eq_name] FROM [states] st JOIN [equipment] eq ON  st.eq_id = eq.eq_id";
     $res;
     $meas;
     $eq;
     $conn = false;
     if ($system['status'] != 'default') {
         if ($system['dbversion'] == '2000') {
             $port = '1434';
             try {
                 $conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=" . $system['host'] . "," . $port . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
             } catch (ErrorException $e) {
                 $res[$system['name']] = ['error' => 'Connection Error'];
                 $res[$system['name']]['meas'] = [];
                 $res[$system['name']]['equipment'] = [];
             }
             if ($conn) {
                 $results = odbc_exec($conn, $queryMeas);
                 $realDataMeas = [];
                 $i = 0;
                 while ($row = odbc_fetch_object($results)) {
                     $row->status = $row->param_name != "O2" && $row->ad_limit && $row->value > $row->ad_limit ? 'warning' : 'success';
                     $realDataMeas[$i] = $row;
                     $i++;
                 }
                 $meas = json_decode(json_encode($realDataMeas), true);
                 odbc_free_result($results);
                 $results = odbc_exec($conn, $queryEquipment2000);
                 $realDataEq = [];
                 $i = 0;
                 $state;
                 while ($row = json_decode(json_encode(odbc_fetch_object($results)), true)) {
                     foreach ($row as $key => $item) {
                         if ($key == "eq_name" && is_string($item)) {
                             $row[$key] = iconv('UCS-2LE', 'UTF-8', $item);
                         }
                     }
                     $realDataEq[$i] = $row;
                     $i++;
                 }
                 $eq = $realDataEq;
                 odbc_free_result($results);
                 odbc_close($conn);
                 $res[$system['name']] = ['meas' => $meas, 'equipment' => $eq];
             }
         } else {
             try {
                 $conn = new PDO("sqlsrv:Server=" . $system['host'] . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
             } catch (PDOException $e) {
                 $res[$system['name']] = ['error' => 'Connection Error'];
                 $res[$system['name']]['meas'] = [];
                 $res[$system['name']]['equipment'] = [];
             }
             if ($conn) {
                 $sql = $conn->prepare($queryMeas);
                 $sql->execute();
                 $meas = $sql->fetchAll();
                 foreach ($meas as $key => $result) {
                     $result['status'] = $result['param_name'] != 'O2' && $result['ad_limit'] && $result['value'] > $result['ad_limit'] ? 'warning' : 'success';
                     $meas[$key] = $result;
                 }
                 $sql = $conn->prepare($queryEquipment);
                 $sql->execute();
                 $eq = $sql->fetchAll();
                 $conn = null;
                 $res[$system['name']] = ['meas' => $meas, 'equipment' => $eq];
             }
         }
     } else {
         $res[$system['name']] = ['error' => 'Connection Error'];
         $res[$system['name']]['meas'] = [];
         $res[$system['name']]['equipment'] = [];
     }
     return $res;
 }
Beispiel #25
0
/**
 * Frees a result set.
 *
 * @param resource $res The database query resource returned from
 *                      the {@link dbi_query()} function.
 *
 * @return bool True on success
 */
function dbi_free_result($res)
{
    if (strcmp($GLOBALS["db_type"], "mysql") == 0) {
        return mysql_free_result($res);
    } else {
        if (strcmp($GLOBALS["db_type"], "mysqli") == 0) {
            return mysqli_free_result($res);
        } else {
            if (strcmp($GLOBALS["db_type"], "mssql") == 0) {
                return mssql_free_result($res);
            } else {
                if (strcmp($GLOBALS["db_type"], "oracle") == 0) {
                    // Not supported.  Ingore.
                    if ($GLOBALS["oracle_statement"] >= 0) {
                        OCIFreeStatement($GLOBALS["oracle_statement"]);
                        $GLOBALS["oracle_statement"] = -1;
                    }
                } else {
                    if (strcmp($GLOBALS["db_type"], "postgresql") == 0) {
                        return pg_freeresult($res);
                    } else {
                        if (strcmp($GLOBALS["db_type"], "odbc") == 0) {
                            return odbc_free_result($res);
                        } else {
                            if (strcmp($GLOBALS["db_type"], "ibm_db2") == 0) {
                                return db2_free_result($res);
                            } else {
                                if (strcmp($GLOBALS["db_type"], "ibase") == 0) {
                                    return ibase_free_result($res);
                                } else {
                                    dbi_fatal_error("dbi_free_result(): db_type not defined.");
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}
 /**
  * Loads the map of ODBC data types to Creole (JDBC) types.
  *
  * NOTE: This function cannot map DBMS-specific datatypes. If you use a
  *       driver which implements DBMS-specific datatypes, you will need
  *       to modify/extend this class to add the correct mapping.
  */
 public static function loadTypeMap($conn = null)
 {
     if (self::$typeMap !== null && count(self::$typeMap) > 0) {
         return;
     }
     if ($conn == null) {
         throw new SQLException('No connection specified when loading ODBC type map.');
     }
     self::$typeMap = array();
     $result = @odbc_gettypeinfo($conn->getResource());
     if ($result === false) {
         throw new SQLException('Failed to retrieve type info.', $conn->nativeError());
     }
     $rowNum = 1;
     while (odbc_fetch_row($result, $rowNum++)) {
         $odbctypeid = odbc_result($result, 'DATA_TYPE');
         $odbctypename = odbc_result($result, 'TYPE_NAME');
         switch ($odbctypeid) {
             case SQL_CHAR:
                 self::$typeMap[$odbctypename] = CreoleTypes::CHAR;
                 break;
             case SQL_VARCHAR:
                 self::$typeMap[$odbctypename] = CreoleTypes::VARCHAR;
                 break;
             case SQL_LONGVARCHAR:
                 self::$typeMap[$odbctypename] = CreoleTypes::LONGVARCHAR;
                 break;
             case SQL_DECIMAL:
                 self::$typeMap[$odbctypename] = CreoleTypes::DECIMAL;
                 break;
             case SQL_NUMERIC:
                 self::$typeMap[$odbctypename] = CreoleTypes::NUMERIC;
                 break;
             case SQL_BIT:
                 self::$typeMap[$odbctypename] = CreoleTypes::BOOLEAN;
                 break;
             case SQL_TINYINT:
                 self::$typeMap[$odbctypename] = CreoleTypes::TINYINT;
                 break;
             case SQL_SMALLINT:
                 self::$typeMap[$odbctypename] = CreoleTypes::SMALLINT;
                 break;
             case SQL_INTEGER:
                 self::$typeMap[$odbctypename] = CreoleTypes::INTEGER;
                 break;
             case SQL_BIGINT:
                 self::$typeMap[$odbctypename] = CreoleTypes::BIGINT;
                 break;
             case SQL_REAL:
                 self::$typeMap[$odbctypename] = CreoleTypes::REAL;
                 break;
             case SQL_FLOAT:
                 self::$typeMap[$odbctypename] = CreoleTypes::FLOAT;
                 break;
             case SQL_DOUBLE:
                 self::$typeMap[$odbctypename] = CreoleTypes::DOUBLE;
                 break;
             case SQL_BINARY:
                 self::$typeMap[$odbctypename] = CreoleTypes::BINARY;
                 break;
             case SQL_VARBINARY:
                 self::$typeMap[$odbctypename] = CreoleTypes::VARBINARY;
                 break;
             case SQL_LONGVARBINARY:
                 self::$typeMap[$odbctypename] = CreoleTypes::LONGVARBINARY;
                 break;
             case SQL_DATE:
                 self::$typeMap[$odbctypename] = CreoleTypes::DATE;
                 break;
             case SQL_TIME:
                 self::$typeMap[$odbctypename] = CreoleTypes::TIME;
                 break;
             case SQL_TIMESTAMP:
                 self::$typeMap[$odbctypename] = CreoleTypes::TIMESTAMP;
                 break;
             case SQL_TYPE_DATE:
                 self::$typeMap[$odbctypename] = CreoleTypes::DATE;
                 break;
             case SQL_TYPE_TIME:
                 self::$typeMap[$odbctypename] = CreoleTypes::TIME;
                 break;
             case SQL_TYPE_TIMESTAMP:
                 self::$typeMap[$odbctypename] = CreoleTypes::TIMESTAMP;
                 break;
             default:
                 self::$typeMap[$odbctypename] = CreoleTypes::OTHER;
                 break;
         }
     }
     @odbc_free_result($result);
 }
Beispiel #27
0
 /**
  * Returns information about a table or a result set
  *
  * @param object|string  $result  DB_result object from a query or a
  *                                 string containing the name of a table.
  *                                 While this also accepts a query result
  *                                 resource identifier, this behavior is
  *                                 deprecated.
  * @param int            $mode    a valid tableInfo mode
  *
  * @return array  an associative array with the information requested.
  *                 A DB_Error object on failure.
  *
  * @see DB_common::tableInfo()
  * @since Method available since Release 1.7.0
  */
 function tableInfo($result, $mode = null)
 {
     if (is_string($result)) {
         /*
          * Probably received a table name.
          * Create a result resource identifier.
          */
         $id = @odbc_exec($this->connection, "SELECT * FROM {$result}");
         if (!$id) {
             return $this->odbcRaiseError();
         }
         $got_string = true;
     } elseif (isset($result->result)) {
         /*
          * Probably received a result object.
          * Extract the result resource identifier.
          */
         $id = $result->result;
         $got_string = false;
     } else {
         /*
          * Probably received a result resource identifier.
          * Copy it.
          * Deprecated.  Here for compatibility only.
          */
         $id = $result;
         $got_string = false;
     }
     if (!is_resource($id)) {
         return $this->odbcRaiseError(DB_ERROR_NEED_MORE_DATA);
     }
     if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
         $case_func = 'strtolower';
     } else {
         $case_func = 'strval';
     }
     $count = @odbc_num_fields($id);
     $res = array();
     if ($mode) {
         $res['num_fields'] = $count;
     }
     for ($i = 0; $i < $count; $i++) {
         $col = $i + 1;
         $res[$i] = array('table' => $got_string ? $case_func($result) : '', 'name' => $case_func(@odbc_field_name($id, $col)), 'type' => @odbc_field_type($id, $col), 'len' => @odbc_field_len($id, $col), 'flags' => '');
         if ($mode & DB_TABLEINFO_ORDER) {
             $res['order'][$res[$i]['name']] = $i;
         }
         if ($mode & DB_TABLEINFO_ORDERTABLE) {
             $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
         }
     }
     // free the result only if we were called on a table
     if ($got_string) {
         @odbc_free_result($id);
     }
     return $res;
 }
Beispiel #28
0
 $rsSQLS = odbc_exec($conn, "SELECT Count(*) AS contador_abm FROM ThirdParties WHERE DocumentNumber = '{$DocumentNumber}'");
 $result = odbc_fetch_array($rsSQLS);
 odbc_free_result($rsSQLS);
 if ($result['contador_abm'] >= 1) {
     //manejamos una variable para enviar los mensajes
     $mensaje = "";
     $rs_datos_abm = odbc_exec($conn, "SELECT \n\t\t\t\t\t\t\t\t\t\tPhoneNumber,\n\t\t\t\t\t\t\t\t\t\tFaxNumber,\n\t\t\t\t\t\t\t\t\t\tAddress,\n\t\t\t\t\t\t\t\t\t\tMobilePhoneNumber,\n\t\t\t\t\t\t\t\t\t\tEmail,\n\t\t\t\t\t\t\t\t\t\tPOBox,\n\t\t\t\t\t\t\t\t\t\tCityIataCode\n\t\t\t\t\t\t\t\t\tFROM ThirdParties WHERE DocumentNumber = '{$DocumentNumber}'");
     $result_abm = odbc_fetch_array($rs_datos_abm);
     $telefono_abm = $result_abm['PhoneNumber'];
     $fax_abm = $result_abm['FaxNumber'];
     $celular_abm = $result_abm['MobilePhoneNumber'];
     $email_abm = $result_abm['Email'];
     $direccion_abm = $result_abm['Address'];
     $cod_postal_abm = $result_abm['POBox'];
     $city_iata_abm = $result_abm['CityIataCode'];
     odbc_free_result($rs_datos_abm);
     echo "<br> <b> Ya existe el registro en el ABM :..</b><br>";
     //Actualizacion de datos si son diferentes, en vtiger al ABM...
     if ($telefono_vtiger != $telefono_abm) {
         // Actualizacion de Tlf
         $update_telefono = odbc_exec($conn, "UPDATE ThirdParties SET [PhoneNumber] = '{$telefono_vtiger}'\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE [DocumentNumber] = '{$DocumentNumber}'");
         $mensaje = "<br>- Se actualiz&oacute; el Nro. de Tel&eacute;fono del Contacto. <br>";
     }
     if ($fax_vtiger != $fax_abm) {
         // Actualizacion de fax
         $update_fax = odbc_exec($conn, "UPDATE ThirdParties SET \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t[FaxNumber] = '{$fax_vtiger}'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE [DocumentNumber] = '{$DocumentNumber}'");
         $mensaje .= "<br>- Se actualiz&oacute; el Nro. de Fax del Contacto. <br>";
     }
     if ($celular_vtiger != $celular_abm) {
         // Actualizacion de celular
         $update_celular = odbc_exec($conn, "UPDATE ThirdParties SET \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t[MobilePhoneNumber] = '{$celular_vtiger}'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE [DocumentNumber] = '{$DocumentNumber}'");
Beispiel #29
0
 /**
  * Returns metadata for all columns in a table.
  * @param  string
  * @return array
  */
 public function getColumns($table)
 {
     $result = odbc_columns($this->connection);
     $res = array();
     while ($row = odbc_fetch_array($result)) {
         if ($row['TABLE_NAME'] === $table) {
             $res[] = array('name' => $row['COLUMN_NAME'], 'table' => $table, 'nativetype' => $row['TYPE_NAME'], 'size' => $row['COLUMN_SIZE'], 'nullable' => (bool) $row['NULLABLE'], 'default' => $row['COLUMN_DEF']);
         }
     }
     odbc_free_result($result);
     return $res;
 }
Beispiel #30
0
 /**
  * Define the application's command schedule.
  *
  * @param  \Illuminate\Console\Scheduling\Schedule  $schedule
  * @return void
  */
 protected function schedule(Schedule $schedule)
 {
     $schedule->call(function () {
         $systems = System::all();
         foreach ($systems as $key => $system) {
             $queryMeas = "SELECT [meas].param_id, [meas].time, [meas].value, [param].param_name, [param].param_unit, [limit].ad_limit FROM [meas] LEFT JOIN [param] on meas.param_id = [param].param_id LEFT JOIN [limit] on meas.param_id = [limit].param_id";
             $queryEquipment2000 = "SELECT [time] ,st.[eq_id],[state_OK],[state_MaintRQ],[state_InMaint],[state_Fault] ,CAST(CAST([eq_name] AS VARBINARY) AS VARCHAR) as eq_name FROM [states] st JOIN [equipment] eq ON  st.eq_id = eq.eq_id";
             $queryEquipment = "SELECT [time] ,st.[eq_id],[state_OK],[state_MaintRQ],[state_InMaint],[state_Fault], [eq_name] FROM [states] st JOIN [equipment] eq ON  st.eq_id = eq.eq_id";
             $meas;
             $eq;
             $conn = false;
             if ($system['dbversion'] == '2000') {
                 $port = '1434';
                 try {
                     $conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=" . $system['host'] . "," . $port . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
                 } catch (ErrorException $e) {
                     $system->status = 'default';
                     $system->save();
                 }
                 if ($conn) {
                     $sysStatus = 'success';
                     $results = odbc_exec($conn, $queryMeas);
                     $realDataMeas = [];
                     $i = 0;
                     while ($row = odbc_fetch_object($results)) {
                         $row->status = $row->param_name != "O2" && $row->ad_limit && $row->value > $row->ad_limit ? 'warning' : 'success';
                         $sysStatus = $sysStatus != $row->status && $row->status == 'warning' ? $row->status : $sysStatus;
                         $realDataMeas[$i] = $row;
                         $i++;
                     }
                     $meas = json_decode(json_encode($realDataMeas), true);
                     odbc_free_result($results);
                     $results = odbc_exec($conn, $queryEquipment2000);
                     $realDataEq = [];
                     $i = 0;
                     while ($row = json_decode(json_encode(odbc_fetch_object($results)), true)) {
                         if ($row['state_Fault'] == 1 && $sysStatus != 'error') {
                             $sysStatus = 'error';
                         }
                         if ($sysStatus != 'error' && ($row['state_InMaint'] == 1 || $row['state_MaintRQ'] == 1)) {
                             $sysStatus = 'warning';
                         }
                         if ($sysStatus != 'warning' && $sysStatus != 'error' && $row['state_OK'] == 1) {
                             $sysStatus = 'success';
                         }
                         foreach ($row as $key => $item) {
                             if ($key == "eq_name" && is_string($item)) {
                                 $row[$key] = iconv('UCS-2LE', 'UTF-8', $item);
                             }
                         }
                         $realDataEq[$i] = $row;
                         $i++;
                     }
                     $system->status = $sysStatus;
                     $system->save();
                     odbc_free_result($results);
                     odbc_close($conn);
                 }
             } else {
                 try {
                     $conn = new PDO("sqlsrv:Server=" . $system['host'] . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
                 } catch (PDOException $e) {
                     $system->status = 'default';
                     $system->save();
                 }
                 if ($conn) {
                     $sysStatus = 'success';
                     $sql = $conn->prepare($queryMeas);
                     $sql->execute();
                     $meas = $sql->fetchAll();
                     foreach ($meas as $key => $row) {
                         $row['status'] = $row['param_name'] != 'O2' && $row['ad_limit'] && $row['value'] > $row['ad_limit'] ? 'warning' : 'success';
                         $sysStatus = $sysStatus != $row['status'] && $row['status'] == 'warning' ? $row['status'] : $sysStatus;
                         $meas[$key] = $row;
                     }
                     $sql = $conn->prepare($queryEquipment);
                     $sql->execute();
                     $eq = $sql->fetchAll();
                     foreach ($eq as $key => $row) {
                         if ($row['state_Fault'] == 1 && $sysStatus != 'error') {
                             $sysStatus = 'error';
                         }
                         if ($sysStatus != 'error' && ($row['state_InMaint'] == 1 || $row['state_MaintRQ'] == 1)) {
                             $sysStatus = 'warning';
                         }
                         if ($sysStatus != 'warning' && $sysStatus != 'error' && $row['state_OK'] == 1) {
                             $sysStatus = 'success';
                         }
                         $eq[$key] = $row;
                     }
                     $system->status = $sysStatus;
                     $system->save();
                     $conn = null;
                 }
             }
         }
     })->everyMinute();
 }