This page is no longer current.

Please visit the Worksoft Customer Portal for the latest content.

Articles

How To Use The "SQL to RecordSet" Action


The “SQL to RecordSet” action is part of the Record Set class. It is used to import any number of rows or columns into a layout and recordset from another SQL Server ODBC Data Source.

This new action is applicable only to Certify 9.0.1 and up.

PREREQUISITE

An ODBC Connection is required. To create a SQL Server ODBC Data Source, please go to http://community.worksoft.com/Knowledge-Base/how-to-create-a-sql-server-odbc-data-source.html

RULES
  1. The columns in the Recordset must match the columns in the SQL statement that is being executed. For example, if the SQL query is “SELECT col1,col2,col3,… FROM dbname.tablename”, then the Recordset columns should also be (in order) col1,col2,col3,…
  2. Only one SELECT statement is allowed.
  3. No UPDATE or DELETE statements allowed.
  4. No cascading query in  the SELECT statement allowed.

    EXAMPLE

    SQL QUERYSELECT Name,Description FROM vABenny_MC.dbo.Project
    This query returns the ‘Name’ and ‘Description’ from the table ‘dbo.Project’ in database ‘vABenny_MC’.

    This example will use a layout (called ‘sql2rs_L’) that has two text variables ‘Name’ and ‘Description’ as shown here.




































    This layout has a recordset ‘sql2rs_RS’ as shown here.






























    TEST STEP:
    (refer to the following screenshot)

    Application Version = System 1.0
    Window = System
    Object = Record Set
    Action = SQL to RecordSet

    PARAMETERS:
    (refer to the following screenshot)

         (i)    SQL (select only) = <type yor SQL select statement here>
                 SELECT Name,Description FROM vABenny_MC.dbo.Project
         (ii)   ODBC Connection = <type your System DSN here as per PREREQUISITE>
                 SQLtoRecordset_DS
         (iii)  ODBC UserName
                 CertifyTester
         (iv)  ODBC Password
                 ********

























    PARAMETERS (continued):
    (refer to the following screenshot)

         (v)    Layout = <choose the appropriate layout as per the SQL statement>
                  sql2rs_L
         (vi)   Recordset = <the appropriate recordset associated with the above layout>
                  sql2rs_RS
         (vii)  Mode = Append/Overwrite
                  Overwrite
         (viii) ColumnDelimiter = <COMMA>/<TAB>/<SEMICOLON>/<SPACE>
                  <COMMA>
         (ix)    Ignore First Line checkbox
                  Unchecked
         (x)    TextQualifier = <DOUBLEQUOTE>/<SINGLEQUOTE>/<NONE>
                  <SINGLEQUOTE>
                  Note: If there is a single quote any where in your Text, then use the <DOUBLEQUOTE> qualifier.
























    Run the example process above.























    The RecordSet ‘sql2rs_RS’ has now been populated with 13 rows of values from the table dbo.Project. Refer to the following two screenshots.





















































    End of article