private static OdbcParameter[] DeriveParametersFromStoredProcedure(OdbcConnection connection, OdbcCommand command) { List <OdbcParameter> list = new List <OdbcParameter>(); CMDWrapper statementHandle = command.GetStatementHandle(); OdbcStatementHandle hrHandle = statementHandle.StatementHandle; string leftQuote = connection.QuoteChar("DeriveParameters"); string[] strArray = MultipartIdentifier.ParseMultipartIdentifier(command.CommandText, leftQuote, leftQuote, '.', 4, true, "ODBC_ODBCCommandText", false); if (strArray[3] == null) { strArray[3] = command.CommandText; } ODBC32.RetCode retcode = hrHandle.ProcedureColumns(strArray[1], strArray[2], strArray[3], null); if (retcode != ODBC32.RetCode.SUCCESS) { connection.HandleError(hrHandle, retcode); } using (OdbcDataReader reader = new OdbcDataReader(command, statementHandle, CommandBehavior.Default)) { reader.FirstResult(); int fieldCount = reader.FieldCount; while (reader.Read()) { OdbcParameter item = new OdbcParameter { ParameterName = reader.GetString(3) }; switch (reader.GetInt16(4)) { case 1: item.Direction = ParameterDirection.Input; break; case 2: item.Direction = ParameterDirection.InputOutput; break; case 4: item.Direction = ParameterDirection.Output; break; case 5: item.Direction = ParameterDirection.ReturnValue; break; } item.OdbcType = TypeMap.FromSqlType((ODBC32.SQL_TYPE)reader.GetInt16(5))._odbcType; item.Size = reader.GetInt32(7); switch (item.OdbcType) { case OdbcType.Decimal: case OdbcType.Numeric: item.ScaleInternal = (byte)reader.GetInt16(9); item.PrecisionInternal = (byte)reader.GetInt16(10); break; } list.Add(item); } } retcode = hrHandle.CloseCursor(); return(list.ToArray()); }
// DeriveParametersFromStoredProcedure ( // OdbcConnection connection, // OdbcCommand command); // // Uses SQLProcedureColumns to create an array of OdbcParameters // static private OdbcParameter[] DeriveParametersFromStoredProcedure(OdbcConnection connection, OdbcCommand command) { List <OdbcParameter> rParams = new List <OdbcParameter>(); // following call ensures that the command has a statement handle allocated CMDWrapper cmdWrapper = command.GetStatementHandle(); OdbcStatementHandle hstmt = cmdWrapper.StatementHandle; int cColsAffected; // maps an enforced 4-part qualified string as follows // parts[0] = null - ignored but removal would be a run-time breaking change from V1.0 // parts[1] = CatalogName (optional, may be null) // parts[2] = SchemaName (optional, may be null) // parts[3] = ProcedureName // string quote = connection.QuoteChar(ADP.DeriveParameters); string[] parts = MultipartIdentifier.ParseMultipartIdentifier(command.CommandText, quote, quote, '.', 4, true, Res.ODBC_ODBCCommandText, false); if (null == parts[3]) { // match Everett behavior, if the commandtext is nothing but whitespace set the command text to the whitespace parts[3] = command.CommandText; } // note: native odbc appears to ignore all but the procedure name ODBC32.RetCode retcode = hstmt.ProcedureColumns(parts[1], parts[2], parts[3], null); // Note: the driver does not return an error if the given stored procedure does not exist // therefore we cannot handle that case and just return not parameters. if (ODBC32.RetCode.SUCCESS != retcode) { connection.HandleError(hstmt, retcode); } using (OdbcDataReader reader = new OdbcDataReader(command, cmdWrapper, CommandBehavior.Default)) { reader.FirstResult(); cColsAffected = reader.FieldCount; // go through the returned rows and filter out relevant parameter data // while (reader.Read()) { // devnote: column types are specified in the ODBC Programmer's Reference // COLUMN_TYPE Smallint 16bit // COLUMN_SIZE Integer 32bit // DECIMAL_DIGITS Smallint 16bit // NUM_PREC_RADIX Smallint 16bit OdbcParameter parameter = new OdbcParameter(); parameter.ParameterName = reader.GetString(ODBC32.COLUMN_NAME - 1); switch ((ODBC32.SQL_PARAM)reader.GetInt16(ODBC32.COLUMN_TYPE - 1)) { case ODBC32.SQL_PARAM.INPUT: parameter.Direction = ParameterDirection.Input; break; case ODBC32.SQL_PARAM.OUTPUT: parameter.Direction = ParameterDirection.Output; break; case ODBC32.SQL_PARAM.INPUT_OUTPUT: parameter.Direction = ParameterDirection.InputOutput; break; case ODBC32.SQL_PARAM.RETURN_VALUE: parameter.Direction = ParameterDirection.ReturnValue; break; default: Debug.Assert(false, "Unexpected Parametertype while DeriveParamters"); break; } parameter.OdbcType = TypeMap.FromSqlType((ODBC32.SQL_TYPE)reader.GetInt16(ODBC32.DATA_TYPE - 1))._odbcType; parameter.Size = (int)reader.GetInt32(ODBC32.COLUMN_SIZE - 1); switch (parameter.OdbcType) { case OdbcType.Decimal: case OdbcType.Numeric: parameter.ScaleInternal = (Byte)reader.GetInt16(ODBC32.DECIMAL_DIGITS - 1); parameter.PrecisionInternal = (Byte)reader.GetInt16(ODBC32.NUM_PREC_RADIX - 1); break; } rParams.Add(parameter); } } retcode = hstmt.CloseCursor(); return(rParams.ToArray());; }
private OdbcDataReader ExecuteReaderObject(CommandBehavior behavior, string method, bool needReader, object[] methodArguments, ODBC32.SQL_API odbcApiMethod) { // MDAC 68324 OdbcDataReader localReader = null; try { DisposeDeadDataReader(); // this is a no-op if cmdState is not Fetching ValidateConnectionAndTransaction(method); // cmdState will change to Executing if (0 != (CommandBehavior.SingleRow & behavior)) { // CommandBehavior.SingleRow implies CommandBehavior.SingleResult behavior |= CommandBehavior.SingleResult; } ODBC32.RetCode retcode; OdbcStatementHandle stmt = GetStatementHandle().StatementHandle; _cmdWrapper.Canceling = false; if (null != _weakDataReaderReference) { if (_weakDataReaderReference.IsAlive) { object target = _weakDataReaderReference.Target; if (null != target && _weakDataReaderReference.IsAlive) { if (!((OdbcDataReader)target).IsClosed) { throw ADP.OpenReaderExists(); // MDAC 66411 } } } } localReader = new OdbcDataReader(this, _cmdWrapper, behavior); //Set command properties //Not all drivers support timeout. So fail silently if error if (!Connection.ProviderInfo.NoQueryTimeout) { TrySetStatementAttribute(stmt, ODBC32.SQL_ATTR.QUERY_TIMEOUT, (IntPtr)this.CommandTimeout); } // todo: If we remember the state we can omit a lot of SQLSetStmtAttrW calls ... // if we do not create a reader we do not even need to do that if (needReader) { if (Connection.IsV3Driver) { if (!Connection.ProviderInfo.NoSqlSoptSSNoBrowseTable && !Connection.ProviderInfo.NoSqlSoptSSHiddenColumns) { // Need to get the metadata information //SQLServer actually requires browse info turned on ahead of time... //Note: We ignore any failures, since this is SQLServer specific //We won't specialcase for SQL Server but at least for non-V3 drivers if (localReader.IsBehavior(CommandBehavior.KeyInfo)) { if (!_cmdWrapper._ssKeyInfoModeOn) { TrySetStatementAttribute(stmt, (ODBC32.SQL_ATTR)ODBC32.SQL_SOPT_SS.NOBROWSETABLE, (IntPtr)ODBC32.SQL_NB.ON); TrySetStatementAttribute(stmt, (ODBC32.SQL_ATTR)ODBC32.SQL_SOPT_SS.HIDDEN_COLUMNS, (IntPtr)ODBC32.SQL_HC.ON); _cmdWrapper._ssKeyInfoModeOff = false; _cmdWrapper._ssKeyInfoModeOn = true; } } else { if (!_cmdWrapper._ssKeyInfoModeOff) { TrySetStatementAttribute(stmt, (ODBC32.SQL_ATTR)ODBC32.SQL_SOPT_SS.NOBROWSETABLE, (IntPtr)ODBC32.SQL_NB.OFF); TrySetStatementAttribute(stmt, (ODBC32.SQL_ATTR)ODBC32.SQL_SOPT_SS.HIDDEN_COLUMNS, (IntPtr)ODBC32.SQL_HC.OFF); _cmdWrapper._ssKeyInfoModeOff = true; _cmdWrapper._ssKeyInfoModeOn = false; } } } } } if (localReader.IsBehavior(CommandBehavior.KeyInfo) || localReader.IsBehavior(CommandBehavior.SchemaOnly)) { retcode = stmt.Prepare(CommandText); if (ODBC32.RetCode.SUCCESS != retcode) { _connection.HandleError(stmt, retcode); } } bool mustRelease = false; CNativeBuffer parameterBuffer = _cmdWrapper._nativeParameterBuffer; RuntimeHelpers.PrepareConstrainedRegions(); try { //Handle Parameters //Note: We use the internal variable as to not instante a new object collection, //for the common case of using no parameters. if ((null != _parameterCollection) && (0 < _parameterCollection.Count)) { int parameterBufferSize = _parameterCollection.CalcParameterBufferSize(this); if (null == parameterBuffer || parameterBuffer.Length < parameterBufferSize) { if (null != parameterBuffer) { parameterBuffer.Dispose(); } parameterBuffer = new CNativeBuffer(parameterBufferSize); _cmdWrapper._nativeParameterBuffer = parameterBuffer; } else { parameterBuffer.ZeroMemory(); } parameterBuffer.DangerousAddRef(ref mustRelease); _parameterCollection.Bind(this, _cmdWrapper, parameterBuffer); } if (!localReader.IsBehavior(CommandBehavior.SchemaOnly)) { // Can't get the KeyInfo after command execution (SQL Server only since it does not support multiple // results on the same connection). Stored procedures (SP) do not return metadata before actual execution // Need to check the column count since the command type may not be set to SP for a SP. if ((localReader.IsBehavior(CommandBehavior.KeyInfo) || localReader.IsBehavior(CommandBehavior.SchemaOnly)) && (CommandType != CommandType.StoredProcedure)) { short cColsAffected; retcode = stmt.NumberOfResultColumns(out cColsAffected); if (retcode == ODBC32.RetCode.SUCCESS || retcode == ODBC32.RetCode.SUCCESS_WITH_INFO) { if (cColsAffected > 0) { localReader.GetSchemaTable(); } } else if (retcode == ODBC32.RetCode.NO_DATA) { // do nothing } else { // any other returncode indicates an error _connection.HandleError(stmt, retcode); } } switch (odbcApiMethod) { case ODBC32.SQL_API.SQLEXECDIRECT: if (localReader.IsBehavior(CommandBehavior.KeyInfo) || _isPrepared) { //Already prepared, so use SQLExecute retcode = stmt.Execute(); // Build metadata here // localReader.GetSchemaTable(); } else { #if DEBUG //if (AdapterSwitches.OleDbTrace.TraceInfo) { // ADP.DebugWriteLine("SQLExecDirectW: " + CommandText); //} #endif //SQLExecDirect retcode = stmt.ExecuteDirect(CommandText); } break; case ODBC32.SQL_API.SQLTABLES: retcode = stmt.Tables((string)methodArguments[0], //TableCatalog (string)methodArguments[1], //TableSchema, (string)methodArguments[2], //TableName (string)methodArguments[3]); //TableType break; case ODBC32.SQL_API.SQLCOLUMNS: retcode = stmt.Columns((string)methodArguments[0], //TableCatalog (string)methodArguments[1], //TableSchema (string)methodArguments[2], //TableName (string)methodArguments[3]); //ColumnName break; case ODBC32.SQL_API.SQLPROCEDURES: retcode = stmt.Procedures((string)methodArguments[0], //ProcedureCatalog (string)methodArguments[1], //ProcedureSchema (string)methodArguments[2]); //procedureName break; case ODBC32.SQL_API.SQLPROCEDURECOLUMNS: retcode = stmt.ProcedureColumns((string)methodArguments[0], //ProcedureCatalog (string)methodArguments[1], //ProcedureSchema (string)methodArguments[2], //procedureName (string)methodArguments[3]); //columnName break; case ODBC32.SQL_API.SQLSTATISTICS: retcode = stmt.Statistics((string)methodArguments[0], //TableCatalog (string)methodArguments[1], //TableSchema (string)methodArguments[2], //TableName (short)methodArguments[3], //IndexTrpe (short)methodArguments[4]); //Accuracy break; case ODBC32.SQL_API.SQLGETTYPEINFO: retcode = stmt.GetTypeInfo((short)methodArguments[0]); //SQL Type break; default: // this should NEVER happen Debug.Fail("ExecuteReaderObjectcalled with unsupported ODBC API method."); throw ADP.InvalidOperation(method.ToString()); } //Note: Execute will return NO_DATA for Update/Delete non-row returning queries if ((ODBC32.RetCode.SUCCESS != retcode) && (ODBC32.RetCode.NO_DATA != retcode)) { _connection.HandleError(stmt, retcode); } } // end SchemaOnly } finally { if (mustRelease) { parameterBuffer.DangerousRelease(); } } _weakDataReaderReference = new WeakReference(localReader); // XXXCommand.Execute should position reader on first row returning result // any exceptions in the initial non-row returning results should be thrown // from ExecuteXXX not the DataReader if (!localReader.IsBehavior(CommandBehavior.SchemaOnly)) { localReader.FirstResult(); } _cmdState = ConnectionState.Fetching; } finally { if (ConnectionState.Fetching != _cmdState) { if (null != localReader) { // clear bindings so we don't grab output parameters on a failed execute if (null != _parameterCollection) { _parameterCollection.ClearBindings(); } ((IDisposable)localReader).Dispose(); } if (ConnectionState.Closed != _cmdState) { _cmdState = ConnectionState.Closed; } } } return(localReader); }
private OdbcDataReader ExecuteReaderObject(CommandBehavior behavior, string method, bool needReader, object[] methodArguments, ODBC32.SQL_API odbcApiMethod) { OdbcDataReader target = null; try { ODBC32.RetCode typeInfo; this.DisposeDeadDataReader(); this.ValidateConnectionAndTransaction(method); if ((CommandBehavior.SingleRow & behavior) != CommandBehavior.Default) { behavior |= CommandBehavior.SingleResult; } OdbcStatementHandle statementHandle = this.GetStatementHandle().StatementHandle; this._cmdWrapper.Canceling = false; if ((this.weakDataReaderReference != null) && this.weakDataReaderReference.IsAlive) { object obj2 = this.weakDataReaderReference.Target; if (((obj2 != null) && this.weakDataReaderReference.IsAlive) && !((OdbcDataReader)obj2).IsClosed) { throw ADP.OpenReaderExists(); } } target = new OdbcDataReader(this, this._cmdWrapper, behavior); if (!this.Connection.ProviderInfo.NoQueryTimeout) { this.TrySetStatementAttribute(statementHandle, ODBC32.SQL_ATTR.QUERY_TIMEOUT, (IntPtr)this.CommandTimeout); } if ((needReader && this.Connection.IsV3Driver) && (!this.Connection.ProviderInfo.NoSqlSoptSSNoBrowseTable && !this.Connection.ProviderInfo.NoSqlSoptSSHiddenColumns)) { if (target.IsBehavior(CommandBehavior.KeyInfo)) { if (!this._cmdWrapper._ssKeyInfoModeOn) { this.TrySetStatementAttribute(statementHandle, (ODBC32.SQL_ATTR) 0x4cc, (IntPtr)1L); this.TrySetStatementAttribute(statementHandle, ODBC32.SQL_ATTR.SQL_COPT_SS_TXN_ISOLATION, (IntPtr)1L); this._cmdWrapper._ssKeyInfoModeOff = false; this._cmdWrapper._ssKeyInfoModeOn = true; } } else if (!this._cmdWrapper._ssKeyInfoModeOff) { this.TrySetStatementAttribute(statementHandle, (ODBC32.SQL_ATTR) 0x4cc, (IntPtr)0L); this.TrySetStatementAttribute(statementHandle, ODBC32.SQL_ATTR.SQL_COPT_SS_TXN_ISOLATION, (IntPtr)0L); this._cmdWrapper._ssKeyInfoModeOff = true; this._cmdWrapper._ssKeyInfoModeOn = false; } } if (target.IsBehavior(CommandBehavior.KeyInfo) || target.IsBehavior(CommandBehavior.SchemaOnly)) { typeInfo = statementHandle.Prepare(this.CommandText); if (typeInfo != ODBC32.RetCode.SUCCESS) { this._connection.HandleError(statementHandle, typeInfo); } } bool success = false; CNativeBuffer parameterBuffer = this._cmdWrapper._nativeParameterBuffer; RuntimeHelpers.PrepareConstrainedRegions(); try { if ((this._parameterCollection != null) && (0 < this._parameterCollection.Count)) { int initialSize = this._parameterCollection.CalcParameterBufferSize(this); if ((parameterBuffer == null) || (parameterBuffer.Length < initialSize)) { if (parameterBuffer != null) { parameterBuffer.Dispose(); } parameterBuffer = new CNativeBuffer(initialSize); this._cmdWrapper._nativeParameterBuffer = parameterBuffer; } else { parameterBuffer.ZeroMemory(); } parameterBuffer.DangerousAddRef(ref success); this._parameterCollection.Bind(this, this._cmdWrapper, parameterBuffer); } if (target.IsBehavior(CommandBehavior.SchemaOnly)) { goto Label_0443; } if ((target.IsBehavior(CommandBehavior.KeyInfo) || target.IsBehavior(CommandBehavior.SchemaOnly)) && (this.CommandType != System.Data.CommandType.StoredProcedure)) { short num2; typeInfo = statementHandle.NumberOfResultColumns(out num2); switch (typeInfo) { case ODBC32.RetCode.SUCCESS: case ODBC32.RetCode.SUCCESS_WITH_INFO: if (num2 > 0) { target.GetSchemaTable(); } goto Label_029A; } if (typeInfo != ODBC32.RetCode.NO_DATA) { this._connection.HandleError(statementHandle, typeInfo); } } Label_029A: switch (odbcApiMethod) { case ODBC32.SQL_API.SQLEXECDIRECT: if (target.IsBehavior(CommandBehavior.KeyInfo) || this._isPrepared) { typeInfo = statementHandle.Execute(); } else { typeInfo = statementHandle.ExecuteDirect(this.CommandText); } break; case ODBC32.SQL_API.SQLCOLUMNS: typeInfo = statementHandle.Columns((string)methodArguments[0], (string)methodArguments[1], (string)methodArguments[2], (string)methodArguments[3]); break; case ODBC32.SQL_API.SQLSTATISTICS: typeInfo = statementHandle.Statistics((string)methodArguments[0], (string)methodArguments[1], (string)methodArguments[2], (short)methodArguments[3], (short)methodArguments[4]); break; case ODBC32.SQL_API.SQLTABLES: typeInfo = statementHandle.Tables((string)methodArguments[0], (string)methodArguments[1], (string)methodArguments[2], (string)methodArguments[3]); break; case ODBC32.SQL_API.SQLGETTYPEINFO: typeInfo = statementHandle.GetTypeInfo((short)methodArguments[0]); break; case ODBC32.SQL_API.SQLPROCEDURECOLUMNS: typeInfo = statementHandle.ProcedureColumns((string)methodArguments[0], (string)methodArguments[1], (string)methodArguments[2], (string)methodArguments[3]); break; case ODBC32.SQL_API.SQLPROCEDURES: typeInfo = statementHandle.Procedures((string)methodArguments[0], (string)methodArguments[1], (string)methodArguments[2]); break; default: throw ADP.InvalidOperation(method.ToString()); } if ((typeInfo != ODBC32.RetCode.SUCCESS) && (ODBC32.RetCode.NO_DATA != typeInfo)) { this._connection.HandleError(statementHandle, typeInfo); } } finally { if (success) { parameterBuffer.DangerousRelease(); } } Label_0443: this.weakDataReaderReference = new WeakReference(target); if (!target.IsBehavior(CommandBehavior.SchemaOnly)) { target.FirstResult(); } this.cmdState = ConnectionState.Fetching; } finally { if (ConnectionState.Fetching != this.cmdState) { if (target != null) { if (this._parameterCollection != null) { this._parameterCollection.ClearBindings(); } target.Dispose(); } if (this.cmdState != ConnectionState.Closed) { this.cmdState = ConnectionState.Closed; } } } return(target); }