locked
Declare SQL statement RRS feed

  • Question

  • User-1035692366 posted

    Hi 

    I have been changing my asp.net application from mssql to mysql due to client's request

    This is the following codes that i have been changing and couldn't make it work due to this error

    MSSQL : 

    mySQL = "DECLARE @outputtbPollID TABLE (PollID CHAR(38)); INSERT INTO Poll (AddedDate, AddedBy, QuestionText, IsCurrent) OUTPUT INSERTED.PollID INTO @outputtbPollID VALUES(N'" + DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + "', N'" + Session["UserID"] + "' , ?Question, '1'); SELECT PollID FROM @outputtbPollID;";

    MySQL : 

    I tried

    mySQL = "BEGIN DECLARE outputtbPollID CHAR(38) END;" + "INSERT INTO Poll (AddedDate, AddedBy, QuestionText) OUTPUT INSERTED.PollID INTO @outputtbPollID VALUES(N'" + DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + "', N'" + Session["UserID"] + "' , ?Question); " + "SELECT PollID FROM @outputtbPollID;";

    OR

    mySQL = "DECLARE outputtbPollID CHAR(38);select PollID from Poll;SELECT outputtbPollID;" + "INSERT INTO Poll (AddedDate, AddedBy, QuestionText) OUTPUT INSERTED.PollID INTO @outputtbPollID VALUES(N'" + DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + "', N'" + Session["UserID"] + "' , ?Question); " + "SELECT PollID FROM @outputtbPollID;";

    but no result


    Tuesday, August 20, 2013 12:12 AM

Answers

All replies

  • User1508394307 posted

    There are no table variables in mysql. Use temporary tables instead

    CREATE TEMPORARY TABLE outputtbPollID (PollID char(38)); 
    INSERT INTO outputtbPollID ...
    SELECT * FROM outputtbPollID 

    http://stackoverflow.com/questions/1524858/create-table-variable-in-mysql

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 20, 2013 4:21 AM
  • User-1035692366 posted

    Hi 

    I was trying it out in MySQL workbench query statement

    when i run the statement it gives me error 1146 'Table 'dbName.outputtPollId' does not exist'

    the error occurs at the Insert statement

    Code Reference:

    CREATE TEMPORARY TABLE outputtbPollID (PollID char(38));
    INSERT INTO outputtbPollID(PollID) Select PollID from Poll;
    SELECT * FROM outputtbPollID

    UPDATE


    Erm i tried on my project now it works fine..

    But i has an error at the later part of the statement..

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTPUT INSERTED.PollID INTO outputtbPollID VALUES('21-08-2013 10:38:31', 'bf655f' at line 1

    Tuesday, August 20, 2013 10:21 PM
  • User-1035692366 posted

    Hi 

    I have solved it thanks

    Wednesday, August 21, 2013 1:27 AM