What is best practice for referencing data rows within stored procedures – via PK, code column or data value?

Suppose you have a table for colours with columns:

  • id = automatically incrementing integer, primary key
  • code = short code reference for the colour, unique
  • colour = human-readable name of colour, unique

Example values might be:

  • 1, BL, Blue
  • 2, GR, Green

Now imagine you have a stored procedure that, at some point, needs to reference this table. Let’s say the business logic says to obtain the colour "Green". To achieve this, you could have any of the following three WHERE clauses:

  • WHERE id = 2
  • WHERE code = GR
  • WHERE colour = Green

Now, if the system is designed such that it is agreed that a code value, once created, never changes, then, in my view, that is the best column to reference because:

  • It is an alternate key
  • It is human-readable for people who maintain the code
  • It will not be impacted when the business decides to change the colour value to ‘Sea Green’

However, if a legacy table lacks such code values, what, in your opinion, is best practice? To reference the id column, or the colour column?

If you reference the id column, the code is not readable unless you then also add comments – you shouldn’t have to comment simple things like this. It sucks figuring out what statements like WHERE id not in (1, 7, 17, 24, 56) mean.

I’m not sure how often, in reality, the id value might change – but consider if you run a script during development to insert new colours but then delete those and insert some more. If your stored procedure references the id values from that last set of colours inserted but when you create your new colours in your next environment you skip the step that inserted the colours which ended up deleted, then the id values won’t match in that next environment. Bad practice, but it can happen – a developer develops their script on a dev instance not thinking that the id values will conflict with production (which, for example, may have had additional colours created manually by the business before your colour creation script runs).

If you reference the colour column, you run the risk that if the business does ask to update the description from ‘Green’ to ‘Sea Green’, that your procedure will begin to fail.

I suppose a further solution is to implement the code column when you need it, if it isn’t there already – probably the best solution?

MySQL – Call a View inside a Stored Procedure

Let’s say, I have a table as follows

tbl_1

id             name                 status               usertype                         flag ================================================================================================ 1              Name 1                  1                    2                               0 2              Name 2                  1                    3                               0 3              Name 3                  1                    1                               0 4              Name 4                  1                    4                               0 5              Name 5                  1                    5                               0  

HTML PAGE

<select id="type" name="type">    <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> <option value="4">4</option> <option value="5">5</option>  </select> 

My requirement is that I need to get all the data from table where the usertype is selected from the html page by the user (could be 1, 2, 3,4,5, etc.)

I want to create a view, and call that inside stored procedure

I cannot pass an argument inside of a View. So, I was told to create a stored procedure, and call the view inside it. The problem is, I don’t know how to do it.

How to get count of an object, through 3 different tables in postgres with ID’s stored in each table

I’m currently using Postgres 9.6.16.

I am currently using 3 different tables to store a hypothetical users details.

The first table, called contact, this contains:

ID, Preferred_Contact_Method 

The second table, called orders, This contains:

ID, UserID, Contact_ID (the id of a row, in the contact table that relates to this order) 

The Third Table, Called order_details

ID, Orders_ID (the id in the orders table that relates to this order details) 

The tables contain other data as well, but for minimal reproduction, these are the columns that are relevant to this question.

I am trying to return some data so that i can generate a graph, in this hypothetical store, There’s only three ways we can contact a user: Email, SMS, or Physical Mail.

The graph is supposed to be 3 numbers, how many mails, emails, and SMS we’ve sent to the user; since in this hypothetical store whenever you purchase something you get notified of the successful shipment, these methods are 1:1 to the order_details, so if there’s 10 order_detail rows for the same user, then we sent 10 tracking numbers, and since there can be multiple order_details (each item has a different row in order_details) in an order, we can get the count by counting the total rows of order details belonging to a single user/contact, then attributing to what kind of contact method that user preferred at the time of making that order.

To represent this better: If a new user makes a new order, and orders 1 apple, 1 banana, and 1 orange. For the apple, the user set preferred tracking number delivery as SMS, for the banana, they set it to EMAIL, for the orange, they thought it would be funny to set the tracking number delivery via MAIL. Now, i want to generate a graph to this users preferred delivery method. So i’d like to query all those rows and obtain:

SMS, 1 EMAIL, 1 MAIL, 1 

Here’s a SQL Fiddle link with the schema and test data: http://sqlfiddle.com/#!17/eb8c0

the response with the above dataset should look like this:

method | count SMS,     4 EMAIL,   4 MAIL,    4 

