Re: [firebird-support] ASCII_CHAR(9)
I will implement this in my code : update produse set cod_produs = substring(trim(cod_produs) from 1 for char_length(trim(cod_produs)) - 1) where substring(trim(cod_produs) from char_length(trim(cod_produs)) for 1) = ASCII_CHAR(9) Thank you again, Tiberiu
Re: [firebird-support] ASCII_CHAR(9)
Thank you, exactly what I needed ! Tiberiu ---In firebird-support@yahoogroups.com, wrote :
[firebird-support] ASCII_CHAR(9)
Hi all, Firebird 3, Delphi IBX. Somehow one of my customers inserted a product code so that at the end of the string he added chr(9) and stored this string in the database. If I try to select cod_produs from produse where cod_produs = 'EI20010518' I get an empty query if I do select cod_produs from produse where cod_produs starting with 'EI20010518' I get the record if I do select cod_produs,-> 'EI20010518' char_length(trim(cod_produs)) -> 11 (instead of 10) from produse where cod_produs starting with 'EI20010518' I get the record this query : select cod_produs from produse where cod_produs starting with 'EI20010518' and substring(cod_produs from 11 for 1) = ASCII_CHAR(9) it is true, I get the record. My question is : Is there a query, a function, for filtering out these errors ? Someting like : update produse set cod_produs = AnsiOnly(cod_produs) ? Thank you, Tiberiu
Re: [firebird-support] Digest Number 9396
All right, thank you all for your time ! I have code that uses temporary tables in at least 30 places in my programs. This is the way I did complicated queries. Most of these procedures are written 10 years ago (Interbase, Firebird 1.0). I cannot change everything (replace every query that uses temporary tables), cannot do this without proper testing. In the first phase I changed every field definiton of these temporary tables, defined my domains Integer -> MyIntegerDomain and changed the definition (DDL) of the temporary tables in my code. I sure will change the code / queries that uses those temporary tables. Tiberiu
RE: [firebird-support] Digest Number 9396
I tested the query on a big database. It was faster if I disabled the index. Tiberiu ---In firebird-support@yahoogroups.com, wrote : > 2. the use of index is deliberately blocked, I want to do it this way, my > query > is faster. "Blocked" why? Sean
RE: [firebird-support] Digest Number 9396
Yes, exactly this is what I wrote before ! Tiberiu
Re: [firebird-support] Digest Number 9396
And how should I do my queries ? Is there some documentation for this ? I need to compile data from several tables, scan the records, update , delete some and then prepare the data for a report. For me the with query as( select ) select * from query is not enough. Tiberiu
Re: [firebird-support] Digest Number 9396
I understand. And I saw that when I drop the temporary table these auto generated domain names disappear but the generated names keep on "growing" . I modified my routines so that : I verify if I have the domain SELECT * FROM RDB$FIELDS where RDB$FIELD_NAME = 'MYINTEGERDOMAIN' if not, then create my domain MYINTEGERDOMAIN and finally I create the tables : create myTempTable1122 (id MYINTEGERDOMAIN) Hope this is all right now . Thank you all for your time, Tiberiu
Re: [firebird-support] Digest Number 9396
So, everytime I create a table in FireBird, a new domain is created automatically in the database ? This surely could be an explanation of my problem ! I will try to create domains and use those in DDL . Is this a correct approach : create domain MyIntegerDomain Integer ; create table myTable (id MyIntegerDomain) ; ? Tiberiu
Re: [firebird-support] Digest Number 9396
1. I create those temporary tables for one user only, for one transaction only, not visible for other users. I keep a track (log) of those tables and delete when I exit the program (drop table). The temporray table name is generated randomly 'TEMP_TABLE_' + . 2. the use of index is deliberately blocked, I want to do it this way, my query is faster. 3. maybe I was not clear in stating my problem. I do not declare domains. Firebird declares those domains automatically when I create new tables (with usual types: Integer, char, etc). My problem is that when I create those tables, this automatic domain declaration declares (creates automatically) "huge" domain names (saw these with IBExpert, setting system domains visible). I can backup the database containing these domain names but I cannot restore normally. One screen capture here : Screenshot 2016-11-16 17.49.51.png https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0 https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0 Screenshot 2016-11-16 17.49.51.png https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0 Shared with Dropbox View on www.dropbox.com https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0 Preview by Yahoo Thank you, Tiberiu
Re: [firebird-support] Digest Number 9396
This is how I create my temporary tables : create global temporary table TEMP_112233 ( id : INTEGER, name : CHAR(20) ) on commit preserve rows I also do some create views : CREATE OR ALTER VIEW CAUT_PRODUSE_VIEW_4982590 ( COD_PRODUS, ID_PRODUS ) AS select produse.cod_produs, produse.id_produs from produse inner join sortiment on (sortiment.id_sortiment + 0 = produse.id_sortiment) where denumire_sortiment <> 'N ' I do not know about any domain definitions, this should be an internal FireBird thing. if I have RDB$1 defined this way : CREATE DOMAIN RDB$1 AS INTEGER whenever I create another table with an Integer field, this domain should be used, am I wrong ? is there a command create table TEMP_112233 ( id : INTEGER use domain RDB$1 ) ? BTW, I am located in Romania. Thank you, Tiberiu
Re: [firebird-support] Digest Number 9396
OK, but if gbak backups this domain name , shouldn't it restore it also ? Tiberiu ---In firebird-support@yahoogroups.com, wrote : The RDB$ domains are auto generated by the system - RDB$1297080260861 says that it is the 1,297,080,260,861 domain created by the system.
Re: [firebird-support] Digest Number 9396
I use only CREATE TABLE and DROP TABLE metadata commands in runtime. I populate the tables and that's all. I don't create domains, not at all. I use FB 3.0 on a Linux server (I don't maintain that server) . The database file is very big - 1+ GB in size, my customers append arround 3000 records each day. To try to fix my domain name problem I do as you suggested : I do the restore of the new database file with the correct structure and datapump the records. I just did that yesterday (I have some 6 million records in a few tables) but this process is very time consuming - around 4 hours of work. I have one possible explanation : last time I upgraded from FB 2.55 to FB 3.0, I did the process on a Windows machine : backup FB 2.55 and restore FB 3.0 on the machine. I copied , then, the FDB file in Linux (Samba) , didn't performed a normal restore on Linux (because it is very time-consuming and my customer couldn't wait). Maybe something bad happened then with those domain names. Yesterday I did this : 1. Exported the records (in some XML files) 2. Restored an empty database file (full restore - not copy the FDB file) 3. Datapumped the records (from the XML files) . I will see the result and keep you informed. Thank you , Tiberiu
Re: [firebird-support] Digest Number 9396
Thank you for your answer, "You have data in your column in that table that no longer fits in the new definition" The data in a table that uses one of those domains has only one record, I do not alter that record at all (some document type name default defined). I create many temporary tables in runtime (with random names) and drop those when I leave / close the database. Can this be the problem ? but I use this structure / program at many other customers ... No problem there. Anyway, if GBAK could backup those domains (with huge names), why , the same GBAK, could not restore those with the same name ? Tiberiu
Re: [firebird-support] Digest Number 9396
Support for Users of Firebird ReleasesHi all, I have a FireBird 3.0 server on a Linux machine, at a customer who appends arround 3000 records / day in some tables. I have a strange problem that keeps me from performing normal backup / restore operations. In the database there are some huge domain names in System Domains (I use IBExpert). For example : "RDB$1297080260861" (but some other domain names are normal "RDB$100") These domains are used by my normal tables and DROP DOMAIN RDB$1297080260861 gets me to an error, given the fact that the domain is used by one of my tables. Also, any temporary table that I create (in runtime) creates these long domain names. When I backup my database gbak generates normal fbk file - no error here But when I restore this file, at the end, it generates an error - see screenshot : https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0 gbak: ERROR: arithetic exception, numeric overflow, or string truncation gbak: ERROR:numeric value is out of range I am sure this is connected to the system domain name. my questions : 1. How can I modify a System Domain name ? 2. For a given Field in a Table , how can I modify the Domain that it uses - replace with another ? 3. Is there a way to modify the generator that generates these name ? Probably it is a way to fix this also ... I have a fbk file and I have the initial FDB file. If somebody could help me with this I could send the file - I deleted all the data. In my program I have a procedure that exports the containts of every table in the database and them imports in a new , empty database - I did this a few months ago. But in this case, after a few days, the problem repeated. Thank you , Tiberiu
RE: [firebird-support] FB 3.0
I was not complaining about IBX nor about FB. I was just asking. Embarcadero / Idera stopped developing IBX, they now support FireDAC components, this is why I asked. By now, replacing FB 2.5.5 with 3.0 on my test machine seems to be ok. Tiberiu
[firebird-support] FB 3.0
Support for Users of Firebird ReleasesHi all, Currently using FB 2.5.5 but starting tests with the new FB 3.0 . A few questions : 1. The new ODS (12) is not compatible with older (<=11.2) so, according to http://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-apiods.html we have to do a manual backup on 2.5.5 and a restore on 3.0. It says that “A legacy provider for databases with ODS 8 to 11.2 is planned for a future sub-release.” This means that (I have some 100+ databases in use at some of my clients) I should wait for a way to access these ODS 11.2 databases from FB 3.0 ? It would be perfect ... 2. Anybody tested code with with FB 3.0 and Delphi and IBX ? Any problems ? Can IBX properly access the boolean fields in FB 3.0 ? Congratulations for this new release, Tiberiu
Re: [firebird-support] FB 3.0
Now it works fine here too ... Thank you , Tiberiu
Re: [firebird-support] FB 3.0
Perfect !!! Thank you very much, I replaced all fbclient.dll and gds32.dll -s (\windows\system32, etc) with the new FB 3.0 libraries and the app connected !!! Thank you, Tiberiu
Re: [firebird-support] FB 3.0
Thank you for your answer. I used FireDAC before for MySQL and SQLite databases. I wanted to know if there is a way that I could use IBX with FB 3.0 . It seems there is not ... Tiberiu
[firebird-support] FB 3.0
Support for Users of Firebird ReleasesHi all, We use FireBird since 1.0 and develop programs with Delphi (recently upgraded to D10 Seattle). We use plain IBX components to connect to the FB server in a few big accounting programs. Tried to play with FB3.0 RC1 and installed it on port 3051 for testing, alongside FireBird 2.5.4 on port 3050. Our problem is that Delphi (the IDE) connects to the new server / ODS database, also IBExpert connects to the new server / database but the programs, on runtime, does not. Can we specify somewhere (params in TIBDatabase / TIBTransaction) the path / file of the client dll for Firebird / Interbase ? Probably the app cannot load the proper dll (FB3.0) on statup. Anybody using Delphi and IBX could connect to FB3.0 with their programs ? Are these compatible ? Is there something we could do ? Upgrading to FireDAC or IBO is our last option. Thank you, Tiberiu PS backup and restore from ODS 11.x to OSD 12.0 is not a problem, since we could install both versions (2.5.4 and 3.0) and automate the process.
RE: [firebird-support] Cumulative queries
Yes , it is superserver I am running. I will try it ! Thank you, Tiberiu
Re: Odp: [firebird-support] Cumulative queries
If you want to query (management asks for) the yearly turnover of the company from the start of the year to yesterday and you have 100+ invoices ... It is time and resource consuming, even with stored procedures. If I have monthly archives (tables) then I have to query each of the tables. Tiberiu
[firebird-support] Cumulative queries
Support for Users of Firebird ReleasesHi all, I have a Firebird stock management system with 100+ tables and 10+ clients. I have some cumulative queries in my Delphi program that generates reports. My problem is that while the queries are run (less than 5 minutes each), the whole system performance is poor, people complaining about the speed of the database. How do you deal with this ? Thinking about : 1. Create a backup database and have the queries run there (backup / restore during the night) ? This would be the obvious option. If data for the reports is one day behind the real system is not a big problem. 2. In FireBird do I have some switch to lower the priority of a query ? select * from table –low priority mode- some other ideas ? Thank you in advance , Tiberiu
[firebird-support] Re: BLOB
I ran some tests : A. "D:\Program Files (x86)\Firebird\Firebird_2_5\bin\gbak.exe" -b -g -v -service xx.xx.xx.14:service_mgr -user sysdba -password masterkey e:\fdb\gestoc_asolo.fdb gestoc_asolo.fbk B. "D:\Program Files (x86)\Firebird\Firebird_2_5\bin\fbsvcmgr.exe" xx.xx.xx.14:service_mgr -user sysdba -password masterkey action_backup -dbname e:\fdb\gestoc_asolo.FDB -bkp_file gestoc_asolo.fbk A. finished in 2 minutes, no visible delay saving table "log_table" . Much much faster than my backup but the FBK file IS ON THE SERVER. I want it on my backup PC (I do this for my clients in case they have a fatal error or they want to restore the database to some previous state) . B. got a "Feature is not supported" error. Anyway, I presume that both A and B is about saving the data to a local file on the server. This is not what I want to do. How do other FireBird users manage this with really large databases ? Save locally and get the FBK file via FTP or some other protocol ? Thank you, Tiberiu
Re: [firebird-support] BLOB
RE: [firebird-support] BLOB
I recorded the process of backup , if anybody would be so kind to help me, please take a look at : FireBirdBackup http://youtu.be/txUruy35qHE http://youtu.be/txUruy35qHE FireBirdBackup http://youtu.be/txUruy35qHE This feature is not available right now. Please try again later. View on youtu.be http://youtu.be/txUruy35qHE Preview by Yahoo the command line is : "C:\Program Files (x86)\Firebird\Firebird_2_5\bin\gbak.exe" -b -g -v -user SYSDBA -password masterkey xx.xx.xx.14:e:\fdb\gestoc_asolo.FDB gestoc_asolo.fbk between 1:24 and 9:41 gbak says "Writing data for LOG_TABLE" . sure this is not normal. Tiberiu
Re: [firebird-support] BLOB
my BLOB records are plain text phrases, somewhere between 1 and 400 char-s , containing setup information (in Delphi I save a TStringList to file and I backup that file in my database). My question was about saving these tables with gbak -v (verbose) where I can see the time spent to backup / restore each table. Can I speedup this process somehow ? 1. maybe gbak backup without some tables 2. maybe some magic gbak switch that "knows" that my BLOBs are text only This is not a showstopper (the largest of my tables of this kind was backed up in less than 30 minutes) , I was just asking. Thank you for all the answers, Tiberiu
[firebird-support] BLOB
Hi all, I have the following table : CREATE TABLE LOG_TABLE ( ID_LOG_TABLE INTEGER NOT NULL, SESSION_IDINTEGER, LOG_DATA DATE, LOG_TIME TIME, LOG_TEXT VARCHAR(100), LOG_TEXT_COMPLET BLOB SUB_TYPE 1 SEGMENT SIZE 80 ) with some 3000+ records. Why does it takes so much time to backup / restore (gbak) this table ? (because the BLOB field). Do I have any options to speed up the process ? Thank you, Tiberiu
