Welcome Guest, Not a member yet? Register   Sign In
HELP: db-connection settings for ODBC to MSSQL
#1

[eluser]zyddee[/eluser]
Hello !

Im trying to setting up an database-connection for an computer on the internet with an mssql-database.
For some unknown reason I cant reach it with ipadress:port so Ive decided to make an odbc connection and use it to connect.
Ive searched the wiki and forum for an solution on how to use CI to connect to odbc. but havnt found anything usefull, hum.

It would be great if the connection source-code could be similar as possible to this code I use here below:
Code:
// DB Config --------------
        $config['hostname'] = $data['adress'];
        $config['username'] = $data['username'];
        $config['password'] = $data['password'];
        $config['database'] = $data['db'];
        $config['dbdriver'] = "mssql";
        $config['dbprefix'] = "";
        $config['pconnect'] = FALSE;
        $config['db_debug'] = TRUE;
        $config['active_r'] = TRUE;
        // ------------------------
            
        $conn = mssql_connect($data['adress'],$data['username'],$data['password']);
            
        if(is_resource($conn)){
            $DB_MSSQL = $this->load->database($config, TRUE);
        }
        else {
            return 'Kunde inte ansluta till databasen';
        }

So Is there any good solution for me out there?

// best regards
#2

[eluser]zyddee[/eluser]
hmmm

Code:
$conn = odbc_connect('dsnname','usrname','password');
works perfectly, i can connect and fetch data..

Code:
// DB Config --------------
        $config['hostname'] = 'dsnname';
        $config['username'] = 'usrname';
        $config['password'] = 'password';
        $config['database'] = 'database';
        $config['dbdriver'] = 'odbc';
        $config['dbprefix'] = "";
        $config['pconnect'] = FALSE;
        $config['db_debug'] = TRUE;
        $config['active_r'] = TRUE;
        // ------------------------
dosent work at all..

any comments, anyone that got odbc-connection to work in codeigniter? Tongue
#3

[eluser]zyddee[/eluser]
ah, now i get it to connect..
BUT i get an error on num_rows() when i use ODBC-connection
Code:
// DB Config --------------
        $config['hostname'] = $data['adress'];
        $config['username'] = $data['username'];
        $config['password'] = $data['password'];
        $config['database'] = $data['db'];
        $config['dbdriver'] = $data['driver'];
        $config['dbprefix'] = "";
        $config['pconnect'] = FALSE;
        $config['db_debug'] = TRUE;
        $config['active_r'] = TRUE;
        // ------------------------

        if( $data['driver'] == strtolower('mssql') )
            $conn = mssql_connect($data['adress'],$data['username'],$data['password']);
        else if( $data['driver'] == strtolower('odbc') )
            $conn = odbc_connect($data['adress'],$data['username'],$data['password']);
            
        if(is_resource($conn)){
            $DB_MSSQL = $this->load->database($config, TRUE);
        }
        else {
            return 'Kunde inte ansluta till databasen';
        }
        
        $this->table = 'employee';
        $DB_MSSQL->where('a', $a);
        $DB_MSSQL->where('b', $b);
        $DB_MSSQL->select('a,b');
        $query =  $DB_MSSQL->get($this->table);
        if ( $query->num_rows() == 1 ) //<- error (Call to a member function num_rows() on a non-object in)
        {    
            $data2 = $query->row_array();
        }

Call to a member function num_rows() on a non-object in
#4

[eluser]falloutphil[/eluser]
Hi,

Firstly - just stick your ODBC DSN into the 'hostname' - google to find the right format.

Then crucially you're using 1.6 (and perhaps in other versions - I don't know), there is a problem with the odbc driver meaning no type of odbc connection will work. You need to make an edit to odbc_driver.php as follows at around line 44 (depending on version):

function CI_DB_odbc_driver($params)
{
parent::CI_DB($params); <---- PUT THIS LINE IN
$this->_random_keyword = ' RND('.time().')'; // database specific random keyword
}

When the constructor was added no explict call was made to the parent to pass back the connection parameters, meaning no data to set up the connection with.
Someone has reported this:
http://codeigniter.com/bug_tracker/bug/3374/

