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

541 Views

Avatar
obj63mc

6 November 2012 at 1:17pm Community Member, 24 Posts

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