public void TestOutputColumnConversion()
{
_epService.EPAdministrator.Configuration.AddVariable("myvariable", typeof(int), 10);
var fields = new String[] { "myint" };
var stmtText = "@Hook(Type=HookType.SQLCOL, Hook='" + typeof(SupportSQLColumnTypeConversion).FullName + "')" +
"select * from sql:MyDB ['select myint from mytesttable where myint = ${myvariable}']";
var stmt = _epService.EPAdministrator.CreateEPL(stmtText);
_listener = new SupportUpdateListener();
stmt.Events += _listener.Update;
Assert.AreEqual(typeof(bool?), stmt.EventType.GetPropertyType("myint"));
EPAssertionUtil.AssertPropsPerRowAnyOrder(stmt.GetEnumerator(), fields, new Object[][] { new Object[] { false } });
// assert contexts
var type = SupportSQLColumnTypeConversion.TypeContexts[0];
Assert.AreEqual("Integer", type.ColumnSqlType);
Assert.AreEqual("MyDB", type.Db);
Assert.AreEqual("select myint from mytesttable where myint = ${myvariable}", type.Sql);
Assert.AreEqual("myint", type.ColumnName);
Assert.AreEqual(1, type.ColumnNumber);
Assert.AreEqual(typeof(int?), type.ColumnClassType);
SQLColumnValueContext val = SupportSQLColumnTypeConversion.ValueContexts[0];
Assert.AreEqual(10, val.ColumnValue);
Assert.AreEqual("myint", val.ColumnName);
Assert.AreEqual(1, val.ColumnNumber);
_epService.EPRuntime.SetVariableValue("myvariable", 60); // greater 50 turns true
EPAssertionUtil.AssertPropsPerRowAnyOrder(stmt.GetEnumerator(), fields, new Object[][] { new Object[] { true } });
}
//@Hook(type=HookType.SQLCOL, hook="this is a sample and not used")
private void RunAssertionOutputColumnConversion(EPServiceProvider epService)
{
SupportSQLColumnTypeConversion.Reset();
epService.EPAdministrator.Configuration.AddVariable("myvariableOCC", typeof(int), 10);
var fields = new string[] { "myint" };
string stmtText = "@Hook(Type=HookType.SQLCOL, Hook='" + typeof(SupportSQLColumnTypeConversion).FullName + "')" +
"select * from sql:MyDB ['select myint from mytesttable where myint = ${myvariableOCC}']";
EPStatement stmt = epService.EPAdministrator.CreateEPL(stmtText);
var listener = new SupportUpdateListener();
stmt.Events += listener.Update;
Assert.AreEqual(typeof(bool?), stmt.EventType.GetPropertyType("myint"));
EPAssertionUtil.AssertPropsPerRowAnyOrder(stmt.GetEnumerator(), fields, new object[][] { new object[] { false } });
// assert contexts
SQLColumnTypeContext type = SupportSQLColumnTypeConversion.TypeContexts[0];
Assert.AreEqual("System.Int32", type.ColumnSqlType);
Assert.AreEqual("MyDB", type.Db);
Assert.AreEqual("select myint from mytesttable where myint = ${myvariableOCC}", type.Sql);
Assert.AreEqual("myint", type.ColumnName);
Assert.AreEqual(1, type.ColumnNumber);
Assert.AreEqual(typeof(int?), type.ColumnClassType);
SQLColumnValueContext val = SupportSQLColumnTypeConversion.ValueContexts[0];
Assert.AreEqual(10, val.ColumnValue);
Assert.AreEqual("myint", val.ColumnName);
Assert.AreEqual(1, val.ColumnNumber);
epService.EPRuntime.SetVariableValue("myvariableOCC", 60); // greater 50 turns true
EPAssertionUtil.AssertPropsPerRowAnyOrder(stmt.GetEnumerator(), fields, new object[][] { new object[] { true } });
stmt.Dispose();
}
private IList<EventBean> Execute(
DbDriverCommand driverCommand,
object lookupValuePerStream)
{
var hasLogging = _factory.IsEnableLogging && ADO_PERF_LOG.IsInfoEnabled;
using (var myDriverCommand = driverCommand.Clone()) {
var dbCommand = myDriverCommand.Command;
if (ExecutionPathDebugLog.IsDebugEnabled && Log.IsInfoEnabled) {
Log.Info(".execute Executing prepared statement '{0}'", dbCommand.CommandText);
}
DbParameter dbParam;
// set parameters
SQLInputParameterContext inputParameterContext = null;
if (_columnTypeConversionHook != null) {
inputParameterContext = new SQLInputParameterContext();
}
var mk = _factory.InputParameters.Length == 1 ? null : (HashableMultiKey) lookupValuePerStream;
for (var i = 0; i < _factory.InputParameters.Length; i++) {
try {
object parameter;
if (mk == null) {
parameter = lookupValuePerStream;
}
else {
parameter = mk.Keys[i];
}
if (ExecutionPathDebugLog.IsDebugEnabled && Log.IsInfoEnabled) {
Log.Info(
".Execute Setting parameter " +
" to " +
parameter +
" typed " +
((parameter == null) ? "null" : parameter.GetType().Name));
}
if (_columnTypeConversionHook != null) {
inputParameterContext.ParameterNumber = i + 1;
inputParameterContext.ParameterValue = parameter;
parameter = _columnTypeConversionHook.GetParameterValue(inputParameterContext);
}
dbParam = dbCommand.Parameters[i];
dbParam.Value = parameter ?? DBNull.Value;
}
catch (DbException ex) {
throw new EPException("Error setting parameter " + i, ex);
}
}
// execute
try {
// generate events for result set
IList<EventBean> rows = new List<EventBean>();
using (var dataReader = dbCommand.ExecuteReader()) {
try {
SQLColumnValueContext valueContext = null;
if (_columnTypeConversionHook != null) {
valueContext = new SQLColumnValueContext();
}
SQLOutputRowValueContext rowContext = null;
if (_outputRowConversionHook != null) {
rowContext = new SQLOutputRowValueContext();
}
var rowNum = 0;
if (dataReader.HasRows) {
// Determine how many fields we will be receiving
var fieldCount = dataReader.FieldCount;
// Allocate a buffer to hold the results of the row
var rawData = new object[fieldCount];
// Convert the names of columns into ordinal indices and prepare
// them so that we only have to incur this cost when we first notice
// the reader has rows.
if (_dbInfoList == null) {
_dbInfoList = new List<DbInfo>();
foreach (var entry in _outputTypes) {
var dbInfo = new DbInfo();
dbInfo.Name = entry.Key;
dbInfo.Ordinal = dataReader.GetOrdinal(dbInfo.Name);
dbInfo.OutputTypeDesc = entry.Value;
dbInfo.Binding = entry.Value.OptionalBinding;
_dbInfoList.Add(dbInfo);
}
}
var fieldNames = new string[fieldCount];
for (var ii = 0; ii < fieldCount; ii++) {
fieldNames[ii] = dataReader.GetName(ii);
}
// Anyone know if the ordinal will always be the same every time
// the query is executed; if so, we could certainly cache this
// dbInfoList so that we only have to do that once for the lifetime
// of the statement.
while (dataReader.Read()) {
var colNum = 1;
DataMap row = new Dictionary<string, object>();
// Get all of the values for the row in one shot
dataReader.GetValues(rawData);
// Convert the items into raw row objects
foreach (var dbInfo in _dbInfoList) {
var value = rawData[dbInfo.Ordinal];
if (value == DBNull.Value) {
value = null;
}
else if (dbInfo.Binding != null) {
value = dbInfo.Binding.GetValue(value, dbInfo.Name);
}
else if (value.GetType() != dbInfo.OutputTypeDesc.DataType) {
value = Convert.ChangeType(value, dbInfo.OutputTypeDesc.DataType);
}
if (_columnTypeConversionHook != null) {
valueContext.ColumnName = fieldNames[colNum - 1];
valueContext.ColumnNumber = colNum;
valueContext.ColumnValue = value;
value = _columnTypeConversionHook.GetColumnValue(valueContext);
}
row[dbInfo.Name] = value;
colNum++;
}
EventBean eventBeanRow = null;
if (_outputRowConversionHook == null) {
eventBeanRow = _agentInstanceContext.EventBeanTypedEventFactory
.AdapterForTypedMap(
row,
_factory.EventType);
}
else {
rowContext.Values = row;
rowContext.RowNum = rowNum;
var rowData = _outputRowConversionHook.GetOutputRow(rowContext);
if (rowData != null) {
eventBeanRow = _agentInstanceContext.EventBeanTypedEventFactory
.AdapterForTypedObject(
rowData,
(BeanEventType) _factory.EventType);
}
}
if (eventBeanRow != null) {
rows.Add(eventBeanRow);
rowNum++;
}
}
}
}
catch (DbException ex) {
throw new EPException(
"Error reading results for statement '" + _factory.PreparedStatementText + "'",
ex);
}
}
return rows;
}
catch (DbException ex) {
throw new EPException("Error executing statement '" + _factory.PreparedStatementText + "'", ex);
}
}
}
public Object GetColumnValue(SQLColumnValueContext valueContext)
{
valueContexts.Add(valueContext);
return(((int?)valueContext.ColumnValue) >= 50);
}
public object GetColumnValue(SQLColumnValueContext valueContext)
{
ValueContexts.Add(valueContext);
return (int?) valueContext.ColumnValue >= 50;
}
