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";

SQL DB corruption recovery procedure

I’ve like to have this discussion on db corruption topic. We run weekly dbcc checkdb and most time, this sql job went through without errors. However, if it does, that usually means big headache for any dbas.

The recommended way is to check if it can be fixed without data loss or restore from last good copy.

My question is about the procedure to restore from last good copy.

Our backup strategy is weekly full back on sunday, diff backup daily and tlog backup hourly.

Say if weekly integrity check indicated errors,

How to determine the last good backup once last good backup is determined, say corruption on wed, should I use last week full backup + diff backup on tuesday + all tlogs after tuesday’s diff backup till current time to restore db? question should I use replace option ?

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) 

Procedure to truncate the desired table after a specific time does not work properly,”SYSDATE” and the “SYSTEM CLOCK” show different hours!

I wrote a procedure as you can see below:

create or replace procedure truncate_test is         hour_part char(40);     begin    <*** Other Parts***>                         select to_char(sysdate, 'HH24') into hour_part from dual; -- Check the time here      if (hour_part >= 16) then     execute immediate 'truncate table cust_modern_channel_branch';   end if;     <*** Other Parts***>  end; 

This procedure is a part of an ETL which runs everyday.The aim of this part of the procedure is to check the time and if it’s after 4:00 PM in the afternoon, it truncates the table (ETL might reach the truncate part at different hours). I’m using this query to extract the hour part of sysdate select to_char(sysdate, 'HH24'). The problem is that this query select sysdate from dual returns this 3:28:43 PM (I’ve omitted the date part) so the above query returns 15, but the system clock (bottom right corner) shows 4:08 PM !!So I’m expecting that the procedure truncates my table when I execute it cause it’s 4:08 PM but sysdate is showing a different time and nothing happens! why is this happening? Why the sysdate and system clock shows different hours?

Thanks in advance

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?

No authorized routine named “SQLJ.INSTALL_JAR” of type “PROCEDURE” having compatible arguments was found

My goal is to deploy a Java routine to a DB2 server. After reading Deploying Java routine class files to DB2 database servers on p.267-268 of IBM DB2 10.5 for Linux, UNIX, and Windows Developing User-defined Routines (SQL and External) I issued the following call in IBM Data Studio:

call sqlj.install_jar('file:/C:/Projects/javadb2.jar', 'javadb2'); 

where C:/Projects/javadb2.jar is the path on the DB2 server machine to the JAR to be deployed to its local filesystem. It throws the following error:

No authorized routine named "SQLJ.INSTALL_JAR" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.18.60

I tried under the regular user and under db2admin to no avail. The only question that I found for this error on SE was where they added a 3d parameter, so this is not my case. Anyhow, with or without the 3d parameter of 0, it still fails with the same error message.

Rewrite existing procedure without merge as Oracle 12.1 doesn’t support it

I have a procedure where data is pushed into table using merge logic used as part of it. The procedure takes string passed at runtime and splits it into rows.

I need to change the merge part using any other method other than merge, maybe cursor for loop as Oracle 12.1 does not support merge and similar take input string and insert/update accordingly in the destination table. inside procedure and we cannot get it upgraded due to constraints.

So everything in proc would be same just that it would be without merge.

The details are inside the fiddle with table and sample data.

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=34a88972aa53cf60b1199f9f277285f4

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)