How can I insert store procedures?

[ulisespm]

Hi Steve, Hi all:

I've been looking for and finally, I know how can I "make" a new field based on substrings of another fields. It's through store procedures. My question is: How can I insert a store procedure in my Dataface Application?

Hope you can help me

Thanks in advance

[shannah]

Sorry I didn't reply to your earlier query. Can you describe specifically what you are wanting to do so I can comment on the best ways to achieve it?
-Steve

[ulisespm]

Steve, All:

I have a table with information about transactions (i.e. income, balance, etc). One field has to be built based on the result of some operations. I mean.

Field 1 = Price
Field 2 = Income
Field 3 = Price - Income (this is the one I have to build)

I've asked to some of my friends and they said I have to make a "store-procedure". However I don't know how to call it from MyPhpAdmin and I don't know how to "write" or integrate in __sql__ command to Dataface.

May you help me?

Thank you very much
Ulises, from Mexico

[shannah]

OK. Here's the easy way to do this.

Use the __sql__ parameter of the fields.ini file to define a custom select query for your table.

e.g.

Code: Select all

__sql__ = "select *, Price - Income as Net from mytable"


Then you can treat your Net field like any other field in your table. It just isn't editable (read only).

Note, I haven't tested this. If the SQL parser doesn't like this, let me know and there are workarounds.

-Steve

[ulisespm]

Sorry, this is the message I get every time I try to perform the activity

SELECT COUNT(*) FROM (select *, agencias_costototal - agencias_ingresopago as agencias_suma from Comisiones) as `Comisiones` WHERE `actitud_id` = '4'
Fatal error: Duplicate column name 'agencias_suma'SELECT COUNT(*) FROM (select *, agencias_costototal - agencias_ingresopago as agencias_suma from Comisiones) as `Comisiones` WHERE `actitud_id` = '4'On line 127 of file /home/ulisespa/public_html/latinhotparty/dataface/Dataface/QueryTool.php in function printStackTrace()

[shannah]

Do you already have a column in your table called agencias_suma?

[ulisespm]

Do you already have a column in your table called agencias_suma?

Sure. I have an agencias_costototal (for the amount) an agencias_ingresopago (for the payment) and an agencias_suma (for the difference between the amount and the payment. My table is Comisiones and datatype is float. By the way, I've cleaned up the table in order to avoid any inconsistence with previous data.

The __sql__ code has been inserted at the top of the "fields.ini" of the Comisiones table.

Is there anything that I've forgotten?

Thank you very much
U.

[shannah]

It seems like since your agencias_suma column is simply the difference between two other columns (i.e. it is calculated) you don't need to have it as a separate column in the table. Better to just treat it as a calculated field.

I think that the reason for the error you received ("Duplicate column name") is because you defined a calculated field with the same name as a field that already exists.

-Steve