Jump to:

3430 Posts in 1057 Topics by 734 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Calling DB Query for stored procedure fails with 'Commands out of sync' on SS3.0

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 510 Views
  • obj63mc
    Avatar
    Community Member
    24 Posts

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

    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

    510 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.