// Note: There used to be a method here called AsSql(string data) that just put quotes around any data.
// Putting strings directly into SQL queries is very dangerous, and a user uncovered this bug by trying
// to create a custom field with an apostrophe. All string data should be put into the query using
// SetStringParamter.
/// <summary>
/// Used in preparing a string for an SQL command. Returns "null" if the string passed in is null, returns "?"
/// if the string isn't null. The odc handle is used to do a SetStringParameter with the string and index passed in.
/// </summary>
/// <param name="data">string to insert into query</param>
/// <param name="odc">odc handle to use</param>
/// <param name="index">index to use for the SetStringParameter if the string is not null. Will be incremented if the string is not null.</param>
/// <returns></returns>
private string AsSql(string data, IOleDbCommand odc, ref uint index)
{
if (data == null)
{
return("null");
}
odc.SetStringParameter(index, (uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, data, (uint)data.Length);
index++; // Increment the index by one for next time
return("?");
}
/// <summary>
/// Execute the query sql, which contains zero or one string parameter whose value is
/// supplied as param (or null). The result is a single rowset, which may contain zero or
/// more rows, each containing cols columns, each an integer value. Read the entire rowset,
/// returning a List of int[cols] arrays.
/// </summary>
/// <param name="cache"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="cols"></param>
/// <returns></returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
static public List <int[]> ReadIntArray(FdoCache cache, string sql, string param, int cols)
{
List <int[]> resultList = new List <int[]>();
IOleDbCommand odc = null;
try
{
cache.DatabaseAccessor.CreateCommand(out odc);
if (param != null)
{
odc.SetStringParameter(1, // 1-based parameter index
(uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, //flags
param,
(uint)param.Length); // despite doc, impl makes clear this is char count
}
odc.ExecCommand(sql, (int)SqlStmtType.knSqlStmtSelectWithOneRowset);
odc.GetRowset(0);
bool fMoreRows;
odc.NextRow(out fMoreRows);
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
while (fMoreRows)
{
int[] result = new int[cols];
for (int i = 0; i < cols; ++i)
{
bool fIsNull;
uint cbSpaceTaken;
odc.GetColValue((uint)(i + 1), rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (!fIsNull)
{
result[i] = IntFromStartOfUintArrayPtr(rgHvo);
}
}
resultList.Add(result);
odc.NextRow(out fMoreRows);
}
}
}
finally
{
ShutdownODC(ref odc);
}
return(resultList);
}
/// ------------------------------------------------------------------------------------
/// <summary>
/// Internals the read ints from command.
/// </summary>
/// <param name="cache">The cache.</param>
/// <param name="sql">The SQL.</param>
/// <param name="param">The param.</param>
/// <returns></returns>
/// ------------------------------------------------------------------------------------
protected virtual List <int> InternalReadIntsFromCommand(FdoCache cache, string sql, string param)
{
List <int> list = new List <int>();
IOleDbCommand odc = null;
try
{
cache.DatabaseAccessor.CreateCommand(out odc);
if (param != null)
{
odc.SetStringParameter(1, // 1-based parameter index
(uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, //flags
param,
(uint)param.Length); // despite doc, impl makes clear this is char count
}
odc.ExecCommand(sql, (int)SqlStmtType.knSqlStmtSelectWithOneRowset);
odc.GetRowset(0);
bool fMoreRows;
odc.NextRow(out fMoreRows);
bool fIsNull;
uint cbSpaceTaken;
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
while (fMoreRows)
{
odc.GetColValue(1, rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (!fIsNull)
{
list.Add(IntFromStartOfUintArrayPtr(rgHvo));
}
odc.NextRow(out fMoreRows);
}
}
}
finally
{
ShutdownODC(ref odc);
}
return(list);
}
/// ------------------------------------------------------------------------------------
/// <summary>
/// Execute some arbitrary sql, which may take one
/// optional parameter (pass null if no param is needed). It is not expected
/// to return any results.
/// </summary>
/// <param name="cache">The cache.</param>
/// <param name="sql">The SQL.</param>
/// <param name="param">The param.</param>
/// ------------------------------------------------------------------------------------
public static void ExecuteStatementNoResults(FdoCache cache, string sql, string param)
{
IOleDbCommand odc = null;
try
{
cache.DatabaseAccessor.CreateCommand(out odc);
if (param != null)
{
odc.SetStringParameter(1, // 1-based parameter index
(uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, //flags
param,
(uint)param.Length); // despite doc, impl makes clear this is char count
}
odc.ExecCommand(sql, (int)SqlStmtType.knSqlStmtNoResults);
}
finally
{
ShutdownODC(ref odc);
}
}
/// <summary>
/// Execute the query sql, which optionally contains one string parameter whose value is
/// supplied as param. The result is a single rowset, which may contain zero or
/// more rows, each containing a pair of integers. The row set represents multiple
/// sequences. A sequence is defined by consecutive rows with the same value for the first
/// item. Each sequence is entered into the values dictionary, with the column 1 value
/// as the key, and a list of the column 2 values as the value.
/// Rows where either value is null or key is zero will be ignored.
/// </summary>
/// <param name="cache"></param>
/// <param name="sql"></param>
/// <param name="param">May be null, if not required.</param>
/// <param name="values"></param>
/// <returns></returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
static public void LoadDictionaryFromCommand(FdoCache cache, string sql, string param, Dictionary <int, List <int> > values)
{
// As of 11/30/2006, all callers of this method are looking for reference sequence data,
// so this List of ints cannot be a set.
List <int> list = null;
IOleDbCommand odc = null;
try
{
cache.DatabaseAccessor.CreateCommand(out odc);
if (param != null)
{
odc.SetStringParameter(1, // 1-based parameter index
(uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, //flags
param,
(uint)param.Length); // despite doc, impl makes clear this is char count
}
odc.ExecCommand(sql, (int)SqlStmtType.knSqlStmtSelectWithOneRowset);
odc.GetRowset(0);
bool fMoreRows;
odc.NextRow(out fMoreRows);
bool fIsNull;
uint cbSpaceTaken;
int currentKey = 0;
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
for (; fMoreRows; odc.NextRow(out fMoreRows))
{
int key, val;
odc.GetColValue(1, rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (fIsNull)
{
continue;
}
key = IntFromStartOfUintArrayPtr(rgHvo);
if (key == 0)
{
continue;
}
odc.GetColValue(2, rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (fIsNull)
{
continue;
}
val = IntFromStartOfUintArrayPtr(rgHvo);
if (key != currentKey)
{
list = new List <int>();
currentKey = key;
values[currentKey] = list;
}
list.Add(val);
}
}
}
finally
{
ShutdownODC(ref odc);
}
}
// Note: There used to be a method here called AsSql(string data) that just put quotes around any data.
// Putting strings directly into SQL queries is very dangerous, and a user uncovered this bug by trying
// to create a custom field with an apostrophe. All string data should be put into the query using
// SetStringParamter.
/// <summary>
/// Used in preparing a string for an SQL command. Returns "null" if the string passed in is null, returns "?"
/// if the string isn't null. The odc handle is used to do a SetStringParameter with the string and index passed in.
/// </summary>
/// <param name="data">string to insert into query</param>
/// <param name="odc">odc handle to use</param>
/// <param name="index">index to use for the SetStringParameter if the string is not null. Will be incremented if the string is not null.</param>
/// <returns></returns>
private string AsSql(string data, IOleDbCommand odc, ref uint index)
{
if (data == null)
return "null";
odc.SetStringParameter(index, (uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, data, (uint)data.Length);
index++; // Increment the index by one for next time
return "?";
}
