Generic SQL Provider

{"type":"doc","content":[{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{},"macroMetadata":{"macroId":{"value":"5bc67a2b-9fc3-435b-9660-4c8aaf8f0dc2"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"4db8c99a-b928-41f8-a78e-463aae0e460b"}},{"type":"rule"},{"type":"paragraph","content":[{"text":"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 ","type":"text"},{"text":"SearchParameter","type":"text","marks":[{"type":"strong"}]},{"text":" element. The value of a parameter can be called in the query with {","type":"text"},{"text":"ParameterName","type":"text","marks":[{"type":"em"}]},{"text":"}.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n false\n Generic SQL Provider\n {Base64 Image}\n false\n {ConnectionString}\n System.Data.OleDb\n \n \n SELECT \n [SPALTENNAME] AS {field name primedocs} \n FROM [TABLE]\n WHERE\n UCase([FirstName]) Like UCase('%{firstName}%') AND\n UCase([LastName]) Like UCase('%{lastName}%') AND\n UCase([Street] & ' ' & [Housenr]) Like UCase('%{street}%') AND\n [Zip] Like '%{plz}%' AND\n UCase([City]) Like UCase('%{city}%')\n \n \n \n \n \n \n \n \n \n \n \n Contact\n Column_LastName\n Column_FirstName\n \n","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":1},"content":[{"text":"Structure","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Debug","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"true","type":"text","marks":[{"type":"code"}]},{"text":" if additional information should be logged.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Title","type":"text","marks":[{"type":"strong"}]},{"text":" Title of the address provider (will be shown like this in Tab).","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Icon","type":"text","marks":[{"type":"strong"}]},{"text":" Image for icon ","type":"text"},{"text":"(as Base64 string; recommended: PNG, 32x32 pixels)","type":"text","marks":[{"type":"em"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"SafeQuery","type":"text","marks":[{"type":"strong"}]},{"text":" Inserts a '@' in front of each parameter in the query (","type":"text"},{"text":"true","type":"text","marks":[{"type":"em"}]},{"text":" only possible for ","type":"text"},{"text":"System.Data.SqlClient","type":"text","marks":[{"type":"em"}]},{"text":"), should always be set to ","type":"text"},{"text":"true","type":"text","marks":[{"type":"code"}]},{"text":" to prevent SQL injection attacks, but this is not valid for all database systems.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"ConnectionString","type":"text","marks":[{"type":"strong"}]},{"text":" database ","type":"text"},{"text":"ConnectionString","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.connectionstrings.com/"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"ConnectionProvider","type":"text","marks":[{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"System.Data.Odbc","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"System.Data.OleDb","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"System.Data.OracleClient","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"System.Data.SqlClient","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"ContactMapping","type":"text","marks":[{"type":"strong"}]},{"text":" See ","type":"text"},{"text":"XML contact mapping","type":"text","marks":[{"type":"strong"},{"type":"link","attrs":{"href":"/wiki/spaces/PDTEN/pages/61408087"}}]},{"text":".","type":"text"}]}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":1},"content":[{"text":"Query","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Example for Transact-SQL:","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT \n [Column1] AS OneOffixxFieldname1, \n [Column2] AS OneOffixxFieldname2, \nFROM [TABLENAME]\nWHERE\n UCase([Column1]) Like UCase('%{searchParam1}%') AND\n UCase([Column2]) Like UCase('%{searchParam2}%')","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Example for Oracle SQL:","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT \n Column1 AS \"OneOffixxFieldname1\",\n Column2 AS \"OneOffixxFieldname2\"\nFROM TABLENAME\nWHERE\n UPPER(Column1) Like UPPER('%{searchParam1}%') AND\n UPPER(Column2) Like UPPER('%{searchParam2}%') ","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":1},"content":[{"text":"Search parameters","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name","type":"text","marks":[{"type":"strong"}]},{"text":" Unique field name. Can be used in the query in the format {XXX}.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Label","type":"text","marks":[{"type":"strong"}]},{"text":" Display text in dialog in front of control","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Type","type":"text","marks":[{"type":"strong"}]},{"text":" Type of the control (Possible values are String, Long, Boolean, Date)","type":"text"}]}]}]}],"version":1}

Browser not supported