How to get applied stored procedure for inserted row in mysql php query

$ query = "CREATE PROCEDURE IF NOT EXISTS Insertion(IN firstname varchar(40),IN lastname varchar(40),IN email varchar(40),IN department varchar(40),IN doj date,IN basicpay int(11)) BEGIN

DECLARE HRA decimal(20,2); DECLARE DA decimal(20,2); DECLARE PF decimal(20,2); DECLARE NET_SALARY decimal(20,2);

IF department = ‘HUMAN RESOURCE’ THEN SET HRA = (5/100)*basicpay; SET DA = (7/100)*basicpay; SET PF = (10/100)*basicpay;

ELSEIF department = ‘MARKETING’ THEN SET HRA = (5/100)*basicpay; SET DA = (7/100)*basicpay; SET PF = (10/100)*basicpay;

ELSEIF department = ‘PRODUCTION’ THEN SET HRA = (5/100)*basicpay; SET DA = (7/100)*basicpay; SET PF = (10/100)*basicpay;

ELSEIF department = ‘FINANCE AND ACCOUNTING’ THEN SET HRA = (5/100)*basicpay; SET DA = (7/100)*basicpay; SET PF = (10/100)*basicpay;

ELSE SET HRA = (5/100)*basicpay; SET DA = (7/100)*basicpay; SET PF = (10/100)*basicpay;

END IF; SET NET_SALARY = basicpay+HRA + DA + PF;

insert into employees(FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT,DATE_OF_JOINING,BASIC_PAY,HRA,DA,PF,NET_SALARY) values(firstname,lastname,email,department,doj,basicpay,HRA,DA,PF,NET_SALARY);

END";

Can a user benefit from personal powers stored in an empowered object?

I realized while writing this question that I have another issue with empowered objects. Specifically, it is the object using the stored powers, rather than the wearer/wielder/user. Can such an object "target" the user with personal or "self-only" powers? I intend to have a more powerful psionic NPC loan a PC a Psychometabolic item, but most of the powers it’ll have access to are self-targeting and I realized I wasn’t sure if the PC could even use them.

Pause and resume execution of stored procedure

There are two processes(p1, p2) that may run simultaneously. p2 is a scheduled execution of SPROC while p1 consists of a group of stored procedures that is triggered on the request. Execution of p1 while p2 is in progress can create issues. Only one should run at a time. There are three ways to solve this problem

  1. When p1 starts check whether p2 is in progress and wait until it completes. p2 can run for more than a day and it might not be a preferable solution
  2. Kill p2, complete p1 and then restart p2. Killing and restarting p2 isn’t safe due to nature of the sproc
  3. pause p2 and resume p2 when p1 completes

Questions

1. How can I search and stop store procedure being executed?

2. Is there any way I can pause and resume a stored procedure that is being executed?

Strange behaviour on stored procedure

I have the following mysql stored procedure:

DELIMITER // CREATE PROCEDURE INSERT_INTO_WORKDAY(IN deviceName VARCHAR(16), IN cardUID VARCHAR(14))  BEGIN     SET @projectId = (SELECT MIN(ID) FROM PROJECT WHERE DEVICEID = (SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName) ORDER BY ID DESC LIMIT 1);     SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);     SELECT @workerId;          SET @id = (SELECT MIN(ID) FROM WORKDAY WHERE WORKERID = @workerId AND PROJECTID = @projectId AND STOPTIME IS NULL AND STARTTIME IS NOT NULL);     IF (@id >= 0)         THEN             UPDATE WORKDAY SET STOPTIME = now() WHERE ID = @id;     ELSE         INSERT INTO WORKDAY (WORKERID, PROJECTID, STARTTIME) VALUES (@workerId, @projectId, now());     END IF; END; // DELIMITER ; 

When I call the procedure once, I get the result:

mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80"); +-----------+ | @workerId | +-----------+ |         1 | +-----------+ 1 row in set (0.01 sec)  Query OK, 1 row affected (0.09 sec) 

From second run, is not working anymore:

mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80"); +-----------+ | @workerId | +-----------+ |      NULL | +-----------+ 1 row in set (0.00 sec)  ERROR 1048 (23000): Column 'WORKERID' cannot be null 

I did try to isolate and run the procedure with only this two lines of code:

    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);     SELECT @workerId; 

And it behaves just like above, but if I try to run SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = "045D91B22C5E80"; alone, works without problems. The @projectId variable behaves normal everytime I call the stored procedure.

