[firebird-support] Re: Ran "successful" install on Mac, but nothing is installed.
Hi Alan, Mine installedl in /Library/Frameworks/Firebird.framework hth Iain ---In firebird-support@yahoogroups.com, wrote : Please help. This is my first time downloading Firebird. I downloaded from http://www.firebirdsql.org/en/firebird-2-5/#MacOSX http://www.firebirdsql.org/en/firebird-2-5/#MacOSX and the version I got was FirebirdSS-2.5.3-26778-x86_64.pkg.zip Twice I have run the installer, and each time the installation was "successful," yet nothing called "Firebird" is under Applications or anywhere else on my computer, other than the installer. I am using Mac OSX 10.6 (Snow Leopard) on a Core 2 Duo (64-bit) Macbook from late 2007.
Odp: SV: [firebird-support] Firebird Embedded corruptions
Hi, 1. you use old FB version. 2.5.1 is not newest version 2. Please include error message from FB log Regards, Karol Bieniaszewski - Reply message - Od: "fabianoas...@gmail.com [firebird-support]" Do: Temat: SV: [firebird-support] Firebird Embedded corruptions Data: niedz., wrz 14, 2014 01:21 If Windows has volume shadow copy enabled this always corrupt databases in several ways. Em 13/09/2014 17:32, "Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]" escreveu: >Hi, > >We have shipped Firebird Embedded bundled together with our product for a few >years now and the system is currently in production at several thousand of our >customer's sites. >Currently we are using Firebird Embedded 2.5.1 with the latest .NET-driver and >a stack consisting of Castle Active Record on top on NHibernate and the system >is running on the >latest versions of Windows. > >All is well and Firebird has served us good so far with the exception of >database corruptions that gets reported from a new set of customers every >week. For some of them it is >possible to instruct the customer on how to repair the databases themselves, >but some of the databases are unfortunately so heavily corrupted that they >need to be sent to us >for repairing (which is a tedious work that steals time from other tasks). >Most of them corruptions are normally found in the tables that gets the most >writes, but I guess that is >only natural. > >We are now at the planning stage for the next major release of our product and >we are thus rethinking if Firebird really is a good choice, because of this. > >Lots of effort has gone into solving this problem on our side, so I think the >normal prerequisites has already been put into place (e.g using forced writes >and so forth), but our >system needs to be up and running 24x7, which means that it is not possible to >schedule periodic backup/restore cycles and my personal theory is that >Firebird embedded gets >corrupted over time if you are not doing this regularly. > >So I have have a few questions that I would appreciate if someone could answer: > >1. Is it feasible to run Firebird Embedded 24x7 in a setup where there are no >scheduled backup/restore cycles. If not, how often should this be performed to >ensure that the >database does not get corrupted. > >2. Most of our customers are not using a UPS. From my experiments I have not >managed to create a corrupted database by turning of the power while doing a >large set of >writes (in a session running in VirtualBox). Could someone please confirm that >this is indeed safe when you are running with synchronized writes turned on? > >3. Are there any operations on a live database that should be avoided to >minimize the risk of corruptions? > >4. Just read a discussion about whether it is needed or not to call >fb_shutdown to stop Firebird Embedded. Could this be the reason why we are >getting corruptions? Should >we change our service to perform this call when it is stopped? > >5. I have also seen discussions of turning of automatic sweeps of the database >(and doing them manually instead). Is this a likely source of corruptions for >our setup? > >Thanks in advance. Maybe are there no certain answers to my questions, but any >pointers in the right direction would be very appreciated. Firebird has been a >real workhorse >for us and we would rather like to keep it. Hei Jan! The one thing I try to avoid, is running DDL (CREATE, ALTER, DROP
Re: [firebird-support] Firebird Embedded corruptions
Hi, First a sincere thanks to all of you for your answers. We have all kinds of different corruptions and maybe do they not have the same root cause. Here I will give you three typical examples. *Example 1* This user complained that the system had stopped. Upon further investigation the following exception was found in our logs and when we received the database it was indeed corrupted. Exception while executing job: NHibernate.Exceptions.GenericADOException: Error executing Enumerable() query[SQL: select sequencevo0_.recording_sequence_id as col_0_0_ from Recording_Sequence sequencevo0_ where not (exists (select filesequen1_.SEQ_ID from File_Seq filesequen1_, Recording_Sequence sequencevo2_ where filesequen1_.SEQ_ID=sequencevo2_.recording_sequence_id and filesequen1_.SEQ_ID=sequencevo0_.recording_sequence_id)) and sequencevo0_.StopTime<@p0] ---> FirebirdSql.Data.FirebirdClient.FbException: database file appears corrupt (C:\PROGRAMDATA\AXIS COMMUNICATIONS\AXIS CAMERA STATION SERVER\ACS.FDB) wrong page type page 3819 is of wrong type (expected 7, found 3) ---> FirebirdSql.Data.Common.IscException: Exception of type 'FirebirdSql.Data.Common.IscException' was thrown. at FirebirdSql.Data.Client.Native.FesDatabase.ParseStatusVector(IntPtr[] statusVector) at FirebirdSql.Data.Client.Native.FesStatement.Fetch() at FirebirdSql.Data.FirebirdClient.FbCommand.Fetch() *Example 2* Here is another example of a corruption. FirebirdSql.Data.FirebirdClient.FbException (0x80004005): unsuccessful metadata update MODIFY RDB$INDICES failed internal gds software consistency check (CCH_precedence: block marked (212), file: cch.cpp line: 4390) ---> unsuccessful metadata update MODIFY RDB$INDICES failed internal gds software consistency check (CCH_precedence: block marked (212), file: cch.cpp line: 4390) at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteNonQuery() *Example 3* Here is a file that got sent in from a user that complained that his system was no longer working. For this file it looks like one table in the database has two records with foreign keys that refers to non existing primary keys in another table (which we have constraints for), so how this data has entered a transaction is somewhat of a mystery to us: $ gfix -v -user SYSDBA -password masterkey acs_system_2014-06-17_16-17-33.737.fdb $ gbak -b -g -user SYSDBA -password masterkey acs_system_2014-06-17_16-17-33.737.fdb out.fbk $ gbak -c -user SYSDBA -password masterkey out.fbk restored.fdb gbak:cannot commit index FKA6F4437CE96F23CD gbak: ERROR:violation of FOREIGN KEY constraint "FKA6F4437CE96F23CD" on table "FILE_SEQ" gbak: ERROR:Foreign key reference target does not exist gbak:cannot commit index FKA6F4437C58FFBFFC gbak: ERROR:violation of FOREIGN KEY constraint "FKA6F4437C58FFBFFC" on table "FILE_SEQ" gbak: ERROR:Foreign key reference target does not exist gbak:Database is not online due to failure to activate one or more indices. gbak:Run gfix -online to bring database online without active indices. If anyone is interested I can provide you with more details or even complete database files for further investigation. We have loads of corruptions like these three. Best Regards //Jan Flyborg 2014-09-13 22:19 GMT+02:00 Alexey Kovyazin a...@ib-aid.com [firebird-support] : > > > Hi Jan, > > You did not tell what kind of corruption you had (please provide full text > of error). There are plenty of them, as well as reasons. > You also could use our tool FirstAID (Direct) to analyze database on low > level and see where are the problems. > > Regards, > Alexey Kovyazin > IBSurgeon (www.ib-aid.com) > > > > > Hi, > > We have shipped Firebird Embedded bundled together with our product for a > few years now and the system is currently in production at several thousand > of our customer's sites. Currently we are using Firebird Embedded 2.5.1 > with the latest .NET-driver and a stack consisting of Castle Active Record > on top on NHibernate and the system is running on the latest versions of > Windows. > > All is well and Firebird has served us good so far with the exception of > database corruptions that gets reported from a new set of customers every > week. For some of them it is possible to instruct the customer on how to > repair the databases themselves, but some of the databases are > unfortunately so heavily corrupted that they need to be sent to us for > repairing (which is a tedious work that steals time from other tasks). Most > of them corruptions are normally found in the tables that gets the most > writes, but I guess that is only natural. > > We are now at the planning stage for the next major release of our product > and we are thus rethinking if Firebird really is a good choice, because of > this. > > Lots of effort has gone into solving this problem on our side, so I think > the normal prerequisites has already been put into place (e.g using forced > writes and so forth), but our system needs to be up and ru
Re: [firebird-support] Firebird Embedded corruptions
Hi, Thanks for your answers. Please se my comments inline below. Best Regards //Jan Flyborg 2014-09-13 22:31 GMT+02:00 Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] < firebird-support@yahoogroups.com>: > Hei Jan! > > Hej! > The one thing I try to avoid, is running DDL (CREATE, ALTER, DROP > careful, but not all too long ago, a colleague caused some problems when he > did > > ALTER MyTable DROP MyField; > > while he simultaneously had another transaction having uncommitted changes > to MyField in one record. > > We never do that. All our database upgrades takes place just after our middleware has started and before we give any access to the other parts of the system, so this is probably not the explanation for our problems, since there is always just one transaction running when we are modyfying the data model. > I think (but have no experience), that possible reasons for corruption > could include file system backups of the database while it is in use > (exclude the database file(s) from such backups, rather use gbak for the > backup, and include the resulting file in the system backup), > Since we target a market consisting of normal end users it is hard for us to exclude our files from backups that our customers are performing. We can instruct them to do that, but we can never be sure that they follow our instructions. Also I can understand that the Firebird database files could become corrupted if you performed a (non-consistent) backup of them and then read back this backup into the production system, but we are seeing these corruptions on non backed up database files. > and possibly anti-viruses preventing Firebird from doing it's work (though > I would expect this to result in the database being unaccessible, not > corrupted). > > Yes I agree. The file locking would probably not corrupt the database file, but I am by no means any expert. > Another thing that's only affecting Fb 2.5.1, is that this version has an > error relating to compund indices (requiring backup/restore or rebuilding > such indices if upgrading to 2.5.2). Though I doubt this error would cause > data corruptions involving more than the index. > > We are going to upgrade in any case as soon as possible, so we will see if the problem will disappaer then. > Others will be able to give you a more thorough answer, despite having > used Firebird since it's inception (0.9.4), I've very little experience > with corruptions (undoubtedly related to only working on a handful of > databases with about 20 simultaneous users). > > That sounds promising to us. Everyone else seems to have good success with Firebird. > HTH, > Set > > >
Re: SV: [firebird-support] Firebird Embedded corruptions
Hi, Thanks for this. 2014-09-14 1:21 GMT+02:00 fabianoas...@gmail.com [firebird-support] < firebird-support@yahoogroups.com>: > > > If Windows has volume shadow copy enabled this always corrupt databases in > several ways. > Em 13/09/2014 17:32, "Svein Erling Tysvær > svein.erling.tysv...@kreftregisteret.no [firebird-support]" < > firebird-support@yahoogroups.com> escreveu: > Do you mean it would be enough to have the Windows Backup service (or any other backup program that uses VSS) to corrupt a live database or do you mean that the backed up files would be corrupted and the error would be produced when such a backup was read back into production? Best Regards //Jan Flyborg
Re: SV: [firebird-support] Firebird Embedded corruptions
Hi, Thanks for this. We will upgrade to 2.5.3 as soon as possible. I will also try to gather firebird.log from our customers in the future. Best Regards //Jan Flyborg 2014-09-15 12:52 GMT+02:00 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] < firebird-support@yahoogroups.com>: > > > Hi, > > 1. you use old FB version. 2.5.1 is not newest version > 2. Please include error message from FB log > > Regards, > Karol Bieniaszewski > > - Reply message - > Od: "fabianoas...@gmail.com [firebird-support]" < > firebird-support@yahoogroups.com> > Do: > Temat: SV: [firebird-support] Firebird Embedded corruptions > Data: niedz., wrz 14, 2014 01:21 > > > > > If Windows has volume shadow copy enabled this always corrupt databases in > several ways. > Em 13/09/2014 17:32, "Svein Erling Tysvær > svein.erling.tysv...@kreftregisteret.no [firebird-support]" < > firebird-support@yahoogroups.com> escreveu: > >> >> >> >Hi, >> > >> >We have shipped Firebird Embedded bundled together with our product for >> a few years now and the system is currently in production at several >> thousand of our customer's sites. >> >Currently we are using Firebird Embedded 2.5.1 with the latest >> .NET-driver and a stack consisting of Castle Active Record on top on >> NHibernate and the system is running on the >> >latest versions of Windows. >> > >> >All is well and Firebird has served us good so far with the exception of >> database corruptions that gets reported from a new set of customers every >> week. For some of them it is >> >possible to instruct the customer on how to repair the databases >> themselves, but some of the databases are unfortunately so heavily >> corrupted that they need to be sent to us >> >for repairing (which is a tedious work that steals time from other >> tasks). Most of them corruptions are normally found in the tables that gets >> the most writes, but I guess that is >> >only natural. >> > >> >We are now at the planning stage for the next major release of our >> product and we are thus rethinking if Firebird really is a good choice, >> because of this. >> > >> >Lots of effort has gone into solving this problem on our side, so I >> think the normal prerequisites has already been put into place (e.g using >> forced writes and so forth), but our >> >system needs to be up and running 24x7, which means that it is not >> possible to schedule periodic backup/restore cycles and my personal theory >> is that Firebird embedded gets >> >corrupted over time if you are not doing this regularly. >> > >> >So I have have a few questions that I would appreciate if someone could >> answer: >> > >> >1. Is it feasible to run Firebird Embedded 24x7 in a setup where there >> are no scheduled backup/restore cycles. If not, how often should this be >> performed to ensure that the >> >database does not get corrupted. >> > >> >2. Most of our customers are not using a UPS. From my experiments I have >> not managed to create a corrupted database by turning of the power while >> doing a large set of >> >writes (in a session running in VirtualBox). Could someone please >> confirm that this is indeed safe when you are running with synchronized >> writes turned on? >> > >> >3. Are there any operations on a live database that should be avoided to >> minimize the risk of corruptions? >> > >> >4. Just read a discussion about whether it is needed or not to call >> fb_shutdown to stop Firebird Embedded. Could this be the reason why we are >> getting corruptions? Should >> >we change our service to perform this call when it is stopped? >> > >> >5. I have also seen discussions of turning of automatic sweeps of the >> database (and doing them manually instead). Is this a likely source of >> corruptions for our setup? >> > >> >Thanks in advance. Maybe are there no certain answers to my questions, >> but any pointers in the right direction would be very appreciated. Firebird >> has been a real workhorse >> >for us and we would rather like to keep it. >> >> Hei Jan! >> >> The one thing I try to avoid, is running DDL (CREATE, ALTER, DROP >> > careful, but not all too long ago, a colleague caused some problems when he >> did >> >> ALTER MyTable DROP MyField; >> >> while he simultaneously had another transaction having uncommitted >> changes to MyField in one record. >> >> I think (but have no experience), that possible reasons for corruption >> could include file system backups of the database while it is in use >> (exclude the database file(s) from such backups, rather use gbak for the >> backup, and include the resulting file in the system backup), and possibly >> anti-viruses preventing Firebird from doing it's work (though I would >> expect this to result in the database being unaccessible, not corrupted). >> >> Another thing that's only affecting Fb 2.5.1, is that this version has an >> error relating to compund indices (requiring backup/restore or rebuilding >> such indices if upgrading to 2.5.2). Though I doub
Re: [firebird-support] Firebird Embedded corruptions
Hi, Thanks again. 2014-09-14 19:56 GMT+02:00 Ann Harrison aharri...@ibphoenix.com [firebird-support] : > > > On Sat, Sep 13, 2014 at 12:22 PM, Jan Flyborg jan.pers...@gmail.com > [firebird-support] wrote: > > >> >> Lots of effort has gone into solving this problem on our side, so I think >> the normal prerequisites has already been put into place (e.g using forced >> writes and so forth), but our system needs to be up and running 24x7, which >> means that it is not possible to schedule periodic backup/restore cycles >> and my personal theory is that Firebird embedded gets corrupted over time >> if you are not doing this regularly. >> > > Nice theory, but if the database is physically corrupt, you can't back it > up, and if it's logically corrupt, you can't restore it. I think it's > worth looking elsewhere for the problem. > Yes you are correct. I can see that now. > > So I have have a few questions that I would appreciate if someone could >> answer: >> >> 1. Is it feasible to run Firebird Embedded 24x7 in a setup where there >> are no scheduled backup/restore cycles. If not, how often should this be >> performed to ensure that the database does not get corrupted. >> > > It should be possible to run Firebird Embedded 24x7. Without knowing what > you're seeing as corruptions, it's very hard to guess why they're > occurring. What errors are your customers seeing? What do they (and you) > do to correct the errors? > I just made another posting where I tried to describe three different examples of things we have seen. It would be really nice if you could take a look at this. > >> 2. Most of our customers are not using a UPS. From my experiments I have >> not managed to create a corrupted database by turning of the power while >> doing a large set of writes (in a session running in VirtualBox). Could >> someone please confirm that this is indeed safe when you are running with >> synchronized writes turned on? >> > > A hard shutdown should not corrupt a database that has forced writes > enabled. It might corrupt the file system, but again, without knowing what > the errors and problem are, it's hard to guess. > >> >> 3. Are there any operations on a live database that should be avoided to >> minimize the risk of corruptions? >> > > Dropping tables and altering tables to drop fields are pretty dangerous > operations, but even if that is what's happening, the development group > should be given a reproducible case that corrupts databases. > As explained in a previous post, we never do that with other transactions running. > >> 4. Just read a discussion about whether it is needed or not to call >> fb_shutdown to stop Firebird Embedded. Could this be the reason why we are >> getting corruptions? Should we change our service to perform this call when >> it is stopped? >> >> 5. I have also seen discussions of turning of automatic sweeps of the >> database (and doing them manually instead). Is this a likely source of >> corruptions for our setup? >> > > No. Sweeping the database is very much like backing it up without creating > the backup file. When a sweep starts during heavy database usage, it can > reduce performance but not corrupt the database. > > So, question back to you: what errors are you seeing and how have you > fixed them? > > Typically we fix our problems with a gfix -mend and then doing a backup restore cycle. Usually some tables then still have problems (typically foreign keys that refers to non existing primary keys), so if possible we then remove the faulty records and then it works again. However, some database are so heavily corrupted that this strategy would give us an empty database and if that is the case we have to tell the customer to start all over again again with an empty file. Best Regards //Jan Flyborg
Re: SV: [firebird-support] Firebird Embedded corruptions
If you have VSS enabled in a drive where a live Firebird database is working this is enought to corrupt the database as you described in the examples. Also, antivirus softwares do this but ussualy only when the database became more than 1gb. We had various problems like yours. We "solved" by performing an obrigatory backup and restore cicly every day and check if all is correct. When we have a problem the system stops, the client call us and we instruct (again) to do not use vss or a external backup program or file copy or antivirus. This reduced our problems and always found a problem related with user configuration. We had only 2 corruptions related to a bad ram and a bad disk. The next step will be detect if an antivirus software is running and tell the user to add an exception to our application. Also we will not run when vss is enabled. Em 15/09/2014 08:32, "Jan Flyborg jan.pers...@gmail.com [firebird-support]" escreveu: > > > Hi, > > Thanks for this. > > 2014-09-14 1:21 GMT+02:00 fabianoas...@gmail.com [firebird-support] < > firebird-support@yahoogroups.com>: > >> >> >> If Windows has volume shadow copy enabled this always corrupt databases >> in several ways. >> Em 13/09/2014 17:32, "Svein Erling Tysvær >> svein.erling.tysv...@kreftregisteret.no [firebird-support]" < >> firebird-support@yahoogroups.com> escreveu: >> > > Do you mean it would be enough to have the Windows Backup service (or any > other backup program that uses VSS) to corrupt a live database or do you > mean that the backed up files would be corrupted and the error would be > produced when such a backup was read back into production? > > Best Regards > //Jan Flyborg > >
Re: SV: [firebird-support] Firebird Embedded corruptions
Morre info: a VSS corruption occurs when the vss is working or at this end. A VSS corruption occurs with Oracle database also by the way. Changing the database may not by an option, maybe... Em 15/09/2014 08:58, escreveu: > If you have VSS enabled in a drive where a live Firebird database is > working this is enought to corrupt the database as you described in the > examples. > > Also, antivirus softwares do this but ussualy only when the database > became more than 1gb. > > We had various problems like yours. We "solved" by performing an > obrigatory backup and restore cicly every day and check if all is correct. > When we have a problem the system stops, the client call us and we instruct > (again) to do not use vss or a external backup program or file copy or > antivirus. > This reduced our problems and always found a problem related with user > configuration. We had only 2 corruptions related to a bad ram and a bad > disk. > > The next step will be detect if an antivirus software is running and tell > the user to add an exception to our application. Also we will not run when > vss is enabled. > Em 15/09/2014 08:32, "Jan Flyborg jan.pers...@gmail.com > [firebird-support]" escreveu: > >> >> >> Hi, >> >> Thanks for this. >> >> 2014-09-14 1:21 GMT+02:00 fabianoas...@gmail.com [firebird-support] < >> firebird-support@yahoogroups.com>: >> >>> >>> >>> If Windows has volume shadow copy enabled this always corrupt databases >>> in several ways. >>> Em 13/09/2014 17:32, "Svein Erling Tysvær >>> svein.erling.tysv...@kreftregisteret.no [firebird-support]" < >>> firebird-support@yahoogroups.com> escreveu: >>> >> >> Do you mean it would be enough to have the Windows Backup service (or any >> other backup program that uses VSS) to corrupt a live database or do you >> mean that the backed up files would be corrupted and the error would be >> produced when such a backup was read back into production? >> >> Best Regards >> //Jan Flyborg >> >> >
[firebird-support] Select based upon date between two date fields
Hello,
I am new to firebird so excuse the possibly stupid question.
I am trying to select based upon a few criteria, one being that a date passed
in '9/15/2014' be between the values of two date fields in the firebird table.
Those would be fromdatetime and todatetime.
The values in the database for those two fields respectively are '09/01/2014'
and '10/23/2014'
The sql is below. It works fine until I add the BETWEEN statement. Hopefully
you can point out something simple that I am doing wrong.
select description, WOReqd, Addontype as AddonTypeID, RatebookID from
rentaladdons left join rentalratebook on rentaladdons.ratebookID =
rentalratebook.ratebookID where addonID = '1002' and cast('09/15/2014' as date)
between rentalratebook.fromdatetime and rentalratebook.todatetime
RE: [firebird-support] Select based upon date between two date fields
> The sql is below. It works fine until I add the BETWEEN statement. Hopefully
> you can point out something simple that I am doing wrong.
>
>
> SELECT description, WOReqd, Addontype as AddonTypeID, RatebookID FROM
> rentaladdons LEFT JOIN rentalratebook on rentaladdons.ratebookID =
> rentalratebook.ratebookID WHERE addonID = '1002' and cast('09/15/2014' as
> date) BETWEEN rentalratebook.fromdatetime and
> rentalratebook.todatetime
You are using the wrong approach, you need to try:
SELECT
description, WOReqd, Addontype as AddonTypeID, RatebookID
FROM rentaladdons
LEFT JOIN rentalratebook on rentaladdons.ratebookID
=rentalratebook.ratebookID
WHERE
addonID = '1002'
and rentalratebook.fromdatetime >= cast('09/15/2014' as date)
and rentalratebook.todatetime =< cast('09/15/2014' as date)
Sean
Re: [firebird-support] Select based upon date between two date fields
>> The sql is below. It works fine until I add the BETWEEN statement. Hopefully
>> you can point out something simple that I am doing wrong.
>>
>>
>> SELECT description, WOReqd, Addontype as AddonTypeID, RatebookID FROM
>> rentaladdons LEFT JOIN rentalratebook on rentaladdons.ratebookID =
>> rentalratebook.ratebookID WHERE addonID = '1002' and cast('09/15/2014' as
>> date) BETWEEN rentalratebook.fromdatetime and
>> rentalratebook.todatetime
>
>You are using the wrong approach, you need to try:
>
>SELECT
> description, WOReqd, Addontype as AddonTypeID, RatebookID
>FROM rentaladdons
> LEFT JOIN rentalratebook on rentaladdons.ratebookID
> =rentalratebook.ratebookID
>WHERE
> addonID = '1002'
> and rentalratebook.fromdatetime >= cast('09/15/2014' as date)
> and rentalratebook.todatetime =< cast('09/15/2014' as date)
Why is his approach wrong, Sean? It might be unusual to have the constant on
the left side of a comparison, but I'm sure I've done it when it is the easiest
way to write a query. I'm pretty sure BETWEEN AND should
work regardless of which dates are constant, parameters or field names.
I think his error has nothing to do with the BETWEEN, but rather the reason is
that he didn't only add the BETWEEN, he also added the LEFT JOIN and at least
RatebookID exists in both tables (probably giving him the ambiguous field name
error). He may just have assumed it was the BETWEEN that gave him the error,
rather than try the statement without the BETWEEN.
I'd change the query to:
SELECT ra.description, ra.WOReqd, ra.Addontype as AddonTypeID, ra.RatebookID
FROM rentaladdons ra
JOIN rentalratebook rr on ra.ratebookID = rr.ratebookID
WHERE ra.addonID = '1002' AND '09/15/2014' BETWEEN rr.fromdatetime and
rr.todatetime
(change ra to rr if I guess wrong which table some fields belong to)
LEFT JOIN is sometimes good to use, but in your case you use fields from
rentalratebook also in your WHERE clause, effectively turning the LEFT JOIN
into an [INNER] JOIN. [INNER] JOIN gives the optimizer more of a choice
regarding which PLAN to generate, so in this query, you're probably better off
not using LEFT (LEFT JOINs can sometimes be useful, but this is not one of
those cases). Moreover, there's no need to cast 09/15/2014 to date, it doesn't
harm doing it, but you don't need to.
One further thing to note, is that you use between and
. 09/15/2014 is between 09/14/2014 00:00:01 and 09/15/2014 23:59:59,
but it is not between 09/15/2014 00:00:01 and 09/15/2014 23:59:59. Moreover, I
would not trust that it was between 09/15/2014 00:00:00 and 09/15/2014
23:59:59, since I would be uncertain whether 09/15/2014 00:00:00 was an exact
value or an approximation that could be slightly less or slightly more than
09/15/2014.
HTH,
Set
RE: [firebird-support] Select based upon date between two date fields
> Why is his approach wrong, Sean? It might be unusual to have the constant > on the left side of a comparison, but I'm sure I've done it when it is the > easiest way to write a query. Agreed, that might be my own bias showing. Also, BETWEEN would not use indexes whereas my query would ;-] Sean
[firebird-support] Firebirdsql adding new charsets to database
Dear Firebirdsql Community, I want to add GB18030 charset to an existing firebirdsql database. How can I do that? Is that even doable, or will that require a new database to be created? Firebird database engine version: 2.5.2 Thank you! Regards, Vardan Minasyan Regards, Vardan Minasyan 949-645-1019 x2 If you need to send us a file, follow the link below. https://quickbooksconsultant.sharefile.com/r/r487ddfe0d9c490f8
Odp: [firebird-support] Select based upon date between two date fields
Hi,
First - what error message you get?
Regards,
Karol Bieniaszewski
- Reply message -
Od: "idea...@yahoo.com [firebird-support]"
Do:
Temat: [firebird-support] Select based upon date between two date fields
Data: pon., wrz 15, 2014 20:40
Hello,
I am new to firebird so excuse the possibly stupid question.I am trying to
select based upon a few criteria, one being that a date passed in '9/15/2014'
be between the values of two date fields in the firebird table. Those would be
fromdatetime and todatetime.The values in the database for those two fields
respectively are '09/01/2014' and '10/23/2014'
The sql is below. It works fine until I add the BETWEEN statement. Hopefully
you can point out something simple that I am doing wrong.
select description, WOReqd, Addontype as AddonTypeID, RatebookID from
rentaladdons left join rentalratebook on rentaladdons.ratebookID =
rentalratebook.ratebookID where addonID = '1002' and cast('09/15/2014' as date)
between rentalratebook.fromdateti
me and rentalratebook.todatetime
