Server closes connection when running sql

[eluser]Kyle123[/eluser]
Hi All,

I'm just getting started with CI and right about now I feel like throwing my computer out of the window

I'm running a development LAMP stack (turnkey) on a virtual machine and using MSSQL 2008 for the back end database.

I have a basic set-up (with a customised controller class that works perfectly) comprising of the following:

Model:

Code:

<?
class shouts_model extends CI_Model {

public function __construct()
{
  $this->load->database();
}

public function get_newest() {
  $sql = "Exec sp_wallboard_topShouts";
  $query = $this->db->query($sql);
  return $query->result();
}

Controller:

Code:

<?php
class Shoutboard extends MY_Controller {

public function __construct()
{
  parent::__construct();
  $this->load->model('shouts_model');
}


public function index(){
  
  $this->title = "Shout! Board";
  $this->keywords = "Shout Reporting";
  $this->javascript['page'] = "wallboard.js";
  $this->css['shout'] = "shoutboard.css";
  
  $this->data['top_shouts'] = $this->shouts_model->get_newest();
  $this->_render('pages/shoutboard');
}

}

View:

Code:

<ul>
&lt;? echo $top_shouts; ?&gt;
&lt;? foreach($top_shouts as $row): ?&gt;
<li>
  <div class="postit">
   <span class="label">To: &lt;? echo $row->from ?&gt;</span>
   <span class="label">From: &lt;? echo $row->sent ?&gt;</span>
   <p class="message">&lt;? echo $row->note ?&gt;</p>
  </div>
</li>
&lt;? endforeach; ?&gt;
</ul>

when the execution hits this line (as far as I can tell):

Code:

$query = $this->db->query($sql);

The server closes the connection and returns no data (error 324 in chrome, but I also get it in IE) and no error is displayed or in the logs.

All the rest of my code executes properly and this is the first issue that I've run into that's stumped me, at first I thought it was the recordset being returned by the database, but it is very simple and nothing special, 3 text fields and a date.

Any help/pointers would be very much appreciated (and would stop me having to buy a new computer!)

Thanks in advance

[eluser]InsiteFX[/eluser]
Try adding the parent::__construct

Code:

class shouts_model extends CI_Model {

public function __construct()
{
  parent::__construct();

  $this->load->database();
}

public function get_newest() {
  $sql = "Exec sp_wallboard_topShouts";
  $query = $this->db->query($sql);
  return $query->result();
}

[eluser]Kyle123[/eluser]
Thanks, but no change

I can substitute the sproc with another and it works though so I'm starting to think CI has an issue with the returned recordset. Are there any known issues with any mssql datatypes and CI?

[eluser]CroNiX[/eluser]
Are you sure you're not hitting some sort of memory or time limit? Either PHP, web server or your SQL server? Have you checked the logs for each of the services?

[eluser]Kyle123[/eluser]
Yes to both I'm afraid, it's odd. I've tracked it down to a single field in the query. Having replaced the sproc with SQL, executing the following works:

Code:

public function get_newest() {
  $sql = "select top 20
    (rtrim(sender.actfirstname)+' '+rtrim(sender.actsurname)) as [from],
    (rtrim(tot.actfirstname)+' '+rtrim(tot.actsurname)) as [sent],
    convert(text,rntNote) as [note]
     from tb_rntRepnote
     inner join tb_actActor Sender
      on sender.ActKeyactor = RntSendTo
     inner join tb_actActor tot
      on tot.actkeyactor = rntsentby
    where rntCreatedwhen > '2013-01-02 18:55:00'
    and rntclassification = 13
    Order by rntCreatedWhen Desc";
  $query = $this->db->query($sql);
  return $query->result();

But this doesn't:

Code:

public function get_newest() {
  $sql = "select top 20
    (rtrim(sender.actfirstname)+' '+rtrim(sender.actsurname)) as [from],
    (rtrim(tot.actfirstname)+' '+rtrim(tot.actsurname)) as [sent],
    convert(text,rntNote) as [note],
    rntCreatedwhen as createdWhen
     from tb_rntRepnote
     inner join tb_actActor Sender
      on sender.ActKeyactor = RntSendTo
     inner join tb_actActor tot
      on tot.actkeyactor = rntsentby
    where rntCreatedwhen > '2013-01-02 18:55:00'
    and rntclassification = 13
    Order by rntCreatedWhen Desc";
  $query = $this->db->query($sql);
  return $query->result();

rntCreatedwhen is a smalldatetime field.

substituting with the following works:

Code:

convert(datetime,rntCreatedwhen) as createdWhen

Which is fine now I understand it, but it still seems like odd behaviour to me - but having Googled smalldatetime and PHP - there are loads of hits and it appears to be quite a common issue.