Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Southeast Michigan
    Posts
    5

    Question Unanswered: Dynamic SQL in MySQL trigger

    I am developing a web-based application that acquires raw sensor data (typically a 12 bit integer) and stores it in a MySQL database. To improve overall system performance, I would like to also store the actual value measured by the sensor, i.e., the output (floating point) of operating on the raw data with some function. The function in question is also stored in the database, thus exercising the function requires the use of dynamic SQL (or at least this is the only approach I have been able to figure out).

    To minimize the amount of time from when the web-app receives the sensor data until it replies, my intent was to have the conversion function be carried out by a trigger, namely an AFTER_myTable_UPDATE trigger. However, based on my reading here, http://www.mysqltutorial.org/mysql-t...mentation.aspx, a MySQL trigger cannot contain dynamic SQL.

    Can someone please suggest an approach that I can use to work around this issue?

    Thank you.

    p.s. I tried to post this to the 'official' MySQL forum, but the moderator refused to allow it. I guess that anything that might appear to be critical is not allowed. Not a smart policy from a customer service perspective...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe I missed a meeting, but the only reason I could imagine using dynamic SQL for this would be to allow expressions to be passed as columns. If you are using a simple static function, just code the function call into your MySQL trigger.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2002
    Location
    Southeast Michigan
    Posts
    5
    PatP,

    Sorry that my original post was unclear. We have multiple types of sensors, each of which has its own unique function. This is why the functions are stored in the DB.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How many sensor types do you have? I bet that a CASE statement would solve this problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2002
    Location
    Southeast Michigan
    Posts
    5
    Quote Originally Posted by Pat Phelan View Post
    How many sensor types do you have? I bet that a CASE statement would solve this problem.

    -PatP
    At present, we have about 20 types. We expect the number to increase regularly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •