0

I am relatively new to databases and SQL, and I am not clear on how or whether transactions may relate to a problem that I am trying to solve. I want to be able to temporarily set a value in a database table, run some query, and then clear out the value that was set, and I don't want any operations outside of the transaction to be able to see or alter the temporary value that was set.

The reason I am doing this is so that I can create predefined views that query certain data depending on variables such as the current user's id. In order for the predefined view to have access to the current user's id, I would save the id into a special table just before querying the view, then delete the id immediately afterward. I don't want to worry about some other user overwriting the current user's id while the transaction is in process. Is this a proper use for a transaction?

I am using H2 if that makes a difference.

grandinero
  • 1,155
  • 11
  • 18

2 Answers2

2

SET @tempVar=value;

I don't know if you really need to go through the pain of creating a temp table and setting the value. This seems far simpler.

You can then do - SELECT * FROM TABLE_NAME WHERE COLUMN=@tempVar;

Raj Kumar
  • 1,547
  • 14
  • 19
  • My wording may have been confusing. I am not planning to create a temp table, but to have a special table that would only be used for this purpose. Basically, the first row of the table would include the user's id and whatever other variables I need, the table would never have more than one row, and it would be empty whenever it's not being used. If I use variables as you suggest, how can I isolate them the way I want to? Do I still need to wrap the whole thing in a transaction, and do `SET @tempVar=NULL;` at the end? – grandinero Oct 30 '17 at 02:32
  • The problem with the special table approach is you don't know what the columns will be and how many values you need. You can have a pre-defined set of variables in a transaction and ignore the values that you do not need. And as variables will be a part of the transaction isolation is guaranteed by default which isn't the case with a table. – Raj Kumar Oct 30 '17 at 02:53
1

I think you want a Procedure or Function. Both can take a parameter as input.

ex.

CREATE PROCEDURE pr_emp
(
    @input INT
) 
AS 
SELECT * 
  FROM myTable
 WHERE emp_id = @input

ex.

CREATE FUNCTION v_empid (@input INT)
RETURNS TABLE
AS
RETURN
   SELECT * FROM myTABLE WHERE emp_id = @input;

These could let you to access information for an empid. For example:

SELECT * FROM v_empid(32)
Zorkolot
  • 1,899
  • 1
  • 11
  • 8
  • I think this is a good approach. H2 handles functions differently, but I think the principle is the same. I'll try it out. – grandinero Oct 30 '17 at 03:02
  • This solves my problem, but I'd still like to know if my understanding of transactions is correct. – grandinero Oct 30 '17 at 12:22
  • It sounds like you want to lock the entire table. A regular transaction may only lock the row. For example, someone can insert rows into the table while you transaction is still open, uncommitted. SQL Server has HOLDLOCK, I'm not sure if it is the same in H2... there is an article about HOLDLOCK here: [link](https://stackoverflow.com/questions/9842253/lock-table-while-inserting). – Zorkolot Oct 30 '17 at 13:32