Re: [firebird-support] Using a selectable stored procedure
My problem is solved I used a view instead of SP. Many thanks to all and for advises too and sorry for my many posts
Re: [firebird-support] Using a selectable stored procedure
The sample I posted is selecting from a view which join four tables no problem I can call it directly without the SP. But my real problem is in another query which is complex and that I could not build a simply view. I meet problem when I build it in a only a view so I had to build firstly the view and call it SP with other tables. I try to query an order table and product items tables (suppliers, adresses, etc...) executing the SP I get the right data but as my question was on only to select from SP I thought It was not necessary to post it. Here is my SP: BEGIN FOR SELECT CF.COMMANDE_ID,CF.ORDRE_ID, CF.NUM_COMMANDE,CF.FOUR_ID, CF.DATE_COMMANDE, CF.PREFIXE,CF.VERROUILLER, CF.EMPLOYE_ID,CF.DEPOT_LIVRAISON_ID, CF.ADRESSE_ID,CF.UTILISATEUR, CF.LAST_UPDATE,CF.FACTURE_ID,CF.NOTE,FO.NOM_FOUR,FO.CODE_FOUR,FO.PREFIXE,FO.SUFFIXE,FF.NUM_FACTURE,FF.ANNEE, EM.EMPLOYE_NOM,EM.EMPLOYE_PRENOMS,CL.MONTANT_HT,CL.MONTANT_REMISE,CL.MONTANT_HT_NET,CL.MONTANT_TAXE,CL.MONTANT_NET,AD.TYPE_ADRESSE,AD.INTITULE_ADRESSE,AD.ADR_LIGNE1, AD.ADR_LIGNE2,AD.ADR_LIGNE3,AD.VILLE,AD.CODE_POSTAL,AD.TEL,AD.FAX,AD.NOM_WILAYA,AD.NOM_PAYS,CF.TRANSFORMER_FACTURE,CF.TRANSFORMER_LIVRAISON,OD.MOTIF_ORDRE, OD.UNITE_ID,U.INTITULE_UNITE,U.CODE_UNITE, P.NOM_PROJET,OD.PROJET_ID,CF.DELAI_LIVRAISON,CF.STATUS_COMMANDE,D.NOM_DEPOT,OD.NUM_ORDRE, OD.PREFIXE,OD.DATE_ORDRE,CF.COMMANDE_ANNULEE,OD.ANNEE_ORDRE FROM COMMANDE_FOURNISSEUR AS CF LEFT JOIN TOTAUX_COMMANDEFOUR_LIGNE(CF.COMMANDE_ID ) AS CL ON( 1 = 1 ) LEFT JOIN FOURNISSEUR FO ON CF.FOUR_ID=FO.FOUR_ID LEFT JOIN ORDRE_ACHAT OD ON CF.ORDRE_ID=OD.ORDRE_ID LEFT JOIN FACTURE_FOURNISSEUR FF ON CF.FACTURE_ID=FF.FACTURE_ID LEFT JOIN EMPLOYE EM ON CF.EMPLOYE_ID=EM.EMPLOYE_ID LEFT JOIN VW_ADRESSE_TIERS AD ON CF.ADRESSE_ID=AD.ADRESSE_ID LEFT JOIN UNITE U ON CF.UNITE_ID=U.UNITE_ID LEFT JOIN PROJET P ON OD.PROJET_ID=P.PROJET_ID LEFT JOIN DEPOT D ON CF.DEPOT_LIVRAISON_ID=D.DEPOT_ID INTO :COMMANDE_ID,:ORDRE_ID,:NUM_COMMANDE,:FOUR_ID,:DATE_COMMANDE,:PREFIXE_NUM_COMMANDE,:VERROUILLER,:EMPLOYE_ID,:DEPOT_LIVRAISON_ID, :ADRESSE_ID,:UTILISATEUR,:LAST_UPDATE,:FACTURE_ID,:NOTE,:NOM_FOUR,:CODE_FOUR,:PREFIXE_FOUR,:SUFFIXE_FOUR,:NUM_FACTURE,:ANNEE_FACTURE, :EMPLOYE_NOM,:EMPLOYE_PRENOMS,:TOTAL_HT,:TOTAL_REMISE,:TOTAL_HT_NET,:TOTAL_TVA,:TOTAL_TTC,:TYPE_ADRESSE,:INTITULE_ADRESSE,:ADR_LIGNE1, :ADR_LIGNE2,:ADR_LIGNE3,:VILLE,:CODE_POSTAL,:TEL,:FAX,:NOM_WILAYA,:NOM_PAYS, :TRANSFORMER_FACTURE,:TRANSFORMER_LIVRAISON,:MOTIF_ORDRE, :UNITE_ID,:INTITULE_UNITE,:CODE_UNITE,:NOM_PROJET,:PROJET_ID,:DELAI_LIVRAISON,:STATUS_COMMANDE,:INTITULE_DEPOT_LIVRAISON,:NUM_ORDRE, :PREFIXE_ORDRE,:DATE_ORDRE,:COMMANDE_ANNULEE,:ANNEE_ORDRE DO SUSPEND; END TOTAUX_COMMANDEFOUR_LIGNE is a view which gives the sum of product items VW_ADRESSE_TIERS is a view which gives the suppliers adresses In that case, can I use stored procedure ? is that useful ? The same code I have built in a view call it directly but I get double records so I had to use that SP.
Re: [firebird-support] Using a selectable stored procedure
On 6-1-2018 15:19, m_brahi...@yahoo.fr [firebird-support] wrote: > > > Heres is a sample code of the SP > > BEGIN > FOR SELECT O.ORDRE_ID, O.UNITE_ID, O.NUM_ORDRE, O.PREFIXE, O.DATE_ORDRE, > O.MOTIF_ORDRE, O.STRUCTURE_ID, O.PROJET_ID, O.BESOIN_NECESSAIRE, > O.COMMANDE_TRANSMISE, O.UTILISATEUR, O.LAST_UPDATE, O.INTITULE_UNITE, > O.INTITULE_STRUCTURE, O.NOM_PROJET, O.FILE_EXTENSION > FROM VW_ORDRE_ACHAT O > > INTO > :ORDRE_ID, :UNITE_ID, :NUM_ORDRE, :PREFIXE, :DATE_ORDRE, > :MOTIF_ORDRE, :STRUCTURE_ID, :PROJET_ID, :BESOIN_NECESSAIRE, > :COMMANDE_TRANSMISE, > :UTILISATEUR, :LAST_UPDATE, :INTITULE_UNITE, :INTITULE_STRUCTURE, > :NOM_PROJET, :FILE_EXTENSION > DO > SUSPEND; > END > > The call built in a string variable from the client app is: > > SELECT FIRST :NBROW O.* FROM GET_ORDRE_ACHAT O WHERE > O.ORDRE_ID>=:START_ROW ORDER BY O.ORDRE_ID DESC That example doesn't do anything that would warrant the use of a stored procedure. You would be better off selecting from the table directly, or maybe use a view, not a selectable stored procedure. > It works for me but I am worried and planning too if the app wi ll not > get trouble for the futur in production mode. > As I have many criteria to choice from client I had left the FOR EXECUTE > SATEMENT method for this. And according to the advises I received in my > recents post here to not use FOR EXECUTE SATEMENT. Please don't take advice for a specific situation as a blanket advice that applies everywhere. My advice for your previous question was for that specific code as shown in that question: you were dynamically constructing a query in a way that was unnecessary for that specific query as the query wasn't actually dynamic. Mark -- Mark Rotteveel
Re: [firebird-support] Using a selectable stored procedure
06.01.2018 15:19, m_brahi...@yahoo.fr [firebird-support] wrote: > Heres is a sample code of the SP There is no point in such SP. You'll get only bad performance and no advantages. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Using a selectable stored procedure
Heres is a sample code of the SP BEGIN FOR SELECT O.ORDRE_ID, O.UNITE_ID, O.NUM_ORDRE, O.PREFIXE, O.DATE_ORDRE, O.MOTIF_ORDRE, O.STRUCTURE_ID, O.PROJET_ID, O.BESOIN_NECESSAIRE, O.COMMANDE_TRANSMISE, O.UTILISATEUR, O.LAST_UPDATE, O.INTITULE_UNITE, O.INTITULE_STRUCTURE, O.NOM_PROJET, O.FILE_EXTENSION FROM VW_ORDRE_ACHAT O INTO :ORDRE_ID, :UNITE_ID, :NUM_ORDRE, :PREFIXE, :DATE_ORDRE, :MOTIF_ORDRE, :STRUCTURE_ID, :PROJET_ID, :BESOIN_NECESSAIRE, :COMMANDE_TRANSMISE, :UTILISATEUR, :LAST_UPDATE, :INTITULE_UNITE, :INTITULE_STRUCTURE, :NOM_PROJET, :FILE_EXTENSION DO SUSPEND; END The call built in a string variable from the client app is: SELECT FIRST :NBROW O.* FROM GET_ORDRE_ACHAT O WHERE O.ORDRE_ID>=:START_ROW ORDER BY O.ORDRE_ID DESC It works for me but I am worried and planning too if the app will not get trouble for the futur in production mode. As I have many criteria to choice from client I had left the FOR EXECUTE SATEMENT method for this. And according to the advises I received in my recents post here to not use FOR EXECUTE SATEMENT. Thanks
Re: [firebird-support] Using a selectable stored procedure
On 6-1-2018 14:30, m_brahi...@yahoo.fr [firebird-support] wrote: > Selecting fields of stored procedure build with FOR SELECT from a > delphi app with where clause is not bad a a method ? Thanks I think you need to illustrate this with a small example. Mark -- Mark Rotteveel
