Oracle ojdbc驱动程序未记录SQL查询参数

huangapple go评论76阅读模式
英文:

Oracle ojdbc drivers not logging SQL query parameters

问题

我已经正确配置了我的应用程序以记录SQL查询,但参数值未显示,而是显示问号(?)。

我在StackOverflow上查看了很多问题,以及官方文档,但都没有效果。

我的应用程序在OpenLiberty服务器上运行。

驱动程序:ojdbc6_g.jar

我的配置:

com.ibm.ws.logging.trace.specification=*=audit:oracle=CONFIG
oracle.jdbc.Trace=true

# 设置日志级别
.level=SEVERE
oracle.level=INFO
oracle.jdbc.driver.level=CONFIG
oracle.sql.level=CONFIG
# 配置处理程序
oracle.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=CONFIG
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

是否有任何方法可以在不使用第三方库或修改应用程序代码的情况下查看SQL参数?

英文:

I've configured my application properly to log SQL queries but the parameter values are not shown but question mark instead (?).

I followed a lot of questions in StackOverflow as well as official documentation but nothing worked.

My application runs on OpenLiberty server.

Driver: ojdbc6_g.jar

My configuration:

com.ibm.ws.logging.trace.specification=*=audit:oracle=CONFIG
oracle.jdbc.Trace=true

# set levels 
.level=SEVERE
oracle.level=INFO
oracle.jdbc.driver.level=CONFIG
oracle.sql.level=CONFIG
# configure handlers
oracle.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=CONFIG
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

Is there any way to see the SQL parameters whitout using third party libraries nor modifying application's code?

答案1

得分: 1

> 是否有任何方法可以在不使用第三方库或修改应用程序代码的情况下查看SQL参数?

必须使用oracle.jdbc.driver=FINE修改您的logging.properties,因为这正是记录参数的确切记录器名称

> 我已经正确配置了我的应用程序以记录SQL查询,但参数值没有显示,而是显示问号(?)。

Oracle驱动程序的编码方式是将参数记录为单独的日志语句。从您的问题中,似乎您试图解决的问题是由Oracle驱动程序创建多个用于参数的日志记录以及一个仅包含绑定的查询本身的日志记录引起的格式问题。由于您遇到了格式问题,因此日志记录框架要求您创建自定义格式化程序,将日志记录转换为您想要查看的特定输出格式。

您需要创建的格式化程序的唯一新颖之处在于,您需要将多个日志记录合并为一个漂亮格式的消息。

以下是一个示例,供您参考:

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.util.ArrayDeque;
import java.util.logging.Formatter;
import java.util.logging.LogRecord;
import java.util.logging.SimpleFormatter;

public class CoalesceFormatter extends Formatter {

    private final ArrayDeque<LogRecord> records = new ArrayDeque<>();
    
    private final Formatter target = new SimpleFormatter();
    
    @Override
    public synchronized String format(LogRecord record) {
        if (records.isEmpty()) {
            if (!isStart(record)) {
                return target.format(record);
            } else {
                records.add(record);
                return ""; //suppress output
            }
        }
        
        if (isEnd(record)) {
            LogRecord first = records.poll();
            String query = this.formatMessage(first);
            for (LogRecord parameter;  (parameter = records.poll()) != null;) {
                query = query.replaceFirst("\\?", this.formatMessage(parameter));
            }
            
            LogRecord clone = clone(first);
            clone.setMessage(query);
            clone.setParameters(null);
            assert records.isEmpty() : records;
            return this.target.format(clone);
        }
        
        if (isDelayed(record)) {
            records.add(record);
            return ""; //suppress output
        }
        
        //Normal record
        return target.format(record);
    }
    
    private boolean isStart(LogRecord r) {
        //TODO: this is the call site of preparing the query. 
        return "prepareStatement".equals(r.getSourceMethodName());
    }
    
    private boolean isDelayed(LogRecord r) {
        //TODO: this needs more filtering
        return "setParameter".equals(r.getSourceMethodName());
    }
    
    private boolean isEnd(LogRecord r) {
        //TODO: fix as needed
        //valueOf deals with null
        return String.valueOf(r.getSourceMethodName()).startsWith("execute");
    }
    
    private LogRecord clone(LogRecord r) {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try (ObjectOutputStream oos = new ObjectOutputStream(out)) {
            oos.writeObject(r);
        } catch(IOException ignore) {
        }
        
        try (ByteArrayInputStream in = new ByteArrayInputStream(out.toByteArray());
                ObjectInputStream oos = new ObjectInputStream(in)) {
            return (LogRecord) oos.readObject(); 
        } catch(IOException | ClassNotFoundException ignore) {
        }
        return r;
    }
}
英文:

> Is there any way to see the SQL parameters whitout using third party libraries nor modifying application's code?

You have to modify your logging.properties with oracle.jdbc.driver=FINE as that is the exact logger name that records the parameters.

> I've configured my application properly to log SQL queries but the parameter values are not shown but question mark instead (?).

The Oracle driver was coded to log the parameters as individual log statements. From your question it appears the problem you are trying to solve is a formatting issue caused by the Oracle driver creating multiple log records for the parameters and one for the query itself which just contains the binds. Since you have a formatting issue then the logging framework requires you to create a custom formatter to convert the log records to a specific output format you would like to see.

The only thing novel about the formatter you have to create is that you need to coalesce multiple log records into one nicely formatted message.

Here is an example to get you started:

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.util.ArrayDeque;
import java.util.logging.Formatter;
import java.util.logging.LogRecord;
import java.util.logging.SimpleFormatter;
public class CoalesceFormatter extends Formatter {
private final ArrayDeque<LogRecord> records = new ArrayDeque<>();
private final Formatter target = new SimpleFormatter();
@Override
public synchronized String format(LogRecord record) {
if (records.isEmpty()) {
if (!isStart(record)) {
return target.format(record);
} else {
records.add(record);
return ""; //suppress output
}
}
if (isEnd(record)) {
LogRecord first = records.poll();
String query = this.formatMessage(first);
for (LogRecord parameter;  (parameter = records.poll()) != null;) {
query = query.replaceFirst("\\?", this.formatMessage(parameter));
}
LogRecord clone = clone(first);
clone.setMessage(query);
clone.setParameters(null);
assert records.isEmpty() : records;
return this.target.format(clone);
}
if (isDelayed(record)) {
records.add(record);
return ""; //suppress output
}
//Normal record
return target.format(record);
}
private boolean isStart(LogRecord r) {
//TODO: this is the call site of preparing the query. 
return "prepareStatement".equals(r.getSourceMethodName());
}
private boolean isDelayed(LogRecord r) {
//TODO: this needs more filtering
return "setParameter".equals(r.getSourceMethodName());
}
private boolean isEnd(LogRecord r) {
//TODO: fix as needed
//valueOf deals with null
return String.valueOf(r.getSourceMethodName()).startsWith("execute");
}
private LogRecord clone(LogRecord r) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
try (ObjectOutputStream oos = new ObjectOutputStream(out)) {
oos.writeObject(r);
} catch(IOException ignore) {
}
try (ByteArrayInputStream in = new ByteArrayInputStream(out.toByteArray());
ObjectInputStream oos = new ObjectInputStream(in)) {
return (LogRecord) oos.readObject(); 
} catch(IOException | ClassNotFoundException ignore) {
}
return r;
}
}

huangapple
  • 本文由 发表于 2020年8月25日 15:56:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/63574404.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定