Re: [firebird-support] Compatibility questions: Current release versus 1.5.3.4870
Hi ! It's dificult to tell... From version to version there is minor problens that can occur, like new reserved wirds and so on. But in general, you will be safe doing the upgrade. It's recommend to perform a back-up/restore cycle to upgrade de database ODS. But FB 2.5 can read databases created by 1.5 version. You have a problem, in the actual state, it's not working, what can you lost if you try to upgrade and discover that it will not work ? Make a copy of the database and give it a try, it will not hurt. good luck ! Em 26/7/2017 12:52, scott...@comcast.net [firebird-support] escreveu: Hello! I know it's been a while since I've posted on this Group, but current circumstances have brought me back. Today, I discovered that Microsoft forcibly removed fbserver & fbguard for version 1.5.3.4870. This version is included with SAM Broadcaster v3, which is critical to my work as an "On-Net Personality" on a very popular music stream. There was no warning for it's removal at the Windows 10 Creator's Update "updates" that happened recently. All of the files for Firebird were removed. The application's DB is safe. When trying to re-install Firebird-1.5.3.4870-0-Win32.exe, Windows throws the "This App can't run on your PC" message with "Firebird doesn't work on this version of Windows. An updated app may be available" message and shows buttons with "Learn more" and "Close" I've been running the 1.5.3.4870 build under Windows 10 with *zero* problems up until the last update. */ /* My question (and I'm sorry if I'm asking this before doing any research on my own) is simply, how compatible is the 2.5 version with databases created with 1.5.3? I'm nearly in panic-mode here, and exploring all options to get this resolved. Thanks! --Scott Kindorf General Manager, The WebMaster Internet Radio Network On-Net Personality "The ScottK Radio Show" on WebMaster Internet Radio Network. -- Alexandre Benson Smith Santo Andre - Sao Paulo - Brazil
Re: [firebird-support] Enable authorization for legacy Firebird clients
HI Em 7/7/2016 05:49, tomi.j...@oscar.fi [firebird-support] escreveu: Hi, I don’t know if i have understand “Enable authorization for legacy Firebird clients” right but.. I just installed Firebird 3 to new development server and checked “Enable authorization for legacy Firebird clients” so I can test connection from old 2.5 clients. In this installation window I create random password like “pass1234”. When I connected to database from same server connection is okay with “pass1234”. But when I tried to connect from old development server where firebird 2.5 is installed with “pass1234” I get error “Your user name and password…”. My surprise was when I tried connect database from old server with pasword “masterkey” that connection is succesfully. I have not created user SYSDBA with “masterke” password and still I get connected in database. So my question is that should this be like this? Best regards, -Tomi I don't know how are your configurations, but to enabled Legacy Auth you must change: AuthServer = Srp, Legacy_Auth UserManager = Srp, Legacy_UserManager WireCrypt = Enabled after that, the server is enabled to authenticate using the old protocol. I think you are in this point, but are unable to authenticate from other users because the other users was create by the default plugin (SRP), you must create those users using the Legacy_UserManager plug in.The SYSDBA was automatically created by the installer using the Legacy_UserManager plugin this is the reason you could connect using SYSDBA. connect to your database and execute the following: select sec$user_name, sec$plugin from sec$users; You should see something like: SEC$USER_NAME SEC$PLUGIN === === User1 Srp User2 Srp This indicates that the users are created but with the new SRP plugin, to authenticate legacy users you need to create them using the legacy user manager plugin. execute this: create user User1 password 'pas1234' using plugin Legacy_AuthManager; commit; after that execute this: select sec$user_name, sec$plugin from sec$users; You shoud see: SEC$USER_NAME SEC$PLUGIN === === User1 Srp User2 Srp User1 Legacy_UserManager Note that you have two entries for User1, one for the SRP plugin and the other for the Legacy Authentication. HTH see you !
[firebird-support] FB 3 - Per database configurations (Providers and Security Database vs Legacy Auth)
Hi !
I am looking at the per-database configuration (FB 3) and have two doubts:
I defined my databases.conf like:
demo_naolocal=c:\bd\demo_naolocal.fdb
{
Providers = Remote,Loopback
}
after that on the same machine I tryed:
C:\fb3>isql demo_naolocal -user teste -password abc
Statement failed, SQLSTATE = 08001
I/O error during "CreateFile (open)" operation for file "demo_naolocal"
-Error while trying to open file
-O sistema não pode encontrar o arquivo especificado. (Translates to:
The system could not find the specified file)
Use CONNECT or CREATE DATABASE to specify a database
SQL> quit;
C:\fb3>isql localhost/3053:demo_naolocal -user teste -password abc
Statement failed, SQLSTATE = 08001
unavailable database
Use CONNECT or CREATE DATABASE to specify a database
SQL>
I expected the first one to fail and the second to succeed.
On the release notes page 18 I found this:
Disable embedded connections
If you don't want any type of direct access, you may disable embedded
mode (= direct filesystem-level
access) altogether by opening firebird.conf and locating the Providers
entry. The default (which is
probably commented out) is:
#Providers = Remote,Engine12,Loopback
Now, either remove the hash mark and the Engine12 provider (this is the
one that makes the embedded
connections), or – better – add an uncommented line:
Providers = Remote,Loopback
The Remote provider takes care of remote connections; the Loopback
provider is responsible for TCP/IP
connections via localhost, as well as (on Windows) WNET/NetBEUI and XNET
connections to databases
on the local machine. All these connection types require full
authentication and have the server process, not
the user process, open the database file.
Please notice that you can also set the Providers parameter on a
per-database basis. You can set a default
in firebird.conf as shown above, and then override it for individual
databases in databases.conf
like this:
bigbase = C:\Databases\Accounting\Biggus.fdb
{
Providers = Engine12,Loopback
}
if I change the databases.conf to:
demo_naolocal=c:\bd\demo_naolocal.fdb
{
Providers = Remote,Loopback,Engine12
}
I can connect with both alternatives.
C:\fb3>isql demo_naolocal -user teste -password abc
Database: demo_naolocal, User: TESTE
SQL> quit;
C:\fb3>isql localhost/3053:demo_naolocal -user teste -password abc
Database: localhost/3053:demo_naolocal, User: TESTE
SQL> quit;
C:\fb3>
What I am doing wrong ?
The second doubt is regarding per-database security vs Legacy User Manager
I have on databases.conf the following entry:
demo_s2=c:\bd\demo_s2.fdb
{
SecurityDatabase = demo_s2
}
SQL> create user t4 password 't4' using plugin legacy_usermanager;
Statement failed, SQLSTATE = 42S02
add record error
-table PLG$VIEW_USERS is not defined
SQL>
I can create SRP users without a problem, my question is:
Is it possible to have legacy users stored on a database distinct from
security3.fdb ? if so... I just need to create the missing view ?
SQL> show view plg$view_users;
PLG$USER_NAME (SEC$USER_NAME) VARCHAR(31) CHARACTER
SET UNICOD
E_FSS Not Null
PLG$GROUP_NAME (SEC$USER_NAME) VARCHAR(31) CHARACTER
SET UNICOD
E_FSS Nullable
PLG$UID (PLG$ID) INTEGER Nullable
PLG$GID (PLG$ID) INTEGER Nullable
PLG$PASSWD (PLG$PASSWD) VARCHAR(64) CHARACTER SET
OCTETS No
t Null
PLG$COMMENT (RDB$DESCRIPTION) BLOB segment 80,
subtype TEXT
CHARACTER SET UNICODE_FSS Nullable
PLG$FIRST_NAME (SEC$NAME_PART) VARCHAR(32) CHARACTER
SET UNICOD
E_FSS Nullable
PLG$MIDDLE_NAME (SEC$NAME_PART) VARCHAR(32) CHARACTER
SET UNICOD
E_FSS Nullable
PLG$LAST_NAME (SEC$NAME_PART) VARCHAR(32) CHARACTER
SET UNICOD
E_FSS Nullable
View Source:
==
SELECT PLG$USER_NAME, PLG$GROUP_NAME, PLG$UID, PLG$GID, PLG$PASSWD,
PLG$COMMENT, PLG$FIRST_NAME, PLG$MIDDLE_NAME, PLG$LAST_NAME
FROM PLG$USERS
WHERE CURRENT_USER = 'SYSDBA'
OR CURRENT_ROLE = 'RDB$ADMIN'
OR CURRENT_USER = PLG$USERS.PLG$USER_NAME
SQL>
TIA
see you !
++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
Re: [firebird-support] Advice requested on design pattern
On 20/5/2015 16:03, Mike Ro miker...@gmail.com [firebird-support] wrote: Thank you for your response. On 20/05/15 11:25, Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support] wrote: Another way of doing what you want would be to use SPs instead of Views. Then if you select * from SP and hide it as below, no-one can see what you are doing with the data. In this way wouldn't the SP have the same problem that was mentioned by Louis, namely that only the index from the first table in the query would be used? Sorry if I misunderstood or my knowledge of SP is lacking. I did not read it in full details... But the case of select on views be able to use the index on the first table I believe that's because of using of outer joins, with inner joins that would not be the case... see you ! --- Este email está limpo de vírus e malwares porque a proteção do avast! Antivírus está ativa. http://www.avast.com
Re: [firebird-support] Computed index for case insensitive queries
Em 25/8/2014 00:59, Caroline Beltran caroline.d.belt...@gmail.com [firebird-support] escreveu: Mark, thank you for responding. I ended up downloading FlameRobin and it does show computed indexes in the 'Index' tab as well as in the DDL, i.e.: CREATE INDEX IDX_FNAME ON PEOPLE COMPUTED BY (lower(fname)); P.S. I found your response in my Google 'Sent' folder, it never made it into my inbox, but anyway, I am glad I located your message. Why not use a case insensitve collation ? see you !
Re: [firebird-support] Suboptimal plan
Hi ! There is some tricks to change the way optimizer will choose the order of tables: example: select * from LongTable L left join ShortTable S on (S.FieldA = L.FieldA) The outer join would force the table on the left to be scaned first. Another option is select * from LongTable L join ShortTable S on (S.FieldA = L.FieldA+0) the +0 (or || '' if the field is a char/varchar) would prevent the optimizer to use the index on LongTable.FieldA and could lead to change the join order see you ! Em 1/8/2014 18:09, Aldo Caruso aldo.car...@argencasas.com [firebird-support] escreveu: Sean and Carlos, thanks for your answer. Although the statistics are up to date the optimizer still chooses the shorter stream as the controller. Nevertheless, I found a workaround using a subquery on the shorter table instead of a join. This forced the optimizer to traverse the longer table first and boosts the performance even more than using an optimized query with a fixed plan. Thank you very much for your help. Aldo El 01/08/14 a las 16:01, 'Carlos H. Cantu' lis...@warmboot.com.br [firebird-support] escibió: Are the indexes statistics up to date? Also, post the query, query plan, and indexes stats. Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br ACacacfs Hello, ACacacfs In a select statement there are two tables joined by a single ACacacfs matching field, with search conditions in both of them. One of them has ACacacfs roughly 13 records, while the other has 600. ACacacfs Inspecting the plan generated by the optimizer, I realized that it ACacacfs uses the shorter one as the controlling stream instead of the longer one ACacacfs (i.e. the shorter table is at the left side). ACacacfs Using a PLAN expression with the corresponding indexes to bypass ACacacfs the plan deviced by the optimizer in order to switch the order of the ACacacfs streams, the performance is boosted (nearly three times faster). ACacacfs What can cause the optimizer to pick a shorter table as the ACacacfs controlling stream ? ACacacfs Is there a workaround to induce the optimizer to select the streams ACacacfs in a fixed way or should I have to resort to a manually imposed PLAN ? ACacacfs Thanks, ACacacfs Aldo
Re: [firebird-support] How to convert big FIREBIRD DB to one charset (win1251) to UTF8 ?
Em 30/5/2014 04:26, loris.lu...@gmail.com [firebird-support] escreveu: I need your help and/or suggestions to solve a (maybe uncommon) task. I searched a lot on the web and I had some success using a tool called fbclone but this is definitely not a solution (due to extreme slowness of the conversion, many days, something must be wrong with this software) I have a large Firebird 2.5 DB (a couple of tables have few millions records) to convert from WIN1251 charset to UTF8 charset. Is there a way to do efficiently this kind of conversion? (preferably not using a intermediate text sql dump, that would not applicable because the size of the DB). I'm using Firebird on a linux machine, and I really like to do the job using a bash script, but I can fallback also using a remote Windows pc. Thanks for any suggestions. Best regards. I don't know if there is a faster way... But this one would be a secure and very easy way: 1.) Extract metadata from the source database (isql -x) 2.) Change the definition of the domains and/or fields to the desired charset 3.) Create a new database with the corrected script 4.) Use IBDatapump (or any other datapump tool) to transfer the data I don't know the size of your database, but I doubt it will take days to transfer it. To speed up the process, turn forced writes off, try to keep the source/destination database on diferent disks and use local/embedded connection see you !
Re: [firebird-support] Aliases and Windows Host - any
Hi ! Em 26/5/2014 21:17, Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support] escreveu: I'm trying to do that exactly but cannot make it work. I'm using Win7, FB 2.5.1 on host (server) and Win7 on client. The application is running in Delphi XE2 and works perfectly on host client when I do the following (as I have since Delphi5): In the application folder I have the database, the application, plus a text file called App_Data.Txt with the following line: //AJD-SONY/C:/APPS/PJSMOULDINGS/ORDERS.FDB I load this file and set the database name to it - then open the database and it all works as it should. Now I try and replicate the example below from Helen et al. On the server I have this in my aliases.conf PJSMOULDINGS=C:\DATABASES\PJSMOULDINGS\ORDERS.FDB I also set PJSMOULDINGS as the DataBaseName in the Delphi app itself and this works on the server without loading App_Data.Txt Also if I set the database name to 'AJD-SONY:'+PJSMOULDINGS, it works. But not on the client. The error I get after 2-3 minutes is Unable to complete network request to host AJD-SONY. Failed to establish a connection . The program loads but database is unavailable. This is regardless of whether using a mapped drive or a shortcut. Any help would be appreciated. So far my own method has worked without a hitch, but sooner or later Regards Alan Alan J Davies Aldis I think you have the TCP/IP port 3050 closed on the host machine. Check the windows firewall In the first example you are not using a TCP/IP conection. 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Query optimization mystery
Em 13/5/2014 17:14, Kevin Donn kd...@msedd.com [firebird-support] escreveu: Try this: select * from ( select a.User_ID from Advocate where a.USER_ID=37 ) as FILTER1, supprog sp Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code This doesn't quite execute. I cleaned it up to this: select * from ( select a.User_ID, a.ADVOCATE_CODE from Advocate a where a.USER_ID=37 ) as FILTER1,supprog sp Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code It gets the same plan: PLAN JOIN (SP NATURAL, FILTER1 A INDEX (ADVOCATE_)). But it has another problem, too: ultimately I'm wanting to use this as part of a view, so I can't do my filtering inside the query. Why isn't Firebird using the index? Do the index statistics have anything to do with it? Are the statistcs up to date ? The two tables you mentioned has the same amount of records ? The statistics are quite diferent, so, or the number of rows are bery diferent or the index statistics are out of date. Try this one: select sp.STUDENTSEQ, a.User_ID from supprog sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code+0 where a.USER_ID=37 see you !
Re: Odp: [firebird-support] Request for advice
Em 25/4/2014 01:31, liviusliv...@poczta.onet.pl escreveu: Hi, But you also misunderstud advice. Blobs are not stored in table with its other fields. In record exists only blob id and blob is stored on separate pages. Then moving it to separate table do nothing. But i always prefere store big data like document scans outside of database and in main database have only some hint about file name without final location. Only application have knowlage about final location and can load document Regards, Karol Bieniaszewski Nope... If the blob is small enough to fit on the data page with the records it will be stored there. I don't remember the rule exactly, but it is how it works. It reduces dramatically the number of records per page, once the most of the page are used to store the blob content. see you !
Re: [firebird-support] Request for advice
Em 24/4/2014 17:08, Mark Rotteveel escreveu: On 24-4-2014 20:49, Frank Schlottmann-Gödde wrote: As a compromise, I would suggest to place your blobs into a separate table, it will increase the size of your main database, but will also speed up the acccess to your blobs without interfering with everyday database operations. As most blobs are stored out of band, I would expect that moving them to a separate table this doesn't have a big impact unless you have the habit of always retrieving and loading all fields of a table. Mark If the blob's are small enough to fit into the data page, it will dramatically reduce the number of records per page, and ever if you don't select it you will read the whole page, where most part of it is the blob content. Moving the blob to a separate table would reduce this. see you !
Re: [firebird-support] procedure that calls a procedure
Em 11/4/2014 13:59, artmcc...@yahoo.com escreveu: I have a procedure that calls several other procedures. I have changed one of the embedded procedures and can run that independently and get the results I expect, I was able to compile it and it works.] My question is do I need to compile the parent procedure again, or should it just pick up the new version of the prod. And if I am calling this with an app that is connected, do I need to stop that app and restart it, or will that use the new version? Art as far as I recall, the other conections will still use the old version, you need to disconnect and reconnect so it can see the changes see you !
Re: [firebird-support] Optimizing this select Query
Em 10/4/2014 19:14, Marius Labuschagne escreveu: SELECT Sum(SALEITEMS.QUANTITY), Sum(SALEITEMS.QUANTITY * SALEITEMS.COSTPRICEEX) FROM SALES SALES INNER JOIN SALEITEMS SALEITEMS ON SALES.LINECODE = SALEITEMS.LINECODE WHERE SALES.POSTSTATUS = 'Posted' AND SALES.SALE_DATE BETWEEN :vStartDate AND :vEndDate AND SALEITEMS.SKU = :vSKU AND SALES.CASHCREDIT = 'Cash' The first thing... Are the indices statistics up to date ? If so... You could try this one: SELECT Sum(SALEITEMS.QUANTITY), Sum(SALEITEMS.QUANTITY * SALEITEMS.COSTPRICEEX) FROM SALES SALES INNER JOIN SALEITEMS SALEITEMS ON SALES.LINECODE = SALEITEMS.LINECODE WHERE SALES.POSTSTATUS = 'Posted' AND SALES.SALE_DATE BETWEEN :vStartDate AND :vEndDate AND SALEITEMS.SKU+0 = :vSKU AND SALES.CASHCREDIT = 'Cash' and see if it uses the Date index. see you !
Re: [firebird-support] Optimizing this select Query
Em 10/4/2014 19:42, Thomas Beckmann escreveu: Hm, this is just getting rid of using the index on sku - depending on it's selectivity, this might be a way, but than, the index might be of no use... SALEITEMS.SKU+0 = :vSKU AND You might check combined indexes... It's avoiding to use the index in this particular query, it does not mean it has no use... In a diferent query it could be a good index. And I think this index is generated by a FK constraint, and could not be removed. How he can combine the index of two distinct tables ? To the OP: If you change the query to use fixed values instead of parameters does it change anything ? I cant remember if the FB optimizer take in count the range of the between, I don't think so, but you could give it a try. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Reading unnecessary records
Em 4/4/2014 09:31, fabianoas...@gmail.com escreveu: I agree with Tim. Whe dont uwe views too becase this speed problem. Em 04/04/2014 08:51, Tim Ward t...@telensa.com mailto:t...@telensa.com escreveu: We generate the queries on the fly rather than trying to use a view, precisely because of these problems. So if CityName is not required in the output, there's no JOIN to City. And if there's something in the WHERE clause such as HobbyCode 27 then we know that HobbyID can't be null, which means we can use a JOIN instead of a LEFT JOIN, and quite often that results in a better plan. But yes, it does involve hundreds of lines of quite complex code to analyse what information is required in a particular case and generate the appropriate query, and in many cases the query optimiser could, theoretically, have worked this out for itself, but it doesn't. On 04/04/2014 12:36, kokok_ko...@yahoo.es mailto:kokok_ko...@yahoo.es wrote: I use the latest version of FB 2.5. There is a view for called PERSON. Each row represents a person and it shows information as address, name, hobbies, etc. There are 20 joined codification tables using LEFT JOIN. For example all cities are codified, hobbies, etc. The structure of the view is something like CREATE VIEW PersonView .. SELECT * FROM PersonTable P LEFT JOIN City ON City.ID = P.CityID LEFT JOIN Hobby ON Hobby.ID = P.HobbyID ... and so on for the 20 codified tables. City.ID is a primary key, like all IDs for remaining codifications. How can I optimize this view? My problem is that Firebird uses a really slow plan because it reads ALL codification records. For example, supposing PersonTable has 10 records. SELECT COUNT(*) FROM PersonView I would expect that Firebird only read 10 record, however it reads 10 + 10x20 = 210 records. In theory the optimizer could know that City.ID and Hobby.ID are both primary keys (or unique) and therefore only scan PersonTable. Another example: SELECT CityName FROM PersonView I woud expect that Firebird read 10 records for PersonTable and 10 for City table, but it reads 210. The real problem is that I have millions of records in the database, and a simple consult can take minutes when it could take few seconds with an optimized plan. What options do I have? Thank you -- Tim Ward I disagree... The problem does not lies on the view per se, but in the query... If you don't need a particular table information, having it on the view is useless... So if you have the need of a distinct rescult set only one view would not handle this. So the on the fly generated query is not better than the view because it's better optimzed than a view would be, but because it's a diferent query. Getting back to the original question: Do you really need OUTER JOINS ? Then you use all LEFT JOIN's the optimizer could just choose the order of the first to scan table between the tables that are on the left of an OUTER JOIN, in this case, there is no option, since there is just on table. If the Columns of the Foreign Keys could be null and in fact you really need the unrelated records, you really need the OUTER JOIN, and a query like this: select * from Person LEFT JOIN City on (City.CityID = Person.CityID) where City.Name = 'Paris' will do a full scan on table person and then use the PK index on City.CityID, where the ideal plan would be Use an index on City.Name and then do an index scan on the Foreign Key index of Person.CityID This is not possible (in the current version) since the optimizer should start with one of the tables on the left side of an OUTER JOIN Perhaps one day the optimizer could analyze this query and get to the conclusion that it could be better written as select * from Person JOIN City on (City.CityID = Person.CityID) where City.Name = 'Paris' So the problem is not on the view per se, but on the way the query is written. Give it a try, change your LEFT JOIN's to INNER JOIN's and se how it performs, be it a directly query or using a VIEW see you !
Re: [firebird-support] Row_Number () over (partition by field1 order by field 2 desc) as ROWNUMBER
Em 27/3/2014 09:39, Erik Drescher escreveu: Hey there, I use Firebird 2.5 and look after a alternative function ti use Row_Number () over (partition by field1 order by field 2 desc) as ROWNUMBER This works great on Firebird 3, but does anybody knows a good alternative? thanks erik You can try a Stored Procedure or a sub-select. A simples example, may not reflect what you need, but you will get the idea... select c.country, (select count(*) from country c2 where c2.country = c.country) from country c order by c.country
Re: [firebird-support] Backup
Em 7/3/2014 14:13, Art McCabe escreveu: I am trying to make a backup of our database that is on a Unix box, from my windows computer. I have used: Firebird Maestro, IP Firebird Database Manager, Firebird SQL Studio. I am not sure if I am doing something wrong, or just not possible. When I run the backup from these tools, and select the database and final location and press start, it looks like it is going thru a backup process, but the fbk file is not created on my computer. I am new to firebird and the GUI Tools that may be available. Any assistance is appreciated. Art I don't know any of these tools... But I strongly suggest that you use the native firebird applications use gbak for back-up (it's on the installation directory under the bin folder) the most simple way is: gbak IP_OF_THE_SERVER:NAME_OF_YOUR_DATABASE NAME_OF_BACKUP_FILE -user sysdba -password masterkey -v of course change the password for the one you defined. the -v switch is optional, but I think it will hep you once it will show the progress see you !
Re: [firebird-support] RE: very slow inserts with blob-fields
Em 6/3/2014 07:06, Bryan Cole escreveu: On Wed, 2014-03-05 at 22:40 -0800, ma_go...@yahoo.com wrote: Hi! Firebird do not like ext4 FS. You must chane some settings. http://www.firebirdnews.org/?p=6421 Thanks for the tip. Remounting my ext4 partition with barrier=0 increased the write speed from ~8 rows/sec to 450 rows/sec. Yeay. There are a bewildering array of linux filesystem options I could look at tuning further. Can anyone recommend a good combination (ext3 vs ext4,xfs,btrfs with options writeback-vs-ordered, barriers etc.) for a firebird database. I can afford to lose a few seconds of data in the event of a hard crash, but the filesystem/database as whole needs to remain consistent. Thanks, Bryan This is something I don't master and I really like to know about it... If someone has numbers or information about this I would love to know.
Re: [firebird-support] OT: Vote for Firebird as Database of the Year 2013 at LinuxQuestions
That site sucks. Last Year I registered to vote, I have tried a lot of things and find no way to vote, Then someone told me I need to make a post (oh ! a really smart way to avoid robots :) ), so I made a post, no way to vote, one told me I need to wait some time to refresh the data, other said that I need to log off/log on, etc, etc, etc. so I gave up. This year I entered the site, remembered that I need to make a post to vote, and just press the log off button. Am I lazy ? Perhaps... But I really dislike that site, will not vote there, period. And couldn't agree more that Firebird users lost his passion... Why ? I don't know... Em 5/2/2014 10:39, Carlos H. Cantu escreveu: Re: [firebird-support] OT: Vote for Firebird as Database of the Year 2013 at LinuxQuestions From my own experience with the number of votes in polls at firebidNews.org and firebase.com.br, I think people are lazy about voting. This LinuxQuestions poll is even worse in such aspect, since not everyone uses FB on Linux, and even when they use, most people don't wanna waste 5 minutes registering to a site they will not use, just to be able to vote por FB. I would say that in the past, Firebird users were more passionate about the product. Unfortunately, this seems to not be true anymore (and I could list some possible reasons for that). Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org http://www.firebirdnews.org- www.FireBase.com.br http://www.FireBase.com.br Hi All, I think more than 6000 people here should be more active in terms of Firebird visibility support - this poll received only 29 votes for Firebird. Also, it would be nice to have more testimonials from Firebirders: http://www.firebirdsql.org/en/testimonials/ (submit your testimonial in the end of the page - in any language). Regards, Alexey Kovyazin Vote for Firebird as Database of the Year 2013 at LinuxQuestions (*must be registered user with at least one Linux related post*) http://www.linuxquestions.org/questions/2013-linuxquestions-org-members-choice-awards-109/database-of-the-year-4175488206/ _
Re: [firebird-support] install without admin
Em 29/1/2014 13:38, Z T Minhas escreveu: Hello, I would like to know if there is a way to install firebird without the need for admin priveleges. for example i can run mysql from my flash drive, simply unzip and run. is there an install version for firebird? thank you for your assistance. regards zafar You can... Use the Embedded version. see you !
Re: [firebird-support] RE: Table update performance dropped significantly within few days.
Hi !
Em 16/1/2014 04:13, brucedickin...@wp.pl escreveu:
Hi guys.
Yesterday I was trying to figure out what is the cause of my problem.
And I've noticed that two months ago my colleague added one trigger on
this table. The trigger is:
SET TERM ^ ;
ALTER TRIGGER CLIENT_LM INACTIVE
AFTER INSERT OR UPDATE POSITION 1
AS
BEGIN
UPDATE OR INSERT INTO LAST_MODIFICATION (TABLE_NAME, RECORD_TIME)
VALUES ('CLIENT', NEW.RECORD_TIME)
MATCHING (TABLE_NAME);
END^
SET TERM ; ^
So, after each insert or update this trigger was saving last
modification time to a different table. On each table in the system
such trigger exists. After dissabling the trigger, performance get
back to normal.
Results for gstat are strange:
Database header page information:
Flags 0
Checksum 12345
Generation 228629442
Page size 16384
ODS version 11.2
Oldest transaction 219409373
Oldest active 219409374
Oldest snapshot 219409374
Next transaction227560937
Bumped transaction 1
Sequence number 0
Next attachment ID 1068492
Implementation ID 24
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date Aug 9, 2013 23:34:51
Attributes force write
Variable header data:
Sweep interval: 2
*END*
I see the big difference between oldest transaction and the current
transaction. Is it possible that this trigger is caused this? BTW my
colleague has reported to me that he had a number of deadlocks becuase
of this mentioned trigger.
Oldest transaction 219409373
Oldest active 219409374
Oldest snapshot 219409374
Next transaction227560937
Your oldest transaction is around 8 million transctions old, you have
around 1.5 milion transactions a day (22 million transactions in 150
days), so you have an open transaction for around 6 days.
The trigger is not causing the transaction lock, it's caused by an open
transaction that never gets closed.
The trigger could cause a lot of record versions (check with gstat
you_databse.fdb -r -t LAST_MODIFICATION -user sysdba -password
masterkey) because it updates the LAST_MODIFICATION table and since
there is a transaction that needs to see the old version, new versions
are created and never get garbage collected, because it's still
interesting for some transaction.
You should check wich application holds the oldest transaction open, and
fix it.
see you !
Re: [firebird-support] index ignored with parameterised 'Like' search?
Em 15/1/2014 10:13, jamesjlovel...@gmail.com escreveu: I know there are some nuances to ensuring an index is used with 'Like' searches. I would expect this search to use an appropriate index if one is available (hvalue_search has an ascending_index with selectivity recalculated). SELECT id, hname, hvalue, hvalue_search from entity_header where hvalue_search like 'GLX%' If I do a search in straight sql, the plan returns this: 'PLAN (ENTITY_HEADER INDEX (I_HEADER_HVALUESEARCH))' This is the plan used either using a tool like DBWorkbench, or using the python DB-API with the FDB driver. However, if I try to use a parameterised version of this query (using either the FDB driver or DB Workbench), then the index is ignored and the entire table is scanned. 'PLAN (ENTITY_HEADER NATURAL)' I assumed at first that this was a problem with the python driver or with the python db-api. So when I tested with DB Workbench I was surprised to see that it seems that firebird will do a full table scan if there is a parameterised query with like. Is this the normal behaviour? This is what I am using in DB Workbench to show that the introduction of a parameter causes the full table scan: SELECT id, hname, hvalue, hvalue_search from entity_header where hvalue_search like :1 I can't seem to run a paramterised query within the command line ISQL, so I can't determine if this behaviour is really a limitation of the tools I've been trying or of firebird itself. Yes, it's the normal behaviour... A parameterised query will be re-used with any value for the parameter, so the plan is choosen before the value of a given parameter will be set, since there is no way to predict if the parameter would be 'ABC%' or '%ABC%' or '_ABC' the optimizer chooses a plan that will solve all the possibilities, thereafter it can't use a plan that uses an index. You could use STARTING WITH, this way you will always use an index. see you !
Re: [firebird-support] Computed Index for Integer-Fields and STARTING WITH clause
Em 15/1/2014 10:37, Christian Kusenbach escreveu: Hi, I have a question about computed indices related to integer fields. In my program there are several selects on integer-fields with a STARTING WITH clause. Firebird internally converts the integer value to a string and then filters the value. It would be great if I could create a computed index on that (internal) string so FB does an index-lookup for the data. I tried to create an computed index on a table with the expression CAST(MY_INT_FIELD AS VARCHAR(11)) cause I think firebird internally uses a VARCHAR(11) or CHAR(11) value but that doesn't solve the problem. Any idea on how to get this working without changing every select in the program? Thanks and best regards! Christian I think you meant expression index instead of compound index The expression index will be used if the search criteria is the same as the expression index: your example: select * from MyTable where MyIntCollumn starting with '1' you created an expression index as Cast(MyIntColumn as varchar(11)) so, your query should be: select * from MyTable where Cast(MyIntCollumn as varchar(11)) starting with '1' So there is no way you could use the expression index without rewriting your query. I have no idea why you are doing this kind of select but it lookslike weird to me... And since you will need to rewrite the query, perhaps could be a good idea to write it in a better way (in the last case, use a mirror collumn updated by a after insert/update trigger with the int value formated as you wish to search for). see you !
Re: [firebird-support] Table update performance dropped significantly within few days.
Em 15/1/2014 16:30, brucedickin...@wp.pl escreveu: Hi guys. I have one table that currently has 100 000 000 records. PageSize is 16384. Index statistics for primary key are 0.00 (I've recomputed them). Over a few days inserting and updating this table slowed down significantly. When I try to update one row: UPDATE TABLE SET VALUE=X WHERE PK_COLUMN_ID =12335343 update executes in 0.250 miliseconds. What is surprising for me that just few days ago everything was fine.. how could I speed thing up? What could happen over these few days? Thanks in advance. It's hard to tell without more information. But the first thing I would look out is the transaction counters. Perhaps you have some open transaction that is preventing the garbage collection process and acumulating record versions. use GSTAT -h to get the header information of your database and post it back. see you !
Re: [firebird-support] nbackup questions
Em 15/1/2014 17:30, Maury Markowitz escreveu: On 2014-01-15, at 2:02 PM, Hugo Eyng wrote: I am not sure if i understood your question. You want to move the fdb file to another computer? That is correct. For reasons that I do not understand, the simple method of simply copying the FDB from one machine to another does not work. When I try to attach to that database, it reports file not found, which I assume is a spurious error code. But there already is a small (example) database file on that machine that I *can* attach to. I can also restore into it. So if I can get nbackup to work, that provides a way to move the data over. I think it's related to not finding the delta file you mentioned on another post. For some reason the delta file is not merging back to the main file. Take a look on nbackup (http://www.firebirdsql.org/manual/nbackup.html) manual so you could try to merge it back manually. see you !
Re: [firebird-support] Table update performance dropped significantly within few days.
Em 15/1/2014 17:36, fabianoas...@gmail.com escreveu: We had this same issue. We have a large table with much indices and some day all goes slow. Reducing index quantity helped a lot. We was using 90 pages of cache in Classic. We increased this number to 150, shutdown all conections and thats it! Problem solved. Latter we increased this number to 2048 as Superserver default. Our big client has 140 active simultaneous connections and no problem. Of course we calculate the necessarie ram. Maybe the problem was solved when you detached all connections because it closes the an old open transaction :) see you !
Re: [firebird-support] RE: Table update performance dropped significantly within few days.
Em 15/1/2014 17:52, brucedickin...@wp.pl escreveu: Regarding the number of pages. Correct me if I am wrong (http://www.firebirdsql.org/manual/gfix-buffers.html) but if Superserver is using 2048 pages and page size is 16384, than computer must have 32 GB RAM installed. What is happening when there is less RAM installed? 32 MB ! :) Page size is defined in bytes
Re: [firebird-support] Table update performance dropped significantly within few days.
Em 15/1/2014 18:01, fabianoas...@gmail.com escreveu: No. Only when increased cache. Shutdown all conections because when they reconnect will use the new cache limit. I have my doubts... I still think it's more related to a long running transaction then to the increase on the page buffers... see you !
Re: [firebird-support] Restore Data
Em 14/11/2013 05:01, liviuslivius escreveu: W dniu 2013-11-13 19:51:19 użytkownik Marco Sucuzhañay msucuzhanay.act...@gmail.com napisał: Dear as I can only restore Data ln a database empty, thanks Hi, sorry but what is the question? Describe it much better regards, Karol Bieniaszewski I think he wants a data pump. He wants to copy the data from a database to another one. Google for fbcopy, fbexport and ibdatapump see you !
Re: [firebird-support] RE: Insert into Large Table is slowly!
Em 5/9/2013 13:18, jym...@gmail.com escreveu: Thanks, I will try use GLOBAL TEMPORARY and INACTIVE index. The insert time will be faster without the index, but you will spend a lot of time when you reactivate it... And any query that will run during the insert time will not use the indice, so, it will be very slow to do a full table scan in a multi million record table Take into account that you will reactivate the index 24 times a day
Re: [firebird-support] Today's performance question - index direction
Em 4/9/2013 13:02, Tim Ward escreveu: Ah, yes, I did write one of those once - a 1980s spelling dictionary for a word processor, where being able to encode an entire word in the smallest possible number of five-bit codes was *much* more important than being able to read the list backwards. (I think we ended up with less than 3 bytes per word for an English dictionary?) I'm slightly surprised that it's thought to matter with this century's disk prices however. -- Tim Ward AFAIK it's not about disk price, but to make it more dense, so more index entries could fit in a single page, leading to fewer page reads and so improving speed. see you !
Re: [firebird-support] Classic vs Superserver
Em 29/8/2013 12:17, Tim Ward escreveu: But I thought Superserver used threads? And threads can run on separate CPUs? (Processes are an address space thing, not a CPU thing.) There are threads, but in fact they are serialized, perhaps it's just an over simplification, I don't know FB internals, but the threads does not run in parallel (FB 3.0 will fix that). If you have a multi core server (wich is an obvious thing theses days) you should prefer Classic Server, the only case where I think SuperServer will be a choice is when you have just one connection per database. Perhaps you have automatic sweep disabled (check with gstat -h), if you have sweep disabled the garbage will acumulate, so when a query need to scan the table it will pay the cost to clean it up. I am not saying to use automatic sweep, since it could trigger in the middle of the day generating an unknown slowdown... What I suggest is that you keep automatic sweep disabled and run a manual sweep (gfix -sweep) during off peak hours. I will not worry about the shared cache, the file system cache will do almost the same as the shared cacee in SS will do, you will have a small memory overhead because of the separed caches for each connection, even if will set default cache to 1000 you will have 8MB or 16MB of cache duplication per connection, not that much... see you !
Re: [firebird-support] Re: gbak trigger(3) error
Hi ! Em 9/8/2013 12:10, jack.mason58 escreveu: Where do I find fbstat? It is not included in the 2.5.2 download I pulled yesterday from firebirdsql.com. It is not in the bin directory nor the top directory. It's GSTAT It's a long long time that I used Interbase... But I think you should use gbak from Interbase to produce the back-up and restore it using gbak from firebird. I don't know if the back-up produced by IB 6 gbak will be compatible with FB 2.5 gbak. when I migrate from IB 6 it was to FB 0.9... If FB 2.5 gbak could not understand IB 6 gbak back-up, you could try a two step migration IB 6 - FB 1 - FB 2.5 or a 3 step migration IB 6 - FB 1 - FB 1.5 - FB 2.5 As far as I know gbak will be retro compatible, but IB 6 is very old version, don't know how long the retro compatibility is preserved. Another option is to extract SQL Metadata using IB isql (-X option), then create a new FB 2.5 database, run the generated script, and pump the data from the IB server to FB server. see you !
Re: [firebird-support] Re: Historic tables design
Em 31/7/2013 21:38, Iwan Cahyadi Sugeng escreveu: I plan to check the historical table on system start and update the metadata. I'm using n-tier solution, so my server application will do the metadata update I don't know your logic and perhaps this comment doesn't apply to your case, but anyway... I think it's better to check the metadata and stop/report about some misleading table information, it's not recommended to update metadata with the database in use. see you !
Re: [firebird-support] Query optimization help
Hi ! Em 25/7/2013 07:54, fabianoas...@gmail.com escreveu: Just remove any view you use in your statement and try again. Fb is very slow when resolving queryes that use views joining other tables Can you give a real life example of that I had never find such a situation... see you !
Re: [firebird-support] Query optimization help
Em 25/7/2013 10:53, Kevin Donn escreveu: On Thu, Jul 25, 2013 at 8:03 AM, Alexandre Benson Smith ibl...@thorsoftware.com.br wrote: Em 25/7/2013 07:54, fabianoas...@gmail.com escreveu: Just remove any view you use in your statement and try again. Fb is very slow when resolving queryes that use views joining other tables Can you give a real life example of that Alexandre, if my situation qualifies as a real life example I'd be happy to send you a development database to look at. Contact me directly. kd The fact that your query is slow using views doent mean that it will be fast without the views It could be possible that your query will be fastar if you use direct tables, but if you just translate the views into a single select, I don't think it will be any faster I completely rewrite of it using tables is another thing see you !
Re: [firebird-support] Re: NBackup problem, causing deadlock
Em 17/7/2013 16:38, matthiasbreda escreveu: In the log file of the firebird, a second before stopping all users, have this line: SRVHYPERV2Tue Jul 16 08:40:11 2013 Database: D:\WORK\DATA.FDB deadlock internal Firebird consistency check (Can't lock alloc table for reading) Thereafter, the system uses the database stopped on all machines and process nBackup just stood, had to terminate the processes, start the firebird and merge the delta file. Sorry again about my english. The first thing I would try is to recreate this database, perhaps it has some kind of corruption. use gbak to create a back-up and restore it to *another* place, so you could be sure the back-up is not corrupted. If you could restore the back-up, replace the production database with the freshly restored one, and look if nbackup has problems after that. Of course.. with everyone disconnected from the database... see you !
[firebird-support] RSB$USER_PRIVILEGES vs RDB$SECURITY_CLASS
Hi ! Can someone explain me what's the diference between these two tables ? I did the following test: create table foo( a integer, b varchar(10)); grant select on foo to public; grant insert on foo to Alexandre; grant update (a) on foo to Pedro; grant delete on foo to Maria; commit; First let's take a look on RDB$USER_PRIVILEGES: select * from rdb$USER_PRIVILEGES where RDB$RELATION_NAME = 'FOO'; RDB$USER RDB$GRANTOR RDB$PRIVILEGE RDB$GRANT_OPTION RDB$RELATION_NAME RDB$FIELD_NAME RDB$USER_TYPE RDB$OBJECT_TYPE SYSDBA SYSDBAS 1 FOO [null] 80 SYSDBA SYSDBAI 1 FOO [null] 80 SYSDBA SYSDBAU 1 FOO [null] 80 SYSDBA SYSDBAD 1 FOO [null] 80 SYSDBA SYSDBAR 1 FOO [null] 80 PUBLIC SYSDBAS 0 FOO [null] 80 ALEXANDRE SYSDBAI 0 FOO [null] 80 PEDRO SYSDBAU 0 FOO A80 MARIA SYSDBAD 0 FOO [null] 80 well... From the above I can see that: SYSDBA has (S)elect, (I)nsert, (U)pdate, (D)elete and (R)eference privileges, all with GRANT OPTION PUBLIC has (S)elect only no GRANT OPTION ALEXANDRE has (S)elect (because of public) and (I)nsert without GRANT OPTION PEDRO has (S)elect (because of public) and (U)pdate on column A without GRANT OPTION MARIA has (S)elect (because of public) and (D)elete without GRANT OPTION All the privileges granted above is represented completely in this table Now let's see what is on RDB$SECURITY_CLASS, but first we need to now what secutiry class is applied to each object: select RDB$RELATION_NAME, RDB$SECURITY_CLASS, RDB$DEFAULT_CLASS from RDB$RELATIONS where RDB$RELATION_NAME = 'FOO'; RDB$RELATION_NAME RDB$SECURITY_CLASSRDB$DEFAULT_CLASS FOO SQL$8 SQL$DEFAULT5 and select RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$SECURITY_CLASS from RDB$RELATION_FIELDS where RDB$RELATION_NAME = 'FOO'; RDB$RELATION_NAMERDB$FIELD_NAME RDB$SECURITY_CLASS FOO A SQL$GRANT9 FOO B [null] So we need to check SQL$8, SQL$DEFAULT5 and SQL$GRANT9 select RDB$SECURITY_CLASS, cast(RDB$ACL as varchar(2000)) from RDB$SECURITY_CLASSES; (formatted for a better reading) RDB$SECURITY_CLASS RDB$ACL SQL$8ACL version 1 person: SYSDBA, privileges: (PCDWR) person: ALEXANDRE, privileges: (IR) person: MARIA, privileges: (ER) person: PEDRO, privileges: (UR) all users: (*.*), privileges: (R) SQL$DEFAULT5 ACL version 1 person: SYSDBA, privileges: (PCDWR) person: ALEXANDRE, privileges: (IR) person: MARIA, privileges: (ER) all users: (*.*), privileges: (R) SQL$GRANT9 ACL version 1 person: SYSDBA, privileges: (PCDWR) person: ALEXANDRE, privileges: (IR) person: MARIA, privileges: (ER) person: PEDRO, privileges: (UR) all users: (*.*), privileges: (R) I don't know why SQL$DEFAULT5 misses person: PEDRO, privileges: (UR) that is on SQL$8 that refers to the table too, perhaps this indicates that UPDATE has a special record on RDB$RELATION_FIELDS.RDB$SECURITY_CLASS Let me try to interpret that data: all users (Public) can (R)read ALEXANDRE can (I)nsert and (R)ead MARIA can (E)rase and (R)read PEDRO can (U)pdate (column A, that is the field that has SQL$GRANT9) and (R)ead SYSDBA has P, C, D, W and R privileges, wich I don't know the meaning, but of course it is administrative/owner rights Besides SQL$8 lists person: PEDRO, privileges: (UR) a further check must be in place to see if he can alter each field... Trying to put the information above on the same terms, I get: From RDB$USER_PRIVILEGES: SYSDBA has (S)elect, (I)nsert, (U)pdate, (D)elete and (R)eference privileges, all with GRANT OPTION PUBLIC has (S)elect only no GRANT OPTION ALEXANDRE has (S)elect (because of public) and (I)nsert without GRANT OPTION PEDRO has (S)elect (because of public) and (U)pdate on column A without GRANT OPTION MARIA has (S)elect (because of public) and (D)elete without GRANT OPTION From RDB$SECURITY_CLASSES: SYSDBA ??? PUBLIC has (S)elect only ALEXANDRE has (S)elect and (I)nsert PEDRO has (S)elect (U)pdate on column A MARIA has (S)elect(D)elete -- No information about
Re: [firebird-support] Re: RSB$USER_PRIVILEGES vs RDB$SECURITY_CLASS
Em 18/7/2013 00:20, Dmitry Yemanov escreveu: 18.07.2013 6:02, Alexandre Benson Smith wrote: Can someone give some info about the role of each table ? As far as I can see RDB$USER_PRIVILEGES has all the information needed and RDB$SECURITY_CLASS dos not have all the information (misses GRANT OPTION) but have some info for SYSDBA that I don't know the meaning... RDB$USER_PRIVILEGES is kinda public interface for RDB$SECURITY_CLASSES, the latter is mostly a lower level representation of the former. RDB$SECURITY_CLASSES defines ACLs actually used by the engine to validate permissions. It includes not only SQL permissions but also some special ones like control, protect and delete that are assigned to the object owner. RDB$USER_PROCEDURES is used only when we need to grant something or show/export the grants, as it lists only grantable permissions and includes the grant option. Dmitry Thank you Dmitry ! I think you meant RDB$USER_PRIVILEGES where you wrote RDB$USER_PROCEDURES, right ? see you !
Re: [firebird-support] singleton table with many fields
Em 16/7/2013 00:03, thp_pkmi escreveu: Hi all, I have a table that always contains 1 record and have about 50 fields with various type. It's function is to store system parameters and is accessed very frequent from application and also from database triggers and stored procedures. Is it wise to utilize a singleton table like this, or is it better to implement a table with many records storing parameter name, type, and values ? Thank you very much. Regards, Tjioe I doubt it will make any diference... I think you will have a dozen or so records.. that will fit in a single page... Since every operation will read/write a page... see you !
Re: [firebird-support] Re: Making a Value Matrix - Delphi Array or Create Query
Em 15/7/2013 21:55, Craig Cox escreveu: I will use them in a GIS application or for charting to plot one compound against another, or to show pie charts in the GIS app I prefer to create the cross tab on the client side... I think I have much more control over it... see you !
Re: [firebird-support] Re: singleton table with many fields
Em 16/7/2013 01:37, thp_pkmi escreveu: Thank you Alexandre for your reply. Furthermore, is it any difference accessing that singleton table by: SELECT .. INTO .. FROM SINGLETON_TABLE or SELECT .. INTO .. FROM SINGLETON_TABLE ROWS 1 Regards, Tjioe There is no way a query in a table with a single row to be slow :) Put it in a loop for 1000 times and look for the diference... see you !
Re: [firebird-support] Firbird slow performance at windows server 2012
Em 8/7/2013 05:51, Svein Erling Tysvær escreveu: Dear All, I have application with delphi and firebird database, there is no problems until migrate the server to windows server 2012. The query (select, insert, update etc) running to slow. I searching and found the solution example : change filesystemcache,tempbocksize,defaultdbcachepages at firebird.conf but the query still running to slow. There are another way? Please tell us more, Chandra, even though I know nothing about Windows vs Firebird, I know that a detailed and specific problem description is more likely to result in good answers than general questions. You say that you migrate to Windows server 2012, what do you migrate from? Is everything except the server identical on the fast and slow server - e.g. indexes and plans for all problematic queries? The same number of users and the same content in the databases? What about database statistics? If there are specific queries running slow, you could post them here together with the generated plans and some information about the fields in the indexes being used. Set Besides that. Wich FB version ? Newer windows versions (64bit) has changed th way to manage filesystem cache and it became a problem... Read more here: http://dyemanov.blogspot.com.br/2012/03/firebird-vs-windows-file-system-caching.html see you !
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 !
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 !
Re: [firebird-support] Why a database grows after a cycle backup/restore?
Em 18/6/2013 19:44, W O escreveu: The source database has 3.1 Gb but after the backup/restore have 4.2 Gb Seems very strange for me. Greetings. Walter. Just a shot in the dark... Could be that the original database was restored with -USE_ALL_SPACE and the new database was not be restored with the same option ? see you !
Re: [firebird-support] Help on this SQL statment
Em 15/6/2013 16:54, Iwan Cahyadi Sugeng escreveu: Change it into this syntax: SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTODE, PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 The error will be corrected by the addition of ProductCode to the GROUP BY clause, as the others have said, but I think the ORDER clause should be: SQLCommand = SQLCommand ORDER BY 3 DESC That way the result would be order by the Sum of ProductQty see you !
Re: [firebird-support] Fluxo do software
Em 14/6/2013 08:22, Fabiano Kureck escreveu: Tenho algumas dúvidas quanto ao fluxo dos softwares: A princípio se usa um programa CAD (AutoCad, Solidworks, etc) para gerar o arquivo 3D .stl certo? Após isso se passa para no netfabb para que o arquivo stl seja preparado/compatível com uma impressão 3D. Então, passa-se pelo Slickr (algo assim) que pega o arquivo STL e gera um GCODE. Nesse GCODE existem as fatias de impressão que contém a espessura de cada uma, temperatura do hot end para cada fatia, velocidade do cooler e etc. É isso? Por fim, o Slickr manda o GCODE para o repetier host que é instalado no PC, que passa pelo cabo usb/rs232 para o Repetier (marlin, etc) na placa Gen7BR/RAMPS/Sethi3d para que o Repetier movimente os motores de passo. Estou certo em meu raciocínio? Two things This is an internation list, so you should write in English, this list is about Firebird (RBDMS) and has no relation with your doubts regarding CAD/CAM systems. Perhaps you post to the wrong list. Good luck ! Duas coisas... Esta é uma lista internacional e deve postar em ingles aqui.. outra coisa, esta lista é sobre Firebird, um gerenciador de banco de dados open source e não tem relação alguma com a tua duvida que é sobre CAD/CAM. Deve ter postado em lista errada... boa sorte !
Re: [firebird-support] 32bit Clients accessing 64bit Server
Em 10/6/2013 11:30, Sudheer Palaparambil escreveu: Hi, Is it okay to access a 64bit Firebird Server (FirebirdCS-2.5.2 amd64) database installed on Scientific Linux 6.3 from Clients Win7 and WinXP 32bit (Firebird-2.5.2 Win32)? The client softwares are using IBPP library to access the server database. Regards. Sudheer Palaparambil Yes
Re: [firebird-support] Re: Rename database command
Em 5/6/2013 20:01, sir_wally_lewis escreveu: Hi Sean, While I don't pretend to understand Firebird at the atomic level. I am just trying to cope with database slowdowns. We find the only bullet proof methodology to solve database slowdowns is a backup restore. So we are searching for a method to be able to resolve database slowdowns, while keeping the database online. I am not concerned with whether theoretically firebird should or should not require a backup/restore. It seems that in practice under large database conditions to be a requirement. Our networks guy is going to spend some time seeing if he can give evidence of this requirement. Of course we will try any method to attempt to resolve this. In the past we have not found sweeping the database to help, but we will continue to do everything we can to resolve this for our customers sake. Kind Regards, Robert Robert, Maybe you (or the person responsible for the application code) needs to understand the Firebird principles (MGA) this way you could understand why the system become slow and fix the reason. I don't think this is understand Firebird at atomic level, but to understand why some coding mistakes could lead to a system slow down and how to fix it. Look at the cause not at the symptom... If you manage your transactions properly I doubt you will need a back-up/restore cycle every week to keep the system responsive. You said that sweep did not fix the problem, but in what circustances was the sweep run ? If your application keeps a transaction open for a long time (what I think is the case) running sweep could not help that much, but if you run sweep when no one is connected I think the result will be the same (as I said previously the restore process will do more than a sweep, but I think it's not necessary) as running a back-up/restore cycle. The transaction counter is a 32bit integer and when will get close to this limit you will need to perform a restore to reset it (as the restore creates a new database, the transaction counter will start from 0 again). To remove garbage and move forward the OIT the sweep is all you need, as long when you run sweep there is no transaction open for hours. see you !
Re: [firebird-support] Many Items in Many Locations
Em 7/6/2013 01:37, jwcane2003 escreveu: I am building an image database and want to know locations of all copies of images. An image can be displayed in one or more locations: a web page, a show, a book, a commercial gallery, someone's residence, etc. Any of these locations can have multiple images. How is this best tracked, recorded and returned as a query result? One solution is to create a table called ImageLocs, having foreign key references to each image and to each image's location. I cannot get past the idea of a separate table for each location type: gallery, show, book, magazine, web page, residence (the latter with a FK link to the contacts table), etc. Each location table contains a location name (show or book title, etc) and foreign keys to the image table and to the location table(s). But then, ImageLocs will have lots of blank, foreign key cells, because only one or very few locations will contain any given image. For example, if I publish a book with 100 images, ImageLocs will have records containing that book's primary key (PK) 100 times, alongside the PKs of each of its images, etc. Seems redundant, or is this the standard practice? Or is there a better way? Hi ! Perhaps I misunderstand your message, but I will answer as I understood it. I think you could have something like: Images (Table) ImageID integer (PK) Image BLOB Name varchar Locations (Table) LocationID integer (PK) Description varchar ImageLocation (Table) ImageLocationID integer (PK) ImageID integer (FK to Images) LocationID integer (FK to Locations) AdditionalInfo varchar If you want to show all the locations you have an image you will need a query like this: select I.ImageID, I.Name, L.Description, IL.AdditionalInfo from Images I join ImageLocations IL on (IL.ImageID = I.ImageID) join Locations L on (L.LocationID = IL.LocationID) where I.ImageID = ? If you want to show all the images for a desired location you will need a query like this: select I.ImageID, I.Name, L.Description, IL.AdditionalInfo from Images I join ImageLocations IL on (IL.ImageID = I.ImageID) join Locations L on (L.LocationID = IL.LocationID) where L.LocationID = ? It's a classic many to many relationship. Did I missed something ? Of course you could have additional colluns like Type to indicate if it is a Book, a WebSite and so on. It could be a typed column (Integer) or you could have another table. I would go with another table... see you !
Re: [firebird-support] Re: Rename database command
Em 5/6/2013 17:34, Marcus Bajohr escreveu: From my little knowlegde i would guess Robert is talking about the delta between the oldest active transaction and next transaction. But as long as i do not know anything about the application (event listeners, long time transactions and that) and the environment (Number of active connections and that) Robert writes about (may be he has in an earlier post) , it is just peeking around. just my EUR 0,02, Marcus yep... that *would* cause slowndown... but back-up/restore is not needed to fix it. In fact.. the fix is in the application (transaction control), and if the application could not be fixed a simple SWEEP (gfix -sweep) would fix that if run when no one is connected (I assume that it is possible since he could back-up/restore) see you !
Re: [firebird-support] Re: In memory sorting consumes alot of space
Em 17/5/2013 15:33, haris_p_sw escreveu: --- In firebird-support@yahoogroups.com, Svein Erling Tysv�r svein.erling.tysvaer@... wrote: Hi, I 'd like to share my recent testing experiences with you hoping to help in Firebird development. I 've been using Firebird (classic server 70 connections for web serving) for 13 years now. Recently, I decided to test certain suspicious select queries more carefully. Here are my conclusions and I hope you prove me wrong: 1. If the query plan uses indexing, everything works OK and very fast. 2. If the query plan goes the NATURAL way then sorting happens which is totally expected. However, if the tuning variable TempCacheLimit has a value that is not big enough for the dataset the sorting process happens on hard disk and is extremely slow. it depends on the machine and the dataset but it has to be a very powerful machine to manage it under 10 secs. On my new 64-bit laptop, Firebird 2.5.2 for a dataset around 30-40 MB I got 35-40 seconds. The NATURAL plan was unavoidable since there was a need of ordering by two columns from two different tables. I started tuning TempCacheLimit, LockMemSize, DefaultDbCachePages and FileSystemCacheThreshold. I discovered that caching in memory does not speed up things so I gave FileSystemCacheThreshold a big value. I managed to get an execution time around 2 seconds(4 for the first run) when TempCacheLimit was: 536870912(classic super server. With plain classic server you need more memory). I know that the above value is only a limit. The problem is that during query execution I noticed that 200 up to 480 MB were actually occupied(depending on the run). I don't know if things are better with Super Server. It is not an option for me because I am using FreeBSD. As a conclusion, I was disappointed when I tested postgres 9.2 with the same query. It finishes under a second without using extra memory. It is obvious that postgres also does extra work for my query but in a much more efficient way. Why Firebird sorts datasets loading whole rows into memory when the plan is NATURAL? Couldn't it sort only primary keys for example? There might be something I 'm missing... Hi Haris! I've no clue about your actual question, but am curious as to what your query looks like and what size of tables and result sets we're talking about. Going NATURAL on the first table in a plan is quite normal and usually not too time consuming unless we talk about large tables (though I've no experience with Classic...), but NATURAL on other tables in the plan can be quite ugly performancewise. And Firebird can (or at least could) be very slow if you issue the wrong kind of query against it (e.g. ... WHERE somefield IN (SELECT DISTINCT AnotherField ... wasn't recommended). Set Hi Set, Thanks for your reply. Here is my query which is a join between three tables. prdInCatTab holds the keys to product and prdCategory and it's a one-to-many relationship: SELECT FIRST 10 SKIP 500 * FROM product JOIN prdInCatTab ON prdInCatTab.PINCPrdId = product.prdId JOIN prdCategory ON prdCategory.catId = prdInCatTab.PINCCatId WHERE prdCategory.catShowFlag = '1' AND product.prdHideFlag != '1' AND prdInCatTab.PINCPrimary = '1' ORDER BY prdInCatTab.PINCRank DESC, product.nameUp ASC; And here is the plan: PLAN SORT (JOIN (PRDINCATTAB NATURAL, PRDCATEGORY INDEX (PK_PRDCATEGORY), PRODUCT INDEX (PK_PRODUCT))) Well, my actual question could be: Why does Firebird consume such a large amount of sorting space? It is almost impossible for a web server which has over 100 connections to allocate such amounts of memory space. As I wrote in my first post here are my numbers: 1. Table product's size is between 30 and 40 MB. The other two have small size. 2. TempCacheLimit = 536870912 with Classic Super Server if you want to have an acceptable run(4-5 secs first run on my laptop, around 2 secs all next runs). 3. Firebird server does actually need from 200 to 450 MB for each run. Although my production servers are much faster than my laptop, I replaced the above query with another that uses a subquery as a virtual table. It turned out very fast and not memory consuming: SELECT * FROM product JOIN prdInCatTab ON prdInCatTab.PINCPrdId = product.prdId JOIN prdCategory ON prdCategory.catId = prdInCatTab.PINCCatId JOIN ( SELECT FIRST 10 SKIP 500 product.prdId as bprdid FROM product JOIN prdInCatTab ON prdInCatTab.PINCPrdId = product.prdId JOIN prdCategory ON prdCategory.catId = prdInCatTab.PINCCatId WHERE catShowFlag = '1' AND product.prdHideFlag != '1' AND prdInCatTab.PINCPrimary = '1' ORDER BY PINCRank DESC, product.nameUp ASC ) AS b ON product.prdid = b.bprdid AND prdInCatTab.PINCPrimary = '1' and the plan: PLAN JOIN (JOIN (SORT (JOIN (B PRDINCATTAB NATURAL, B PRDCATEGORY INDEX (PK_PRDCATEGORY), B PRODUCT INDEX
Re: [firebird-support] Re: Overflow during backup. Apparent date problem.
Em 17/5/2013 18:24, Ted Miglautsch escreveu: I frequently make changes to system tables and started using Interbase and then Firebird because I could do that. Mostly I extent the size of char and varchar fields. It just requires knowing what you are doing. You have to consider whether the change you are making could cause the problem that you encountered. The correct way of doing this is using a DDL command ALTER TABLE In future versions of FB directly system table updates will be denied. see you !
Re: [firebird-support] Re: Unable to INSERT INTO TABLE
Em 15/5/2013 16:35, Allstars escreveu:
Ok sorry, now I am getting a different error. When I do the insert,
I'm getting a
Error: arithmetic exception, numeric overflow, or string truncation
string right truncation
What does that mean? Thanks!
If I read you example correctly you are trying to insert an empty string
('') into a int column.
see you !
Re: [firebird-support] Temporarily stop and disable garbage collection
Em 25/4/2013 21:58, Robert martin escreveu: Is this possible? http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/gfix-cmdline.html Sure. see parameter: -housekeeping set sweep interval n Hi I was probably not specific enough in my problem description :) I know that I can disable garbage collection via the cmd line. What I am looking for is the ability to... 1. Cancel any existing garbage collection 2. Disable garbage collection 3. Do my stuff 4. Re enable garbage collection. I know I can to 2 and 4 via gFix but item 1 is the issue. Also I would want to use the services manager rather than command line tools. Thanks Rob GFIX will disable sweep. Garbage Collection and Sweep is not the same thing. There is a lot of detailed explanations about it, search for Ann messages and you will see the diference. Besides the diference between Garbage Collection and Sweep, there are diferent modes of Garbage Collection in SuperServer x Classic Server. AFAIR there is a parameter that you could use in your transaction (TPB parameter) that will disable garbage collection, this is the trick that gbak does when using the -g swiitch see you !
Re: [firebird-support] Another Unoptimized question
Em 16/4/2013 11:09, skander_sp escreveu: May be obvious, but til now, i don't see how much unoptimized works the field in subselect syntax. I'm using it in some procedure, just right til now, after check they work in the worse unoptimized mode... example: select * from Table1 T1 where T1.id in (select T2.id from Table2 T2 where T2.Customer=:customer) of course T2.id is a Primary key and T1.id is a Foreing key I think they resolve getting the value(s) in the subselect, and then applying to the main where, but the performace analisys show he run all the T1 table from begin to end and then check with the subselect of course I can solve (in procedure) with a FOR subselect to get every value and check after not the nicest way, and pretty ugly and less human readable, but by far much more quick and optimized. for select T2.id from Table2 T2 where T2.Customer=:customer into :id do select * from Table1 T1 where T1.id=:id There is some way to get a good result using field in subselect? some trick to get an optimized result using this syntax? Internally your query is transformed into: select * from Table1 T1 where T1.id exists (select * from Table2 T2 where T2.id = T1.id and T2.Customer=:customer) That's why you see a full table scan on T1. Your non correlated sub-query becomes a correlated sub-query after the transformation. an easy way to achieve what you want is to convert it to a simple JOIN like this: select t1.* from Table1 T1 join Table2 T2 on (T2.id = T1.id) where T2.Customer=:customer if the relationship is not 1:1 you would need to avoid duplicates with something like: select distinct t1.* from Table1 T1 join Table2 T2 on (T2.id = T1.id) where T2.Customer=:customer Some handling for null values should be taken into account if aplicable if it will be faster or not depends on your data. see you !
Re: [firebird-support] Newbie -- Connecting to an FDB file on a network drive; Windows XP; FB 2.5.1
Em 5/3/2013 22:21, bluewindow99 escreveu: I'm just getting started with FB using the .NET interface. This connect string works just fine: private string ConnectionStringFireBird = User ID=sysdba;Password=masterkey;Database=localhost:C:\\MYLOG.FDB;DataSource=localhost;Charset=NONE;; But if I copy the MYLOG.FDB file to a mapped network drive and substitute Z: for C: in the oonnect string above, I get a message unavailable database. This remote drive is on a different PC and that PC does NOT have FB installed. As expected, the drive must be a physical (local) drive As a second experiment, I copied the FDB file to another PC which has the FB server running on it and I could connect nicely by prefacing the file with the IP address of the remote PC. As expected, FB is a client/server database, a cliente will connect to a server on a remote machine So I gather that the FDB file must be on a machine where FB server is running. yes But this confuses me a bit because my mapped Z drive was logically part of my PC. It must be a local physical drive, mapped drive would no work. (on POSIX systems you could use a network share, but it's not recomended) What would happen if I had a local hard drive partitioned into C and D, and I put the FDB file on the D drive. It will work Thanks in advance for any light you might shed. Best Harry see you !
Re: [firebird-support] Create index while database is in use?
Em 1/3/2013 18:39, Raith,Daniel escreveu: Is it safe? I want to create an index on a table but the database (fb1.5) has ~20 active users. Database usage is mostly reads, especially the table I want to index. I just don't want to take a chance creating an index with active connections could corrupt the database. Daniel If it's not safe, it would not be allowed. see you !
Re: [firebird-support] Re: Firebird speed degrades during the day
Em 7/2/2013 08:53, Carlos H. Cantu escreveu: Are you sure you ran gstat when things were already slow (as asked by Thomas)? Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br Besides that... We should not forget he is using FB 1.5, I can't remember if was 1.5 or 2.X that fixes the problem with garbage colletion on long chain of duplicate value on indices... see you !
Re: [firebird-support] Some alternative to ZeBeDee?
Em 7/2/2013 04:04, W O escreveu: Hello everybody In some articles I saw that people use ZeBeDee for Internet communication with Firebird, however that product seems discontinued since the year 2005. Do you know another software similar to ZeBeDee which works well with Firebird and it's free? Greetings. Walter. you could use ssh see you !
Re: [firebird-support] Interbase/Firebird interaction?
Em 17/1/2013 21:40, lcampbell escreveu: We have the following report from a user, who owns a copy of our AuctionTracker software: Background: AT uses IBObjects 4.8.7, and is built running against Firebird V2.2.1.17910. The normal installation requires a Firbird installation on the workstation. Our development environment is Delphi7. This user had an old DB app. that runs against Interbase; with an IB6.5 installation on his workstation. He installed AT on the workstation WITHOUT a Firebird Server installation. Apparently, AT starts, runs, finds its data and executes several processes with no problems in this environment. We're using the IBObjects TIBODatabase to connect, passing it the location of the .FDB database files, and calling its Connect method. Apparently, the underlying IBO code doesn't care if it's running against an IB server or an FB Server. We are working to verify this. This raises a couple of questions: 1) Are there any adverse conflicts between IB6.5 and Firebird in a situation like this ... i.e., would running AT against an IB6.5 engine ultimately damage the data? 2) Is there some way we can determine at startup time whether there is a Firbird engine running or an IB engine? We might want to ensure that AT is running in an environment served by Firebird only. Anybody feel free to jump in with info/advice Lane Campbell NW Software Well.. I know I am not answering your questions But.. I would install FB on a diferent port and run against it. I think it will save you any kind of trouble... The IB server could not understand the FB ODS, will not provide all the features, but, in the general case de cliente API is very similar. see you !
Re: [firebird-support] unavailable database
Em 9/1/2013 13:22, André Knappstein escreveu: after executing all possible combinations of gfix -m, gfix -v still reports Number of record level errors: 1, and gbak -b -g -i -v ... still does not get over starting transaction. I don't have any idea what happened, but suddenly the backup *did* work. This completely toils my understanding of why - after a clean reboot of the server and all users disconnected - a command would at first not work, but then suddenly would when just trying often enough. So, I made a backup, restored that to a new database, and this one seems to work so far. I hope there are no hidden surprises. Garbage Collection ? When a database is broken, the first thing to try to make a back-up is to use -g option, to avoid garbage collection, sometimes the problems resides on versions that are not needed to complete the back-up. 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] database over internet
Em 9/1/2013 17:45, Matthias Hanft escreveu: On the other side, if your client really *needs* each and every row and colum, you just can look for a faster Internet connection because AFAIK there's no possible data compression between FB clients and server. But this case should happen very rarely... -Matt One can use Zebedee (there is an article written by Artur Anjos on how to configure it with Firebird), it's pretty easy to set up. SSH tunnel has compression too see you !
Re: [firebird-support] Rolling back from 2.5 to 2.0
Em 8/1/2013 18:58, halaekinree escreveu: Hi all, I work with proprietary software that interfaces with Firebird 2.0. I recently updated to 2.5, but found that some features of our software had stopped working. Unfortunately, when I tried to roll back my test machine (Win7x64) to 2.0, the features of software that broke with 2.5 remain broken. I've performed a complete uninstall (including the registry), the directory has been deleted, but the software still acts like it did when 2.5 was installed. Does anyone know what might be persisting from the update? Thanks for your time. Could it be the ODS upgrade ? see you !
Re: [firebird-support] Using PLAN string in a view
Em 9/11/2012 07:48, patrick_marten escreveu: Hello, some time ago I've posted in the IBO support group (although it would have fit in here better) a question about custom PLAN strings for dynamic SQL statements of a query and the conclusion was, that there is mostly no need to use own PLAN strings, because the engine does it quite well. Based on the replies in the other post I assume that the conclusion applies to views I'm creating as well, doesn't it? Although their statements are static, unlike the query in the question above, the PLAN string generated by the engine will probably be better here as well. I have some views, which were created with a custom PLAN string. In order to be able to do some changes I had to drop some contraints temporary, but for one of them it failed, because there was 1 dependency. I checked the views and one was using that constraint in the custom PLAN string, so I would need to drop the view as well... If I create the views without cutsom PLAN strings, could it still come to the same error, if the engine generates it's own PLAN string? I think no, because it would be generated only when accessing that view, but I'm not sure about it and wanted to ask for confirmation. Best regards, Patrick The constraint is a key (primary, foreign or unique), if you do not specify it explicitly in the plan on the view definition there is no dependency. When you execute a query the engine you check the available indices and use the best for the query, if you droped some constraint the index will not be available, and so, not be used. So, answering your question, you are right, you will have no dependency problem.
Re: [firebird-support] UNION prevents all records from being pulled to the results
Em 29/10/2012 18:39, SoftTech escreveu: Greetings All, The SQL that follows pulls these results: (These results are missing 5 records from the second select of the union) FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 SVC1.1710 SVC3010 This is the SQL statement: SELECT FC.FEE_CODE, COALESCE(PCF.CASE_FEE_AMT_EARNED_AGENCY_1 + PCF.CASE_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY, FC.SHOW_IN_PMT_DIST_PLAN, FC.FEE_CATEGORY FROM PMT_CASE_FEE PCF JOIN PMT_CASE PC ON PC.ACCT_ID = PCF.ACCT_ID AND PC.CASE_ID = PCF.CASE_ID AND PC.PMT_ID = PCF.PMT_ID AND PC.PMT_NO = PCF.PMT_NO JOIN CASE_FEE_HISTORY CFH ON CFH.CASE_FEE_ID = PCF.CASE_FEE_ID AND CFH.PMT_ID = PCF.PMT_ID JOIN CASE_FEE CF ON CF.CASE_FEE_ID = PCF.CASE_FEE_ID JOIN FEE_CODE FC ON FC.FEE_ID = CF.FEE_ID WHERE PCF.PMT_ID = :iPmtID AND PCF.PMT_NO = :iPmtNo AND PCF.ACCT_ID = :iAcctID AND PCF.CASE_ID = :iCaseID UNION SELECT FC.FEE_CODE, COALESCE(PDF.DEBT_FEE_AMT_EARNED_AGENCY_1 + PDF.DEBT_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY, FC.SHOW_IN_PMT_DIST_PLAN, FC.FEE_CATEGORY FROM PMT_DEBT_FEE PDF JOIN PMT_DEBT PD ON PD.ACCT_ID = PDF.ACCT_ID AND PD.DEBT_NO = PDF.DEBT_NO AND PD.PMT_ID = PDF.PMT_ID AND PD.PMT_NO = PDF.PMT_NO JOIN DEBT_FEE_HISTORY DFH ON DFH.DEBT_FEE_ID = PDF.DEBT_FEE_ID AND DFH.PMT_ID = PDF.PMT_ID JOIN DEBT_FEE DF ON DF.DEBT_FEE_ID = PDF.DEBT_FEE_ID JOIN FEE_CODE FC ON FC.FEE_ID = DF.FEE_ID JOIN DEBT D ON D.ACCT_ID = PDF.ACCT_ID AND D.DEBT_NO = PDF.DEBT_NO WHERE PDF.PMT_ID = :iPmtID AND PDF.PMT_NO = :iPmtNo AND D.ACCT_ID = :iAcctID AND D.CASE_ID = :iCaseID If I execute just the first select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 If I execute the second select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY SVC3010 SVC3010 SVC3010 SVC3010 SVC3010 SVC1.1710 SVC3010 Again, executing the select statements separately results in the correct records being returned, executing both selects with the union omits 5 records. Using Firebird 1.5.3 Any ideas? Thanks, Mike Use UNION ALL to avoid removing duplicates
Re: [firebird-support] Parameterised like query won't use index in the plan
Em 29/10/2012 20:02, roydamman escreveu: Hello, I have table with an indexed field (Firebird 2.1/2.5). When I use the query: select * from mytable where myfield like 'test' the plan uses the index on myfield and the query returns quickly. When I use the query: select * from mytable where myfield like :myparameter and define myparameter = 'test' the plan doesn't use the index (natural) and my query returns slowly. The big question is: What am I doing wrong? Any help is appreciated. Regards, Roy Damman Once it's a parameter, the parameter could hold any value like: 'ABC' 'ABC%' '%ABC' the first two could use an index during a search the last on could not use any index. The optimizer shoul prepare the path for all the scenarios. You could change your query to something like: select * from mytable where myfield starting with :myparameter This way an index would be used. In fact in a non parameterized query, your original query has a kind of code injection, and become something like: select * from mytable where myfield like 'test%' and myfield starting with 'test' Thus the index could be used. see you !
Re: [firebird-support] How do you 0/null results to a GROUP BY query
try something like this:
with recursive
Datas (Data) as
(select
Cast('2009-01-01' as Date)
From
rdb$database
union all
Select
Data + 1
From
Datas
Where
Data + 1 = Cast('2009-01-31' as Date))
select
D.Data, sum(PVI.QuantidadeOriginal)
from
Datas D left join
PedidoVendaItem PVI on (D.Data = PVI.DataEntrega)
group by
D.Data
change the field/table names to fit you needs
HTH
Em 24/10/2012 19:54, cornievs escreveu:
Hi All
I have query which extract the sales per day from a table
Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO,
SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO.
It works 100%, but only returns the dates with sales, I need it to also
include the dates with zero sales, for example:
YEARNO DAYNO SUM
2012 015000
2012 026000
2012 030 (or null will be fine)
2012 047000
etc.
Any help will be appreciated!
Cornie van Schoor
InfoStar Software
++
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
Re: [firebird-support] How do you 0/null results to a GROUP BY query
Em 24/10/2012 20:21, Doug Chamberlin escreveu: On 10/24/12 5:54 PM, cornievs wrote: I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it. I used to do this way, but with CTE you create that table dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days
Re: [firebird-support] How do you 0/null results to a GROUP BY query
Em 24/10/2012 20:37, Doug Chamberlin escreveu: On 10/24/12 6:25 PM, Alexandre Benson Smith wrote: Em 24/10/2012 20:21, Doug Chamberlin escreveu: On 10/24/12 5:54 PM, cornievs wrote: I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it. I used to do this way, but with CTE you create that table dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days I saw that. However, I don't see the advantage. Using a reference table is simpler and clearer than using CTE or other code to generate the reference values. One don't need to refill the values, like at the beginning of each year... of course you could pre-fill i with a century of days... :)
Re: [firebird-support] How to create an calculated field for bank balance?
Em 12/9/2012 15:28, K Z escreveu: Hello, Is there a trick to have this? CREATE TABLE BANK_ACCOUNT ( ID INTEGER NOT NULL, OCCURRENCE TIMESTAMP, DESCRIPTION VARCHAR(100), AMOUNT DECIMAL(10,2) NOT NULL CONSTRAINT PK_ BANK_ACCOUNT _1 PRIMARY KEY (ID) ); CREATE VIEW BANK_ACCOUNT_STATEMENT (ID, OCCURRENCE , DESCRIPTION , AMOUNT, BALANCE) AS SELECT r.*, (Calculated Field) as BALANCE FROM BANK_ACCOUNT r order by r.OCCURRENCE desc ; Calculated Field is the previous record balance plus the current record field AMOUNT. If there is no previous record, then is the current record field AMOUNT. For example: ID DESCRIPTION AMOUNT BALANCE 1 Deposit 10 10 2 Check-55 3 Check-10 -5 4 Credit transfer5045 Thank you in advance for your support. I would implement it on the client side... It depends on the date range selected, the order of the records stored, and so on... IMHO it's a dynamic thing that must be calculated on the fly.
Re: [firebird-support] How to create an calculated field for bank balance?
Em 12/9/2012 18:02, K Z escreveu: Hello, The ideia is that the first record is the most recent one. The last is the oldest. How to handle where what is the result of select * from Table and select * from Table where AccountId = 12 and select * from Table where AccountID = 12 and DocumentType = 3
Re: [firebird-support] Re: Desktop Application For Centralized Firebird Database...
Em 6/9/2012 17:54, Aage Johansen escreveu: Vishal wrote: ... Here, my doubt is Using Delphi 5 and Firebird database, is it possible for all the instances of this application which are installed at different cities could access one centralized database ? If yes then HOW ? ... You need a fixed IP address for the machine which is hosting the database (the db server). Your program - wherever it runs - just need to use a connection string with IP address or machine name:database path and filename, or alias, e.g. 123.234.123.234:D:\DB\ourdb.fdb (or possibly VISHAL_SERVER:LIBRARY_DB, with necessary definitions in place). You may have to persuade administrators to open port 3050 in firewalls (in- and outbound). Besides all said by Aege, I recomend you to read this article: http://web.firebirdsql.org/download.php?op=fileid=firebird_zebedee_eng.pdf Even using a compressed tunnel, FB protocol is known to be slow over high latency connections, if you could develop using a 3-tier approach you will have a much better performance. Replication is another option, but adds some complexity. see you !
Re: [firebird-support] Re: Crystal Report loading pblm from client ODBC
Em 10/9/2012 02:22, mathewabraham68007 escreveu: I checked with a database manager, first the view is dropped and created again, and data is retrived. but for the second time the view cannot be dropped.. My first advice is to not recreate the view each time, there is no reason to do that, Use a SQL statament directly or create the view once and use it without recreating. see you !
Re: [firebird-support] execute statement
Em 9/8/2012 17:03, Sergio escreveu: Hello! I'm trying to do a trigger to maintain a history table. I'm using (for the first time!) execute statement. What I want to do is very simple: if a field change I save the old value in the history When I execute the trigger I get an error: Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 1, column 1. if. I'm sure I'm using execute statement in the wrong way !!! this is the trigger: CREATE OR ALTER trigger tlm_maestro_au0 for tlm_maestro active after update position 0 AS declare variable loc_nuevo_id id; declare variable loc_ejecutar descripcion_larga; begin loc_nuevo_id = gen_id(gen_tlm_maestro_hist,1); insert into tlm_maestro_hist (id,modificado) values (:loc_nuevo_id, current_timestamp); for select 'if (new.' || trim(rdb$field_name) || ' is distinct from old.' || trim(rdb$field_name) || ') then update tlm_maestro_hist set ' || trim(rdb$field_name) || ' = old.' || trim(rdb$field_name) || ' where id = :loc_nuevo_id;' from rdb$relation_fields where rdb$relation_name = 'TLM_MAESTRO' into :loc_ejecutar do begin execute statement loc_ejecutar; end end There is no IF fucntion in FB, did you meant IIF ??? see you !
Re: [firebird-support] execute statement
Em 9/8/2012 17:20, Mark Rotteveel escreveu: On 9-8-2012 22:18, Alexandre Benson Smith wrote: There is no IF fucntion in FB, did you meant IIF ??? IF does exist in PSQL. Mark Yes, I know... But at first glance I thought he was trying to use IIF since it's inside a SELECT statement. After you mentioned EXECUTE BLOCK I re-read the post and saw what he really wants to do...
Re: [firebird-support] Re: execute statement
Em 9/8/2012 17:19, Mark Rotteveel escreveu: I think you need to create an EXECUTE BLOCK statement as text and then execute that using EXECUTE STATEMENT, however I am not actually sure if that would give you access to the NEW and OLD context tables. Mark There is no way to access NEW and OLD context variables inside EXECUTE STATEMENT. To achieve what he wants he will need to write an app (or stored procedure, or whatever) that loops trough the fields and generate the trigger PSQL code like CREATE OR ALTER trigger tlm_maestro_au0 for tlm_maestro active after update position 0 AS declare variable loc_nuevo_id id; declare variable loc_ejecutar descripcion_larga; begin loc_nuevo_id = gen_id(gen_tlm_maestro_hist,1); insert into tlm_maestro_hist (id,modificado) values (:loc_nuevo_id, current_timestamp); IF (new.Field1 is distinct from old.Field1) then update tlm_maestro_hist set Field1 = old.Field1 where ID = :Loc_Nuevo_ID; IF (new.Field2 is distinct from old.Field2) then update tlm_maestro_hist set Field2 = old.Field2 where ID = :Loc_Nuevo_ID; IF (new.Field3 is distinct from old.Field3) then update tlm_maestro_hist set Field3 = old.Field3 where ID = :Loc_Nuevo_ID; end
Re: [firebird-support] Error Failed to load gds32.dll
Em 6/8/2012 13:28, himanshu escreveu: Hello all, I am new to this world of connecting and converting databases. I am trying to convert a database from one format to the other. It was recommended in the set instructions written by the previous author to use Firebird, and hence I am here. I am using Windows XP (32 bit only) and Windows 7 (32 and 64 bit) versions. (lot to handle here). I installed Firebird and then tried to connect the database. The system gave me following error message Failed to load gds32.dll (#-904) I am unable to understand what it means, why it appears and how to fix it. Wondering if anyone can help me here. I will greatly appreciate any help regards, Himanshu Bahirat When you got this message ? what application are you using to connect ? Perhaps a 32 bits app trying to load a 64 bits DLL ??? see you !
Re: [firebird-support] matrix report
Em 1/8/2012 08:23, mahdoom_a escreveu: Dear all, I need your help to create this report AQNOJan Feb Mar Apr May Jun Jul..Dec. Total_paid --- --- --- --- --- --- --- ----- 10012000 2000 2000 2000 8000 CHQNO 214 215216 217 it will fill the amount(PAYMNT) in each month (PAYDATE) and its cheque number (CHQNO) table structure is CREATE TABLE RNTSTAT ( STATNO INTEGER NOT NULL, RNTDUE DOUBLE PRECISION, DUEDATEDATE, PAYMNT DOUBLE PRECISION, PAYTYP VARCHAR(30), CHQNO VARCHAR(30), PAYDATEDATE, INVOICEVARCHAR(30), EXPNS DOUBLE PRECISION, XPNSDATE DATE, XPENSTYP VARCHAR(30), MENAINTYP VARCHAR(30), CHRGVALDOUBLE PRECISION, CHRGTYPVARCHAR(30), RNTNO INTEGER, EXNSNO INTEGER, AQNO INTEGER, PANNO INTEGER, CONTNO VARCHAR(30), CHRGPERFLOAT, FROM_DATE DATE, TO_DATEDATE, AWQAFNONEW_DOMAIN ); any hint will be helpful. thanks regards, AHMAD I would usually do this on the client-side, it's called cross-tab report. You could implement it on the server-side using sub-queries, in-line views or CTE's. I would post a sample query that I used on a talk I did last July on Firebird Developers Day to show how to use CTE's with ProdutoVendaAnual(Ano, ProdutoID, TotalVendido) as (select extract(year from DataEntrega) Ano, ProdutoID, Sum(QuantidadeOriginal) from PedidoVendaItem group by 1, 2) select P.Codigo, P.Descricao, V_2006.TotalVendido, V_2007.TotalVendido, V_2008.TotalVendido, V_2009.TotalVendido from Produto P left join ProdutoVendaAnual V_2006 on (V_2006.ProdutoID = P.ProdutoID and V_2006.Ano = 2006) left join ProdutoVendaAnual V_2007 on (V_2007.ProdutoID = P.ProdutoID and V_2007.Ano = 2007) left join ProdutoVendaAnual V_2008 on (V_2008.ProdutoID = P.ProdutoID and V_2008.Ano = 2008) left join ProdutoVendaAnual V_2009 on (V_2009.ProdutoID = P.ProdutoID and V_2009.Ano = 2009) HTH
Re: [firebird-support] (unknown)
Em 11/7/2012 14:23, K Z escreveu: Hello, I'm still far from a expert on firebird and sql. If someone can help me to improve this code, i will be grateful. 1) GOAL: I need to make a search for a partial word in several fields of the customers table. 2) STRUCTURE: I am using a view because i join the customers table with the country names table and payment conditions table. CREATE TABLE CLIENTES ( IDCLIENTES INTEGER NOT NULL, CLIENTE VARCHAR(100) NOT NULL COLLATE UNICODE_CI_AI, MORADA VARCHAR(100) NOT NULL COLLATE UNICODE_CI_AI, CP VARCHAR(10) DEFAULT NULL COLLATE UNICODE_CI_AI, LOCALIDADE VARCHAR(50) NOT NULL COLLATE UNICODE_CI_AI, PAIS VARCHAR(2) DEFAULT 'PT' NOT NULL COLLATE UNICODE_CI_AI, TELEFONE VARCHAR(15) DEFAULT NULL COLLATE UNICODE_CI_AI, FAX VARCHAR(15) DEFAULT NULL COLLATE UNICODE_CI_AI, EMAIL VARCHAR(100) DEFAULT NULL COLLATE UNICODE_CI_AI, CONDICOES_PAGAMENTO SMALLINT DEFAULT 1 NOT NULL, REGIME_IVA VARCHAR(1) DEFAULT 'G' NOT NULL COLLATE UNICODE_CI_AI, PER_DESCONTO DECIMAL(6,2) DEFAULT 0 NOT NULL, NUNCA_BLOQUEAR VARCHAR(1) DEFAULT 'F' NOT NULL COLLATE UNICODE_CI_AI, CONTRIBUINTE VARCHAR(25) DEFAULT NULL COLLATE UNICODE_CI_AI, COD_CLIENTE_FAT VARCHAR(15) DEFAULT NULL COLLATE UNICODE_CI_AI, ATIVO VARCHAR(1) DEFAULT 'T' NOT NULL COLLATE UNICODE_CI_AI, VALIDADO VARCHAR(1) DEFAULT 'F' NOT NULL COLLATE UNICODE_CI_AI, CONSTRAINT INTEG_26 PRIMARY KEY (IDCLIENTES) ); CREATE INDEX IDX_CLIENTES1 ON CLIENTES (CLIENTE); CREATE INDEX IDX_CLIENTES2 ON CLIENTES (PAIS); CREATE INDEX IDX_CLIENTES3 ON CLIENTES (CONDICOES_PAGAMENTO); CREATE TABLE COND_PAGAMENTO ( IDCOND_PAGAMENTO INTEGER NOT NULL, DESCRICAO VARCHAR(45) NOT NULL COLLATE UNICODE_CI_AI, QTD_DIAS SMALLINT DEFAULT 0, ENTREGA_PAGAMENTO VARCHAR(1) DEFAULT 'T' NOT NULL COLLATE UNICODE_CI_AI, CONSTRAINT INTEG_14 PRIMARY KEY (IDCOND_PAGAMENTO) ); CREATE TABLE PAISES ( IDPAISES INTEGER NOT NULL, NOME VARCHAR(60) NOT NULL COLLATE UNICODE_CI_AI, CODIGO_ISO_3166_1 VARCHAR(2) NOT NULL COLLATE UNICODE_CI_AI, VALOR_PORTES DECIMAL(10,2) DEFAULT NULL, ISENCAO_PORTES DECIMAL(10,2) DEFAULT NULL, CONSTRAINT INTEG_79 PRIMARY KEY (IDPAISES) ); CREATE VIEW LISTA_CLIENTES (COND_PAGAMENTO_DESCRICAO, PAIS_NOME, IDCLIENTES, CLIENTE, MORADA, CP, LOCALIDADE, PAIS, TELEFONE, FAX, EMAIL, CONDICOES_PAGAMENTO, REGIME_IVA, PER_DESCONTO, NUNCA_BLOQUEAR, CONTRIBUINTE, COD_CLIENTE_FAT, ATIVO, VALIDADO) AS select cp.DESCRICAO COND_PAGAMENTO_DESCRICAO,p.NOME PAIS_NOME,c.* FROM CLIENTES c inner join PAISES p on p.CODIGO_ISO_3166_1=c.PAIS inner join COND_PAGAMENTO cp on cp.IDCOND_PAGAMENTO=c.CONDICOES_PAGAMENTO order by c.CLIENTE; 3) THE SLOW QUERY: From Flamerobin i run the following query and is take 60 seconds to finished: 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 If i change the query to: select first 20 * from LISTA_CLIENTES where (CLIENTE like '%crist%') order by CLIENTE it runs much faster. But as more fields i had in the WHERE clause, more slow it gets. I try using the CONTAINING function instead of LIKE but i got no improvement. I also tried to create a PROCEDURE to speed up but again not speed up on getting the result. I use this kind of code with other tables and it runs very fast. I also tried using MySQL for comparing and this exact query runs in milliseconds. I tried to read docs in the Firebird site and still no improvements. Can anyone help me with this problem? Thank you. This kind of search criteria (like '%crist%') will not use an index, so the size of the tables would impact on the time to get the records. I think that if you just filter using CLIENTE like '%crist%' is faster because it starts by this table, but when you put the fields from the other tables the optimizer thinks that will be faster to start from table PAIS for example, and thus the final result is slow. There is no way to make this kind of query to be fast, it simply cannot use any index to speed it up and worse sicne you are searching for the same information on fields from a bunch of tables the optimizer will choose very bad plans because it will start from the table with fewer rows even when the data is in fact on the larger table. CONTAINING will no help as you already experienced and the stored procedure will not help either... You could fool the optimizer forcing the table CLIENTES to be the first one scanned using a query like: CREATE VIEW LISTA_CLIENTES (COND_PAGAMENTO_DESCRICAO, PAIS_NOME, IDCLIENTES, CLIENTE, MORADA, CP, LOCALIDADE, PAIS, TELEFONE, FAX, EMAIL, CONDICOES_PAGAMENTO, REGIME_IVA, PER_DESCONTO, NUNCA_BLOQUEAR, CONTRIBUINTE, COD_CLIENTE_FAT,
Re: [firebird-support] Restoring backup from 2.5 to 2.1
Did you tried to use 2.1 gbak against 2.5 server to produce the gbk ? see you ! Em 4/7/2012 15:59, Aldo Caruso escreveu: To restore, I am using gbak 2.1 against a 2.1 server, but reading a *.fbk backup file that was created using a 2.5 server. AFAIK I'm not using specific things from 2.5 Thanks, Aldo El 04/07/12 13:56, Thomas Steinmaurer escribió: When I try to restore a backup done in a 2.5 firebird server to a 2.1 firebird server, I get the following error message: gbak:transportable backup -- data in XDR format gbak: ERROR:Expected backup version 1..8. Found 9 gbak:Exiting before completion due to errors Isn't it possible to restore a backup database from a 2.5 to a 2.1 firebird server ? Are you using gbak from 2.1 against a 2.5 server and I hope you don't use any new/specific things from 2.5? -- With regards, Thomas Steinmaurer http://www.upscene.com/ [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
Re: [firebird-support] Re: database in external USB HDD
Are you sure you are not mixing it with using the database on Pen Drives ? Em 4/7/2012 19:10, Sergio escreveu: I use FB 2.01 in external USB drive connected to the server for about 3-4 years. No problem at all, I think FB 2.5 won't be a problem too. Currently, using FB 2.5 on a RAM Disk (in server) for a better speed. Thanks for the answer!! If memory serves, I think I read somewhere a post from Helen Borrie in which she says that is not a good idea... But can't find that post now! :( Thanks again! sergio ++ 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
Re: [firebird-support] How to limit the number of concurrent users to a database
Em 13/6/2012 14:56, Thomas Steinmaurer escreveu: Not bullet-proof, but you could write an ON CONNECT database trigger and throw an exception if you want to abort the connection establishment. The condition could be queried e.g. by a COUNT(*) on the MON$ATTACHMENTS monitoring table or whatever you want to be included in the condition. Not bullet-proof in a way, that database trigger can be temporarily disabled at session level or deactivated/dropped when connecting with the database owner via a tool like isql etc. where you can issue a DROP TRIGGER statement. Regards, Thomas Besides the cases you pointed out, there is the case of client lost conection. But I think that this is the easiest way to do it... The OP could tell us some more info like: Do you want to count the simultaneous connections or the number of users ? (ex. the costumer could have 10 valid users but are allowed to use just 5 at a time) 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
Em 13/6/2012 17:17, Fulvio Senore escreveu: Besides the cases you pointed out, there is the case of client lost conection. But I think that this is the easiest way to do it... The OP could tell us some more info like: Do you want to count the simultaneous connections or the number of users ? (ex. the costumer could have 10 valid users but are allowed to use just 5 at a time) 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. So they could install it on 5 computers but, if they payed for 2 connections, they will be able to use the program only on 2 computers at the same time. Fulvio I would go for the Thomas's sugestion to use on connect trigger. *If* you identify that lost connections (ungracefully client app exit) is a concern, you could employ some logic into your app to update the last time used in a table, and ignore any connection that is not used for 5 minutes (or any time you think is reasonable) ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
Em 13/6/2012 18:03, Jesús García escreveu: Why control it in the server side?if your customer can connect to the database as sysdba, can remove the trigger or change the logic in the connect event. If You do it on the client side, after connect, do a select of mon$attachments and if it is greater than the licensed concurrent connections, then You can disconnect the user or show the connection list and let the user drop an existing connection. Jesus He could use the approachs combined. I think that just relying on MON$CONNECTIONS (or any table populate upon connect/disconnect) is prone to error due to abnormal app termination. If I would implement it I would have a last time used that is updated by the application, a timer or any other periodical interval (open query, open form, etc.). And consider any connection with a last time used greater than some interval as a dead connection. see you !
Re: [firebird-support] Re: Firebird 2.5 (64 bit), strange SQL result
Em 9/5/2012 00:00, trskopo escreveu: Thanks for your reply. I think my database is in problem, because when I tried to restored it, I got error Engine Message : validation error for column ID_DIV_LC, value *** null *** warning -- record could not be restored I think, may be there is a leak in Firebird to trap an error when null value allowed to store in a not null value definition. I forgot how to insert this null value to the database. Thanks and regards, Sugiarto No, As said before, in the beginning the colunm allows null, you insert some records with null on that column, then you changed the column to be not null, since it's not null the back-up won't restore. You need to provide some value for a column that allows null when you change it to not null see you !
Re: [firebird-support] Firebird 2.5 (64 bit), strange SQL result
Em 7/5/2012 21:51, trskopo escreveu: Hi all, I run sql statement via flamerobin, the statment is like this : SELECT * FROM M_INT_FRM a where a.ID_TRS_TYP = 100 It will returns : ID ID_DIV_LC SCT_INI ID_TRS_TYP FRM_INI 4 8 MP 100PO 5 9 MN 100PO 6 0 MS 100PO but if I change sql statement to this SELECT * FROM M_INT_FRM a where a.ID_TRS_TYP = 100 and a.id_div_lc = 0 no sql result returns. You can download the database at http://www.mediafire.com/?bx8k29kt7x2ucks Strange, could it be a bug? Thanks and regards, Sugiarto What the datatype of ID_DIV_LC column ? what about: SELECT * FROM M_INT_FRM a where a.ID_TRS_TYP = 100 and a.id_div_lc between -0.1 and 0.1 see you !
Re: [firebird-support] why Blob is so slow ?
Em 1/5/2012 14:51, Fabricio Araujo escreveu: Remember Alexandre, GBAK (and Services API) are a DataPump-style backup, diffent of NBAK (which AFAIR restores database pages instead of loginal structure) which makes me think: you tried that restore on a heavily fragmented storage? Since GBAK works as a datapump, certainly it makes the Server grows the *.fdb file so many times. Would be nice if we could (if it already doesn't do that) specify an file size on restore and it could be created using instant file instancing (I know it have something to do with a volume operation, since the service user need to have disk volume operations' permission on Windows - MSSQL use that and brings the restore multi GB time to a half - or less). Yes, I know, but the same occurs to an gorwing database file with just simple types (varchar, date, integer, etc.) and the time is considerably diferent. There is a new feature on Fb (I can't remember in wich version) that grows the database more than a page at once, this was implemented to avoid disk full problems, but as a side effect it could improve a lot the restore time. But the case I faced has something to do with my hardware and/or filesystem. The very same back-up restored under 3s on Cantu's and Kuzmenko's computers, where in my server it need more than 10 minutes to finish. I did another test on my notebook out of my VM and it took under 3s too. I ruled hardware out to fast, because I faced the slow restore on a costumer, and did a test on my server, and on both the time was so big, this leads me to rule out hardware, but perhaps the filesystem on both machines are the same, unfortunatelly I did not have remote access to that costumer server, and did not visited them since, so I could not tell anything about the costumer filesystem. Something is very diferent on restoring blobs than simple types, I know it's comparing apple to orange, but 2 databases with the same size has considerably diferent times for back-up/restore if it's made of simple datatypes or with blob content. I know it's not a fair comparison, but anyway, I think it's not completely invalid. I am very busy this days, but I will perform more tests on distinct hardware to see some numbers about it. Thanks for your message. 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Performance over VPN
Em 27/4/2012 15:11, Maury Markowitz escreveu: Well one way or the other the latency of the link does seem to be the most important factor in the problem I'm seeing. I have three ways to get at the data: 1) log into the remote server using RDP and run the queries locally. even the most complex ones complete in under a second I use RDP for connections of internet, it's the best performance I can get and no changes on my code. What's interesting is that there does seem to be some relationship between the number of joins and the performance. This makes me *suspect* that the driver is pulling tables to the local client and doing local JOINs. Well I don't know the JDBC implementation, but I can assure you that the JOINS are done server side. One thing, could be possible that some library (hibernate or some other O/R mapping) are doing things behind the scenes ? see you !
Re: [firebird-support] why Blob is so slow ?
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 possible. Quoting Ann Harrison from the top of my head (probably not the exact words) if you don't need a correct answer, the answer is 13. I don't use Blob's that much, but in some cases I think it's a good sollution. Anyway, thanks for sharing your thoughts, I know that store large binary data inside/outside the database is the kind of thing that there is no rule of thumb to choose between one or another, myself use both approachs for distinct use cases. My concerns is that something is strange regarding blob manipulation. It's too slow to me. see you ! Alexandre
Re: [firebird-support] why Blob is so slow ?
Em 19/4/2012 12:13, Tupy... nambá escreveu: 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. 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 !
Re: [firebird-support] why Blob is so slow ?
Em 19/4/2012 12:28, Carlos H. Cantu escreveu: Sorry but the discussion is going off-topic for the original question, that is: why backup/restore of blobs are so much slower compared to non-blobs data. I'm also curious about this. Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br I noted this slowness for some time, but never created a test case so it can be measured. I am sending a back-up to Dmitry Kuzmenko (as he asked for) so he could take a look. I really don't know what's happening, but it's strange to me. I think that a profilling of gbak and fb server process during the restore could show where the time is used and shed some light. see you !
Re: [firebird-support] why Blob is so slow ?
Em 19/4/2012 13:18, Tupy... nambá escreveu: 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. I don't said the Garbage Collection is the same as defragmentation on MSSQL, I said that I don't know about PG, but I *think* VACCUMM is the same as FB Garbage Collection :) and I didn't say that I am sure about it All the tests are done on freshly restore DB, so it's not fragmented, the slowness is on back-up/restore of a freshly created test database. In this moment I am doing tests with Carlos Cantu and Dmitry Kuzmenko, and the culprit so far is my machine, on their machine (both !) the restore took 3s in mine 10 minutes ! I am testing on ext3 and ext4 partitions and I will make more tests on another machine, so I can isolate hardware as a factor. see you !
Re: [firebird-support] why Blob is so slow ?
Em 19/4/2012 16:28, Carlos H. Cantu escreveu: LC It is a little amazing at time when some things work fast on one machine and a LC lot slower on another, but the sort of problem you are seeing I would check that LC there is not a problem with the hard disc. I've seen that sort of effect when LC the controller is having trouble reading a disk. It WILL read the data LC eventually, but keeps winding the heads back to '0' and repositioning for each LC block read. Replacing the hard disk and restoring the data invariably cleared LC the problem. Had it a couple of time now - 'Maxtor' discs have been stripped LC from all my customer machines now! My guess is that the time differences are also related to the configuration of the file system used in his linux server (ie: barrier and other params). Kouzmenko and me tested in Windows machines. Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br I am still doing some tests to try to identify the culprit. I tested on another linux machine and the restore is under 3s, but I can't compare because this machine uses SCSI disks on RAID, and mine is a simple (and pretty old) SATA disc. I will test on some real hardware and report back. I had ruled out hardware/file system too fast, thats the reason I posted the original message, the reason I ruled out hardware/file system configuration is because I noted the slowdown on a client site and then tested on my server I noted the same speed problem... But I think that both servers (mine and my customer) have something weird (perhaps filesystem options as pointed out by Carlos). Unfortunatelly I had no remote access to that server. Thanks for all the input and to Carlos and Dmitry for the time to perform the tests. see you !
Re: [firebird-support] What the best way to to join 2 tables accross 2 databases ?
Em 19/3/2012 00:15, Helen Borrie escreveu: At 02:19 PM 19/03/2012, Alexandre Benson Smith wrote: You can do cross database joins using execute statement in FB 2.5. It's true that you can perform DML in an external database from within a PSQL module in v.2.5, using the EXECUTE STATEMENT extensions. But, no, you can't do cross-database joins in Firebird at all. It won't happen until Firebird supports database namespaces, which is not yet. ./heLen Helen, Thanks for the clarification... I'm not using 2.5 yet, but I recall to take a look on this feature (cross database DML), Perhaps I did an SP to check on another database... I will look for the test I did. To the OP: Sorry for the misinformation
Re: [firebird-support] What the best way to to join 2 tables accross 2 databases ?
Em 18/3/2012 16:59, nathanelrick escreveu: hello, What the best way to to join 2 tables accross 2 databases ? actually i do on the 1rt database a select First 100 ID from myTable1 where ... and in the seconde database select * from MyTable2 where ID in (id_we_retrieve_from_the_first_select) but the problem, the select * from MyTable2 where ID in (id_we_retrieve_from_the_first_select) is very slow, especially on the prepare :( is their any better way ? You can do cross database joins using execute statement in FB 2.5. Take a look on the release notes. see you !
Re: [firebird-support] Firebird security acessing database on other server as SYSDBA
Em 5/3/2012 16:19, Marcelo Trópia escreveu: Hi Iwan, thank you. Regarding DB2 security (mainframe DB2 no z/OS), yes, there is this kind of security. If someone copy the VSAM dataset (DB2 tablespace access method) and try to register this tablespace on other DB2 instance, DB2 will not accept it because dbid and obid identifiers are stored inside the dataset and you need to know these identifiers to inform to DB2. Someone with a deep knowing of DB2 (a hacker) could discover these identifiers, but it is not easy. Best regards, Marcelo What DB2 implements is Security by obscutiry I understand you concern, but there is no security if you cannot garantee it at file system level. Even if FB 3.0 stores the user information inside database, it will be easy to complie a new version of FB that bypass the user authentication. It's easy to circunvent it in FB since it is open source, and anyone could analyze the code and recompile it. see you !
Re: [firebird-support] Firebird 1.5: How to tell the size of one table inside Fdb file?
Em 14/2/2012 22:46, Nando escreveu: Hi to all, I have a Firebird 1.5 database that has grown up to 26.6 Gb. Most of the data is stored in one single table that holds a memo field. I'm worried about the size of this table, because as far as I know the maximun table size for version 1.5 is about 36 Gb and I guess I'll soon get close to this limit. How can I tell the size of a single table inside the Fdb file. Thank your for any help. Nando. Look on the gstat output for the number of data pages and multiply it by your page size. see you !
Re: [firebird-support] what the most efficient way to do update or insert with value ?
Em 15/2/2012 04:54, nathanelrick escreveu: hello, i want to do something like update or insert into mytable(ID, amount) values (123, amount + 100); how to do this ? :) Pretty hard to answer anything taking into account the information you provided
