CODEWITHSUNDEEP
c#mysql
kanabut
kanabut

Reputation: 41

C# Fatal error encountered during command execution

i try to execute this command by my application(C# winapp) but it show error like Parameter '@sqlstmt' must be defined but i try this command by navicat it working well please help me

"DELIMITER $$" +

                " DROP PROCEDURE IF EXISTS " + txtDB.Text + ".`index_startTime` $$" +
                " CREATE PROCEDURE " + txtDB.Text + ".`index_startTime`" +
                " (" +
                    " given_database VARCHAR(64)," +
                    " given_table    VARCHAR(64)," +
                    " given_index    VARCHAR(64)," +
                    " given_columns  VARCHAR(64)" +
                " )" +
                " BEGIN" +

                    " DECLARE IndexIsThere INTEGER;" +

                    " SELECT COUNT(1) INTO IndexIsThere" +
                    " FROM INFORMATION_SCHEMA.STATISTICS" +
                    " WHERE table_schema = given_database" +
                    " AND   table_name   = given_table" +
                    " AND   index_name   = given_index;" +

                    " IF IndexIsThere = 0 THEN" +
                        " SET @sqlstmt = CONCAT('CREATE INDEX ',given_index,' ON '," +
                        " given_database,'.',given_table,' (',given_columns,')');" +
                        " PREPARE st FROM @sqlstmt;" +
                        " EXECUTE st;" +
                        " DEALLOCATE PREPARE st;"+
                    " ELSE" +
                        " SELECT CONCAT('Index ',given_index,' already exists on Table ',"+
                        " given_database,'.',given_table) CreateindexErrorMessage; "+
                    " END IF;"+

                " END $$" +

                " DELIMITER ;";

Upvotes: 1

Views: 937

Answers (1)

Drew
Drew

Reputation: 24959

The scenario is one in which the mysql connection needs to be tweaked to allow for User Variable support. Such as

string connString = @"server=localhost;userid=myUser;password=thePassword;database=stackoverflow;Allow User Variables=True";

(note the ending above). See the MySQL Manual Page here concerning Allow User Variables=True . If you don't do that, it will choke bigtime on the @ sign coming in with the string in the user variable part.

A test:

public void testCreateFunctionOnDB()
{   // http://stackoverflow.com/questions/40100348

    using (MySqlConnection lconn = new MySqlConnection(connString))
    {
        lconn.Open();
        MySqlCommand cmd = new MySqlCommand();
        string sXX = @"DROP PROCEDURE IF EXISTS asdf7; 
        CREATE PROCEDURE asdf7() 
        BEGIN 
        SET @sqlstmt = 'dog';  
        IF 1=1 THEN 
          SET @sqlstmt = CONCAT('qwerty ',1,2); 
        END IF; 
        SELECT @sqlstmt; 
        END";  
        cmd.Connection = lconn;
        cmd.CommandText = sXX;
        cmd.ExecuteNonQuery();
    }
}

and the screenshot of it successfully saving to proc to the db:

enter image description here

Also note what I had mentioned in the comments under your question. The insertion of variables into a query like yours for tablename etc is easy with simple CONCATs on the c# side.

If there is a security risk related to Allow User Variables=True (and I don't doubt there is), then have this connection string tweak be relevant to this routine only and not globally.

Another take-away here is that DELIMITER is not necessary in my testing at least (similar to PHPMyAdmin and SQLFiddle). Different than MySQL Workbench / Navicat / SQLYog (perhaps) / Toad (perhaps)

Upvotes: 3

Related Questions