Skip to main content

How Informer handles Native SQL

Informer prepares, connects, and executes SQL in a way designed for security, efficiency, and compliance with JDBC standards.

SQL string processing​

Before executing a query, Informer preprocesses the SQL string so it complies with JDBC standards:

  • Parameterization: Informer's $input syntax is replaced with JDBC-compliant ? parameters.
  • Default values: inputs with default values are resolved and used as runtime parameters.
  • Custom Field syntax: the ${customField} syntax is replaced to match the expected query format.
  • Parameter collection: the parameters to use in execution are finalized.

Establishing the connection​

Once the SQL is prepared, Informer opens a native connection, attempts to set it to read-only via the JDBC API (to protect data integrity), and configures the transaction isolation level.

Query execution​

  • If parameters exist, Informer uses a PreparedStatement for native parameterization security; if not, it uses a plain Statement.
  • For sample queries, the maximum number of rows is set for performance.
  • The statement runs through the JDBC .executeQuery() method, which requires the driver to return exactly one ResultSet. If it does not, the driver raises an error.

Why it works this way​

Using .executeQuery() reinforces read-only operation and removes driver-specific ambiguity from multi-statement scripts. Native SQL in Informer is intended for single SELECT queries that return a single ResultSet, not multiple statements or full SQL scripts.

When a SQL string contains multiple ResultSet-producing queries, which one is returned depends on the JDBC driver. Vendors rarely document this, but most drivers Informer uses return the last ResultSet generated.

Recommendation​

If you need temporary tables, consider Common Table Expressions (CTEs) instead. They are part of a single SQL statement and behave like inline views. This matters most on SQL Server, where prepared statements and the max-rows setting can otherwise lead to unexpected behavior.