Database classes for SQL Server 2005 and SQL Server 2008 (using php_sqlsrv.dll from the Microsoft Data Programmability t

[eluser]imorris[/eluser]
ccddarkness, please follow these steps to fix the problem:

in sqlsrv_result.php make your function "num_rows" look like below.

function num_rows()
{
$num_rows=0;
return !$num_rows = sqlsrv_num_rows($this->result_id) ? 0 : $num_rows;

}

[eluser]dcallan[/eluser]
num_rows() always seems to return 1 for me. :-( I know the developer mentioned 'pain' in relation to this method but I thought that was fixed? I using static cursor.

[eluser]vs.dev[/eluser]
I am using the 2.0 version of the native php driver. I think I fixed some issues with row count and calling stored procedures.
Please handle with care I'm just experimenting...


Starting from http://www.phrenzy.org/code/sqlsrv-1.1.tar.gz

sqlsrv_driver.php
- Had problems with charset, so left default settings
- Added ReturnDatesAsStrings parameter, this configuration item must be added to application/config/database.php
$db['default']['returndates_as_strings']

Code:

function db_connect($pooling = false)
    {
        // Check for a UTF-8 charset being passed as CI's default 'utf8'.
        $character_set = (0 === strcasecmp('utf8', $this->char_set)) ? 'UTF-8' : $this->char_set;

        $connection = array(
            'UID'                => empty($this->username) ? '' : $this->username,
            'PWD'                => empty($this->password) ? '' : $this->password,
            'Database'            => $this->database,
            'ConnectionPooling' => $pooling ? 1 : 0,
            /*'CharacterSet'        => $character_set,*/
            'ReturnDatesAsStrings'=>    empty($this->returndates_as_strings) ? FALSE : $this->returndates_as_strings,
        );
        
        // If the username and password are both empty, assume this is a
        // 'Windows Authentication Mode' connection.
        if(empty($connection['UID']) && empty($connection['PWD'])) {
            unset($connection['UID'], $connection['PWD']);
        }

        return sqlsrv_connect($this->hostname, $connection);
    }

- Added a switch so stored procedures can be called with SQLSRV_CURSOR_FORWARD, while all other queries are called with the SQLSRV_CURSOR_STATIC. This is only for stored procedures which return a resultset (no parameter handling yet).

Code:

function _execute($sql)
    {
        $sql = $this->_prep_query($sql);

        $options  = array(
            'Scrollable' => SQLSRV_CURSOR_STATIC
        );
        if (stripos($sql, '{call') !== FALSE)
        {
            $options['Scrollable'] = SQLSRV_CURSOR_FORWARD;
        }

        return sqlsrv_query($this->conn_id, $sql, null, $options);
    }

sqlsrv_result.php
- for some reason function must return TRUE in case of stored procedures, this should return correct row count when using SQLSRV_CURSOR_STATIC for standard queries

Code:

function num_rows()
    {
        $num_rows= sqlsrv_num_rows($this->result_id);
        return $num_rows ? $num_rows : TRUE;
    }

[eluser]broadband[/eluser]
Using:
- IIS 7.5 & FastCGI
- PHP 5.3.5 (thread safe)
- MSSQL MSDE
- sqlsrv 2.0 lib(dll)
- http://www.phrenzy.org/code/sqlsrv-1.1.tar.gz

I have problems with performance when using:

Code:

$stmt = sqlsrv_query($conn, $tsql, null, array(
            'Scrollable'                => SQLSRV_CURSOR_STATIC | KEYSET,
            'SendStreamParamsAtExec'    => true
        ));

Above code works slow but num_rows() works.


If I use SQLSRV_CURSOR_FORWARD everything works quite fast, but num_rows doesn't work as is described in API. PHP outputs some NOTICE. For this to work I also had to change this two methods:

Code:

function _execute($sql)
    {
        $sql = $this->_prep_query($sql);
        return sqlsrv_query($this->conn_id, $sql);
    }

    function num_rows()
    {
        //return !$num_rows = sqlsrv_num_rows($this->result_id) ? 0 : $num_rows;
        // if i use select statement I use count with select count(*) where
        // the_same_conditions;
        return 1;
    }

Any ideas how to keep the performance and still have num_rows functionality?

[eluser]greenflash[/eluser]
Hello, have you figured out the num_rows issue? I'm very interested in this, as I'm eager to start using CI, and have a project with a mssql 2008 db, standard php + adodb with mssqlnative driver which is working perfectly and I'm wondering if this driver could be the solution to move to CI gradually.

Anyway, thanks a lot for the development for CI, I'll try and see how it goes!

[eluser]broadband[/eluser]
You could use:
$q = "select row1, row2 from table;";
$q_count = "select count(row1) from table";

If adodb with mssql native driver works ok(num_rows, result in correct charset, utf8...) we could make new "wrapper" for CI.

[eluser]greenflash[/eluser]
I agree about the new wrapper. The one provided here is very useful though. After the tips found in this thread (adding the escape strings and the count string) all is quite stable.

Still have a problem with the query "Recordcount". I could do a quick "select count(*) where.." but the real thing about CI ActiveRecord to me is to use the db->where, db->like etc. The thing is that count_all_records will reset (clear) those values after compiling and running, as any other query. What I did was to modify active record definition for count_all_results as below; when passing false for $reset, values will be kept for the db->get that I'll run after the count.

Workaround for now.

<pre>
function count_all_results($table = '', $reset = true)
{
if ($table != '')
{
$this->_track_aliases($table);
$this->from($table);
}

$sql = $this->_compile_select($this->_count_string . $this->_protect_identifiers('numrows'));

$query = $this->query($sql);
if ($reset) //added
$this->_reset_select(); //added

if ($query->num_rows() == 0)
{
return 0;
}

$row = $query->row();
return (int) $row->numrows;
}
</pre>

[eluser]ballmatic[/eluser]
I just got handed a project using PHP/CodeIgniter and SQL Server 2008 R2. I've downloaded and installed the Microsoft PHP+SQL 2.0 drivers and these unofficial drivers. I copied some of the code in here to fix some of the issues. But I'm having a problem with the offset options when paging.

Code:

$this->db->limit(10, 5);
$query = $this->db->get('table_name');

shorthand version doesn't work either:

Code:

$query = $this->db->get('jobs', 5, 10);

Doesn't give me the results back that I'm looking for. Am I doing something wrong?

Is there any sort of official support from EllisLab? Or anybody have ideas for making a 100% working driver? I've never done so much Google searching!

[eluser]greenflash[/eluser]
[quote author="ballmatic" date="1326914705"]I just got handed a project using PHP/CodeIgniter and SQL Server 2008 R2. I've downloaded and installed the Microsoft PHP+SQL 2.0 drivers and these unofficial drivers. I copied some of the code in here to fix some of the issues. But I'm having a problem with the offset options when paging.

Code:

$this->db->limit(10, 5);
$query = $this->db->get('table_name');

shorthand version doesn't work either:

Code:

$query = $this->db->get('jobs', 5, 10);

Doesn't give me the results back that I'm looking for. Am I doing something wrong?
[/quote]

Been myself in the same battle. Problem seems to be really SQL Server that doesn't support any LIMIT/OFFSET variant. Currently (prototyping only), I'm using ADODB adapted for codeigniter for SQLServer2008 and some tweaks in CI paginator; the trick I guess is that ADODB has some internal workaround for SQLServer to "skip" records in a cache in order to mimic the OFFSET utility, CI ActiveRecord is more pure. SQLServer 2012 will finally have something for this functionality to work natively (only will work on WinServer 2008+/Win7)

Code:

SELECT email FROM emailTable WHERE id=3
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;