Basic Walkthrough SQLServer Part-5

 

Basic Walkthrough SQLServer Part-5

In this part we will study some more useful database objects. For example VIEWS, TRIGGERS, PROCEDURES and few other.

As we had an introduction to trigger creation in part 2, we will discuss how we run scripts first. SQLSERVER supports different kind of scripts. You can run system shell scripts (.bat, .cmd, .js, .vbs,.sh and many more).  Let us try to understand how we can run scripts. Simple SQL script looks like

 

C:\Documents and Settings\user1>sqlcmd –i script.sql

use sample; Select Fname,Lname,Phone,Cell from address; select count(*) from address;

This was a simple SQL script. This is to be run in sqlcmd command window. The syntax has been indicated in blue. Being an example script, I just listed few commands. In actual scripts these lines are usually over hundreds.

C:\Documents and Settings\user01>sqlcmd –i script.sql -o script.out.txt

            Similarly, we can use system scripts too for accomplishing same mission. Have a look at following script. This one is equivalent to the previous one.

C:\Documents and Settings\user01>Script.bat

sqlcmd -d sample -Q “Select Fname,Lname,Phone,Cell from address” sqlcmd -d sample -Q “select count(*) from address”

This is very straightforward that we use sqlcmd keyword and removed the terminator. The scripts are usually written for various tasks including generating daily reports. As a rule of thumb, add all the commands to script file that are to be repeated in future and invoke the script using one of above syntaxes.

Let us create another trigger on TRANS table (created in part 2) to ensure that user uses in or out keywords only in code column. Using anything else will be waste of data. So, we will try to reject entry if the code is not set properly.

C:\Documents and Settings\user01>sqlcmd –td@ -f TriggerCheckCode.sql

CREATE TRIGGER Check_Code ON TRANS  FOR INSERT  AS       declare @code CHAR(4);       set @code = (select code from inserted);        IF (upper(@code)<>'IN' AND upper(@code)<>'OUT')            RAISERROR('Code must be either "in" or "out"',20,10);

This trigger will stop us from entering wrong ‘Code’ in TRANS table. This is to ensure that data entered will be usable by the trigger update_total trigger to update TOTAL tables.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from total”

WEEKNUM INCOME   EXPENSE  TIMEUPDATE ------- -------- -------- -----------------------      49   224.20    49.40 2018-12-04 22:25:21.000 (1 rows affected)

C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into TRANS values (120.00,'Not IN')”

Msg 2754, Level 16, State 1, Server XPSQL1\SQLEXPRESS, Procedure Check_Code, Line 9 Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.The statement has been terminated.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into TRANS values (default, 120.00,'In')”

(1 rows affected)

C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from total”

WEEKNUM INCOME   EXPENSE  TIMEUPDATE ------- -------- -------- -----------------------      49   344.20    49.40 2018-12-04 23:43:29.500 (1 rows affected)

            Cool, the transaction was applied to TOTAL table when we entered the correct code. Remember we had used ‘case free’ iN and OuT key words. It will work with any case.

Let us try to have some flavor of VIEWS. As explained earlier the VIEW is combination of columns from different tables. We will create another table called PERSONS with names of all family members. Then we will create a view joining TRANS and PERSONS tables to save info about who had carried out the transaction. For this purpose, we will add a column PID to TRANS table.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “alter table TRANS add PID CHAR(2)”

Now create table PERSONS as below.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “create table PERSONS (PID CHAR(2) not null primary key, FNAME CHAR(16), LNAME CHAR(12))”

Add some data to the table.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into PERSONS values (‘00’,’David’,’Boon’)”   C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into PERSONS values (‘01’,’Marry’,’Boon’)”   C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into PERSONS values (‘02’,’John’,’Boon’)”   C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from persons”   PID FNAME            LNAME --- ---------------- ------------ 00  David            Boon 01  Marry            Boon 02  John             Boon (3 rows affected)

