Tuesday, December 28, 2010

Concept of Store Procedure

What is Store Procedure

Stored procedures are precompiled database queries (set of SQL statements) that improve the security, efficiency and usability of database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to application developers for use in other environments, such as web applications. All of the major database platforms, including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology are the substantial performance gains from precompiled execution, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.

Benefits of Stored Procedures

• Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
• Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
• Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

Stored procedures are very similar to user-defined functions, but there are subtle differences. For more information, read Comparing Stored Procedures and User-Defined Functions.

Structure

Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you'll find that stored procedures are actually quite simple.

Example: SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

Our Florida warehouse manager can then access inventory levels by issuing the command

EXECUTE sp_GetInventory 'FL'

The New York warehouse manager can use the same stored procedure to access that area's inventory.

EXECUTE sp_GetInventory 'NY'

How to execute Store Procedure

Exec Storeprocedurename @parm1,@parm2,@parm3

Points to keep in mind when Store Procedure

Some of the points mentioned in this article is getting through the following site. I added them for my future reference of getting all information at one point.

http://www.devx.com/getHelpOn/10MinuteSolution/20057/1954

Spell out your Procedure’s Purpose

A stored procedure works best when doing only one thing. However, some shops are under pressure to localize the logic underlying data modifications. This centralizes code maintenance by putting the logic in one procedure instead of three or four (e.g., procedures for insert, update, and delete). Unfortunately, combining these actions makes the underlying procedure exponentially more complicated. Instead of doing only one thing, the procedure is pressed into general service, doing three or four things. Your first line of defense in debugging this type of general-purpose stored procedure is a good header that enables you to document its purpose(s). Once the header is set up and maintained, you have a running history of what is happening in the code.

Header contains details like Author, Date, Last Rev, Revision History, Purpose, Creates, uses, parameters in, parameters out , Relations, Good Joins, Parking Lot

Determine the Flow of Control

By branching based on the parameters sent to the procedure, you delegate control to the relevant sections to do the DML required. Assume you have a procedure that has one parameter: @Action, which could be an Insert, Update, or Delete parameter. To test the value of @Action and perform accordingly, you need a structure something like this:

IF @Action = XXXXXX
BEGIN
Your code here
END

You should get this fleshed out by pseudo-coding your intention first and then creating the logic to accomplish what you intend. Since most errors are logic errors, getting this down in stub form will assure that the code is at least executing the flow of control correctly.

Basic things needs to check while Testing Store Procedure

  • Naming convention of Store procedure
  • Whether SP is part of DB or not
  • Name of parameter, No of parameters, type of parameters
  • Check for the Outputs
    • When output is zero (zero row affected)
    • When some records are extracted
    • Output contains many records
    • What a stored procedure is supposed to do and not supposed to do
    • Write a simple queries to see if a stored procedure populates right data
  • Check for the input parameters
  • Check for the valid data for each parameter
  • Check for the invalid data for each parameter
  • Check for the boundary value of data
  • Check for all the required parameters
  • Check parameter for data type
  • Check for the Return values
  • Check if SP return any values
  • Check if failure occurs then it should return nonzero
  • Check for the Error Messages
    • Make stored procedure fail and cause every error message to occur at least once
    • Find out any exception that doesn’t have a predefined error message
  • Check for other values
  • Whether a stored procedure grants correct access privilege to a group/user
  • See if a stored procedure hits any trigger error, index error, and rule error
  • Look into a procedure code and make sure major branches are test covered.
  • Integration check for the procedure
  • Group related stored procedures together. Call them in particular order
  • If there are many sequences to call a group of procedures, find out equivalent classes and run tests to cover every class.
  • Make invalid calling sequence and run a group of stored procedures.
  • Design several test sequences in which end users are likely to do business and do stress tests

Let the System Do the Work with everything

By creating a parameter mill, you can feed a stored procedure a collection of parameters that represent the universe of things it can accept, enabling you to test the scope of conditions anticipated by your code. You can automate the boundary conditions with a table and procedure to do the drudgery for you.

