RE: [firebird-support] Evaluate "empty" string
too complicated, less readable and i guess consuming more Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, August 17, 2017 9:17 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Evaluate "empty" string >If (coalesce(terminal, '') <> '') then No need for coalesce since is an unknown state and compared with a value it neither returns true nor false. Hence, I suspect if (trim(terminal) > '') to be equivalent to if (((CHAR_LENGTH(trim(terminal))>0) and (terminal is not null)) Set --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
RE: [firebird-support] Evaluate "empty" string
This is also possible If (coalesce(terminal, '') <> '') then Regards Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Wednesday, August 16, 2017 6:39 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Evaluate "empty" string May be this : if (terminal<>'') and ( not (terminal is null)) then do something With best regards Michel Le Mercredi 16 août 2017 18h20, "Mark Rotteveel m...@lawinegevaar.nl [firebird-support]" <firebird-support@yahoogroups.com> a écrit : On 16-8-2017 17:53, Jorge Andres Brugger jorge.brug...@gmail.com [firebird-support] wrote: > Hello all. > I need to evaluate if one SP varchar parameter is "not empty". I´m using: > if (((CHAR_LENGTH(trim(terminal))>0) or (terminal is not null)) then something>. > If I send '' (empty string) as parameter content, the expression > evaluates to true (I´m expecting to get a false). > What am I doing wrong? An empty string is not null, so even though (CHAR_LENGTH(trim(terminal))>0) might be false, (terminal is not null) is true, so the IF evaluates to true and is executed. Maybe you meant AND instead of OR? -- Mark Rotteveel --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
RE: [firebird-support] think there is a bug in gpre cobol for 2.5.x
Hi I haven't been working with Cobol for 25 years, but: if Disconnect is in Procedure Division then Connect should also be there. Regards Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Monday, July 3, 2017 7:28 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] think there is a bug in gpre cobol for 2.5.x Hi- I gave this a really good look. It seems no matter what i do I get an error in gpre with cobol. here is my cobol code: IDENTIFICATION DIVISION. PROGRAM-ID. Moon. ENVIRONMENT DIVISION. Configuration Section. Special-Names. DATA DIVISION. WORKING-STORAGE section. 01 WS_TEXT PIC X(80). EXEC SQL SET DATABASE DB2 = 'localhost:employee'; EXEC SQL CONNECT DB2; PROCEDURE DIVISION. EXEC SQL SELECT COUNT(PHONE_EXT) INTO :WS_TEXT FROM EMPLOYEE; DISPLAY 'FOO MEISTER'. DISPLAY WS-TEXT. EXEC SQL DISCONNECT DB2; STOP RUN. I can't get it to run no matter whether I stand on my head or not. I get an error code of 104 or else 100 if I am using cursors. I have an alias of employee. does anyone out there have success with gpre with 2.5.7.27050-0? i looked at a book on embedded sql. Pretty much the same except slightly different syntax to gpre cobol. Any ideas where I can go for help? One guy said he changed versions ever so slightly and a different version worked for hi m. Is there an archive of slightly older versions? I also defined ISC_USER with exports etc. I'd like to try a different version of 2.5.x thx. - jim --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
RE: [firebird-support] Strange Behavior
Nope there is only one database in the server. -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Wednesday, June 22, 2016 11:41 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Strange Behavior 22.06.2016 8:44, 'Mihalache Bogdan' bogdan.mihala...@soter.ro [firebird-support] wrote: > - On the machines where the new values was not visible, i have restarted the > connection (Close the App and ReOpend it), but still the old value was there, not the new one. Most likely they are attached to different database. -- 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
[firebird-support] Strange Behavior
Hello! We have a 64 bits firebirds 2.5.2 SC server on a linux machine. The stange behavior that i don`t understrans is the following : - I`ve Used Database WorkBench to update a value in a table from 0 to 1. - I`ve commited the transaction and the new value was visible from my connection, with DWB and also with an ODBC connection when selecting the new value. - The new value that i`ve just updated was not visible for all the connections in the network, just for some of them. (all this connection are using ODBC to connect and SYSDBA as user.) - On the machines where the new values was not visible, i have restarted the connection (Close the App and ReOpend it), but still the old value was there, not the new one. I think that there is problem with the separated caches of the server, as that value is read every time the App opens, and i`m guesing that it` s loading in there. All the connections are using Read/Write Wait transactions. This is a thing that i could actualy see and my fear is that it might not be an isolated case. Is there something that i cand do to stop this in the future? Thank you!
[firebird-support] IN OUT PARAMETERS
In firebird is there an option to declare a parameter in a stored procedure as an IN/OUT. (like ORACLE server has) http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/parameter_declaration. htm I want to send a parameter to a procedure, do some operation with it, and return his new value. Thank you!
RE: [firebird-support] Extract seconds from timestamp
I know that – result is rounded to next integer number
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, January 06, 2016 10:36 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Extract seconds from timestamp
select cast(extract(second from cast('12.12.2016 23:59:59:4900' as timestamp))
as integer) from rdb$database
59
On Wednesday, January 6, 2016 10:37 AM, "'Bogdan' bog...@mordicom.si
[firebird-support]" <firebird-support@yahoogroups.com> wrote:
Hi all
I wonder if this is an error or sql srandard:
select cast(extract(second from cast('12.12.2016 23:59:59:9000' as timestamp))
as integer) from rdb$database
gives 60
Regards
< div class="yiv3464183871MsoNormal" id="yui_3_16_0_1_1452055013363_4929">
Bogdan
---
Ta e-pošta je bila pregledana z Avast protivirusnim programom.
https://www.avast.com/antivirus
[firebird-support] Extract seconds from timestamp
Hi all
I wonder if this is an error or sql srandard:
select cast(extract(second from cast('12.12.2016 23:59:59:9000' as
timestamp)) as integer) from rdb$database
gives 60
Regards
Bogdan
---
Ta e-pošta je bila pregledana z Avast protivirusnim programom.
https://www.avast.com/antivirus
RE: [firebird-support] Extract seconds from timestamp
Tnx
Regards
Bogdan
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, January 06, 2016 6:53 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Extract seconds from timestamp
On 06/01/16 08:37, 'Bogdan' bog...@mordicom.si [firebird-support] wrote:
> I wonder if this is an error or sql srandard:
>
> select cast(extract(second from cast('12.12.2016 23:59:59:9000' as
> timestamp)) as integer) from rdb$database
http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-extract.html
Check the notes;)
--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
---
Ta e-pošta je bila pregledana z Avast protivirusnim programom.
https://www.avast.com/antivirus
[firebird-support] Character set
If someone can help me. I have a database with character set = none. That happened by mistake when upgrading dialect 1 database from 1.5 to 2.5. What is the procedure to change character set back to win1250 as it used to be. Any help will be appreciated. Regards, Bogdan --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
RE: [firebird-support] Error in order by clause
I wasn't aware of this. I've assumed that if i = 0 Firebird looks only at the first part of the second iif. Thanks Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Tuesday, August 25, 2015 11:47 AM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Error in order by clause (Not sure if this is a duplicate thread or the same as Wrong sort results but I'll answer here too) 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 sums are NUMERIC. Even though the result is always the same (dependent on you input I), Firebird doesn't know that yet. For Firebird the result CAN VARY between records and that's not allowed (record one could be a VARCHAR and record 2 could be NUMERIC for all Firebir d knows). 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 sums to order right aligned) ---In firebird-support@yahoo groups.com, bogdan@... wrote : It should be SUMA A B C D E 12 14 nbs p; 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 - Reply message - Van: 'Bogdan' bogdan@... [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 order by iif(:i = 1, t.c, IIF(:I = 0, sum(t.a) - sum(t.b), E)) DESCENDING --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
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
[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
RE: [firebird-support] error firebird v2.5.4 using trigger and hash function and update or delete record same content
I believe there is no PK in the table, so engine doesn't know what record to delete Regards, Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Tuesday, August 11, 2015 12:54 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] error firebird v2.5.4 using trigger and hash function and update or delete record same content On 10 Aug 2015 14:23:44 -0700, email...@yahoo.com.br [firebird-support] mailto:email...@yahoo.com.br%20[firebird-support] firebird-support@yahoogroups.com wrote: - I add new records mannually, add one new ID field value and the other fields have your values by TRIGGER BEFORE INSERT - Then, I try add 2 or more record with ID = 1 - Later, I try delete any one record and I have this message: *-*-*-*-*-*-*-*-*-*-* Firebird error message when trying delete or update any record with same values in your fields *-*-*-*-*-*-*-*-*-*-* .Cannot delete record! .There is at least one record with same fields value! These don't look like Firebird errors, is this coming from your application? If so, show exactly what you're doing. .Error Message: . .Invalid token. .Dynamic SQL Error. .SQL error code = -104. .Unexpected end of command - line 2, column 66416683. *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* This indicates you have a syntax error somewhere Any idea for this error? The Firebird dont use Internal RDB$DB_KEY (or just DB_KEY) to control records (internal) in table when dont exist PrimaryKey or Index? I am not sure what you mean with this, or how it would be relevant in the context of this question. Mark --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. https://www.avast.com/antivirus
RE: [firebird-support] Soc Sec No comparison using Firebird
create procedure social_number_comparison ( i1 varchar(20), i2 varchar(20) ) returns ( result varchar(100) ) as declare variable x1 char(20); declare variable x2 char(20); declare variable e smallint; declare variable e1 smallint; declare variable e2 smallint; declare variable i smallint; begin i = 0; x1 = i1; x2 = i2; e = 0; while (i 20 and e 3) do begin i = i + 1; if (substring(x1 from i for 1) substring(x2 from i for 1)) then begin e = e + 1; if (e = 1) then e1 = i; else if (e = 2) then e2 = i; end end if (e = 0) then result = 'OK'; else if (e = 3) then result = 'Not equal'; else if (e = 1) then result = e1 || '. character ' || substring(i1 from i for 1) || ' has changed'; else begin if (substring(x1 from e1 for 1) = substring(x2 from e2 for 1) and substring(x1 from e2 for 1) = substring(x2 from e1 for 1)) then result = e1 || ' and ' || e2 || ' were swapped'; end suspend; end Regards, Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Wednesday, June 24, 2015 5:12 PM To: Firebird Support Subject: [firebird-support] Soc Sec No comparison using Firebird Greetings All, I would like to pass into a stored procedure two social security numbers for comparison and have the result tell me if one character has changed, or if two characters were switched or if it does not compare at all. Has anyone done anything like this they could share? Or is it even possible? Thoughts? Thanks, Mike . _ https://www.avast.com/antivirus Avast logo This email has been checked for viruses by Avast antivirus software. 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] Cumulative queries
Are you running superserver ? If so i would recommend switching to classic or superclassic Regards Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, March 12, 2015 8:06 AM To: firebird-support@yahoogroups.com Subject: Odp: [firebird-support] Cumulative queries Hi, This look for me like wrong design. Query running minutes are not acceptable from my POV. I do not know how your system work, but in any stock system i see month work. Month is closed and data are calculated and stored. And in next month the same Regards, Karol Bieniaszewski - Reply message - Od: apos;Tiberiu Horvathapos; tiberiu_horv...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cumulative queries Data: czw., mar 12, 2015 07:19 Hi all, I have a Firebird stock management system with 100+ tables and 10+ clients. I have some cumulative queries in my Delphi program that generates reports. My problem is that while the queries are run (less than 5 minutes each), the whole system performance is poor, people complaining about the speed of the database. How do you deal with this ? Thinking about : 1. Create a backup database and have the queries run there (backup / restore during the night) ? This would be the obvious option. If data for the reports is one day behind the real system is not a big problem. 2. In FireBird do I have some switch to lower the priority of a query ? select * from table –low priority mode- some other ideas ? Thank you in advance , Tiberiu --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. http://www.avast.com
RE: [firebird-support] How to change Primary Key on a table by adding a new field
Select trim(RC.RDB$INDEX_NAME) from RDB$RELATION_CONSTRAINTS RC where (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') and (RC.RDB$RELATION_NAME = :TABELA) into :PKTEMP Using that select you can fiind the name of the column who has the primary key. Inspired from Carlos Cantu external log procedure. Maybe you can be inspired to. -Original Message- From: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Monday, January 26, 2015 8:06 PM To: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com Subject: [firebird-support] How to change Primary Key on a table by adding a new field Hello, I have an application that use a Firebird database and I need to change a Primary Key on a table by adding a new field. I can drop a primary key constraint by ALTER TABLE MyTable DROP CONSTRAINT MyINTEG_000 where MyINTEG_000 is the constraint on my development database file. I cannot use the instruction above because I don't know in all production databases of my customers the name of the Primary Key I want update. How I can solve? Thanks. -- Luigi Siciliano -- ++ 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
RE: [firebird-support] How to change Primary Key on a table by adding a new field
Select trim(I.RDB$FIELD_NAME) from RDB$RELATION_CONSTRAINTS RC join RDB$INDEX_SEGMENTS I on (I.RDB$INDEX_NAME = RC.RDB$INDEX_NAME) join RDB$INDICES IDX on (IDX.RDB$INDEX_NAME = RC.RDB$INDEX_NAME) where (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') and (RC.RDB$RELATION_NAME = :TABELA) order by I.RDB$FIELD_POSITION into :PKTEMP Using that select you can fiind the name of the column who has the primary key. Inspired from Carlos Cantu external log procedure. Maybe you can be inspired to. -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Monday, January 26, 2015 8:06 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] How to change Primary Key on a table by adding a new field Hello, I have an application that use a Firebird database and I need to change a Primary Key on a table by adding a new field. I can drop a primary key constraint by ALTER TABLE MyTable DROP CONSTRAINT MyINTEG_000 where MyINTEG_000 is the constraint on my development database file. I cannot use the instruction above because I don't know in all production databases of my customers the name of the Primary Key I want update. How I can solve? Thanks. -- Luigi Siciliano -- ++ 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
RE: [firebird-support] Re: Firebird 1.5 - CURRENT_TIMESTAMP difference
Sent: Monday, December 15, 2014 8:50 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: Firebird 1.5 - CURRENT_TIMESTAMP difference On 14 Dec 2014 14:14:21 -0800, emad...@yahoo.com.au [firebird-support] mailto:emad...@yahoo.com.au%20[firebird-support] firebird-support@yahoogroups.com wrote: Unfortunately we're using 1.5 so NOW isn't available to us. We're running the statements in real-time so a 30 minute difference is definitely wrong.. It is also exactly 30 minutes which leads me to believe there's some kind of funny offset due to timezone or something to that effect that we aren't seeing for whatever reason. I thought if I figured out where the CURRENT_TIMESTAMP statement is pulling its time from I would know where to look for the issue. Do you also get this 30 minute difference if you execute SELECT CURRENT_TIMESTAMP FROM RDB$DATABASE from ISQL and/or Flamerobin, or only inside your application? What is your own timezone, and what is the server timezone? Mark The last time i've seen such behaviour, we found there was another job scheduled every 30 minutes that took all the resources Regards Bogdan --- avast! Antivirusna zaščita. E-pošta je čista. Ne vsebuje virusov in zlonamerne programske kode. Prenesite si avast! Antivirusno zaščito iz www.avast.si. http://www.avast.com
RE: [firebird-support] Advice with the following sql command
Manyou shlould read a bit more before going on this road. It a simple example of select with group option. Select TRANS_DATE,TRANSTIME, POSNUMBER, TRANSNUMBER, Count(ITEM_QTY) as ItemsSold, SUM(LINE_TOTAL) as LineTotal from accounthistory WHERE TRANS_DATE BETWEEN ' StartDate ' AND ' EndDate AND AccountID='1001' GROUP BY TRANS_DATE, TRANSTIME, POSNUMBER, TRANSNUMBER Order By TRANS_DATE, TRANSTIME DESC And at least...do you know what you want from that select? Are you sure that you il will suit your needs ? Good luck! From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, September 25, 2014 5:11 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Advice with the following sql command Ok so how do I sort it? Select TRANS_DATE,TRANSTIME, POSNUMBER, TRANSNUMBER, Count(ITEM_QTY) as ItemsSold, SUM(LINE_TOTAL) as LineTotal from accounthistory WHERE TRANS_DATE BETWEEN ' StartDate ' AND ' EndDate AND AccountID='1001' GROUP BY ItemsSold, LineTotal Order By TRANS_DATE, TRANSTIME DESC; Is that how I would sort it? From: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 25 September 2014 3:09 PM To: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com Subject: Re: [firebird-support] Advice with the following sql command Means exactly what it says. You don't have TRANS_DATE and TRANSTIME in either the GROUP BY or in an aggregate function. As the query can process many records with different values for these columns what do you expect the query to return? On 25/09/2014 14:38, 'Andrew Gable' andrew.ga...@tesco.net mailto:andrew.ga...@tesco.net [firebird-support] wrote: Hi all, I need someone please to advice me of the following sql code Select TRANS_DATE,TRANSTIME, POSNUMBER, TRANSNUMBER, Count(ITEM_QTY) as ItemsSold, SUM(LINE_TOTAL) as LineTotal from accounthistory WHERE TRANS_DATE BETWEEN ' StartDate ' AND ' EndDate AND AccountID='1001' GROUP BY POSNUMBER, TRANSNUMBER Order By TRANS_DATE, TRANSTIME DESC; When I run that within my application I get this error message Run Time Error -2147467259 (80004005) [ODBC Firebird Driver][Firebird] SQL Error SQL error code =-104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) Any advice is welcomed as this has me stumped as well as my SQL expert mate Thanks Andy -- Tim Ward
RE: [firebird-support] database gone after server reboot
Hi Why do you have RESERVE set to ON ? Regards Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of jakef...@yahoo.com Sent: Tuesday, April 15, 2014 8:18 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] database gone after server reboot These two occurrences are the only ones I've seen in 17+ years of IB/FB experience. Both after a scheduled Win2008R2 server reboot and both running FB2.5 Classic. One was a physical server and the other was a virtual server. Version: WI-V6.3.1.26351 Firebird 2.5 Creation date = 2014-03-09 09:29:13.2960 Page size = 8192 Number of DB pages allocated = 3902905 DB buffer pages = 0 Actual buffer pages = 75 Database SQL dialect= 3 Sweep interval = 2 Forc ed Writes are ON Read Only flag is OFF Reserve flag is ON Transaction - oldest = 867893 Transaction - oldest active = 882874 Transaction - oldest snapshot = 882874 Transaction - Next= 899432 ODS = 11.2 --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com
RE: [firebird-support] Round - quarter hour
Round(100/15.00) * 15 Or (div(100,15)*15) + (cast((mod(100,15)/15.00) as Integer)*15) From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of checkmail Sent: Tuesday, April 8, 2014 8:55 AM To: firebird-support@yahoogroups.com Subject: AW: [firebird-support] Round - quarter hour Hi, yes, every 15 Minutes. Von: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von Thomas Beckmann Gesendet: Dienstag, 8. April 2014 07:47 An: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com Betreff: Re: [firebird-support] Round - quarter hour Hi Olaf, how abaout normalizing to quarters (divide by 15e0), rounding and multiply by 15? Thomas Am 08.04.2014 07:39, schrieb checkmail: Hello, in my Case I would round a integer number to a full quarter number: 12 to 15, 91 to 90, 98 to 105 etc. Is there anything I can use? ; Thanks a lot. Regards Olaf -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
RE: [firebird-support] toggle betweenn 0 and 1
Update table set column = Mod(column+1,2) where id = :id From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Mark Rotteveel Sent: Wednesday, April 2, 2014 10:16 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] toggle betweenn 0 and 1 On Wed, 2 Apr 2014 09:07:25 +0200, checkmail check_m...@satron.de mailto:check_m...@satron.de wrote: is there a simple way to toggle between 0 and 1? Update table set column NOT column where id = :id? If column = 1 - then set to 0, else set to 1. Sure: COLUMN = 1 - COLUMN (however this assumes that the column is NOT NULL and always 1 or 0). or with a simple CASE: COLUMN = CASE COLUMN WHEN 1 THEN 0 ELSE 1 END or a searched CASE: COLUMN = CASE WHEN COLUMN = 1 THEN 0 ELSE 1 END Mark
[firebird-support] Select stored in a table
I have this Sql statement strored in a table : Select sum(secunde_fact) as cantitate From agenda Where id_contract = :lcIdContract and id_ramura = :lcIdRamura And id_subramura = :lcIdSubramura And (agenda.data_factura between :ldDela And :ldPanala ) And facturabil = 1 and deleted = 0 I wanna used in a stored procedure, like this : lcIdClient = id_client ; lcIdContract = id_contract ; lcIdRamura = id_ramura ; lcIdSubramura = id_subramura ; ldDela = lcDela ; ldPanala = lcPanala ; lnAn = Extract (Year From ldDela) ; lnLuna = Extract (Month From ldDela) ; For Select ab.um, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4, aa.int_start, aa.int1, aa.int2, aa.int3, aa.int4, aa.id_criteriu_pret, aa.id_pret_um, aa.felpret, aa.ispretfix, ac.id_subramura From contracte_det_pret aa Left Join nom_contracte_pret_um ab on aa.id_pret_um = ab.id Left Join nom_contracte_pretac on aa.id_criteriu_pret = ac.id Where aa.id_contract = :lcIdContract And ac.id_subramura = :lcIdSubramura Into :um, :sql, :val1, :val2, :val3, :val4, :int_start, :int1, :int2, :int3, :int4, :lcIdCriteriuPret, :lcIdPretUm, :felpret, :ispretfix, :lcIdSubramura do begin if (lower(:felpret) = 'pretunitar') then begin Execute Statement Sql Into :lnCantitate ; lnCantitate = coalesce(:lnCantitate, 0) ; valoare = lnCantitate * val1 ; suspend; end I am using IBExpert as a tool to view my database. In debug mode it works just fine. But if i hit play, i`m receivind an error, that he can`t fiind column lcIdContract, at this line(when he is trying to execute the sql statement) :Execute Statement Sql Into :lnCantitate ; If i remove the variables(:lcIdContract, :lcIdRamura, etc.) from the sql statement, that is stored in the table, it`s working, but I really need to keep them, and keep the sql in the table. Does anyone has a solution to my problem? Bogdan MIHALACHE
RE: [firebird-support] Select stored in a table
Meanwhile i have found that link, but i was wondering if there is a way to pass the parameters in a variable or something like that. I have made a new fiels in the table that stores the sql statement, and put in there the variables like this : - lcIdContract := :lcIdContract, lcIdRamura := :lcIdRamura, lcIdSubramura := :lcIdSubRamura, ldDela := :ldDela, ldPanala := :ldPanala My problem is that there is more than one sql statement there, with different parameters name and count. i`ve tried execute statement (sql) (parametes) since there it says that i can use an other expresion (When a statement has parameters, it must be placed in parentheses when EXECUTE STATEMENT is called, regardless whether it is given directly as a string, as a variable name, or by another expression.) but i think that i don`t really understant how this works. As a question is there an aproximate way to use this : execute statement (sql) (parametes) ? or something similar to it? Has anyone tried something like this? What was your aproach? Thank you verry much! Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvar Sent: Tuesday, March 4, 2014 12:52 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Select stored in a table I have this Sql statement strored in a table : Select sum(secunde_fact) as cantitate From agenda Where id_contract = :lcIdContract and id_ramura = :lcIdRamura And id_subramura = :lcIdSubramura And (agenda.data_factura between :ldDela And :ldPanala ) And facturabil = 1 and deleted = 0 I wanna used in a stored procedure, like this : lcIdClient = id_client ; lcIdContract = id_contract ; lcIdRamura = id_ramura ; lcIdSubramura = id_subramura ; ldDela = lcDela ; ldPanala = lcPanala ; lnAn = Extract (Year From ldDela) ; lnLuna = Extract (Month From ldDela) ; For Select ab.um, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4, aa.int_start, aa.int1, aa.int2, aa.int3, aa.int4, aa.id_criteriu_pret, aa.id_pret_um, aa.felpret, aa.ispretfix, ac.id_subramura From contracte_det_pret aa Left Join nom_contracte_pret_um ab on aa.id_pret_um = ab.id Left Join nom_contracte_pretac on aa.id_criteriu_pret = ac.id Where aa.id_contract = :lcIdContract And ac.id_subramura = :lcIdSubramura Into :um, :sql, :val1, :val2, :val3, :val4, :int_start, :int1, :int2, :int3, :int4, :lcIdCriteriuPret, :lcIdPretUm, :felpret, :ispretfix, :lcIdSubramura do begin if (lower(:felpret) = 'pretunitar') then begin Execute Statement Sql Into :lnCantitate ; lnCantitate = coalesce(:lnCantitate, 0) ; valoare = lnCantitate * val1 ; suspend; end I am using IBExpert as a tool to view my database. In debug mode it works just fine. But if i hit play, i`m receivind an error, that he can`t fiind column lcIdContract, at this line(when he is trying to execute the sql statement) :Execute Statement Sql Into :lnCantitate ; If i remove the variables(:lcIdContract, :lc IdRamura, etc.) from the sql statement, that is stored in the table, it`s working, but I really need to keep them, and keep the sql in the table. Does anyone has a solution to my problem? Things are slightly more complex with parameters, Bogdan, though just slightly: You need to pass the parameters into your execute statement (think of execute statement as self contained, with no knowledge of things outside of itself), i.e. EXECUTE STATEMENT (SQL) (lcIdContract, lcIdRamura, lcIdSubramura, ldDela, ldPanala) Into :lnCantitate ; (take a look at www.firebirdsql.org/refdocs/langrefupd25-psql-execstat.html http://www.firebirdsql.org/refdocs/langrefupd25-psql-execstat.html for syntax examples) HTH, Set
RE: [firebird-support] Select stored in a table
This works just fine. Thank you!(multumesc!) Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Virna Constantin Sent: Tuesday, March 4, 2014 1:03 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Select stored in a table :sql=replace(:sql,':lcIdContract',:lcIdContract); :sql=replace(:sql,':lcIdRamura',:lcIdRamura); ... On Tuesday, March 4, 2014 12:24 PM, Bogdan Mihalache bogdan.mihala...@soter.ro wrote: I have this Sql statement strored in a table : Select sum(secunde_fact) as cantitate From agenda Where id_contract = :lcIdContract and id_ramura = :lcIdRamura And id_subramura = :lcIdSubramura And (agenda.data_factura between :ldDela And :ldPanala ) And facturabil = 1 and deleted = 0 I wanna used in a stored procedure, like this : lcIdClient = id_client ; lcIdContract = id_contract ; lcIdRamura = id_ramura ; lcIdSubramura = id_subramura ; ldDela = lcDela ; ldPanala = lcPanala ; lnAn = Extract (Year From ldDela) ; lnLuna = Extract (Month From ldDela) ; For Select ab.um, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4, aa.int_start, aa.int1, aa.int2, aa.int3, aa.int4, aa.id_criteriu_pret, aa.id_pret_um, aa.felpret, aa.ispretfix, ac.id_subramura From contracte_det_pret aa Left Join nom_contracte_pret_um ab on aa.id_pret_um = ab.id Left Join nom_contracte_pretac on aa.id_criteriu_pret = ac.id Where aa.id_contract = :lcIdContract And ac.id_subramura = :lcIdSubramura Into :um, :sql, :val1, :val2, :val3, :val4, :int_start, :int1, :int2, :int3, :int4, :lcIdCriteriuPret, :lcIdPretUm, :felpret, :ispretfix, :lcIdSubramura do begin if (lower(:felpret) = 'pretunitar') then begin Execute Statement Sql Into :lnCantitate ; lnCantitate = coalesce(:lnCantitate, 0) ; valoare = lnCantitate * val1 ; suspend; end I am using IBExpert as a tool to view my database. In debug mode it works just fine. But if i hit play, i`m receivind an error, that he can`t fiind column lcIdContract, at this line(when he is trying to execute the sql statement) : Execute Statement Sql Into :lnCantitate ; If i remove the variables(:lcIdContract, :lcIdRamura, etc.) from the sql statement, that is stored in the table, it`s working, but I really need to keep them, and keep the sql in the table. Does anyone has a solution to my problem? Bogdan MIHALACHE [Non-text portions of this message have been removed]
RE: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2
I second this answer. Regards, Bogdan MordiCom d.o.o. i...@mordicom.si +386 (05) 33 00 360 From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Alexey Kovyazin Sent: Tuesday, February 18, 2014 3:01 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2 Hi Marius, Sorry, I don't want to be rude. I just want to point that there is no problem with Windows 2008 R2, and a bit tired with all the same questions (with the same answers). Our largest customers databases are on Win2008R2, and it works just fine - 500+ users and 150Gb, 440Gb with 50 users, etc, etc. And, btw, 440Gb is running at the server in Port Elizabeth. Regarding promotion of our business - there are very few professional service providers listed on the Firebirdsql.org, and they are also Firebird sponsors. Without our sponsorship Firebird would not exists and all users would sit with InterBase 6.0, as it was released by Borland in 2000. Regards, Alexey Kovyazin IBSurgeon Hi, I suppose we all have our opinions. This is not an opinion, this is statement, confirmed by our 12 years experience with Firebird and 5 years with Win 2008. Your problem with Win2008 is lack of knowledge, that's all, sorry for the truth. And I'm not going to offer such technical support for free in this support list, sorry. Try other Firebird service providers: http://www.firebirdsql.org/en/professional-support/ may be they have free time. Regards, Alexey Kovyazin IBSurgeon [Marius Labuschagne] What you doing in this forum Alexey? As far as I am aware this is a support forum, am I wrong? You promoting your business over here with all your years of experience? (Or at least you must be thinking that 12 is a very high number) Lets leave it at that, I would not dare ask you for any support anyway. Regards Marius --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com
RE: [firebird-support] Udf returning VARCHAR
Why don`t you use CAST ( var as VARCHAR(NN)) ? Bogdan MIHALACHE From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of liviuslivius Sent: Friday, January 31, 2014 10:22 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] Udf returning VARCHAR Hi, i try to find any example of writing and declaring udf function returning varchar instead of Char without success. Is this possible? regards, Karol Bieniaszewski
RE: [firebird-support] get data from table based on criteria from another table
Select * from TABLE_A aa where Exists (Select ab.id From table_b ab And ab.data_b = 100 And aa.id = ab.id) From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of peter_...@yahoo.com Sent: Monday, December 16, 2013 12:41 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] get data from table based on criteria from another table Hi, I have two tables, TABLE_A and TABLE_B TABLE_A: -- ID: INT DATA_A: INT TABLE_A data: (ID, DATA_A) 1, 11 2, 12 3, 13 4, 14 9, 19 TABLE_B: -- ID: INT DATA_B: INT TABLE_B data: (ID, DATA_B) 1, 100 2, 200 3, 200 5, 100 7, 300 9, 100 I want to do the following: get all records from TABLE_A, for which ID exists in TABLE_B and DATA_B for that ID equals 100. Or in other words: (1).from tableB, get all ID's for which DATA_B = 100 (here - 1,5,9), and then (2).get all the records from TABLE_A containing an ID from(1) In the above case, the desired result would be 1, 11 9, 19 Hope I explained it well. Thanks for any suggestions. Peter
Re: [firebird-support] Update query and trigger
if(new.field_b0) and new.field_a = old.field_a then dosomething; else dosomethingelse; Original message From: Dr. Fabrizio Villanova fvillan...@essedata.it Date: To: firebird-support@yahoogroups.com Subject: [firebird-support] Update query and trigger hi all, for example: mytable ( field_A varchar(x), field_B integer default 0, ) query: update mytable set field_a=XXX where y; mytrigger: before update if(new.field_b0) then dosomething; else dosomethingelse; the trigger must operate only when i update field_b not when i'm updating field_A. Calling the first query new.field_b is always '0' and 'dosomethingelse' is executed; checking for field_b is not null is useless, it's '0'. There is a way to recognize that the query is not altering field_b? thank you. Fabrizio
RE: [firebird-support] Re: How to improve Firebird performance on a large table?
Dear Alan, Thanks for your reply. This is my table ddl : CREATE TABLE LOG ( ID Integer NOT NULL, ID_TBL Integer NOT NULL, TBL_NM Varchar(40) NOT NULL, OPR Char(1) NOT NULL, LOG_AT Timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, SNC Smallint DEFAULT 0, PRIMARY KEY (ID) ); I think, I found the problem. I made a small app that seek for snc's value every second. It seems makes firebird server works heavily. Changing seeking time from 1 second to 10 seconds seems solved the problem. Thanks anyway for your suggestions. Best regards, Anto. Hi I would also add a compounded index snc + ID, or just snc Regards Bogdan [Non-text portions of this message have been removed]
RE: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
Mark Thank you, I understand the need for the DB to return to the requester the exception but you have to admit from the high level perspective it sounds ridiculous to report an error on data that has not yet being prepared by the triggers. After all what's the logic in validating raw data when we know there are triggers waiting to massage the data before posting it to the engine? Even if the raw data is valid, the triggers could then modify it and make it non-compliant so from the high level point of view it seems the validation process is not efficient, it is effective but is checks things before there are ready. Now from the Firebird's developer perspective, I do understand 100% they need to allocate the memory for a variable and that variable needs to be inherited from somewhere. Perhaps in the future this issue will come to a head in some developers meeting and a solution would be found. If you think it breaking the problem by level, it is easier to see the issue, let's think it this way: 1) Level Requester, where the insert statement is created, or App. 2) Level Massaging where the triggers occur before being ready to pass to the data saving process. 3) Level Archiving, where the DB validates and either saves or returns error to requester. The key is understanding that in 1) and 2) there is still change occurring while 3) is a black or white outcome, where either the request can be inserted or it is illegal and fails. At the moment the DB is returning an error on a request that has being posted by 1) but not being processed by 2), so it seems inefficient to report on an issue before allowing 2) to take care of it. Cheers Fabian Hi Fabian I would go with stored procedure with input parameter 32K. Inside stored procedure, you can do whatever you want. Or am i missing something ? Regards Bogdan [Non-text portions of this message have been removed]
RE: [firebird-support] Re: Parameterised like query won't use index in the plan
I second that Regards Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison Sent: Tuesday, October 30, 2012 3:36 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: Parameterised like query won't use index in the plan On Tue, Oct 30, 2012 at 4:50 AM, Kjell Rilbe kjell.ri...@datadia.se mailto:kjell.rilbe%40datadia.se wrote: ... if it contains no wildcards (% and _), use: where myfield = :myparameter ... If it contains a single % wildcard at the end... If it contains multiple wildcards, but none at the beginning... .If the search string starts with a wildcard, resort to this: where myfield like :myparam Now, I wonder if something similar could actually be done internally by Firebird for the general like case? It could some nifty query logic like the above internally, could it not? If like xxx always generated internally a query logic like this: where myfield starting with prefixof :param up to but not including first wildcard and myfield like :param I assume starting with will return true for all data values if the prefix is an empty string - is this true? In that case, would this internal query return the same results as the direct/simple myfield like :param query? And would it actually give better performance in general? Would it risk significantly worse performance in any situation? Since a parameterized query is compiled and optimized only once and reused, that logic would have to be in the execution plan. A goal of execution plans is to be dirt simple, adding parsing and string analysis would be unnatural. I'm not saying it couldn't be done, but the existing data structures - which are built as they are for a reason - cannot handle that complexity. Good luck, Ann [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
RE: [firebird-support] Re: Win7 gbak.exe restore show fail to create database d:\dbback\rsdata.fdb
In WinXp, I use the command gbak.exe -c -v -R -z -user sysdba -password masterkey d:\dbback\20120831.fbk d:\dbback\rsdata.fdb Shouldn't the -R be lower case? I also try -r ,but all the same. to check if my backup file (d:\dbback\20120831.fbk) is OK. but when I run this command in Win7, the command console show fail to create database d:\dbback\rsdata.fdb - Does the directory d:\dbback exist? FYI,I use firebird Ver 1.5.4910, and gbak.exe is placed in d:\dbback so I can sure d:\dbback exist. - Does the user, under which the Firebird server process is running has proper rights in that directory? the user you mean is [Win7 login user] or [sysdba] ? in Win7, when I use the command gbak.exe -b -g -z -v -user sysdba -password masterkey server:d:\fb\dbdata.fdb d:\dbback\20120831.fbk to backup remote database to local Win7 pc, and the backup process is doing just fine. I don't know how to set the proper right to d:\dbback WIN7 user has to have rights to the file to create and to delete if the db file exists. Any chance that dbdata.fdb is open by another process? No, the dbdata.fdb is a new fdb name. As I say before, this command work just fine in WinXp. I would try: gbak.exe -c -v -R -z -user sysdba -password masterkey d:\dbback\20120831.fbk server:d:\dbback\rsdata.fdb Regards Bogdan [Non-text portions of this message have been removed]
RE: [firebird-support] Generators, what could cause resetting generator values to some earlier version
I would check the data. It seems like an old version of database . Copy, system restore ?? Regards, Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Sofija Blazevski Sent: Tuesday, August 07, 2012 4:07 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Generators, what could cause resetting generator values to some earlier version Hello, I have a encountered a problem at client installation I've never had before, I didn't even think it would be possible. Server setup is Firebird 2.1.3 SuperServer on Windows Server 2008 Enterprise. Firebird is used on default port with no special setup. Multiple users log on the server via RDP. Problem is that generator values are changed (from nowhere) to some earlier version say about 15 days ago, on the other side all of the data is current. Do you have any idea what could have caused an issue like this? Could this be due to some windows issue? Same server had security.fdb corrupted about a month ago. Thanks, - Sofija [Non-text portions of this message have been removed]
RE: RES: [firebird-support] Copy of the current db not updating generators
Although I agree with your advice, in general copying the file can corrupt the target file, but it cannot corrupt not the source file. Usually for copying a file the copy program will acquire a read lock, so nothing is changed and the database process can continue writing to the file without being hindered by the copy process. It happens - corruption of the original fdb file when copying it - rare but just once is too much. Bogdan [Non-text portions of this message have been removed]
RE: [firebird-support] OIT / NT
Friday, March 30, 2012, 12:13:06 PM, you wrote: TH which) somehow foods my server. While this client is using my TH software, somehow the gap between OIT and NT increases 200 transactions each 5 seconds. I Other people noted lot of things, but I want to ask - what do you do in the application, that it generates 40 transactions per second? I mean, it's some kind of robot, inserting bulk data, or it is a human, typing data with non-human speed? Firebird does not generates transactions, so, all these transactions are coming only from your application. O don't know If is firebird or not but i have had the same problem wiith ibx+delphi+interbase 2009. I have had jumps of 2 transactions, and of course is imposible by the kind of our app generate 2 transactions in seconds. I have observes it, but I have not been able To reproduce it . Any chance that you guys are running something in a loop with AutoCommit set to True? Or maybe two events that trigger each other Regards Bogdan [Non-text portions of this message have been removed]
RE: [firebird-support] Re: Timestamp of Database File
We are working with large (5-30 GB) Firebird (2.x) databases. There were some occasions where when copying even the original became currupted. Not often but still - we haven't documented it. Lately we don't encounter such problems - as you securely understand - man get used to automatically avoid getting into trouble. Regards Bogdan Probably beating a dead horse, but ... On Wed, Feb 29, 2012 at 2:44 PM, todderamaa toddera...@yahoo.com mailto:todderamaa%40yahoo.com wrote: I have heard that you can corrupt a database when copying it with active connections. I am wondering if incremental windows backups are different than a 'copy'. You may have heard that, but your informant was misinformed. Copying a file that's in use with random writes will not corrupt that file, but the copy will not be internally consistent, unless you're extremely lucky. That's why database systems offer backup programs. Good luck, Ann [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
RE: [firebird-support] Re: Backup-Restore, without killing existing attachments.
Actually, active users stay connected to old renamed database. It can happen only on linux, never on Windows. Regards Bogdan Hi, I get your point. But I wonder what can happen when we don't kill the existing clients before restoring with the same database file name. Actually I had a bad exprience on this :) -One client remained active after a restore operation and continued to run on the newly restored database. (It's firebird 2.5.1) -It generated ID numbers which was already consumed with others. -It did not generate any errors even on uniquely indexed rows, the records it created were just not reachable anymore. So I think every client should be disconnected before restoring and this should be a very strict rule for preventing any data loss or conflicts, maybe firebird developers can confirm this. --- In firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com , Elmar Haneke elmar@... wrote: I have a question on backup/restore. Think of a firebird classic on a linux box. -Do a backup with existing attachments -After backup, Rename the old gdb file to something else -Restore the backup file with the original gdb name. You have to kill all connected clients before starting backup and you have to prevent them from reconnecting until restore is complete. Else at least the updates done while backup/restore are lost. In Addition existing connections should prevent you from renaming the database. As an alternative you should consider if restorerename is really required. Just run the backup and do an restore to an dummy database to verify backup file. It is not required to rebild database on each backup. Elmar [Non-text portions of this message have been removed]
RE: [firebird-support] How do I convert a timestamp to a string from within a stored procedure?
Extract(day,month ... From ?date) And then some ifs and etc Regards, Bogdan Lp, Bogdan Sent from Win 7.5 mobile -Original Message- From: SoftTech Sent: 26.1.2012 12:50 To: firebird-support@yahoogroups.com Subject: [firebird-support] How do I convert a timestamp to a string from within a stored procedure? Greetings All, Does anyone know how I can accomplish this from within a stored procedure? I need to convert a timestamp 01/26/2012 05:38:32am to a string 01262012053832. Will actually be using CURRENT_TIMESTAMP within the stored procedure. Still using Firebird 1.5.3 (Hope to convert down the road) Thanks to all who respond. Mike [The entire original message is not included.] [Non-text portions of this message have been removed]
RE: [firebird-support] How to get the latest record?
A table was created like create table Test(ITEM varchar(20) not null primary key, VAL varchar(20)); It is filled by records like (aaa_20111215, ...) (aaa_20111215, ...) (aaa_20111201, ...) ... My question is how to select the record having the latest date for ITEM? The answer for the above records is (aaa_20111215, ...). Please write a sql to do this. Add an additional DATE column to the table, put an descending index on that column and use MAX to query the record with the most recent date entry. With regards, Thomas Steinmaurer I usually do it this way: Select first 1 test from test where test starting with 'aaa_' order by test descending Presuming it is not always aaa_ in front of date part Bogdan * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php [Non-text portions of this message have been removed]
RE: [firebird-support] How to put an entire Database in memory using cache?
-Original Message- From: Ann Harrison Sent: 20. september 2011 17:44 To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How to put an entire Database in memory using cache? On Tue, Sep 20, 2011 at 10:17 AM, Alexey Kovyazin a...@ib-aid.com wrote: Ann, ... By allocating that much memory to the database page cache, you reduce the amount of memory available for other uses, like the system page cache. if Marcelo wants to achieve better performance, not just put database to the memory, I think it's good to mention here that performance could be worse in this case. It can be very bad in case of Classic/SuperClassic and slightly worse in case of SuperServer (he did not indicate what architecture he uses and how many users are there). Two or three other people had offered better suggestions, and in sections of my message that you didn't quote, I did say that using the database page cache to move the database to memory was not a good idea. However what Marcelo asked was how to calculate the cache size to put his whole database in memory. Whether or not that's a good idea, it is a straight-forward calculation. Certainly such vacuous cases are interesting to discuss, but I think people in support forum can recognize it as signal to change their Firebird configurations, which will lead to unforeseen consequences. If anyone out there is considering using the Firebird configurations to move an whole database into memory, THAT IS A BAD IDEA! Don't do it. Cheers, Ann [Non-text portions of this message have been removed] [The entire original message is not included.] [Non-text portions of this message have been removed]
