Defining stored procedures

[Yarin]

I'm assuming there's probably no support for defining stored procedures using DAL functions. Instead I was going to try to create them in raw sql using executesql whenever migrate was turned on. Just wondering if this is the best technique, or someone has another idea

[Anthony]

Do they need to be created dynamically by your app? If not, you could just create them outside of web2py.

Side note -- if you want web2py to generate SQL for any queries, you can form the queries as usual and call the ._select() method instead of .select() -- that will return the SQL as a string rather than actually running the query.

Anthony

[Yarin Kessler]

Yeah I could create them outside of web2py, was just wondering if there was a good way to perform SQL 'prep' on a db along with the table migrations.. not critical though.

--
 
 
 

[Anthony]

I suppose executesql would be fine in that case if you want to do it that way.

[Andrew]

Often wondered about this too. You would also have to call them with executesql.
So should the dal API support stored procedure , database macro definitions and execution?

Would require work in each database adapter, but could we come up with a single interface ?

[Anthony]

Often wondered about this too.  You would also have to call them with executesql.
So should the dal API support stored procedure , database macro definitions and execution?

Would require work in each database adapter, but could we come up with a single interface ?

Do you mean for creating stored procedures, or calling them? To call them, you can use db.executesql(). If you want the returned data to be parsed into a DAL Rows object like a regular select() would be, you can now specify a "fields" argument to executesql(). Here's the docstring explaining its usage:

Added 2012-08-24 "fields" optional argument. If not None, the

results cursor returned by the DB driver will be converted to a

DAL Rows object using the db._adapter.parse() method. This requires

specifying the "fields" argument as a list of DAL Field objects

that match the fields returned from the DB. The Field objects should

be part of one or more Table objects defined on the DAL object.

The "fields" list can include one or more DAL Table objects in addition

to or instead of including Field objects, or it can be just a single

table (not in a list). In that case, the Field objects will be

extracted from the table(s).

The field names will be extracted from the Field objects, or optionally,

a list of field names can be provided (in tablename.fieldname format)

via the "colnames" argument. Note, the fields and colnames must be in

the same order as the fields in the results cursor returned from the DB.

 

Anthony

[Andrew]

Thanks Anthony,   Wasn't aware of that one, and it looks quite useful.

[Yarin Kessler]

+1

--
 
 
 

[Johann Spies]

+1




--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[Anthony]

We just added it a couple days ago. If you get a chance to try it, let us know if there are any problems.

 Anthony

[mcamel]

Hi,

I've read this section on the manual several times (http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#executesql), but wasn't able to understand its meaning.

The reason is that the first three lines are missing, letting the text without the explanation of the purpose of 'fields' argument:

Added 2012-08-24 "fields" optional argument. If not None, the
results cursor returned by the DB driver will be converted to a
DAL Rows object using the db._adapter.parse() method. This requires

 

 I think the manual needs to include the words "...will be converted to a DAL Rows object..." to clarify it.

¡Thank you, Anthony!.

[Hector Chacon]

Yarin Hello, good afternoon, I take your question to answer in general terms as used in web2py Procedures.
1- sure that the database engine you use supports stored procedures (ex: mysql, postgresql, Sybase, Oracle, etc).
2- must create the procedure in the database using the SQL client that corresponds to your database engine, always remember enforce a standard structure in its design (declare variables in / out, creating logic DML or DDL required your business, handle errors, etc).
3 - hard test your SQL SP with the cliente dba, etc.
4 to 1 Once you have the well-oiled sp layer web2py comes.
5 - deberasa create a function in web2py. perfeccionalos then follow these steps (the explanation is conceptual trial and error will have to make in the implementation):
6 - in the "model" of web2py should create a new layer .py module (it must be created after instantiate and register the DAL connection).
 Deberas with the module in question make a standard function that allows you to receive parameters, "the name of the procedure", "sql chain execution."
ex: def exec_proc_web2py (nombre_sp, parameters)
            string_sql = "CALL base." Nombre_sp + + "(" + parameters + ");"
            admindb.executesql return = (string_sql)
ex: if I run the procedure from the SQL client: you would use the command ,Ex: mysql

CALL stm_desa.sp_stm_apl_dep ('A' , '16');

7- in the layer model you need to call the function used to call the procedure and will spend the parameters that your function needs.
8- In case your sp need to do more than one instruction to insert, update or delete, you must do the transaction group within the SP, in blocks start and end of DML transactions. in short inside "begin transaction" and "commit"

We spent a couple of references such as the execution of SQL as well as the execution of the command "db.ExecuteSQL ()"

If you use mysql:
https://dev.mysql.com/doc/refman/5.7/en/commit.html
http://www.latinuxpress.com/books/drafts/web2py/caps/cap6.html
http://web2py.com/book/default/chapter/06