[firebird-support] Firebird 3, nulls and integer division
Hi, I am (EVENTUALLY), getting around to upgrading from Firebird 2.5 to Firebird 3. I purchased the conversion guide, and so far so good, except for this problem, which I cannot find a mention of anywhere, no online, nor in conversion the guide: SELECT C.ENDDATE FROM CMN_PERIODS C WHERE (C.PERIOD = mod(:IPeriod, 100)) AND (C.YR = (:IPeriod / 100)) The error is: SQL error state =42000 Expression evaluation not supported. Invalid data type for division in dialect 3 Eventually, I figured out that this possibly happens on the Firebird 3 server when :IPeriod is null, since this now works: SELECT C.ENDDATE FROM CMN_PERIODS C WHERE (C.PERIOD = mod(:IPeriod, 100)) AND (C.YR = (coalesce(:IPeriod) / 100)) BUT, this also works! (ie. Does not give an error) SELECT C.ENDDATE FROM CMN_PERIODS C WHERE (C.PERIOD = mod(:IPeriod, 100)) AND (C.YR = (null / 100)) I am doing his via FastReports and FIBPlus datasets, so not sure if that is affecting things. The parameter type is set to Integer. I am going to go ahead and add coalesce to my 20 queries that have this in various reports, but would like to know if this is an intended side-effect (and my Google-Fu just not good enough to find the relevant documentation), and/or if there is some other better way I should be dealing with this problem rather (since I am not 100% understanding what has changed and why). Thanks Maya
RE: [firebird-support] FreeAdhocUDF and Firebird 3 not working
>I have been using Firebird 2.5.7 64 bit on Windows 7 for sometime, also using >FreeAdhocUDF. I just installed Firebird 3.0.2.32703 64 bit and now >FreeAdhocUDF is no longer loading. >I see that there is a support ticket in place for this exact bug, but so far >no resolution. This is a major problem as I will have to roll back to 2.5 >unless I find a work around. >Has any one found a resolution for this problem? I mainly use the F_DVL >function to get around null values, so I guess I can use a case statement (a >lot of work though). It would be nice to get FreeAdhocUDF working again, of >course. >Any pointers appreciated! I can’t see any reference to F_DVL on http://freeadhocudf.org/index_eng.html to see what it does, but can you not just use coalesce to get around null values? As of 2.5 I have managed to remove all dependencies I had on FreeUDFLib. Trickiest one to find a replacement function for was F_DIV() till I realised you just replace F_DIV(int1, int2) with (Int1 / Int2) and Firebird does integer division for you.
RE: [firebird-support] Firebird 2.5 rejecting connections
>>Can you connect locally by using TCP/IP(i.e.: using localhost or the >>server's own IP)? Just my 2c. We have also experienced this on a few PC's. A connection string of: localhost:c:\MyFolder\MyData.fdb gives the connection rejected error. Changing to: c:\MyFolder\MyData.fdb resolves the problem. We haven't managed to get to the bottom of it yet, we usually just end up using the local connection string, but it would be nice to finally know what's causing it instead of using the workaround. Unfortunately, once the workaround is in effect, we then don't end up with much time to scrutinise the person's PC to find out what the problem was.
RE: [firebird-support] Installing Firebird during the installation of my product
>I have a small application written in Delphi that uses Firebird, It is >intended to be used on a single computer currently. Possibly moving the >database to a server later which would mean installing firebird on the server >and then a separate install of Firebird on the client. >But for now I just need to setup Firebird on a single computer and would like >to do so silently if possible so my app is painless as possible to install. Yes, it’s very easy, client or server, just see http://www.firebirdfaq.org/faq193/
RE: [firebird-support] Inserting/Updating new records with data from previous records
>Alan Wrote: >Partno, del_qty no_boxes, (pallet_qty, qty_per_box - do not vary for each part) >ABC 200 10300 20 >ABC 200 10300 20 >ABC 100 5 300 20 >So far so good, but some of their customers require a second barcode label >attached to the outer pallet (the 300 above) >I need to produce 2 pallet labels as follows: >Partno, del_qty no_boxes >ABC 300 15 >ABC 200 10 You will need a field to say which pallet the items are in, for example palletno. Then, select partno, palletno, sum(del_qty), sum(no_boxes) from yourTable group by partno, palletno -Maya
RE: [firebird-support] Steps to Set Wait/No Wait Transaction
Hi Vishal,
>I need to understand how to set Wait/No Wait Transaction in firebird.
I think you mean in Delphi? Might be better to ask on the Firebird tools group,
but I have replied below
>Also I have one doubt that I when we start transaction through code as shown
>below, the transaction locks particular rows in the table or it locks entire
>table?
I think you will be fine as long as you use generator to get your ID’s if you
use auto-incremented numeric primary keys.
Delphi code snippet for FIBPlus would be:
sqlMain.Transaction.TPBMode := tpbDefault;
sqlMain.Transaction.TRParams.Clear;
sqlMain.Transaction.TRParams.Add('read_committed');
sqlMain.Transaction.TRParams.Add('rec_version');
sqlMain.Transaction.TRParams.Params.Add('nowait');
//try
sqlMain.Transaction.StartTransaction;
try
//Have Insert SQL Execution here
sqlMain.Transaction.Commit;
except on E: Exception do
begin
sqlMain.Transaction.Rollback;
raise;
end;
end;
FireDac would be quite similar.
Cheers
Maya
RE: [firebird-support] Speed of comparing one field to another
>Loius Wrote: >You could create a denormalized field that contains the difference of those >values (updated by a trigger, perhaps), and you can put >an index on that new field. Then change the query to find rows where [new >difference field] < 0. >Make sense? Thank you, looks like I will need to go that route
RE: [firebird-support] Re: How to do a running total in SQL
Norbert wrote: >execute block >returns (reference varchar(10), due double precision, balance double >precision) >as >begin >balance = 0; >for select >reference,due >from invoices into :reference, :due >do begin >balance = balance+due; >suspend; >end >end Thank you Norbert, that does look like the simplest solution till FB 3 is available.
RE: [firebird-support] How to do a running total in SQL
>>In Firebird 3 it will be possible with window functions. In Firebird 2.5 I >>don't know a way in pure SQL. >>Mark Great, thanks Mark. Cool, so I’ll be doing something like this, when Firebird 3 is available: select emp_no, salary, sum(salary) over (order by salary) cum_salary, sum(salary) over (order by salary desc) cum_salary_desc from employee order by emp_no; EMP_NO SALARY CUM_SALARY CUM_SALARY_DESC ==== = 2 105900.00 1990493.02 113637875.00 4 97500.001680929.02 113939039.00 28 22935.0022935.00115522468.02 121 9900.00 115522468.029900.00 145 32000.00113210.00 115441258.02 From: http://www.firebirdsql.org/file/community/ppts/fbcon11/fb3windowing.pdf
[firebird-support] How to do a running total in SQL
Hi, I'd like to add a running total to my result set. For example: Table: Invoices Reference Due Invoice1 50.00 Invoice2 30.00 Invoice3 20.00 I'm guessing SQL would be something like: Select Reference, Due, Sum_Total(Due) as Balance >From Invoices Desired Result: Reference DueBalance Invoice1 50.00 50.00 Invoice2 30.00 80.00 Invoice3 20.00 100.00 I know I can do this quite easily from within a selectable stored procedure, but the problem there is the running total won't be correct if a different sort order is specified, which happens often in my real world application. Is it possible to do this using just a simple SQL statement? Thanks Maya
[firebird-support] Possible to use FIRST 1 inside a group by?
Hi, I've got quite a tricky SQL query (well, at least for me it is, I'm hoping someone may have done this before) Here's a simplified example of what I'm trying to do: I have a table with IDGroupID FKCode Value 1 1 ABC+5 2 1 XYZ -5 3 2 ABC+8 4 2 XYZ -8 5 3 ABC-2 6 3 XYZ +2 I'd like to select the first record of each group, eg. IDGroupID FKCode Value 1 1 ABC+5 3 2 ABC+8 5 3 ABC-2 Any ideas? Thanks in advance Maya
[firebird-support] RE: Possible to use FIRST 1 inside a group by?
I've got quite a tricky SQL query (well, at least for me it is, I'm hoping someone may have done this before) Here's a simplified example of what I'm trying to do: I have a table with IDGroupID FKCode Value 1 1 ABC+5 2 1 XYZ -5 3 2 ABC+8 4 2 XYZ -8 5 3 ABC-2 6 3 XYZ +2 I'd like to select the first record of each group, eg. IDGroupID FKCode Value 1 1 ABC+5 3 2 ABC+8 5 3 ABC-2 I think I just solved my own problem. Might not be the most efficient method, but this works: select ID, GroupID, FKCode, Value from MyTable T where T.ID = (select first 1 T2.ID from MyTable T2 where T.GroupID = T2.GroupID order by T2.ID)
RE: [firebird-support] Re: MON$STATEMENTS question
I'm not sure if this is a problem or not, but I have quite a few entries like this with NULL transaction ID's in the MON$STATEMENTS table: Is this a sign that my code isn't cleaning something up properly, or is it perfectly normal? Any idea under what circumstances this will happen? i.e. if the statement is prepared but not currently executed, then its transaction id will be NULL in MON$STATEMENTS. Ah, thanks Dmitry, I checked it out, and yes it is prepared statements that are not currently open. I just never realised how many prepared queries I actually had - just did a quick select with count on the MON$STATEMENTS table, and I have on average 100 prepared queries per connected user. Is that not going to be affecting performance in a bad way? (I have classes that automatically construct the SQL, and sometimes it helps to have a prepared query, but a lot of them will only get re-used a few times - it will be quite an undertaking to change it, but if it's using up resources unnecessarily, it might be worth it) ++ 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
RE: [firebird-support] Re: MON$STATEMENTS question
Is that not going to be affecting performance in a bad way? Nope. Thanks Dmitry! ++ 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
[firebird-support] MON$STATEMENTS question
Hi, I'm not sure if this is a problem or not, but I have quite a few entries like this with NULL transaction ID's in the MON$STATEMENTS table: MON$STATEMENT_ID 460577 MON$ATTACHMENT_ID 128 MON$TRANSACTION_ID NULL MON$STATE 0 MON$TIMESTAMP NULL MON$SQL_TEXT Select abc from xyz where this = that MON$STAT_ID 421 Is this a sign that my code isn't cleaning something up properly, or is it perfectly normal? Any idea under what circumstances this will happen? Thanks Maya
[firebird-support] RE: Case insensitive search on a memo field
Thank you for all the suggestions hopefully, we're not talking about more than a few million rows in this table?... I'd say definitely not more than a million, probably around 15000 for the products tables, 5000 for the customer tables, and 100 000 for the jobs and orders tables. But, each memo could be 5 pages long (or more, theoretically). So, while the insensitive search is quite quick, even though it doesn't use an index, if I have to convert each and every line of all the memos to lower or upper case, it could make the existing search slow. PS. I'm using LIKE 'find me%' or like '%find me%' inside a stored procedure, depending on whether the end user ticks starting with or containing text in my software, so it's not easy to use containing and changing the middle of the SQL, hence I just use LIKE, even though it can't use an index. -Maya
[firebird-support] Anyone know what has happened to FIBPlus
Hi, Does anyone have any idea what has happened to FIBPlus from DevRace for Delphi? They haven't brought out any updates for a year now, not since Delphi XE4. They aren't responding to support tickets, or emails either. Thanks Maya
[firebird-support] Do not recognize record type 28
Hi, I am trying to restore a backup using Firebird 2.5, and am getting the following error: --- Omni Control Centre --- Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements. Do not recognize record type 28. --- OK The log at that point is showing: gbak:restoring parameter OPREVSTMNTAMNTINCL for stored procedure gbak:restoring parameter OSTMNTAMNTINCL for stored procedure gbak:restoring parameter ONEXTSTMNTAMNTINCL for stored procedure gbak:restoring parameter OINCLLOCALAMNT for stored procedure gbak:restoring parameter OPROCESSEXCHANGERATE for stored procedure gbak:restoring parameter OPROCESSEXCHANGERATETYPE for stored procedure I tried googling the error, but came up with absolutely nothing (except a similar bug on record type 0 that was fixed in 2.5 BETA) Anyone have any idea what might be causing this, or how/where to start troubleshooting? Thank you Maya
