Results 1 to 9 of 9

Thread: Cannot create MySQL stored procedure / views from Qt

  1. #1
    Join Date
    Sep 2007
    Posts
    36
    Thanks
    5
    Thanked 3 Times in 1 Post

    Arrow Cannot create MySQL stored procedure / views from Qt

    I am trying to create a stored proecure in MySQL from Qt (since i want to distribute my application)

    But exec() will not create a stored procedure. I tried changing the delimiter and everything else that I could think of.

    created tables and inserts the date but it does not create procedures and views.

    Here is the code.

    Qt Code:
    1. QString create_tables_sql;
    2.  
    3. create_tables_sql =
    4. "DROP PROCEDURE IF EXISTS `test`.`GetStockTotal`;"
    5. "CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.`GetStockTotal`(IN curstockid INT(11), OUT stocktotal DECIMAL(15,2))"
    6. "BEGIN"
    7. "DECLARE stocktotal FLOAT DEFAULT 0;"
    8. "SELECT opbalance INTO stocktotal from ledgers where curid = curstockid;"
    9. "END;";
    10.  
    11. me.exec();
    To copy to clipboard, switch view to plain text mode 

    Are there any work around other than doing it directly from MySQL.


  2. #2
    Join Date
    Sep 2008
    Posts
    60
    Thanks
    8
    Thanked 10 Times in 9 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Cannot create MySQL stored procedure / views from Qt

    It looks like you forgot to associate the string with the query:
    Qt Code:
    1. QSqlQuery me(create_tables_sql);
    To copy to clipboard, switch view to plain text mode 

  3. The following user says thank you to yuriry for this useful post:

    pshah.mumbai (8th October 2008)

  4. #3
    Join Date
    Sep 2007
    Posts
    36
    Thanks
    5
    Thanked 3 Times in 1 Post

    Default Re: Cannot create MySQL stored procedure / views from Qt

    yeah i did that. it does not work.

    above code is incorrect. it has the me.exec(create_tables_sql)



    here is the correct code..

    Qt Code:
    1. QString create_tables_sql;
    2.  
    3. create_tables_sql =
    4. "DROP PROCEDURE IF EXISTS `test`.`GetStockTotal`;"
    5. "CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.`GetStockTotal`(IN curstockid INT(11), OUT stocktotal DECIMAL(15,2))"
    6. "BEGIN"
    7. "DECLARE stocktotal FLOAT DEFAULT 0;"
    8. "SELECT opbalance INTO stocktotal from ledgers where curid = curstockid;"
    9. "END;";
    10.  
    11. me.exec(create_tables_sql);
    To copy to clipboard, switch view to plain text mode 

    It does drop the procedure but the create procedure does not work.
    Last edited by pshah.mumbai; 1st October 2008 at 10:00.

  5. #4
    Join Date
    Sep 2008
    Posts
    60
    Thanks
    8
    Thanked 10 Times in 9 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Cannot create MySQL stored procedure / views from Qt

    Strange... I would expect drop procedure to be rolled back if create procedure fails, at least this is default behavior in PostgreSQL.

    Have you tried to split the two commands and execute them one by one? Also, what does qDebug() << query.lastError() say?

    Also, is it possible that create procedure fails because you define a local variable stocktotal with the same name as the name of the OUT parameter?
    Last edited by yuriry; 2nd October 2008 at 08:15.

  6. The following user says thank you to yuriry for this useful post:

    pshah.mumbai (8th October 2008)

  7. #5
    Join Date
    Jul 2006
    Location
    Catalunya - Spain
    Posts
    117
    Thanks
    16
    Thanked 8 Times in 8 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Cannot create MySQL stored procedure / views from Qt

    Or perhaps you have to break your STORED in two sentences, managing transaction manually :

    Qt Code:
    1. // Assuming DB is your QSqlDatabase...
    2. if ( DB.transaction() )
    3. {
    4. QSqlQuery me;
    5. me.exec ( "DROP PROCEDURE IF EXISTS `test`.`GetStockTotal`;" );
    6.  
    7. QString create_tables_sql =
    8. "CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.`GetStockTotal`(IN curstockid INT(11), OUT stocktotal DECIMAL(15,2))"
    9. "BEGIN"
    10. "DECLARE stocktotal FLOAT DEFAULT 0;"
    11. "SELECT opbalance INTO stocktotal from ledgers where curid = curstockid;"
    12. "END;";
    13.  
    14. me.exec(create_tables_sql);
    15. DB.commit();
    16. }
    To copy to clipboard, switch view to plain text mode 

  8. The following user says thank you to jpujolf for this useful post:

    pshah.mumbai (8th October 2008)

  9. #6
    Join Date
    Sep 2007
    Posts
    36
    Thanks
    5
    Thanked 3 Times in 1 Post

    Default Re: Cannot create MySQL stored procedure / views from Qt

    Qt Code:
    1. //Main program
    2. int main(int argc, char *argv[])
    3. {
    4. QApplication app(argc, argv);
    5. db = QSqlDatabase::addDatabase("QMYSQL");
    6. db.setHostName("localhost");
    7. db.setUserName("root");
    8. db.setPassword("root");
    9. db.setDatabaseName("test");
    10. if(db.open())
    11. {
    12. QString create_tables_sql;
    13.  
    14. create_tables_sql = "DELIMITER $$ \n\
    15. CREATE PROCEDURE Fun() \n\
    16. BEGIN \n\
    17. SELECT * FROM test; \n\
    18. END $$ \n\
    19. DELIMITER ; ";
    20.  
    21. qDebug() << create_tables_sql;
    22. QSqlQuery me;
    23. if (!me.exec(create_tables_sql))
    24. {
    25. qDebug() << "Query exec problem";
    26. ; }
    27. qDebug() << me.lastError(); //QSqlError(-1, "", "")
    28. exit(1);
    29. }
    30. else
    31. {
    32. qDebug() << "Db not open";
    33. exit(1);
    34. }
    35.  
    36. return app.exec();
    37. }
    To copy to clipboard, switch view to plain text mode 

    Query exec problem
    QSqlError(1064, "QMYSQL: Unable to execute query", "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 'DELIMITER $$
    CREATE PROCEDURE Fun()
    BEGIN
    SELECT * FROM test;
    END $$
    DELIMI' at line 1")

  10. #7
    Join Date
    Sep 2007
    Posts
    36
    Thanks
    5
    Thanked 3 Times in 1 Post

    Default Re: Cannot create MySQL stored procedure / views from Qt

    Now it seems to work when I remove the delimiter statements.

    I will test it more and report back

  11. #8
    Join Date
    Sep 2007
    Posts
    36
    Thanks
    5
    Thanked 3 Times in 1 Post

    Default Re: Cannot create MySQL stored procedure / views from Qt

    here is the code.

    Qt Code:
    1. QString create_tables_sql;
    2.  
    3. create_tables_sql = "CREATE PROCEDURE Fun() \n\
    4. BEGIN \n\
    5. SELECT * FROM test; \n\
    6. END";
    7.  
    8. qDebug() << create_tables_sql;
    9. if (!me.exec(create_tables_sql))
    10. {
    11. qDebug() << "Query exec problem";
    12. }
    To copy to clipboard, switch view to plain text mode 

  12. #9
    Join Date
    Sep 2007
    Posts
    36
    Thanks
    5
    Thanked 3 Times in 1 Post

    Default Re: Cannot create MySQL stored procedure / views from Qt

    Its working perfectly now.

    1. Do not use the DELIMTER statements

    Qt Code:
    1. QString procedure = "CREATE PROCEDURE Fun() \n\
    2. BEGIN \n\
    3. SELECT * FROM test; \n\
    4. END ;";
    5.  
    6. me.exec(procedure);
    To copy to clipboard, switch view to plain text mode 

    2. Do not call drop procedure in the same query.

Similar Threads

  1. Accessing REF CURSOR in Stored Procedure from Qt
    By sureshbabu in forum Qt Programming
    Replies: 3
    Last Post: 11th August 2010, 16:51
  2. Stored procedure and ODBC
    By filya in forum Qt Programming
    Replies: 2
    Last Post: 15th March 2010, 09:40
  3. Accessing array (TABLE TYPE) in Stored Procedure from Qt
    By sureshbabu in forum Qt Programming
    Replies: 0
    Last Post: 26th September 2007, 12:36
  4. no record returns from stored procedure
    By mandal in forum Qt Programming
    Replies: 0
    Last Post: 26th April 2007, 15:45
  5. MySql Stored Procedures Woes
    By stevey in forum Qt Programming
    Replies: 9
    Last Post: 19th October 2006, 13:58

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.