CI 1.6.1 ODBC issue when using PostgreSQL

[eluser]piotrpolak[/eluser]
I found a strange issue when using ODBC driver together with PostgreSQL:

Log:

Code:

ERROR - 2008-04-17 21:02:30 --> Severity: Warning  --> mysql_num_rows(): supplied resource is not a valid MySQL result resource C:\wamp\www\pepiscms\codeigniter\database\drivers\mysql\mysql_result.php 37

mysql_num_rows() in ODBC?

Example model's code:

Code:

$this->db->select('*');        
$this->db->join('usergroups', 'users.group_id = usergroups.group_id');
$this->db->where('user_email', $user_email);
$this->db->where('password', md5($password));
        
$query = $this->db->get('users');
$row = $query->row();

The reason was in CI_DB_driver line 37

Code:

var $dbdriver        = 'mysql';

It was enough add a single line to CI_DB_odbc_driver

Code:

class CI_DB_odbc_driver extends CI_DB {

    /**
     * The syntax to count rows is slightly different across different
     * database engines, so this string appears in each driver and is
     * used for the count_all() and count_all_results() functions.
     */
    var $_count_string = "SELECT COUNT(*) AS ";
    var $_random_keyword;
    var $dbdriver = 'odbc'; // here!

[eluser]Seppo[/eluser]
Cool... I'm not able to test it (I don't have that many differents db servers engine), but it seems like a real bug... So report it in the bug tracker

[eluser]Derek Allard[/eluser]
What does your config/database.php file look like? Could you copy and paste it (removing any sensitive information)?

[eluser]piotrpolak[/eluser]

Code:

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

$db['default']['hostname'] = "DRIVER={PostgreSQL Unicode};Server=localhost;Database=dbname;uid=user; pwd=pass";
$db['default']['username'] = "";
$db['default']['password'] = "";
$db['default']['database'] = "";
$db['default']['dbdriver'] = "odbc";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

I belive it was something like this.

I have also tried replacing $this->hostname variable by DSN in the odbc_connect and odbc_pconnect functions of odbc driver.

[eluser]Derek Allard[/eluser]
I see, so have you moved onto using the postgre driver directly then? Do very simple queries work? I haven't gone through the code yet, but I'd like to narrow it down a bit more. The dbdriver should be automatically instantiated, but I admit to never having used odbc myself.

[eluser]piotrpolak[/eluser]
I wrote a CMS that is DB independent. It successfully works with MySQL and PostgreSQL using mysql mysqli and postgre driver. I was asked to make it work with PostgreSQL DBMS using odbc functions. Since I didn't want to "spoil" my app by using native PHP functions directly in models code, i tried with ODBC CI driver.

I also tried to make it work with Oracle (XE 10 version), i designed a compatible database but i got the same error when using ODBC. Also, queries were incompatible, too many " or ()..

There should be some kind of extension making ODBC driver more configurable. For me ODBC driver is useless, I couldn't make it work without modifying the source (adopting for a certain DBMS).

I haven't tried ODBC for anything else than MySQL, PostgreSQL, Oracle.

[eluser]Unknown[/eluser]
I need ODBC to connect to Eloquence databases and found out that $this->whatever is null in odbc_driver.php. I've replaced $this->hostname with the DSN and $this->username with the privileged username. The log shows no errors now.

Specifying $this->load->database('default') does nothing to solve the problem.

[eluser]piotrpolak[/eluser]
I derived my own PostgreSQL ODBC driver for CodeIgniter.

It works fine for me.

By changing the ODBC driver name in DNS of connect() and pconnect() methods you can adopt it for any DBMS/ODBC. In few cases the driver specific SQL escape functions could give some SQL errors.

There is no universal solution for CI/ODBC since CI odbc driver is an abstraction layer over another abstraction layer (ODBC) specific to DBMS.