title: Using Stored Function with C API & Prepared Statements
body: <p>I am attempting to use a stored function with a prepared statement but not having any success.</p>
  
  <p>MySQL prepares the statement but then does not execute it, no error is given, it just silently fails.</p>
  
  <p>This is the *function:</p>
  
  <pre><code>
  drop function if exists lineDistanceC;
  delimiter //
  CREATE FUNCTION lineDistanceC (la1 DOUBLE, lo1 DOUBLE, la2 DOUBLE, lo2 DOUBLE) RETURNS DOUBLE 
  BEGIN 
  SET @r = 6371;
  SET @lat1 = RADIANS(la1);
  SET @lon1 = RADIANS(lo1);
  SET @lat2 = RADIANS(la2);
  SET @lon2 = RADIANS(lo2);
  SET @x = (@lon2-@lon1) * COS((@lat1+@lat2)/2);
  SET @y = (@lat2 - @lat1);
  RETURN (SQRT((@x*@x) + (@y*@y)) * @r);
  END
  //
  delimiter ;
  </code></pre>
  
  <p>It works on the command line as expected, and substituting a hard coded double in the SELECT statement then allows it to work as expected, so the surrounding code is fine as well.</p>
  
  <p>I have enabled CLIENT_MULTI_STATEMENTS as a MySQL connect parameter, I suspect that it only relates to CALLing procedures, but it was a straw worth clutching.</p>
  
  <p>The statement is prepared using:</p>
  
  <pre><code>
  strcpy (sqlStr, "SELECT lineDistanceC(Y(pnt),X(pnt), ?, ?) FROM mytable");
  MYSQL_STMT *statement;
  if (mysql_stmt_prepare(statement, sqlStr, strlen(sqlStr))) {
      fprintf(stderr, "ERROR:mysql_stmt_prepare() failed. Error:%s\nsql:%s\n", mysql_stmt_error(statement), sqlStr);
  }
  
  </code></pre>
  
  <p>Followed by the required bind statements for the parameters and the results, then executed using:</p>
  
  <pre><code>
  if (mysql_stmt_execute(statement)) {
      fprintf(stderr, "mysql_stmt_execute(), failed. Error:%s\n", mysql_stmt_error(statement));
  }
  </code></pre>
  
  <p>As mentioned earlier if the function in the above SELECT statement is replaced by a hard code float (Eg. SELECT 2.3 .....) then it returns the hard coded number as expected.</p>
  
  <p>In reality the prepared statement code is inserted using macros, there are many hundreds of statements working as required so the preparation, binding or execution syntax is not the issue (and if it is incorrect above then it is my translation from the macros that is wrong) unless it needs to be different for use with functions.</p>
  
  <p>These are the log entries when one of the inputs is bound as the output:</p>
  
  <pre><code>
  Prepare SELECT ? FROM mytable
  Execute SELECT 51.36000061035156 FROM mytable
  </code></pre>
  
  <p>The is the log entry when I attempt to use the function:</p>
  
  <pre><code>
  Prepare SELECT lineDistanceC(latitude, longitude, ?, ?) FROM mytable;
  </code></pre>
  
  <p>As mentioned above, it silently fails without executing the statement, no errors in the log  or elsewhere.</p>
  
  <p>Any clues would be appreciated.</p>
  
  <p>*To give credit where it is due, this function was derived from a formula <a href="http://www.movable-type.co.uk/scripts/latlong.html" rel="nofollow">here</a></p>