Solved

Handling two tables from a PDO Call to a MS SQL Procedure

Posted on 2010-08-17
4
347 Views
Last Modified: 2013-12-13
HI,

I have a procedure call to a MS SQL DB, the procedure returns two tables, how should I handle the tables, I'm quite able to deal with one. I'm doing it with 2 single calls at present but the page takes too long to call.

My get tables method:

 
public function getTables($procname,$parameter) {
      //see : http://www.php.net/manual/en/pdo.prepared-statements.php example #10 and #11
      $vars = explode(",", $parameter);
      $num = count($vars);
      //create holders for calls
      for ($i = 0; $i <= $num; $i++) {
         $holder.="?";
         if ($i != $num)
         {
            $holder.=",";
         }
      }
//var_dump($holder);
//var_dump($vars );
      $stmt = DBO::prepare("EXECUTE " . $procname . " " . $holder);
      // TODO build bind, may need to change type for strings and ints and others
      // perhaps look to put into dbcalls
      for ($i = 0; $i <= $num; $i++) {
         $stmt->bindParam($i + 1, $vars[$i], PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 4000);
      }

// call the stored procedure
      try
      {
        $stmt->execute();
      } catch (PDOException $e)
      {
//echo "\nPDO::errorCode(): ";
//print $dbh->errorCode();
         var_dump($e);
         // var_dump($e);
      }
      $data = ($stmt->fetchAll(PDO::FETCH_ASSOC));
      $stmt->closeCursor();
     // var_dump($data);
      return $data;
   }

Open in new window

0
Comment
Question by:darren-w-
  • 2
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
RQuadling earned 500 total points
ID: 33463147
Once you've processed the first result set, you need to get the next result set.

http://docs.php.net/manual/en/pdostatement.nextrowset.php contains the documentation for PDOStatement->nextRowset.

PDOStatement->nextRowset() returns true/false if there is/is not a next result set.
0
 
LVL 13

Author Comment

by:darren-w-
ID: 33463353
Thats great, thanks.
0
 
LVL 13

Author Comment

by:darren-w-
ID: 34205727
Coming back to this, and it looks like the MSSQL driver does not support this feature?
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 34205779
@Darren, you may be running an older version of the driver.

http://msdn.microsoft.com/en-us/library/ff628153(SQL.90).aspx
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now