none
OdbcCommand parameters RRS feed

  • Question

  • I am working on an ASP.NET web application project in C# with an Oracle db backend.  Currently our application is using Oracle's ODP.NET since Microsoft's System.Data.OracleClient has been depricated.  We are having compatibility issues with our test and production servers so we are creating an ODBC version of our application.  I am having trouble with CommandText and adding parameters for the OdbcCommand.  Here is what I have for the ODP.NET:

    using (OracleCommand cmd = new OracleCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "oracle_pkg.sproc_name"; cmd.Parameters.Add("param1", txtbox1.Text); cmd.Parameters.Add("param2", txtbox2.Text); cmd.Parameters.Add(new OracleParameter("o_refcursor", OracleDbType.RefCursor, ParameterDirection.Output)); cmd.Connection = UHFUtility.GetDBConnection(); OracleDataAdapter oda = new OracleDataAdapter(cmd);
    }

    When converting to OdbcCommand here is what I have so far but the CommandText is not formatted correctly.

    using (OdbcCommand cmd = new OdbcCommand())
    {
    	cmd.CommandType = CommandType.StoredProcedure;
    	cmd.CommandText = "{ ?= call oracl_pkg.sproc_name( ?, ? ) }";
    	cmd.Parameters.Add("p_start_date", txtFromDate.Text);
    	cmd.Parameters.Add("p_end_date", txtToDate.Text);
    cmd.Parameters.Add(new OdbcParameter("o_rc", OdbcType.Something, ParameterDirection.Output)); cmd.Connection = UHFUtility.GetMetricsDBConnection(); OdbcDataAdapter oda = new OdbcDataAdapter(cmd); }

    I utilized these two web pages as my reference to create the CommandText string: http://support.microsoft.com/kb/255043/en-us and http://msdn.microsoft.com/en-us/library/system.data.odbc.odbccommand.commandtext(v=vs.110).aspx

    I am not sure how the CommandText is supposed to be formed especially since I have a SYS_REFCURSOR as an out parameter from the stored procedure.

    Many thanks,

    Jared


    • Moved by Fred BaoModerator Friday, September 5, 2014 5:23 AM
    • Edited by iJared Friday, September 5, 2014 7:53 PM edited second block of code to reflect new found info
    Friday, September 5, 2014 2:14 AM

Answers

  • Hello iJared,

    I think the ref_cursor is not supported in ODBC since we cannot find a parameter which represents the ref_cursor type in database. And I find that there are some implements for ODBC with rer-cursor, while there is not an environment for .NET:

    http://docs.oracle.com/cd/B19306_01/server.102/b15658/app_odbc.htm#sthref977

    Seaching for G.9 Enabling Result Sets.

    I would suggest that you could confirm with Oracle database experts to see whether there is a .NET supported environment.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by iJared Monday, March 30, 2015 8:18 PM
    Monday, September 8, 2014 8:08 AM
    Moderator

All replies

  • Hello iJared,

    Since you are working with the Oracle database, I would suggest that you could post it to the Oracle forum:

    https://www.oracle.com/communities/index.html

    To check whether the ref cursor type supports .NET Framework.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 5, 2014 7:10 AM
    Moderator
  • Thanks for the advice Fred but my question isn't whether or not ref cursor type works in .NET.  I already know it does.  The first set of code works.  I do retrieve the ref_cursor from the Oracle database.  My question has to do with this line of code in particular in the second set of code lines.

    >> cmd.CommandText = "{ [?=] call oracl_pkg.sproc_name[([" + txtbox1.Text +"][" + txtbox2.Text + "][ resultset 0, io_cursor ])] }";

    I've actually changed this line to:

    cmd.CommandText = "{ ? = call bld_paypal.get_paypal_upload(?,?) }";
    cmd.Parameters.Add("p_start_date", txtFromDate.Text);
    cmd.Parameters.Add("p_end_date", txtToDate.Text);
    cmd.Parameters.Add(new OdbcParameter("o_rc", OdbcType., ParameterDirection.Output));

    I am trying to figure out what the syntax of this line should be for ODBC.  Here's a post I found on StackOverflow: http://stackoverflow.com/questions/2687187/what-is-the-proper-odbc-command-for-calling-oracle-stored-procedure-with-paramet, but I am trying to find out if .NETs ODBC provider supports receiving the ref_cursor.

    Thanks though,

    Jared



    • Edited by iJared Friday, September 5, 2014 7:35 PM added content
    Friday, September 5, 2014 5:32 PM
  • Hello iJared,

    I think the ref_cursor is not supported in ODBC since we cannot find a parameter which represents the ref_cursor type in database. And I find that there are some implements for ODBC with rer-cursor, while there is not an environment for .NET:

    http://docs.oracle.com/cd/B19306_01/server.102/b15658/app_odbc.htm#sthref977

    Seaching for G.9 Enabling Result Sets.

    I would suggest that you could confirm with Oracle database experts to see whether there is a .NET supported environment.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by iJared Monday, March 30, 2015 8:18 PM
    Monday, September 8, 2014 8:08 AM
    Moderator