• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CI and SQL SERVER 2005 and MS ADODB not ADOdb

#1
[eluser]riza_nurhadi[/eluser]
hi im a new to CI and been using it with sql server 2005 with changed ntwdblib.dll.
im using autoload too.

it works fine but it comes with unstable database connection. sometimes its just connect to database server and sometimes its says 'unable to connect to database'. ill just refresh my browser a few times and it connect again.

maybe there is something wrong with the web server and database server. but im not the one who set the server. and i dont care bout it.

im using driver mssql. and with changed ntwdblib.dll.

and now im using ADODB object from MS. to use it ive created some library for it.

this is the library.

Code:
<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

    class ADODB {
           //////
        var $ado_data_source = '192.168.1.1';
        var $ado_db_name = 'thisisdb';
        var $ado_db_uid = 'sa';
        var $ado_db_pwd = 'iforgot';

       function adodb_query_result_array($sql){
         $conn = new COM("ADODB.Connection") or die("Cannot start ADO");
         $conn->Open("Provider=SQLOLEDB; Data Source=".$this->ado_data_source.";
         Initial Catalog=".$this->ado_db_name."; User ID=".$this->ado_db_uid."; Password=".$this->ado_db_pwd."");
        
        
         $rs = $conn->Execute($sql);
         $num_columns = $rs->Fields->Count();
         $ado_field_name = array();
         for ($i=0; $i < $num_columns; $i++) {
          $ado_field_name[] = $rs->Fields($i)->Name;
         }
        
         $result = array();
         $test = array();
         $rowcount = 0;
         if(!$rs->EOF && !$rs->BOF) {
         $rs->MoveFirst;
          while (!$rs->EOF) {
          for ($i=0; $i < $num_columns; $i++) {
            $result[$rowcount][$ado_field_name[$i]] = $rs->Fields[$i]->value;
        }
          $rs->MoveNext();
          $rowcount++;
         }
         }
         $rs->Close();
         $conn->Close();
         $conn = null;
          return $result;
       }
      
          function adodb_query_insert_id($sql){
          $conn = new COM("ADODB.Connection") or die("Cannot start ADO");
         $conn->Open("Provider=SQLOLEDB; Data Source=".$this->ado_data_source.";
         Initial Catalog=".$this->ado_db_name."; User ID=".$this->ado_db_uid."; Password=".$this->ado_db_pwd."");
         $conn->Execute($sql);
         $sql = " SELECT @@IDENTITY AS last_id";
         $rs =  $conn->Execute($sql);
         $insertid = $rs->Fields[0]->value;
         return $insertid;
       }
      
       function adodb_query_execute($sql){
         $conn = new COM("ADODB.Connection") or die("Cannot start ADO");
         $conn->Open("Provider=SQLOLEDB; Data Source=".$this->ado_data_source.";
         Initial Catalog=".$this->ado_db_name."; User ID=".$this->ado_db_uid."; Password=".$this->ado_db_pwd."");
         $conn->Execute($sql);
         $conn->Close();
         $conn = null;
       }


    }

?&gt;

the result is the execution time is getting longer...its really slow..

ive tried sqlsvr driver...but it just gone blank...

as you see... im creating this to match the result_array and insert_id function. and creating some stable connection to the database.

if anyone have interest in this i appreciate it..
see ya in few weeks

please forgive for my bad English.

#2
[eluser]TheFuzzy0ne[/eluser]
Can you not go with MySQL or SQLite?

#3
[eluser]riza_nurhadi[/eluser]
ok im posting again..
for CodeIgniter 1.6.3/4 and SQL SERVER 2005 as database
here we go
now im succeed creating an adodb driver in codeigniter
so you can autoload the driver and its not as a library.

im still using php COM class to connect to database
Code:
function db_connect()
    {
         $conn = new COM("ADODB.Connection") or die("Cannot start ADO");
         $conn->Open("Provider=SQLOLEDB; Data Source=".$this->hostname.";
         Initial Catalog=".$this->database."; User ID=".$this->username."; Password=".$this->password."");
        return $conn;
    }
and you can use config/database.php to call or autoload the SQL SERVER 2005 database like this
Code:
//@system/config/database.php
$db['default']['hostname'] = "dbserver";
$db['default']['username'] = "sa";
$db['default']['password'] = "iforgot";
$db['default']['database'] = "mydb2";
////here is the driver
$db['default']['dbdriver'] = "adodb";
////end here is the driver
$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";

as you see im using dbdriver "adodb" not mssql or sqlsrv.
to use this you need to forge the codeigniter code.

first of all you need to create new dbdriver
create new folder system\database\drivers and rename it "adodb"

then create 4 files inside adodb folder
- adodb_driver.php
- adodb_forge.php
- adodb_result.php
- adodb_utility.php

so youll have some thing like this
Code:
system/database/adodb/adodb_driver.php
                     /adodb_forge.php
                     /adodb_result.php
                     /adodb_utility.php
                /mssql/
                /mysql/
                blablabla

you can copy the driver from mssql driver[located @ system\database\drivers\mssql]
and rename it to adodb the filename and the class name..well that is what i do


but first i add something to DB_driver.php and DB_result.php.
i only change the query function and result_array function just because this is the only CodeIgniter function that i use

the change that i made in DB_driver is only at function query
here is the change for DB_driver [located @ system\database\DB_driver.php]
Code:
///.....bla bla bla
// this is @ system\database\DB_driver.php
function query($sql, $binds = FALSE, $return_object = TRUE){
///.....bla bla bla
    // Load and instantiate the result driver    
        $driver         = $this->load_rdriver();
        $RES             = new $driver();
        $RES->conn_id    = $this->conn_id;
        $RES->result_id    = $this->result_id;
        $RES->num_rows    = $RES->num_rows();
        ///just add something here for adodb driver
        $RES->dbdriver = $this->dbdriver;

///.....bla bla bla
}
///.....bla bla bla
the change that i made in DB_result is only at function result_array
here is the change for DB_result [located @ system\database\DB_result.php]
Code:
/// ......bla bla bla
function result_array()
    {
    // no change here the change is down there
        if (count($this->result_array) > 0)
        {
            return $this->result_array;
        }
        // In the event that query caching is on the result_id variable
        // will return FALSE since there isn't a valid SQL resource so
        // we'll simply return an empty array.
        // no change here
        if ($this->result_id === FALSE OR $this->num_rows() == 0)
        {
            return array();
        }
        $this->_data_seek(0);    
/////BEGIN this is the change i made
        if($this->dbdriver != 'adodb'){
        while ($row = $this->_fetch_assoc())
        {
            $this->result_array[] = $row;
        }
        } else {
        $this->result_array = $this->_fetch_assoc();
        }
/////END this is the change i made        
        return $this->result_array;
    }
    /// ......bla bla bla

ok next post is the 4 code inside adodb folder
- adodb_driver.php
- adodb_forge.php
- adodb_result.php
- adodb_utility.php

#4
[eluser]riza_nurhadi[/eluser]
and now for the adodb_driver.php [located @ system\database\drivers\adodb_driver.php]
this is the code.

i change function db_connect, db_pconnect so its used php COM class
oh yea i forgot i also change function insert_id
just look at the code

Code:
&lt;?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class CI_DB_adodb_driver extends CI_DB {

    var $_count_string = "SELECT COUNT(*) AS ";
    var $_random_keyword = ' ASC'; // not currently supported

    function db_connect()
    {
         //this is the change
         //return @mssql_connect($this->hostname, $this->username, $this->password);
         $conn = new COM("ADODB.Connection") or die("Cannot start ADO");
         $conn->Open("Provider=SQLOLEDB; Data Source=".$this->hostname.";
         Initial Catalog=".$this->database."; User ID=".$this->username."; Password=".$this->password."");
        return $conn;
    }
    
    function db_pconnect()
    {
        //return @mssql_pconnect($this->hostname, $this->username, $this->password);
        //return true;
        return $this->db_connect();
    }
    
    function db_select()
    {
        //return @mssql_select_db($this->database, $this->conn_id);
        return true;
    }

    function db_set_charset($charset, $collation)
    {
        // TODO - add support if needed
        return TRUE;
    }

    function _execute($sql)
    {
        //$sql = $this->_prep_query($sql);
        // return @mssql_query($sql, $this->conn_id);
                $conn = $this->conn_id;
            $rs = $conn->Execute($sql);
        return $rs;
    }
    
    function insert_id()
    {
        // $ver = self::_parse_major_version($this->version());
        // $sql = ($ver >= 8 ? "SELECT SCOPE_IDENTITY() AS last_id" : "SELECT @@IDENTITY AS last_id");
        // $query = $this->query($sql);
        // $row = $query->row();
        // return $row->last_id;
        $conn = $this->conn_id;
        $sql = " SELECT @@IDENTITY AS last_id";
         $rs =  $conn->Execute($sql);
        $insertid = $rs->Fields[0]->value;
        return $insertid;
    }
}

as you see i only copy this code from mssql driver

#5
[eluser]riza_nurhadi[/eluser]
and now for the adodb_result.php [located @ system\database\drivers\adodb_result.php]
this is the code.

the change that i made was
- function num_rows
- function _data_seek
- function _fetch_assoc....this is the code is important for function result_array

the other thing was just to close the mssql_whatever php function.

Code:
&lt;?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class CI_DB_adodb_result extends CI_DB_result {
    function num_rows()
    {
        //return @mssql_num_rows($this->result_id);
        //$rs = $conn->Execute($sql);
        
        $rs = $this->result_id;
         $rowcount = 0;
         if(!$rs->EOF && !$rs->BOF) {
         $rs->MoveFirst;
          while (!$rs->EOF) {
          $rs->MoveNext();
          $rowcount++;
          }
            $rs->MoveFirst;
          }
        
        return $rowcount;
    }

    function _data_seek($n = 0)
    {
        //return mssql_data_seek($this->result_id, $n);
        return true;
    }

    function _fetch_assoc()
    {
        $rs = $this->result_id;
        $num_columns = $rs->Fields->Count();
         $ado_field_name = array();
         for ($i=0; $i < $num_columns; $i++){
          $ado_field_name[] = $rs->Fields($i)->Name;
         }
        
         $result = array();
         $test = array();
         $rowcount = 0;
         if(!$rs->EOF && !$rs->BOF) {
         $rs->MoveFirst;
          while (!$rs->EOF) {
          for ($i=0; $i < $num_columns; $i++) {
            $result[$rowcount][$ado_field_name[$i]] = $rs->Fields[$i]->value;
        }
          $rs->MoveNext();
          $rowcount++;
         }
          $rs->MoveFirst;
         }
        
        //return mssql_fetch_assoc($this->result_id);
        return $result;
    }

}


/* End of file mssql_result.php */
/* Location: ./system/database/drivers/mssql/mssql_result.php
AND CHANGED TO Location: ./system/database/drivers/adodb/adodb_result.php
*/

as you see i only copy this code from mssql driver.
and thats it...im not change the other 2 driver.

#6
[eluser]riza_nurhadi[/eluser]
and now you can load the database using autoload.php
with this the only function that working is
bear with it

Code:
$this->db->query($sql);
Code:
$this->db->query($sql);  
return $this->db->insert_id();
Code:
$result_id =  $this->db->query($sql);  
return $result_id->result_array();

#7
[eluser]riza_nurhadi[/eluser]
the result is....

its reaalllly slooooowwww

ok thats it..

#8
[eluser]riza_nurhadi[/eluser]
[quote author="riza_nurhadi" date="1241650487"]the result is....

its reaalllly slooooowwww

ok thats it..[/quote]

ok its SOLVED....

its not so slow anymore just with a lil bit of change in
system/application/config/database.php

change this
Code:
$db['default']['hostname'] = "dbserver";
into this
Code:
$db['default']['hostname'] = "10.10.10.100";

it seems that if the hostname is an ipaddress the connection to database server is much more faster

i dont know why..


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.