Include more than 1000 Values in Oracle IN Clause

In Oracle we can’t include more than 1000 values in the “IN” clause.

To get around this limitation, you divide the string of comma delimited values to be included into two or more parts using SUBSTR or through any similar other function and then these new strings should be placed in the IN clause of different SQL statements that are later combined using the UNION operator.

Sample Code

First create two hidden parameter that include the initial first and second half of the selected values of the parameter. For this write a custom code can be used.

Sample code to break the parameter into two hidden parameter of half the original length.

Public Function Group1(ByVal parameter as Parameter) as String
 Dim s as String
 s=String.Empty
 If parameter.IsMultiValue then
 For i as integer = 0 to parameter.Count/2
 s = s "" CStr(parameter.Value(i)) + ","
 Next

Else
 s = CStr(parameter.Value(0))

End If
 Return s
 End Function

Public Function Group2(ByVal parameter as Parameter) as String
 Dim s as String
 s=String.Empty
 If parameter.IsMultiValue then
 For i as integer = (parameter.Count/2) to parameter.Count-1
 s = s "" CStr(parameter.Value(i)) + ","
 Next

Else
 s = ""
 End If
 Return s
 End Function

Sample Query

New we use two SQL queries including the two hidden parameters joined by the UNION operator

SELECT * FROM TABLE_1 WHERE VALUE_1 IN (:prmHiddenInspectorFirstGroup1)
UNION
SELECT * FROM TABLE_1 WHERE VALUE_1 IN (:prmHiddenInspectorFirstGroup2)

Obviously, this example breaks when you get to 2000 values, but it is presented here as a sample approach that can be easily modified to suit more advanced situations.

The post Include more than 1000 Values in Oracle IN Clause appeared first on OptimusBI.

Guide to Migrating a .NET Site to Azure

What are the Steps to Migrating .NET Site to Azure?

Microsoft generally does a good job of integrating its products and that is mostly true for .NET and Azure. Migrating a .NET site to Azure is relatively easy with a few details that need to be looked after.

Here is a step by step guide to migrating a .NET site to Azure.

Open the web site project

Before you convert a project, you should verify that it is working correctly. This will prevent errors during conversion.

  1. Open the existing solution.
  2. In the Build menu, click Build Web Site.

Creating a new Web application project

Now we are going to create a new local copy so we can prepare the site for Azure without wrecking our working local copy.

  1. Create a new, blank Web application project in a separate directory.
  2. Create an ASP.NET Empty Web Application.
  3. Type values for Name, Location, and Solution Name, and then clicks OK to create the Web application project.

Copying Files to the Web Application Project

The easiest way to add your existing files is to copy the files from a web site project directory to the web application project directory.

  1. In the Solution Explorer, right-click the web site project and select Open Folder in Windows Explorer.
  2. Select the files of the web site project to copy.
  3. Right-click the selected files and then click Copy.
  4. In the web application project, right-click the web application project and click Open Folder in Windows Explorer.
  5. Paste the Web site project files into the Web application project directory.
  6. In Solution Explorer of the Web application project, click the Show All Files button.
  7. Select the new files in Solution Explorer.
  8. Right-click the selected files and then click Include In Project.

One difference between a web site project and a Web application project is that in a Web site project, ASP.NET dynamically generates partial classes for ASP.NET web forms, pages and user controls.

After you copy the files from the Web site project to the Web application project, you will notice that the code behind files for each page and user-control are still associated with the .aspx, .master, and .ascx files.

Add Class Library Projects in Solution

You may have noticed in the last step that no .designer.cs or .designer.vb files have been generated. Now we will convert these pages to save their partial classes in a .designer.cs or .designer.vb file.

  1. Add Class libraries that are used in the working site  (or that can be used in the Web Application project).
  2. Then build these Project libraries.

Setting Project References

Add references of build class libraries to the web application project.

If the Web site project requires additional project or assembly references, you can add them to the Web application project.

To help prevent errors, before you convert the project files, add references to the web application project for assemblies that are in the Bin folder in the web site project.

To add references to a web application project

  1. In Solution Explorer, right-click References, and then click Add ReferenceThe Add Reference dialog box should display.
  2. Select the reference that you want to add and then click OK.
  3. In Solution Explorer, right-click the Web application and click Build.

