. Student Registration for an Online Portal In this simple project, we will get the student details and insert them into the database. A unique student roll number (student_id) will be generated once the registration is complete. If the student is already registered, we will send a message saying the student already exists. create table student (id integer primary key autoincrement, student_name varchar2(50), dept varchar2(10), major_subject varchar2(10), student_id varchar2(50), email varchar2(50) not null, pwdhash binary(64) not null); Note that we are using binary (64) to store the password in an encrypted manner. This means we cannot directly insert the user's details but process it before inserting it into the table. Same way, to generate a unique student id, the university decides to combine the fields id and dept, which is dependent on the values that the student enters. To process all the values, we need to write a stored procedure! For passwords, we use the hashbytes function by specifying the encoding type and password entered by the user: hashbytes('SHA2_512', pwdhash) For the student_id field, we can use the concat function as student_id as concat(id, ‘_,’ dept) Here is the basic structure of a stored procedure that you can use for this project: CREATE PROCEDURE dbo.addStudent email VARCHAR2(50), pwdhash binary(64), student_name VARCHAR2(50), dept VARCHAR2(10), major_subject VARCHAR2(10), student_id VARCHAR2(50) as concat(id,'_',dept), responseMessage NVARCHAR(250) OUTPUT AS BEGIN SET NOCOUNT ON BEGIN TRY INSERT INTO student values(student_name, dept, major_subject, student_id, email, pwdhash); SET @responseMessage=' Success.' END TRY BEGIN CATCH SET @responseMessage=ERROR_MESSAGE() END CATCH END
. Student Registration for an Online Portal
In this simple project, we will get the student details and insert them into the
create table student (id integer primary key autoincrement, student_name varchar2(50), dept varchar2(10), major_subject varchar2(10), student_id varchar2(50), email varchar2(50) not null, pwdhash binary(64) not null);
Note that we are using binary (64) to store the password in an encrypted manner. This means we cannot directly insert the user's details but process it before inserting it into the table. Same way, to generate a unique student id, the university decides to combine the fields id and dept, which is dependent on the values that the student enters. To process all the values, we need to write a stored procedure!
For passwords, we use the hashbytes function by specifying the encoding type and password entered by the user: hashbytes('SHA2_512', pwdhash)
For the student_id field, we can use the concat function as student_id as concat(id, ‘_,’ dept)
Here is the basic structure of a stored procedure that you can use for this project:
CREATE PROCEDURE dbo.addStudent
email VARCHAR2(50),
pwdhash binary(64),
student_name VARCHAR2(50),
dept VARCHAR2(10),
major_subject VARCHAR2(10),
student_id VARCHAR2(50) as concat(id,'_',dept),
responseMessage NVARCHAR(250) OUTPUT
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
INSERT INTO student values(student_name, dept, major_subject, student_id, email, pwdhash);
SET @responseMessage=' Success.'
END TRY
BEGIN CATCH
SET @responseMessage=ERROR_MESSAGE()
END CATCH
END
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
