loading

SQL Interface

The SQL Interface module provides access from RoboRealm into an ODBC compatible SQL database. Using the provided interface you can execute statements against an ODBC database to insert RoboRealm variables into SQL tables and read back information from the database into RoboRealm.

NOTE: As this module runs each time an image is processed, it is very likely that you can fill a table with many values very quickly. Care should be take to determine when a value should be written either by selecting the "Send only when statements change" or by surrounding this module with the If_Statement module that will prevent execution on every pipeline iteration.

Interface

Instructions

1. Connection String - The connection string is where you specify how you want to connect to your database. If you already have a DSN setup you can specify DSN=name to utilize that DSN to connect.

2. Remember As Default - Select the "Remember as Default" checkbox if you would like the current Connection String to be remembered by RoboRealm such that whenever the module is inserted into the RoboRealm pipeline the Connection String will be auto-populated to the current setting. This ability allows you to not have to constantly type in the Connection String setting when loading in successive RoboRealm robofile configurations as long as the connection properties remain the same.

3. SQL Statements - Enter in the SQL statements that you want to execute. This interface supports multiple SQL statements separated by a semicolon (;) and can include INSERT, DELETE, UPDATE and SELECT statements. Any information returned by a SELECT statement is added into the RoboRealm variable namespace with a "SQL_" prefix. To populate SQL statements with RoboRealm variables you can include RoboRealm variables within [] which will be processed as specified in the Expressions page.

As SELECT statements can include field names that are not compatible with the RoboRealm variable naming, any character within the field string that is not a letter or digit will be replaced with an underscore. Thus if you query for count(*) it will be translated into SQL_count___ where the last ___ are the translated (*) characters.

4. Insert - A convenience button is provided to allow you to specify a variable from within RoboRealm to be inserted into the Text editing. This provides no more functionality than just adding in the variable text name into the SQL Statement editor to ensure correct spelling.

5. Connect - Press the Connect button when you have specified the Connection String and SQL statements to execute. This will connect to the database and begin executing the SQL statements after parsing for [expressions].

6. Send only when statements change - To help reduce the load on the database you can chose to only execute the SQL statements when the values specified in the SQL statements change. This works for INSERT and UPDATE statements (assumed to be the more common use of the module) since they specify values to be saved to the database but will NOT work for SELECT or DELETE statements unless something in the query is changing (perhaps a WHERE id = [my_id]) otherwise the statements will never change their text and therefore not execute during successive pipeline iterations.

7. Send Rate - To further help reduce the load, you can select to only execute the SQL statements at a defined timing rate.

Examples

	insert into Report (image_num, result, date)
		values ([IMAGE_COUNT], [RESULT], NOW())
would insert the current image count (IMAGE_COUNT), the value of a variable called RESULT and the current date/time (MySql function) into a Report table. Likewise,
	select config_z, config_a from Config_Table limit 1;
would read in two configuration items (perhaps values to be used in processing modules) and enter them into the RoboRealm variable table as SQL_config_z and SQL_config_a. Finally,

	select count(*) as total from reports
would insert the number of records in the reports table within a SQL_total (as apposed to SQL_count___) variable.

Variables


SQL_ERROR - Set when an error is encountered.

See Also


Write Variables
Write Text

For more information


Wikipedia SQL

 New Post 

SQL_Interface Related Forum PostsLast postPostsViews
SQL_Interface fails INSERT INTO
Dear RR team, I downloaded your trial to check the creation of a smart barrier. The RR part should ...
5 year 3 1622
Net Framework Data Provider
Dear STeven, What is a syntax of the connection string  supported in SQL_Interface module? Only OD...
7 year 2 1915