How to use ODBC Data Object in Certify

Before you start working or using ODBC Data Object in Certify you need to know about ODBC.

What is ODBC?                           (Source: https://support.microsoft.com/kb/110093)

Open Database Connectivity (ODBC) is Microsoft’s strategic interface for accessing data in a heterogeneous environment of relational and non – relational database management systems. Based on the Call Level Interface specification of the SQL Access Group, ODBC provides an open, vendor- neutral way of accessing data stored in a variety of proprietary personal computer, minicomputer, and mainframe databases.

ODBC alleviates the need for independent software vendors and corporate developers to learn multiple application programming interfaces. ODBC now provides a universal data access interface. With ODBC, application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases. ODBC is a core component of Microsoft Windows Open Services Architecture.

To use ODBC you need a data source better known as DSN (data source name).

Data Source Name

This is an ODBC term that refers to the name of the database to which you’re connecting. A data source name or DSN is a string that has an associated data structure used to describe a connection to a data source. DSN attributes may include the following, but are not limited to:

  • Name of the data source
  • Location of the data source
  • Name of a driver which can access the data source
  • User ID for data access (if required)
  • User password for data access (if required)

Types of Data Source

There are three type of DSN.

  1. User DSN
  2. System DSN or Machine DSN
  3. File DSN
  • User DSN – The most common type of DSN. Stores information about how to connect to a specific data source. It can be used only by the current user on the current machine.
  • System DSN – Like the User DSN, the System DSN stores information about how to connect to a specific data source, but is available to all users on a particular machine. It is local to a computer, rather than dedicated to a user. The system, or any user having privileges, can use a data source set up with a system DSN. This is the preferred DSN type for Data Security.
  • File DSN – A data source stored in a file that can be shared among all users who have the same ODBC drivers installed. These data sources need not be dedicated to a specific user or local or in network to a computer.

How to create a Data Source Name (DSN) in Windows

You can create a DSN for your data source as follows:

Step 1: Go to Control Panel.

  • On Windows NT systems, double-click the ODBC Data Sources icon.
  • On Windows 7/XP systems, open the Administrative Tools folder and then double-click the Data Sources (ODBC) icon.
  • On some systems, the icon may appear labeled as ‘32bit ODBC’.

    Once you double click on the highlighted icon from the above screen shot, a new window ‘ODBC Data Source Administrator’ will appear.
    Step 2: Select the User DSN tab and click Add. (You must be logged in as the Administrative User or else you need the required privileges to create a DSN). Refer the screen shot below.Step 3: After pressing on ‘Add’ button a new window ‘Create New Data Source’ will appear. Select your database from Name column on this window and press ‘Finish’ button. Refer the screen shot below.Step 4: Once you select your database and press the ‘Finish’ button a window for creating a DSN will appear. Here you need to enter the below.

    • Name – your data source name (DSN). It can be any name but better to give some meaningful name. Remember you cannot create a duplicate DSN.
    • Description – some information about your data source (optional).
    • Server – IP address of your database.

    After entering all this information press the ‘Next’ button. Refer the below screen shot.

    Step 5: Then in the next window select the ‘with SQL Server authentication’ radio button and enter your database credentials. Refer the below screen shot.

    Step 6: If your credential is correct then pressing ‘Next’ from the above screen shot will take you to the next screen. Make sure that ‘Change the default database’ option is unchecked and Press the ‘Next’ button on this screen and after that press the ‘Finish’ button to complete the setup. Refer the below screen shot.

    Step 7: After completing the setup i.e. pressing the ‘Finish’ button from the above step, a new screen will appear from where you can test your DSN whether correctly configured or not. If you want to test the DSN you created press the ‘Test Data Source’ button. If everything is fine then you will get a screen with a Test Result which will give you a success message. Once you get this success message press to ‘OK’ button to complete the setup. Refer the below screen shots.



    Step 8: Now you can see the DSN which you created from Step 4 is showing in the respective area.

    ODBC Data Object in Certify

    The ODBC Data Object allows you to execute a valid SQL query during a process execution to store a data value from the underlying database and/or validate the value between Certify and database. You can query a single value per process step. For example you can query the value of a user name in one step and then query the password for that user name in the next step. Your query can be as complex as necessary to get the value you need.

    There are two actions available for this object in Certify.

    1. Database Store Action
    2. Database Verify Action

    These actions do not write to the database, but reads from the database and then writes the value to a variable in Certify.

    Important – before writing your query makes sure that your query should return a single row and a single column as a result. Certify will through an error if your query returns you more than single row/column as a part of query result.

    Database Store Action – Use this action when you want to extract a sum of numbers from the database to use in your test.

    For example – If you want to know the total number of customers you have in a certain region or the total number of users you have in the system, use the Database Store action.

    For the above example query will be like this:

    Select Count(*) from Table;

    Now refer the below screen shot how to perform this query in Certify.

    Database Verify Action – The action allows you to execute a valid SQL query during a process execution to verify a data value exists in the underlying database. This action reads from the database and then compares the value to the specified criteria. You can query a single value per process step.

    For example – You can verify the value of Last Name for the given user and let’s assume that you are getting the last name value for that user from somewhere (application).


    Contribution by Gautam Jha

Leave a Reply

Retype the CAPTCHA code from the image
Change the CAPTCHA codeSpeak the CAPTCHA code
 

SOAIS - Worksoft Newsletter

To view on your browser, click here
Facebook Twitter LinkedIn
Dear Default Value,
 

Welcome to SOAIS Newsletter of September 2021!

Continuous Testing with Remote Execution
 
The speed of innovation continues to increase, driving rapid and relentless change for today’s ever-evolving IT landscapes, creating greater risk as IT and business teams scramble to ensure timely delivery. How can your organization keep pace? Test more, worry less. With Worksoft’s Connective Automation Platform, you can easily build and maintain automated tests, accelerating testing time without losing scope or volume. You can schedule and execute remote, continuous tests to intercept defects sooner and prioritize remediation - without sacrificing your nights and weekends. Explore how continuous test automation and remote execution can empower your organization.

Click here to connect with us to get more information on our services.
 

Skip Costly Rework with Dynamic Change Resiliency​

Change resiliency is imperative in ever-evolving IT environments. Our patented object action framework streamlines change management by assigning object definitions to your shared assets. The same object may be used in a thousand automation steps, but it can be easily updated by making one simple change to the model definition. The change automatically propagates to every single instance where that object may have been used without a single line of code or manual human involvement. For more change readiness you can also engage our Impact Analysis for SAP to predict how changes in SAP transports will affect your business processes. 

Please click here to watch the video to get a gist.
 

SOAIS Blog – Nuts and bolts of Certify Database Maintenance​

One of the key thing, which is often missed by the organizations, who have invested in using Worksoft Certify for automating their Business Process Validation initiatives, is implementing a Database Maintenance Plan. While the business and the test automation consultants get excited about the shiny new thing that they have got and start building the regression suite; planning and executing a database maintenance plan for most of the customers gets pushed down the priority list. However, since all the test assets in Certify are stored in a Database, a robust database maintenance plan is very important to maintain smooth operation of Certify with acceptable performance criteria. The customers usually start facing issues once they have built significant number of Certify processes which they have started executing on regular basis. Such executions add a lot of data to the tables storing results data and increase the overall size of the Certify database.

Please click here to read the complete blog.
 

Worksoft Blog – Process Intelligence: A Multi-Dimensional Approach

The ability to extract process knowledge has become easier through the years. Technology has evolved to the point where we can deploy capabilities that connect at multiple levels to extract different types of process insight. In the past, organizations were forced to spend enormous energy extracting data manually from different applications and databases. Then, they would have to use things like spreadsheets to transform the data and convert it into meaningful information. 

Please click here and read the complete blog.
 
India
Unit 9, Level 5, Navigator, ITPL,
Bangalore - 560 066.
Phone: +91 80 40071234
US
Suite 101, 1979, N Mill St,
Naperville, IL 60563
Phone 1-800-262-2427
Please click here to Unsubscribe / Unsubscribe Preferences