Announcement

Collapse
No announcement yet.

Any examples of Store Procedure ?

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

  • Any examples of Store Procedure ?

    I'm looking in SC Docs, but the help about the Stored Procedure is very blurry (probably because ot the language problems).
    As in title - are there any workink projects or examples we could study to see how the Stored Procedures work ?

    Arthur
    -----------------------------------------
    Arthur Klisiewicz
    dATA POINT SOFTWARE
    www.datapointsoftware.com

  • #2
    Why look you at sc for stored procedures? This is a database thing ... For MySQL look at this Tutorial.
    Best regards: - Reinhard -

    I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

    Comment


    • #3
      thanks Reinhard

      I did ask about the SC example not the SQL tutorial.
      -----------------------------------------
      Arthur Klisiewicz
      dATA POINT SOFTWARE
      www.datapointsoftware.com

      Comment


      • #4
        Yes, you have to create procedures by sc ... but from experience I would advise ... Do all relationships between tables, tiggres, procedures, views, etc ... in your database, as this will make your application more quickly after they developed.

        Comment


        • #5
          my question is how would I call Stored Procedure from SC ?
          -----------------------------------------
          Arthur Klisiewicz
          dATA POINT SOFTWARE
          www.datapointsoftware.com

          Comment


          • #6
            In principle without parameters:
            sc_select(ds, "CALL <proc_name>()");

            In principle with parameters:
            sc_begin_trans()
            sc_select(ds1, "CALL <proc_name>(@first_param, @second_param)");
            sc_select(ds2, "SELECT @first_param, @second_param");
            sc_commit_trans()

            The first statement calls the stored procedure and fills the parameters. The second statement read the parameters. sc_begin / sc_commit is needed because otherwise the internal values ​​can not be assigned. Look at this tutorial ...
            Last edited by RHS; 10-22-2013, 10:07 AM.
            Best regards: - Reinhard -

            I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

            Comment


            • #7
              that is much better explanation
              so I'm assuming it could be only done through the code - right ?


              THANK YOU
              Arthur
              -----------------------------------------
              Arthur Klisiewicz
              dATA POINT SOFTWARE
              www.datapointsoftware.com

              Comment


              • #8
                Originally posted by RHS View Post
                In principle without parameters:
                sc_select(ds, "CALL <proc_name>()");

                In principle with parameters:
                sc_begin_trans()
                sc_select(ds1, "CALL <proc_name>(@first_param, @second_param)");
                sc_select(ds2, "SELECT @first_param, @second_param");
                sc_commit_trans()

                The first statement calls the stored procedure and fills the parameters. The second statement read the parameters. sc_begin / sc_commit is needed because otherwise the internal values ​​can not be assigned. Look at this tutorial ...
                If I understand your code is only for stored procedure which is going to output paramaters but what about the stored procedure which has out and in paramaters ? For example my case(I tried to apply your logic but it doesn't work is there anything I need to change? please help):


                sc_begin_trans();
                sql_exec_sql('CALL RetournerValeur(@preponse ,' . {nom} . ',' . {ppreview} . ',' . {id_p} . ',@pnompost,@pphoto)');
                sc_lookup(ds, "SELECT @preponse,@pnompost,@pphoto");
                sc_commit_trans();

                Comment


                • #9
                  Try this ...

                  Code:
                  sc_begin_trans();
                  sc_select(ds1, "CALL RetournerValeur(@preponse ,' . {nom} . ',' . {ppreview} . ',' . {id_p} . ',@pnompost, @pphoto)");
                  sc_select(ds2, "SELECT @preponse, @pnompost, @pphoto");
                  sc_commit_trans();
                  
                  while(!$ds2->EOF)
                  {
                      debug($ds2->fields[0]);
                      debug($ds2->fields[1]);
                      debug($ds2->fields[2]);
                  }
                  ... and look at http://www.joeyrivera.com/2009/using...ysqlmysqlipdo/
                  Best regards: - Reinhard -

                  I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

                  Comment


                  • #10
                    It is not working. I wonder how scriptcase a big software would ignore the stored procedure execution yet It one of the cores of database>

                    Comment


                    • #11
                      One post is sufficient. Please do not double and certainly do not tripple the same post!
                      Albert Drent
                      aducom software netherlands
                      scriptcase partner, reseller, support and (turn-key) development
                      www.scriptcase.eu / www.scriptcase.nl

                      Comment


                      • #12
                        The focus of ScriptCase, as I see it, is on producing a nice looking and feature rich database based application with a minimum of effort, and in my opinion it succeeds well in this.

                        There are a lot of different programming models and philosophies created by many generations of developers. Many of them have merits, but they are never an end all solution to all development. The problem in development, again in my humble opinion :-), is that the latest batch of developers sometimes see all previous models and technology (and sometimes even the programmers trained in them) as old fashioned and useless. I am not without fault, having occasionally thought just like that.

                        One developer will insist that there must be no logic or procedures in the database at all, "The days of the database is past. It is now just a simple data storage, which one should be able to just discard and replace with any kind of data storage". He will insist that there should be many layers of code handling all logic. Another developer will insist that business logic and validation should reside inside the database so that it is maximally protected against erroneous code which might corrupt the integrity of it. Also inter table transactions handled by triggers may be a lot faster than outside code layers. There may be merit in both viewpoints.

                        More recently, there have been examples of developers insisting on nosql databases for any and all kind of solutions. Certainly, nosql is just the right tool a very specific kind of solution, but very often the time tested tool of relational databases and SQL will be just the right tool for the job.

                        ScriptCase works in a certain way with the database. Follow that, and you can get a good looking application completed very fast. Its not a 100% perfect application. However, it is perhaps 80% with just 20% of the effort -- and that is really good. For the last 20% you may have to resort to normal PHP programming. If calling stored procedures is really important in your application, I advice you to just create and use a PDO connection in PHP code.

                        Through the Database Variables that ScriptCase provides, one can get the connection, username and password of the existing ScriptCase database connection and use them for creating a separate PDO connection like this for MySQL:

                        $dbadr = explode(":", [sc_glo_servidor]);
                        $conn_str = "mysql:host=" . $dbadr[0] . ";port=" . $dbadr[1] . ";dbname=" . [sc_glo_banco];
                        $db = new PDO($conn_str, [sc_glo_usuario], sc_decode([sc_glo_senha]) );

                        With the PDO connection, you can call the stored procedure.
                        See example 4 in: http://php.net/manual/en/pdo.prepared-statements.php
                        Best regards,

                        Frank

                        Comment


                        • #13
                          Originally posted by Orion View Post
                          The focus of ScriptCase, as I see it, is on producing a nice looking and feature rich database based application with a minimum of effort, and in my opinion it succeeds well in this.

                          There are a lot of different programming models and philosophies created by many generations of developers. Many of them have merits, but they are never an end all solution to all development. The problem in development, again in my humble opinion :-), is that the latest batch of developers sometimes see all previous models and technology (and sometimes even the programmers trained in them) as old fashioned and useless. I am not without fault, having occasionally thought just like that.

                          One developer will insist that there must be no logic or procedures in the database at all, "The days of the database is past. It is now just a simple data storage, which one should be able to just discard and replace with any kind of data storage". He will insist that there should be many layers of code handling all logic. Another developer will insist that business logic and validation should reside inside the database so that it is maximally protected against erroneous code which might corrupt the integrity of it. Also inter table transactions handled by triggers may be a lot faster than outside code layers. There may be merit in both viewpoints.

                          More recently, there have been examples of developers insisting on nosql databases for any and all kind of solutions. Certainly, nosql is just the right tool a very specific kind of solution, but very often the time tested tool of relational databases and SQL will be just the right tool for the job.

                          ScriptCase works in a certain way with the database. Follow that, and you can get a good looking application completed very fast. Its not a 100% perfect application. However, it is perhaps 80% with just 20% of the effort -- and that is really good. For the last 20% you may have to resort to normal PHP programming. If calling stored procedures is really important in your application, I advice you to just create and use a PDO connection in PHP code.

                          Through the Database Variables that ScriptCase provides, one can get the connection, username and password of the existing ScriptCase database connection and use them for creating a separate PDO connection like this for MySQL:

                          $dbadr = explode(":", [sc_glo_servidor]);
                          $conn_str = "mysql:host=" . $dbadr[0] . ";port=" . $dbadr[1] . ";dbname=" . [sc_glo_banco];
                          $db = new PDO($conn_str, [sc_glo_usuario], sc_decode([sc_glo_senha]) );

                          With the PDO connection, you can call the stored procedure.
                          See example 4 in: http://php.net/manual/en/pdo.prepared-statements.php
                          Or you can just http://www.scriptcase.net/forum/show...0997#post40997

                          And IMHO, Stored Procedures has more advantages than disavantages, and I use it always I can. But this I think goes to another post.
                          /Giuseppe

                          Professional Scriptcase Services
                          Some Customers opinions

                          Comment


                          • #14
                            I fully agree; stored procedures can be very helpful.

                            My point was just that there is no need to dump ScriptCase down for not supporting calls to them. Sure, it could be nice to have, but they are not strictly necessary for creating a workable application front-end and I think that less experienced users are better be able to develop when they can see the tables directly and just select fields.
                            Best regards,

                            Frank

                            Comment


                            • #15
                              Originally posted by Orion View Post
                              I fully agree; stored procedures can be very helpful.

                              My point was just that there is no need to dump ScriptCase down for not supporting calls to them. Sure, it could be nice to have, but they are not strictly necessary for creating a workable application front-end and I think that less experienced users are better be able to develop when they can see the tables directly and just select fields.

                              But dump down SC is not needed to work with SP, as my link explain
                              /Giuseppe

                              Professional Scriptcase Services
                              Some Customers opinions

                              Comment

                              Working...
                              X