[firebird-support] Problem with where clause
Hi all, I am using FB 3 and IBExpert and with the DDL below I get message and it works when I remove the where clause. I think the problem is with the between command. Can you please help me ? create or alter procedure test_commande ( from_date date, to_date date) returns ( commande_id integer, ordre_id integer, num_commande integer, four_id integer, date_commande date, prefixe_num_commande varchar(6), verrouiller smallint, employe_id integer, depot_livraison_id integer, adresse_id integer, utilisateur varchar(30), last_update timestamp, facture_id integer, note varchar(1000), nom_four varchar(150), code_four integer, prefixe_four varchar(6), suffixe_four varchar(6), num_facture varchar(20), annee_facture integer, employe_nom varchar(30), employe_prenoms varchar(30), total_ht numeric(15,2), total_remise numeric(15,2), total_ht_net numeric(15,2), total_tva numeric(15,2), total_ttc numeric(15,2), type_adresse varchar(1), intitule_adresse varchar(50), adr_ligne1 varchar(50), adr_ligne2 varchar(50), adr_ligne3 varchar(50), ville varchar(80), code_postal varchar(6), tel varchar(30), fax varchar(30), nom_wilaya varchar(100), nom_pays varchar(80), transformer_facture smallint, transformer_livraison smallint, motif_ordre varchar(50), unite_id integer, intitule_unite varchar(100), code_unite varchar(3), nom_projet varchar(200), projet_id integer, delai_livraison integer, status_commande varchar(2), intitule_depot_livraison varchar(50), num_ordre integer, prefixe_ordre varchar(2), date_ordre date, commande_annulee smallint, annee_ordre integer) as declare variable v1 varchar(1); begin V1= '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,'; V1=:V1 || '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,'; V1=:V1 || '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,'; V1=:V1 || '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,'; V1=:V1 || '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,'; V1=:V1 || 'OD.PREFIXE,OD.DATE_ORDRE,CF.COMMANDE_ANNULEE,OD.ANNEE_ORDRE'; V1=:V1 || ' FROM COMMANDE_FOURNISSEUR AS CF'; V1=:V1 || ' LEFT JOIN TOTAUX_COMMANDEFOUR_LIGNE(CF.COMMANDE_ID ) AS CL ON( 1 = 1 )'; V1=:V1 || ' LEFT JOIN FOURNISSEUR FO ON CF.FOUR_ID=FO.FOUR_ID'; V1=:V1 || ' LEFT JOIN ORDRE_ACHAT OD ON CF.ORDRE_ID=OD.ORDRE_ID'; V1=:V1 || ' LEFT JOIN FACTURE_FOURNISSEUR FF ON CF.FACTURE_ID=FF.FACTURE_ID'; V1=:V1 || ' LEFT JOIN EMPLOYE EM ON CF.EMPLOYE_ID=EM.EMPLOYE_ID'; V1=:V1 || ' LEFT JOIN VW_ADRESSE_TIERS AD ON CF.ADRESSE_ID=AD.ADRESSE_ID'; V1=:V1 || ' LEFT JOIN UNITE U ON CF.UNITE_ID=U.UNITE_ID'; V1=:V1 || ' LEFT JOIN PROJET P ON OD.PROJET_ID=P.PROJET_ID'; V1=:V1 || ' LEFT JOIN DEPOT D ON CF.DEPOT_LIVRAISON_ID=D.DEPOT_ID'; V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '||:FROM_DATE||' AND '||:TO_DATE; FOR EXECUTE STATEMENT v1 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
[firebird-support] Problem with where clause
Hi, I think there is a quote missing at the end of ":TO_DATE"; Regards Martin
Re: [firebird-support] Problem with where clause
Normally, a query would contain something like: WHERE CF.DATE_COMMANDE BETWEEN '1.1.2018' AND '2.1.2018' What you are doing is forgetting the apostrophes and trying to use: WHERE CF.DATE_COMMANDE BETWEEN 1.1.2018 AND 2.1.2018 which correctly receives a syntax error. I can think of three ways to fix this: Either V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '''||:FROM_DATE||''' AND '''||:TO_DATE||; or (named parameters): V1=:V1 || ' WHERE CF.DATE_COMMANDE BETWEEN :MY_FROM_DATE AND :MY_TO_DATE'; FOR EXECUTE STATEMENT (V1) (MY_FROM_DATE := FROM_DATE, MY_TO_DATE := TO_DATE) INTO ... or (positional parameters): V1=:V1 || ' WHERE CF.DATE_COMMANDE BETWEEN ? AND ?'; FOR EXECUTE STATEMENT (V1) (FROM_DATE, TO_DATE) INTO ... HTH, Set 2018-01-05 12:58 GMT+01:00 Köditz, Martin martin.koed...@it-syn.de [firebird-support] : > > > Hi, > > > > I think there is a quote missing at the end of „:TO_DATE“; > > > > Regards > > Martin > > > >
Re: [firebird-support] Problem with where clause
Thanks but adding a quote at the end doesn't work. In fact adding double quote because with one the SP will not be compiled
Re: [firebird-support] Problem with where clause
On 5-1-2018 12:52, m_brahi...@yahoo.fr [firebird-support] wrote: > I am using FB 3 and IBExpert and with the DDL below I get message > command> and it works when I remove the where clause. I think the > problem is with the between command. Can you please help me ? Why are you using `execute statement`, and not simply `for select ...`? There doesn't seem to be any reason why you need dynamically built SQL here, as the only dynamic thing are the from_date and to_date parameters, which would work just fine in `for select ...`. So, first try to convert this to a normal `for select` statement. Otherwise, if you really need dynamically generated SQL, change the last line to: V1=:V1 || ' WHERE CF.DATE_COMMANDE BETWEEN :FROM_DATE AND :TO_DATE'; and use `execute statement v1 (from_date := from_date, to_date := to_date) into ...` Your immediate problems is caused by missing quotes around the date values though. Mark -- Mark Rotteveel
Re: [firebird-support] Problem with where clause
On 5-1-2018 13:37, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > Your immediate problems is caused by missing quotes around the date > values though. Specifically, it would need to be: V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '''||:FROM_DATE||''' AND '''||:TO_DATE||; But that isn't the 'right' solution for this. Mark -- Mark Rotteveel
Re: [firebird-support] Problem with where clause
On 5-1-2018 13:23, m_brahi...@yahoo.fr [firebird-support] wrote: > Thanks but adding a quote at the end doesn't work. In fact adding double > quote because with one the SP will not be compiled That is because || '; would open a string but not end it, then ||''; doesn't work, because that is just an empty string. You'd need ||;, as '' is how you escape a single quote in a string literal. But as I answered elsewhere, this isn't the right solution for your problem. Mark -- Mark Rotteveel
Re: [firebird-support] Problem with where clause
Now with V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '''||:FROM_DATE||''' AND '''||:TO_DATE||; It works for me and I tried with V1=:V1 || ' WHERE CF.DATE_COMMANDE>='''||:FROM_DATE||''' AND CF.DATE_COMMANDE<='''||:TO_DATE||; It works too. In which case this isn't the right solution ?
Re: [firebird-support] Problem with where clause
Anyway, as it solved thanks to all
Re: [firebird-support] Problem with where clause
On 5-1-2018 14:35, m_brahi...@yahoo.fr [firebird-support] wrote: > > > Now with > > V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '''||:FROM_DATE||''' AND > '''||:TO_DATE||; > > It works for me and I tried with > > V1=:V1 || ' WHERE CF.DATE_COMMANDE>='''||:FROM_DATE||''' AND > CF.DATE_COMMANDE<='''||:TO_DATE||; > > It works too. In which case this isn't the right solution ? It works, but you should not be using execute statemen. Using for select should work just fine. In your case, execute statement only adds overhead and removes compile time syntax checking and makes it easier to make mistakes and harder to spot them. Changing this to use for select would simplify your code, as you would not be dealing with string concatenation, and you get a compile time syntax check in return. Mark -- Mark Rotteveel
Re: [firebird-support] Problem with where clause
I use FOR SELECT in some part of the DB but the problem is when I use multiple criteria for example for the same table order order_date, order_no, etc... then I had to use execute statement to build query for each criteria. According to your advise, do I need to build the SP with FOR SELECT and call it from the client (delphi) with criteria ?
[firebird-support] tcpsvd listening on different IP addresses controlling security2.fdb location
Hi All, Sorry for the repost, but it's been a year We have tcpsvd binding fb_inet_server processes to different port/address/root combinations but sharing a tmpfs lock folder and security2.fdb on the same server. Reading and writing to security2.fdb seems to be about the only significant IO on the root partition so I'm thinking about moving it onto a tmpfs too and symlinking back into /var/lib/firebird/2.5/system/security2.fdb. (The users in the security database are fairly static and new ones are only created when we start and stop customers - i.e. I don't really care if it survives a reboot as I manage users from bash scripts already. ) However I think it would be neater if I could just tell fb_inet_server to use a different security database in a similar way to the lock file and root, and even better if I can give each fb_inet listener it's own security database. Am I being thick and not seeing either: 1. an easy way to tell fb_inet_server where the security database is? 2. a glaring big hole in this plan that is going to bring it all crashing down around me? So far (for the last 4 years) the only thing I've seen is that I need to be certain that processes accessing shared databases use the same lock folder otherwise bad things start to happen quickly. But I think if they get their own security2.fdb then they can probably get their own lock folders too, and truly all customers will have separate resources for all IO. Thanks Ian
[firebird-support] garbage problem
Dear currently i have an application at client side runing a timer 5s keep do the select sql read the Table as status on server DB. after few day run the fb service hang up and slow respond the DB file size also incease any solution？ From Hugo Chia
Re: [firebird-support] Query and problem with nulls
Hallo, Il 04/01/2018 21.57, setysvar setys...@gmail.com [firebird-support] ha scritto: I expect the following query to give you the result you want: ... Yes. I think I understand your query and I'll treasure it for the next time. Thanks so much. -- Luigi Siciliano --
Re: [firebird-support] garbage problem
05.01.2018 18:09, HuI HaO huihaoc...@hotmail.com [firebird-support] wrote: > currently i have an application at client side runing a timer 5s keep do the > select sql > read the Table as status on server DB. after few day run the fb service hang > up and slow > respond the DB file size also incease > any solution？ You must monitor health of your database using gstat, monitoring tables and other DBA tools to locate problem and solve it. Also OS monitoring tools must be used to diagnose whole system. -- WBR, SD.
