Informix

 View Only
Expand all | Collapse all

How can I create stored procedures from c# code that uses .NET provider?

  • 1.  How can I create stored procedures from c# code that uses .NET provider?

    Posted Fri June 04, 2021 04:25 PM
    Hi all:

    Through the .NET provider, my c# program can successfully create tables, constraints, and indices. But whenever I try to create a stored procedure, I get error 42000 'A syntax error occurred'. However, if I copy and paste the text assigned to the .CommandText property, into dbaccess, then it creates the procedure successfully. This occurs for any stored procedure, not just for some specific one. Are there any settings that have to be adjusted on the connection or on the IfxCommand object, to allow it to create stored procedures?

    Since my program is 32 bit, I am using clientsdk.4.10.TC14.windows86.zip provider, and my server is 14.10FC4.

    Thanks all!
    #Informix


  • 2.  RE: How can I create stored procedures from c# code that uses .NET provider?

    IBM Champion
    Posted Fri June 04, 2021 04:49 PM
    Post some sample code to create a simple proc. and we'll review. There is no magic wand or setting, it should just work. Here's a ESQL/C code snippet from one of my utilities (dbsavail) to create a stored procedure. .NET should work nearly the same:

           snprintf( procstr,
                     sizeof procstr,
                     "CREATE FUNCTION dbsavail_detail( p_dbsnum INT ) \n"
                     "    RETURNING CHAR(256) AS chk_path, INT AS chk_pages, INT AS chk_free_pgs;\n\n"
                     "    DEFINE l_path CHAR(256);\n "
                     "    DEFINE l_pgs, l_free INTEGER;\n\n "
                     "    FOREACH select fname, chksize, nfree \n"
                     "    INTO l_path, l_pgs, l_free\n"
                     "    FROM %s\n "
                     "    WHERE dbsnum = p_dbsnum\n"
                     "    ORDER BY chknum\n "
                     "        RETURN l_path, l_pgs, l_free WITH RESUME; \n"
                     "    END FOREACH;\n "
                     "END FUNCTION;\n ",
                     (fast_ok ? "syschunks_fast" : "syschunks") );
           EXEC SQL EXECUTE IMMEDIATE :procstr;
           if (sqlca.sqlcode) {
               fprintf( stderr,
                        "Error creating detail function: sqlcode: %d, ISAM: %d @%d.\n",
                        sqlca.sqlcode,
                        sqlca.sqlerrd[1],
                        sqlca.sqlerrd[4] );
               fprintf( stderr, "%s\n", procstr );
               EXEC SQL ROLLBACK WORK;

               return sqlca.sqlcode;
           }
       }



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 3.  RE: How can I create stored procedures from c# code that uses .NET provider?

    Posted Sat June 05, 2021 03:06 AM
    Hi Mehmet,

    Below is part of my working C# .NET code to drop, create and run the very simple(simplest) stored procedure. You can follow the below constructs for your stored procedure operations.

    HTH

    try
    {
    thisTest.testLog.LogLine("Set CommandType to StoredProcedure");
    thisTest.defaultCmd.CommandType = CommandType.StoredProcedure;

    thisTest.testLog.LogLine("Set CommandText");
    thisTest.defaultCmd.CommandText = "noInParams";

    thisTest.testLog.LogLine("Execute Procedure");
    thisTest.defaultCmd.ExecuteReader();
    }
    catch(IfxException ie)
    {
    thisTest.CheckErrors(ie);
    }


    try
    {
    thisTest.testLog.LogLine("Dropping procedure noInParams");
    thisTest.defaultCmd.CommandText = "DROP PROCEDURE noInParams";
    thisTest.defaultCmd.ExecuteNonQuery();
    }
    catch
    {
    //handle any exception here
    }

    thisTest.testLog.LogLine("Creating procedure noInParams");
    thisTest.defaultCmd.CommandText = "CREATE PROCEDURE noInParams(); END PROCEDURE";
    thisTest.defaultCmd.ExecuteNonQuery();

    Thanks
    -Shesh

    ------------------------------
    Sheshnarayan Agrawal
    ------------------------------

    Original Message