CREATE PROCEDURE dbo.usp_TestFacilityRoster
@InAction VARCHAR(15),
@EntryMonth INT,
@EntryYear I
@FileType CHAR(1)
AS
IF @InAction ='NEWREINS' or @InAction = 'NEWREINSSNP'
BEGIN
--do stuff for new members
END
IF @InAction = 'DISENROL' OR @InAction = 'EXISTING'
BEGIN
--do common stuff for existing or disenrolled members
IF @InAction = 'DISENROL'
BEGIN
--specific for disenroll
ELSE
IF @InAction = 'EXISTING'
BEGIN
--specific for existing
END

Create a test plan table with the following code

create table p1 (param1 char(8))
create table p2 (param2 int)
create table p3 (param3 int)
create table p4 (param4 char(1))
create table testplan (param1 char(8), param2 int, param3 int, param4 char(1))
insert p1 values('NEWREINS')
insert p1 values('DISENROL')
insert p1 values('EXISTING')
insert p2 values(1)
insert p2 values(2)
insert p2 values(3)
insert p2 values(4)
insert p2 values(5)
insert p2 values(6)
insert p2 values(7)
insert p2 values(8)
insert p2 values(9)
insert p2 values(10)
insert p2 values(11)
insert p2 values(12)
insert p3 values(1999)
insert p3 values(2000)
insert p3 values(2001)
insert p3 values(2002)
insert p3 values(2003)
insert p3 values(2004)
insert p4 values('A')
insert p4 values('S')
insert p4 values('P')
insert testplan select * from p1,p2,p3,p4

This will create a Cartesian Product (cross join) of all the combinations of the parameters your procedure can accept.

During development, you try to mimic the call to your procedure by writing something like this near the top of the code:

/* TEST BLOCK
set @InAction ='NEWREINS'
set @entrymonth = 7
set @entryyear = 2003
*/

Instead, you can create a string to execute the stored procedure that leaves placeholders for your procedure's parameters:

select 'Execute dbo.usp_TestFacilityRoster('+
''''+
param1 + ''''+','
+cast(param2 as char(2))
+','+
+cast(param3 as char(4))
+','
+''''+param4+''''
+')'
from testplan

This code doesn't execute it though. You need to use dynamic SQL to execute the string. So the above code can be wrapped in a cursor like this:

set nocount on
declare @param1 varchar(25)
,@param2 varchar(2)
,@param3 varchar(4)
,@param4 varchar(25)
declare C1 Cursor for
select param1, param2, param3, param4
from testplan (nolock)
open C1
fetch next from C1
into @param1, @param2, @param3, @param4
while @@fetch_status = 0
begin
exec dbo.usp_TestFacilityRoster, @param1, @param2, @param3,
@param4
fetch next from C1
into @param1, @param2, @param3, @param4
end
close C1
deallocate C1

-- this statement builds the string from the table
Update Testplan
Set @sqlstring2 = @sqlstring2 + cast(param1 as char(8)) + ', '
+cast(param2 as char(2)) + ', ' + cast(param3 as char(4))+ ', '
+cast(param4 as char(1))
print @sqlstring2
set @sqlstring = @sqlstring + @sqlstring2
execute sp_executesql @sqlstring

You also can use this approach from the command line and imitate the testing procedure outlined above by using the FOR IN DO construct in a batch file, or in better flow of control language from WSH so that you can make repeated calls to your procedure with the different parameter sets. However, the approach given here lets you work from within Query Analyzer without having to resort to the command window or another language to get the job done.

These ideas were necessary for me to circumvent the absence of permissions to run the SQL Debugger or to install my own error messages so that I could use 'raise error' in my assignment. This Solution has provided a few ideas for getting around similar limitations in your own environment, and they lets you test your code automatically to have a higher degree of confidence in the correctness of your own solutions

Thursday, December 9, 2010

Concept of JOIN

Concept of JOIN:

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

INNER JOIN