It's a simple fix so I reckon the developers will fix it for the next minor release.

Don't get your hopes up too much tho - having fixed this I cannot get scaffolding or active records to work via odbc. I'm using Sybase under the bonnet so couldn't comment if this will affect other db users:

This now works:
$this->db->query("SELECT * FROM Cycle");

But this doesn't:
$this->db->get('Cycle');

An Error Was Encountered

Error Number: 42000

[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Incorrect syntax near '`'.

SELECT COUNT(*) AS `numrows` FROM `Cycle`


Hope this helps,

Phil.
#5

[eluser]tonanbarbarian[/eluser]
DO NOT USE ACTIVE RECORD WITH ODBC and MSSQL

There is a bug in the odbc driver in that it assumes all odbc data sources allow backticks ` and required brackets around table names.

I have posted a thread about this but so far have not had a reply from the dev team.

So ODBC will work with MSSQL but you have to code all queries manually, or hack the code yourself, which I did.
#6

[eluser]Derek Allard[/eluser]
I've removed the backticks from ODBC. Apologies for the inconvenience. tonanbarbarian, I didn't see your other thread. Could you point me to it?
#7

[eluser]tonanbarbarian[/eluser]
CI 1.6 odbc driver issues

The post includes some suggested features to make it more customisable as well
#8

[eluser]MikeeJay[/eluser]
[quote author="falloutphil" date="1202708359"]Hi,

This now works:
$this->db->query("SELECT * FROM Cycle");

But this doesn't:
$this->db->get('Cycle');

An Error Was Encountered

Error Number: 42000

[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Incorrect syntax near '`'.

SELECT COUNT(*) AS `numrows` FROM `Cycle`


Hope this helps,

Phil.[/quote]

$this->db->get(’Cycle’); works with mysql with $active_record = TRUE in database config
At least with MYSQL ODBC. But not with MSSQL ODBC =(
#9

[eluser]Unknown[/eluser]
[quote author="mikeejay" date="1210615904"][quote author="falloutphil" date="1202708359"]Hi,

This now works:
$this->db->query("SELECT * FROM Cycle");

But this doesn't:
$this->db->get('Cycle');

An Error Was Encountered

Error Number: 42000

[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Incorrect syntax near '`'.

SELECT COUNT(*) AS `numrows` FROM `Cycle`


Hope this helps,

Phil.[/quote]

$this->db->get(’Cycle’); works with mysql with $active_record = TRUE in database config
At least with MYSQL ODBC. But not with MSSQL ODBC =([/quote]

-----------------------------------------------------------------------------
Hi, anybody could help me pls...

I am new to CI and i want my PHP to connect to Sybase Tables in Windows XP OS
I think i successfully connected to my Database but when I run it,
Error occurs,

this is the error: Fatal error: Call to a member function num_rows() on a non-object

I changed the database.php like this below code:
------------------------------------------------
$db['default']['hostname'] = "10.195.1.32";
$db['default']['username'] = "giba";
$db['default']['password'] = "butingting";
$db['default']['database'] = "MyDevDb";
$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";
------------------------------------------------

Here below is my controller to retrieve data from the database:

if($qry_ups->num_rows() >0){ <<<<------Fatal error: Call to a member function num_rows() on a non-object

foreach($qry_ups->result() as $row){
$this->table->add_row($row->UPS_INDEX,
$row->UPS_BRAND,
$row->UPS_MODEL);
}

hoping for your help with this matter.

Thank You very much,
GiBa
}
#10

[eluser]Unknown[/eluser]
i use this lines to connect a remote ms Sql Server using odbc and work perfect!!!!

on database.php config

$db['default']['hostname'] = "Driver={SQL Server Native Client 10.0};Server=remote_host_name;Database=database_name;";
$db['default']['username'] = "database_user_name";
$db['default']['password'] = "database_password";
$db['default']['database'] = "database_name";
$db['default']['dbdriver'] = "odbc";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = FALSE;
$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 hope this help you




Theme © iAndrew 2016 - Forum software by © MyBB