Generic SQL

Addresses are fetched via SQL SELECT query at the data source and assigned to an address object in OneOffixx. Each database column is filled into a OneOffixx field with the keyword "as". The WHERE clause contains the filter parameters. These are defined in the SearchParameter element. The value of a parameter can be called in the query with {Parametername}.

<AddressProvider id="8C51B042-81EA-46E3-A429-821641E19A6A" order="1" active="false">
  <Debug>false</Debug>
  <Title>Generic SQL Provider</Title>
  <Icon>{Base64 Image}</Icon>
  <SafeQuery>false</SafeQuery>
  <ConnectionString>{ConnectionString}</ConnectionString>
  <ConnectionProvider>System.Data.OleDb</ConnectionProvider>
  <Query>
    <!-- Example in T-SQL -->
    SELECT 
        [SPALTENNAME] AS {Feldname OneOffixx} 
    FROM [TABLE]
    WHERE
        UCase([Vorname]) Like UCase('%{firstName}%') AND
        UCase([Name]) Like UCase('%{lastName}%') AND
        UCase([Strasse] &amp; ' ' &amp; [Hausnummer]) Like UCase('%{street}%') AND
        [Postleitzahl] Like '%{plz}%' AND
        UCase([Ort]) Like UCase('%{city}%')
  </Query>
  <SearchParameters>
    <SearchParameter Name="company" Label="Firma" Type="String" Length="100" Sort="1" />
    <SearchParameter Name="firstName" Label="Vorname/Name" Type="String" Length="100" Sort="2" />
    <SearchParameter Name="lastName" Label="" Type="String" Length="100" Sort="2" Width="90" />
    <SearchParameter Name="street" Label="Strasse" Type="String" Length="100" Sort="3" />
    <SearchParameter Name="plz" Label="PLZ/Ort" Type="String" Length="6" Sort="4" />
    <SearchParameter Name="city" Label="" Type="String" Length="100" Width="130" Sort="4" />
    <SearchParameter Name="country" Label="Land" Type="String" Length="100" Sort="5" />
  </SearchParameters>
  <ContactMapping>
    <ContactItemXPath>Contact</ContactItemXPath>
    <ContactElement id="Person_LastName">Column_LastName</ContactElement>
    <ContactElement id="Person_FirstName">Column_FirstName</ContactElement>
  </ContactMapping>
</AddressProvider>

Structure

• Debug true if additional information should be logged.
• Title Title of the address provider (will be shown like this in Tab).
• Icon Image for icon (as Base64 string; recommended: PNG, 32x32 pixels)
• SafeQuery Inserts a '@' in front of each parameter in the query (true only possible for System.Data.SqlClient), should always be set to true to prevent SQL injection attacks, but this is not valid for all database systems.
• ConnectionString database ConnectionString
• ConnectionProvider
  • System.Data.Odbc
  • System.Data.OleDb
  • System.Data.OracleClient
  • System.Data.SqlClient
• ContactMapping See XML-Kontakt-Mapping.

Query

The query in the correct syntax and language for the chosen provider: it is important to note that there are significant differences between SQL dialects.

Example for Transact-SQL:

SELECT 
    [Column1] AS OneOffixxFieldname1, 
    [Column2] AS OneOffixxFieldname2, 
FROM [TABLENAME]
WHERE
    UCase([Column1]) Like UCase('%{searchParam1}%') AND
    UCase([Column2]) Like UCase('%{searchParam2}%')

Example for Oracle SQL:

SELECT 
    Column1 AS "OneOffixxFieldname1",
    Column2 AS "OneOffixxFieldname2"
FROM TABLENAME
WHERE
    UPPER(Column1) Like UPPER('%{searchParam1}%') AND
    UPPER(Column2) Like UPPER('%{searchParam2}%') 

Search parameters

The list of search parameters is necessary for the input mask. If no search parameter list is specified, search parameters are generated automatically. A maximum of two controls can be placed on one line.

<SearchParameter Name="company" Label="Firma" Type="String" Length="100" Sort="1" />

• Name Unique field name. Can be used in the query in the format {XXX}.
• Label Display text in dialog in front of control
• Type Type of the control (Possible values are String, Long, Boolean, Date)