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


4 Posts   640 Views

Avatar
obj63mc

Community Member, 25 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

Avatar
yommy

Community Member, 2 Posts

24 October 2014 at 8:08pm

Hi obj63mc,
did you managed to solve this issue?

I'm trying to do the same thing with no success...

Avatar
obj63mc

Community Member, 25 Posts

25 October 2014 at 2:48am

Hi Yommy,

What I basically had to do was set some session variables in that function for what was posted, then instead of running the sql query there, redirect to another action on the controller. Then on the template for that new action I would look at the session variables and then run the sql query there. For some reason running this complex query then trying to customize a view with it, just wouldn't work.

Avatar
yommy

Community Member, 2 Posts

25 October 2014 at 3:16am

I think i understand what you have done, it's pretty much what i thought so i will try that way too.

Thank you so much for your prompt answer!