Name of Uppercase Columns with Codeigniter and Postgreesql

Asked

Viewed 401 times

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 example of Select:

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

However, this select works normally on Oracle. Already in Postgresql, it returns me an error where there are no such columns.

To work normally I set _protect_identifiers and it worked, so select was like this: SELECT ID, NOME FROM USUARIO

The problem now is when it returns in the array, in the oracle it returns the array with the uppercase Keys and the pgsql returns lowercase. I can’t reach the middle. What can I do ?

php codeigniter oracle

  • What would be the "middle ground"?

  • I do a select for Oracle and Postgree on Codeigniter. When scanning select, it returns me the keys of the different array, in the oracle it returns me uppercase and in the lowercase postgree. I can’t find a way to access the values of the array independent of the keys that come. I wonder if there’s any way I can do this.

1 answer

1


Hello, if you wear even 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 marry of the resultset variables does not prevent the visualization of them:

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

Now if the problem is in the method select( ) query Builder, for not finding the columns, which should not happen, simply write everything in upper case. Oracle should be able to find lowercase columns written in upper case.

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

Or, you would have to standardize, in all banks used, or for upper or lower case. But this is a solution of worse quality. Before check if you have the latest version of Codeigniter, because this may be a problem already solved.

  • Thanks for the help, but not necessarily I am with error in query Builder, I am with this problem when the database returns the array with the values, in postgree, it returns me the minuscule Keys and in oracle, it returns me uppercase and I am using the latest version of CI.

  • Hi @Huhifeku, what comes to be the keys what do you mention? Are they instances of what class or what type? When you try to access them, what error do you get? Can demonstrate a line where you employ them?

  • Inside a model, at the end of a method, I call Return $query->result_array(); to return me the array with the values of my query, correct? It returns me an array, in postgree, the array comes like this: $array = ('nome' => 'João'); and Oracle: ; $array = ('NOME' => 'João'); Do you notice that the Keys are different? So, this is the problem, I wanted to automate this process to come in a single way, where regardless of when I scan the array, is so I can access these values.

  • @Huhifeku, I get it. It’s because in PHP vector indices are yes case sensitive. The function array_change_key_case( ) solves your problem. Instead of using directly $query->result_array(); utilize array_change_key_case($query->result_array());. Indexes have always been lowercase, unless you want them uppercase: array_change_key_case($query->result_array(), CASE_UPPER);.

Browser other questions tagged php codeigniter oracle

You are not signed in. Login or sign up in order to post.