MySQLi Library with Stored Procedures & Multiple result sets support |
[eluser]Atasa[/eluser]
I was very anxious every time a new release of Ci was coming out to see weather if mysqli driver was more enhanced and complete, and was always tweaking the mysqli_driver to support the projects of mine that use Stored Procedures, and sometimes wanted to have Multiple Result Sets in one go, instead of writing 2 or more sql queries with the same pattern. Maybe I am too lazy but I am happy about it. Therefor one day I decided to write a library to do this and not worry any more about CI system files. So here it is: Code: <?php if (!defined('BASEPATH')) exit('No direct script access allowed');
[eluser]Atasa[/eluser]
OK if found out that there is not much use from what i posted before. So I decided to make a real library OO style at least, and much more useful. What is missing: The error handling that the query might return, the multiple results function, a more detailed documentation. Will come up some time soon. So here is the library file: Code: <?php if (!defined('BASEPATH')) exit('No direct script access allowed'); Usage: On your model you could say: Code: $this->load->library('mydb'); more with result then just give it to you, which I like better. Cheers A.
[eluser][email protected][/eluser]
I really like CI but mulple resultsets support is missing! OMFG!
[eluser][email protected][/eluser]
My little contribution to the CI: DB_result.php Code: function next_result() { return FALSE; } mysqli_result.php Code: /**
[eluser]Atasa[/eluser]
[quote author="[email protected]" date="1246171949"]I really like CI but mulple resultsets support is missing! OMFG![/quote] Well I haven't got the time really to go through all way, but I still have the motivation.
[eluser]Tim Brownlaw[/eluser]
I've running version 1.7.2 and guess what? I've had to modify my mysqli_results.php and DB_result.php file as above, which I've had to do on older versions. Lucky I knew to go back to my "Already Fixed" version and make the changes to this latest version. I dunno, is it just a small few that actually use Stored Procedures that constantly come upon this poor forgotten little function that unbreaks your code? Derek - When are you going to add the function next_result() into CI? If one client/student upgrades CI on their website that utilizes mysqli = busted website. Cheers Tim
[eluser]Tim Brownlaw[/eluser]
Well I'm now into returning multiple result sets back from my Stored Procedures. The changes I had to make to mysqli_result.php work just dandy on my development PC. It's when I put the site up onto my hosting account that things went upside down. The calls to the Stored Procedures only return the 1st result set and ignore everything else. Don't you hate that! It's kind of put my project into screaming halt mode! So I wrote an extensive set of test scripts (using native PHP ) with installer for the demo tables and Stored Procedures up on the same hosting and it works a treat. Native PHP = Works - Local and Host CI Implementation - Work locally, Don't work on the Host. The sample native PHP Code is up at http://www.webatories.com/testsp I'll put up the CI changes as soon as I recover from my extensive messing around in the code and provide the complete code for download when I get the chance. I could go and try another framework but I'd rather contribute to CI as I kind of like even with it shortcomings. That's the whole point of this forum. To help make CI better. Cheers Tim Brownlaw
[eluser]Atasa[/eluser]
I would never touch any CI core libraries, it is very sad then when you want to upgrade to a new version. But the limitation of CI is not for multiple results only. you can't really call a stored proc in the first place.
[eluser]Tim Brownlaw[/eluser]
[quote author="Atasa" date="1270475338"]I would never touch any CI core libraries, it is very sad then when you want to upgrade to a new version. [/quote] That's a valid argument which I've also stated in the past. I decided it was simpler ( in the end ) to just put the required changes into the neccessary "core" files. Cheers Tim
[eluser]binbink[/eluser]
the mentioned solution works fine for me when the procedure returns multiple rows. $result = $this->mydb->Query($sql); but what if routine does not return any data? e.g. it only updates two tables with the specified data. if I use $this->db->query I see Error Number: 2014 Commands out of sync; you can't run this command now if I use $this->mydb->Query($sql); I do not see any error messages; but the data is not updated. NOTE: there should not be an issue with routine itself since if I print the query in question and try to execute this in MySQL Workbench - tables are updated correctly. Let me know if there are any known/possible solutions for this. Thank you |
Welcome Guest, Not a member yet? Register Sign In |