PHP and SQL SERVER 2005 with ADODB.CONNECTION

hmm it seems someone is using the php COM class to connect to SQL SERVER 2005.
and i think im gonna try this function to.

i found this article from a mailing list here

and im gonna copy paste it to my blog.

thanks for the hardwork AJ.

Ok, here is enough code to get you started :-)

/** This class houses the static methods to creating connections to SQLServer. */ class ADODB {

function ConnectToSQLServer ( $server, $database, $username, $password ) { $obj=new COM('ADODB.connection'); if(!$obj->pinned()) { $obj->open("Provider=SQLOLEDB.1;Server=$server;Database= $database;UID=$username;PWD=$password;"); } register_shutdown_function('_adodb_shutdown'); return $obj; }

function ExecuteQuery($sql,&$connection) { $recordsAffected=0; return $connection->Execute($sql, $recordsAffected, 8); }

function Execute($sql,&$connection) { $ra=0; $connection->Execute($sql,$ra,129); }

}

/* Some util functions */

function dbEscape($s) { return "'".str_replace("'", "''", $s)."'"; }

function forceNumber($number) { if(!is_numeric($number)) { // Trigger error is one of my fuctions, you'd have to do somethine else ;-) //trigger_error("Non numeric passed to forceNumber: '$number'", E_USER_ERROR); } return $number*1.0; // ensures it is stored in php as a number }

/* Some examples of using it. * Please note that in these examples I connect to the DB in each function, * this is totally and utterly wrong for a real situation, you should only * make a connection once per php client connection. */

/* Gets a single result */ function exmpl1() { $server=ADODB::ConnectToSQLServer ( $server, $database, $username, $password ); $recset=0; $recset=ADODB::ExecuteQuery("SELECT MIN(RowID) FROM T_RowID", $server); $ret=$recset->Fields; $ret=$ret->item(0); $ret=$ret->Value; $recset->close(); return $ret; }

/* Gets an array of results */ function exmpl2($Name,$FromId) { $server=ADODB::ConnectToSQLServer ( $server, $database, $username, $password ); $recset=0; $Name=dbEscape($name); $FromId=forceNumber($parent); $recset=ADODB::ExecuteQuery("SELECT ToId FROM T_References WHERE KeyName=$Name AND FromId=$FromId",$server); $ret=array(); while(!$recset->EOF()) { $p=$recset->Fields; $p=$p->item(0); array_push($ret,$p->Value); $recset->movenext(); } $recset->close(); return $ret; }

/* Runs an update only */ function exmpl3($parent,$name,$child) { $server=ADODB::ConnectToSQLServer ( $server, $database, $username, $password ); $parent=forceNumber($parent); $name=substr($name,0,112); $name=dbEscape($name); $child=forceNumber($child); $sql="INSERT INTO T_References (FromId,KeyName,ToId) VALUES ($parent,$name,$server)"; ADODB::Execute($sql,$this->connection); } ?>

Cheers

AJ

....again thanks AJ

testing beta test


function index()
    {
    
            /* grab the division list */
            if ($divisions = $this->main->get_all_divisions())
            $data['divisions'][$value['id']=0] = '';
            {
                foreach ($divisions as $key=>$value)
                $data['divisions'][$value['id']] = $value['name'];
            }
    
        $data['site_title'] = $this->config->item('site_title');
        $data['title'] = "Main Page";
        $this->template->write_view('content', 'default/pages/main_index', $data);
        $this->template->render();
    }
    
    function names()
    {
       /* set the POST variable */
        $division = $this->input->post('division', TRUE);
        
        /* grab the division details */
        $item = $this->main->get_division($division);
        
        /* set the output */
        $output = ($item !== FALSE) ? $item : '';
        
        echo $output;
    }



PHP and SQL SERVER 2005

it seems that the connection was unstable....
im using mssql_pconnect to connect PHP to SQL SERVER 2005.

anyway now im trying a new way to connect PHP and SQL SERVER 2005
and thats using ADODB Connection Object.

to use this you have to call COM class from PHP

function adodb_query($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;
$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;
}

this function will execute query and return it as an array.
it was the same with mssql_fetch_array().

but this has a huge problem.
because of everytime you call the function its create a new connection.
its just mean a longer time to wait.