Login

04-03-2008, 07:49 AM

[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-o...deigniter/
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:
&lt;?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);

        }

    }

?&gt;

Then, the view:

Code:
&lt;html&gt;

    &lt;head&gt;

        &lt;title&gt;Componente de noticias&lt;/title&gt;

    &lt;/head&gt;

    &lt;body&gt;

        <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

04-03-2008, 03:59 PM

[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)

04-07-2008, 12:08 PM

[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

05-30-2008, 09:54 PM

[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";