Re: [firebird-support] Replication of Firebird Databases (meany to one)
Andy, The way to know if a DB is accessible (online, and if the customer network can reach you - can not be interconnected networks) is to make a small application with a connection and attempting to connect. With VB6, at this link, you have an explanation of how to do such application. http://www.daniweb.com/software-development/visual-basic-4-5-6/threads/352387/how-to-create-a-connection-with-adodb There you see you can do it easily, may be stil not using ODBC. But, if by any reason, you prefer it, change the connection string for using an ODBC driver. With VB6, you have an connection builder, that can build it for you in both ways, with/out ODBC. Good luck, Roberto Camargo. On Saturday, December 7, 2013 6:24 AM, marcus wrote: Hej all, Andrew Gable wrote: > Hi all, > > Does anyone know any good free to use replication software. > > What i have is as follows > > I have 4-20 terminals with a database that holds all the products/ > cashier ids etc in it and I want the system to sync the local database > > to it the server and then delete any data that his has processed > > can this be done with Replication or do I need to create my own app to > handle this Sorry, could't resist: first answers @ google when searching for "firebird replication" http://www.firebirdsql.org/en/some-solutions-to-old-problems/ http://www.firebirdfaq.org/faq249/ http://www.symmetricds.org/ http://www.ibphoenix.com/download/tools/replication IIRC, Holger Klemt (ibexpert.net) has written an article about how to build your own replication some few years (~2005?) ago in a german developer magazin. There are several show stoppers buried in the database layout, such as ids, primary and foreign keys, unique values, generators and triggers. > > Also does anyone know anyway of checking to see if the server is online > before connection to it in VB6 (via ODBC Driver) You will have no luck accessing the firebird services API via ODBC, as ODBC is data only. One option would be to work with something like try-catch(-finally). I don't know if that feature is available in VB6 Feel free to take a look at: - http://pythonhosted.org/fdb/usage-guide.html#working-with-services Even if not programming in python, it declares a lot about how to attach to a service. > > thanks > > Andy hth, Marcus ++ 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 (Yahoo! ID required) http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: AW: AW: AW: [firebird-support] difficult question
Olaf, Stil for this problem, there is a possible solution. If you have only one user at each time, and if the user have the possibility of deleting only the last that have been inserted, you can use a delete trigger, that will renew the value of the generator (the next value that will be generated) in a manner that the next new id to be generated will be the one that has been deleted. Inside the trigger, you can compare the Id of the record to be deleted with the generator value to be sure that the last generated will be deleted and, if really so, update the value of the generator. This will need some learning and tests of you, but if so is the usage environment, there is a big chance you will get this resolved. Good luck, Roberto Camargo. On Monday, December 9, 2013 3:56 PM, Mark Rotteveel wrote: On 9-12-2013 19:29, checkmail wrote: > I have change it, now the IDs were created by trigger. Only one > disadvantage arises, if the user deletes the last recordset and creates > a new, the last skipped now. Why is that a disadvantage? Holes in your numbering would also happen if a user deleted a different recordset (eg the second last) instead of the last. Mark -- Mark Rotteveel ++ 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 (Yahoo! ID required) http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: AW: AW: [firebird-support] difficult question
Dear mrs.Ann, Thanks for you remembering, excuse me for losing the focus. Mr.Olaf is using almost no code in the application, he is working "data-bound". If he was using triggers, he could do it more easily yet, with generators. And he has almost no control about the lock, because he is using MsAccess to do that. I sugest him learn how to increase the quality of his application, where all this points will be better resolved. Thanks a lot, best regards, Roberto Camargo. On Friday, December 6, 2013 1:49 PM, Ann Harrison wrote: Since this discussion has wandered off into German, I'm going to put this as bluntly as possible in English. Never use "select max (id) + 1 from targetTable" to create unique identifiers. It's the worst kind of bug - one that works when you test your code in single user mode, and fails unpredictably in production. It's also very slow. This warning applies to Firebird and all MVCC databases, and to databases that use lock-based concurrency control if you use either Read Committed or Repeatable Read isolation modes. Generators (aka sequences) were created for a reason. Use them. Luck doesn't enter into it. Cheers, Ann
Re: AW: AW: [firebird-support] difficult question
Olaf, I glaube ich have eine Ahnhung von was passiert. Wenn man Access brauch, wenn du bewegst von eine Linie (record) zu eine andere, Access macht sofort commit. Das heisst, wenn du clickst an eine Linie, die letze wo der Focus früher war wir sofort commited. Dan, man verliert der Kontrol der Sequenz wie die Id´s generiert werden. Dan, wen der haupt Form wieder geladen wird, der Data in der Subform werden geladet als sie commited wurden. Mach ein paar Versuchen mit Insert, wo du in der Subform clicks geben wird, und versuch zu alles schliessen (als du normalerweise machst). Und dann ladet die letzten eingefügten Daten, und sag mir wie die Daten in der Subform geordnet sind, ob als du wünscht oder als du früher clicks gegeben hast. Viel erfolgt, Roberto. (entschuldigung dass Ich ein paar Fehler auf Deutsch mache...) On Friday, December 6, 2013 8:44 AM, checkmail wrote: ..sorry, Roberto of course One thing again, firebird realized in every case the ID-Generation, main-table at the time select max(nr) – will I change – and in detail-table with a generator. Hello Robert, yes, I use Microsoft Access and I have test it with different versions of it. The tables are linked over odbc (Firebird ODBC 32 Bit, only select dialect 3, quoted identifiers and safe thread) and some commands (not used in this case /forms) realized with ado-db. I have separately forms, a main and a subform. In both there is a standard recordsource to the linked tables. I don’t start the transactions manually, odbc is my friend. Thanks. In German Language: Hallo Robert, ich verwende ebenfalls Microsoft Access und habe von 2002 bis 2013 alle Versionen getestet, in allen Versionen konnte ich das Problem beobachten. Leider lässt es sich nicht dingfest machen, nur so viel weiß ich, dass es vor allem dann auftritt, wenn im vorherigen Datensatz der selbe Kunde ausgewählt wurde. Dann passiert es, dass die soeben eingetragenen Detaildatensätze auf einmal Zuwachs bekommen von eben dem vorherigen Vorgang. Schließt man das Formular und öffnet es erneut, findet man im letzten Datensatz keine Detaildatensätze im Unterformular mehr vor, statt dessen sind alle im vorherigen Vorgang abgelegt, die original eingetragenen und auch die vom letzten Vorgang. Das nervt natürlich die Angestellten, auch wenn es nur einige Male in der Woche vorkommt. Formular und Unterformular sind ganz normal angebunden, einfache Datenquelle, Tabellenabfrage. Die Tabellen sind alle verknüpft – zu Firebird. Auch dort versuchte ich schon diverse Versionen, das brachte jedoch auch nichts. Selbst den Treiber tauschte ich schon, keine Veränderung. Die genaue Vorgehensweise: Im Hauptformular wird ein Kunde ausgewählt, einige andere Parameter ebenfalls. Zu dem Zeitpunkt steht die Vergabe der fortlaufenden Nummer noch aus. Sobald man z. B. in das Unterformular reinklickt, wird die Nummer übertragen und im Formular angezeigt. Derzeitig eben noch in Firebird über einen Trigger vergeben, select max(nr).. Das kann ich noch mit einem Generator realisieren, kein Problem. Im Unterformular wählt man dann einige Artikel aus, Mengen usw. Und nun öffnet der Bearbeiter meist den Bericht um ihn auszudrucken. Sobald die Daten refresht werden, sieht man das Theater dass eben Positionen im UFO vorhanden sind die dort gar nicht ausgewählt waren. Löscht man diese jetzt beispielsweise heraus, verschwinden sie auch aus dem vorherigen Vorgang. Wie schon erwähnt, auch wenn nach dem Datensatz von Kunde A ein anderer eingefügt wurde, dennoch wird ein Vorgang in den neuen verschmolzen in welchem der Kunde übereinstimmt. Danke für deine Hilfe, viele Grüße nach Brasilien. Von:firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von Tupy... nambá Gesendet: Freitag, 6. Dezember 2013 12:05 An: firebird-support@yahoogroups.com Betreff: Re: AW: [firebird-support] difficult question Olaf, Du sagst, I habe ein Form und ein SubForm !!! May I ask you wich development enviroment are you using for the front-end application ? I see this kind of application building as having been built with MsAccess !!! Because I work with VB, C# and Delphi, and I found this only with the Office family ! If you have separated forms (sub forms included) for both - parent and child table -, I see that you probably don´t have a only one transaction for both sql commands do you use explicit transaction in your application ? After you comment so: >>2. Have you the same transaction for master detail >Both tables are linked to the firebird database over the firebird odbc driver. >After I insert the master >record and klick into the detail-form, I get the generated Nr back from >firebird (before I insert a detail >data set). I’m sorry, I don’t know how the driver works in detail. ... you don´t talk explicitly something about this. Making this sequence of operations doesn´t mean you have a c
Re: AW: [firebird-support] difficult question
Olaf, Du sagst, I habe ein Form und ein SubForm !!! May I ask you wich development enviroment are you using for the front-end application ? I see this kind of application building as having been built with MsAccess !!! Because I work with VB, C# and Delphi, and I found this only with the Office family ! If you have separated forms (sub forms included) for both - parent and child table -, I see that you probably don´t have a only one transaction for both sql commands do you use explicit transaction in your application ? After you comment so: >>2. Have you the same transaction for master detail >Both tables are linked to the firebird database over the firebird odbc driver. >After I insert the master >record and klick into the detail-form, I get the generated Nr back from >firebird (before I insert a detail >data set). I’m sorry, I don’t know how the driver works in detail. ... you don´t talk explicitly something about this. Making this sequence of operations doesn´t mean you have a common transaction. If you don´t explicitly start a transaction, you will not have a transaction for both commands. Roberto Camargo, aus Brasilien On Friday, December 6, 2013 7:45 AM, checkmail wrote: Hello @ll, Today,I still could notreproducetheerroragain. What I can say, that I’m the only one in my test environment who creates records and the error also occurred there. I can try to create a generator instead of the max-statement and I will see. Of course, severalpeople work at the same time (customer) in the database, but locally I’m the only one and the problem already exists. I don’t know what exactly the error provoked. Currently I have create 100 records and every works. I’m confused. Thanks a lot. Best regards Olaf Von:firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von Svein Erling Tysvær Gesendet:Donnerstag, 5. Dezember 2013 11:42 An: firebird-support@yahoogroups.com Betreff: RE: [firebird-support] difficult question >>>I have a table with orders and a second table with orderpositions. The >>>composite primary key from orders >>>consists of jahr and nr, the orderpositions references with a foreign key to >>>this table and its primary >>>key is a continual ID. > >>>In my Frontend there is a form with the orders and a sub-form with the >>>positions – the data-side of both >>>forms is connected over jahr and nr. Now I can create a order, in main-form >>>I select a supplier and >>>select some articles in my sub-form. Ideally – it works fine. But in some >>>case (notably if in the order >>>before it is the same supplier) I select one or more parts to order and If >>>the form will refresh, the >>>order-positions from the order before are in the actually order! If I close >>>the form and reopen it again, >>>the last orderpositions and in the order before there are the from the last >>>order. The same situation I >>>have with a different odbc-driver and some other versions of Access. I’m >>>confused and the problem exists >>>for some years! One from 50 orders goes wrong in this way, the same problem >>>exists in some other areas > >>1. How you generate pk keys >Master: In this case I generate it with select max(nr)+1 from xxx where >new.jahr… I know, not the best way. >Detail: NEW.ID = GEN_ID(TBESTPOS_ID_GEN, 1); (generator) > >>2. Have you the same transaction for master detail >Both tables are linked to the firebird database over the firebird odbc driver. >After I insert the master >record and klick into the detail-form, I get the generated Nr back from >firebird (before I insert a detail >data set). I’m sorry, I don’t know how the driver works in detail. Transactions are vital in Firebird, I don't use ODBC with Firebird and don't know how to control transactions in your environment. What I think can be a possible cause, is if the master and detail query are in separate transactions. Then I can imagine one user inserting a new master with, say, ID 51, and a few detail records (say ID 101, 102, 103). Then another user (before the transaction of the other master is committed) tries to insert another master, but since the other master isn't committed yet, this ID also is 51. This user also insert detail records (say ID 104, 105). Upon committing, the first user will succeed with his inserts, whereas the other user will get a lock conflict for the transaction for the master and succeed for the transaction with the detail records. Normally, this second user should get an error message, but it is of course possible to suppress that in your program. Hence you may end up with one master with ID 51 and detail 101, 102, 103, 104 and 105, whereas the other master isn't inserted at all. If this is the reason, the simple way to fix things would be to start using one or more generators for the master (you may have a separate generator for each jahr, or reset the generator each jahr if jahr is
Re: AW: [firebird-support] difficult question
Olaf, If you deliver us: -the create table of the involved tables (including all created keys, generators, a.s.o.); -some real data of the involved tables (specially for the cases when the problem happens); you will help people to help you. May be also the procedures involved in the data injection (hanging on the insert sequence, some keys - according to the structure you have) may cause to generate auto-generated keys that will bring you the data back in a not wished manner. For all of this, having some code and data may help to understand what can be causing this problems. Cordially, Roberto Camargo. On Thursday, December 5, 2013 6:26 AM, Iwan Cahyadi Sugeng wrote: Hi, Are you having this problem when there are more than one user in transaction at the same time i'm guessing the problem is the child table have different parent key. Check in the child table wether the parent key is correct. The problem maybe because the child get a different parent key because you might get the parent key using get last id of the parent Can you tell me how you get the parent key
Re: [firebird-support] Is it possible to have a SELECT with a variable number of columns?
So, give us news if you got resolved. Roberto. On Monday, November 18, 2013 10:18 AM, W O wrote: Thank you for the advice Roberto, I'll try Greetings. Walter. On Mon, Nov 18, 2013 at 6:29 AM, Tupy... nambá wrote: > >Walter, > > >This possible using a cross-table. You can build it putting subqueries >side-side (in the result). > > >SELECT > a.Date, a.Invoice, a.Amount_Sale, > (SELECT FIRST 1 b.Amount_Tax FROM Table2 b WHERE b.IdTable1 = a.IdTable2) AS >Tax1, > (SELECT FIRST 2 SKIP1 b.Amount_Tax FROM Table2 b WHERE b.IdTable1 = >a.IdTable2) AS Tax2, > (SELECT FIRST 3 SKIP1 b.Amount_Tax FROM Table2 b WHERE b.IdTable1 = >a.IdTable2) AS Tax3, >/ > > > other "columns" subqueries > > >/ >FROM Table1 a > > >This will bring you something like a spreadsheet result, but you have to >stablish a limit of columns of taxes they may have. With a previous query, you >can ask for the maximum amount of taxes may exist in Table2 for one Table1 >row, and dinamically mount the above query. This can be a front-end SQL >generated code in Delphi, VB, a.s.o..(easy way) or in SQL itself, wich I think >can be a little harder to build, but possible. Try it by yourself. > > >Good luck, best regards, >Roberto Camargo, >Rio de Janeiro/Brazil. > > > >On Saturday, November 16, 2013 11:59 PM, W O > wrote: > > > > >Hello everybody > > >I have an application where the tables are all normalized and so the taxes are >not in the same table as the sales. > > >Sometimes a sale should to pay 1 tax, sometimes 2 taxes, sometimes 3 taxes, >etc. > > >And it would be nice to have in just 1 row data of the sale and of the taxes, >each tax in its own column: > > >DATE, INVOICE, AMOUNT, TAX1, TAX2, TAX3, ... TAXN > > >Table1 >-- >ID_TABLE1 >DATE >INVOICE >AMOUNT_SALE > > >Table 2 >-- >ID_TABLE2 >ID_TABLE1 >ID_TAX >AMOUNT_TAX > > >Greetings. > > > >Walter. > > > > > >
Re: [firebird-support] Is it possible to have a SELECT with a variable number of columns?
Walter, This possible using a cross-table. You can build it putting subqueries side-side (in the result). SELECT a.Date, a.Invoice, a.Amount_Sale, (SELECT FIRST 1 b.Amount_Tax FROM Table2 b WHERE b.IdTable1 = a.IdTable2) AS Tax1, (SELECT FIRST 2 SKIP1 b.Amount_Tax FROM Table2 b WHERE b.IdTable1 = a.IdTable2) AS Tax2, (SELECT FIRST 3 SKIP1 b.Amount_Tax FROM Table2 b WHERE b.IdTable1 = a.IdTable2) AS Tax3, / other "columns" subqueries / FROM Table1 a This will bring you something like a spreadsheet result, but you have to stablish a limit of columns of taxes they may have. With a previous query, you can ask for the maximum amount of taxes may exist in Table2 for one Table1 row, and dinamically mount the above query. This can be a front-end SQL generated code in Delphi, VB, a.s.o..(easy way) or in SQL itself, wich I think can be a little harder to build, but possible. Try it by yourself. Good luck, best regards, Roberto Camargo, Rio de Janeiro/Brazil. On Saturday, November 16, 2013 11:59 PM, W O wrote: Hello everybody I have an application where the tables are all normalized and so the taxes are not in the same table as the sales. Sometimes a sale should to pay 1 tax, sometimes 2 taxes, sometimes 3 taxes, etc. And it would be nice to have in just 1 row data of the sale and of the taxes, each tax in its own column: DATE, INVOICE, AMOUNT, TAX1, TAX2, TAX3, ... TAXN Table1 -- ID_TABLE1 DATE INVOICE AMOUNT_SALE Table 2 -- ID_TABLE2 ID_TABLE1 ID_TAX AMOUNT_TAX Greetings. Walter.
Re: [firebird-support] can stored proc./ trigger write to another DB ?
Hi, Peter, Read http://www.firebirdfaq.org/faq16/. May be this can help you in any way to resolve (or not) your problem. Good luck, Roberto Camargo. From: "peter_...@yahoo.com" To: firebird-support@yahoogroups.com Sent: Tuesday, October 1, 2013 5:42 AM Subject: [firebird-support] can stored proc./ trigger write to another DB ? Hi, I have 2 DB's , db1.FDB and db2.GDB (db2 is older). Server is Firebird 2.1. Both databases are local, on the same PC where the server is. Is it possible a trigger (or a stored procedure) in db1 to write data directly to db2 ? Thanks
Re: [firebird-support] GBak Backup & Restore Problem
Benson, Thanks for insist for to have a solution for this. We believe we found a pratical solution = for the first, we restored the DB structure only and after then, DB data only. This worked in a test machine, we believe this will work at a production machine. Today, we will see how we can run this as an script, since we know we can have other pratical problems. If this can be resolved in this manner, if this problem happen simultaneously at many columns and many tables, this way we will have the problem resolved at all his many occurrences. Thanks a lot for your follow up. Roberto Camargo. From: Alexandre Benson Smith To: firebird-support@yahoogroups.com Sent: Tuesday, June 25, 2013 9:40 PM Subject: Re: [firebird-support] GBak Backup & Restore Problem Roberto, Em 25/6/2013 17:07, Tupy... nambá escreveu: > Mr.Benson, > > No, the constraint wasn´t change. As I explain to mr.Jesus Garcia, we take > the DB having the data and the constraint in the needed conditions, we made a > backup and then the restore. This mean = all the conditions were assured that > the DB were in "good conditions" and, during the backup/restore process, the > data at this column was "lost". > > > Thank you, i.c.u.2 ! > Roberto Camargo > Did you tried my suggestion ? I will insist on that... Update the problematic column/table update MyTable set MyColumn = 0 where Mycolumn is null or MyColumn = 0; commit; then try to back-up/restore. see you ! ++ 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]
Re: [firebird-support] GBak Backup & Restore Problem
Sorry, Softtech, I didn´t understand your message, since I still want to understand what happened. From: Softtech Support To: firebird-support@yahoogroups.com Sent: Tuesday, June 25, 2013 5:13 PM Subject: Re: [firebird-support] GBak Backup & Restore Problem Thanks, Looks like it is working now. Appreciate your guidance - Original Message - From: Tupy... nambá To: firebird-support@yahoogroups.com Sent: Tuesday, June 25, 2013 2:23 PM Subject: [firebird-support] GBak Backup & Restore Problem Dear Friends, We have a strange problem with GBak. We make a backup with gbak and then a restore, also with the same. We have a table with 60 rows where a not null type SmallInt column has 0 (zero) as content. When restoring, GBak returns null for this column. As this column is not null, we get an error message (Error: Validation error for column .). Between these two steps (backup and restore), no further action is done with the backup file. I ask you = What can cause this problem? Is there any solution for this? Any idea about this problem? Thanks a lot, Roberto Camargo, Rio de Janeiro / Brasil [Non-text portions of this message have been removed] [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 [Non-text portions of this message have been removed]
Re: Re[2]: [firebird-support] GBak Backup & Restore Problem
Dmitry, Yes, the problem seems incredible. If I heard it from someone else, I would also have difficult to believe on that. But this is as it is ! I will not repeat (ooops, I am already in the way !) that the source DB (for the backup) is ok. And the backup, as only could be, really has the problem. And the backup was generated not by a rocket, but only by the GBak ! This mean = or the problem happens at the backup process, or during the restore process. But as we have no tool to see the backup content, it´s not possible to know where, in these two steps, the problem happens. We experimented to restore first only the DB structure and then restore the data, and then we got no error. Using the same backup file. We have FB 2.1. Roberto Camargo From: Dmitry Kuzmenko To: firebird-support@yahoogroups.com Sent: Tuesday, June 25, 2013 5:13 PM Subject: Re[2]: [firebird-support] GBak Backup & Restore Problem Hello, Tupy...! Wednesday, June 26, 2013, 12:07:07 AM, you wrote: Tn> backup and then the restore. This mean = all the conditions were Tn> assured that the DB were in "good conditions" and, during the backup/restore process, the Tn> data at this column was "lost". The funny thing is that gbak is not a rocket sience, it simply does select * from table and writes the data to the backup file. So, if you have problem, the source is in the database or in the backup. I also do not see what Firebird version do you use. p.s. please, do not overquote. -- Dmitry Kuzmenko, www.ib-aid.com ++ 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]
Re: [firebird-support] GBak Backup & Restore Problem
Mr.Benson, No, the constraint wasn´t change. As I explain to mr.Jesus Garcia, we take the DB having the data and the constraint in the needed conditions, we made a backup and then the restore. This mean = all the conditions were assured that the DB were in "good conditions" and, during the backup/restore process, the data at this column was "lost". Thank you, i.c.u.2 ! Roberto Camargo From: Alexandre Benson Smith To: firebird-support@yahoogroups.com Sent: Tuesday, June 25, 2013 4:33 PM Subject: Re: [firebird-support] GBak Backup & Restore Problem Em 25/6/2013 16:23, Tupy... nambá escreveu: > > Dear Friends, > > We have a strange problem with GBak. We make a backup with gbak and then a > restore, also with the same. > > We have a table with 60 rows where a not null type SmallInt column has 0 > (zero) as content. > > When restoring, GBak returns null for this column. As this column is not > null, we get an error message (Error: Validation error for column .). > > Between these two steps (backup and restore), no further action is done with > the backup file. > > I ask you = What can cause this problem? Is there any solution for this? Any > idea about this problem? > > Thanks a lot, > Roberto Camargo, > Rio de Janeiro / Brasil > > Did the null/not null constraint was applied after the table has some rows ? IIRC there is something regarding NULL stored and the column be changed to NOT NULL with a DEFAULT, and so FB will return the DEFAULT value to NOT NULL columns that in fact is NULL. I suggest you to do: update MyTable set MyColumn = 0 where MyColumn is null or MyColumn = 0; commit; and then perform a new back-up/restore cycle. see you ! ++ 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]
Re: [firebird-support] GBak Backup & Restore Problem
Hi, Dmitry, Thanks for you response. No one has changed this table. At most, as a not null column, someone could have change it to another value, but not to null. And no one changed it´s validation rule (as not null). This is discarded. We repeated the action = we take the DB, checked the content of this colum (all lines having 0 at this column), then we run a backup and imediatelly a restore, and we reproduced the problem (it ocurred when restoring). Roberto Camargo. From: Dmitry Kuzmenko To: firebird-support@yahoogroups.com Sent: Tuesday, June 25, 2013 4:33 PM Subject: Re: [firebird-support] GBak Backup & Restore Problem Hello, Tupy...! Tuesday, June 25, 2013, 11:23:47 PM, you wrote: Tn> Dear Friends, Tn> We have a table with 60 rows where a not null type SmallInt column has 0 (zero) as content. Tn> When restoring, GBak returns null for this column. As this column Tn> is not null, we get an error message (Error: Validation error for column .). Tn> Between these two steps (backup and restore), no further action is done with the backup file. Tn> I ask you = What can cause this problem? Is there any solution Tn> for this? Any idea about this problem? 1. somebody altered column to not null default 0 while column had null data 2. somebody added column not null default 0. solution - update that column to 0 like this update table set field = 0 p.s. gbak backups data, it does not check correspondence of data and constraints. If it is broken (somehow, by db corruption or by actions I mentioned), restore will show that column data does not correspond to it's check. -- Dmitry Kuzmenko, www.ib-aid.com ++ 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]
Re: [firebird-support] GBak Backup & Restore Problem
Señor Garcia, Gracias por su retorno. If I understood well, you say to make a first insertion at this table, manually apply values to the columns of this table, save the values to the table, delete this line and then restore the table. It´s a possible solution, but not a pratical. Specially if this happens to many other tables and columns. The most important is first understand why (to eventually avoid the causes) and the technical solution for the question, which can be automatically applied (by code, by scripts or something like this). Your suggestion will be taken in consideration, since we go more deeply into the problem. Best regards, Roberto Camargo. From: Jesus Garcia To: "firebird-support@yahoogroups.com" Sent: Tuesday, June 25, 2013 4:28 PM Subject: Re: [firebird-support] GBak Backup & Restore Problem Hello, I think you have a row with null value. The option is update table set column = 0 where column is null and then try to backup restore. I have seen this issue when changing columns from null to not null and you have rows in the table. 2013/6/25 Tupy... nambá > ** > > > > > Dear Friends, > > We have a strange problem with GBak. We make a backup with gbak and then a > restore, also with the same. > > We have a table with 60 rows where a not null type SmallInt column has 0 > (zero) as content. > > When restoring, GBak returns null for this column. As this column is not > null, we get an error message (Error: Validation error for column .). > > Between these two steps (backup and restore), no further action is done > with the backup file. > > I ask you = What can cause this problem? Is there any solution for this? > Any idea about this problem? > > Thanks a lot, > Roberto Camargo, > Rio de Janeiro / Brasil > > [Non-text portions of this message have been removed] > > > [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 [Non-text portions of this message have been removed]
[firebird-support] GBak Backup & Restore Problem
Dear Friends, We have a strange problem with GBak. We make a backup with gbak and then a restore, also with the same. We have a table with 60 rows where a not null type SmallInt column has 0 (zero) as content. When restoring, GBak returns null for this column. As this column is not null, we get an error message (Error: Validation error for column .). Between these two steps (backup and restore), no further action is done with the backup file. I ask you = What can cause this problem? Is there any solution for this? Any idea about this problem? Thanks a lot, Roberto Camargo, Rio de Janeiro / Brasil [Non-text portions of this message have been removed]
Re: [firebird-support] about system table RDB$TRIGGERS
Dear Mark, Thanks a lot for your quick answer. I suspected this, specially because the column rdb$system_flag is different of 0 (zero). But as I don't know deeply the bd structure (the system tables), I asked this for to know if this case could mean useless triggers, and then this could mean trash for the db. I understood your information very clearly and I thank you a lot for that. My best wishes for you, Roberto Camargo. From: Mark Rotteveel To: firebird-support@yahoogroups.com Sent: Saturday, June 15, 2013 10:27 AM Subject: Re: [firebird-support] about system table RDB$TRIGGERS On 15-6-2013 15:20, Tupy... nambá wrote: > Dear friends, > > Opening the table RDB$TRIGGERS, I saw many rows where the column > RDB$TRIGGER_SOURCE is empty (null or an empty string). And the related tables > doesn´t present these triggers. > > I ask you: > -Are these rows at RDB$TRIGGERS useless ? > -May I delete these rows ? > -If I delete these rows, may I cause some any kind of trouble to my database > ? Will this delection be helpful for the DB ? This way, will this delection > affect the DB operation in any way, positive or negatively ? Never, ever directly manipulate the system tables. Most of those triggers are system triggers which are important for the correct working of the Firebird database. A new Firebird database has 35 system triggers which have no source (because they weren't declared using SQL). The RDB$TRIGGER_SOURCE is just informational, the actual (compiled) trigger is stored in RDB$TRIGGER_BLR. So no, don't delete them, you will ruin your database. Mark -- Mark Rotteveel ++ 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]
[firebird-support] about system table RDB$TRIGGERS
Dear friends, Opening the table RDB$TRIGGERS, I saw many rows where the column RDB$TRIGGER_SOURCE is empty (null or an empty string). And the related tables doesn´t present these triggers. I ask you: -Are these rows at RDB$TRIGGERS useless ? -May I delete these rows ? -If I delete these rows, may I cause some any kind of trouble to my database ? Will this delection be helpful for the DB ? This way, will this delection affect the DB operation in any way, positive or negatively ? Thanks a lot, Roberto Camargo [Non-text portions of this message have been removed]
Fw: [firebird-support] Problems with a procedure inside a view
Unfortunatelly, this group removes also pictures. - Forwarded Message - From: Tupy... nambá To: "firebird-support@yahoogroups.com" Sent: Wednesday, December 12, 2012 10:40 AM Subject: [firebird-support] Problems with a procedure inside a view Hi ! I work in a company using Firebird 2.1. I built a procedure having an single output parameter. If I use it inside a single Select command or inside a View, it works fine. The question is: if I want to see the structure of a view that calls that procedure, I can´t see it. Instead, I receive an error message that says "Precompiler Error: 'B341VALIDARCB'." (in a generic manner, "Precompiler Error: '[procedure name]'.". I'd like to see the structure of the vision that I need. If I get this message, I think something must be wrong with either the procedure or the view. I send the image of the structure attached. The basic procedure code is bellow.: CREATE PROCEDURE B341VALIDARCB( COD_BANCO VARCHAR(3) CHARACTER SET WIN1252 DEFAULT NULL, COD_AGENCIA VARCHAR(4) CHARACTER SET WIN1252 DEFAULT NULL, CARTEIRA VARCHAR(3) CHARACTER SET WIN1252 DEFAULT NULL, CONTA VARCHAR(6) CHARACTER SET WIN1252 DEFAULT NULL, VALOR DECIMAL(18, 2) DEFAULT 0, NOSSO_DOCUMENTO VARCHAR(8) CHARACTER SET WIN1252 DEFAULT NULL, CODIGODEBARRAS VARCHAR(50) CHARACTER SET WIN1252 DEFAULT NULL) RETURNS( AVALIACAO VARCHAR(50) CHARACTER SET WIN1252) AS BEGIN AVALIACAO = ''; IF (some condition) THEN AVALIACAO = AVALIACAO || 'Cód.do banco errado - '; IF (another condition) THEN AVALIACAO = AVALIACAO || 'Cód.da agência errado - '; /* other conditions */ IF (AVALIACAO = '') THEN AVALIACAO = 'Código de barras ok'; SUSPEND; END; No special condition, and the output field is handled only to receive the value according to the logical results inside the procedure. And if you look at the picture I send, you will see that the procedure is called by a sub Select inside the view. Does someone can give me some light about the possible problem in the code or in the solution itself ? Or is this a bug ? Thanks a lot, best regards, Roberto Camargo. [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 [Non-text portions of this message have been removed]
[firebird-support] Problems with a procedure inside a view
Hi ! I work in a company using Firebird 2.1. I built a procedure having an single output parameter. If I use it inside a single Select command or inside a View, it works fine. The question is: if I want to see the structure of a view that calls that procedure, I can´t see it. Instead, I receive an error message that says "Precompiler Error: 'B341VALIDARCB'." (in a generic manner, "Precompiler Error: '[procedure name]'.". I'd like to see the structure of the vision that I need. If I get this message, I think something must be wrong with either the procedure or the view. I send the image of the structure attached. The basic procedure code is bellow.: CREATE PROCEDURE B341VALIDARCB( COD_BANCO VARCHAR(3) CHARACTER SET WIN1252 DEFAULT NULL, COD_AGENCIA VARCHAR(4) CHARACTER SET WIN1252 DEFAULT NULL, CARTEIRA VARCHAR(3) CHARACTER SET WIN1252 DEFAULT NULL, CONTA VARCHAR(6) CHARACTER SET WIN1252 DEFAULT NULL, VALOR DECIMAL(18, 2) DEFAULT 0, NOSSO_DOCUMENTO VARCHAR(8) CHARACTER SET WIN1252 DEFAULT NULL, CODIGODEBARRAS VARCHAR(50) CHARACTER SET WIN1252 DEFAULT NULL) RETURNS( AVALIACAO VARCHAR(50) CHARACTER SET WIN1252) AS BEGIN AVALIACAO = ''; IF (some condition) THEN AVALIACAO = AVALIACAO || 'Cód.do banco errado - '; IF (another condition) THEN AVALIACAO = AVALIACAO || 'Cód.da agência errado - '; /* other conditions */ IF (AVALIACAO = '') THEN AVALIACAO = 'Código de barras ok'; SUSPEND; END; No special condition, and the output field is handled only to receive the value according to the logical results inside the procedure. And if you look at the picture I send, you will see that the procedure is called by a sub Select inside the view. Does someone can give me some light about the possible problem in the code or in the solution itself ? Or is this a bug ? Thanks a lot, best regards, Roberto Camargo. [Non-text portions of this message have been removed]
[firebird-support] Restore not recreating some foreign keys
Dear sirs, These days, when restoring a backup of us, I noted that some foreign keys have not been recreated at a new copy of a DB. I don´t know exactly when this happens = if the backup itself is generated without these faulting FK´s or if the fail happens at the DB recreation process. I ask for your gentle help to resolve this problem. Any indication can be of value and will be welcome. In advance, my thanks. Best regards, Roberto Camargo Rio de Janeiro/Brazil [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Not visible columns
A good suggestion, it´s possible this will correct the problem. I will try later. Thanks. From: Mark Rotteveel To: firebird-support@yahoogroups.com Sent: Wednesday, August 15, 2012 11:11 AM Subject: Re: [firebird-support] Re: Not visible columns On Wed, 15 Aug 2012 07:01:50 -0700 (PDT), Tupy... nambá wrote: > Lester, > > I was sure that nothing was wrong with my DDL command, because at the same > DB I created a RH_Cargos2 with the rest identical and the table was created > without problems. > > You used my script (DDL command) and you could manage the table with > Flamerobin, but I also could use it with EMS, as told above. > > I don´t believe that the problem is of EMS because, as I also told, for > the same table, the problem also happens with ErWin. I believe there is > something miss with my DB. Or, under unknown conditions, sometimes this > happens, sometimes it does´nt happen. > > Personally, I believe something happened to the system tables of my DB. > And I have the impression that Firebird have some informations replicated > at system tables, and at design time (running DDL´s), they are got off one > place, and at SQL commands, this information are got from other place ou > system tables. > > But, I see no one has idea about what is happening. Thanks to all, I will > survive ! Does the problem continue if you backup the database and restore it to a different database file? Mark ++ 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]
Re: [firebird-support] Re: Not visible columns
Lester, I was sure that nothing was wrong with my DDL command, because at the same DB I created a RH_Cargos2 with the rest identical and the table was created without problems. You used my script (DDL command) and you could manage the table with Flamerobin, but I also could use it with EMS, as told above. I don´t believe that the problem is of EMS because, as I also told, for the same table, the problem also happens with ErWin. I believe there is something miss with my DB. Or, under unknown conditions, sometimes this happens, sometimes it does´nt happen. Personally, I believe something happened to the system tables of my DB. And I have the impression that Firebird have some informations replicated at system tables, and at design time (running DDL´s), they are got off one place, and at SQL commands, this information are got from other place ou system tables. But, I see no one has idea about what is happening. Thanks to all, I will survive ! Best regards, Roberto Camargo. From: Lester Caine To: firebird-support@yahoogroups.com Sent: Wednesday, August 15, 2012 10:22 AM Subject: Re: [firebird-support] Re: Not visible columns Tupy... nambá wrote: > I found the same problem running a reverse engineering at ErWin 4.1.4 with > ODBC. ODBC like Jaybird which I mentioned privately does not have all of the latest stuff available as both tend to make things look as similar as possible across different database engines. As I indicated, only EMS can advise why they can't display your table, there is nothing wrong with the table as shown in Flamerobin, CREATE TABLE RH_CARGOS ( ID_CARGO Integer NOT NULL, DESCRICAO Varchar(70) CHARACTER SET WIN1252 DEFAULT '' NOT NULL, STATUS Integer NOT NULL, CARGO_SEM_DETALHE Integer NOT NULL, TIPO_CARGO Integer NOT NULL, ACESSO_APPS Integer NOT NULL, CONSTRAINT PK_RH_CARGOS_1 PRIMARY KEY (ID_CARGO) ); ALTER TABLE RH_CARGOS ADD CHECK (CARGO_SEM_DETALHE in (0,1)); ALTER TABLE RH_CARGOS ADD CHECK (TIPO_CARGO in (0,1,2,3)); -- 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/ ++ 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]
Re: [firebird-support] Re: Not visible columns
Martijn, I found the same problem running a reverse engineering at ErWin 4.1.4 with ODBC. Best regards, Roberto Camargo. From: Martijn Tonies To: firebird-support@yahoogroups.com Sent: Wednesday, August 15, 2012 9:40 AM Subject: Re: [firebird-support] Re: Not visible columns >Yes, after this has been found for the first time, the server and the >machine running EMS have been restarted. Have you tried other tools to see if this is an issue with the EMS tool? With regards, Martijn Tonies Upscene Productions http://www.upscene.com/ Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! ++ 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]
Re: [firebird-support] Not visible columns
Sir Tysvaer, To the users, this bring no problems, because the user receives all the data they need - only the structure, at design time, is not visible. I´m resending the attached document, wich will not blocked to your personal inbox. The problem is at development, when we need to see exactly wich columns we can use at some situations. I don´t see any DDL I can use against this table, about this question. Our Firebird is 2.1 version. Yes, we use UDF´s. The Firebird server runs Windows 2003 Server. We use Nod 32, but all files of the Firebird service are not scanned (they are as exclusion of the scan). As you will receive the document, I think this will explain clearly enough. thx, Roberto Camargo. From: Svein Erling Tysvær To: "'firebird-support@yahoogroups.com'" Sent: Wednesday, August 15, 2012 9:33 AM Subject: RE: [firebird-support] Not visible columns >I already had this problem and this happened to me once again. > >I have a table whose columns are showed as not existing, at designers (as at >EMS SQL Studio or ErWin) >and also at the DDL comands for the table. But if you execute a SELECT command >on it, it list all >existing columns, and their data. What do the users typically do when this happens? Do you do any DDL against tables that are in use? Tell us a lot more about your situation, e.g. exact Firebird version (errors are more likely to happen on Fb 0.9.4 than Fb 2.1.5), whether you use UDFs or not (they're a common source of problems), operating system, is it used for anything besides Firebird, any particular type of antivirus etc. The more specific you are in your problem description, the more likely you are to get good answers. Set ++ 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]
Re: [firebird-support] Re: Not visible columns
Yes, after this has been found for the first time, the server and the machine running EMS have been restarted. From: Mark Rotteveel To: firebird-support@yahoogroups.com Sent: Wednesday, August 15, 2012 9:28 AM Subject: Re: [firebird-support] Re: Not visible columns On Wed, 15 Aug 2012 05:23:09 -0700 (PDT), Tupy... nambá wrote: > Excuse me. > > The only column wich is showed, is the Id_Cargo. The others are not showed > at the DDL returned by the EMS and at designers. Did you try: * Restarting EMS and/or designer * Restarting Firebird It might be that your tools see an older version of the table structure because they use an old transaction. Mark ++ 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]
Re: [firebird-support] Not visible columns
Mark, Excuse me, but I think that docs and pictures can help and make clear many situations. This can be the rule, but I think the rule would be better if not forbidding all attachments. Not for me, but for all. But the owner know what want to have, and this not to discuss, only a friendly suggestion. Thanks, Mark. Roberto Camargo. From: Mark Rotteveel To: firebird-support@yahoogroups.com Sent: Wednesday, August 15, 2012 8:40 AM Subject: Re: [firebird-support] Not visible columns On Wed, 15 Aug 2012 04:36:16 -0700 (PDT), Tupy... nambá wrote: > I don´t know why, but I send a doc with pictures showing this, and it has > been removed from my message. You can't included attachments on the mailinglist. ++ 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]
[firebird-support] Re: Not visible columns
Excuse me. The only column wich is showed, is the Id_Cargo. The others are not showed at the DDL returned by the EMS and at designers. From: Lester Caine To: firebird-support@yahoogroups.com Sent: Wednesday, August 15, 2012 9:20 AM Subject: Re: ***SPAM*** [firebird-support] Not visible columns Tupy... nambá wrote: > Lester, > > Below, the DDL command used to build the table. > CREATE TABLE RH_CARGOS ( > ID_CARGO INTEGER NOT NULL, > DESCRICAO VARCHAR(70) CHARACTER SET WIN1252 DEFAULT '' NOT NULL COLLATE >WIN1252, > STATUS INTEGER NOT NULL, > CARGO_SEM_DETALHE INTEGER NOT NULL, > TIPO_CARGO INTEGER NOT NULL, > ACESSO_APPS INTEGER NOT NULL); > > As you can see, no reserved word, and so, no reason for using any kind of > quotes (so I believe) for the table words (this same DDL command was used in > another DB and and build and brought me the table correctly). So which columns are giving a problem? Flamerobin just handles it as I would expect, so it may be that you need to ask on a support list for the tool that is giving the problem. -- 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/ ++ 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]
Re: ***SPAM*** [firebird-support] Not visible columns
Lester, Below, the DDL command used to build the table. CREATE TABLE RH_CARGOS ( ID_CARGO INTEGER NOT NULL, DESCRICAO VARCHAR(70) CHARACTER SET WIN1252 DEFAULT '' NOT NULL COLLATE WIN1252, STATUS INTEGER NOT NULL, CARGO_SEM_DETALHE INTEGER NOT NULL, TIPO_CARGO INTEGER NOT NULL, ACESSO_APPS INTEGER NOT NULL); As you can see, no reserved word, and so, no reason for using any kind of quotes (so I believe) for the table words (this same DDL command was used in another DB and and build and brought me the table correctly). From: Lester Caine To: firebird-support@yahoogroups.com Sent: Wednesday, August 15, 2012 8:53 AM Subject: Re: ***SPAM*** [firebird-support] Not visible columns Tupy... nambá wrote: > I have a table whose columns are showed as not existing, at designers (as at > EMS SQL Studio or ErWin) and also at the DDL comands for the table. But if > you execute a SELECT command on it, it list all existing columns, and their > data. > > For your delight, I send pictures showing the situation. Without the picture it's a little difficult to comment, but I'd go for there being a problem with the use or no of double quotes around the field name. Either a reserved word which the designers automatically wrap, but then may not be visible if the table's column was not defined with double quotes. Or alternatively something case sensitive which would also need to be wrapped properly. -- 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/ ++ 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]
Re: [firebird-support] Not visible columns
I don´t know why, but I send a doc with pictures showing this, and it has been removed from my message. From: Tupy... nambá To: "firebird-support@yahoogroups.com" Sent: Wednesday, August 15, 2012 8:24 AM Subject: [firebird-support] Not visible columns Dear sirs, I already had this problem and this happened to me once again. I have a table whose columns are showed as not existing, at designers (as at EMS SQL Studio or ErWin) and also at the DDL comands for the table. But if you execute a SELECT command on it, it list all existing columns, and their data. For your delight, I send pictures showing the situation. Then, I ask for: 1.some explanation why this happens (may be, we can avoid this to be repeated later); 2.how to repair this situation, without removing and recreating this table. Other indications about this will be welcome. Thanks in advance, Roberto Camargo, Rio de Janeiro/Brazil [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 [Non-text portions of this message have been removed]
[firebird-support] Not visible columns
Dear sirs, I already had this problem and this happened to me once again. I have a table whose columns are showed as not existing, at designers (as at EMS SQL Studio or ErWin) and also at the DDL comands for the table. But if you execute a SELECT command on it, it list all existing columns, and their data. For your delight, I send pictures showing the situation. Then, I ask for: 1.some explanation why this happens (may be, we can avoid this to be repeated later); 2.how to repair this situation, without removing and recreating this table. Other indications about this will be welcome. Thanks in advance, Roberto Camargo, Rio de Janeiro/Brazil [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Slow query with like '%xxx%' clause
Doesn´t matter if just using Clientes or having a join or a view, a search with ' like "%word%" ' will not bring the results you want, this will ever use table scan and will be relatively slow for your needs. Direct in the database, the only approach were you will can speed up your searches will be using a solution like suggested by mr.Frank Schlottmann-Gödde. Using only sql commands and clauses, no way to get what you want. Ms.Ann Harrison already said "Firebird cannot use an index on partial word matches unless it knows the initial characters, so your queries are all going to require full table scans."(On Thu, Jul 12, 2012 at 12:08 PM). To this way, final words. From: peixinhosdalua To: firebird-support@yahoogroups.com Sent: Friday, July 13, 2012 9:57 AM Subject: [firebird-support] Re: Slow query with like '%xxx%' clause What you suggest is not a solution because just using the tables CLIENTES, without any join or inside a view, the problem remains. I tried it. Also, i do not want to make 'word%' search. What i need is '%word%'. ++ 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]
Re: [firebird-support] Slow query with like '%xxx%' clause
Wenn man die Vorschlage aus Herrn Gödde folgen wird, man kann ein "Googler" bauen. d.h., wenn man "insert" oder "update" macht, dann eine Tabelle mit Suchwörte und Index wird aktualiziert.. stimmt das ? This will need a complete change in the conception - this is really the only way to get speed in this kind of search -, but must be considered how frequently this kind of search will be needed and the words need to be "dismounted" at the insert/update time. And each gotten word will need to be converted in a record in this search table. This can mean that the accessories operations will increase the time to insert/update operations. And what will be gained as result, may mean a general loss. So, this solutions need to be clearly considered, because the positive results may not be great enough to justify its implementation, may be better stay with the current search results. Think about. Roberto Camargo, Rio de Janeiro/BR From: Frank Schlottmann-Gödde To: firebird-support@yahoogroups.com Sent: Thursday, July 12, 2012 12:43 PM Subject: Re: [firebird-support] Slow query with like '%xxx%' clause On 07/12/2012 03:17 PM, peixinhosdalua wrote: > Regarding your suggestions, i need to make a search by '%word%' not 'word%'. > For example, most companies have a commercial name or brand that they use and > these are different from the juridic name. It is the juridic name that is > inserted in the table because this table is using to make invoices. So it is > standard to have companies names like: Something Brand LTD and people search > for Brand and if they do it like 'Brand%' never appears. > > Same can happen for phones if you search with out without prefixes. Also the > VAT-ID. For example VAT's in Europe have letters at beginning but most people > omitted the letters when searching for VAT-ID from their own country. > > This is even more true when searching for products. > > So, how can i improve the usage of the like or containing with the %word%? You may try to create an extra search-table sth. like (searchword,tablename,fieldname,ID) and fill this on insert,update or delete using triggers on your original tables, Create one or more stored procedures that do a for select tablename,id from searchtable where searchword like '%whatever%' into .. (maybe trying a starting with first) and returns the values you want (via execute statement) . This will reduce the table scan to one table. hth fsg -- "Fascinating creatures, phoenixes, they can carry immensely heavy loads, their tears have healing powers and they make highly faithful pets." - J.K. Rowling ++ 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]
Re: [firebird-support] Slow query with like '%xxx%' clause
Caro "peixinhosdalua", There is no way to increase such kind of searches in a database, because the searches are made line per line, searching inside each line by the occurrence of your needed search by displacement of the searching word, char by char. You are right that such kind of search has to be made this way, but, then, there is no way to get better results (in time). They will never be so fast as using wildcards only at the right side of the searched word, because this search is made only in the first part ( = at the first char plus the length ) and uses the index for this search. In your case, still if the word search is made using the index table, this will be so slowly as if made at the data table itself. And if the search is composed by a OR clause, this search is made twice, with double delay, and so on For this kind of search, there is no tip and no trick to increase the speed. Good luck, best regards, Roberto Camargo, Rio de Janeiro/Brasil. From: peixinhosdalua To: firebird-support@yahoogroups.com Sent: Thursday, July 12, 2012 10:17 AM Subject: [firebird-support] Slow query with like '%xxx%' clause Hello, I made a few query to count the time. I tried with the CLIENTES having 3553 records and with 1000 records. The changes in time performance is significant! Note that now i am not testing with the VIEW (so not having any joined tabled). 30 SEG (3553 records) 9 SEG (1000 records) (with the VIEW) select first 20 * from LISTA_CLIENTES where (CLIENTE like '%crist%' or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like '%crist%' or PAIS like '%crist%' or PAIS_NOME like '%crist%' or TELEFONE like '%crist%' or EMAIL like '%crist%' or CONTRIBUINTE like '%crist%' or COND_PAGAMENTO_DESCRICAO like '%crist%') order by CLIENTE 20 SEG (3553 records) 6 SEG (1000 records) (without the VIEW) select first 20 * from CLIENTES where CLIENTE like '%crist%' or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like '%crist%' or PAIS like '%crist%' or TELEFONE like '%crist%' or EMAIL like '%crist%' or CONTRIBUINTE like '%crist%' order by CLIENTE total records in CLIENTES = 3553 25 SEG (3553 records) 8 SEG (1000 records) (without the VIEW) select first 20 * from CLIENTES where CLIENTE like '%crist%' or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like '%crist%' or PAIS like '%crist%' or TELEFONE like '%crist%' or EMAIL like '%crist%' or CONTRIBUINTE like '%crist%' 15 SEG (3553 records) 8 SEG (1000 records) (without the VIEW) select first 20 * from CLIENTES where CLIENTE like '%crist%' or MORADA like '%crist%' or LOCALIDADE like '%crist%' or CLIENTE like '%crist%' or PAIS like '%crist%' or TELEFONE like '%crist%' 4 SEG (3553 records) 2 SEG (1000 records) (without the VIEW) select first 20 * from CLIENTES where CLIENTE like '%crist%' or MORADA like '%crist%' Regarding your suggestions, i need to make a search by '%word%' not 'word%'. For example, most companies have a commercial name or brand that they use and these are different from the juridic name. It is the juridic name that is inserted in the table because this table is using to make invoices. So it is standard to have companies names like: Something Brand LTD and people search for Brand and if they do it like 'Brand%' never appears. Same can happen for phones if you search with out without prefixes. Also the VAT-ID. For example VAT's in Europe have letters at beginning but most people omitted the letters when searching for VAT-ID from their own country. This is even more true when searching for products. So, how can i improve the usage of the like or containing with the %word%? Thanks. ++ 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]
Re: [firebird-support] How to limit the number of concurrent users to a database
Signore Fulvio Senore, If the problem really is to limit the simultaneous connections, if after each query, you close the connection, this will by his self assure that the simultaneously connected users will be the minimum. And will bring other benefits, as minimizing net traffic. As normally well planned queries and transactions in well managed servers don´t last more than a second, doesn´t matter how much applications and machines are connected to the db server, they will not stay connected much longer. And so, not many will be connected at the same time - and probablyat most of the time, you will have only one user really connected to the db server. Ask your front-end applications developers, how they programm your applications. Or your software deliverers... Today is not considered a good practice having a connection open for more than the time needed to send a request and get the result back. And for this reason, many paid sql servers send servers by the number of simultaneos connections. Best regards, Roberto Camargo. --- On Thu, 6/14/12, Michael Ludwig wrote: From: Michael Ludwig Subject: Re: [firebird-support] How to limit the number of concurrent users to a database To: firebird-support@yahoogroups.com Date: Thursday, June 14, 2012, 2:05 AM Fulvio Senore schrieb am 13.06.2012 um 22:17 (+0200): > My purpose is very simple. The users can install the program on any > number of computers connected to a Firebird server, and I want to > limit the number of simultaneous connections. Do you control the server? If you don't it'll be difficult to enforce anything anyway. But if you do you might investigate whether it's possible to configure this at the network level, like with iptables on Linux, or whatnot on Windows. Take a look here, I didn't do further checks so I don't know whether it's possible or not: https://www.google.com/search?q=iptables+limit+number+of+connections Michael ++ 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]
RE: Re: RES: [firebird-support] Copy of the current db not updating generators
Bogdan, Please, say that to the one who does it. That´s not me. Under normal conditions, those who doesn´t know how to make the things in a right manner don´t ask for a technical help. So is and so will long to be this way with the one who does this copy. Till he will have a great problem, this was long ago adviced by me. And, still having clouds, you can´t force the rain, if the wind take them away And in the human nature, there are many winds flowing away Vielen dank für alles Best regards, Roberto Camargo. --- On Thu, 5/31/12, bogdan wrote: From: bogdan Subject: RE: Re: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com Date: Thursday, May 31, 2012, 9:48 PM Sorry One can not just copy database and believe that everything will be allright. There is a standard well known procedure that garantees positive outcome. You can not force rain without cloudes Regards Bogdan Helen, i apologize for top posting (but i like it better, it reduces the time needed to scroll thru posts) From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Tupy... nambá Sent: Thursday, May 31, 2012 3:43 PM To: firebird-support@yahoogroups.com Subject: Fw: Re: RES: [firebird-support] Copy of the current db not updating generators Or, according to An Harrison´s explanation, the generators had not been updated due to not have been commited --- On Thu, 5/31/12, Tupy... nambá mailto:anhanguera%40yahoo.com> > wrote: From: Tupy... nambá mailto:anhanguera%40yahoo.com> > Subject: Re: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com <mailto:firebird-support%40yahoogroups.com> Date: Thursday, May 31, 2012, 5:38 PM Mark, I agree your explanation is highly logical. But, using the words that were told to me by another developer, the generators values he found are previous values (the last used values at the development db before the overwriting by the new copy) (may be this information is not 100% of confidence, if the generators are in the same file than the tables). I think, at least, they had to be of values changed during the copy process, or old values, if no change have been applied to some of them, ok ? --- On Thu, 5/31/12, Mark Rotteveel mailto:mark%40lawinegevaar.nl> > wrote: From: Mark Rotteveel mailto:mark%40lawinegevaar.nl> > Subject: Re: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com <mailto:firebird-support%40yahoogroups.com> Date: Thursday, May 31, 2012, 4:56 PM On Thu, 31 May 2012 14:29:41 +0200, Hannes Streicher mailto:hstreicher%40gmx.de> > wrote: > Hello Tupy... nambá, > > what is the setting of forced writes > because generators are outside of transaction control and thf shud be > updated > immediately If your generators are at the start of the database file and the copying takes a while (large db), and there are a lot of concurrent transaction then the state of the generator page at the time that page was copied is not the same as at the time the last page of the database file is copied. Eg T=1 generator page copied, generatorX = 1 T=2 concurrent transaction changes generatorX = 2 . . . T=N last data page is copied, generatorX = 2 + x (where x >= 0) Now the copy of the database is in an inconsistent state as the generator page in the copy says the value = 1, while other parts of the file might have been written as part of the transaction that used the result of the generator having value 2. ++ 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] ++ 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] [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 [Non-text portions of this message have been removed]
Re: Fw: Re: RES: [firebird-support] Copy of the current db not updating generators
We run FB over Windows. The db file were overwritten, not a db restore. Also the information about the generators values can´t be taken as confident informations. I think I have been well attended by all of you. I have enough information about that to me. Thanks to all, Roberto Camargo. --- On Thu, 5/31/12, Mark Rotteveel wrote: From: Mark Rotteveel Subject: Re: Fw: Re: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com Date: Thursday, May 31, 2012, 5:51 PM On Thu, 31 May 2012 06:43:14 -0700 (PDT), Tupy... nambá wrote: > Or, according to An Harrison´s explanation, the generators had not been > updated due to not have been commited Even then, it would still show (older) values from production, not from your previous test database. On what platform or you running this test database? Windows or Linux? If you restored directly over the old database without disconnecting all users, I believe that on Linux that other developer might still see the *old* database if he was connected before the restore (not 100% sure though, never tried it). Mark ++ 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]
Fw: Re: RES: [firebird-support] Copy of the current db not updating generators
Or, according to An Harrison´s explanation, the generators had not been updated due to not have been commited --- On Thu, 5/31/12, Tupy... nambá wrote: From: Tupy... nambá Subject: Re: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com Date: Thursday, May 31, 2012, 5:38 PM Mark, I agree your explanation is highly logical. But, using the words that were told to me by another developer, the generators values he found are previous values (the last used values at the development db before the overwriting by the new copy) (may be this information is not 100% of confidence, if the generators are in the same file than the tables). I think, at least, they had to be of values changed during the copy process, or old values, if no change have been applied to some of them, ok ? --- On Thu, 5/31/12, Mark Rotteveel wrote: From: Mark Rotteveel Subject: Re: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com Date: Thursday, May 31, 2012, 4:56 PM On Thu, 31 May 2012 14:29:41 +0200, Hannes Streicher wrote: > Hello Tupy... nambá, > > what is the setting of forced writes > because generators are outside of transaction control and thf shud be > updated > immediately If your generators are at the start of the database file and the copying takes a while (large db), and there are a lot of concurrent transaction then the state of the generator page at the time that page was copied is not the same as at the time the last page of the database file is copied. Eg T=1 generator page copied, generatorX = 1 T=2 concurrent transaction changes generatorX = 2 . . . T=N last data page is copied, generatorX = 2 + x (where x >= 0) Now the copy of the database is in an inconsistent state as the generator page in the copy says the value = 1, while other parts of the file might have been written as part of the transaction that used the result of the generator having value 2. ++ 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] ++ 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]
Re: RES: [firebird-support] Copy of the current db not updating generators
Mark, I agree your explanation is highly logical. But, using the words that were told to me by another developer, the generators values he found are previous values (the last used values at the development db before the overwriting by the new copy) (may be this information is not 100% of confidence, if the generators are in the same file than the tables). I think, at least, they had to be of values changed during the copy process, or old values, if no change have been applied to some of them, ok ? --- On Thu, 5/31/12, Mark Rotteveel wrote: From: Mark Rotteveel Subject: Re: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com Date: Thursday, May 31, 2012, 4:56 PM On Thu, 31 May 2012 14:29:41 +0200, Hannes Streicher wrote: > Hello Tupy... nambá, > > what is the setting of forced writes > because generators are outside of transaction control and thf shud be > updated > immediately If your generators are at the start of the database file and the copying takes a while (large db), and there are a lot of concurrent transaction then the state of the generator page at the time that page was copied is not the same as at the time the last page of the database file is copied. Eg T=1 generator page copied, generatorX = 1 T=2 concurrent transaction changes generatorX = 2 . . . T=N last data page is copied, generatorX = 2 + x (where x >= 0) Now the copy of the database is in an inconsistent state as the generator page in the copy says the value = 1, while other parts of the file might have been written as part of the transaction that used the result of the generator having value 2. ++ 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]
RE: RES: [firebird-support] Copy of the current db not updating generators
Alan, yes, FORCED WRITES is checked. --- On Thu, 5/31/12, Alan McDonald wrote: From: Alan McDonald Subject: RE: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com Date: Thursday, May 31, 2012, 4:26 PM > Ok, thanks. This is well known. > > Then I ask = restoring the db will put the generators in the updated status ? > > --- On Thu, 5/31/12, Mark Rotteveel wrote: > Is your database set with FORCED WRITES=ON? Alan ++ 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]
Re: RES: [firebird-support] Copy of the current db not updating generators
Thanks, Mark. This is exactly what I needed to know. We know about the risks making copies this way, but this is done by another person, not by ourselves. Since there is a minimal chance of having troubles, certainly they will happen (see Murphy laws). But dividing responsabilities, each one is responsible for their working manner. As you say, I infer that the way the copies are given us is the "guilty", the causer for not having the generators updated, ok ? --- On Thu, 5/31/12, Mark Rotteveel wrote: From: Mark Rotteveel Subject: Re: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com Date: Thursday, May 31, 2012, 4:10 PM On Thu, 31 May 2012 04:25:02 -0700 (PDT), Tupy... nambá wrote: > Ok, thanks. This is well known. > > Then I ask = restoring the db will put the generators in the updated > status ? I am not sure what the problem is your having. Making a backup of production and restoring it in your dev-environment, will set everything in the database as it was at the time the backup was created including generators. If you mean something else, please explain what you mean. Mark ++ 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]
Re: RES: [firebird-support] Copy of the current db not updating generators
Ok, thanks. This is well known. Then I ask = restoring the db will put the generators in the updated status ? --- On Thu, 5/31/12, Mark Rotteveel wrote: From: Mark Rotteveel Subject: Re: RES: [firebird-support] Copy of the current db not updating generators To: firebird-support@yahoogroups.com Date: Thursday, May 31, 2012, 3:19 PM On Thu, 31 May 2012 08:09:33 -0300, "Fabiano" wrote: > Never copy the FDB file! You WILL corrupt both FDB and copy file! 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. Mark ++ 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]
[firebird-support] Copy of the current db not updating generators
Dear sirs, At our company, we use a copy of the production db as development db, for many reasons. When we do that, we make a phisycal copy of the fdb file. We observed that, when we make this copy, the generators are not updated, what forces us to update manually the development generators to the current production generators. It´s possible that we are not using the best approach to have an updated db copy, which will bring the copy with all objects updated for sure. This way, I ask you all if we have a manual procedure that resolve this question. If not, then, I ask you if the generators are stored in a separated file that can be copied. [Non-text portions of this message have been removed]
Re: [firebird-support] Re: why Blob is so slow ?
Roger, Thanks for sharing your personal experience, nothing better than them. Roberto Camargo. --- On Fri, 4/20/12, rcrfb wrote: From: rcrfb Subject: [firebird-support] Re: why Blob is so slow ? To: firebird-support@yahoogroups.com Date: Friday, April 20, 2012, 10:13 AM --- In firebird-support@yahoogroups.com, Ann Harrison wrote: > > On Thu, Apr 19, 2012 at 11:13 AM, Tupy... nambá wrote: > > > > > But, having many NFE (as many as the transactions), don´t you agree that > > these BLOB´s will be a great source of fragmentation inside the DB ? > > > > Err, no. It's not. I'm not 100% sure what you mean by fragmentation, but > all data, metadata, blobs, internal structure and state are kept on fixed > sized pages in a single file. Yes, if you're running on a disk that's full > and fragmented, that file will be scattered around the disk, but inside, > it's quite tidy. > > > > And, if I´m sure about my thinkings, as Firebird doesn´t have a way to > > defragment inside the DB, you don´t have a way to resolve this. > > > > When pages are released, they're reused. > > > > May be, for having a good solution for such kind of business, one had to > > use a MS SQL Server to periodically defragment the DB. Or another DB name > > that has this funcionality. I searched something like this at Postgres and > > I found a command named VACUUM that does something like this. Think about > > all of this, if you want. If have to have BLOB´s, I think Firebird is not a > > good solution for a great number of them. My thought, you don´t need to > > agree. > > > The PostgreSQL vacuum is similar to Firebird's continuous, on-line garbage > collection, except that it's a separate, off-line command. > > Good luck, > > Ann > > > [Non-text portions of this message have been removed] > Some years ago (at the time of Version 1.5) I observed the same behaviour backing up a database containing a lot of BLOBs. While storing the 'normal' data was reasonably fast, it slowed down when it come to store the BLOBs. Digging some deeper into that problem I found that the (System-File)-IO used uniformly large blocks while storing the non-BLOB tables. But when it came to the BLOB data the datablocks written to disk varied in size. It seemed, dumping a BLOB is a two stage job: first dump the 'normal' data of the table's record, then dump the associated BLOB's data; then continue with the next record. So for dumping a table containing a BLOB the IO seemed to be the problem. To resolve the problem we stored the BLOBs direct on disk and held only a reference in the Database (as already suggested). Actually I don't work with firebird anymore, so I can't verify these observations anymore, but maybe these informations can help you. Roger ++ 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]
Re: [firebird-support] why Blob is so slow ?
Still something = doesn´t matter if you have the blob field in a separated table. Since they are all together in a same DB file, they may cause defragmentation, no one can ensure where at the DB file they will be written and probably will be written in the middle of others non-blob columns/fields. If you have an separated DB for the blob-fields-tables, you will not have this problem, but then you will have new ACIDity problems. If Firebird had something like MSSQL Server Linked Servers, than you still could have integration between the two DB´s, having the best of both (no fragmentation at one / blob´s at the other). ... I had used MSSQL 6.5 (yes it's a long time ago) so can't comment on the need of defragmentation. I don't know Postgres, but I think the VACUMM is a similar to FB garbage collection. There is a way to defragment FB, make a back-up/restore, but I don't think it's needed, at least I had never had the need for such operation. A big blob will be stored in a bunch of pages that tends to be contiguous at the end of the file (yes, I know unsed page are reused), so I don't think it's the reason. A typical NFE would be around 10KB, depending on the page size it could be stored with the record, or be stored in two blob pages and just the blob id on the record page, anyway I prefer to have a separate table to hold the blobs, because in my case the access to blob's are not so often, so I prefer to have as many records per page as I can, and read a separate table (and therefore page) to read the blob contents when I need it. It's good to read your thougths, I am just arguing about the options :) see you ! ++ 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]
Re: [firebird-support] why Blob is so slow ?
MSSQL has two commands of the DBCC that allow to do defragmentation. The defragmentation is not a garbage collection, but putting all parts of an object (file or columns, hanging of the level - disc or DB) side by side, in a way that the reading of data will be almost fast, because all data will be found almost together. Normally,this is the way to have quick readings of data. Garbage collection is like removing of erased data. As I quickly read at some PostGreSQL pages, VACUUM has to be a defragment command for PostGreSQL. Since you know that you can make a defragment at Firebird making an DB restore, you can make a restore and compare the reading times at the two situations. If you have a meaningfull increase of readings speed (SELECT´s and so on) after the restore, this will mean that your problem is of high fragmentation. Also, after having made the restore, you can do a new backup and once again, a second restore, and see if you have time reduce. At the first restore, the time has to be long, but at the second, no more, because the second backup will store defragmented data. If you can, let´s try till now, all I have are only theories. Your results will be interesting for all of us. --- On Thu, 4/19/12, Alexandre Benson Smith wrote: I had used MSSQL 6.5 (yes it's a long time ago) so can't comment on the need of defragmentation. I don't know Postgres, but I think the VACUMM is a similar to FB garbage collection. There is a way to defragment FB, make a back-up/restore, but I don't think it's needed, at least I had never had the need for such operation. A big blob will be stored in a bunch of pages that tends to be contiguous at the end of the file (yes, I know unsed page are reused), so I don't think it's the reason. A typical NFE would be around 10KB, depending on the page size it could be stored with the record, or be stored in two blob pages and just the blob id on the record page, anyway I prefer to have a separate table to hold the blobs, because in my case the access to blob's are not so often, so I prefer to have as many records per page as I can, and read a separate table (and therefore page) to read the blob contents when I need it. It's good to read your thougths, I am just arguing about the options :) see you ! ++ 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]
Re: [firebird-support] why Blob is so slow ?
Hi, Alexandre, For the sample you gave (NFE), I agree with you, because the amount of files that will be generated will be very great and each file itself is not so big, probably they will not become a problem. And, in this case, they are part of a transaction. Probably not, but I´m not sure - one have to make comparisons to be sure about the best solution. I told in a generic way, specially were we have contracts, photos, and other no transactional documents. But, having many NFE (as many as the transactions), don´t you agree that these BLOB´s will be a great source of fragmentation inside the DB ? And, if I´m sure about my thinkings, as Firebird doesn´t have a way to defragment inside the DB, you don´t have a way to resolve this. May be, for having a good solution for such kind of business, one had to use a MS SQL Server to periodically defragment the DB. Or another DB name that has this funcionality. I searched something like this at Postgres and I found a command named VACUUM that does something like this. Think about all of this, if you want. If have to have BLOB´s, I think Firebird is not a good solution for a great number of them. My thought, you don´t need to agree. Friendly, best regards,Roberto Camargo. --- On Thu, 4/19/12, Alexandre Benson Smith wrote: From: Alexandre Benson Smith Subject: Re: [firebird-support] why Blob is so slow ? To: firebird-support@yahoogroups.com Date: Thursday, April 19, 2012, 6:42 PM Hi Roberto, Em 19/4/2012 08:52, Tupy... nambá escreveu: > Alexandre, > At my point of view, I prefer avoid using BLOB fields. First of all, because > these kind of field are not indicated for searches of any kind (most of them > are pictures). Second, > because > normally they have very large content, what does the DB increase in a large > amount. I think the most important property of the DB´s is the capability of > searches. But having fields which don´t allow us to do that, disturb the > funcionality of DB´s. > I prefer using to store files outside DB´s, storing inside them the path for > the files. So, you have the speed at all operations (searches and > backup´s/restores) and not a meaningfull increase of the DB´s. > > I´m not sure about the reasons for the backup/restore speed problem, but I > believe that inside the DB happens almost the same as at OS environment = > when adjacent areas are full, then the OS or the DB manager application most > look for distant areas to store parts of the data, causing a data > fragmentation. And to access the complete data, the OS or DB manager must > "remount" them, before delivering to the client. And the DB itself suffers > from the DB file fragmentation at disc level. > At file servers, normally file fragmentation are low (you don´t edit them > directly at the server) and still you can defragment the files. > At SQL server, you find discussions about internal tables and indexes > fragmentation, and you have commands to repair fragmentation. > At Firebird/Interbase, nobody talks about that, but we know it happens and > can became a problem, when the DB is greater in size. BLOB are worst for > causing that, affecting not only the BLOB fields and data itself, but also > fields and data of other data types. And you don´t have (i never see) > commands for DB internal defragment. > Try to do some experiences about that, making comparisons between different > solutions for a same problem. May be imediatelly filled DB will not show > great differences, but DB´s at common filling (day by day), after a great > amount of time, will show meaningfull differences. > Roberto Camargo,Rio de Janeiro / Brazil > In the past I used the approach of store just the filename, and I still use in some cases, but when everything is inside the datase it's easier to be sure that back-up/restore of everything is in place, to move the content around, provide transaction control (all the ACID features) that needs to be re-implemented if I work at filesystem level. Since you are in Brazil I could point a case where the need to store blob's is almost mandatory: The storage of XML files of "Nota Fiscal Eletronica" (eletronic invoice), We need to keep the data for the legal periods specified in our legislation, and to handle thousands (millions ?) of individual files on the filesystem is not the best option in my point of view, it's much easier to be sure that everything is secure inside the database. I disagree with you about the main feature of a RDBMS is search, search is a part of the whole system, but the main feature in my point of view is to store data. :) Of course there is no sense in store something if you cannot search for it, but, you could have a product that stores the data efficiently and not search it so efficiently called a RDBMS, but the other way around is not p
Re: [firebird-support] why Blob is so slow ?
Alexandre, At my point of view, I prefer avoid using BLOB fields. First of all, because these kind of field are not indicated for searches of any kind (most of them are pictures). Second, because normally they have very large content, what does the DB increase in a large amount. I think the most important property of the DB´s is the capability of searches. But having fields which don´t allow us to do that, disturb the funcionality of DB´s. I prefer using to store files outside DB´s, storing inside them the path for the files. So, you have the speed at all operations (searches and backup´s/restores) and not a meaningfull increase of the DB´s. I´m not sure about the reasons for the backup/restore speed problem, but I believe that inside the DB happens almost the same as at OS environment = when adjacent areas are full, then the OS or the DB manager application most look for distant areas to store parts of the data, causing a data fragmentation. And to access the complete data, the OS or DB manager must "remount" them, before delivering to the client. And the DB itself suffers from the DB file fragmentation at disc level. At file servers, normally file fragmentation are low (you don´t edit them directly at the server) and still you can defragment the files. At SQL server, you find discussions about internal tables and indexes fragmentation, and you have commands to repair fragmentation. At Firebird/Interbase, nobody talks about that, but we know it happens and can became a problem, when the DB is greater in size. BLOB are worst for causing that, affecting not only the BLOB fields and data itself, but also fields and data of other data types. And you don´t have (i never see) commands for DB internal defragment. Try to do some experiences about that, making comparisons between different solutions for a same problem. May be imediatelly filled DB will not show great differences, but DB´s at common filling (day by day), after a great amount of time, will show meaningfull differences. Roberto Camargo,Rio de Janeiro / Brazil --- On Thu, 4/19/12, Alexandre Benson Smith wrote: From: Alexandre Benson Smith Subject: [firebird-support] why Blob is so slow ? To: firebird-support@yahoogroups.com Date: Thursday, April 19, 2012, 2:12 AM For some time I wonder why blob's are so slow during back-up/restore. when I access one blob alone I don't think it's slow, but during the process of back-up/restore I can see that the table that holds the blob took so many time to be processed. Today I created a simple test case that resamble my real scenario: Two Tables CREATE TABLE DOCUMENT ( DOCUMENTID integer NOT NULL, PRODUCTID integer, COMPANYID integer, KIND char(1) NOT NULL COLLATE PT_BR, DESCRIPTION varchar(40) NOT NULL, CONSTRAINT PK_DOCUMENT PRIMARY KEY (DOCUMENTID) ); CREATE TABLE DOCUMENTOBLOB ( DOCUMENTBLOBID integer NOT NULL, DOCUMENTID integer, ITEM integer NOT NULL, BINARYDATA blob sub_type 0 NOT NULL, FILENAME varchar(255) NOT NULL COLLATE PT_BR, CONSTRAINT PK_DOCUMENTBLOB PRIMARY KEY (DOCUMENTBLOBID), CONSTRAINT UNQ_DOCUMENTBLOB UNIQUE (DOCUMENTID, ITEM) ); Table Document has 469 records Table DocumentBlob has 463 records The design were made to support more then a BLOB per document (like many JPG's pages, or a mix of JPG, XLS and PDF) The database has 245MB. I create a simple application to measure the size of the Blobs, the size of the binary data is 236MB. The blobs are not big, one of 37MB, one of 4MB, two of 2MB, twenty eight between 1MB and 2MB and the rest less than a MB. The average size is around 500KB per blob. Here are the timing for a back-up restore: # time /opt/firebird/bin/gbak blob_test.fdb blob_test.fbk -user sysdba -password masterkey -t real 0m6.927s user 0m0.671s sys 0m1.191s # time /opt/firebird/bin/gbak blob_test.fbk blob_teste2.fdb -rep -user sysdba -password masterkey -t real 10m8.894s user 0m0.042s sys 0m0.037s I think it's too slow to process less than 1k records and 250MB of data. Some more info: during the back-up or restore the CPU and i/o is low: Tasks: 93 total, 1 running, 92 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 97.3%id, 2.7%wa, 0.0%hi, 0.0%si, 0.0%st Tasks: 93 total, 1 running, 92 sleeping, 0 stopped, 0 zombie Cpu(s): 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Firebird Version: # /opt/firebird/bin/fbserver -z Firebird TCP/IP server version LI-V2.1.4.18393 Firebird 2.1 gstat -a -r output: Database "blob_teste2.fdb" Database header page information: Flags 0 Checksum 12345 Generation 17 Page size 16384 ODS version 11.1 Oldest transaction 1 Oldest active 2 Oldest snapshot 2 Next transaction 9 Bumped transactio
Re: [firebird-support] v 2.5 embedded is multi-user ?
May be the engine itself is multiuser, but it´s non sense to think it as a multiuser, since it has been thought to have it in portable applications, having been put in pendrives and small equipments that will not share it´s data directly. If this data must to be sincronized with another DB server, it´s adequate that the sincronization will be done by the portable application itself. Or it must capable of export it´s data and import data files like csv type (or binary files, a.s.o.). If an application using embedded DB will capture data for a bigger DB system, better to think and plan it to be only data collector, yet if it and the O.S. are capable of multi-processes and tasks. So I think. Roberto Camargo / RJ From: Helen Borrie To: firebird-support@yahoogroups.com Sent: Friday, January 27, 2012 5:55 AM Subject: Re: [firebird-support] v 2.5 embedded is multi-user ? At 09:46 PM 27/01/2012, Elmar Haneke wrote: >Am 26.01.2012 20:39, schrieb Jeff: >> I see that it supports multi-processes. >Id is mostly intended to be able to open an second DB client for debug >purpose. >> how bout multi-user / DB on shared network drive ? No, only on the local machine. >In an scenario with very few DB operations it might be possible to do >so, read documentation to enable operation on shared drive. No >Usually it is the better way to install an server process on one of the >machines (e.g. the fileserver). It is the *only* way to have remote clients accessing a database. ./heLen ++ 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]
Re: [firebird-support] question about copyright law - Email found in subject
Sean, I think the same way as you, but I told that as the only way to have no concerns about this possibility. And below, Helen Borrie speaks about this concern. Since the concern exists, the possibility exists. And, as one of Murphy´s law say, if it´s very difficult to something happens, than, it will happen. Thinking that over and over the customer is correct, nothing better than resolving this kind of situation using clearly a licensed product. If he pays, than he will have clearly no more concerns doesn´t matter what we think and use about GPL. Roberto From: Helen Borrie To: firebird-support@yahoogroups.com Sent: Thursday, December 15, 2011 2:21 PM Subject: Re: [firebird-support] question about copyright law At 11:29 PM 15/12/2011, Mark Rotteveel wrote: >On Thu, 15 Dec 2011 23:25:15 +1300, Helen Borrie >wrote: > >> That was a >> piece of bad advice, not only because it seriously infringes our >trademark >> policy but because it leaves *you* open to criminal prosecution for >false >> pretences. > >I disagree: the IPL and IDPL licenses gives every user the right to >sublicense the work, so they can make a pretty certificate to go with the >license terms if they want to. Whether you agree or disagree, the IPL and IDPL licences refer to the right to copy the source code, not the right to deploy binaries. It is not because of the licences that people get Firebird free of payment. From: "Leyne, Sean" To: "firebird-support@yahoogroups.com" Sent: Thursday, December 15, 2011 4:15 PM Subject: RE: [firebird-support] question about copyright law - Email found in subject Roberto, > The question is that, according to the german culture, there is over and over > the worry about rights. And, still according to the german point of view, > there > is no warranty that later anybody will come and ask for the payment of rights, > still thinking that currently there is no "Vater" or "Mutter" for Firebird > (but, > really, Embarcadero now could be thought as the "Grossvater" of this rights). > > Accordingly with all this questions, better review your project to work with > Microsoft SQL Server or any other paid sql server - may be, can be better > have first a contract with your customer to pay for and make this review. > Then, buy a license for that sql server, and deliver for your customer. If > there > is no confidence with the current praxis for free software, this is the only > way > to be in peace with your customer. Ask your customer if he has licensing concerns about using the Firefox browser. If not, have him open the browser, go to Help | About and click on the "Licensing Information" link. Then have him compare the wording of the Mozilla Public License (MPL) against the InterBase Public License (IPL). He will find that they are 99% the same. The only substantial difference being that the name "Mozilla Project " has been replaced with "Interbase Software". So, he should have no more concern about using Firebird than he does with Firefox! Sean ++ 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]
Re: [firebird-support] question about copyright law
Olaf, I understand what you mean and Mark left it very clear about the no existence of this situation with Firebird. The question is that, according to the german culture, there is over and over the worry about rights. And, still according to the german point of view, there is no warranty that later anybody will come and ask for the payment of rights, still thinking that currently there is no "Vater" or "Mutter" for Firebird (but, really, Embarcadero now could be thought as the "Grossvater" of this rights). Accordingly with all this questions, better review your project to work with Microsoft SQL Server or any other paid sql server - may be, can be better have first a contract with your customer to pay for and make this review. Then, buy a license for that sql server, and deliver for your customer. If there is no confidence with the current praxis for free software, this is the only way to be in peace with your customer. I hoffe das das etwas geholfen hat Viel erfogt ! Mit freundliche Grüsse, Roberto Camargo (mit ein Grossvater aus Westfallen), Rio de Janeiro/Brasilien From: "olaf.kl...@satron.de" To: firebird-support@yahoogroups.com Sent: Wednesday, December 14, 2011 4:23 PM Subject: Re: [firebird-support] question about copyright law On Wed, 14 Dec 2011 19:32:39 +0100, olaf.kl...@satron.de wrote: > Hello everybody, > > one of our customer has no confidence in the Copyright Law of the > firebird-database. Is there a certificate for confirmation? We need to > confirm all copyright claims to take over! > > Thank your for your help! What exactly does your customer question? The law (which has nothing to do with Firebird, but with your country), the license or copyright of Firebird, or the license or copyright of the database? You don't register for copyright, copyright is something that is linked with the creation of a work itself, so there is no 'certificate of confirmation' (whatever that is, and whatever legal value such a document would have). The open-source license of Firebird is available for inspection and review, as is the original open-source licnese for the release of Interbase. The history of source changes and their committers can be viewed on sourceforge (through CVS and Subversion). If you really need advise on copyright, I'd advise you took talk to a IP lawyer with experience with open source.. Also I don't understand what you mean with 'We need to confirm all copyright claims to take over!'. Mark Hello Mark and Thomas, our customer thinks, that he must pay for firebird in future, even retroactively. In German: Sie sind ja bestimmt auch über das deutsche Urheberrechtsgesetz in Kenntnis. Ein Internet Ausdruck hat rechtlich absolut keinen Wert, wenn es dem Eigentürmer einfällt Geld zu verdienen so kann er das jederzeit tun. Auch rückwirkend. Nur durch eine Form der Urkunde werden Rechte überlassen. I don't know how can I formulate this words in english. Thanks again Olaf ++ 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]
[firebird-support] Importing with a Date type column
Dear mates, I´m experiencing problems trying to import a date column to a Firebird db. I used many possibilities, but with none of them I got to import this column. I also tried FBExport but the orientations were not enough to resolve. Does someone also have experienced this problem and got a solution ? I got a message wich says "conversion error from string " (#-413) I found people talking about this problem, but none indicate a solution. Thanks, Roberto Camargo. [Non-text portions of this message have been removed]
