[firebird-support] Re: DeadLock Conflict
Hi all I experience the same problem. Is there a tutorial to track theese kind of problems? tks Razvan
Re: [firebird-support] Re: Differences when adding a Primary Key
Hello Ann Well, that was not the case, because without the word CONSTRAINT has failed and with that word has worked fine and I was the only user of the database at that moment. But no problem, I know the solution: always use the word CONSTRAINT. Greetings. Walter. On Sun, Aug 23, 2015 at 12:37 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, have you sample database to reproduce this. How you test this difference? Have you some backup of database and restore it and then test this two options? regards, Karol Bieniaszewski *From:* mailto:firebird-support@yahoogroups.com firebird-support@yahoogroups.com *Sent:* Sunday, August 23, 2015 5:48 AM *To:* firebird-support@yahoogroups.com *Subject:* Re: [firebird-support] Re: Differences when adding a Primary Key Nobody knows the answer? Well, then I shall add this to the Firebird's mysteries. Greetings. Walter. On Fri, Aug 21, 2015 at 4:47 PM, Walter R. Ojeda Valiente sistemas2000profesio...@gmail.com wrote: Hello Ann Well, the world has not falled out, nothing so dangerous, the error message is: Cannot commit transaction: The insert failed because a column definition includes validation constraints. validation error for column ID, value *** null ***. The question is: Why without using CONSTRAINT appears that message but using CONSTRAINT all works fine? And there are not row/s with a NULL value in the column ID. At least, they are not showed with the following query: SELECT * FROM MyTable WHERE ID IS NULL So, it seems very strange to me. The logic for me is: both works or both fails, but why one fails and the other works? Greetings. Walter. On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com [firebird-support] firebird-support@yahoogroups.com wrote: On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Without using CONSTRAINT doesn't work and in such case the name is choosed by Firebird, not for me. For add a Primary Key to a table we can write: ALTER TABLE MyTable ADD PRIMARY KEY (ID); or we can write: ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID); In the first case, the Firebird puts the name of the Primary Key, in the second case we choose that name, but... Why sometimes the first case fails and the second case always work? But (just sometimes) the first case fails. How does it fail? What's the error message? Or does it kill the connection? Crash the server? Freeze the O/S? Set the machine on fire? Cheers, Ann
Re: [firebird-support] Re: Differences when adding a Primary Key
Hello Karol No, I don't have a backup previous to add the Primary Key then I can use for reproduce the problem. Using ISQL, one time I try to add the Primary Key without using the word CONSTRAINT, seconds later I try again but using the word CONSTRAINT. The first time had failed, the second time worked fine. I was curious ... why? I think that a possibility is that the name INTEG_47 was previously used but not eliminated of the metadata. INTEG_47 was the name of the failed Primary Key, as ISQL shows me. The table had not NULL neither duplicated values in the column ID. Greetings. Walter. On Sun, Aug 23, 2015 at 12:37 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, have you sample database to reproduce this. How you test this difference? Have you some backup of database and restore it and then test this two options? regards, Karol Bieniaszewski *From:* mailto:firebird-support@yahoogroups.com firebird-support@yahoogroups.com *Sent:* Sunday, August 23, 2015 5:48 AM *To:* firebird-support@yahoogroups.com *Subject:* Re: [firebird-support] Re: Differences when adding a Primary Key Nobody knows the answer? Well, then I shall add this to the Firebird's mysteries. Greetings. Walter. On Fri, Aug 21, 2015 at 4:47 PM, Walter R. Ojeda Valiente sistemas2000profesio...@gmail.com wrote: Hello Ann Well, the world has not falled out, nothing so dangerous, the error message is: Cannot commit transaction: The insert failed because a column definition includes validation constraints. validation error for column ID, value *** null ***. The question is: Why without using CONSTRAINT appears that message but using CONSTRAINT all works fine? And there are not row/s with a NULL value in the column ID. At least, they are not showed with the following query: SELECT * FROM MyTable WHERE ID IS NULL So, it seems very strange to me. The logic for me is: both works or both fails, but why one fails and the other works? Greetings. Walter. On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com [firebird-support] firebird-support@yahoogroups.com wrote: On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Without using CONSTRAINT doesn't work and in such case the name is choosed by Firebird, not for me. For add a Primary Key to a table we can write: ALTER TABLE MyTable ADD PRIMARY KEY (ID); or we can write: ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID); In the first case, the Firebird puts the name of the Primary Key, in the second case we choose that name, but... Why sometimes the first case fails and the second case always work? But (just sometimes) the first case fails. How does it fail? What's the error message? Or does it kill the connection? Crash the server? Freeze the O/S? Set the machine on fire? Cheers, Ann
Re: [firebird-support] Re: DeadLock Conflict
Hi, Please provide full error text and detailed description of the problem. Regards, Alexey Kovyazin IBSurgeon Hi all I experience the same problem. Is there a tutorial to track theese kind of problems? tks Razvan
Re: [firebird-support] Error in order by clause
As far as I can tell it sorts as it should. What sort are you expecting? Mark - Reply message - Van: 'Bogdan' bog...@mordicom.si [firebird-support] firebird-support@yahoogroups.com Aan: firebird-support@yahoogroups.com Onderwerp: [firebird-support] Error in order by clause Datum: ma, aug. 24, 2015 10:37 Hi to all Server: Firebird 2.5.4. I have following stored procedure: ALTER PROCEDURE TEST_ (I SMALLINT)RETURNS (SUMA NUMERIC(15,2),A NUMERIC(15,2),B NUMERIC(15,2),C INTEGER,D VARCHAR(10),E VARCHAR(10))ASbeginfor select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from tgroup by c, d, eorder by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDINGinto :suma, :a, :b, :c, :d, :edo suspend;end If i run it with paraneter i = 0 it gives wrong sort order.Results: SUMA A B C D E-1,11,2 2,3 1 11 3-1,83,4 5,2 2 11 30 3 3 2 12 212 14 2 1 11 1Otherwise the sort order is ok. This propagates only when there is iif within iif clause. Am i doing something wrong or it is a bug ? Best regards Bogdan = complete script: SET SQL DIALECT 3; /**// Stored procedures //**/ SET TERM ^ ; CREATE PROCEDURE TEST_ (I SMALLINT)RETURNS (SUMA NUMERIC(15,2),A NUMERIC(15,2),B NUMERIC(15,2),C INTEGER,D VARCHAR(10),E VARCHAR(10))ASBEGIN SUSPEND;END^ SET TERM ; ^/**// Tables //**/ CREATE TABLE (A NUMERIC(15,2),B NUMERIC(15,2),C INTEGER, D VARCHAR(10) COLLATE PXW_SLOV,E VARCHAR(10) COLLATE PXW_SLOV); /**// Stored procedures //**/ SET TERM ^ ; CREATE OR ALTER PROCEDURE TEST_ (I SMALLINT)RETURNS ( SUMA NUMERIC(15,2),A NUMERIC(15,2),B NUMERIC(15,2),C INTEGER,D VARCHAR(10),E VARCHAR(10))ASbeginfor select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from tgroup by c, d, eorder by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDINGinto :suma, :a, :b, :c, :d, :edo suspend;end^SET TERM ; ^ INSERT INTO (A, B, C, D, E) VALUES (1.2, 2.3, 1, '11', '3');INSERT INTO (A, B, C, D, E) VALUES (3.4, 5.2, 2, '11', '3');INSERT INTO (A, B, C, D, E) VALUES (0, 1, 2, '12', '2');INSERT INTO (A, B, C, D, E) VALUES (3, 2, 2, '12', '2');INSERT INTO (A, B, C, D, E) VALUES (14, 2, 1, '11', '1'); COMMIT WORK; Ta e-pošta je bila pregledana z Avast protivirusnim programom. www.avast.com
RE: [firebird-support] Error in order by clause
It should be SUMA A B C D E 12 14 2 1 11 1 0 3 3 2 12 2 -1,11,2 2,3 1 11 3 -1,83,4 5,2 2 11 3 Suma descending Regards Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Monday, August 24, 2015 6:26 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Error in order by clause As far as I can tell it sorts as it should. What sort are you expecting? Mark - Reply message - Van: 'Bogdan' bog...@mordicom.si [firebird-support] firebird-support@yahoogroups.com Aan: firebird-support@yahoogroups.com Onderwerp: [firebird-support] Error in order by clause Datum: ma, aug. 24, 2015 10:37 Hi to all Server: Firebird 2.5.4. I have following stored procedure: ALTER PROCEDURE TEST_ ( I SMALLINT) RETURNS ( SUMA NUMERIC(15,2), A NUMERIC(15,2), B NUMERIC(15,2), C INTEGER, D VARCHAR(10), E VARCHAR(10)) AS begin for select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from t group by c, d, e order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING into :suma, :a, :b, :c, :d, :e do suspend; end If i run it with paraneter i = 0 it gives wrong sort order. Results: SUMA A B C D E -1,11,2 2,3 1 11 3 -1,83,4 5,2 2 11 3 0 3 3 2 12 2 12 14 2 1 11 1 Otherwise the sort order is ok. This propagates only when there is iif within iif clause. Am i doing something wrong or it is a bug ? Best regards Bogdan = complete script: SET SQL DIALECT 3; /**/ / Stored procedures / /**/ SET TERM ^ ; CREATE PROCEDURE TEST_ ( I SMALLINT) RETURNS ( SUMA NUMERIC(15,2), A NUMERIC(15,2), B NUMERIC(15,2), C INTEGER, D VARCHAR(10), E VARCHAR(10)) AS BEGIN SUSPEND; END^ SET TERM ; ^ /**/ /Tables/ /**/ CREATE TABLE ( A NUMERIC(15,2), B NUMERIC(15,2), C INTEGER, D VARCHAR(10) COLLATE PXW_SLOV, E VARCHAR(10) COLLATE PXW_SLOV ); /**/ / Stored procedures / /**/ SET TERM ^ ; CREATE OR ALTER PROCEDURE TEST_ ( I SMALLINT) RETURNS ( SUMA NUMERIC(15,2), A NUMERIC(15,2), B NUMERIC(15,2), C INTEGER, D VARCHAR(10), E VARCHAR(10)) AS begin for select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from t group by c, d, e order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING into :suma, :a, :b, :c, :d, :e do suspend; end^ SET TERM ; ^ INSERT INTO (A, B, C, D, E) VALUES (1.2, 2.3, 1, '11', '3'); INSERT INTO (A, B, C, D, E) VALUES (3.4, 5.2, 2, '11', '3'); INSERT INTO (A, B, C, D, E) VALUES (0, 1, 2, '12', '2'); INSERT INTO (A, B, C, D, E) VALUES (3, 2, 2, '12', '2'); INSERT INTO (A, B, C, D, E) VALUES (14, 2, 1, '11', '1'); COMMIT WORK; _ https://www.avast.com/antivirus Avast logo Ta e-pošta je bila pregledana z Avast protivirusnim programom. www.avast.com https://www.avast.com/antivirus --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
Re: [firebird-support] Expression index for use with the GROUP BY clause
Hello Karol Thank you for your answer. The query is a very simplified one, just for show the idea. I can create an index as the following: CREATE INDEX IDX_ASIENTOSDET1 ON ASIENTOSDET COMPUTED BY ( CAST(ASI_ANOEJE AS CHAR(5)) || CAST(ASI_CODSUC AS CHAR(5)) || CAST(EXTRACT(MONTH FROM (SELECT ASC_FECHAX FROM ASIENTOSCAB WHERE ASC_CODSUC = ASI_CODSUC AND ASC_IDENTI = ASI_IDECAB)) AS CHAR(2)) || ASI_NUMCUE || ASI_NUMSUB ); But is is not used in the PLAN. I had tried several alternatives but without success until now. Using CAST(), without using CAST() and so on. I don't want to add a column ASI_FECHAX to my table ASIENTOSDET if I can avoid create it, but I need rows grouped by that column. ASIENTOSCAB and ASIENTOSDET have a parent-child relationship, where ASIENTOSCAB is the parent. Greetings. Walter. On Mon, Aug 24, 2015 at 1:40 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, No – you can not create single index on more then one table But you really need it? How big is resultset? I do not see any filter in this query no HAVING nor WHERE regards, Karol Bieniaszewski *From:* mailto:firebird-support@yahoogroups.com firebird-support@yahoogroups.com *Sent:* Monday, August 24, 2015 7:28 PM *To:* firebird-support@yahoogroups.com *Subject:* [firebird-support] Expression index for use with the GROUP BY clause Hello everybody I had the following query: SELECT D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES, D.ASI_NUMCUE, D.ASI_NUMSUB FROM ASIENTOSDET D JOIN ASIENTOSCAB C ON D.ASI_CODSUC = C.ASC_CODSUC AND D.ASI_IDECAB = C.ASC_IDENTI GROUP BY D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX), D.ASI_NUMCUE, D.ASI_NUMSUB Is it possible to have an expression index for use here with the GROUP BY clause? The PLAN is the following: PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB))) As you can see there is a SORT there caused by the GROUP BY clause. ASI_ANOEJE is SMALLINT ASI_CODSUC is SMALLINT ASC_FECHAX is DATE ASI_NUMCUE is VARCHAR(16) ASI_NUMSUB is CHAR(5) I'm using Firebird 2.5.4 Greetings. Walter.
Re: [firebird-support] Re: Differences when adding a Primary Key
Hi, what about drop this PK do backup restore and try again? P.S. If you previously name some constraint as INTEG... then conflict with name can appear regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Monday, August 24, 2015 5:00 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: Differences when adding a Primary Key Hello Karol No, I don't have a backup previous to add the Primary Key then I can use for reproduce the problem. Using ISQL, one time I try to add the Primary Key without using the word CONSTRAINT, seconds later I try again but using the word CONSTRAINT. The first time had failed, the second time worked fine. I was curious ... why? I think that a possibility is that the name INTEG_47 was previously used but not eliminated of the metadata. INTEG_47 was the name of the failed Primary Key, as ISQL shows me. The table had not NULL neither duplicated values in the column ID. Greetings. Walter. On Sun, Aug 23, 2015 at 12:37 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, have you sample database to reproduce this. How you test this difference? Have you some backup of database and restore it and then test this two options? regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Sunday, August 23, 2015 5:48 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: Differences when adding a Primary Key Nobody knows the answer? Well, then I shall add this to the Firebird's mysteries. Greetings. Walter. On Fri, Aug 21, 2015 at 4:47 PM, Walter R. Ojeda Valiente sistemas2000profesio...@gmail.com wrote: Hello Ann Well, the world has not falled out, nothing so dangerous, the error message is: Cannot commit transaction: The insert failed because a column definition includes validation constraints. validation error for column ID, value *** null ***. The question is: Why without using CONSTRAINT appears that message but using CONSTRAINT all works fine? And there are not row/s with a NULL value in the column ID. At least, they are not showed with the following query: SELECT * FROM MyTable WHERE ID IS NULL So, it seems very strange to me. The logic for me is: both works or both fails, but why one fails and the other works? Greetings. Walter. On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com [firebird-support] firebird-support@yahoogroups.com wrote: On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Without using CONSTRAINT doesn't work and in such case the name is choosed by Firebird, not for me. For add a Primary Key to a table we can write: ALTER TABLE MyTable ADD PRIMARY KEY (ID); or we can write: ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID); In the first case, the Firebird puts the name of the Primary Key, in the second case we choose that name, but... Why sometimes the first case fails and the second case always work? But (just sometimes) the first case fails. How does it fail? What's the error message? Or does it kill the connection? Crash the server? Freeze the O/S? Set the machine on fire? Cheers, Ann
Re: [firebird-support] Wrong sort results
On 21/08/15 14:17, 'Bogdan' bog...@mordicom.si [firebird-support] wrote: order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING If i run it with i = 0 it gives wrong sort order. SUMA A B C D E -1,11,2 2,3 1 11 3 -1,83,4 5,2 2 11 3 0 3 3 2 12 2 12 14 2 1 11 1 You are using different results in the ORDER BY clause. That's not allowed. Your t.c is an INTEGER and your E is a varchar but your sum are NUMERIC. Even though the result is always the same (dependent on I), Firebird doesn't know that. For Firebird the result CAN VARY between records and that's not allowed. So you need to cast them all to the same type. For instance this will work: order by iif(:i = 1, cast(t.c as numeric), IIF(:i = 0, sum(t.a) - sum(t.b), cast(t.e as numeric))) DESCENDING But if you want to order by E (=varchar) maybe you should cast to varchar but in that case you need to make sure the ordering is done correctly (with adding spaces in front of the sum to order right)
Re: [firebird-support] Expression index for use with the GROUP BY clause
Hi, No – you can not create single index on more then one table But you really need it? How big is resultset? I do not see any filter in this query no HAVING nor WHERE regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Monday, August 24, 2015 7:28 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Expression index for use with the GROUP BY clause Hello everybody I had the following query: SELECT D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES, D.ASI_NUMCUE, D.ASI_NUMSUB FROM ASIENTOSDET D JOIN ASIENTOSCAB C ON D.ASI_CODSUC = C.ASC_CODSUC AND D.ASI_IDECAB = C.ASC_IDENTI GROUP BY D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX), D.ASI_NUMCUE, D.ASI_NUMSUB Is it possible to have an expression index for use here with the GROUP BY clause? The PLAN is the following: PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB))) As you can see there is a SORT there caused by the GROUP BY clause. ASI_ANOEJE is SMALLINT ASI_CODSUC is SMALLINT ASC_FECHAX is DATE ASI_NUMCUE is VARCHAR(16) ASI_NUMSUB is CHAR(5) I'm using Firebird 2.5.4 Greetings. Walter.
Re: [firebird-support] Re: Differences when adding a Primary Key
Hello Karol As I had said before, it is a mystery for me. The Primary Keys created by me always begin with the PK_ characters, if they begin with INTEG_ is because were created by ISQL. But not problem at all, using CONSTRAINT it is solved. Greetings. Walter. On Mon, Aug 24, 2015 at 12:51 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, what about drop this PK do backup restore and try again? P.S. If you previously name some constraint as INTEG... then conflict with name can appear regards, Karol Bieniaszewski *From:* mailto:firebird-support@yahoogroups.com firebird-support@yahoogroups.com *Sent:* Monday, August 24, 2015 5:00 PM *To:* firebird-support@yahoogroups.com *Subject:* Re: [firebird-support] Re: Differences when adding a Primary Key Hello Karol No, I don't have a backup previous to add the Primary Key then I can use for reproduce the problem. Using ISQL, one time I try to add the Primary Key without using the word CONSTRAINT, seconds later I try again but using the word CONSTRAINT. The first time had failed, the second time worked fine. I was curious ... why? I think that a possibility is that the name INTEG_47 was previously used but not eliminated of the metadata. INTEG_47 was the name of the failed Primary Key, as ISQL shows me. The table had not NULL neither duplicated values in the column ID. Greetings. Walter. On Sun, Aug 23, 2015 at 12:37 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, have you sample database to reproduce this. How you test this difference? Have you some backup of database and restore it and then test this two options? regards, Karol Bieniaszewski *From:* mailto:firebird-support@yahoogroups.com firebird-support@yahoogroups.com *Sent:* Sunday, August 23, 2015 5:48 AM *To:* firebird-support@yahoogroups.com *Subject:* Re: [firebird-support] Re: Differences when adding a Primary Key Nobody knows the answer? Well, then I shall add this to the Firebird's mysteries. Greetings. Walter. On Fri, Aug 21, 2015 at 4:47 PM, Walter R. Ojeda Valiente sistemas2000profesio...@gmail.com wrote: Hello Ann Well, the world has not falled out, nothing so dangerous, the error message is: Cannot commit transaction: The insert failed because a column definition includes validation constraints. validation error for column ID, value *** null ***. The question is: Why without using CONSTRAINT appears that message but using CONSTRAINT all works fine? And there are not row/s with a NULL value in the column ID. At least, they are not showed with the following query: SELECT * FROM MyTable WHERE ID IS NULL So, it seems very strange to me. The logic for me is: both works or both fails, but why one fails and the other works? Greetings. Walter. On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com [firebird-support] firebird-support@yahoogroups.com wrote: On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Without using CONSTRAINT doesn't work and in such case the name is choosed by Firebird, not for me. For add a Primary Key to a table we can write: ALTER TABLE MyTable ADD PRIMARY KEY (ID); or we can write: ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID); In the first case, the Firebird puts the name of the Primary Key, in the second case we choose that name, but... Why sometimes the first case fails and the second case always work? But (just sometimes) the first case fails. How does it fail? What's the error message? Or does it kill the connection? Crash the server? Freeze the O/S? Set the machine on fire? Cheers, Ann
[firebird-support] Expression index for use with the GROUP BY clause
Hello everybody I had the following query: SELECT D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES, D.ASI_NUMCUE, D.ASI_NUMSUB FROM ASIENTOSDET D JOIN ASIENTOSCAB C ON D.ASI_CODSUC = C.ASC_CODSUC AND D.ASI_IDECAB = C.ASC_IDENTI GROUP BY D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX), D.ASI_NUMCUE, D.ASI_NUMSUB Is it possible to have an expression index for use here with the GROUP BY clause? The PLAN is the following: PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB))) As you can see there is a SORT there caused by the GROUP BY clause. ASI_ANOEJE is SMALLINT ASI_CODSUC is SMALLINT ASC_FECHAX is DATE ASI_NUMCUE is VARCHAR(16) ASI_NUMSUB is CHAR(5) I'm using Firebird 2.5.4 Greetings. Walter.
[firebird-support] unable to complete network request localhost
Hi- I used this to get by the error: su: failed to execute /bin/nologin: No such file or directory I changed to sbin instead of bin in /etc/passwd I also log in like this for firebird user: # su - firebird -s /bin/bash Now I am getting under the firebird user: I was told to try localhost like this by google: $ isql-fb SQL connect localhost: /var/lib/ firebird/ 2.5/data/ employee. fdb user 'SYSDBA' password 'SYSDBApassword'; Unable to complete network request to host localhost. -Failed to establish a connection. Any tips? THANKS- 3rdshiftcoder
[firebird-support] can't log-in as user account firebird
Hi- I changed firebird to #: passwd myfb at the command-line. At su firebird I get: su: failed to execute /bin/nologin: No such file or directory firebird:x:901:902::/:/bin/nologin not sure what I am doing wrong. I am using firebird 2.5.4 fedora xfce classic. Please assist! Addendum: with the root log-in I can select data from sample db. I have SYSDBA account setup with a password and will do a new account once I get this straightened out. THANKS!
[firebird-support] Re: can't log-in as user account firebird
Hi- I figured this question out. An updated NEW question is coming. Thanks for any assistance. thanks!
Re: [firebird-support] Expression index for use with the GROUP BY clause
Hi, as i say previously you can not do this as index can not be updated if something was changed in ASIENTOSCAB. And any subseclect is not recognized by plan parser as expression index def regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Monday, August 24, 2015 7:55 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Expression index for use with the GROUP BY clause Hello Karol Thank you for your answer. The query is a very simplified one, just for show the idea. I can create an index as the following: CREATE INDEX IDX_ASIENTOSDET1 ON ASIENTOSDET COMPUTED BY ( CAST(ASI_ANOEJE AS CHAR(5)) || CAST(ASI_CODSUC AS CHAR(5)) || CAST(EXTRACT(MONTH FROM (SELECT ASC_FECHAX FROM ASIENTOSCAB WHERE ASC_CODSUC = ASI_CODSUC AND ASC_IDENTI = ASI_IDECAB)) AS CHAR(2)) || ASI_NUMCUE || ASI_NUMSUB ); But is is not used in the PLAN. I had tried several alternatives but without success until now. Using CAST(), without using CAST() and so on. I don't want to add a column ASI_FECHAX to my table ASIENTOSDET if I can avoid create it, but I need rows grouped by that column. ASIENTOSCAB and ASIENTOSDET have a parent-child relationship, where ASIENTOSCAB is the parent. Greetings. Walter. On Mon, Aug 24, 2015 at 1:40 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, No – you can not create single index on more then one table But you really need it? How big is resultset? I do not see any filter in this query no HAVING nor WHERE regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Monday, August 24, 2015 7:28 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Expression index for use with the GROUP BY clause Hello everybody I had the following query: SELECT D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES, D.ASI_NUMCUE, D.ASI_NUMSUB FROM ASIENTOSDET D JOIN ASIENTOSCAB C ON D.ASI_CODSUC = C.ASC_CODSUC AND D.ASI_IDECAB = C.ASC_IDENTI GROUP BY D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX), D.ASI_NUMCUE, D.ASI_NUMSUB Is it possible to have an expression index for use here with the GROUP BY clause? The PLAN is the following: PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB))) As you can see there is a SORT there caused by the GROUP BY clause. ASI_ANOEJE is SMALLINT ASI_CODSUC is SMALLINT ASC_FECHAX is DATE ASI_NUMCUE is VARCHAR(16) ASI_NUMSUB is CHAR(5) I'm using Firebird 2.5.4 Greetings. Walter.
Re: [firebird-support] Expression index for use with the GROUP BY clause
Hi, as i say previously you can not do this as index can not be updated if something was changed in ASIENTOSCAB. And any subseclect is not recognized by plan parser as expression index def regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Monday, August 24, 2015 7:55 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Expression index for use with the GROUP BY clause Hello Karol Thank you for your answer. The query is a very simplified one, just for show the idea. I can create an index as the following: CREATE INDEX IDX_ASIENTOSDET1 ON ASIENTOSDET COMPUTED BY ( CAST(ASI_ANOEJE AS CHAR(5)) || CAST(ASI_CODSUC AS CHAR(5)) || CAST(EXTRACT(MONTH FROM (SELECT ASC_FECHAX FROM ASIENTOSCAB WHERE ASC_CODSUC = ASI_CODSUC AND ASC_IDENTI = ASI_IDECAB)) AS CHAR(2)) || ASI_NUMCUE || ASI_NUMSUB ); But is is not used in the PLAN. I had tried several alternatives but without success until now. Using CAST(), without using CAST() and so on. I don't want to add a column ASI_FECHAX to my table ASIENTOSDET if I can avoid create it, but I need rows grouped by that column. ASIENTOSCAB and ASIENTOSDET have a parent-child relationship, where ASIENTOSCAB is the parent. Greetings. Walter. On Mon, Aug 24, 2015 at 1:40 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, No – you can not create single index on more then one table But you really need it? How big is resultset? I do not see any filter in this query no HAVING nor WHERE regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Monday, August 24, 2015 7:28 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Expression index for use with the GROUP BY clause Hello everybody I had the following query: SELECT D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES, D.ASI_NUMCUE, D.ASI_NUMSUB FROM ASIENTOSDET D JOIN ASIENTOSCAB C ON D.ASI_CODSUC = C.ASC_CODSUC AND D.ASI_IDECAB = C.ASC_IDENTI GROUP BY D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX), D.ASI_NUMCUE, D.ASI_NUMSUB Is it possible to have an expression index for use here with the GROUP BY clause? The PLAN is the following: PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB))) As you can see there is a SORT there caused by the GROUP BY clause. ASI_ANOEJE is SMALLINT ASI_CODSUC is SMALLINT ASC_FECHAX is DATE ASI_NUMCUE is VARCHAR(16) ASI_NUMSUB is CHAR(5) I'm using Firebird 2.5.4 Greetings. Walter.
Re: [firebird-support] DeadLock Conflict
Hi, Probably you mean Lock conflict, not a true deadlock (full error text will help). If you see transaction number in the message, you can track it using MON$ tables, and then find out which connection has started it. Regards, Alexey Kovyazin IBSurgeon Hi All If possible at all, how can I trace which workstation (Firebird Client) is holding a record hostage and thus causing a deadlock conflict? Regards Stef van der Merwe email s...@autotech.co.za [Non-text portions of this message have been removed]
[firebird-support] DeadLock Conflict
Hi All If possible at all, how can I trace which workstation (Firebird Client) is holding a record hostage and thus causing a deadlock conflict? Regards Stef van der Merwe email s...@autotech.co.za [Non-text portions of this message have been removed]
[firebird-support] Error in order by clause
Hi to all Server: Firebird 2.5.4. I have following stored procedure: ALTER PROCEDURE TEST_ ( I SMALLINT) RETURNS ( SUMA NUMERIC(15,2), A NUMERIC(15,2), B NUMERIC(15,2), C INTEGER, D VARCHAR(10), E VARCHAR(10)) AS begin for select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from t group by c, d, e order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING into :suma, :a, :b, :c, :d, :e do suspend; end If i run it with paraneter i = 0 it gives wrong sort order. Results: SUMA A B C D E -1,11,2 2,3 1 11 3 -1,83,4 5,2 2 11 3 0 3 3 2 12 2 12 14 2 1 11 1 Otherwise the sort order is ok. This propagates only when there is iif within iif clause. Am i doing something wrong or it is a bug ? Best regards Bogdan = complete script: SET SQL DIALECT 3; /*** ***/ / Stored procedures / /*** ***/ SET TERM ^ ; CREATE PROCEDURE TEST_ ( I SMALLINT) RETURNS ( SUMA NUMERIC(15,2), A NUMERIC(15,2), B NUMERIC(15,2), C INTEGER, D VARCHAR(10), E VARCHAR(10)) AS BEGIN SUSPEND; END^ SET TERM ; ^ /*** ***/ /Tables / /*** ***/ CREATE TABLE ( A NUMERIC(15,2), B NUMERIC(15,2), C INTEGER, D VARCHAR(10) COLLATE PXW_SLOV, E VARCHAR(10) COLLATE PXW_SLOV ); /*** ***/ / Stored procedures / /*** ***/ SET TERM ^ ; CREATE OR ALTER PROCEDURE TEST_ ( I SMALLINT) RETURNS ( SUMA NUMERIC(15,2), A NUMERIC(15,2), B NUMERIC(15,2), C INTEGER, D VARCHAR(10), E VARCHAR(10)) AS begin for select sum(t.a) - sum(t.b), sum(a), sum(b), c, d, e from t group by c, d, e order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING into :suma, :a, :b, :c, :d, :e do suspend; end^ SET TERM ; ^ INSERT INTO (A, B, C, D, E) VALUES (1.2, 2.3, 1, '11', '3'); INSERT INTO (A, B, C, D, E) VALUES (3.4, 5.2, 2, '11', '3'); INSERT INTO (A, B, C, D, E) VALUES (0, 1, 2, '12', '2'); INSERT INTO (A, B, C, D, E) VALUES (3, 2, 2, '12', '2'); INSERT INTO (A, B, C, D, E) VALUES (14, 2, 1, '11', '1'); COMMIT WORK; --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
[firebird-support] Re: unable to complete network request localhost
Hi- I have more intel on the problem. SQL connect localhost:/var/lib/firebird/data/employee.fdb user SYSDBA password mypass; Statement failed, SQLSTATE = HY000 Can't access lock files' directory /tmp/firebird/ I have the fb listening on 3050 now. tcp6 0 0 :::3050 :::*LISTEN I just noticed it is listening on ipv6 address. I found this snippet: If some wants to know, easy way to configure firebird2.5-super (openSuSe 12.3) is edit as root file /etc/firebird/firebird.config. Just uncomment DatabaseAccess = FULL (whereever is db) and Authentication = native(user=SYSDBA, password=masterkey) to access db if you got accessing error. Of course, you need to restart firebird. Regards. I put DatabaseAccess = Restrict /var/lib/firebird/data. I also put : TempDirectories = /tmp/firebird/ I restart and start with systemctl start firebird-classic.socket and systemctl stop firebird-classic.socket I am not sure what else to do? Thanks for any assistance, jim
Re: [firebird-support] can't log-in as user account firebird
You are not supposed to login as user Firebird, that is also why it is pointing to nologin. That user is only the user for running the service, and for security reasons you shouldn't login with it. To connect to Firebird, you need to have an account in Firebird (created using gsec) and connect to the service (if it is running) from your own OS user account. Mark - Reply message - Van: thirdshiftco...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Aan: firebird-support@yahoogroups.com Onderwerp: [firebird-support] can't log-in as user account firebird Datum: ma, aug. 24, 2015 19:41 Hi- I changed firebird to #: passwd myfb at the command-line. At su firebird I get: su: failed to execute /bin/nologin: No such file or directory firebird:x:901:902::/:/bin/nologin not sure what I am doing wrong. I am using firebird 2.5.4 fedora xfce classic. Please assist! Addendum: with the root log-in I can select data from sample db. I have SYSDBA account setup with a password and will do a new account once I get this straightened out. THANKS!
[firebird-support] Re: unable to complete network request localhost
I have one more curiosity... I lied actually many more- why is fedora insisting in package manager to install embedded. Even when i try to get rid of it fedora chooses to uninstall other files which I canceled and not do? could this be part of the problem that i have both embedded AND other client libfbclient! thanks
Re: [firebird-support] Expression index for use with the GROUP BY clause
Ok, thank you Karol. Greetings. Walter. On Mon, Aug 24, 2015 at 2:32 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, as i say previously you can not do this as index can not be updated if something was changed in ASIENTOSCAB. And any subseclect is not recognized by plan parser as expression index def regards, Karol Bieniaszewski *From:* mailto:firebird-support@yahoogroups.com firebird-support@yahoogroups.com *Sent:* Monday, August 24, 2015 7:55 PM *To:* firebird-support@yahoogroups.com *Subject:* Re: [firebird-support] Expression index for use with the GROUP BY clause Hello Karol Thank you for your answer. The query is a very simplified one, just for show the idea. I can create an index as the following: CREATE INDEX IDX_ASIENTOSDET1 ON ASIENTOSDET COMPUTED BY ( CAST(ASI_ANOEJE AS CHAR(5)) || CAST(ASI_CODSUC AS CHAR(5)) || CAST(EXTRACT(MONTH FROM (SELECT ASC_FECHAX FROM ASIENTOSCAB WHERE ASC_CODSUC = ASI_CODSUC AND ASC_IDENTI = ASI_IDECAB)) AS CHAR(2)) || ASI_NUMCUE || ASI_NUMSUB ); But is is not used in the PLAN. I had tried several alternatives but without success until now. Using CAST(), without using CAST() and so on. I don't want to add a column ASI_FECHAX to my table ASIENTOSDET if I can avoid create it, but I need rows grouped by that column. ASIENTOSCAB and ASIENTOSDET have a parent-child relationship, where ASIENTOSCAB is the parent. Greetings. Walter. On Mon, Aug 24, 2015 at 1:40 PM, liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, No – you can not create single index on more then one table But you really need it? How big is resultset? I do not see any filter in this query no HAVING nor WHERE regards, Karol Bieniaszewski *From:* mailto:firebird-support@yahoogroups.com firebird-support@yahoogroups.com *Sent:* Monday, August 24, 2015 7:28 PM *To:* firebird-support@yahoogroups.com *Subject:* [firebird-support] Expression index for use with the GROUP BY clause Hello everybody I had the following query: SELECT D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES, D.ASI_NUMCUE, D.ASI_NUMSUB FROM ASIENTOSDET D JOIN ASIENTOSCAB C ON D.ASI_CODSUC = C.ASC_CODSUC AND D.ASI_IDECAB = C.ASC_IDENTI GROUP BY D.ASI_ANOEJE, D.ASI_CODSUC, EXTRACT(MONTH FROM C.ASC_FECHAX), D.ASI_NUMCUE, D.ASI_NUMSUB Is it possible to have an expression index for use here with the GROUP BY clause? The PLAN is the following: PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB))) As you can see there is a SORT there caused by the GROUP BY clause. ASI_ANOEJE is SMALLINT ASI_CODSUC is SMALLINT ASC_FECHAX is DATE ASI_NUMCUE is VARCHAR(16) ASI_NUMSUB is CHAR(5) I'm using Firebird 2.5.4 Greetings. Walter.
Re: [firebird-support] Re: unable to complete network request localhost
At 07:52 a.m. 25/08/2015, thirdshiftco...@yahoo.com [firebird-support] wrote: I have one more curiosity... I lied actually many more- why is fedora insisting in package manager to install embedded. Even when i try to get rid of it fedora chooses to uninstall other files which I canceled and not do? could this be part of the problem that i have both embedded AND other client libfbclient! I'm having a bit of trouble figuring out what you are achieving with all your little tweaks. On Fb 2.5, libfbclient.so encompasses both the full server and the embedded. To use it as embedded, you simply omit the server from the connection string and the client then opens the database file directly. When you make a network connection, you are starting up as a network client...you wouldn't normally be doing this on the same box as the host. Of course, you can do so. Network clients (including localhost) can't connect if xinetd isn't running. The firebird user should be already existing after a standard installation. It's not a user that should be used by a human. If the firebird group doesn't exit, you should create it and add any OS users you want to have access to the server. Don't create an OS user called firebird or SYSDBA. SYSDBA exists in the firebird security database after you run the installation scripts, with a password generated and stored in the /firebird root in the text file SYSDBA.password. The root user has the same privs on the server and in databases as SYSDBA. Any OS user you want to create (other than those) will act like Firebird users and have the same privileges as a regular Firebird user (usually none!) Another thing you perhaps don't understand is that each connection to Classic is a separate process that starts on connect and disappears on disconnect. This is unlike Superserver, where a server process runs and listens for clients itself. When it attaches a client, it creates a thread for it. As for what Fedora is playing at, I have no idea. I use Ubuntu and I always install from the tarball and run the scripts. So no help from me on that.
Re: [firebird-support] can't log-in as user acco unt firebird
Hi Mark Helen- I somehow lost my post. I will try to make it briefer. I don't know how links work here. Mark nailed it on the source of my confusion. I thought like in postgres you did : su - # su - postgres pg$ Helen was right that I was doing su - firebird -s /bin/bash which is a tweak because I didn't understand the nologin scenario. Thanks for providing such expert help! Helen I like your books. Please check out the link (i don't know how to give the link a title yet in this forum) I made it fooling around bored as my hobby. It is a query builder that uses swig, c++, tcl, firebird, ibpp thanks again, jim untitled http://3rdshiftcoder.net/wordpress/wp-content/uploads/2015/05/rework_query_builder5.html untitled http://3rdshiftcoder.net/wordpress/wp-content/uploads/2015/05/rework_query_builder5.html //#include ../ibpp_headers/StdCPract.h #include QueryBuilder.h using namespace std; //#define NDEBUG #ifndef NDBUG #define assert(x) #else #define assert(x) \ if (!(x)) \ View on 3rdshiftcoder.net http://3rdshiftcoder.net/wordpress/wp-content/uploads/2015/05/rework_query_builder5.html Preview by Yahoo
