Documentation: Run SQL Select Query

Data – Run SQL Select Query

Run SQL Select Query

Connect to a database using ODBC or an OleDB provider and select data.

Parameters

  • Connection String: database connection string. Replace password with a Secure Text variable for security
  • SQL Statement: the SELECT SQL statement to run (e.g.:  SELECT * FROM my_table)
  • Store-to Variable: a DataTable variable to store the returned database records

Result

  • Returns a DataTable and assigns it to the Store-to Variable
    • Use a ‘Loop’ action to loop through rows in DataTable
    • Use a ‘Set DataTable to Clipbaord’ action to enable you to paste into Excel or other applications
    • Use a ‘QC Write DataTable to Sheet’ action to write DataTable to a spreadsheet

Connection Strings

  • Use the ‘Build Connection String’ button to assist in building connection string
  • You may need to install appropriate ODBC drivers to connect to your database

 

 

MySQL Connection String

To connect to a MySQL Database on your local machine:

  • Select ‘ODBC’ in the Connection Type drop-down
  • Download and install both the 32-bit and 64-bit ODBC (assuming a 64-bit version of Windows) connectors using the appropriate Windows MSI Installers found here:
  • Use a connection string similar to:
    • Provider=MSDASQL;Driver={MySQL ODBC 8.0 ANSI Driver};Server=localhost;
      Database
      =myDataBase;User=myUsername;Password=myPassword;OPTION=67108864

  • OPTION=67108864 is the ODBC option that allows running multiple statements
  • Substitute a Secure Text Variable for ‘myPassword’ to keep your password secure
  • Note that you need to use the correct version # of your MySQL ODBC Driver. The current version is 8.0 at time of this writing
  • More connection strings can be found at:
    • https://www.connectionstrings.com/mysql-connector-odbc-5-2/

 

Oracle Connection String

 There are multiple ways to connect to an Oracle Database. Try the following first:

  • Select ‘Oracle .Net Client’ in the connection type drop-down (use ODBC if using a normal ODBC connection)
  • Use a connection string similar to:
    • User Id=myUserId;Password=myPassword;Data Source=localhost:1521

  • ‘Localhost:1521’ works for Oracle running on your local machine, such as Oracle Database Express Edition. Use the correct Data Source for your scenario
  • Use a Secure Text Variable in place of ‘myPassword’ to keep your password secure