Microsoft SQL server
As an alternative to the OLEDB connection, a Microsoft SQL Server can also be accessed via a native driver.
The most important parameters have the same meaning and differ from the OLEDB parameters only in name.
Notice
A full list of all supported parameters can be found at: https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.connectionstring?view=sqlclient-dotnet-standard-5.2
Key | Description |
|---|---|
Provider* | Value |
Server* | Name or IP address of the database server The name or IP address can be supplemented by an instance name separated by a |
Database* | Name of the database |
Uid(*) Pwd(*) | User name and password for authentication |
Trusted_Connection | Value |
Specify the database command in standard SQL syntax. You can generally use a SELECT statement for this. Depending on the macro function, the provider also supports other SQL commands.
For a query macro such as QueryDbRecord(), for example, a SELECT expression of this type can be defined:
SELECT Column1, Column2 + Column3 AS Column2And3 FROM Table1 WHERE Column1 = @Field1
In most cases, the values of a read-in record are returned below the column name (e.g., as Column1). However, this is not the case for calculated values such as Column2 + Column3. For calculated values, an alias name such as Column2And3 can be assigned if required. If no alias name is assigned, Column {Number} will be used as the default name.
For the sample macro above, only the order of the values in the data set is relevant. When using interactive database queries in the Office+ Index Client, for example, a descriptive display name might also be useful. The dynamic filter value @Field1 is written as a typed variable in the example, such that syntax does not matter. If, however, the value is inserted as %Field1% using pure text replacement, always define a text field as such by surrounding it with quotation marks. Also, pay attention to special cases. One such case would be that of a filter value with quotation marks. Add another set of quotation marks to surround the ones already surrounding the filter value.
The ExecDbCommand() macro is suitable for executing non-query commands (e.g., for inserting, updating and deleting data records). A short example is given here for each of these commands:
INSERT INTO TableName (Column1, Column2) VALUES (@Field1, 'Test') UPDATE TableName SET Column2 = 'Test' WHERE Column1 = @Field1 DELETE FROM TableName WHERE Column1 = @Field1