Search
Generic filters
Exact matches only

Connecting to SQL Databases

Note: The native SQL interface type is no longer available. Why? Well, a) nobody really needed it, and b) the ODBC interface is great and covers everything the native one did (and a bit more). We’ve included a few details here for those users that still have existing native SQL interfaces in use.

Connecting to relational databases usually requires:

  • ODBC drivers installed on Visual KPI Server 
  • Database client tools installed on Visual KPI Server 
  • A technical resource that can test the connection from Visual KPI Server to the database, using the client tools, and has the ability to query the database

See Prerequisites and Downloads for links to the vendor drivers and other required software.

Parameterizing SQL Interfaces

A parameterized query, or prepared statement, is a pre-compiled query in which placeholders or variables are used for parameters. The parameter values are supplied at execution time.

The most important reason for using parameterized queries is to avoid SQL injection attacks. A concatenated SQL query is more vulnerable because malicious code can be inserted into user-input variables that are then executed by the dynamic SQL command. It is better to separate the user input data command from other operations.

Parameterized SQL has the following advantages:

  • Fewer string concatenations
  • No need to worry about any kind of manual string escaping
  • A more generic query form is presented to the database, so it’s likely already hashed and stored as a pre-compiled table
  • Smaller strings are sent as requests since the parsing and optimizing the access is done once for each distinct query type
  • Encourages execution plan reuse (stored procedures) for complex queries

Using Stored Procedures (versus queries)

A stored procedure is prepared SQL code that can be reused. You can write the SQL query that you want to reuse many times, save it as a stored procedure, and then just call the stored procedure to run the SQL code as your query.

You can also pass parameters to the stored procedure. So, you can have a more generic stored procedure with parameters, and then your call will pass parameters for more specific queries.

Using Parameterized Stored Procedures

Your parametrized stored query can use parameters to dynamically generate a result set. It is a good idea to parametrize a stored query when you plan to reuse it for complex queries and to provide some protection against SQL injection attacks.

Was this article helpful?

Related Articles