This join returns rows when there is at least one match in both the tables.
Example:
SELECT * FROM Table1 T1 INNER JOIN Table2 T2 ON T1.col1=T2.col1

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
Example:

SELECT * FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.col1=T2.col1

RIGHT OUTER JOIN

This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
Example:
SELECT * FROM Table1 T1 RIGHT OUTER JOIN Table2 T2 ON T1.col1=T2.col1

FULL OUTER JOIN

This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.
Example:
SELECT * FROM Table1 T1 FULL OUTER JOIN Table2 T2 ON T1.col1=T2.col1

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Below is good reference link for concept of JOIN.
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

Monday, November 22, 2010

Credit Card Testing

Credit Card Testing



Credit card related testing is vast field and sometimes it requires more information on it. I have try to write some tips for testing Credit card Application. There may be chances for more scenarios, but I just tried my best.


  • First of all check with your payment gateway that how many types of cards they support, like Master card, Visa card, American Express, Discover etc.
  • Then each and every card has specific number of digits as card number. For example say, Discover should have 17 digits only.
  • Also each card will have an expiry date.
  • Test the boundary conditions for credit card by giving more than 17 digits and less than 17 digits.
  • Invalid credit card number.
  • Invalid Expiry date etc
  • Network response
  • Buffer page between application page and payment gateway page
  • URL information with card and amount details
Dummy Credit Card Numbers for Testing


Here is a list of dummy credit card number that can be used while testing your applications involving credit card transactions:


  • Visa: 4111-1111-1111-1111
  • MasterCard: 5431-1111-1111-1111
  • Amex: 341-1111-1111-1111
  • Discover: 6011-6011-6011-6611
Credit Card Prefix Numbers:


  • Visa: 13 or 16 numbers starting with 4
  • MasterCard: 16 numbers starting with 5
  • Discover: 16 numbers starting with 6011
  • AMEX: 15 numbers starting with 34 or 37
While testing, use only the credit card numbers listed here. Other numbers produce an error.Expiration Date must be a valid date in the future (use the mmyy format).


Test Credit Card Account Numbers


  • Credit Card Type Credit Card Number
  • American Express 378282246310005
  • American Express 371449635398431
  • American Express Corporate 378734493671000
  • Australian BankCard 5610591081018250
  • Diners Club 30569309025904
  • Diners Club 38520000023237
  • Discover 6011111111111117
  • Discover 6011000990139424
  • JCB 3530111333300000
  • JCB 3566002020360505
  • MasterCard 5555555555554444
  • MasterCard 5105105105105100
  • Visa 4111111111111111
  • Visa 4012888888881881
  • Visa 4222222222222
Note : Even though this number has a different character count than the other test numbers, it is the correct and functional number.


  • Processor-specific Cards
  • Dankort (PBS) 76009244561
  • Dankort (PBS) 5019717010103742
  • Switch/Solo (Paymentech) 6331101999990016 

Wednesday, September 29, 2010

How to Test Payment Gateway Functionality?

How to Test Payment Gateway Functionality?

