// 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 "?"; }