Now we need to enter these PID in TRANS table to indicate who carried the transactions out. Let’s do that.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from TRANS”   ID     AMOUNT   CODE   PID ------ -------- ------ ---      0    34.40 out    -      1   123.45 in     -      2   100.00 in     -      3     0.75 in     -      4    15.00 out    -      6   120.00 In     -     6 record(s) selected.     C:\Documents and Settings\user01>sqlcmd -d sample -Q “update TRANS set PID='00' where ID=0” SQLSERVER0000I  The SQL command completed successfully.   C:\Documents and Settings\user01>sqlcmd -d sample -Q “update TRANS set PID='01' where ID=1” SQLSERVER0000I  The SQL command completed successfully.   C:\Documents and Settings\user01>sqlcmd -d sample -Q “update TRANS set PID='02' where ID=2” SQLSERVER0000I  The SQL command completed successfully.   C:\Documents and Settings\user01>sqlcmd -d sample -Q “update TRANS set PID='03' where ID=3” SQLSERVER0000I  The SQL command completed successfully.   C:\Documents and Settings\user01>sqlcmd -d sample -Q “update TRANS set PID='04' where ID=4” SQLSERVER0000I  The SQL command completed successfully.   C:\Documents and Settings\user01>sqlcmd -d sample -Q “update TRANS set PID='00' where ID=6” SQLSERVER0000I  The SQL command completed successfully.   C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from TRANS”   ID     AMOUNT   CODE   PID ------ -------- ------ ---      0    34.40 out    00      1   123.45 in     01      2   100.00 in     00      3     0.75 in     02      4    15.00 out    02      6   120.00 In     00     (6 rows affected)     C:\Documents and Settings\user01>

                        Now we can create a view name trans_view to reflect name of the person instead of PID. PID is usually handy for use with managing multiple tables within database. Views are very handy and useful for creating end-user reports.     

C:\Documents and Settings\user01>sqlcmd -d sample -Q “create view trans_view as SELECT t.AMOUNT,t.CODE,p.FNAME,p.LNAME from TRANS t, PERSONS p where p.PID=t.PID”   C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from trans_view”   AMOUNT   CODE   FNAME            LNAME -------- ------ ---------------- ------------    13.45 IN     Marry            Boon   123.45 in     David            Boon    34.40 out    John             Boon   123.45 in     John             Boon   100.00 in     David            Boon (5 rows affected)

 

            So, we had an idea how Views work. In actual practice, the views are rarely so simple. But the logic behind a view is always same. JOIN tables and pretend to be one table. Please note that for simplicity of examples, we did not use TIMESTAMP column in TRANS table, which is mostly used to keep track of transaction along with some other more useful info.

            For simplest use of database system, we have already covered most objects. In actual usage I hardly need anything other than what we have covered so far. The other objects are for some more sophisticated actions. For example, PROCEDURES and FUNCTIONS are very useful but for the scope of this tutorial these will be covered in last parts.

            Let’s discuss some of useful built-in functions.

CHAR
The CHAR function returns a fixed-length character string representation of the argument.

ASCII

Returns the ASCII value for the specific character

CONCAT

Adds two or more strings together

HEX

The HEX function returns a hexadecimal representation of a value.

UPPER

Converts a string to upper-case

LOWER
The LOWER function returns a string in which all the characters have been converted to lowercase characters.

LEFT
The LEFT function returns a string that consists of the specified number of leftmost bytes of the specified string units.

LENGTH
The LENGTH function returns the length of a value.

SUBSTRING

Extracts some characters from a string

LOCATE
The LOCATE function returns the position at which the first occurrence of an argument starts within another argument.

RTRIM

Removes trailing spaces from a string

LTRIM
The LTRIM function removes blanks or hexadecimal zeros from the beginning of a string expression.

MAX
The MAX scalar function returns the maximum value in a set of values.

MIN
The MIN scalar function returns the minimum value in a set of values.

RIGHT
The RIGHT function returns a string that consists of the specified number of rightmost bytes or specified string unit from a string.

RTRIM
The RTRIM function removes blanks or hexadecimal zeros from the end of a string expression.

USER_NAME

Returns the database user name based on the specified id

CAST

Converts a value (of any type) into a specified datatype

TIME
The TIME function returns a time derived from a value.

TIMESTAMP
The TIMESTAMP function returns a timestamp derived from its argument or arguments.

Following are some connection dependant REGISTER VARIABLES.

Detailed searchable information is always available online from Microsoft. The manuals are another source of good info if we have these handy for the time when we need.   

Link to online documentation https://docs.microsoft.com/en-us/sql/t-sql/functions/functions

Basic Walkthrough SQLServer Part-4

 

Basic Walkthrough SQLServer Part-4