Can someone help me to find a solution? Thank you!

My setup:

mihai:~$   mysql -V mysql  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu)) 
mysql> describe WORKER; +------------+-------------+------+-----+---------+----------------+ | Field      | Type        | Null | Key | Default | Extra          | +------------+-------------+------+-----+---------+----------------+ | ID         | int         | NO   | PRI | NULL    | auto_increment | | FIRSTNAME  | varchar(15) | NO   |     | NULL    |                | | LASTNAME   | varchar(20) | NO   |     | NULL    |                | | CARDNUMBER | varchar(14) | NO   |     | NULL    |                | | POSITIONID | int         | NO   | MUL | NULL    |                | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec) 

Java stored procedure or user-defined function could not call Java method

After I created a Java external stored procedure in DB2 as in my previous question, the call

call user1.send_mail(    P_TO          => 'me@domain.com'   ,P_CC          => NULL   ,P_BCC         => NULL   ,P_FROM        => 'me@domain.com'   ,P_SUBJECT     => 'db2 java subject'   ,P_TEXT_MSG    => 'db2 java test'   ,P_HTML_MSG    => null   ,P_ATTACH_NAME => null   ,P_ATTACH_MIME => null   ,P_ATTACH_BLOB => null   ,P_SMTP_HOST   => 'domain.com'   ,P_SMTP_PORT   => 465   ,P_USER_NAME   => 'me@domain.com'   ,P_PASSWORD    => 'secret'); 

fails it due to the following error:

Java stored procedure or user-defined function "USER1.SEND_MAIL", specific name "SQL201208113215436" could not call Java method "send_mail", signature "(Ljava/lang/String".. SQLCODE=-4306, SQLSTATE=42724, DRIVER=3.68.61

I made sure that all parameters that have no default values receive non-NULL values. What else is necessary for this to work?

How can I call a SQL Server stored procedure from Oracle and retrieve a scalar return value?

I’m developing a PL/SQL solution in Oracle Database Enterprise Edition 12.1.0.2. I’m calling stored procedures in Microsoft SQL Server 2014.

I am using DG4ODBC with the DBMS_HS_PASSTHROUGH package to call those stored procedures.

For stored procedures that return a tabular result set, I am able to use PARSE, FETCH_ROW, and GET_VALUE to retrieve the values. (My solution uses hard parsing, but it works for the volume we’re expecting. I’m open to tips if you know how I can use binds, but that’s not my question.)

DECLARE    sql_server_cursor_     BINARY_INTEGER;    sql_server_statement_  VARCHAR2(32767);    fetched_value_col1_    whatever_type_from_col1;    fetched_value_col2_    whatever_type_from_col2; BEGIN    sql_server_statement_ := 'EXEC "myDatabase"."dbo"."myProcedure" @p_one = N''' || sanitize__(my_argument_) || ''';';    sql_server_cursor_ := dbms_hs_passthrough.open_cursor@sql_server_link_;    dbms_hs_passthrough.parse@sql_server_link_(sql_server_cursor_, sql_server_statement_);    WHILE dbms_hs_passthrough.fetch_row@sql_server_link_(sql_server_cursor_) > 0    LOOP       dbms_hs_passthrough.get_value@sql_server_link_(sql_server_cursor_,  1, fetched_value_col1_);       dbms_hs_passthrough.get_value@sql_server_link_(sql_server_cursor_,  2, fetched_value_col2_);       dbms_output.put_line('fetched_value_col1_  ' || fetched_value_col1_);       dbms_output.put_line('fetched_value_col2_  ' || fetched_value_col2_);    END LOOP;    dbms_hs_passthrough.close_cursor@sql_server_link_(sql_server_cursor_); END; / 

I have a stored procedure in SQL Server that returns a scalar integer.

DECLARE @return_value int; EXEC @return_value = "myDatabase"."dbo"."myProcedure" @p_one = N'HelloWorld', @p_two = 42; SELECT @return_value; 

From Oracle, how would I call this procedure and retrieve that scalar value?

Stored procedure runs fine when executed manually but fails in scheduled job

I have a stored procedure that runs fine when executed manually but fails in scheduled job. The error I receive is: Executed as user: \USER-a. Login failed for user ‘\USER-a’. [SQLSTATE 28000] (Error 18456). The step failed.

My other stored procedures runs fine when scheduled. The only stored procedures that fails are the ones that are written in this form.

SET @QUERY = ‘SELECT * from abc’

EXEC(@QUERY)