Call a stored procedure

[nickrth]

Hi there,

I'd be super grateful if someone could please help me out.

I've created a stored procedure that I want to call via a button on a from, and pass it a variable. Here's what I've done:
1. Saved a stored procedure in the mySql database called copy_all_mealplan which takes in one variable
2. Put an input field on my form with the code copy_from_unit for the variable
3. Created a procedure in nuBuilder that uses that input field and calls the stored procedure:

$COPYFROMUNIT = '#copy_from_unit#';
$sql1 = "call copy_all_mealplan($COPYFROMUNIT)";
query($sql1);

4. And finally a button on the form which runs that nuBuilder procedure

But it doesn't work. Hoping someone can please point out what I've missed, or if my syntax is wrong, or if my approach is flawed...

Thanks so much.

[kev1n]

Hi,

I created an example in my Code Library:
https://github.com/smalos/nuBuilder4-Co ... /README.md

[nickrth]

Thanks so much for your reply kev1n !

With a bit of fiddling I got it to work with this in the Nubuilder procedure:

Code: Select all

$copyfromunit = '#copy_from_unit#';
function callSpCopyAllMealplan($copyfromunit) {
try {
      $cn = new PDO("mysql:host=localhost;dbname=nucatering;", "username", "password");
      $stmt = $cn->prepare("CALL copy_all_mealplan(?)");
      $stmt->bindParam(1, $copyfromunit);
      $stmt->execute();
   }catch(PDOException $ex){
      nuDisplayError($ex->getMessage());
   }
}
callSpCopyAllMealplan('#copy_from_unit#');

However I have a further question. I seem to have to hard code the values in the PDO call. When I declare the variables up to it says "ERROR invalid data source name". Any clues?

Code: Select all

$servername = "localhost";
$database = "nucatering"; 
$username = "username";
$password = "password";
$sql = "mysql:host=$servername;dbname=$database;";
$copyfromunit = '#copy_from_unit#';
function callSpCopyAllMealplan($copyfromunit) {
try {
      $cn = new PDO($sql, $username, $password);
      $stmt = $cn->prepare("CALL copy_all_mealplan(?)");
      $stmt->bindParam(1, $copyfromunit);
      $stmt->execute();
   }catch(PDOException $ex){
printf("ERROR %s", $ex->getMessage());
      nuDisplayError($ex->getMessage());
   }
}

[kev1n]

The function I posted earlier works perfectly for me. Calling nuRunQuery(''); with an empty string will return the db settings as defined in nuconfig.php. There's no need for hard-coding them.

[nickrth]

Ah! Yes, it's working for me now (I must have done something wrong before). Thank you so much!