ODBC woes

[eluser]Zen Savona[/eluser]
Well, I had built an application intended for MSSQL, and originally ran it that way, I bought a new computer and for some odd reason it won't let me connect via MSSQL.

So I set up ODBC. It connects fine, but it seems to hate active records. Am I going to have to rewrite all my queries? or is there something I'm missing. I get errors like this.

A Database Error Occurred
Error Number: 37000

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.

SELECT * FROM (News) ORDER BY id desc

any thoughts?

[eluser]lsousa[/eluser]
Hi,

Look for this on system\database\drivers\odbc\odbc_driver.php

Code:

function _from_tables($tables)
    {
        if ( ! is_array($tables))
        {
            $tables = array($tables);
        }

        return '('.implode(', ', $tables).')';
    }

and replace for:

Code:

function _from_tables($tables)
    {
        if ( ! is_array($tables))
        {
            $tables = array($tables);
        }

        return implode(', ', $tables);
    }

Let me know how it went

I've also fixed some more bugs in ODBC driver, take a look at my repository:

https://bitbucket.org/lsousa/codeigniter-reactor

[eluser]Zen Savona[/eluser]
well, that removed the () brackets, but I get similar errors.

Code:

A Database Error Occurred
Error Number: S0002

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'News'.

SELECT * FROM News ORDER BY id desc

the object name 'News' IS a valid object, and that is an AR generated query.

I would like my code to be able to be used by mssql and odbc connections ideally.

[eluser]lsousa[/eluser]
Uhm, I'll try tomorrow at work on my coffee break (I don't have a mssql server at home).

Until then, can you try the query without the ORDER BY clause?

Code:

$this->db->get('News');
foreach ($query->result() as $row)
{
    echo $row->title;
}

[eluser]Zen Savona[/eluser]
Tried that already, throws the same error.

Also tried modifying my object name, incase it selects it differently to the mssql driver, i've tried dbo.News, kal_db.dbo.News, [News] and News

[eluser]lsousa[/eluser]
I cannot seem to duplicate your issue.

Are you sure ODBC is beeing connected to the right database? Try this quick and dirty code to see the name of the database and available tables:

Code:

$query = $this->db->query("SELECT db_name()");
echo '<h1>Connected Database Name: </h1>';
foreach ($query->result() as $row)
{
    echo reset($row) .'<br/>';
}

echo '<h1>Available Tables: </h1>';
$query = $this->db->query("SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS");
foreach ($query->result() as $row)
{
    echo reset($row) .'<br/>';
}

echo '<h1>Custom Query Result: </h1>';
$query = $this->db->get('News');
echo '<pre>';
foreach ($query->result() as $row)
{
    print_r($row);
}
echo '</pre>';

I'm using SQL Server 10.50.1600 by the way.

[eluser]morismc[/eluser]
$db['default']['database'] = 'raw';
$db['default']['dbdriver'] = 'odbc';

..... nevertheless it takes "master" as it's database schema. What's wrong?