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.
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.
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.
SQL_ERROR - Set when an error is encountered.
For more information
|SQL_Interface Related Forum Posts||Last post||Posts||Views|
Net Framework Data Provider
What is a syntax of the connection string supported in SQL_Interface module? Only OD...