In previous part we had created the database (single table database) and populated the table with some data. The data seemed all right and we had nice time retrieving the data. Except one that (friend of friend)’s last name begins with small ‘w’. We will correct this and move on to creating triggers and other stuff.

Let us try updating the last name with capital ‘W’. We will see contents and update the row based on some unique parameters. Which means that while updating if our predicate selects more than one row, all selected rows will be updated. So be careful while identifying the target row.

1> Update ADDRESS SET LNAME='Walker' Where PHONE='419-932-9322' 2> go (1 rows affected)

Thank God. The correction was cool.

To have a look at the modified data issue the command: SELECT * FROM ADDRESS. The * basically means all columns.

1> select * from address 2> go FNAME            MNAME        LNAME            PHONE           CELL            ADDR ---------------- ------------ ---------------- --------------- --------------- ----------------------------------- David            M.           Brown            905-264-2644    647-746-7466    1216 Morning Star Drive Miss,ON,CA Dilshaad         Sufi         Akhtar           905-932-9322    416-417-4177    3456 Horner Ave Etobicoke,ON,CA John             D            Walker           419-932-9322    647-417-4879    promised help with JOB Sabar                         Raikoti          905-509-5099    419-914-9144    1032 Mavis Rd Mississauga,ON,CA Tai                           Tang             647-647-6477    416-614-6144    64 Pluto Way Brampton,ON,CA Tai                           Tng              647-647-6477    416-614-6144    64 Pluto Way Brampton,ON,CA (6 rows affected)

When you look at above output you will find last name corrected. Secondly you see word ‘promised’ in ADDR column. That means you are free to insert any text in ADDR column. There is no CONSTRAINT set up. Usually there are no CONSTRAINTS in text fields like address.

There was an extra entry for Tai, with wrong last name (Tng). We did that intentionally to test behavior of primary key. Let us delete it.

1> Delete from ADDRESS where lname='Tng' 2> go   (1 rows affected) 1> Select Fname,Lname,Phone,Cell from address 2> go Fname            Lname            Phone           Cell ---------------- ---------------- --------------- --------------- David            Brown            905-264-2644    647-746-7466 Dilshaad         Akhtar           905-932-9322    416-417-4177 John             Walker           419-932-9322    647-417-4879 Sabar            Raikoti          905-509-5099    419-914-9144 Tai              Tang             647-647-6477    416-614-6144 (5 rows affected)

So, we have seen how to remove ROWS from a table. A word of caution. Do not use DELETE FROM ADDRESS. This will delete all rows. This command should always have where predicate to limit the rows being deleted.

The most liked part of database is input format routines. The user will enter the data and smart systems accept a wide variety of data in loose format and will try its best to format it strict before inserting into table. This seems that this is part of programming. Well, yes, but basic programming that can be handled in small triggers. So I will help you write trigger to adjust hyphens (‘-‘) in the input data when it is fed to PHONE and CELL columns. Please go over SQL Server information online for triggers’ definition and syntax for more detail.

Next step will be to create a trigger to validate the data being input into the database table.

Create a text file with any extension having following SQL code in it. Do not worry about everything for now. Just create the trigger definition file and get it created. This trigger will check for existence of hyphens in appropriate position in phone numbers.

CREATE TRIGGER INS_CELL_PHONE ON ADDRESS  FOR INSERT  AS  declare @phone CHAR(15),@cell CHAR(15);  set @phone = (select phone from inserted);  set @cell = (select cell from inserted);    IF (SUBSTRING(@phone,4,1)<>'-' OR SUBSTRING(@phone,8,1)<>'-'         OR SUBSTRING(@cell,4,1)<>'-' OR SUBSTRING(@cell,8,1)<>'-' )            RAISERROR ('Phone Numbers Expected as 999-999-999',201,10); go

This will create a trigger which will look far hyphens (‘-‘) at specified locations.  If any of hyphens are missing, trigger will raise an error with message as specified as MESSAGE_TEXT.

            To create the trigger, I suppose you have stored the file as INS_PHONE.TRIG in current folder. See the syntax and note that we had ended the file with @. So calling this SQL file we specify the terminator (@) as below.

