Overview
Features
Download
Documentation
Community
Add-Ons & Services

Call a MySQL store procedure with POCO

Please post support and help requests here.

Call a MySQL store procedure with POCO

Postby dbarzo » 09 Jul 2014, 12:34

I'm working with poco libraries 1.5.2-all and I have to call a MySQL store procedure defined as:

Code: Select all
CREATE PROCEDURE AddFonMeasure(IN fono_id         INT UNSIGNED,
                               IN fon_rcd_id      INT UNSIGNED,
                               IN fon_Leq         FLOAT,
                               IN fon_LsMax       FLOAT,
                               IN fon_mem_block   INT,
                               INOUT Leq_state    INT,
                               INOUT LsMax_state  INT)                               
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN

  DECLARE max_fon_Leq     FLOAT;
  DECLARE max_fon_LsMax   FLOAT;

  SELECT fon_Ls_Max, fon_Leq_Max
  INTO max_fon_LsMax, max_fon_Leq
  FROM tbl_utenti
  WHERE fon_id = fono_id;

  IF fon_Leq > max_fon_Leq THEN
    SET Leq_state = 1;
  ELSE
    SET Leq_state = 0;
  END IF;

  IF fon_LsMax > max_fon_LsMax THEN
    SET LsMax_state = 1;
  ELSE
    SET LsMax_state = 0;
  END IF;

  INSERT INTO tbl_misure
              (m_fon_id, m_timestamp, m_fon_rcd_id, m_fon_Leq, m_fon_LsMax, m_Leq_state, m_LsMax_state)
              VALUES(fono_id, now(), fon_rcd_id, fon_Leq, fon_LsMax, Leq_state, LsMax_state);

  INSERT INTO tbl_misure_30m
              (m_fon_id, m_timestamp, m_fon_rcd_id, m_fon_Leq, m_fon_LsMax, m_Leq_state, m_LsMax_state, m_fon_mem_block)
              VALUES(fono_id, now(), fon_rcd_id, fon_Leq, fon_LsMax, Leq_state, LsMax_state, fon_mem_block);


END$$


The Procedure works if I run it into phpmyadmin, so I'm pretty sure its sintax is correct. I'm using the following code to call it:

Code: Select all
EDBReturnValues DataHandler::AddFonMeasure(TDBFonValues& in_values) {

  try {
    int Leq_state = 0;
    int LsMax_state = 0;

    *m_pSession << "{call AddFonMeasure(?, ?, ?, ?, ?, ?, ?)}",
                   useRef(in_values.fon_id),
                   useRef(in_values.fon_rcd_id),
                   useRef(in_values.fon_Leq),
                   useRef(in_values.fon_LsMax),
                   useRef(in_values.fon_mem_block_id),
                   io(Leq_state),
                   io(LsMax_state),
                   now;

    in_values.Leq_state = Leq_state;
    in_values.LsMax_state = LsMax_state;
  }
  catch (Poco::Data::MySQL::MySQLException e) {
    MAIN_LOG.error(Poco::format("[AddFonMeasure] SQL Error : (%s)", e.message()));
    return RV_QUERY_EXCEPTION;
  }
}


But I get the following error:

Code: Select all
SQL Error : ([MySQL]: [Comment]: mysql_stmt_prepare error   [mysql_stmt_error]:
              You have an error in your SQL syntax; check the manual that
              corresponds to your MySQL server version for the right syntax
              to use near '{call AddFonMeasure(?, ?, ?, ?, ?, ?, ?)}' at line 1
              [mysql_stmt_errno]: 1064  [mysql_stmt_sqlstate]: 42000    [statemnt]:
              {call AddFonMeasure(?, ?, ?, ?, ?, ?, ?)})


since SQLSTATE: 42000 is ER_DBACCESS_DENIED_ERROR, is this a syntax problem or access privilege problem?

Regards
dbarzo
 
Posts: 38
Joined: 14 Jan 2013, 17:15
Location: Italy

Re: Call a MySQL store procedure with POCO

Postby dbarzo » 11 Jul 2014, 15:02

Hi, I tried to call it from mysql command line ed it works fine.
Any idea?

Thanks in advance,
Daniele.
dbarzo
 
Posts: 38
Joined: 14 Jan 2013, 17:15
Location: Italy


Return to Support

Who is online

Users browsing this forum: No registered users and 3 guests

cron