To test payment gateway functionality is same as test any other functionality. You should have some test strategy during testing it. Following are some points keep in mind during testing of payment gateway.

  1. Gather appropriate test data for the dummy credit card numbers for various master cards.
  2. Gather payment gateway information like whether used paypal, guestpay etc.
  3. Gather payment gateway documents with error codes – useful it if any error came during testing to identify whether its our application fault or payment gateway related error.
  4. Does the gateway do what it is supposed to do? Does it handle order objects correctly? Does it perform additional calculations correctly?
  5. Understanding of the integration of the payment gateway with application
  6. Understand and test the parameters and sessions passed through payment gateway and application
  7. Understand and test the amount related information passed through query string or session or variables in any form.
  8. Check the format of the amount with currency format
  9. Check the language of the application and payment gateway language
  10. Try to change payment gateway language during the payment process
  11. Test after successful payment all the necessary data retrieved to our application or not
  12. Check what happens if payment gateway goes down during the payment process
  13. Check what happens if payment process went successful but do not return to our application
  14. Check what happens if session goes time out during the payment process
  15. Check what happens in backend during the payment process is the session data stored in temporary table or any id is generated or not?
  16. Check what happens if payment process is fail
  17. Check if any modification transaction is going on through payment gateway, then how much amount is taken out as modified amount whether required to pay more or not. For example- if modified amount is greater than paid amount then and then only application redirect to payment gateway otherwise it should not.
  18. Verify DB entries for the transaction whether they store credit card details and all or not
  19. Verify DB entries for the amount related fields in database for the fresh transaction, modified transaction and canceled transaction.
  20. Verify error page during payment gateway process
  21. Verify security passes for the transaction
  22. Sometimes payment gateway sent confirmation through popup dialogs – so test popup blocker related settings also. What happens is popup blocker is on and all this.
  23. Check buffer pages between application and payment gateway (firefox firebug add-on will be helpful to test)

Hope guys, it will be helpful to you in your testing. If any new points are there then update it in comments, so I also get update in my knowledge.

Wednesday, September 22, 2010

How to run your first selenium test in visual studio 2008 with c#

Step1:

Selenium IDE

  1. Install selenium IDE as add on in your firefox browser.
  2. Run Selenium IDE and record script using it with language as C#.
  3. Stop recording and copy the code.

Step2:

Selenium RC

  1. Download selenium RC file and extract it to your local folder
  2. Go through command prompt and local folder directory
  3. Write command as java – version to check java environment availability.
  4. If java environment is not there then install it first using jdk 1.6
  5. Write command as java –jar selenium-server.java
  6. It will start selenium RC server. If it gives port error then write java –jar selenium-server.jar –port 5555

Step3:

Visual Studio 2008

  1. Create one project with window application
  2. Add references with client drivers of selenium in the project
  3. In form1 add one button
  4. In project add one class as new item.
  5. Copy your selenium IDE c# script to that class1.cs
  6. In setup method give URL of your site going to be test in class1.cs as

public void SetupTest()

{

selenium = new DefaultSelenium("localhost", 4444, "*chrome", "http://www.gmail.com/");

selenium.Start();

verificationErrors = new StringBuilder();

}

7. Add reference of using library

8. In form1.cs button’s click event make object of that class using

private void button1_Click(object sender, EventArgs e)

{

SeleniumTests.Untitled a = new Untitled();

a.SetupTest();

a.TheUntitledTest();

a.TeardownTest();

}

9. Build the solution and run the application on click of button.

Thursday, September 9, 2010

About VMWare Virtual Machine Server

Steps of Configure Virtual Machine

1. Right click in right panel of vmware > Add Virtual Machine

2. Select path where you want to store your virtual machine (or space of virtual machine)

3. Select network path

4. Add disk size

5. Add ram

6. Start virtual machine

7. Install operating system

8. Install vmware tools

9. Give computer name

10. Give IP of computer

11. Set firewall off (as per requirement)

12. Add required software

13. Take snapshot

VMWare -Virtual Machine File types

1. Windows Server 2003 Enterprise Edition-000001.vmdk – Vmware virtual disk file

2. Windows Server 2003 Enterprise Edition.vmsd –VMSD file

3. Windows Server 2003 Enterprise Edition.vmx – vmware configuration file

4. Windows Server 2003 Enterprise Edition-Snapshot7.vmsn- VMware virtual machine snapshot file

5. vmware.log –vmware log file

VMDK is virtual disk file - virtual partition with data and installed operating system (Micosoft Windows, Linux, Mac OS X, MS-DOS etc.) and used by VMware to run as a virtual machine (under host operating system).