To add references for web services to a web application project

  1. In Solution Explorer, right-click References, and then click Add Web ReferenceThe Add Web Reference dialog box should display.
  2. Type the URL for the web service that you want to add and then click OK.
  3. In Solution Explorer, right-click the Web application and click Build.

Converting the Project Files

To convert pages and classes to use partial classes in a Web application project:

  1. In Solution Explorer, right-click the root project folder that contains the pages and classes that you want to convert and then click Convert to Web Application.
  2. Build the project to see whether there are any compilation errors.

If you see errors, the two most likely causes are as follows:

  • An assembly reference is missing and  must be added to your project.
  • There is an issue with a dynamically generated type, such as the Profile object or a typed dataset.

Instead, store class files in any folder of your project other than the App_Code folder. This is done automatically by the Convert to Web Application command, which renames the folder to Old_App_Code.

Running the Web Application Project

You can now compile and run the application. By default, Visual Studio uses the built-in Visual Studio Development Server to run the site. Alternatively, you can configure the project to use Internet Information Services (IIS).

To manage Web application project settings, right-click the project and then click Properties.

Add Windows Azure Project

Add a Windows Azure project with an empty Web Role in the web application project.

  1. In the File menu, click New, and then click Project.
  2. In the Project Types pane, select Cloud from the Visual C# or Visual Basic project template nodes.
  3. In the Templates pane, select Windows Azure Project.
  4. Enter the name and location for your project and a name for the solution. Click OK.
  5. In the New Windows Azure Project dialog, select the empty Web Role node.
  6. Click OK to create your solution in Visual Studio.

Add Role to the Windows Azure project

Finally, add the role to the Windows Azure project.

  1. In the Windows Azure project right click on the Role folder.
  2. Click on Add and then click Web Role project in Solution. The Associate With Role Project dialog box should open.
  3. In the dialog box, select the project dll of the web application.

 

Contact us to learn more.

 

(Note: This post has been updated with new information)

Test Scenarios for Credit Card Payment Through a POS Application

What are the Test Scenarios for Credit Card Payment through as POS Application

Point of sale applications need to handle a wide variety of transactions like cash, debit cards, credit cards, gift cards and loyalty cards. Credit cards play an important role in payment for anything purchased. Below are some of the most important test scenarios for credit card payment which should be tested with any credit card payment solution when integrated with a POS application.

Prerequisites

Credit Card Configuration: Includes configuration of card length, card range and card type (VISA, AMEX, MASTER etc)

Merchant Configuration: A merchant needs to be configured who is authorized to accept card type payments

Credit Card Processor Configuration: Credit card processor needs to be configured to process credit card payment (e.g. Mercury Payment System or Lynk, etc.)

Test Scenarios

Capturing Card Details: Following areas should be tested while capturing card details.

  1. Credit card number : Test card numbers using the correct length and range and card numbers that are outside the correct length and range.
  2. Expiry date: Test valid expiry dates, invalid expiry dates and invalid date formats.
  3. CVV number: Test valid CVV numbers , mismatched CVV numbers and blank CVV numbers.
  4. AVS code: Entering AVS details for configured numeric or alphanumeric formats.
  5. Card reader to capture card details: Test swiping of cards from both sides and chips.
  6. Encryption: Verify that captured card numbers are properly encrypted and decrypted.

Authorization: Once card details are captured, they are sent to processor to be authorized. Following areas need to be tested during authorization.

  1. Authorized amount: Test that the correct amount is being authorized.
  2. Receipt printing: Test that merchant and customer copies of the receipts and any vouchers print properly.
  3. Receipt details: Check that the receipts are printing the proper date, time, card details, authorized amount etc…
  4. Response code: Test that the correct response codes are being returned for approved, declined, on hold and all other transactions.

Settlement: Once the payment is done following things should be tested:

  1. Reprinting receipt: Test that you can reprint the receipt for a closed transaction.
  2. Void credit card’s payment: Check that you can void a payment before posting it and that after posting a payment voiding is not allowed.
  3. Verifying report: All information regarding each credit card transaction should be reflected in reports. Any adjustments made in closed checks should be reflected in the report.

Contact us to lear about our testing services.

(Note: This post has been updated with new information)