How to create CSV files using ODBC connections
Sometimes the data used for a load test can change often and its origin is a database server.
In these circumstances it might be easier to generate the CSV files automatically by querying the database before a load test is run. This can be done by either scripting manually or recording an ODBC application.
Different from accessing the database directly, creating CSV files out of a database connection can have advantages, such as better performance and segregation of the pointers across different agents, if the project is scripted so.
This sample project contains a script and a custom function to create a CSV file out of an ODBC connection. This function can be used as many times as necessary to create individual CSV files for each SQL statement.
Another advantage of creating the files using ODBC in Silk Performer is that testers can obtain the data they need more independently from database administrators, especially in situations where previously used data is no longer suitable for new tests.
This is how the createCSVfromODBC() function is used in the script:
The SQL statements ZipCodes_all_City, ZipCodes_NY_Zipcode and ZipCodes_metadata are defined as follows:
The last statement is just an example of how to obtain the information about the table queried in the previous queries.
The main part of the createCSVfromODBC() function does the following:
- Deletes any previous files;
- Creates a CSV file containing all the fields resulting from the SQL statement up to the number of columns specified as a parameter to the function;
- Replaces NULL fields with the '(NULL)' string in the CSV file.
Here's how it looks:
To adapt the script to a particular environment and connection settings, edit the pertaining Project Attributes from the Workbench Menu: PROJECT | PROJECT ATTRIBUTES. For this sample to work, it is necessary to create an ODBC connection that matches the connection name in project attributes.
To download the sample project click here: odbc2csv.zip