C:\Documents and Settings\user1>sqlcmd -d sample -i ins_phone.trig   C:\Documents and Settings\user1>

            Please note that to run SQL files you have to run it from Command Line and not sqlcmd. These two windows are different although they look the same. If you have started sqlcmd then you may revert back to Command Line by typing QUIT. So now trigger has been created which checks for ‘-‘  at 4^th and 8^th position in PHONE and CELL. If there is any one missing, trigger will force rejection of whole row and nothing will be added.

 

1> insert into address values ('Gurmit','S','Randhawa','416-742-9242','6477204020','1230 The Walkers Road') 2> go Msg 50000, Level 20, State 10, Server XPSQL1\SQLEXPRESS, Procedure INS_CELL_PHONE, Line 11 Phone Numbers Expected as 999-999-999 Msg 2745, Level 16, State 2, Server XPSQL1\SQLEXPRESS, Procedure INS_CELL_PHONE, Line 11 Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.

 

Let us try little different. This time we won’t provide the CELL number.

 

1> insert into address values ( 'Gurmit', 'S', 'Randhawa', '416-742-9242','','1230 The Walkers Road') 2> go Msg 50000, Level 20, State 10, Server XPSQL1\SQLEXPRESS, Procedure INS_CELL_PHONE, Line 11 Phone Numbers Expected as 999-999-999 Msg 2745, Level 16, State 2, Server XPSQL1\SQLEXPRESS, Procedure INS_CELL_PHONE, Line 11Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.

Same, because trigger wants two hyphens in both numbers. So now you are forced tom enter the phone numbers exactly as 999-999-9999. By the way, we have designed the trigger just for practice, so we kept it simple; and you can provide any bad data except ‘-‘ on 4^th and 8^th places in phone numbers.

Now try providing the data in strict format.

1> insert into address values ('Gurmit','S','Randhawa','416-742-9242','647-720-4020','1230 The Walkers Road') 2> go (1 rows affected)

Cool, This works, and is much better because the chances of omission are ruled out this way.

            Triggers are not primarily for this purpose. However being an database object it works faster and ensures data integrity; these triggers can be put to different uses. Normally triggers are used to make calculations based on some columns and populate the other columns with the result. More sophisticated use can be thought of updating other tables based on transaction data entered into main table (usually called TRANSACTION table).

            As a last section of this walkthrough part I would like to mention here that entering all data on command line is usually not welcome. But the good thing is that if you do it manually you get more familiar with your database objects; i.e. tables, views, triggers and sequences etc.

            Still we do not have to go long way. If you so wish, you can use the batch file listed below. This one will allow you to enter data into your table. I will keep it simple for this tutorial.

@ echo off :another set /p fname=First Name : if [%fname%]==[] goto done set /p mname=Middle Name : set /p lname=Last Name : set /p phone=Phone Number : set /p cell=Cell Number : set /p addr=Address : set /p ok=      Ok ? if [%ok%]==[y] goto doit if [%ok%]==[Y] goto doit if [%ok%]==[Yes] goto doit if [%ok%]==[yes] goto doit if [%ok%]==[YES] goto doit goto redo :doit Sqlcmd -d sample -Q "insert into address values ('%fname%','%mname%','%lname%','%phone%','%cell%','%addr%')" echo. :redo set fname= set mname= set lname= set phone= set cell= set addr= set ok= goto another :done set fname= set mname= set lname= set phone= set cell= set addr= set ok=

Please take care saving this script. This will need .bat or .cmd filename extension. Go ahead and practice with data entry. Remember when you want to finish, enter nothing in First Name :. If you miss it then say no to ‘Ok ?’ prompt and then give it empty First Name again. This batch file will keep looping until you provide empty (NULL) for First Name. For each entry you will be expecting following string from SQLServer. SQLServer confirms the successful entry this way. Anything else will signal an error.

(1 rows affected)

            If any constraint is violated, something like below message will be returned. This gives clear idea what may have gone wrong.

Msg 2627, Level 14, State 1, Server XPSQL1\SQLEXPRESS, Line 1 Violation of PRIMARY KEY constraint 'My_FNAME_LNAME_PK'. Cannot insert duplicate key in object 'dbo.ADDRESS'. The statement has been terminated.

If you see an error then you need to repeat the entry. Keep in mind that you have a trigger in action to check your phone numbers. That should be good enough for this part of tutorial. In next part, we will see how we can build a simple application to retrieve data from our database remotely.