Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Calling DB Query for stored procedure fails with 'Commands out of sync' on SS3.0


Reply


585 Views

Avatar
obj63mc

Community Member, 24 Posts

6 November 2012 at 1:17pm

Hello,

I am writing a custom search form on my SS3.x site but any time I run a DB::query command if there are any commands after that I always get "Commands out of sync; you can't run this command now"

Is there a way to call a custom sql query and get around this now in SS3.0

What I am doing is - on the form save action, depending upon what is submitted I am then calling the stored procedure.

public function SearchEventsByLocation($data, $form){
      if($data['Zip'] == "" && $data['State'] == ""){
         Session::set('LocationSearchFormError', 'Please enter a Zip Code or choose a State');         
         $this->redirectBack();
      } else {
         $data['Events'] = array();
         if($data['State'] != ""){            
            // get events in state
            $sql = "CALL getEventsInState('".Convert::raw2sql($data['State'])."');";      
            $results = DB::query($sql);
            for ($i = 0; $i < $results->numRecords(); $i++) {
          $record = $results->nextRecord();
               $data['Events'][] = $record;
            }
         } else {
            //get events by zip and radius
            $sql = "CALL getEventsNearZip(".Convert::raw2sql($data['Zip']).",".Convert::raw2sql($data['Radius']).".0);";      
            $results = DB::query($sql);
            for ($i = 0; $i < $results->numRecords(); $i++) {
          $record = $results->nextRecord();
               $data['Events'][] = $record;
            }
         }
         
      }
      return $this->owner->customise($data)->renderWith(array('Calendar_SearchEventsByLocation', 'Page'));
   }

The problem is then when ever I try to render the page - I am always getting the same error message.

If I don't run the queries the page will render fine. the only way I have found to work around this is to render the page first and then use js templating and an ajax call to then make the queries. I don't want to do this if I don't have to as it is not efficient to the user.

Thanks for any help,
Joe