public function readCursor($storedProcedure, $binds)
{
//
// This function needs two parameters:
//
// $storedProcedure - the name of the stored procedure to call a chamar. Ex:
// my_schema.my_package.my_proc(:param)
//
// $binds - receives an array of associative arrays with: parameter names,
// values and sizes
//
// WARNING: The first parameter must be consistent with the second one
$conn = oci_connect('SECMAN', 'SECMAN', '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.10.24)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = cisqa)))');
if ($conn) {
// Create the statement and bind the variables (parameter, value, size)
$stid = oci_parse($conn, 'begin :cursor := ' . $storedProcedure . '; end;');
foreach ($binds as $variable) {
oci_bind_by_name($stid, $variable["parameter"], $variable["value"], $variable["size"]);
}
// Create the cursor and bind it
$p_cursor = oci_new_cursor($conn);
oci_bind_by_name($stid, ':cursor', $p_cursor, -1, OCI_B_CURSOR);
// Execute the Statement and fetch the data
oci_execute($stid);
oci_execute($p_cursor, OCI_DEFAULT);
oci_fetch_all($p_cursor, $data, null, null, OCI_FETCHSTATEMENT_BY_ROW);
// Return the data
return $data;
}
}
public static function conn_db($dbdata)
{
if (is_array($dbdata)) {
extract($dbdata[Db::$dbconn]);
Db::$db = @$dbtype;
}
$error = '';
switch (Db::$db) {
case 'Pg':
$con = pg_connect("host={$host} port={$port} dbname={$database} user={$user} password={$password}") or die("{$error}=" . pg_result_error());
break;
case 'Mysql':
$con = mysql_connect($host . ':' . $port, $user, $password);
mysql_select_db($database) or die("{$error}=" . mysql_error());
break;
case 'Oci':
$con = oci_connect($user, $password, $host . ':' . $port . '/' . $database);
if (!$con) {
$error = oci_error();
trigger_error(htmlentities($error['message'], ENT_QUOTES), E_USER_ERROR);
}
break;
}
if ($error != '') {
$con = $error;
}
return $con;
}
function get_filteredGames($data)
{
// The connection string is loooooooong. It's easiest to copy/paste this line. Remember to replace 'username' and 'password'!
$conn = oci_connect('malz', '1Qaz2wsx', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=db1.chpc.ndsu.nodak.edu)(Port=1521)))(CONNECT_DATA=(SID=cs)))');
if ($data === 'all') {
$results = array();
$query = 'select * from Game';
$stid = oci_parse($conn, $query);
oci_bind_by_name($stid, ':data', $data);
oci_execute($stid);
//iterate through each row
while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
$results[] = $row;
}
echo json_encode($results);
oci_free_statement($stid);
oci_close($conn);
} else {
$results = array();
$data = $data . '%';
$query = 'select * from Game where gameName like :data';
$stid = oci_parse($conn, $query);
oci_bind_by_name($stid, ':data', $data);
oci_execute($stid);
//iterate through each row
while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
$results[] = $row;
}
echo json_encode($results);
oci_free_statement($stid);
oci_close($conn);
}
}
public function connection($user, $password, $database)
{
$this->conn = oci_connect($user, $password, $database);
if (!$this->conn) {
throw new Exception("Falla en la coneccion a la base de datos [" . $this->name . "]", 1);
}
}
public static function connectionobject($host, $db, $user, $password)
{
if (self::$usepdo) {
/*$db = "
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
";*/
return new \PDO("oci:dbname=" . $db, $user, $password);
} else {
/*$db = "
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
";*/
$ob = oci_connect($user, $password, $db);
self::$ob[self::$database_in_use] = $ob;
if (!$ob) {
self::oci8_debug($ob, $str_sql);
}
return $ob;
}
}
public function testConnection($db_info)
{
if (!$this->isValid($db_info)) {
return false;
}
foreach ($db_info as $key => $value) {
if (empty($db_info[$key])) {
unset($db_info[$key]);
}
}
if ($db_info['db_oracle_type'] == 'tns') {
$connect = @oci_connect($db_info['db_user'], $db_info['db_password'], $db_info['db_net_service_name']);
if (!$connect) {
$error = oci_error();
throw new \Exception($error['message'], $error['code']);
}
} else {
$dsn = $db_info['db_host'];
$dsn .= ':' . $db_info['db_port'];
$dsn .= '/' . $db_info['db_service_name'];
$connect = @oci_connect($db_info['db_user'], $db_info['db_password'], $dsn);
if (!$connect) {
$error = oci_error();
throw new \Exception($error['message'], $error['code']);
}
}
return true;
}
/**
* Creates a connection resource.
*
* @return void
* @throws Zend_Db_Adapter_Oracle_Exception
*/
protected function _connect()
{
if (is_resource($this->_connection)) {
// connection already exists
return;
}
if (!extension_loaded('oci8')) {
/**
* @see Zend_Db_Adapter_Oracle_Exception
*/
require_once 'Zend/Db/Adapter/Oracle/Exception.php';
throw new Zend_DB_Adapter_Oracle_Exception('The OCI8 extension is required for this adapter but not loaded');
}
if (isset($this->_config['dbname'])) {
$this->_connection = @oci_connect($this->_config['username'], $this->_config['password'], $this->_config['dbname']);
} else {
$this->_connection = oci_connect($this->_config['username'], $this->_config['password']);
}
// check the connection
if (!$this->_connection) {
/**
* @see Zend_Db_Adapter_Oracle_Exception
*/
require_once 'Zend/Db/Adapter/Oracle/Exception.php';
throw new Zend_Db_Adapter_Oracle_Exception(oci_error());
}
}
/**
* Make connection
*
* @return object value
*/
public function connect()
{
if (self::$connection === NULL) {
self::$connection = oci_connect(_dbUser, _dbPwd, _dbHost . ":" . _dbPort . "/" . _dbName);
}
return self::$connection;
}
function purchaseTicket()
{
$passenger = unserialize($_SESSION['passengerDetails']);
$flightDetails = unserialize($_SESSION['flightDetailsForBooking']);
$flightDetails->setFare($_SESSION['lastPurchased']);
$userId = $_SESSION['userId'];
$modeOfPayment = $_SESSION['modeOfPayment'];
ini_set('display_errors', 'On');
$db = "w4111c.cs.columbia.edu:1521/adb";
$conn = oci_connect("kpg2108", "test123", $db);
$ticketid = rand();
$stmt = "insert into ticket values('" . $ticketid . "','" . rand() . "','" . $flightDetails->getFlightClassId() . "','" . $userId . "','" . date('m/d/Y') . "','" . $flightDetails->getFlightMiles() . "')";
$stmt1 = oci_parse($conn, $stmt);
$result2 = oci_execute($stmt1);
$stmt2 = "insert into passenger values('" . rand() . "','" . $ticketid . "','" . $passenger->getfname() . "','" . $passenger->getlname() . "','" . $passenger->getage() . "')";
$stmt3 = oci_parse($conn, $stmt2);
$result3 = oci_execute($stmt3);
$stmt4 = "insert into payment values('" . rand() . "','" . $ticketid . "','" . $modeOfPayment . "','" . $flightDetails->getfare() . "','" . date('m/d/Y') . "')";
$stmt5 = oci_parse($conn, $stmt4);
$result2 = oci_execute($stmt5);
$stmtUpdate = "update users set Miles='" . $_SESSION['userMiles'] . "' where user_id='" . $_SESSION['userId'] . "'";
$stmtUpdate1 = oci_parse($conn, $stmtUpdate);
$result3 = oci_execute($stmtUpdate1);
$stmt9 = "update flight_class set no_of_seats ='" . ($flightDetails->getSeatsAvailable() - 1) . "' where flight_class_id='" . $flightDetails->getFlightClassId() . "'";
$stmtUpdate2 = oci_parse($conn, $stmt9);
$result4 = oci_execute($stmtUpdate2);
oci_close($conn);
if ($result2) {
return 1;
}
}
/**
* Connect to a database
* @throws Doctrine_Adapter_Exception
* @return void
*/
private function connect()
{
$this->connection = @oci_connect($this->config['username'], $this->config['password'], $this->config['dbname'], $this->config['charset']);
if ($this->connection === false) {
throw new Doctrine_Adapter_Exception(sprintf("Unable to Connect to :'%s' as '%s'", $this->config['dbname'], $this->config['username']));
}
}
function connect()
{
if (0 == $this->Link_ID) {
if ($this->Debug) {
printf("<br>Connecting to {$this->Database}%s...<br>\n", $this->Host ? " ({$this->Host})" : "");
}
if ($this->share_connections) {
if (!$this->share_connection_name) {
$this->share_connection_name = get_class($this) . "_Link_ID";
} else {
$this->share_connection_name .= "_Link_ID";
}
global ${$this->share_connection_name};
if (${$this->share_connection_name}) {
$this->Link_ID = ${$this->share_connection_name};
return true;
}
}
if ($this->persistent) {
$this->Link_ID = oci_pconnect($this->User, $this->Password, $this->Host ? sprintf($this->full_connection_string, $this->Host, $this->Port, $this->Database) : $this->Database, 'AL32UTF8');
} else {
$this->Link_ID = oci_connect($this->User, $this->Password, $this->Host ? sprintf($this->full_connection_string, $this->Host, $this->Port, $this->Database) : $this->Database, 'AL32UTF8');
}
if (!$this->Link_ID) {
$this->connect_failed();
return false;
}
if ($this->share_connections) {
${$this->share_connection_name} = $this->Link_ID;
}
if ($this->Debug) {
printf("<br>Obtained the Link_ID: {$this->Link_ID}<br>\n");
}
}
}
public function connect()
{
if (is_null(self::$connection)) {
self::$connection = oci_connect(DB_HOST, DB_USER, DB_PWD . ":" . DB_PORT . "/" . DB_DATABASE);
}
return self::$connection;
}
/**
* Připojí k vybrané databázi dle konstruktoru.
*/
function Connect()
{
// připojení k DB provedu dle požadovaného typu
if ($this->connection_type == DB_CONNECTION_USE_PDO_MYSQL) {
// PDO - MySQL
try {
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
$this->connection = new PDO("mysql:host=" . MYSQL_DATABASE_SERVER . ";dbname=" . MYSQL_DATABASE_NAME . "", MYSQL_DATABASE_USER, MYSQL_DATABASE_PASSWORD, $options);
// nastavit pripojeni na UTF-8 - pro starsi verze PHP
//$this->connection->exec("SET NAMES UTF8");
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die;
}
} else {
// DIRECT Oracle
// $kodovani - 'EE8MSWIN1250' = Windows 1250
//$kodovani = 'EE8MSWIN1250'; // CP-1250
$kodovani = 'AL32UTF8';
// UTF-8
$this->connection = oci_connect(ORACLE_DATABASE_USER, ORACLE_DATABASE_PASSWORD, ORACLE_DATABASE_NAME, $kodovani);
// pomocna chyba
$chyba = oci_error();
if ($chyba != null) {
echo "Chyba při připojení k DB: ";
printr($chyba);
}
}
}
/**
* Class constructor
*
* @param string $data the connection string
* @param string $username user name
* @param string $password password
* @param string $options options to send to the connection
*
* @return \PDO object
* @throws \PDOException
*/
public function __construct($data, $username, $password, $options = null)
{
if (!function_exists("\\oci_parse")) {
throw new \PDOException("No support for Oracle, please install the OCI driver");
}
// find charset
$charset = null;
$data = preg_replace('/^oci:/', '', $data);
$tokens = preg_split('/;/', $data);
$data = str_replace(array('dbname=//', 'dbname='), '', $tokens[0]);
$charset = $this->_getCharset($tokens);
try {
if (!is_null($options) && array_key_exists(\PDO::ATTR_PERSISTENT, $options)) {
$this->_con = \oci_pconnect($username, $password, $data, $charset);
$this->setError();
} else {
$this->_con = \oci_connect($username, $password, $data, $charset);
$this->setError();
}
if (!$this->_con) {
$error = oci_error();
throw new \Exception($error['code'] . ': ' . $error['message']);
}
} catch (\Exception $exception) {
throw new \PDOException($exception->getMessage());
}
return $this;
}
/**
* Constructor
*
* @param string $dsn
* @param string $username
* @param string $passwd
* @param array $options
* @return void
*/
public function __construct($dsn, $username = null, $password = null, array $options = array())
{
//Parse the DSN
$parsedDsn = self::parseDsn($dsn, array('charset'));
//Get SID name
$sidString = isset($parsedDsn['sid']) ? '(SID = ' . $parsedDsn['sid'] . ')' : '';
//Create a description to locate the database to connect to
$description = '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ' . $parsedDsn['hostname'] . ')
(PORT = ' . $parsedDsn['port'] . '))
)
(CONNECT_DATA =
' . $sidString . '
(SERVICE_NAME = ' . $parsedDsn['dbname'] . ')
)
)';
//Attempt a connection
if (isset($options[\PDO::ATTR_PERSISTENT]) && $options[\PDO::ATTR_PERSISTENT]) {
$this->_dbh = @oci_pconnect($username, $password, $description, $parsedDsn['charset']);
} else {
$this->_dbh = @oci_connect($username, $password, $description, $parsedDsn['charset']);
}
//Check if connection was successful
if (!$this->_dbh) {
$e = oci_error();
throw new \PDOException($e['message']);
}
//Save the options
$this->_options = $options;
}
/**
* Connect to an Oracle database
*
* New instances of this class will use the same connection during the script life cycle.
*
* @param $username
* @param string $password
* @param string $connectionString
* @param string $characterSet
* @param int $sessionMode
* @return resource
* @see http://php.net/manual/en/function.oci-connect.php
*/
protected function connect($username, $password = null, $connectionString = null, $characterSet = null, $sessionMode = 0)
{
set_error_handler($this->getErrorHandler());
$connection = oci_connect($username, $password, $connectionString, $characterSet, $sessionMode);
restore_error_handler();
return $connection;
}
public function __construct($username, $password, $db)
{
$this->_dbh = @oci_connect($username, $password, $db);
if (!$this->_dbh) {
throw new OCI8Exception($this->errorInfo());
}
}
function get_list($data)
{
$results = array();
$games = array();
$conn = oci_connect('malz', '1Qaz2wsx', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=db1.chpc.ndsu.nodak.edu)(Port=1521)))(CONNECT_DATA=(SID=cs)))');
//Select customer with last name from field
$userQuery = 'select userName from Account where listId = :data';
$listQuery = 'select * from ListGame, Game where listId = :data and ListGame.gameId = Game.gameId';
$stid = oci_parse($conn, $userQuery);
$stid2 = oci_parse($conn, $listQuery);
oci_bind_by_name($stid, ':data', $data);
oci_bind_by_name($stid2, ':data', $data);
oci_execute($stid, OCI_DEFAULT);
//iterate through each row
while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
$results[] = $row;
}
oci_execute($stid2, OCI_DEFAULT);
while ($row = oci_fetch_array($stid2, OCI_ASSOC)) {
$games[] = $row;
}
$results[] = $games;
echo json_encode($results);
oci_free_statement($stid);
oci_free_statement($stid2);
oci_close($conn);
}
public function connect()
{
if (!self::$_connectSource) {
self::$_connectSource = @mysql_connect($this->_dbConfig['host'], $this->_dbConfig['user'], $this->_dbConfig['password']);
self::$_connectSource = oci_connect();
if (!self::$_connectSource) {
throw new Exception('mysql connect error ' . mysql_error());
//die('mysql connect error' . mysql_error());
}
mysql_select_db($this->_dbConfig['database'], self::$_connectSource);
mysql_query("set names UTF8", self::$_connectSource);
}
return self::$_connectSource;
//if(!self::$_connectSource) {
// self::$_connectSource = @mysql_connect($this->_dbConfig['host'], $this->_dbConfig['user'], $this->_dbConfig['password']);
// if(!self::$_connectSource) {
// throw new Exception('mysql connect error ' . mysql_error());
// //die('mysql connect error' . mysql_error());
// }
//
// mysql_select_db($this->_dbConfig['database'], self::$_connectSource);
// mysql_query("set names UTF8", self::$_connectSource);
//}
//return self::$_connectSource;
}
/**
* Setup the oci object to be used for DB connexion
*
* The DB connexion will be used to insert tickets in RIF remedy DB.
*
* @return dbh
*/
public function getdbh()
{
if (!$this->dbh) {
$this->dbh = oci_connect($this->user, $this->password, $this->dsn);
}
return $this->dbh;
}
/**
* Abra a conexão com o banco de dados Oracle
*
* <code>
* $ociHandle = dbOpen_OCI("(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.84)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xe)))", "", "user", "password");
* </code>
*
* @param string $dbHost string de conexão com o banco de dados
* @param string $dbDatabase[optional] database (não usado no Oracle)
* @param string $dbUser[optional] nome do usuário
* @param string $dbPassword[optional] senha do usuário
*
* @return array com o handleId e o nome do driver
*
* @since Versão 1.0
*/
function dbOpen_OCI(&$dbHandle)
{
$debugBackTrace = debug_backtrace();
$debugFile = basename($debugBackTrace[1]["file"]);
$debugFunction = $debugBackTrace[1]["function"];
$dbDriver = $dbHandle[dbHandleDriver];
if (!function_exists("oci_connect")) {
echo "<span style=\"text-align: left;\"><pre><b>{$dbDriver} - {$debugFile} - {$debugFunction}() - Connect</b>:" . "<br />extension=<b>php_oci8.dll</b> is not loaded";
echo "<hr />" . debugBackTrace();
echo "</pre></span>";
die;
}
$dbHost = $dbHandle[dbHandleHost];
$dbDatabase = $dbHandle[dbHandleDatabase];
$dbUser = $dbHandle[dbHandleUser];
$dbPassword = $dbHandle[dbHandlePassword];
// @TODO Incluir tratamento para ver se o driver está carregado
if (!@($oracleConn = oci_connect($dbUser, $dbPassword, $dbHost))) {
$e = oci_error();
echo "<span style=\"text-align: left;\"><pre><b>{$dbDriver} - {$debugFile} - {$debugFunction}() - Connect</b>:" . "<br /><b>Conexão</b>: " . $dbHost . "<br /><b>Código</b>: " . $e["code"] . "<br /><b>Mensagem</b>: [" . htmlentities($e["message"]) . "]" . "<br /><b>Offset</b>: " . ($e["offset"] + 1);
echo "<hr />" . debugBackTrace();
echo "</pre></span>";
die;
}
return $oracleConn;
}
function db_connect()
{
$connection = oci_connect($this->mDatabaseUsername, $this->mDatabasePassword, "//{$this->mDatabaseHost}:{$this->mDatabasePort}/{$this->mDatabaseName}", "zhs16gbk");
if (!is_resource($connection)) {
die('数据库访问错误！');
}
return $connection;
}
public function __construct()
{
$this->connection = oci_connect($this->username, $this->password, $this->host);
//Wenn kein connection besteht/nicht erstellt werden kann
if (!$this->connection) {
echo "DB Verbindung Fehlgeschlagen!";
}
}
public function __construct(array $config)
{
$this->_connection = oci_connect($config['username'], $config['password'], $config['host'] . ":" . $config['port'] . "/" . $config['instance_name'], $config['charset']);
if (!$this->_connection) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
}
/**
* Initializing class
*
* @param array $options
*
* @return void
*/
public function initialize(array $options = array())
{
$defaultOptions = array('username' => null, 'password' => null, 'dbname' => null, 'charset' => null, 'mode' => null);
$this->options = array_merge($defaultOptions, $options);
$this->raw = oci_connect($this->options['username'], $this->options['password'], $this->options['dbname'], $this->options['charset'], $this->options['mode']);
$this->prepareInit();
$this->dialect = new OracleDialect($this);
}
/**
* connect
*
* Provides a connection to the database.
*
* @return Resource|False A connection identifier or FALSE on error.
*/
public static function connect()
{
if (self::$conn === null) {
self::$conn = oci_connect(self::$db_user, self::$db_pass, self::$db_conn, self::$db_charset);
self::select("ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY'");
}
return self::$conn;
}
public function open_connection()
{
$connection_string = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)\n (HOST = " . DB_SERVER . ")(PORT = " . DB_PORT . ")))(CONNECT_DATA=(SID=" . DB_NAME . ")))";
$this->connection = oci_connect(DB_USER, DB_PASS, $connection_string);
if (!$this->connection) {
die("Database connection failed: " . oci_error());
}
}
/**
* Connect to database server and select database
*/
protected function connect()
{
if ($GLOBALS['TL_CONFIG']['dbPconnect']) {
$this->resConnection = @oci_pconnect($GLOBALS['TL_CONFIG']['dbUser'], $GLOBALS['TL_CONFIG']['dbPass'], '', $GLOBALS['TL_CONFIG']['dbCharset']);
} else {
$this->resConnection = @oci_connect($GLOBALS['TL_CONFIG']['dbUser'], $GLOBALS['TL_CONFIG']['dbPass'], '', $GLOBALS['TL_CONFIG']['dbCharset']);
}
}
function db_connect()
{
$desc = "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 397970-vm2.db1.locahost.co.uk)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = mydb.397970-vm2.db1.localhost.co.uk)))";
$c = oci_connect('v2ngw', 'Fri1007', "{$desc}");
#$c = oci_connect(ORA_USER, ORA_PWD, "//".DB_HOST."/" . ORA_DB);
//$c = oci_connect(ORA_USER, ORA_PWD, "//".DB_HOST."/" . ORA_DB);
return $c;
}
public function testRunOracle()
{
$writerData = $this->prepareConfig('oracle');
$testing = $this->container->getParameter('testing');
$this->configuration->updateTable($this->writerId, $testing['table']['id'], ['dbName' => 'keboola.dummy']);
$dbParams = $testing['oracle']['db'];
$dbString = '//' . $dbParams['host'] . ':' . $dbParams['port'] . '/' . $dbParams['database'];
$conn = oci_connect($dbParams['user'], $dbParams['password'], $dbString, 'AL32UTF8');
try {
oci_execute("DROP TABLE keboola.dummy");
} catch (\Exception $e) {
// table doesn't exist
}
$this->write($writerData['id']);
$stid = oci_parse($conn, "SELECT * FROM keboola.dummy");
oci_execute($stid);
$result = [];
while ($res = oci_fetch_assoc($stid)) {
$result[] = array_values($res);
}
$sapiData = $this->storageApi->exportTable($testing['table']['id']);
$sapiDataArr = Table::csvStringToArray($sapiData);
$sapiDataArrFinal = [];
unset($sapiDataArr[0]);
foreach ($sapiDataArr as $row) {
unset($row[3]);
$sapiDataArrFinal[] = array_values($row);
}
$this->assertEquals($sapiDataArrFinal, $result);
}
