AutoMate can easily interact with most standard database systems and its associated databases through the "SQL Query" and "Stored Procedure" actions. This involves setting up an initial ODBC connection to the desired database, which is a necessary procedure in Microsoft Windows to execute SQL statements on database engines provided by various vendors. This is ideal for automated retrieval, update, manipulation or transfer of data stored in a database. All standard SQL statements or commands are supported, such as "Select", "Insert", "Update", and "Delete".
The "SQL Query" action passes a SQL statement (including, but not limited to queries) to the datasource specified via OLEDB. If a query is specified, an AutoMate dataset with the name specified is created and populated with the query results. A "Loop Dataset" action can then be used to loop through the data populated by the dataset. The Stored Procedure action executes the selected stored procedure via OLEDB on the datasource specified. Stored procedures are SQL statements with assigned names that are configured and stored in the database server in compiled form so that it can be shared by a number of programs. Stored Procedures are often faster than repeated SQL calls. The 'Open SQL Connection' action Opens a database connection using the specified custom or predefined connection string. The connection can be identified by a unique session name, which can be referenced by subsequent SQL steps. This allows multiple SQL connections to run simultaneously.