Name of Columns in uppercase with Codeigniter and PostgreeSQL

Question

Name of Columns in uppercase with Codeigniter and PostgreeSQL

Navigation

#1 by (1 votes)

1

I am developing a WEB system using Codeigniter with support for Oracle database and PostgreSQL.

In my queries, I use Codeigniter's own Query Builder.

Simple Select example:

SELECT "ID", "NOME" FROM "USUARIO"

However, this select works normally in Oracle. In PostgreSQL, it returns an error where no such columns exist.

In order to work, I usually configured _protect_identifiers and it worked, so the select looks like this: SELECT ID, NOME FROM USUARIO

The problem now is when it returns in the array, in oracle it returns the array with the uppercase keys and pgsql returns lowercase. I can not get to the middle ground. What can I do?

php oracle codeigniter

asked by anonymous 07.06.2017 / 16:33

1 answer

Write and print data in an ArrayList Error downloading branch in git

score 1 · Accepted Answer

Hello, if you use same as the CodeIgniter query builder, this question should be irrelevant. See your example being built by the query builder:

$this->db->select('id, nome');
$query = $this->db->get('usuario');

And the columns can be accessed like this:

$meu_id = $query->row()->id;
$meu_nome = $query->row()->nome;

PHP is case insensitive, that is, any variance in the case of the resultset variables does not prevent them from being viewed:

$meu_id = $query->row()->ID; // Isto funciona igual
$meu_nome = $query->row()->NOME; // Perfeito também

Now if the problem is in the select( ) method of the query builder, because it does not find the columns, which should not happen, simply write everything in upper case. Oracle should be able to find lowercase columns written in uppercase.

$this->db->select('ID, NOME'); // Isto deve ser entendido pelos bancos

Or, you would have to standardize, on all banks used, or for uppercase or lowercase. But this is a worse quality solution. First make sure you have the latest version of CodeIgniter, because this can be a problem already solved.