PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] mysql stored procedure and INSERT INTO sql statement-VBForums
Results 1 to 4 of 4

Thread: [RESOLVED] mysql stored procedure and INSERT INTO sql statement

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Resolved [RESOLVED] mysql stored procedure and INSERT INTO sql statement

    I am using mysql database and I have sql statement below:

    INSERT INTO tbl_test2(id,ln,fn,mn) SELECT * from tbl_test1; or
    INSERT INTO tbl_test2(id,ln,fn,mn) SELECT id,ln,fn,mn from tbl_test1; or
    INSERT INTO tbl_test2 SELECT id,ln,fn,mn from tbl_test1; or
    INSERT INTO tbl_test2 SELECT * from tbl_test1;

    If I execute any of the four sql statement above, then it will be executed and records will be inserted.
    However, if I place any of the four sql statement above to the stored procedure, then the stored procedure will just hang, lock timeout and no records are saved.

    How can I resolve this problem.
    Last edited by codesearcher; Jul 4th, 2016 at 08:26 PM.

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,387

    Re: mysql stored procedure and INSERT INTO sql statement

    Telling someone that at stored procedure hangs means nothing to us. What do you mean. Can you tell if records are inserted at the backend? Is there an error? What is the rest of the stored procedure.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: mysql stored procedure and INSERT INTO sql statement

    Works for me using 5.6 and MySQL Workbench to do a quick test. As Gary said, we need more info to provide any further help.
    SQL Code:
    1. SELECT * FROM vbtest.mrkup2; 'verify no records exist
    2. use vbtest;
    3. create procedure testInsert()
    4.    INSERT INTO vbtest.mrkup2 SELECT * FROM vbtest.mrkup;
    5.  
    6. call testinsert;
    7. select * from vbtest.mrkup2; '15 records now exist AS expected

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: mysql stored procedure and INSERT INTO sql statement

    Thanks for the replies

    But I resolved it with transaction on stored proc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width