Skip to main content
Version: Classic

Generic SQL

Configuration​

<!-- AddressService -->
<AddressProvider id="E10A8313-A92D-4CB2-A12B-9AEB58F39207" order="1" active="true">
  <!-- serverside generic SQL address provider -->
  <AddressProvider id="7E50AA46-A035-4F11-B44F-BBCBAB4780B7" Title="My DB Soure">
    <SearchParameters>
      <SearchParameter Name="firstName" Label="Vorname/Name" Type="String" Length="100" Sort="1" />
    </SearchParameters>
    <ConnectionString>{ConnectionString}</ConnectionString>
    <ConnectionProvider>System.Data.SqlClient</ConnectionProvider>
    <Query>
      SELECT * FROM Users WHERE FirstName Like '%' + @firstName + '%'
    </Query>
    <Mapping>
      <Map Source="CompanyName" Target="Company_Name" />
      <Map Source="Title" Target="Person_Title" />
      <Map Source="FirstName" Target="Person_FirstName" />
      <Map Source="LastName" Target="Person_LastName" />
      <Map Source="Birthday" Target="Person_BirthDate" />
      <Map Source="Street" Target="Company_Street" />
      <Map Source="Zip" Target="Company_ZipCode" />
      <Map Source="City" Target="Company_City" />
      <Map Source="FaxBusinessDirect" Target="FaxDirect" />
      <Map Source="EmailBusinessDirect" Target="EmailDirect" />
      <Map Target="Company_CountryShortCode">
        <Map.SourceExpression>
          function main()
          {
            switch(source('Country'))
            {
              case 'Albanien': return 'AL';
              case 'Andorra': return 'AD';
              case 'Aserbaidschan': return 'AZ';
              case 'Belgien': return 'BE';
              case 'Bosnien und Herzegowina': return 'BA';
              case 'Bulgarien': return 'BG';
              case 'Dänemark': return 'DK';
              case 'Deutschland': return 'DE';
              case 'Estland': return 'EE';
              case 'Finnland': return 'FI';
              case 'Frankreich': return 'FR';
              case 'Georgien': return 'GE';
              case 'Griechenland': return 'GR';
              case 'Irland': return 'IE';
              case 'Island': return 'IS';
              case 'Italien': return 'IT';
              case 'Kasachstan': return 'KZ';
              case 'Kosovo': return 'XK';
              case 'Kroatien': return 'HR';
              case 'Lettland': return 'LV';
              case 'Liechtenstein': return 'LI';
              case 'Litauen': return 'LT';
              case 'Luxemburg': return 'LU';
              case 'Malta': return 'MT';
              case 'Mazedonien': return 'MK';
              case 'Republik Moldau': return 'MD';
              case 'Monaco': return 'MC';
              case 'Montenegro': return 'ME';
              case 'Niederlande': return 'NL';
              case 'Norwegen': return 'NO';
              case 'Österreich': return 'AT';
              case 'Polen': return 'PL';
              case 'Portugal': return 'PT';
              case 'Rumänien': return 'RO';
              case 'Russland': return 'RU';
              case 'San Marino': return 'SM';
              case 'Schweden': return 'SE';
              case 'Schweiz': return 'CH';
              case 'Serbien': return 'RS';
              case 'Slowakei': return 'SK';
              case 'Slowenien': return 'SV';
              case 'Spanien': return 'ES';
              case 'Tschechien': return 'CZ';
              case 'Türkei': return 'TR';
              case 'Ukraine': return 'UA';
              case 'Ungarn': return 'HU';
              case 'Vatikanstadt': return 'VA';
              case 'Vereinigtes Königreich': return 'GB';
              case 'Weißrussland': return 'BY';
              
              default: return 'CH';
            }
            
            return '';
          }
        </Map.SourceExpression>
      </Map>
    </Mapping>
  </AddressProvider>
</AddressProvider>

Parameters​

  • id 7E50AA46-A035-4F11-B44F-BBCBAB4780B7
  • SearchParameter List with all possible entries.
    • *Name__ Unique id for the parameter. Corresponds to the named parameter for the sql query.
    • Label Displayed text in front of the input field Type Input type: String (Text), Long (Number), Boolean (Yes/No) or Date (Date) Length Maximum length for strings
    • Sort Sort value against the other parameters
  • Mapping Mapping to OneOffixx contact fields, see Mapping
  • ConnectionString ConnectionString. May be encoded.
  • ConnectionProvider System.Data.Odbc, System.Data.SqlClient, MySqlConnector, Oracle.ManagedDataAccess.Client.
  • Title The title that will be displayed to the user.
  • Icon Base64 icon that will be displayed to the user in the recipient dialog.

Query​

The SQL query must be in the format for the respective target database. Since all search parameters are treated as SQL parameters, the respective SQL parameter syntax of the target system must be followed.

Example: MS SQL​

SELECT FirstName, LastName FROM Users
WHERE FirstName LIKE '%' + @firstName + '%' AND LastName LIKE '%' + @lastName + '%'

Example: MySQL​

SELECT FirstName, LastName FROM Users
WHERE FirstName LIKE Concat('%', @firstName, '%') AND LastName LIKE Concat('%', @lastName, '%')
warning

CAUTION
For Oracle, Odbc and PostgreSQL the order of the placeholders in the query must match the order of the "SearchParameters!

Example: Oracle​

SELECT FirstName, LastName from Users
WHERE UPPER(NVL(FirstName, ' ')) Like UPPER('%' || :firstName || '%') AND UPPER(NVL(LastName, ' ')) Like UPPER('%' || :lastName || '%')

Example: Odbc​

SELECT FirstName, LastName FROM Users
WHERE FirstName LIKE '%' + ? + '%' AND LastName LIKE '%' + ? + '%'

Example: PostgreSQL​

SELECT FirstName, LastName FROM Users
WHERE UPPER(FirstName) LIKE UPPER('%' || ? || '%') AND UPPER(LastName) LIKE UPPER('%' || ? || '%')
warning

CAUTION
The restriction of the sequence has the consequence that a parameter can be used exactly once in the query.

The following MS SQL query cannot be translated directly to Oracle or Odbc:

SELECT FirstName, MiddleName, LastName FROM Users
WHERE FirstName LIKE '%' + firstName + '%' OR MiddleName LIKE '%' + firstName + '%'

If a parameter needs to be compared on more than one column, then these columns can be combined in the SELECT using the respective function of the database system:

Example: Odbc​

SELECT STRING(FirstName, ' ', MiddleName) AS CompleteName, LastName FROM Users
WHERE CompleteName LIKE '%' + ? + '%'