Sybase via ODBC - Active Record and Scaffolding - some success!!

[eluser]falloutphil[/eluser]
Hi,

The current project I'm working on dictates that I write a web frontend for a Sybase DB in PHP. To my knowledge no MVC PHP framework supports this natively yet - I've tried a few and got more or less, nowhere...

I'm a fan of CI so I decided to have a blast at connecting via ODBC. After a bit of huffing and puffing (and hacking), I've got a driver together and with the limited testing I've done I've managed to successfuly perform standard queries, Active Record style queries and I've got the Scaffolding 'View' working.

Anyway - it's a starting point for us poor folk who have to use Sybase, what I suggest is that given that this can never be checked into the branch in it's current form (the updates will probably break ODBC to other databases) - I will continue to post any other additions I make here as when I find something else doesn't work that I need.

If others want to do the same then that would be splendid :-)

One day I may write a proper Sybase driver, but I have no time right now - if anyone else has - I'd love to get my hands on one!

Looks like I can't attach non image files nor fit the full source in here so for now - the below should hopefully include all my changes.

Phil


odbc_driver.php:

~Line 44:

Code:

function CI_DB_odbc_driver($params)
    {
        parent::CI_DB($params);  <--- ADD ME!
        $this->_random_keyword = ' RND('.time().')'; // database specific random keyword
    }

~Line 306:

Code:

function _list_tables($prefix_limit = FALSE)
    {
        /*  Another Sybase fudge from Phil
        $sql = "SHOW TABLES FROM `".$this->database."`";

        if ($prefix_limit !== FALSE AND $this->dbprefix != '')
        {
            //$sql .= " LIKE '".$this->dbprefix."%'";
            return FALSE; // not currently supported
        }
        */
        $sql = "SELECT name FROM sysobjects WHERE type='U'";
        return $sql;
    }

~Line 332:

Code:

function _list_columns($table = '')
    {
        # Phil says Sybase doesn't like SHOW COLUMNS - the below
        # doesn't work properly either - but it's closer - you can't select from an SP
        #return "SELECT column_name, type_name, column_def FROM (EXEC sp_columns ".$this->_escape_table($table).")";
        # ...but in this situation we only care about the first column returned by SHOW COLUMNS - so we can use the below:
        return  "select syscolumns.name from syscolumns, sysobjects where sysobjects.name='".$this->_escape_table($table)."' and sysobjects.id = syscolumns.id";
        #return "SHOW COLUMNS FROM ".$this->_escape_table($table);
    }

~Line 418:

Code:

function _protect_identifiers($item, $first_word_only = FALSE)

Comment out the whole of this function bar the final return statement.


~Line 473:

Code:

function _from_tables($tables)
    {
        if (! is_array($tables))
        {
            $tables = array($tables);
        }
        
        #return '('.implode(', ', $tables).')';
        return ''.implode(', ', $tables).'';
    }

[eluser]ascotan[/eluser]
I'm also stuck with Sybase /cry. I have hacked together a driver from the mysql/odbc modules which works fairly well, however one big problem is getting sybase to report it's last error. There is no 'sybase_error' or 'sybase_errno' in php. The best you can do is read back that last transaction and see if it worked or not. It's not that hard to cobble together a fake sybase driver from the current codebase (pending the fact you don't ever expect to get sybase to return you an error message /sigh).

[eluser]Unknown[/eluser]
Nothing like replying to a thread that's been inactive for 2 years

I'm not sure about error reporting for Sybase via ODBC but if you're using PHP's MS SQL or Sybase drivers, the sybase_get_last_message() is the equivalent of mysql_error().

http://php.net/manual/en/function.sybase...essage.php

The MS SQL equivalent works with Sybase connections too.

[eluser]feroy[/eluser]
Do you make a Sybase's driver for CI?

I have a problem with Sybase, i never used this database before. Now i am asked to connect to Sybase via ODBC.
Can you give me a template what i should fill in the "database.php" to connect to Sybase?

Assume i have a database named "example"

Code:

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "example";
$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";

Thanks for your help