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
Why look you at sc for stored procedures? This is a database thing … For MySQL look at this Tutorial.
thanks Reinhard
I did ask about the SC example not the SQL tutorial.
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.
my question is how would I call Stored Procedure from SC ?
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 …
that is much better explanation
so I’m assuming it could be only done through the code - right ?
THANK YOU
Arthur
What if my procedure is not going to return any dataset but only have to give some output parameter value?
[QUOTE=RHS;18287]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 …[/QUOTE]
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();
Try this …
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-mysql-stored-procedures-with-php-mysqlmysqlipdo/
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>
One post is sufficient. Please do not double and certainly do not tripple the same 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
[QUOTE=Orion;41116]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[/QUOTE]
And IMHO, Stored Procedures has more advantages than disavantages, and I use it always I can. But this I think goes to another 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.
[QUOTE=Orion;41135]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.[/QUOTE]
But dump down SC is not needed to work with SP, as my link explain
What I meant was that there is no need to be so critical of ScriptCase:
SC does what it is supposed to do and the rest can be done through regular PHP programming.
But as your excellent example points out, there is even a way to make it work in ScriptCase, so magezi should just have acknowledged that instead of opening new threads.