{"type":"doc","content":[{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{},"macroMetadata":{"macroId":{"value":"4f4e0431-4c49-4d0c-b498-c8aff67d8ee8"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"d5b4efeb-00fb-45a0-8f94-8b8c1a5d236f"}},{"type":"rule"},{"type":"panel","attrs":{"panelIconId":"1f51c","panelIcon":":soon:","panelIconText":"🔜","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"SqlDataProvider","type":"text","marks":[{"type":"code"}]},{"text":" will be available in the next primedocs version.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Via the SqlData provider arbitrarily configurable SQL databases can be accessed.","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Configuration","type":"text"}]},{"type":"paragraph","content":[{"text":"In addition to the ","type":"text"},{"text":"Mapping","type":"text","marks":[{"type":"code"}]},{"text":" and ","type":"text"},{"text":"SearchParameters","type":"text","marks":[{"type":"code"}]},{"text":" configuration, which all ","type":"text"},{"text":"DataProvider","type":"text","marks":[{"type":"code"}]},{"text":"s of the ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://primesoft-group.atlassian.net/wiki/spaces/PDTEN/pages/71532545"}},{"text":" require, the ","type":"text"},{"text":"SqlDataProvider","type":"text","marks":[{"type":"code"}]},{"text":" can be configured via the ","type":"text"},{"text":"Option","type":"text","marks":[{"type":"code"}]},{"text":" element:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n \n {ConnectionString}\n System.Data.SqlClient\n \n SELECT * FROM Users WHERE FirstName Like '%' + @firstName + '%'\n \n \n ...\n","type":"text"}]},{"type":"paragraph","content":[{"text":"Via ","type":"text"},{"text":"Options ","type":"text","marks":[{"type":"strong"}]},{"text":"the connection and the database query is defined.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"ConnectionString:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"ConnectionString","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.connectionstrings.com/"}}]},{"text":", can be encrypted.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"ConnectionProvider:","type":"text","marks":[{"type":"strong"}]},{"text":" Defines the type of the database - supported types:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"System.Data.Odbc","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"System.Data.SqlClient","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"MySql.Data.MySqlClient","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Oracle.ManagedDataAccess.Client","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Query: ","type":"text","marks":[{"type":"strong"}]},{"text":"Defines the SQL query, here the ","type":"text"},{"text":"SearchParameters","type":"text","marks":[{"type":"code"}]},{"text":" are treated as SQL parameters.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"The SQL query must be in the format for the respective target database. Since all search parameters are treated as SQL parameters by the ","type":"text"},{"text":"SearchParameters","type":"text","marks":[{"type":"code"}]},{"text":" configuration, ","type":"text"},{"text":"the respective SQL parameter syntax of the target database must be followed","type":"text","marks":[{"type":"strong"}]},{"text":".","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Examples","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MS SQL","type":"text","marks":[{"type":"em"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT FirstName, LastName FROM Users\nWHERE FirstName LIKE '%' + @firstName + '%' AND LastName LIKE '%' + @lastName + '%'","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":" ","type":"text"},{"text":"MySQL","type":"text","marks":[{"type":"em"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT FirstName, LastName FROM Users\nWHERE FirstName LIKE Concat('%', @firstName, '%') AND LastName LIKE Concat('%', @lastName, '%')","type":"text"}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#FFEBE6","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"CAUTION","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"For Oracle, Odbc and PostgreSQL the order of the placeholders in the query must match the order of the \"SearchParameters!","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Oracle","type":"text","marks":[{"type":"em"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT FirstName, LastName from Users\nWHERE UPPER(NVL(FirstName, ' ')) Like UPPER('%' || :firstName || '%') AND UPPER(NVL(LastName, ' ')) Like UPPER('%' || :lastName || '%')","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Odbc","type":"text","marks":[{"type":"em"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT FirstName, LastName FROM Users\nWHERE FirstName LIKE '%' + ? + '%' AND LastName LIKE '%' + ? + '%'","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL","type":"text","marks":[{"type":"em"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT FirstName, LastName FROM Users\nWHERE UPPER(FirstName) LIKE UPPER('%' || ? || '%') AND UPPER(LastName) LIKE UPPER('%' || ? || '%')","type":"text"}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#FFEBE6","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"CAUTION","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"The restriction of the order has the consequence that a parameter can be used in the query exactly once.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"The following MS SQL query cannot be translated directly to Oracle or Odbc:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT FirstName, MiddleName, LastName FROM Users\nWHERE FirstName LIKE '%' + firstName + '%' OR MiddleName LIKE '%' + firstName + '%'","type":"text"}]},{"type":"paragraph","content":[{"text":"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:","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Odbc","type":"text","marks":[{"type":"em"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT STRING(FirstName, ' ', MiddleName) AS CompleteName, LastName FROM Users\nWHERE CompleteName LIKE '%' + ? + '%'","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Example","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n \n \n \n \n Data Source=localhost;Initial Catalog=Customers;User Id={user};Password={pwd};\n System.Data.SqlClient\n \n SELECT * FROM Customers WHERE Name Like '%' + @Name + '%'\n \n \n \n \n \n \n \n \n \n","type":"text"}]}],"version":1}

Browser not supported