[firebird-support] firebird found more than one transation isolation in tbp
Hi all,
My application is executed fine but when I try to execute an SP I get
that message above
the SP do some works to clean the db at app first installation
before it worked fine for two years and did not make changes the code
since today and now it cannot be executed
Thanks for help
This is the code:
SET TERM ^ ;
create or alter procedure init_first_start
as
declare variable nom varchar(30);
declare variable pwd varchar(250);
declare variable login_name varchar(30);
declare variable id integer;
declare variable role_name varchar(20);
BEGIN
/*
SELECT SEC$USER_NAME FROM SEC$USERS WHERE
SEC$USER_NAME='ADMINISTRATEUR' INTO :NOM;
IF (:NOM IS NULL) THEN
BEGIN
SELECT PWD FROM TB_USER WHERE LOGIN_NAME='ADMINISTRATEUR' INTO :PWD;
EXECUTE PROCEDURE
USER_CREATE('ADMINISTRATEUR',PWD,0,0,'RDB$ADMIN','SYSDBA','','','MASTERKEY');
END
*/
DELETE FROM CONNECTION_LOG;
DELETE FROM MESSAGERIE;
DELETE FROM MESSAGERIE_ATTACHMENT;
UPDATE PARAMETRES SET
BACKUP_DB_PATH='',MIN_LENGTH_PASSWORD=5,EVALUATEUR_MUST_VALIDATE=0,ENABLE_INPUT_CONTROL=0;
FOR SELECT LOGIN_NAME FROM TB_USER INTO :LOGIN_NAME DO
BEGIN
LOGIN_NAME=COALESCE(:LOGIN_NAME,'');
LOGIN_NAME=TRIM(:LOGIN_NAME);
IF (:LOGIN_NAME<>'ADMINISTRATEUR') THEN
BEGIN
EXECUTE STATEMENT 'DROP USER ' || LOGIN_NAME ;
SELECT USERID FROM TB_USER WHERE LOGIN_NAME=:LOGIN_NAME INTO :ID;
DELETE FROM USER_PROFIL WHERE USERID=:ID;
DELETE FROM ALERTE WHERE USERID=:ID;
DELETE FROM TB_USER WHERE LOGIN_NAME=:LOGIN_NAME;
END
END
END^
SET TERM ; ^
/* Following GRANT statements are generated automatically */
GRANT SELECT,DELETE ON CONNECTION_LOG TO PROCEDURE INIT_FIRST_START;
GRANT SELECT,DELETE ON MESSAGERIE TO PROCEDURE INIT_FIRST_START;
GRANT SELECT,DELETE ON MESSAGERIE_ATTACHMENT TO PROCEDURE INIT_FIRST_START;
GRANT SELECT,UPDATE ON PARAMETRES TO PROCEDURE INIT_FIRST_START;
GRANT SELECT,DELETE ON TB_USER TO PROCEDURE INIT_FIRST_START;
GRANT SELECT,DELETE ON USER_PROFIL TO PROCEDURE INIT_FIRST_START;
GRANT SELECT,DELETE ON ALERTE TO PROCEDURE INIT_FIRST_START;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE INIT_FIRST_START TO SYSDBA;
GRANT EXECUTE ON PROCEDURE INIT_FIRST_START TO GESTIONNAIRE;
GRANT EXECUTE ON PROCEDURE INIT_FIRST_START TO OPERATEUR;
[firebird-support] Re: Using system tables in stored procedures
Sorry I found a solution I cancel this topic .
[firebird-support] Using system tables in stored procedures
Hi All, I have many stored procedures and I'd like to include the code below in each one for doing parameters comparisons between a ps parameters and another table which contains user input settings for each parameter in a ps. I'd like to know please, if such code below will not reduce performance especially with a FOR SELECT because I heard that cursor reduce performance and for I don't know for system tables. Thanks The code: FOR SELECT RDB$PROCEDURE_PARAMETERS.RDB$PARAMETER_NAME, RDB$FIELDS.RDB$FIELD_TYPE, CASE RDB$FIELDS.RDB$FIELD_TYPE WHEN 7 THEN 'SMALLINT' WHEN 8 THEN 'INTEGER' WHEN 9 THEN 'QUAD' WHEN 10 THEN 'FLOAT' WHEN 11 THEN 'D_FLOAT' WHEN 12 THEN 'DATE' WHEN 13 THEN 'TIME' WHEN 14 THEN 'CHAR' WHEN 16 THEN 'INT64' WHEN 27 THEN 'DOUBLE' WHEN 35 THEN 'TIMESTAMP' WHEN 37 THEN 'VARCHAR' WHEN 40 THEN 'CSTRING' WHEN 261 THEN 'BLOB' END AS FIELD_TYPE FROM RDB$PROCEDURE_PARAMETERS, RDB$FIELDS WHERE RDB$FIELDS.RDB$FIELD_NAME = RDB$PROCEDURE_PARAMETERS.RDB$FIELD_SOURCE AND RDB$PROCEDURE_NAME='EMPLOYE_UI'
Re: ODP: [firebird-support] Re: Objects cached in FB memory
Because I need to know if compiled views are cached in memory a the sp
[firebird-support] Re: Objects cached in FB memory
I mean through the system tables I have FB 3
[firebird-support] Objects cached in FB memory
Hi All, Is it possible please to see by sql what is cached in FB memory some DBMS does it. Thanks
Re: [firebird-support] Default value in DDL not work
Yes this happen when I omit the field the default value is not applied. I do this insert part into a stored procedure with other SQL statement for other tables. INTITULE_SALAIRE='N-'||F_GET_MONTH_NAME(:MOIS)||' '||:ANNEE; INSERT INTO SALAIRES (EMPLOYEUR_ID, EMPLOYE_ID, UNITE_ID, MOIS, ANNEE,FROM_DATE,TO_DATE,INTITULE_SALAIRE,TRIMESTRE,TYPE_SALAIRE,TYPE_DECLARATION) VALUES (:EMPLOYEUR_ID, :EMPLOYE_ID, :UNITE_ID, :MOIS, :ANNEE,:PAIE_FROM,:PAIE_TO,:INTITULE_SALAIRE,F_GET_TRIMESTRE(:PAIE_FROM),'N','N') RETURNING ROWID INTO :SALAIRE_ID; This table has many fields so I post some of them in defined in DDL with default values like this I can post the entire DDL if needed: CREATE TABLE SALAIRES ( ROWID BIGINT NOT NULL, EMPLOYEUR_ID INTEGER NOT NULL, EMPLOYE_IDINTEGER NOT NULL, UNITE_ID INTEGER NOT NULL, MOISINTEGER NOT NULL, ANNEE INTEGER NOT NULL, NB_JOUR_TRAVAILLERNUMERIC(4,2) DEFAULT 0, NB_HEURE_TRAVAILLER NUMERIC(5,2) DEFAULT 0, NB_FERIE_TRAVAILLER NUMERIC(4,2) DEFAULT 0, NB_CONGE_EXCEPTIONNEL NUMERIC(4,2) DEFAULT 0, NB_CONGE_ANNUEL NUMERIC(4,2) DEFAULT 0, NB_CONGE_SANS_SOLDE NUMERIC(4,2) DEFAULT 0, NB_ABSENCE_AUTORISEE NUMERIC(4,2) DEFAULT 0, NB_ABSENCE_NON_AUTORISEE NUMERIC(4,2) DEFAULT 0, NB_MISSIONNUMERIC(4,2) DEFAULT 0, NB_INTEMPERIE NUMERIC(4,2) DEFAULT 0, NB_MALADIENUMERIC(4,2) DEFAULT 0, NB_HEURE_RETARD NUMERIC(4,2) DEFAULT 0, NB_DEDUCTION NUMERIC(4,2) DEFAULT 0, INTITULE_SALAIRE VARCHAR(50) ); I 've just made I test that I forgot to do it under IBEXPERT with the insert above the default values are applied. If it is correct maybe it comes from my delphi app ?
[firebird-support] Re: Default value in DDL not work
My FB version is Firebird-3.0.3.32900_0_Win32 under XP PRO SP3
[firebird-support] Default value in DDL not work
Hi all, I build tables under IBEXPERT and define some fields with default value FIELD_NAME INTEGER DEFAULT 0 I except 0 as value but has always NULL Any idea ? Thanks
Re: [firebird-support] Start/Stopping service
>Guessing that you installed the Fb service with a non-default service >name - "FirebirdServerfirebird3" - because you have another version of >Firebird running on the same host machine and you want to call instsvc >from your application to check the status of the Fb3 server process... Yes I had an old version (FB2) that I uninstalled before maybe not completly. >I don't know whether this answers your question, though. Yours answers it's enough for me thank you very much Helen
Re: [firebird-support] Start/Stopping service
Thanks Mark
Re: [firebird-support] Start/Stopping service
<> It is my case I have "FirebirdServerfirebird3" on XP pro It means that when I install Firebird3 for the first time without having older FB version on my machine the service name will be "FirebirdServerDefaultInstance" in all windows version ? If having installed older FB version the service name wil be "FirebirdServerfirebird3" ? because it is my case althought I uninstalled old version (FB2) maybe not completely uninstalled ?
Re: [firebird-support] Start/Stopping service
Thank you for answer, Yes the service is present in Local Services and the file executable file in the area is C:\Firebird\Firebird_3_0\firebird.exe -s firebird3 anyway I have no problem with managing service to access my application but I don't know if it is correct firebird.exe instead of instsvc in the service path area. So I need to know please If the service name "FirebirdServerfirebird3" is always the same in any windows version because I must manage FB service within delphi routine like checking service status, start, stop. Thanks
[firebird-support] Start/Stopping service
Hello, I am using Firebird-3.0.3.32900_0_Win32.exe on XP when I try to start service in installed FB folder with C:\Firebird\Firebird_3_0>instsvc sta or instsvc sto I get that message "Error occured during OpenService" The specified service doens't exists as installed service (sorry I had to translate from french) Usually I start service from .bat file with net start "Firebird Server - firebird3" Where is the problem please ?
Re: [firebird-support] How many record can I fill in a table
Thank you very much to all
[firebird-support] How many record can I fill in a table
Hello, I need to know please, what is the limit of record in a table with more than a hundred fields in FB 3 without reaching the 64 KB maximum row size. How many records my table must contains ? Sorry if my question seems lazy. Thanks
Re: [firebird-support] What I am missing in the transaction ?
Simply !! I didn't thought really to that. I've just test it. I am very confused. Thank you very much Dimitry !
[firebird-support] Re: What I am missing in the transaction ?
In the transaction I use tpConcurrency, tpWait,tpWrite parameters
[firebird-support] What I am missing in the transaction ?
Hi all, Two users access at the same record. The first delete it and the second update the delete record after the first user. The second who update the record must obtains something like : Statement failed, SQLCODE = -901 cannot update erased record In my case I don't obtain that message maybe I am missing parameter in the transaction isolation ? Thanks for the help
[firebird-support] Adding another field description to a table
Hello, Is it I know there is one field description to comment the table, I need to know please if it is possible to add another varchar field ? Thanks
[firebird-support] Resizing fields data type
Hi All, I need to resize fields data type (numeric length and scale) of certain tables but I cannot do it du to dependencies (views and stored procedures). Is there a way please, to do it through system tables using a stored procedure which allows to modify data type in the table and in other object where the concerned fields/tables are referenced ? Thanks
[firebird-support] violation of PRIMARY or UNIQUE KEY constraint
Hi all,
I get the following message when create and compiling a view under IBEXPERT :
violation of PRIMARY or UNIQUE KEY constraint "RDB$INDEX_15" on table
"RDB$RELATION_FIELDS".
Problematic key value is ("RDB$FIELD_NAME" = 'COMMUNE_ID', "RDB$RELATION_NAME"
= 'VW_EMPLOYE_FULL_LISTE').
I am using FB3
Can you please tell me from where it comes ?
[firebird-support] Violation primary key in constraint
Hi all, I am not sure if my post was sent anyway my problem is solved about the violation primary key in constraint Thanks
[firebird-support] How to use CAST
Hi all, I need to know please If I can use CAST one time for all fields in a calculation in order to get 2 digits. For example : SUM(CAST((CL.QUANTITE * CL.PRIX_UNITAIRE*(1.0-COALESCE(CL.TAUX_REMISE,0)/100.0) *(1+COALESCE(CL.TAUX_TVA,0)/100.0)) AS NUMERIC(9,2))) AS MONTANT_NET When I use CAST one time it doesn't affect the result calculation as I use CAST for each field ? Thanks
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
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
[firebird-support] Using a selectable stored procedure
Hi all, Selecting fields of stored procedure build with FOR SELECT from a delphi app with where clause is not bad a a method ? Thanks
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 ?
Re: [firebird-support] Problem with where clause
Anyway, as it solved thanks to all
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
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
[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
RE: [firebird-support] SELECT GROUP BY
Yes it was that Thanks sab
Re: [firebird-support] SELECT GROUP BY
Sorry, it was an SQL and not FB problem Thanks Karol
[firebird-support] SELECT GROUP BY
Hi all, SELECT CAST( COALESCE(CL.QUANTITE * CL.PRIX_UNITAIRE,0) AS NUMERIC(9,2)) AS TOTAL_HT, SUM(CAST(CL.QUANTITE * CL.PRIX_UNITAIRE *( COALESCE(CL.TAUX_REMISE,0)/100.0) AS NUMERIC(9,2))) AS TOTAL_REMISE, SUM((CL.QUANTITE * CL.PRIX_UNITAIRE) - CL.QUANTITE * CL.PRIX_UNITAIRE * (COALESCE(CL.TAUX_REMISE,0)/100.0)) AS TOTAL_HT_NET, SUM((COALESCE(CL.TAUX_TVA,0)/100.0)*CL.QUANTITE * CL.PRIX_UNITAIRE*(1.0-(COALESCE(CL.TAUX_REMISE,0)/100.0))) AS TOTAL_TVA, SUM(CL.QUANTITE * CL.PRIX_UNITAIRE*(1.0-COALESCE(CL.TAUX_REMISE,0)/100) *(1+COALESCE(CL.TAUX_TVA,0)/100.0)) AS TOTAL_TTC FROM COMMANDE_FOUR_LIGNE CL WHERE CL.COMMANDE_ID=1 GROUP BY CL.QUANTITE,CL.PRIX_UNITAIRE,CL.TAUX_REMISE,CL.TAUX_TVA,CL.COMMANDE_ID I need to sum records which have the same foreign key value (CL.COMMANDE_ID=1) in order to obtain only one line of the fields I sum. The table contains 4 records and I get 4 four records instead of one record. Is that something wrong with my query,can you please help me ?
Re: [firebird-support] Joining more than 10 tables and views in a view
Thank you. Another question please: are the views cached as the stored procedure ?
[firebird-support] Joining more than 10 tables and views in a view
Hi all, I created a view where I join 6 tables and 2 other views that contains 3 or 4 tables joined each one. Can you please, tell me if is this correct to join mutliple tables and views and will not be slow when the server processes this view ? and what will be the result when I will transfer the data for about 500 records of that view from the server to the client in the network ? Here is the code and thanks for your help. CREATE OR ALTER VIEW VW_COMMANDE_FOURNISSEUR( COMMANDE_ID, NUM_COMMANDE, ANNEE_COMMANDE, FOUR_ID, DATE_COMMANDE, PREFIXE_NUM_COMMANDE, VERROUILLER, EMPLOYE_ID, DEPOT_LIVRAISON_ID, ADRESSE_ID, UTILISATEUR, LAST_UPDATE, PROJET_ID, FACTURE_ID, NOTE, NOM_FOUR, CODE_FOUR, PREFIXE_FOUR, SUFFIXE_FOUR, NUM_FACTURE, ANNEE_FACTURE, NOM_PROJET, EMPLOYE_NOM, EMPLOYE_PRENOMS, TOTAL_HT, TOTAL_REMISE, TOTAL_HT_NET, TOTAL_TVA, TOTAL_TTC) AS SELECT CF.COMMANDE_ID, CF.NUM_COMMANDE, CF.ANNEE, 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.PROJET_ID, CF.FACTURE_ID,CF.NOTE, FO.NOM_FOUR,FO.CODE_FOUR,FO.PREFIXE,FO.SUFFIXE,FF.NUM_FACTURE,FF.ANNEE,PR.NOM_PROJET,EM.EMPLOYE_NOM,EM.EMPLOYE_PRENOMS, CAST( COALESCE(CL.MONTANT_HT,0) AS NUMERIC(9,2)) AS TOTAL_HT, SUM(CAST( COALESCE(CL.MONTANT_REMISE,0) AS NUMERIC(9,2))) AS TOTAL_REMISE, SUM(CAST(COALESCE(CL.MONTANT_HT_NET,0) AS NUMERIC(9,2))) AS TOTAL_HT_NET, SUM(CAST(COALESCE(CL.MONTANT_TAXE,0) AS NUMERIC(9,2))) AS TOTAL_TVA, SUM(CAST(COALESCE(CL.MONTANT_NET,0) AS NUMERIC(9,2))) AS TOTAL_TTC FROM COMMANDE_FOURNISSEUR CF INNER JOIN FOURNISSEUR FO ON CF.FOUR_ID=FO.FOUR_ID LEFT JOIN VW_LIGNECOMMANDEFOURNISSEUR CL ON CF.COMMANDE_ID=CL.COMMANDE_ID LEFT JOIN FACTURE_FOURNISSEUR FF ON CF.FACTURE_ID=FF.FACTURE_ID LEFT JOIN PROJET PR ON CF.PROJET_ID =PR.PROJET_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 STRUCTURE ST ON CF.STRUCTURE_ID=ST.STRUCTURE_ID GROUP BY CF.COMMANDE_ID, CF.NUM_COMMANDE, CF.ANNEE, 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.PROJET_ID, CF.FACTURE_ID,CF.NOTE, FO.NOM_FOUR,FO.CODE_FOUR,FO.PREFIXE,FO.SUFFIXE,FF.NUM_FACTURE,FF.ANNEE,PR.NOM_PROJET,EM.EMPLOYE_NOM,EM.EMPLOYE_PRENOMS, CL.MONTANT_HT,CL.MONTANT_REMISE,CL.MONTANT_HT_NET,CL.MONTANT_TAXE,CL.MONTANT_NET ;
[firebird-support] Execute statement performance
Hello, I need to know please, If there is a difference of perfomance between an execute statement query in a stored procedure and without execute statement for the same SQL query . Thank you
Re: [firebird-support] Moving DB from 32 bits to 64 bits
Yes exactly and thanks again !
Re: [firebird-support] Moving DB from 32 bits to 64 bits
I am only using some functions from the ib_udf.dll which is installed in the \Firebird\Firebird_3_0\UDF and have built a little functions calling functions of that dll there is no problem I suppose ?
Re: [firebird-support] Moving DB from 32 bits to 64 bits
I am already under FB3 and I am only planning in case I will move to 64 bits server Thank you very much for these informations
[firebird-support] Moving DB from 32 bits to 64 bits
Hello, I would like to know please, if we can use a DB in win64 but built under win32 . The client app is built with delphi 32 bits so the clients will connect to that DB in 64 bits. Is it possible ? Thanks
Re: [firebird-support] Primary key composed by multiple integer fields
Thank you very much
Re: [firebird-support] Primary key composed by multiple integer fields
Thanks for the answers All fields contained in the PK are updated is that mean by operations affected ? Thanks for explanation but if case of decreasing does we need to reindex table manually ?
[firebird-support] Primary key composed by multiple integer fields
Hello, I need to know please if it is allowed that a primary key has multiple integer fields ? and only that. For example a PK that has seven integer fields will it decrease the performance table ? I am using FB 3. Thanks
RE: [firebird-support] Re: MON$REMOTE_ADDRESS
It's ok for me I understand now Thank you very much
Re: [firebird-support] Re: MON$REMOTE_ADDRESS
Thank you
[firebird-support] Re: MON$REMOTE_ADDRESS
In the client I use the port number 3050 in the connection string. Is FB use multiple port as the same time ? Is there a document which explains that ? Thanks
[firebird-support] MON$REMOTE_ADDRESS
Hello, Can you please, tell what mean the second part number of the IP (2711) after the slash when I select MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS ? 192.168.1.20/2711 Thank you.
RE: [firebird-support] Firing event when deleting in MON$ATTACHMENTS
I am sorry and I am confused. I registered event with 'DISCONNECT_USER' instead of 'DISCONNECT_USER]' I forgot the bracket and now it works for the 1st scenario and not for the second because I was wrong with using 'DISCONNECT_USER]'||:USR in the PS and certaintly doesn't work but It give me an idea. I will try from the client to loop through all users existing in the DB and register them with 'DISCONNECT_USER]'+Users[i] I don't know if I can register more than hundred users in the client in case the number of users will increase in the futur. Thanks.
Re: [firebird-support] Firing event when deleting in MON$ATTACHMENTS
Hello, Yes in my delphi application I registered the event name I tried POST_EVENT in a stored procedure but without DELETE FROM MON$ATTACHMENTS statement sometimes it is fired sometimes not. I didn't understand why When I write the event name like this : 1-POST_EVENT 'DISCONNECT_USER'; It is fired 2-POST_EVENT 'DISCONNECT_USER]'||:USR; (I splitt the event name using "]" in the client in order to retrieve the event name part and the username part) It is not fired And when I back to 1 when it worked it is not fired at all Anyway I kept that idea of running POST_EVENT in SP and after waiting I execute the delete statement. I consider that my problem is solved Thank you very much to all
[firebird-support] Firing event when deleting in MON$ATTACHMENTS
Hello, I am deleting user connected with: DELETE FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = :ID_CONNECTION; I need to use POST_EVENT in order to notify user that he has been disconnected but don't found a trigger for MON$ATTACHMENTS to do it. Can you please help me ? Thanks
RE: [firebird-support] Hiding password step within innosetup
Thank you very much Sean
RE: [firebird-support] Hiding password step within innosetup
Thanks Sean for your suggestion but which way do I create my own project ?
Sorry I did not explained the whole context. In innosetup script I include FB
executable file with my app with the line below and change the SYSDBA password
too:
Filename: "{app}\firebird\Firebird-3.0.1.32609_0_Win32.exe"; Parameters:
"/SYSDBAPASSWORD=XX /SP- /SILENT /NOGDS32 /NOCANCEL
/DIR={code:GetServerPath} LOG={app}\install_log.txt /
/COMPONENTS=""ServerComponent,ServerComponent\SuperServerComponent,DevAdminComponent,ClientComponent""";
Description: Launch application;StatusMsg: Installation du serveur de base de
données;Check: GetParam1
By the pre-created security database is that mean changing SYSDBA password as
I done above ?
Is that enough ?
Thanks.
[firebird-support] Hiding password step within innosetup
Hello, I need to know please if I can hide the password step when installing my app with Firebird 3 within innosetup. With FB 2 I can use the following parameter : /SYSDBAPASSWORD, /SP- /VERYSILENT, /NOGDS32, /NOCANCEL, LOG We can only assign password in the SYSDBA step and don't know if exists the possibilty of hiding the step. Thanks.
[firebird-support] Proposition for displaying field name in error server message
Hello, When happen the following message: "arithmetic exception, numeric overflow, or string truncation string right truncation" I am wondering if it is possible to the Firebird team to add in the message the field name which is concerned by the error. It is not a problem for people who had experienced with Firebird but when people are beginner in FB DBMS I think it is so hard for them to know what field of what table is concerned by the error especially when a table has many field. I think, that way will encourage people to stay with FB and not to left it for other DBMS. If this group is not approriate for this subject, can you please, show me where I can post it ? Thank you very much and sorry for my english.
