Detecting static and prepared statements about datasource-proxy HOT 8 CLOSED

Hi @shahamit

Is this about detecting query execution between java.sql.Statement vs java.sql.PreparedStatement?
If so, now(v1.3) ExecutionInfo contains statementType which indicates whether it was an invocation of statement, prepared statement, or callable statement.
I think that can be used to update your statistics based on type.
If you use the query count statistics, the QueryCount also contains number of queries by type(statement, prepared, callable).

Or, is it something specific to database(oracle)? Retrievable from JDBC API or driver(db vender) specific API?

Regards,

from datasource-proxy.

No, it won't be a check between between java.sql.Statement vs java.sql.PreparedStatement. In the example that I mentioned earlier

select emp_name from employee where emp_id = 24 and emp_type = ?

This would be a PreparedStatement but the emp_id parameter is hard coded. So its not a PreparedStatement practically.

from datasource-proxy.

I’m not sure about the definition of the static statement.
So, when there is a hardcoded parameter(“key=value”) in where clause, it is a static statement regardless of executed as PreparedStatement??

sample1:

Statement stat = conn.createStatement();
stat.execute("SELECT * FROM employee WHERE id = 100”);

• static statement since it’s Statement

sample2:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM employee WHERE id = 100 and type=’sales'”);
ps.execute();

• static statement because there is no parameters even though it is PreparedStatement ??

sample3:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM employee WHERE id = 100 and type=?”);
ps.setString(1, “sales”);
ps.execute();

• static statement because there is “id=100” even though PreparedStatement and type=? ??

sample4:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM employee WHERE id = ? and type=?”);
ps.setInt(1, 100);
ps.setString(1, “sales”);
ps.execute();

• not static statement

Is my assumption correct??

from datasource-proxy.

Yes, your assumption is right. Is it possible to distinguish between these static and prepared statements in the code?

from datasource-proxy.

It's kinda workaround check but how about this?

private boolean isStaticStatement(ExecutionInfo execInfo, String query) {
    StatementType statType = execInfo.getStatementType();
    if (StatementType.STATEMENT.equals(statType)) {
        return true;
    } else if (StatementType.PREPARED.equals(statType) && !query.contains("?")) {
        return true;
    }
    return false;
}

from datasource-proxy.

This wouldn't catch "sample 3" from your initial reply which is the main problem. It would mark sample 3 as a Prepared Statement when it is not completely a Prepared Statement

from datasource-proxy.

ah, you're right.
hmm, then i think it needs to parse the query to find hardcoded "key=value" unless oracle jdbc driver has a way to say it's a static query or not...

from datasource-proxy.

Since the detection of static statement requires analyzing the query, datasource-proxy can be a starting point to trigger such logic, but is not planning to have the logic itself in datasource-proxy.

Thanks for raising the issue, now I'm closing this.

from datasource-proxy.