As part of my audit logging process, I need to set a variable @user_id in my code before running the INSERT INTO... statement. INSERT will initiate a TRIGGER which uses the @user_id.
I have tried the following queries:
SET @user_id = 5
This does not work because ResultSet cannot be NULL (and this query returns nothing)
SELECT 51 INTO @user_id
This does not work because again, ResultSet cannot be NULL in Java
SELECT @user_id := 51
This does not work because the : character is translated into a parameter token when running queries using a PersistenceManager and a Query object.
So, using DataNucleus, how can I set a variable so my TRIGGER statement succeeds with the necessary information for my auditing purposes?
My Java code to run the queries is as follows:
PersistenceManager pm = getPersistenceManager(); // My own function to get the PersistenceManager
Query q = pm.newQuery("javax.jdo.query.SQL", "QUERY HERE");
q.execute();
If this cannot be done, what other options can I try to achieve the same result?
Edit: I am using Google App Engine, and therefore stored procedures are not available to me.
I couldn't find a way around the issues described above, so instead used a different approach which I'll explain:
I created a table named transaction_properties which has two columns: user_id and connection_id.
Before each transaction commit, I INSERT into the transaction_properties table with the ID of the user (which I know at this stage), and the MySQL placeholder CONNECTION_ID(), which outputs the current connection ID.
In my trigger, I use the following statements to find the user_id for this transaction:
DECLARE user_id bigint;
SELECT `userid` INTO user_id FROM `transaction_properties`
WHERE `transaction_id` = CONNECTION_ID() LIMIT 1;
IF user_id IS NULL THEN
SET user_id = -1;
END IF;
I can then reference the declared variable user_id in my INSERT to the audit table elsewhere in the trigger.