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) { ArrayList rParams = new ArrayList(); // following call ensures that the command has a statement handle allocated HandleRef hstmt = command.GetStatementHandle(); int cColsAffected; ODBC32.RETCODE retcode; retcode = (ODBC32.RETCODE)UnsafeNativeMethods.Odbc32.SQLProcedureColumnsW( hstmt, null, 0, null, 0, command.CommandText, (Int16)ODBC32.SQL_NTS, null, 0); // 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, ODBC32.SQL_HANDLE.STMT, retcode); } OdbcDataReader reader = new OdbcDataReader(command, command._cmdWrapper, CommandBehavior.Default); reader.FirstResult(); cColsAffected = reader.FieldCount; // go through the returned rows and filter out relevant parameter data // while (reader.Read()) { 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 = reader.GetInt32(ODBC32.COLUMN_SIZE - 1); switch (parameter.OdbcType) { case OdbcType.Decimal: case OdbcType.Numeric: parameter.Scale = (Byte)reader.GetInt32(ODBC32.DECIMAL_DIGITS - 1); parameter.Precision = (Byte)reader.GetInt32(ODBC32.NUM_PREC_RADIX - 1); break; } rParams.Add(parameter); } retcode = (ODBC32.RETCODE)UnsafeNativeMethods.Odbc32.SQLCloseCursor(hstmt); // Create a new Parameter array and copy over the ArrayList items // OdbcParameter[] pList = new OdbcParameter[rParams.Count]; for (int i = 0; i < rParams.Count; i++) { pList[i] = (OdbcParameter)rParams[i]; } return(pList); }
// 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());; }
// 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 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(); }