+- UserSpice (https://userspice.com/forums)
+-- Forum: Support Center (https://userspice.com/forums/forumdisplay.php?fid=23)
+--- Forum: UserSpice 4.3 and Below (https://userspice.com/forums/forumdisplay.php?fid=26)
+--- Thread: Stored Procedures / Routines + general US startup questions (/showthread.php?tid=1309)
Pages:
1
2
RE: Stored Procedures / Routines + general US startup questions - Developer_J - 01-27-2019
Thanks for looking at this Brandin.
The function I added to the DB class:
Code:
public function nextRowset(){
if(!($this->_query->nextRowset())) return [];
return $this->_query->fetchALL(PDO::FETCH_OBJ);
}is to allow access to the second result set (and any further) returned from my stored procedure. As the $this->_query object/var is private to DB class I cannot call the pdo nextRowset() function on the $result variable in my code when using $result = $db->query(....etc.
The function I posted second was just my example of how I use the function added to the DB class. This is in one of my misc files somewhere:
Code:
private function my_fn($amount){
$result = $db->query("CALL MY_PROC(?,?,?)",array($user->data()->id,$amount,NULL));
$result_A = $result->results();
$result_B = $result->nextRowset(); // <-- added to DB class
var_dump($result_A, $result_B);
exit();
}For completeness, my stored proc essentially ends like this:
Code:
proc:BEGIN
-- GENERATE AND INSERT to TABLES
SELECT * from table_a WHERE ID = last_insert_id(); -- result set A
SELECT * from table_b WHERE guid LIKE concat(@guid_begin,'%'); -- result set B
END;As i'm very rusty on my pdo (due to PEAR) I had to google to get the nextRowset() fn name, This is one of the SO pages I came across:
SO multi query pdo
Like yourself, and mudmin, said - I think keeping track of the db.php file and re-adding my extra function if it changes is possibly the best solution for now, I've created a DB.mine file just in case.
On a separate note, I saw the vid on YT for version 4.4, looks great and looking forward to trying it out.
Thanks
RE: Stored Procedures / Routines + general US startup questions - Brandin - 01-28-2019
Hi Developer_J,
Your solution seems proper after further explanation, and this information will even be useful for myself given the nature of my work, so thank you for this! We're happy to hear you enjoyed the 4.4 video, @mudmin did a fantastic job with it! Looking forward to hearing more from you if you require any support.
Brandin.