[firebird-support] Re: Firebird book on the DVD - three different versions?
Does anyone know what is contained on the DVD besides PDFs? When you purchase the DVD (which contains an enormous amount of additional resource material) I would like to know what is this enormous amount of material :)
Re: [firebird-support] Re: Firebird book on the DVD - three different versions?
At 07:41 p.m. 18/06/2013, un_spoken wrote: Does anyone know what is contained on the DVD besides PDFs? When you purchase the DVD (which contains an enormous amount of additional resource material) I would like to know what is this enormous amount of material :) http://www.ibphoenix.com/products/dvd/developer_dvd lists most of it. ./heLen
[firebird-support] Re: Recursive lookup
I don't quite get what you're trying to do, I'll guess, but feel free to reformulate your problem if my guessing is wrong. My guess is that you're trying to do, is to traverse two trees and join them together. Hi, Thanks, you saved my day! To explain what I use this select query for: It's about a typical project (in this case about Derelict and Abandoned mines in South Africa) where you have activity threads (thread type classified by using a tree-structure - Progress_Type_ID linked to the the tree-structure) describing what the team members are busy with and what they completed. Think about a typical web forum (such as this one) where you ask a question under a topic, someone reply and you explain etc. till the thread dies a natural death. In my web application, users may load maps, documents etc. and then any node of the thread my be linked to one or more pre-loaded documents. Also the progress activity threads may be displayed in a web grid and printed as a proper formatted document. I use the ALevel variable in the query to do the indenting (nbsp for indents in the browser and a few blank spaces in the ReportBuilder report - HTML or pdf). Typical these kind of projects carry on over several years or even decades. Team members come and go. Hopefully this web application will capture the memory of the history of the project's activities. Regards, Nols Smit
[firebird-support] Re: Firebird rookie - help selecting correct Firebird Version
Hi Helen, Many thanks for clarifying.. Geoff --- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote: At 11:23 p.m. 16/06/2013, you wrote: Hi, I'm considering using Firebird for my database applications. My applications are not large and certainly cater for (usually!) around 10 users on a LAN. I use a single database and it is usually accessed by users their own individual instances of my application. Can I use Firebird Embedded for this, or should I use the full client server version? If the full client/server, which version Classic, SuperClassic or Superserver? Well, the beautiful thing is, you don't have to decide that until you have a real environment to deploy to. These are not different versions of Firebird but different execution models of the same database engine, that allow you to deploy the same software to sites varying from a single user in an attic to thousands of users in an enterprise in the way that best suits your data volume, hardware resources and network availability. With 10 users on a LAN it doesn't make any sense to consider deploying 10 stand-alone installations - unless, for some absurd reason, every employee has to maintain his or her own database. That sounds like a management nightmare. Firebird is designed to make one database accessible to everyone on a network, as clients. Embedded is good when you have one and only one client: the attic user or as the back-end to a managed client layer such as a web-application. For two or more clients, you're looking at a client/server deployment. Helen Borrie, Support Consultant, IBPhoenix (Pacific) Author of The Firebird Book and The Firebird Book Second Edition http://www.firebird-books.net __
[firebird-support] Re: Does Firebird have a way to reference similar named fields? (Like MONTH_1 thru MONTH_120)
I think you have 2 options:
1. normalize the table to have 2 fields Month Number Month Value, then
you can search record by Month Number for updating Month Value
2. use PSQL statement: EXECUTE STATEMENT, you can read about it in
Firebird-2.5-LangRef-Update.pdf
--- In firebird-support@yahoogroups.com, Softtech Support stwizard@...
wrote:
In Delphi I have the ability to reference a field using FieldByName() inside
a For loop like this:
For I := 1 to 120 do
begin
with cdsReport do
begin
FieldByName('MONTH_' + IntToStr(I)).Visible := iNoOfCols = I;
end;
end;
Is there any way to do this in a Firebird Stored Procedure? I have 120
fields named MONTH_1 thru MONTH_120 and currently I have to reference them
like this. Just wanting to know if there is a better way..
...
I = 1;
WHILE (I = iNoOfColumns ) DO
BEGIN
IF (I = 1) THEN
MONTH_1 = MONTH_1 + nTotalMonth;
ELSE IF (I = 2) THEN
MONTH_2 = MONTH_2 + nTotalMonth;
ELSE IF (I = 3) THEN
MONTH_3 = MONTH_3 + nTotalMonth;
ELSE IF (I = 4) THEN
MONTH_4 = MONTH_4 + nTotalMonth;
ELSE IF (I = 5) THEN
MONTH_5 = MONTH_5 + nTotalMonth;
ELSE IF (I = 6) THEN
MONTH_6 = MONTH_6 + nTotalMonth;
ELSE IF (I = 7) THEN
MONTH_7 = MONTH_7 + nTotalMonth;
ELSE IF (I = 8) THEN
MONTH_8 = MONTH_8 + nTotalMonth;
ELSE IF (I = 9) THEN
MONTH_9 = MONTH_9 + nTotalMonth;
ELSE IF (I = 10) THEN
MONTH_10 = MONTH_10 + nTotalMonth;
ELSE IF (I = 11) THEN
MONTH_11 = MONTH_11 + nTotalMonth;
ELSE IF (I = 12) THEN
MONTH_12 = MONTH_12 + nTotalMonth;
ELSE IF (I = 13) THEN
MONTH_13 = MONTH_13 + nTotalMonth;
ELSE IF (I = 14) THEN
MONTH_14 = MONTH_14 + nTotalMonth;
ELSE IF (I = 15) THEN
MONTH_15 = MONTH_15 + nTotalMonth;
ELSE IF (I = 16) THEN
MONTH_16 = MONTH_16 + nTotalMonth;
ELSE IF (I = 17) THEN
MONTH_17 = MONTH_17 + nTotalMonth;
ELSE IF (I = 18) THEN
MONTH_18 = MONTH_18 + nTotalMonth;
ELSE IF (I = 19) THEN
MONTH_19 = MONTH_19 + nTotalMonth;
ELSE IF (I = 20) THEN
MONTH_20 = MONTH_20 + nTotalMonth;
ELSE IF (I = 21) THEN
MONTH_21 = MONTH_21 + nTotalMonth;
ELSE IF (I = 22) THEN
MONTH_22 = MONTH_22 + nTotalMonth;
ELSE IF (I = 23) THEN
MONTH_23 = MONTH_23 + nTotalMonth;
ELSE IF (I = 24) THEN
MONTH_24 = MONTH_24 + nTotalMonth;
ELSE IF (I = 25) THEN
MONTH_25 = MONTH_25 + nTotalMonth;
ELSE IF (I = 26) THEN
MONTH_26 = MONTH_26 + nTotalMonth;
ELSE IF (I = 27) THEN
MONTH_27 = MONTH_27 + nTotalMonth;
ELSE IF (I = 28) THEN
MONTH_28 = MONTH_28 + nTotalMonth;
ELSE IF (I = 29) THEN
MONTH_29 = MONTH_29 + nTotalMonth;
ELSE IF (I = 30) THEN
MONTH_30 = MONTH_30 + nTotalMonth;
ELSE IF (I = 31) THEN
MONTH_31 = MONTH_31 + nTotalMonth;
ELSE IF (I = 32) THEN
MONTH_32 = MONTH_32 + nTotalMonth;
ELSE IF (I = 33) THEN
MONTH_33 = MONTH_33 + nTotalMonth;
ELSE IF (I = 34) THEN
MONTH_34 = MONTH_34 + nTotalMonth;
ELSE IF (I = 35) THEN
MONTH_35 = MONTH_35 + nTotalMonth;
ELSE IF (I = 36) THEN
MONTH_36 = MONTH_36 + nTotalMonth;
ELSE IF (I = 37) THEN
MONTH_37 = MONTH_37 + nTotalMonth;
ELSE IF (I = 38) THEN
MONTH_38 = MONTH_38 + nTotalMonth;
ELSE IF (I = 39) THEN
MONTH_39 = MONTH_39 + nTotalMonth;
Re: [firebird-support] Re: Does Firebird have a way to reference similar named fields? (Like MONTH_1 thru MONTH_120)
Thanks for your reply.
I should have mentioned that this stored procedure is used to generate a
Stair-Step Financial Report (My first attempt at such a report I might add) and
therefore option 1 below would not work as I do not need to store the results.
I have used EXECUTE STATEMENT in the past for SELECT statements and even after
looking again at the LangRef-Update.pdf (For v1.5) I do not see how it could be
used for this instance. Am I missing something?
A little more info:
I basically created a stored procedure that would generate a Stair-Step
Financial report that can list the number of accounts/cases turned, amount
turned, average amount turned for collections for a given period, followed by
the amount received for every month since the given period.
The report works as follows:
If Client X has been a client for two years, then it would be a 24 x 24 report.
Shown below is an example of a client signed on Oct 2012 and we started
receiving accounts in Dec 2012. So the report is ran for the first year
10/01/12 thru 06/30/13 and is a 9 x 9 report (9 rows - Periods x 9 columns -
Monthly collections).
The start date for this report is 10/01/12 and every month following the first
has to be listed whether they had acounts turned to them for collections or
not. Then the Recovery would show how much was received in each month
following the turn.
Obviously, each row has one less column filled out..so when you get to the last
row, there is only Month 1 on the report.
PERIOD CASES PRINC_AMT AVG_CASE MONTH_1 MONTH_2 MONTH_3 MONTH_4 MONTH_5
MONTH_6 MONTH_7 MONTH_8 MONTH_9
Oct-12 0 0 000000
000 0
Nov-12 0 0 000000
000 0
Dec-12292 162894.14557.85 1101.64 2721.09 3510.35 2571.71 381.21
256.6425.000 0
Jan-13229 161576.07705.57 3802.94 762.90 3995.06 1062.37 1433.10
3206.2500 0
Feb-13182 150174.56825.13 1315.53 1502.86 1920.4660.0087.74
000 0
Mar-13158 122131.82772.98 187.00 1867.37 1805.65 4251.790
000 0
Apr-13207 157906.67762.83 1629.24 2649.20 500.8300
000 0
May-13208 166616.77801.04 167.23 1800.69000
000 0
Jun-13294 236136.50803.18 200.000000
000 0
Is there somewhere I can post attachments? If so then I could post the full
stored procedure and an excel file that was generated by it.
So any other ideas how to modify this stored procedure?
Mike
- Original Message -
From: thp_pkmi
To: firebird-support@yahoogroups.com
Sent: Tuesday, June 18, 2013 5:14 AM
Subject: [firebird-support] Re: Does Firebird have a way to reference similar
named fields? (Like MONTH_1 thru MONTH_120)
I think you have 2 options:
1. normalize the table to have 2 fields Month Number Month Value, then
you can search record by Month Number for updating Month Value
2. use PSQL statement: EXECUTE STATEMENT, you can read about it in
Firebird-2.5-LangRef-Update.pdf
--- In firebird-support@yahoogroups.com, Softtech Support stwizard@...
wrote:
In Delphi I have the ability to reference a field using FieldByName()
inside a For loop like this:
For I := 1 to 120 do
begin
with cdsReport do
begin
FieldByName('MONTH_' + IntToStr(I)).Visible := iNoOfCols = I;
end;
end;
Is there any way to do this in a Firebird Stored Procedure? I have 120
fields named MONTH_1 thru MONTH_120 and currently I have to reference them like
this. Just wanting to know if there is a better way..
...
I = 1;
WHILE (I = iNoOfColumns ) DO
BEGIN
IF (I = 1) THEN
MONTH_1 = MONTH_1 + nTotalMonth;
ELSE IF (I = 2) THEN
MONTH_2 = MONTH_2 + nTotalMonth;
ELSE IF (I = 3) THEN
MONTH_3 = MONTH_3 + nTotalMonth;
ELSE IF (I = 4) THEN
MONTH_4 = MONTH_4 + nTotalMonth;
ELSE IF (I = 5) THEN
MONTH_5 = MONTH_5 + nTotalMonth;
ELSE IF (I = 6) THEN
MONTH_6 = MONTH_6 + nTotalMonth;
ELSE IF (I = 7) THEN
MONTH_7 = MONTH_7 + nTotalMonth;
ELSE IF (I = 8) THEN
MONTH_8 = MONTH_8 + nTotalMonth;
ELSE IF (I = 9) THEN
MONTH_9 = MONTH_9 + nTotalMonth;
ELSE IF (I = 10) THEN
MONTH_10 = MONTH_10 + nTotalMonth;
ELSE IF (I = 11) THEN
MONTH_11 = MONTH_11 + nTotalMonth;
ELSE IF (I = 12) THEN
MONTH_12 = MONTH_12 + nTotalMonth;
ELSE IF (I = 13) THEN
MONTH_13 = MONTH_13 + nTotalMonth;
ELSE IF (I = 14) THEN
MONTH_14 = MONTH_14 + nTotalMonth;
ELSE IF (I = 15) THEN
MONTH_15 = MONTH_15 + nTotalMonth;
ELSE IF (I = 16) THEN
MONTH_16 = MONTH_16 +
[firebird-support] Charset for external table filename
Hi, I just tried to create an external table with a filename that contains ö. The database charset is UTF-8 and I did it from FlameRobin using UTF-8 as connection character set. It failed because the filename obviously got encoded as UTF-8, and passed like that to the server's file system, resulting in a filename with ö instead of ö. This can't be right... How is this supposed to work and how am I supposed to get it right? I would have thought that the Firebird server would be aware that the string literals it receives from the client are encoded in the connection charset, and transliterate to the file system's charset before trying to create/access the file. Am I missing something? Regards, Kjell -- -- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64
[firebird-support] Primary Key Initial Value
In creating a new table, how is primary key set to begin at a value other than 0 or 1?
Re: [firebird-support] Primary Key Initial Value
Afternoon jwcane2003, On 18/06/13 14:26, jwcane2003 wrote: In creating a new table, how is primary key set to begin at a value other than 0 or 1? Imagine this: create table whatever ( pk_column bigint not null , other_stuff char(100), ... ); commit; alter table whatever add constraint pk_whatever primary key (pk_column); commit; Insert into whatever (pk_column, other_stuff) values (667, 'The neighbour of the beast!'); commit; In this example, the pk_column will be set to whatever value is passed to the INSERT statement and need not start at zero or one. If, as is possible, you are using a sequence to generate the values - say via a trigger, then you simply set the sequence value to the desired starting number. So, if you used a sequence named seq_pk_whatever, you could - before using it for the first time - execute the following to ensure that the next value generated will be 667: set generator seq_pk_whatever to 666; HTH Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
Re: [firebird-support] Primary Key Initial Value
In creating a new table, how is primary key set to begin at a value other than 0 or 1? begin suggests that you plan on some form of auto-incrementing a value for the primary key field? If so, I'd say you'll need a generator anyway. You can set this generator to any (valid) value you like, but you should always be aware that especially setting it to a lower value than before can mean a lot of trouble. Here's the syntax for Firebird 2.5 ALTER SEQUENCE Generator_Name RESTART WITH new_Value
[firebird-support] Problem with not null fields
Hello, I have some BDs, where fields not null are without the check not null. For example, all tables have a field 'codigo' not null, now all this fields are not check not null. Someone know what could happened? Could be some option or problem in restore? Thanks Rejane
[firebird-support] Why a database grows after a cycle backup/restore?
Hello everybody Which can be the cause that a database of 3.1 gigabytes grows to 4.2 gigabytes after a cycle backup/restore? The file FIREBIRD.CONF seems normal, nothing strange there. Growing 1.1 gigabytes seems to me too much. Greetings. Walter. [Non-text portions of this message have been removed]
RE: [firebird-support] Why a database grows after a cycle backup/restore?
Walter, Which can be the cause that a database of 3.1 gigabytes grows to 4.2 gigabytes after a cycle backup/restore? The file FIREBIRD.CONF seems normal, nothing strange there. Growing 1.1 gigabytes seems to me too much. To be clear, it is source database or the backup file 3.1GB? Sean
Re: [firebird-support] Why a database grows after a cycle backup/restore?
The source database has 3.1 Gb but after the backup/restore have 4.2 Gb Seems very strange for me. Greetings. Walter. On Tue, Jun 18, 2013 at 6:03 PM, Leyne, Sean s...@broadviewsoftware.comwrote: ** Walter, Which can be the cause that a database of 3.1 gigabytes grows to 4.2 gigabytes after a cycle backup/restore? The file FIREBIRD.CONF seems normal, nothing strange there. Growing 1.1 gigabytes seems to me too much. To be clear, it is source database or the backup file 3.1GB? Sean [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Why a database grows after a cycle backup/restore?
Em 18/6/2013 19:44, W O escreveu: The source database has 3.1 Gb but after the backup/restore have 4.2 Gb Seems very strange for me. Greetings. Walter. Just a shot in the dark... Could be that the original database was restored with -USE_ALL_SPACE and the new database was not be restored with the same option ? see you !
[firebird-support] Understanding query performance changes
Hi, In upgrading from Firebird 2.0 to Firebird 2.5, I have a query that has dramatically changed performance. It would be great if I could understand the performance information given in FlameRobin, and how this can be used to return the query to its former performance. The statistics reported by FlameRobin for the two database versions are as follows: Firebird 2.5: 827,493,131 fetches, 6 marks, 509 reads, 6 writes. 0 inserts, 0 updates, 0 deletes, 2,376,096 index, 200,925,311 seq. Delta memory: 118,904 bytes. Total execution time: 00:13:04 (hh:mm:ss) Firebird 2.0: 16,930,040 fetches, 6 marks, 122,122 reads, 6 writes. 0 inserts, 0 updates, 0 deletes, 2,376,082 index, 1,252,998 seq. Delta memory: 88,432 bytes Total execution time: 18.109s The Firebird 2.5 query was run with the Windows Classic Server 2.5.1.26351 64-bit. The database has the ODS Version 11.2 with a page size of 8192 and a default character set of UTF8. Page buffers is set to 2048. The Firebird 2.0 query was run with the Windows Classic Server 2.0.5.13206 32-bit. The database has ODS Version 11 with a page size of 4096 and a default character set of ASCII. Page buffers is set to 75. The query run is the following: SELECT Phrase.* FROM Phrase INNER JOIN ( SELECT DISTINCT Phrase.PhraseKey FROM Phrase, ( SELECT PhraseKey, ParentPhraseKey FROM Phrase WHERE Description CONTAINING 'Some Value' AND PhraseologyKey = (SELECT PhraseologyKey FROM Phraseology WHERE Name = 'Some Name') ) Matches WHERE ( ( ( SELECT Flag FROM HAS_PHRASEOLOGY_PARENT( Phrase.PhraseKey, Matches.PhraseKey ) ) = 1 ) OR ( ( SELECT Flag FROM HAS_PHRASEOLOGY_PARENT( Matches.PhraseKey, Phrase.PhraseKey ) ) = 1 ) ) ) T ON (Phrase.PhraseKey = T.PhraseKey) OR (Phrase.ParentPhraseKey = T.PhraseKey) HAS_PHRASEOLOGY_PARENT is a stored procedure with the following definition: SET TERM ^ ; CREATE PROCEDURE HAS_PHRASEOLOGY_PARENT ( PHRASEKEY Char(18) CHARACTER SET OCTETS, PARENTPHRASEKEY Char(18) CHARACTER SET OCTETS ) RETURNS ( FLAG Integer ) AS DECLARE VARIABLE CurrentKey CHAR(18) CHARACTER SET OCTETS; BEGIN CurrentKey = PhraseKey; SELECT 0 FROM rdb$database INTO FLAG; WHILE (CurrentKey IS NOT NULL) DO BEGIN IF (CurrentKey = ParentPhraseKey) THEN BEGIN Flag = 1; CurrentKey = NULL; END ELSE BEGIN SELECT ParentPhraseKey FROM Phrase WHERE PhraseKey = :CurrentKey INTO :CurrentKey; END END SUSPEND; END^ SET TERM ; ^ The query plan for both database versions is: PLAN (T MATCHES PHRASEOLOGY INDEX (RDB$33)) PLAN (RDB$DATABASE NATURAL)(PHRASE INDEX (RDB$PRIMARY31)) PLAN (RDB$DATABASE NATURAL)(PHRASE INDEX (RDB$PRIMARY31)) PLAN JOIN (SORT (JOIN (T PHRASE NATURAL, T MATCHES PHRASE NATURAL)), PHRASE INDEX (RDB$PRIMARY31, PHRASEPARENTSORTORD)) The schema of the PHRASE table is: CREATE TABLE PHRASE ( PHRASEKEY Char(18) CHARACTER SET OCTETS NOT NULL, PHRASEOLOGYKEY Char(18) CHARACTER SET OCTETS NOT NULL, CODE Varchar(20), DESCRIPTION Blob sub_type 1, PARENTPHRASEKEY Char(18) CHARACTER SET OCTETS, UNIT Varchar(12), SORTORD Integer NOT NULL, DATEADD Timestamp NOT NULL, DATEMOD Timestamp NOT NULL, PRIMARY KEY (PHRASEKEY) ); CREATE INDEX PHRASEPARENTSORTORD ON PHRASE (PARENTPHRASEKEY,SORTORD); The schema of the PHRASEOLOGY table is: CREATE TABLE PHRASEOLOGY ( PHRASEOLOGYKEY Char(18) CHARACTER SET OCTETS NOT NULL, NAME Varchar(50) NOT NULL, NOTES Blob sub_type 1, DATEADD Timestamp NOT NULL, DATEMOD Timestamp NOT NULL, PRIMARY KEY (PHRASEOLOGYKEY), UNIQUE (NAME) ); Regards, Gareth Marshall
