Properties
The SQL module has the following properties:
| Name | Description | ID | Flags |
|---|---|---|---|
| ADO CommandTimeout | Indicates how long to wait (in seconds) while executing an SQL command before terminating the attempt and generating an error. The default is 30 seconds. If you enter the value 0, SQL commands will never timeout. | 1090 | Persisted |
| AvgSQLQTime | The average query time in milliseconds for SQL queries performed. This is averaged from the last time the statistics were reset or Apis was restarted. | 100000 | PerformancePage |
| Connection state | The current state of the connection to the database. This field allows you to see if the bee is connected to the database, if it has the appropriate security rights and if the stored procedure ran successfully. | 10000 | InfoPage |
| Custom ConnectionString | When not using MS SQL Server, or if you want to have full control over the connection string, you can specify a custom connection string here. NOTE! When this property is in use, the database login, database login password, database name, and database server are ignored. | 1040 | Persisted, ExpertPage |
| Database login | The same as User ID in a standard connection string. If this field is blank, integrated security will be used. | 1020 | Persisted, User |
| Database login password | The password to use when accessing the SQL database. | 1030 | Persisted, Password |
| Database name | The name of the database on given server, used in the connection string. | 1015 | Persisted |
| Database server | The server machine where the database exists, used in the connection string. | 1010 | Persisted |
| Description | A description of this module, useful for future maintenance to describe what this module should do. | 101 | Persisted |
| ExchangeRate | The exchange rate in milliseconds for the 'ExternalItems' timer event. By default, used for updating external items values. | 100 | Persisted |
| Last executed SQL | The last executed SQL statement, the text last sent to the SQL server for execution. This field can be useful for debugging, as it allows you to see exactly what SQL is being sent to the SQL server. | 10100 | InfoPage |
| LastRecordsetParseTime | The last time a recordset was received and parsed from the SQL Server. | 100100 | PerformancePage |
| Locale ID | The locale ID to be used when parsing files. This is a unique id for the country and language, used to determine multiple factors including date formats and decimal points. | 1057 | Persisted |
| MaxSQLQTime | The maximum query time in seconds for the SQL queries performed since the last time the statistics were reset or Apis was restarted. | 100001 | PerformancePage |
| PDS snapshot SourceDatabase | The name of the snapshot source database. Must be specified when running queries on a PDS snapshot, use this field together with the property 'PDS snapshot usage' | 1046 | Persisted, ExpertPage |
| PDS snapshot usage | Whether to use PDS Snapshots or not. When snapshots are to be used, specify the name of the source database database in property 'PDS snapshot SourceDatabase' as well , and the SQL query will be executed on a snapshot acquired from the PDS Snapshot Manager. | 1045 | Persisted, Enumerated, ExpertPage |
Provider | The provider of the module. | Persisted | |
| SQL statement | The SQL statement to execute. Can be an 'execute statement' or a fully qualified file name containing the statements to execute. This, in combination with the SQL statement type property, decides what text to send to SQL server. | 1080 | Persisted |
| SQL statement type | This dropdown property has two options: Execute as text - the text will be sent to the SQL database as it's written and executed. For example, SELECT * FROM AnyTableName; Execute as stored procedure - A name of a stored procedure with predefined signature that will be executed with arguments (mandatory when using Write items). The write items are sent as XML, which is covered in further detail in the Quick Start Guide. | 1079 | Persisted, Enumerated |
| Table schema | How the data will be returned from the SQL executed. 'By rows' means item name, value, timestamp and quality are in one row, in this order. 'By columns' means item name(s) is column header(s), value in first row, timestamps and quality are not supported. | 1075 | Persisted, Enumerated |
| Timer interval | Timer interval in milliseconds, must be >= 50 ms. Ensure that this is not smaller than the runtime of the SQL executed, or the server may struggle to keep up with demand from the module. | 1110 | Persisted |
| TimeReferenceItem | An item, the value of which will be used as the time reference for this module instead of the system time when setting the timestamp on items. | 200 | Persisted, ApisItem, ExpertPage |
| Version | The version of SQL Bee this module is running. | Persisted |
See also Module Properties