Silktest And Excel

Using SilkTest’s DB_Tester Functions with an Excel Spreadsheet

INTRODUCTION It is often desirable to be able to store test data in an easily accessible environment. Standard ASCII files can fit that bill. However, if one wants more power for getting data into and out of a test data structure, Segue’s Silk Test’s DB Tester functions are an excellent way of accessing information in database. Additionally, for even greater ease-of-user, an Excel spreadsheet can be used as a database provided that proper conditions are met. This white paper will not cover all of the intricacies of SQL statements. There are plenty of on-line guides to SQL. Here are a few: o http://www.sqlcourse.com/ o http://w3.one.net/~jhoffman/sqltut.htm

E S TA B L I S H I N G

AN

E X C E L D ATA B A S E

One might think that putting data into a Microsoft Excel spreadsheet and saving the spreadsheet would be all that’s needed. Unfortunately, this is not the case. It might be logical if each spreadsheet contained in a Microsoft Excel workbook were considered a table in the database. However, Microsoft designed Excel differently. In the following example, the worksheet named “Ages” is not a database table, just a worksheet.

In order to make the data into a table, you must select the desired region of the spreadsheet or select the entire spreadsheet and name the region. This is accomplished by enter a name for the new region into the Name Box in Excel. If you plan on your database table contents growing, it may be best to make the entire spreadsheet the named region.

SilkTest and Excel Databases

Page 2 of 8

After selecting the desired region, enter a name into the Name Box and hit Enter.

Name Box

Any time you want to select the entire region later, all you have to do is select it from the Name Box PopupList. The entire named region will be selected. Save your Excel workbook wherever you like. But remember where it is.

E S TA B L I S H I N G

AN

O D B C D ATA S O U R C E

SilkTest’s DB Tester functions require that the database have an ODBC Data Source. In order to make your Excel spreadsheet an ODBC Data Source, launch the ODBC Manager from Control Panel.

SilkTest and Excel Databases

Page 3 of 8

Click the Add… PushButton. You will then be prompted for which driver you want to use.

After clicking Finish you’re not really finished. You will then be prompted for more information about the Excel database you’re trying to establish a connection to. Give your data source a name (recommended not to use spaces in your name) and then use the Select Workbook PushButton to browse to the database you just selected.

Click OK.

SilkTest and Excel Databases

Page 4 of 8

You’ve now created a new ODBC Data Source and it should appear in your list of data sources.

A C C E S S I N G D ATA

FROM

SILKTEST

The DB Tester functions in SilkTest are very well documented. However, here’s a brief look at the functions and how they’re used. o DB_Connect o This must be executed prior to any other statement as it returns the connection handle to the database. The argument passed to the function is the connection string to the database. In terms of an Excel database, the connection string will look like this: “dsn=SilkTest_DB” (or whatever name you gave the ODBC Data Source in the ODBC Manager). If you need to write to the database, the connection string must also include “ReadOnly=False” such as “dsn=SilkTest_DB;ReadOnly=False” even if you specified that the connection is not read only in the ODBC Administrator. o DB_ExecuteSQL o This actually executes the desired query on the database and returns a connection handle to the temporary table that the query returns (in the case of SELECT queries). There are two arguments passed to this function: 

Database connection handle returned by DB_Connect. It is possible to connect to more than one database at a time, which is why this function needs to know which database connection to use.



SQL Statement. This can be any valid SQL statement.

o DB_FetchNext SilkTest and Excel Databases

Page 5 of 8

o This is the workhorse that actually retrieves data from the spreadsheet/database. It has two arguments to it. 

Handle to the SQL connection made by DB_ExecuteSQL. Just as you can have multiple database connections open, you may have multiple SQL queries running. As such, this function needs to know what query to retrieve data from.



Variable(s) to retrieve the data into. You can either specify multiple variables for a query that’ll retrieve multiple data, or you can specify a record datatype whose fields match the data to be returned.

o DB_FinishSQL o This closes the SQL connection, essentially cleaning up after yourself and deleting the temporary table created by the SELECT query. The only argument required is the handle to the SQL connection. o DB_Disconnect o This closes the connection to the database. Pass in the handle to the database connection as an argument. Here’s an example of a simple query on the spreadsheet and ODBC Data Source created earlier in this document.

SilkTest and Excel Databases

Page 6 of 8

As mentioned, if getting more information from the database, you can pass multiple arguments to the DB_FetchNext function:

Or, you can create a record data structure:

And then you can use the data structure in DB_FetchNext:

SilkTest and Excel Databases

Page 7 of 8

CONCLUSION Retrieving data from an Excel spreadsheet isn’t all that difficult so long as you know how to setup the spreadsheet, the system, and SilkTest correctly. In short: o Name a region to be considered a “table”. o Create a data source pointing to the spreadsheet. o Call it like any other database.

SilkTest and Excel Databases

Page 8 of 8