CodeIgniter Forums
Oracle issues - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Oracle issues (/showthread.php?tid=7317)

Oracle issues - El Forum - 04-03-2008

[eluser]iffs[/eluser]
First, good day to all!

Now, let's go to business. I'm trying to create a simple application with CI, with 2 databases, one in Oracle, and the other in PostgreSQL. I've managed to successfully connect and perform queries to both databases, thanks to what I've found in these two posts:

http://www.abbett.org/2007/12/02/using-oracle-with-php-and-codeigniter/
http://ellislab.com/forums/viewthread/74105/

I've had issues with Oracle, returning an unnumbered error, then the errors listed in the first site, and finally errors with multiple queries. So the first question is: will all these changes apply to CI? Are they indeed correct? These changes worked fine for me. :lol:

Another thing, now about encoding. Both of the databases have LATIN1 encoding. The code I used for testing follows:

First, the Controller:

Code:
<?php
    class Noticia extends Controller {
        
        function Noticia() {
            parent::Controller();
        }
        
        function index() {
            $db = $this->load->database('seguranca', TRUE);
            $data['query'] = $db->get('unidades_federais');
            $data['query2'] = $db->get('s_pessoa');
            $this->load->view('noticia_view', $data);
        }
    }
?>

Then, the view:

Code:
<html>
    <head>
        <title>Componente de noticias</title>
    </head>
    <body>
        <ul>
            &lt;?php if($query->num_rows() > 0) : ?&gt;
                &lt;?php foreach($query->result() as $uf) : ?&gt;
                    <li>&lt;?= $uf->SIGLA ?&gt; - &lt;?= $uf->UNIDADE ?&gt;</li>
                &lt;?php endforeach; ?&gt;
            &lt;?php endif; ?&gt;
        </ul>
        <hr>
        <ol>
            &lt;?php if($query2->num_rows() > 0) : ?&gt;
                &lt;?php foreach($query2->result() as $pessoa) : ?&gt;
                    <li>&lt;?= $pessoa->NU_CPF ?&gt; - &lt;?= $pessoa->NO_PESSOA ?&gt;</li>
                &lt;?php endforeach; ?&gt;
            &lt;?php endif; ?&gt;
        </ol>
    &lt;/body&gt;
&lt;/html&gt;

And finally, my database connection settings:

Code:
&lt;?php  if (!defined('BASEPATH')) exit('No direct script access allowed');
/*
| -------------------------------------------------------------------
| DATABASE CONNECTIVITY SETTINGS
| -------------------------------------------------------------------
| This file will contain the settings needed to access your database.
|
| For complete instructions please consult the "Database Connection"
| page of the User Guide.
|
| -------------------------------------------------------------------
| EXPLANATION OF VARIABLES
| -------------------------------------------------------------------
|
|    ['hostname'] The hostname of your database server.
|    ['username'] The username used to connect to the database
|    ['password'] The password used to connect to the database
|    ['database'] The name of the database you want to connect to
|    ['dbdriver'] The database type. ie: mysql.  Currently supported:
                 mysql, mysqli, postgre, odbc, mssql
|    ['dbprefix'] You can add an optional prefix, which will be added
|                 to the table name when using the  Active Record class
|    ['pconnect'] TRUE/FALSE - Whether to use a persistent connection
|    ['db_debug'] TRUE/FALSE - Whether database errors should be displayed.
|    ['cache_on'] TRUE/FALSE - Enables/disables query caching
|    ['cachedir'] The path to the folder where cache files should be stored
|    ['char_set'] The character set used in communicating with the database
|    ['dbcollat'] The character collation used in communicating with the database
|
| The $active_group variable lets you choose which connection group to
| make active.  By default there is only one group (the "default" group).
|
| The $active_record variables lets you determine whether or not to load
| the active record class
*/

$active_group = "default";
$active_record = TRUE;

$db['default']['hostname'] = "10.1.1.34";
$db['default']['username'] = "mmacorporativo";
$db['default']['password'] = "*********";
$db['default']['database'] = "adsi";
$db['default']['dbdriver'] = "postgre";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "latin1";
$db['default']['dbcollat'] = "latin1_general_ci";

$db['seguranca']['hostname'] = "//10.1.1.46/mmadsv";
$db['seguranca']['username'] = "systemsecurity";
$db['seguranca']['password'] = "********";
$db['seguranca']['database'] = "mmadsv";
$db['seguranca']['dbdriver'] = "oci8";
$db['seguranca']['dbprefix'] = "";
$db['seguranca']['pconnect'] = TRUE;
$db['seguranca']['db_debug'] = TRUE;
$db['seguranca']['cache_on'] = FALSE;
$db['seguranca']['cachedir'] = "";
$db['seguranca']['char_set'] = "latin1";
$db['seguranca']['dbcollat'] = "latin1_general_ci";
?&gt;

Some results in my page are as follows:

Gerencia de Conservac?o da Biodiversidade (SBF/DCBIO)
Gerencia de Gest?o de Recursos Geneticos (SBF/DCBIO)
Gerencia de Gest?o de Recursos Pesqueiros (SBF/DCBIO)
SBF/DCBIO/PROBIO II - Projeto de Conservac?o e Uso Sustentavel da Biodiversidade e Ac?es Integradas, Publicas/Privadas para Biodiversidade (SBF/DCBIO)

It does not matter which browser encoding I choose, the ? characters still appear. Can you help me with this one?

Thanks to all, and happy coding!!

Igor Felix

Oracle issues - El Forum - 04-03-2008

[eluser]unficyp[/eluser]
hi,

i'm not sure what CI does with
$db['seguranca']['char_set'] = "latin1";
$db['seguranca']['dbcollat'] = "latin1_general_ci";
to set a proper Oracle NLS Environment.

Try setting NLS_LANG=.... at your apache startup script or try
putenv("NLS_LANG=...."); in your php code.

Which Oracle Version do you use ?
Which nls setting does the database have ? (select * from v$nls_parameters)

Oracle issues - El Forum - 04-07-2008

[eluser]iffs[/eluser]
Thanks a lot!! =)

I've set NLS_LANG to

putenv("NLS_LANG=american_america.WE8ISO8859P1");

And everything worked just fine!!

:lol:

God, I wish I could convince everyone here at work to use CI!! It's just like natural programming, much much better than the framework we use here...

Again, thanks!!

Igor Felix

Oracle issues - El Forum - 05-30-2008

[eluser]Weblizard[/eluser]
Well I don't want to dig this up but :
both db_connect AND db_pconnect method of oci8_driver missed 4th parameter (charset) and if you are using Oracle 9.2+ you MUST set charset to something sensible so here is my 2 cents
edit CI_root/database/drivers/oci8/oci8_drive.php
around line 73 change this:
Code:
return @ocilogon($this->username, $this->password, $this->hostname);
to this
Code:
return @ocilogon($this->username, $this->password, $this->hostname, $this->char_set);
and the same for db_pconnect method

Now you can set your NLS lang under CI_root/config/database.php
something like this for utf8
Code:
$db['default']['char_set'] = "al32utf8";