[firebird-support] RE: How to transfer one fdb content to another?
Well we have a database that we fill in with data every day. We were working fine, until last thursday I think (or someday this week) that someone on our team accidentally duplicated the .fdb file on our server, and then he updated one file and the other team member updated the second file at the same time. What we wanted to do is to combine the contents of the files (specifically on a single table) so that we will not re enter the files again on one of the databases to keep it updated. Thanks,
[firebird-support] How to transfer one fdb content to another?
Hi, I have a frebird database in which was copied and was updated at the same time. My questions is how can I transfer the content of one of the databases to another so that I can unify it and make it as a single file with all the contents of the 2 separated databases on it? They both share the same content, because they were duplicated and was updated at the same time but with different content. Thanks,
[firebird-support] Error in inputting content in a table
Hello, We are currently having trouble entering data on one of our tables. We are trying to enter data on specific columns, but after refreshing it, the data is lost. Here is the outline of our table: CREATE TABLE PAYMENT ( PAYMENT_DATE Timestamp DEFAULT current_timestamp NOT NULL, PMID Bigint NOT NULL, LID Integer, CID Integer, AMOUNT CURRENCY, BALANCE CURRENCY, OVER CURRENCY, DEFICIT CURRENCY, PAYMENT_TYPE Varchar(5), DATE_OF_PAYMENT Varchar(15), CONSTRAINT PK_PAYMENT PRIMARY KEY (PMID) ); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON PAYMENT TO SYSDBA WITH GRANT OPTION; Dependencies: SET TERM ^ ; ALTER TRIGGER PAYMENT_BI ACTIVE BEFORE INSERT POSITION 0 AS DECLARE VARIABLE tmp DECIMAL(18,0); BEGIN IF (NEW.PMID IS NULL) THEN NEW.PMID = GEN_ID(GEN_PAYMENT_ID, 1); ELSE BEGIN tmp = GEN_ID(GEN_PAYMENT_ID, 0); if (tmp new.PMID) then tmp = GEN_ID(GEN_PAYMENT_ID, new.PMID-tmp); END END^ SET TERM ; ^ SET TERM ^ ; ALTER TRIGGER PAYMENT_BI ACTIVE BEFORE INSERT POSITION 0 AS DECLARE VARIABLE tmp DECIMAL(18,0); BEGIN IF (NEW.PMID IS NULL) THEN NEW.PMID = GEN_ID(GEN_PAYMENT_ID, 1); ELSE BEGIN tmp = GEN_ID(GEN_PAYMENT_ID, 0); if (tmp new.PMID) then tmp = GEN_ID(GEN_PAYMENT_ID, new.PMID-tmp); END END^ SET TERM ; ^ What could be the possible cause for this? the database worked fine in the past days, but after yesterday we cannot enter data to it. Thanks,
[firebird-support] Data transfer from one table to another
Hi all, is it possible to transfer the content of a table to a new one? Thanks,
[firebird-support] Cannot edit fdb content.
Hi we were encoding data yesterday on our fdb database, and after resting for the day we tried connecting again now but everytime we put a new entry it doesn't enter. We are using a delphi application and zeos as driver for it. What could be the problem here? Thanks.
[firebird-support] Can a Firebird database be hosted on a hosting site?
Hello to all, I would like to host my firebird database on a provider, but don't have an idea on where to start. We currently have a firebird database in which is connected via lan. As our company grows, so its needs. We plan to expand on another branch which will be located on a different site, and we plan to have a website so that we can access our database from one branch to another. I tried searching for providers which can host my database, but failed to find one. I was also thinking of hosting it on our own server but don't know where to start also. Thank you very much.
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Ann Harrison aharrison@... wrote: On Fri, May 3, 2013 at 8:07 PM, dice_in_trouble dice_in_trouble@...wrote: You wrote: the client id also doens't auto increment. I asked How do you generate client identifiers? You responded What do you mean by client identifiers? I would like to summarize all the data in a certain column. Summarizing data can be done with computed columns, or with triggers. Triggers introduce a chance for deadlocks but perform better if you have a high read:write ratio because the sum doesn't have to be computed on reference. However, you also mentioned that your client_id field is not generated automatically. One way people get in trouble with Firebird is using something like setting an identifier column with an expression like identifier = MAX(identifier) + 1 I wondered if that was how you were creating unique identifiers. Good luck, Ann [Non-text portions of this message have been removed] Thanks for the reply. Unfortunately I just put a char on the id and without a null option. I encountered once a problem because I used the number 1 already but have it deleted on the record, but I cannot use the number 1 again. Anyways I was planning to make the client ID automatically generates the numbers so that I will not mix up if the time comes that I have lots of data already encoded to the database. Regarding your answer regarding the summary. I honestly can't understand anything from deadlocks to triggers and identifiers. Thank you. Sincerely, Cedris
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, nor...@dunbar-it.co.uk Norman@... wrote: Evening Cedris, When I'm in the office on Tuesday, ill send you some info. A crash course in Firebird. Cheers, Norm. Sent from my HTC [Non-text portions of this message have been removed] Good day Norm, Thank you very much. Have a nice day. Best regards, Cedris
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Norman Dunbar Norman@... wrote: On 02/05/13 11:15, dice_in_trouble wrote: Hi Cedris, Thank you for this wonderful post. Will try this now. Thank you again. You are welcome. I suggest you purchase a book on Firebird, as has been suggested, or at the very least, a decent beginner's guide to database design which will help you understand the subject. I have plans to do a Firebird Database Design manual for the documentation, but as with many things, time is not on my side! So, don't wait for me! One other thing, you might be wondering why I've put the COLLECTION table's payment column as a bigint rather than a floating pint of some kind? That was just for simplicity. Currency is a difficult subject to get exact values in when using a floating point data type (. It's best to use a fixed point data type of some kind (decimal) or count in pennies (in my case) (bigint) rather than in pounds and pennies. However, the data type DECIMAL(18,4) - means 18 digits wide with 4 of them appearing after the decimal - is useful for a CURRENCY type, so you could create a domain. CREATE DOMAIN CURRENCY AS DECIMAL(18,4); In the UK, that's £99,999,999,999,999. - which is about my hourly rate! ;-) Seriously, it's a value between -2**63 and +2**63 -1. Why 18,4? If you are dividing, for some reason, monetary values, the extra precision will help avoid too many rounding errors. DECIMALs are good for counting currencies, but beware if the currency you are counting (or accumulating) could become very large - see above. The Italian Lira (as it was) for example, could soon fill a CURRENCY type. The Euro, might take a bit longer! And a DECIMAL is limited to 18 digits. Have fun. 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 OIC. Thank you very much norm for the informative and very useful post and example you have given me. I would love to buy a book on firebird but I don't have a credit card, and I cannot find one here in our country. I'm from the Philippines BTW. And also regarding database design what book is advisable for beginners like myself? Regarding the big int, floating int, etc. I can't still understand them. I just put decimal in the value for integers because I have a TButton that automatically computes the balance left (which was my first design). That formula was executed successfully, however, I have difficulty updating my collection (because it updates daily.) and btw how can I include formulas like from excel to my firebird database? I would like to sum up the total payments made in the collection and the balance on the clients table, which will appear at the bottom and cannot be edited unless I change or add a value to the balance and payment.
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, dice_in_trouble dice_in_trouble@... wrote: --- In firebird-support@yahoogroups.com, Norman Dunbar Norman@ wrote: On 02/05/13 11:15, dice_in_trouble wrote: Hi Cedris, Thank you for this wonderful post. Will try this now. Thank you again. You are welcome. I suggest you purchase a book on Firebird, as has been suggested, or at the very least, a decent beginner's guide to database design which will help you understand the subject. I have plans to do a Firebird Database Design manual for the documentation, but as with many things, time is not on my side! So, don't wait for me! One other thing, you might be wondering why I've put the COLLECTION table's payment column as a bigint rather than a floating pint of some kind? That was just for simplicity. Currency is a difficult subject to get exact values in when using a floating point data type (. It's best to use a fixed point data type of some kind (decimal) or count in pennies (in my case) (bigint) rather than in pounds and pennies. However, the data type DECIMAL(18,4) - means 18 digits wide with 4 of them appearing after the decimal - is useful for a CURRENCY type, so you could create a domain. CREATE DOMAIN CURRENCY AS DECIMAL(18,4); In the UK, that's £99,999,999,999,999. - which is about my hourly rate! ;-) Seriously, it's a value between -2**63 and +2**63 -1. Why 18,4? If you are dividing, for some reason, monetary values, the extra precision will help avoid too many rounding errors. DECIMALs are good for counting currencies, but beware if the currency you are counting (or accumulating) could become very large - see above. The Italian Lira (as it was) for example, could soon fill a CURRENCY type. The Euro, might take a bit longer! And a DECIMAL is limited to 18 digits. Have fun. 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 OIC. Thank you very much norm for the informative and very useful post and example you have given me. I would love to buy a book on firebird but I don't have a credit card, and I cannot find one here in our country. I'm from the Philippines BTW. And also regarding database design what book is advisable for beginners like myself? Regarding the big int, floating int, etc. I can't still understand them. I just put decimal in the value for integers because I have a TButton that automatically computes the balance left (which was my first design). That formula was executed successfully, however, I have difficulty updating my collection (because it updates daily.) and btw how can I include formulas like from excel to my firebird database? I would like to sum up the total payments made in the collection and the balance on the clients table, which will appear at the bottom and cannot be edited unless I change or add a value to the balance and payment. the client id also doens't auto increment.
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Ann Harrison aharrison@... wrote: On Fri, May 3, 2013 at 2:16 PM, dice_in_trouble dice_in_trouble@...wrote: and btw how can I include formulas like from excel to my firebird database? I would like to sum up the total payments made in the collection and the balance on the clients table, which will appear at the bottom and cannot be edited unless I change or add a value to the balance and payment. Look at any information you can find about computed fields. the client id also doens't auto increment. How do you generate client identifiers? Good luck, Ann ++ 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 [Non-text portions of this message have been removed] Ann, What do you mean by client identifiers? I would like to summarize all the data in a certain column. Thanks. Cedris
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Norman Dunbar Norman@... wrote: Morning dice_in_trouble, If I understand you correctly, you have or wish to have the following: CLIENT TABLE: CLIENT DATE BALANCE ADDRESS COLLECTIONS TABLE: TIMESTAMP PAYMENT CLIENT BALANCE And if you update the client table you want the client and/or balance updated on the collections table, and if you update the collections table, you wish for the client and/or balance on the client table to be updated. I see a problem, you could end up in a circular loop where an update to one table causes an update to the other which updates the first which updates the second which .. I think your design, as I understand it, is wrong. In a relational database you should not be duplicating information, this is what normalisation does for you, it helps you design your tables correctly. Data should be updated once, in one place and that update will be visible everywhere. Off the top of my head, I would suggest something like the following is needed: CLIENT TABLE: CLIENT_ID CLIENT_NAME ADDRESS ... BALANCE TABLE: CLIENT_ID DATE? BALANCE COLLECTIONS TABLE: TIMESTAMP PAYMENT CLIENT_ID CLIENT_ID in BALANCE and COLLECTION will be a foreign key referencing the client_id in the CLIENT table. Also, CLIENT_ID in the COLLECTIONS TABLE will be a foreign key referencing the BALANCE table. I'm not sure why you would need a DATE field (which is most likely a reserved word, but you can check that for me!) in the CLIENT table. The balance for each client, if your processing is correct, is as of right now. So, you can now update the client details in one place, the CLIENT table. Payments update the COLLECTION table, and, if necessary, a trigger could then add the payment into the BALANCE for that CLIENT_ID. However, if a payment by cheque, for example, needs 4 days to clear, you may have to do some batch processing to update cheques that cleared into the BALANCE table. It depends on your needs. Maybe you would have a PAYMENT_CLEARED flag in the COLLECTIONS table, and when that was updated to show that the payment had cleared, then a trigger would update the BALANCE table for that client. You might have a PENDING_PAYMENTS_BALANCE on the BALANCE table to total up the payments that have been made, but have not yet been cleared for that particular client. It all depends on what your requirements are. 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 Thanks Nrom for the reply. I also thought of this idea, wherein I make a separate table for the balance then link it up to the other 2 tables. But I don't know how to make table relations. I am using flamerobin to build up my firebird database. Can you give me a simple example of making a relationship between 3 tables?
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Werner F. Bruhin werner.bruhin@... wrote: Hi, On 02/05/2013 10:47, dice_in_trouble wrote: ... Thanks Nrom for the reply. I also thought of this idea, wherein I make a separate table for the balance then link it up to the other 2 tables. But I don't know how to make table relations. I am using flamerobin to build up my firebird database. Can you give me a simple example of making a relationship between 3 tables? You would use alter table for this, not sure how one uses this in flamerobin as I don't use it to generate my databases (I use Python/SQLAlchemy and after I designed my ORM declarative classes SQLAlchemy does the creation for me. Sometimes I do it manually for testing something then I use IBExpert (just because I new it before flamerobin), it has an option create foreign key which then walks you through the process. I am no expert but to me your question makes me think that you might need to read up a bit more about databases, SQL and Firebird, you should be able to find tutorials with a google search. Obviously the FB documentation is also not to be forgotten. http://www.firebirdsql.org/en/reference-manuals/ Maybe invest in the Firebird Book, e.g. the second Edition Quote: The Firebird Book (Second Edition) is now available as a print on demand book (at createspace) in three parts, Volume 1: `Firebird Fundamentals https://www.createspace.com/4203352 Volume 2: `Developing with Firebird Data https://www.createspace.com/4206843 Volume 3: `Administering Firebird Servers and Databases https://www.createspace.com/4206991 Regards Paul EndQuote Werner [Non-text portions of this message have been removed] Thanks Paul for the advice. I have read some of the documentations on the firebird site, but I am having a hard time understanding them, and I have posted here hoping I might get some help on how to make my database as I want it. Regards, Cedris
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Norman Dunbar Norman@... wrote: On 02/05/13 09:47, dice_in_trouble wrote: Thanks Nrom for the reply. I also thought of this idea, wherein I make a separate table for the balance then link it up to the other 2 tables. But I don't know how to make table relations. I am using flamerobin to build up my firebird database. Can you give me a simple example of making a relationship between 3 tables? Start FlameRobin and comment to the database with the appropriate user. Right click TABLES and select Create New. In the dialogue that appears, type the following: CREATE TABLE client ( client_id bigint not null, client_name varchar(100) not null, client_address varchar(1024), constraint pk_client primary key (client_id) ); CREATE TABLE balance ( client_id bigint not null, balance bigint, constraint pk_balance primary key (client_id), constraint fk_client_balance foreign key (client_id) references client(client_id) ); CREATE TABLE collection ( collection_id bigint not null, client_id bigint not null, payment bigint not null, date_received timestamp default current_timestamp not null , constraint pk_collection primary key (collection_id), constraint fk_client_collection foreign key (client_id) references client (client_id) ); Press F4 to execute the statement. Commit; The client name and address are not in an ideal format, but for this example, they will suffice. You now have three tables that are in a parent child relationship with client being the parent and two child tables - balance and collection. The FK constraints mean that you cannot accept a payment for a client you don't have, and, you cannot create a balance for a client you don't have. Also, there's probably no need to have a FK between collection and balance (balance being the parent) because the two FKs that exists already mean that you can't have a payment for a balance that doesn't exist. This design (I use the term loosely as I haven't really designed it, just whipped it off the top of my head) means that you can set up clients without any balances or collections. Which is correct - if a client has yet to make a payment, they have no balance. Your code, business rules etc will determine when a balance gets created - when the customer is created, or when a payment arrives and is the first one for the client. equally, you are unable to delete (or change the client_id) for a client while there are existing balance or collection records for that client_id. Now, obviously, there will need to be triggers and sequences to get the client_id and collection_id values (they are surrogate keys as opposed to natural keys) so: Under the CLIENT table, right click the CLIENT_ID column and select Properties. In the form that appears, under autoincrement, click create new generator and create trigger. Click execute and then commit. Do similar for the COLLECTION table. If the tables don't have the ability to drop down the columns, double click the table to force a refresh. Close the window that appears and then you should be able to drop down the list of columns for the tables. Now you can insert data into the CLIENT table and also create BALANCEs and COLLECTIONs for that client. You should note that the primary key of the BALANCE table is actually the primary key of the CLIENT table. This is not a problem. The client_id is unique and allows the balance for that client to be easily found, once you have found the client's id. HTH -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767 Thank you for this wonderful post. Will try this now. Thank you again. Sincerely, Cedris
[firebird-support] how to use the same column from a single table to other tables?
I have 2 tables, namely clients and collection. I have on the client table the columns named client,date,balance and address respectively. The daily collection has a column named time stamp and payment. But it also need to have the list of client and balances from the clients table and I want the client table to be updated if I edit the collection, same goes to the collection table, I want it to be updated when I enter data on the client table. The columns that I need to have a relation are the client and balance columns but I have no idea on how to do it. Caon somebody please guide me on this. Thank you very much.
[firebird-support] building table relationships on a firebird database
I have a Firebird database with 2 tables namely masterlist and daily collection. I used Zeos 7.0.3 to access my Firebird database. My masterlist contains the following columns: #9556;#9552;#9552;#9552;#9552;#9552;#9552;#9574;#9552;#9552;#9552;#9552;#9552;#9552;#9574;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9574;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9559; #9553; name #9553; date #9553; balance #9553; status #9553; #9562;#9552;#9552;#9552;#9552;#9552;#9552;#9577;#9552;#9552;#9552;#9552;#9552;#9552;#9577;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9577;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9565; My daily collection contains the following columns: #9556;#9552;#9552;#9552;#9552;#9552;#9552;#9574;#9552;#9552;#9552;#9552;#9552;#9552;#9574;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9574;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9559; #9553; date #9553; name #9553; payment #9553; balance #9553; #9562;#9552;#9552;#9552;#9552;#9552;#9552;#9577;#9552;#9552;#9552;#9552;#9552;#9552;#9577;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9577;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9552;#9565; I would like to build a relation in which the balance from masterlist will be copied to the balance column of the daily collection, and when I update the column in the daily collection it will also update the content of the masterlist.
[firebird-support] how to build a relationship between 2 tables in firebird?
I have a Firebird database with 2 tables namely masterlist and daily collection. I used Zeos 7.0.3 to access my Firebird database. My masterlist contains the following columns: name date balance status My daily collection contains the following columns: date name payment balance I would like to build a relation in which the balance from masterlist will be copied to the balance column of the daily collection, and when I update the column in the daily collection it will also update the content of the masterlist.
[firebird-support] autodate and autofill columns
hello i have a firebird database in which i have columns namely date, name, balance and payment i want my software to publish a list of the content with the date being adjusted daily and the content being the same, e.g. datenamebalance payment 20/4/13 client1 100 20/4/13 client2 200 then tomorrow it will auto fill the cells with the same data on the name and balance columns but the date will be set on the current date. is this possible? if it is can somebody help me on this matter? btw i am using zeoslib and delphi 7 thanks
[firebird-support] how to call 'commit' after you exit the cell being edited?
i had this experience earlier in which i need to close the app for the db grid to update even i have these components: 1 zconnection 1 ztable 1 datasource 1 dbgrid 1 navigation bar if i press refresh on the nav bar, the entry on the other computer wont appear. i would like the dbgrid to update when i click the refresh button whenever someone edit it's contents. i also think that the database might overlap the entries from 2 or more users. some users say i need to call commit, how do i do that and do i need to put it on every db grid i have on the application? btw i am using delphi 7 and zeoslib 7.0.3 thanks
[firebird-support] Re: how to call 'commit' after you exit the cell being edited?
--- In firebird-support@yahoogroups.com, Mark Rotteveel mark@... wrote: On Fri, 19 Apr 2013 15:44:57 -, dice_in_trouble dice_in_trouble@... wrote: i had this experience earlier in which i need to close the app for the db grid to update even i have these components: 1 zconnection 1 ztable 1 datasource 1 dbgrid 1 navigation bar if i press refresh on the nav bar, the entry on the other computer wont appear. i would like the dbgrid to update when i click the refresh button whenever someone edit it's contents. i also think that the database might overlap the entries from 2 or more users. some users say i need to call commit, how do i do that and do i need to put it on every db grid i have on the application? btw i am using delphi 7 and zeoslib 7.0.3 I have never used Zeoslib, but the ZConnection has a Commit method. Be aware though that the writer needs to commit, but also the reader (otherwise the reader will not see the updates!) Mark can it be done in the object inspector? or will i need to put a code? either way what do you mean the reader and writer needs to commit? you mean i need to commit on both application?
[firebird-support] firebird connection with zeoslib and delphi 7
hello i am using zeoslib 7.0.2, firebird 2.5.2 and delphi 7. i have a app that connects to my firebird database locally. i have set it up and it connects. but when i tried connecting via the local network, there is always an error when i am connecting. i used a zconnection and put in the database field on the object inspector the ip and path of my server and database (e.g. 192.168.1.1:c:\test.fdb and put on the hostname field the ip of the server i wish to connect (e.g. 192.168.1.1) i have disabled the firewall and even opened the port manually when i tried connecting but still got errors. can anybody advise me on the settings of my zconnection?