This is a virtual disk file, which stores the contents of the virtual machine's hard disk drive.
A virtual disk is made up of one or more .vmdk files. If you have specified that the virtual disk should be split into 2GB chunks, the number of .vmdk files depends on the size of the virtual disk. As data is added to a virtual disk, the .vmdk files grow in size, to a maximum of 2GB each. (If you specify that all space should be allocated when you create the disk, these files start at the maximum size and do not grow.) Almost all of a .vmdk file's content is the virtual machine's data, with a small portion allotted to virtual machine overhead.
If the virtual machine is connected directly to a physical disk, rather than to a virtual disk, the .vmdk file stores information about the partitions the virtual machine is allowed to access.
Earlier VMware products used the extension .dsk for virtual disk files.

-.vmdk
This is a redo-log file, created automatically when a virtual machine has one or more snapshots. This file stores changes made to a virtual disk while the virtual machine is running. There may be more than one such file. The e2_# indicates a unique suffix added automatically by VMware Workstation to avoid duplicate file names.

Log files – Log files are just that- a log of virtual server activity for a single virtual server. Log files should be used only when you are having trouble with a virtual machine.

VMEM – A VMEM file is a backup of the virtual machine’s paging file. It will only appear if the virtual machine is running, or if it has crashed.

VMSN & VMSD files – these files are used for VMware snapshots. A VMSN file is used to store the exact state of the virtual machine when the snapshot was taken. Using this snapshot, you can then restore your machine to the same state as when the snapshot was taken. A VMSD file stores information about snapshots (metadata). You’ll notice that the names of these files match the names of the snapshots.

NVRAM files – these files are the BIOS for the virtual machine. The VM must know how many hard drives it has and other common BIOS settings. The NVRAM file is where that BIOS information is stored.

VMX files – a VMX file is the primary configuration file for a virtual machine. When you create a new virtual machine and answer questions about the operating system, disk sizes, and networking, those answers are stored in this file. As you can see from the screenshot below, a VMX file is actually a simple text file that can be edited with Notepad.

What is a VMware Shapshot?

A snapshot is a picture of your system at the time the snapshot is taken. Think of it as an image of your computer’s hard drive. Besides just the data on the hard drive, the VMware configuration for that virtual machine and the BIOS configuration are also saved when you take a snapshot. The snapshot files that are created contain only the changes that have occurred to the virtual machine since the snapshot was taken. Thus, over time, the snapshot files will grow as the machine is used more and more.

What Snapshot files are created?

When a snapshot is created a number of files are created in the directory for that virtual machine.

  • -SnapshotX.vmsn (Where X is the number of the snapshot taken) This file stores the state of the virtual machine when the snapshot was taken.
  • -SnapshotX.vmem (Where X is the number of the snapshot taken) This file stores the state of the virtual machine memory when the snapshot was taken.
  • -nnnnnn.vmdk (where nnnnnn is the number of the disk image, not corresponding to the snapshot number) These are log files which store changes to the virtual machine, since snapshot was taken. There may be many of these files over time.

Snapshots in Workstation vs Server

VMware Workstation has the ability to create multiple snapshots and offers a very nice Snapshot Manager. The Snapshot Manager was introduced in VMware Workstation 5.0. With Snapshot Manager, you can view the snapshot tree. Each snapshot will be represented by a screenshot of what the screen looked like when the snapshot was taken. VMware Server lacks two important features: Snapshot Manager (multiple snapshots) and virtual machine cloning. In other words, in VMware server, you can only take a single snapshot and then revert back to that snapshot.

One alternative to taking snapshots that can be used in VMware Server is to shutdown the virtual machine and copy the vmdk, vmem, vmx, and nvram files. Later you could replace these files and your virtual machine would be back at the point of when that copy was made. This is a manual way of taking snapshots. However, this method takes much more disk space when compared to snapshots.

Summary

VMware snapshots are an extremely valuable feature of VMware. Currently, VMware workstation offers the Snapshot Manager, which offers the possibility of multiple snapshots. Currently, VMware Server offers only the ability to take a single snapshot. With snapshots, you can save the state of your virtual system BEFORE you make risky changes like installing applications, adding a new patch, or making a configuration change. When things go wrong, Snapshots can really save your day