RE: [firebird-support] Very Strange: Query giving different results
Additional information: 3 – About point 3, I have recreated the FK regarding field IDESTADO in the table and now I am obtaining the record when fetching with IDESTADO = 3. Nevertheless I am still not capable of solving point 1, I am not fetching that record from ISQL nor Flamerobin, just from the .NET provider. De: Alexis (PROINTER) [mailto:ale...@prointersl.com] Enviado el: martes, 06 de octubre de 2015 9:31 Para: 'firebird-support@yahoogroups.com' Asunto: RE: [firebird-support] Very Strange: Query giving different results Hi, thanks for your response I put the table script in txt. CREATE TABLE CABECERA_PROCESO ( IDCABECERA Bigint NOT NULL, COD_GESTION Varchar(15) NOT NULL, IDPROCESO Smallint NOT NULL, IDSUBPROCESO Smallint NOT NULL, COD_ALBARAN Varchar(50), IDESTADO Smallint DEFAULT 1 NOT NULL, FECHA_Y_HORA Timestamp, IDUSER Smallint, FECHA_INICIO Timestamp, FECHA_FIN Timestamp, COD_CLIENTE Varchar(10), COD_PROVEEDOR Varchar(10), COD_INTERNO Varchar(10), OBSERVACIONES Varchar(500), NUM_CARGA Bigint, TIPO_INVENTARIO Varchar(10), CONSTRAINT PK_CABECERA_PROCESO PRIMARY KEY (IDCABECERA), CONSTRAINT U_CABECERA_PROCESO UNIQUE (COD_GESTION) ); ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PROCESO_ESTADOS FOREIGN KEY (IDESTADO) REFERENCES ESTADOS (IDESTADO) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PROCESO_USERS FOREIGN KEY (IDUSER) REFERENCES USERS (IDUSER) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PROCES_CLIENTES FOREIGN KEY (COD_CLIENTE) REFERENCES CLIENTES (COD_CLIENTE) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PROCES_PROCESOS FOREIGN KEY (IDPROCESO) REFERENCES PROCESOS (IDPROCESO) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PRO_PROVEEDORES FOREIGN KEY (COD_PROVEEDOR) REFERENCES PROVEEDORES (COD_PROVEEDOR) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PRO_SUBPROCESOS FOREIGN KEY (IDSUBPROCESO) REFERENCES SUBPROCESOS (IDSUBPROCESO) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PR_TIPOS_INTER FOREIGN KEY (COD_INTERNO) REFERENCES TIPOS_INTERNO (COD_INTERNO) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PR_TIPOS_INVEN FOREIGN KEY (TIPO_INVENTARIO) REFERENCES TIPOS_INVENTARIO (COD_TIPOINV) ON UPDATE CASCADE ON DELETE CASCADE; GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON CABECERA_PROCESO TO SYSDBA WITH GRANT OPTION; At the moment I have had no luck fetching by PK from isql, I am going to try entering by secondary others. De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Enviado el: lunes, 05 de octubre de 2015 22:25 Para: firebird-support@yahoogroups.com Asunto: Re: [firebird-support] Very Strange: Query giving different results On Mon, Oct 5, 2015 at 12:59 PM, 'Alexis (PROINTER)' ale...@prointersl.com [firebird-support] wrote: Hi, we have found a very strange problem with our firebird database, a record on a table of firebird databes is not always beeing returned: 1 - Using flamerobin we are never fetching record 1648 of our datble. 2 - Using an application we are developing (with vb.net on vs2010) the record is fetched: 3 - If we query from the application with condition IDESTADO = 3 wich the desired record has that value that record is also not fetched: Yahoo ate your table structure, so this is just a guess. You may have an index that's missing an entry - that shouldn't happen, of course. Using isql, try fetching the record by primary key value, primary key concatenated with an empty string (if it's a string) or plus zero (if it's a number). They try the same thing on any secondary indexes. Good luck, Ann
RE: [firebird-support] Very Strange: Query giving different results
Hi, thanks for your response I put the table script in txt. CREATE TABLE CABECERA_PROCESO ( IDCABECERA Bigint NOT NULL, COD_GESTION Varchar(15) NOT NULL, IDPROCESO Smallint NOT NULL, IDSUBPROCESO Smallint NOT NULL, COD_ALBARAN Varchar(50), IDESTADO Smallint DEFAULT 1 NOT NULL, FECHA_Y_HORA Timestamp, IDUSER Smallint, FECHA_INICIO Timestamp, FECHA_FIN Timestamp, COD_CLIENTE Varchar(10), COD_PROVEEDOR Varchar(10), COD_INTERNO Varchar(10), OBSERVACIONES Varchar(500), NUM_CARGA Bigint, TIPO_INVENTARIO Varchar(10), CONSTRAINT PK_CABECERA_PROCESO PRIMARY KEY (IDCABECERA), CONSTRAINT U_CABECERA_PROCESO UNIQUE (COD_GESTION) ); ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PROCESO_ESTADOS FOREIGN KEY (IDESTADO) REFERENCES ESTADOS (IDESTADO) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PROCESO_USERS FOREIGN KEY (IDUSER) REFERENCES USERS (IDUSER) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PROCES_CLIENTES FOREIGN KEY (COD_CLIENTE) REFERENCES CLIENTES (COD_CLIENTE) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PROCES_PROCESOS FOREIGN KEY (IDPROCESO) REFERENCES PROCESOS (IDPROCESO) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PRO_PROVEEDORES FOREIGN KEY (COD_PROVEEDOR) REFERENCES PROVEEDORES (COD_PROVEEDOR) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PRO_SUBPROCESOS FOREIGN KEY (IDSUBPROCESO) REFERENCES SUBPROCESOS (IDSUBPROCESO) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PR_TIPOS_INTER FOREIGN KEY (COD_INTERNO) REFERENCES TIPOS_INTERNO (COD_INTERNO) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CABECERA_PROCESO ADD CONSTRAINT FK_CABECERA_PR_TIPOS_INVEN FOREIGN KEY (TIPO_INVENTARIO) REFERENCES TIPOS_INVENTARIO (COD_TIPOINV) ON UPDATE CASCADE ON DELETE CASCADE; GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON CABECERA_PROCESO TO SYSDBA WITH GRANT OPTION; At the moment I have had no luck fetching by PK from isql, I am going to try entering by secondary others. De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Enviado el: lunes, 05 de octubre de 2015 22:25 Para: firebird-support@yahoogroups.com Asunto: Re: [firebird-support] Very Strange: Query giving different results On Mon, Oct 5, 2015 at 12:59 PM, 'Alexis (PROINTER)' ale...@prointersl.com [firebird-support] wrote: Hi, we have found a very strange problem with our firebird database, a record on a table of firebird databes is not always beeing returned: 1 - Using flamerobin we are never fetching record 1648 of our datble. 2 - Using an application we are developing (with vb.net on vs2010) the record is fetched: 3 - If we query from the application with condition IDESTADO = 3 wich the desired record has that value that record is also not fetched: Yahoo ate your table structure, so this is just a guess. You may have an index that's missing an entry - that shouldn't happen, of course. Using isql, try fetching the record by primary key value, primary key concatenated with an empty string (if it's a string) or plus zero (if it's a number). They try the same thing on any secondary indexes. Good luck, Ann
Re: [firebird-support] Very Strange: Query giving different results
On Mon, Oct 5, 2015 at 12:59 PM, 'Alexis (PROINTER)' ale...@prointersl.com [firebird-support] wrote: > Hi, we have found a very strange problem with our firebird database, a > record on a table of firebird databes is not always beeing returned: > > 1 - Using flamerobin we are never fetching record 1648 of our datble. > > 2 - Using an application we are developing (with vb.net on vs2010) the > record is fetched: > > 3 - If we query from the application with condition IDESTADO = 3 wich the > desired record has that value that record is also not fetched: > > Yahoo ate your table structure, so this is just a guess. You may have an index that's missing an entry - that shouldn't happen, of course. Using isql, try fetching the record by primary key value, primary key concatenated with an empty string (if it's a string) or plus zero (if it's a number). They try the same thing on any secondary indexes. Good luck, Ann
[firebird-support] Very Strange: Query giving different results
Hi, we have found a very strange problem with our firebird database, a record on a table of firebird databes is not always beeing returned: 1 - Using flamerobin we are never fetching record 1648 of our datble. 2 - Using an application we are developing (with vb.net on vs2010) the record is fetched: 3 - If we query from the application with condition IDESTADO = 3 wich the desired record has that value that record is also not fetched: 4 - This is the design of our table: If anyone can give me a clue about why is this happening we will truly appreciate. Thanks, Alexis. [Non-text portions of this message have been removed]