How web servers work with the Oracle database

I write this based on my personal experience when trying to make a connection to an Oracle database through php .. but it always appear error messages etc. ..

Assuming I use the appserver for windows

intrigued with this ,finally I'am try to find a solution in the following way

   1. Download Oracle Instant Client in this link, if done extract the file search and select multiple files that oraociei10.dll, orannzsbb10.dll, oci.dll. Copy the file to c: \ windows \ system32 \
   2. Change Registry NLS_LANG in a way go to Start -> Run -> Regedit -> HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> NLS_LANG, input or Delete Null value .. like the picture below

   3. On his php_oci8.dll extension on a web server php.ini settings file, by Start -> Run -> php.ini, find and remove (;) to activate the file extension, such as the following picture:

 
   4. Restart your web server
   5. Then check php_info (); you, through the following picture you see the following description

 

ok you are ready to work with Oracle database ...^_^

Codeigniter with oracle

Now let me show how to make..CI connect with oracle database..
there are some things we need to prepare

•   make sure our web server already supports connections to Oracle databases by checking through browser.with create a some *.php file with the contents

<?;
phpinfo();
?>

 and you will see a description like in the picture below

if the information is there then there is ready to work with Oracle database.or if not then you need to set up your web server settings .. see how web servers work with the Oracle database this link

Connection Parameters

Not all of the parameters in application/config/database.php are used as one might expect.  Namely, $db[‘default’][‘database’] isn’t used at all.  The value used for $db[‘default’][‘hostname’] depends on whether the Oracle client’s tnsnames.ora file exists and contains information about the database to be used.  If the file exists and is configured for the intended database, this parameter should be set to the symbolic name.  Otherwise, it should be set to a single string of the connection parameters that tnsmames.ora would normally contain.
An example of connection parameters for the latter case:

$db['default']['hostname'] = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SID=dbsid)))';
$db['default']['username'] = 'dbuser';
$db['default']['password'] = 'dbpassword';
$db['default']['database'] = ''; // not used by this Oracle driver
$db['default']['dbdriver'] = 'oci8'; 

In this case, the appropriate values need to be set for the HOST, PORT, and SID keywords in the hostname parameter.

Different queries return the same results

If you run two different queries in the same script you can run into the problem, that both queries return the same result.

Example

$this->load->database();
$query1 = $this->db->query("SELECT * FROM table1");
$result1 = $query1->result(); // Returns Value of table 1
$query1->free_result();

$query2 = $this->db->query("SELECT * FROM table2");
$result2 = $query2->result(); // Also returns Value of table 1 

Solution

In file codeigniter/database/driver/oci8/oci8_driver.php you should change the lines

function _set_stmt_id($sql)
    {
        if ( ! is_resource($this->stmt_id))
        {
            $this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
        }
    } 

 

to


function _set_stmt_id($sql)
    {
        $this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
    } 


happy CI..^_^


Make Trigger in MySQL

Using MySQL triggers, I want to sync content between two tables; I have table “user” and table “users”, each have similar columns “last_name” “first_name” “user_id” …etc, and when a row gets written to table “users”, I wanted that record to be replicated to “user”. In this case, I’d recommend calling a stored procedure to perform this type of replication. – almost identical tables. copying data from user to users create table user (userid int auto_increment primary key, fname varchar(10), lname varchar(10)); create table users (userid int primary key, fname varchar(10), lname varchar(10)); delimiter | create procedure copy_row(uid int) begin INSERT INTO users (userid, fname, lname) SELECT userid, fname, lname from user WHERE userid = uid; end; | delimiter ; mysql> insert into user (fname, lname) values (’joe’, ’smith’), (’steve’, ‘brown’),(’jon’, ‘haddad’); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> call copy_row(1); Query OK, 1 row affected (0.01 sec) mysql> select * from user; +——–+——-+——–+ | userid | fname | lname | +——–+——-+——–+ | 1 | joe | smith | | 2 | steve | brown | | 3 | jon | haddad | +——–+——-+——–+ 3 rows in set (0.00 sec) mysql> select * from users; +——–+——-+——-+ | userid | fname | lname | +——–+——-+——-+ | 1 | joe | smith | +——–+——-+——-+ 1 row in set (0.00 sec) If you really want to do this with a trigger, you can do something like this: delimiter | create trigger user_ins AFTER insert on user for each row begin call copy_row(NEW.userid); end; | create trigger user_upd AFTER update on user for each row begin call copy_row(NEW.userid); end; | create trigger user_del AFTER delete on user for each row begin delete from users WHERE userid = OLD.userid; end; | delimiter ; mysql> insert into user (fname, lname) values (’octavious’, ‘jones’); Query OK, 1 row affected (0.00 sec) mysql> select * from users; +——–+———–+——-+ | userid | fname | lname | +——–+———–+——-+ | 1 | joe | smith | | 4 | octavious | jones | +——–+———–+——-+ 2 rows in set (0.00 sec) Hopefully this helps.