PLSQL Procedure in Erlang

By -
Functions/Procedures from Erlang:

Prepare your DB:

drop table employee;

CREATE TABLE EMPLOYEE
( "NR" NUMBER,
"FIRSTNAME" VARCHAR2(20),
"LASTNAME" VARCHAR2(20),
"GENDER" CHAR(1),
"EXPERIENCE" NUMBER
) ;

ALTER TABLE EMPLOYEE ADD PRIMARY KEY ("NR");
DROP SEQUENCE EMPLOYEE_SEQ;
CREATE SEQUENCE EMPLOYEE_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20;

create or replace TRIGGER EMPLOYEE_SEQ_TRG
BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
IF INSERTING AND :NEW.NR IS NULL THEN
SELECT EMPLOYEE_SEQ.NEXTVAL INTO :NEW.NR FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;


insert into EMPLOYEE(FIRSTNAME, LASTNAME, GENDER, EXPERIENCE) values ('Richa','Vaidya','F',4);

select * from EMPLOYEE;
and check the output.

Lets create a procedure for inserting:

create or replace procedure insert_employee (V_FIRSTNAME IN EMPLOYEE.FIRSTNAME%TYPE,
V_LASTNAME IN EMPLOYEE.LASTNAME%TYPE, V_GENDER IN EMPLOYEE.GENDER%TYPE ,
V_EXPERIENCE IN EMPLOYEE.EXPERIENCE%TYPE, V_NR OUT EMPLOYEE.NR%TYPE) as
begin
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, GENDER, EXPERIENCE) VALUES
(V_FIRSTNAME, V_LASTNAME, V_GENDER, V_EXPERIENCE) RETURNING NR INTO V_NR;
end;

To get the output and confirm that your procedure is working fine:

exec INSERT_EMPLOYEE('Foo', 'Bar', 'M', 5, :V_NR);

print :V_NR;

Your Employee table looks like this now:

Erl program: (Here I had problems while saving numbers in db from erl, so i formatted the callable)

Save the file as dbInsert.erl:


-module(dbInsert).

-export([save_to_db/4]).


save_to_db(FirstName, LastName, Gender, Experience) ->
odbc:start(),
{ok, Ref}=odbc:connect("DSN=****;UID=****;PWD=****", [{scrollable_cursors, off},{time_out, infinity}]),
Formated_Sql=io_lib:format("{call INSERT_EMPLOYEE(?,?,?,~p,?)}", [Experience]),
SQL=binary_to_list(binary:list_to_bin(Formated_Sql)),
{executed,1,[ID]}=odbc:param_query(Ref, SQL ,[{{sql_varchar, 20}, in, [FirstName]}, {{sql_varchar, 20}, in, [LastName]}, {{sql_varchar, 1}, in, [Gender]}, {sql_integer, out, [1]}], infinity),
odbc:disconnect(Ref),
io:fwrite("Generated ID ~w~n", [ID]),
odbc:disconnect(Ref),
ok.



Ouput:
Eshell V7.0 (abort with ^G)
1> c(dbInsert).
{ok,dbInsert}
2> dbInsert:save_to_db("A","B","M",10).
Generated ID {5}

ok


And your DB looks like:

Comments

Post a Comment

Popular posts from this blog

Writing your own ejabberd Module

By -
Coming soon: What is ejabberd? What is erlang? Installation details. I will start with writing the ejabberd module first. Once you have done all the installations: sudo make sudo make install sudo ejabberdctl start sudo ejabberdctl stop You can check the status using: sudo ejabberdctl status You can locate logs at: (With default installation) /var/log/ejabberd All config related changes will be mainly done at: /etc/ejabberd/ejabberd.yml You will need a basic understanding of erlang. You can take a short crash course at: http://www.erlang.org/course/course.html This is a good reference. Go to your ejabberd-15.07/src directory. This is where you will write the hook for your ejabberd server. Create a file, say mod_changeBody.erl. Your module name and file name should be same. Add the following basic contents into your erl file: -module(mod_changeBody). -behavior(gen_mod). -include("ejabberd.hrl"). -include("logger.hrl"). -expo...
Read more

npm ECONNREFUSED error

By -
If you face error which says ECONNREFUSED while doing npm install <<package name>>, you are probably behind a proxy which is not allowing you to connect to the server and download the packages required. Just follow the below steps: npm config set proxy  http://your_proxy_name:port npm config set https-proxy https://your_proxy_name:port And then try npm install How to get npm config? npm get proxy will do the work :)
Read more

Conditional Flow - Spring Batch Part 6

By -
Jump to Conditional Flow using Custom Status There can be muliple scenario where you want your jobs to proceed depending on the outcome of your steps. For example, in case of our Corporate gifts shop, in case the automatic packaging of the gifts fails, we want some manual intervention to be done i.e. somebody should manually package the item.  IF-ELSE IF loop for packaging:     if packageStep fails                              then manualPackageStep                         else if packageStep executes successfully                              then sendOutForDelivery Entire code available at git repo:   https://github.com/ricsr/spring-batch-demo/tree/exercise_06 Using SpringBatch you can achieve it as follows: Create you manualPacka...
Read more