Announcement

Collapse
No announcement yet.

Run MySQL stored procedure in SC

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Run MySQL stored procedure in SC

    Can someone please tell me how to run a stored procedure in SC. I want the procedure to pick values from a form.
    so rather than having a number for example 12212345. I want it to be {number}, is this possible?

    Here is my procedure (I need to replace 79090200 with {from} and 79090233 with {to}):
    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_increment`()
    BEGIN


    DECLARE start_val INT;
    DECLARE end_val INT;

    SET start_val=79090200;
    SET end_val=79090233;

    WHILE start_val <= end_val DO
    INSERT INTO awb (gsa, no, status, aid) VALUES(000, start_val, "N", 0);
    SET start_val = start_val + 11;
    END WHILE;

    END

    Any help would mean a lot.
    Last edited by gamer99; 03-11-2014, 09:10 AM.

  • #2
    Hi,
    this should work.

    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_increment`(IN start_val INT, IN end_val INT)
    BEGIN

    WHILE start_val <= end_val DO
    INSERT INTO awb (gsa, no, status, aid) VALUES(000, start_val, "N", 0);
    SET start_val = start_val + 11;
    END WHILE;

    END


    In you app: sc_exec_sql("CALL sp_increment({from}, {to})");

    jsb

    Comment


    • #3
      Originally posted by jsbinca View Post
      Hi,
      this should work.

      DELIMITER $$

      CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_increment`(IN start_val INT, IN end_val INT)
      BEGIN

      WHILE start_val <= end_val DO
      INSERT INTO awb (gsa, no, status, aid) VALUES(000, start_val, "N", 0);
      SET start_val = start_val + 11;
      END WHILE;

      END


      In you app: sc_exec_sql("CALL sp_increment({from}, {to})");

      jsb
      Thank you so much. Works perfectly. You my friend are a life saver!

      Just another question if you dont mind. Can you please tell me how I can also get the gsa value from the form?

      Thanks.
      Last edited by gamer99; 03-12-2014, 04:51 AM.

      Comment


      • #4
        Just add it to your procedure:

        DELIMITER $$

        CREATE OR ALTER PROCEDURE `sp_increment`(In gsa_val INT, IN start_val INT, IN end_val INT)
        BEGIN

        WHILE start_val <= end_val DO
        INSERT INTO awb (gsa, no, status, aid) VALUES(gsa_val, start_val, "N", 0);
        SET start_val = start_val + 11;
        END WHILE;

        END


        In you app: sc_exec_sql("CALL sp_increment({gsa}, {from}, {to})");

        jsb

        Comment


        • #5
          Thank you. I knew it was something like that. I mixed up the sequence at the start that's why it didnt work.

          Once again a super thank you.

          Comment


          • #6
            Is there any way to count the number of records entered from the stored procedure and display that in a message?

            Thanks

            Comment


            • #7
              Here we go.

              Change your procedure (see the variable @vz)


              CREATE PROCEDURE `sp_increment`(In gsa_val INT, IN start_val INT, IN end_val INT)
              BEGIN

              SET @vz = 0;

              WHILE start_val <= end_val DO
              INSERT INTO awb (gsa, no, status, aid) VALUES(gsa_val, start_val, "N", 0);
              SET start_val = start_val + 2;

              SET @vz = @vz+1;

              END WHILE;

              SELECT @vz AS cnt;

              END


              In your application.

              sc_lookup(ins,"CALL sp_increment({gsa}, {from}, {to})");
              if(isset({ins[0][0]}))
              {
              echo 'Number of inserted records: '.{ins[0][0]};
              }

              jsb

              Comment


              • #8
                JSB, you my friend are the best! Thank you.

                UPDATE: Everything works fine, however I get the following error message after the insert (the number or records inserted also shows up here, you can see the attached image):

                Database access
                Commands out of sync; you can't run this command now

                error.png
                Last edited by gamer99; 03-17-2014, 04:47 AM.

                Comment


                • #9
                  Hi,
                  check if your db-user has sufficient rights (execute). Even though the message text suggests something else. I've been there. :-)
                  To isolate the problem, try your procedure call on a control form. Put your call in onValidate and see what happens.

                  jsb
                  Last edited by jsbinca; 03-17-2014, 10:24 AM.

                  Comment


                  • #10
                    Hi there,

                    Thanks for all the help.

                    I tried it on a control form. Still getting the same error. However now its letting me view the sql query, that according to Sc may be causing the problem. Its the select query used to pull data for a drop down on the control form.
                    error2.jpg
                    I'll try removing the select query and see what happens.

                    Comment


                    • #11
                      Update: Yup, its being caused by the select field!

                      Comment


                      • #12
                        Hi there. I haven't figured out whats going on with the select. However I wanted to ask for your help on another matter, only if you have the time.

                        I have created another procedure along the lines you mentioned.
                        Here is my procedure:
                        Code:
                        DELIMITER $$
                        
                        CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_awbreturn`(IN `gsa_val` int, IN `start_val` int, IN `end_val` int, IN `agent_val` int, IN `orig_date` datetime)
                        BEGIN
                        
                        SET @vz = 0;
                        
                        WHILE start_val <= end_val DO
                        INSERT INTO awb_returned (awb_pre, awb_no, return_date, aid, orig_issue_date) VALUES(gsa_val, start_val, NOW(), agent_val, orig_date);
                        SET start_val = start_val + 11;
                        
                        SET @vz = @vz+1;
                        
                        END WHILE;
                        
                        SELECT @vz AS cnt;
                        
                        END
                        For some reason it doesnt update the table
                        Here is the code from the form:
                        Code:
                        sc_lookup(ins,"CALL sp_awbreturn({gsa}, {from}, {to}, {agent}, {origdate})");
                        if(isset({ins[0][0]}))
                        {
                        echo 'AWBs Returned: '.{ins[0][0]};
                        }
                        Am I doing something wrong? As always any help would mean a lot. Thanks

                        Comment


                        • #13
                          Originally posted by gamer99 View Post
                          Code:
                          sc_lookup(ins,"CALL sp_awbreturn({gsa}, {from}, {to}, {agent}, {origdate})");
                          Since DATE/DATETIME is handled as a string in MYSQL you have to encapsulate your date field ({origdate}) in single quotation marks.

                          Code:
                          sc_lookup(ins,"CALL sp_awbreturn({gsa}, {from}, {to}, {agent}, '{origdate}')");
                          jsb

                          Comment


                          • #14
                            I see. Thank you for all the help. Much appreciated.

                            Comment

                            Working...
                            X