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.PER
>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 pr
>>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
>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
>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
>
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
>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
Hi,
I have a large table, and I'm only extracting a few records using:
Select t.ID
>From my_table t
Where t.Invoiced < t.Received
Invoice and received are of type decimal(18,5)
IBExpert is reporting these as unindexed reads.
Is there an index I could create to speed up the loading?
Thanks in
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 th
>>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) ov
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 In
Thank you for the help everyone!
I think Sean's looks the most efficient, as that will only run the sub-query
once.
Now that I know how to use this trick, I think I could use this in quite a few
other places too ;-)
>Try:
>SELECT
> T.ID
> T.GroupID
> T2.FKCode
> T2.Value
>FROM (
>sele
>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
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
>> Is that not going to be affecting performance in a bad way?
>Nope.
Thanks Dmitry!
++
Visit http://www.firebirdsql.org and click the Documentation
>> 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
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 = t
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
Hi,
I'm using Firebird 2.5.3, and I am looking for text in a emo field, eg.
Select * from mytable t where t.mymemo like '%find me%'
The will only find
"find me"
and not
FIND ME or
Find me or
Find Me etc
I'm tempted to do something like
Select * from mytable t where lowercase(t.mymemo) like
>>I have FB 2.5.3 on a laptop. Win 7 32 bit.
>>I introduced a stored procedure and in it call F_LEFT(:varName, 24) to
>>truncate :varName from a vc(48).
F_LEFT belong to FreeUDFLib which you need to install.
Since you are already using FB2, it’s much easier to just use the built in
LEFT() func
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
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 reco
Nowadays, I'm very particular about what I sign up for. Once in a while I go
through my gmail account (with about 800 unread mails at the moment) and
unsubscribe from anything I haven't managed to read in the past year, no matter
how much I really wanted to get to doing so.
If a poll does not l
We chose Firebird ourselves, and love Firebird.
We use Windows though, not Linux, but we do have a few sites that have chosen
to use a Linux server. Haven't voted on the poll, as we really have no Linux
related question to ask, so don't qualify to vote on that poll.
Have added a testimonial to
1. Your insert is failing, but you haven’t provided us with the SQL you
are using for an insert. Also, check the refresh SQL property of your DB
component.
2. I notice you have an ; at the end of all your SQL. I don’t think that
is necessary when entering the SQL into the SQL proper
By the way, google's query language has a PIVOT syntax:
https://developers.google.com/chart/interactive/docs/querylanguage#Pivot
Eg.
select sum(salary) pivot dept
Would be nice if Firebird could do the same, I understand you couldn't use it
inside a stored procedure, where your columns need t
>>when I restore FK I foun some records that break referential integrity. I
>>delete this records and create FK.
I have had 2 or 3 databases now, where the primary key index gets corrupted.
Because the system used the corrupt PK to check, it doesn’t find a certain
value, and allows the duplic
>> Mark Rotteveel wrote:
>>If both are DATE or both are TIMESTAMP everything is OK. When one is a DATE
>>and the other is a TIMESTAMP it breaks down
Aha, thanks, have fixed by casting the timestamp to a date:
when ((p.StartDate <= cast('Today' as date)) and (p.EndDate >= cast('Today' as
date)))
>I managed to get around it as follows, but I'd say it is a bug:
>
>What I wanted to do:
>
>case
> when ((p.StartDate <= cast('Today' as date)) and (p.EndDate >=
> cast('Today' as date))) then
>(cast('Today' as date) + 1)
> else
> p.enddate + 1
>
>Mark Rotteveel wrote:
>I am unable to reproduce it. Could you create a simple reproduction script
>that creates the table, adds the testdata and includes the query?
>My reproduction is:
>CREATE TABLE int_date_table
>(
> intfield INTEGER,
>datefield DATE,
> timestampfield TIMESTAMP
>);
I managed to get around it as follows, but I'd say it is a bug:
What I wanted to do:
case
when ((p.StartDate <= cast('Today' as date)) and (p.EndDate >=
cast('Today' as date))) then
(cast('Today' as date) + 1)
else
p.enddate + 1
end
What I
Hi,
Is this a bug, or am I missing something?
This works:
case
when p.year = 2200 then p.year
else p.year
end
This gives error: Datatypes are not comparable in expression CASE
case
when p.year = 2200 then p.EndDate
else p.EndDate
> The question is - do global temporary tables have performance
> advantages over usual tables?
I'm not sure if querying a regular table is faster or not, but I use temp
tables for speeding up complicated queries that need to use the same result of
a complex calculation several times over. So
>I have a Firebird 1.5 database, which somehow does not have NOT NULL ticked
>for the primary key field of a few tables.
>When I restore the database on Firebird 2.5, it just silently drops the
>primary keys.
>I have found and corrected 2, but suspect there may be more, which could cause
>some
Hi,
In have a Firebird 1.5 database, which somehow does not have NOT NULL ticked
for the primary key field of a few tables.
When I restore the database on Firebird 2.5, it just silently drops the primary
keys.
I have found and corrected 2, but suspect there may be more, which could cause
some
>If you are using just standard query, you might be able to force using ibx,
>but i suggest you changed it to FireDAC or dbexpress, it has much better
>support for firebird spesific syntax.
Or FIBPlus (which is based on the original IBX source code so is quite
similar), or IBObjects.
>> Got an interesting question from one of my customers:
>> "The backup runs in 1 secs and 2 secs for zipping the file but the
> >restore takes 1min 32secs. Why is there such a difference in the time?"
> >Well, restoring obviously has to do a lot more work, as it has to
> >rebuild indices, and da
Hi,
Got an interesting question from one of my customers:
"The backup runs in 1 secs and 2 secs for zipping the file but the restore
takes 1min 32secs. Why is there such a difference in the time?"
Well, restoring obviously has to do a lot more work, as it has to rebuild
indices, and database stru
>>I have installed firebird 1.5 with a zip file and delete the bat files. I
>>need to uninstall and download a newer version, in order for Omni accounts to
>>work.
Hi Elize,
You need to follow the Omni upgrade instructions we gave you.
And follow instructions received from Omni support, not
>>PS. if there are brand new machines, I'd put 64 bit windows on, then run 32
>>bit applications. That way your PC can utilise more memory.
>Unfortunately not. A 32-bit application can't use more than 4GB, regardless
>of how much RAM the 64-bit machine has.
The way I understood it, is each 32
If you connect on the server to localhost, is there still a problem?
Are you using a GDB or FDB files extension. GDB has issues.
Also, check you just have one instance of Firebird/Interbase running.
Make sure your network cables are the right ones for your network's speed.
Make sure your anti-
>>first latest version of FB 1.5 series is Firebird-1.5.6.5026-0-Win32.exe -
>>try it and you will see if problem still exists
>>and really you should migrate to most recent major version
Agreed. I had an index corruption issue that was only resolved in FB 2.5
>> If you fail to do the rebuild, and you run into the bug, what actually
>> happens? Do you get some kind of error message that can be recognised
>> by my software, and added to to tell the end user what to do to fix it?
>If you use that database with FB 2.5.1 or FB 2.5.2, then an error is thro
Helen wrote:
>Normally, no. But I suspect this thread might be concerned with the problem
>that happened with compound indexes in v.2.5.1, that was >corrected in 2.5.2.
>If you had a database with compound indexes that was created or restored to
>2.5.1 you need to do either a full restore or a
>> SOURCEID INTEGER,
>> SOURCETYPE VARCHAR(15),
>You told us, that SOURCETYPE is not a [VAR]CHAR but a numeric type. Ts, ts, ts
>...
Ahhh, thank you, thank you, thank you
I must be going cross-eyed and looked at the one above by mistake - readi
>>Can you extract the table's full DDL and post here?
CREATE TABLE STK_STOCKTRANSACTION (
ID INTEGER DEFAULT 0 NOT NULL,
TRANSACTIONTYPE VARCHAR(6),
STOCKCODEVARCHAR(25),
SALESORDERNO VARCHAR(15),
UOM
> Running SET STATISTICS on this particular index
> Query RDB$INDICES.RDB$STATISTICS for this index
No Change: 0.049277592
>Also you can generate a histogram of the used values/permutation with:
>select
> sourcetype
> , transactiontype
> , refno
> , count(*)
>from
> stk_stocktransac
>> select t.SalesOrderNo from stk_stocktransaction t where (t.SourceType
>> = 34) and (t.TransactionType = 'EXTCST') and (t.RefNo = 'I1')
>>
> >Plan is:
> >PLAN (T NATURAL)
>>
> >Yet I have the following index: SourceType, TransactionType, RefNo asc
>>
> >With selectivity: 0.049
>The
> Hi,
>
> This is really weird.
>
> I cannot see why a NATURAL plan is being used instead of my index, I am not
> doing anything fancy.
>
> Using: FB2.5.1 32 bit classic server
>
> Query:
>
> select t.SalesOrderNo from stk_stocktransaction t where (t.SourceType
> = 34)
>and (t.TransactionTyp
Hi,
This is really weird.
I cannot see why a NATURAL plan is being used instead of my index, I am not
doing anything fancy.
Using: FB2.5.1 32 bit classic server
Query:
select t.SalesOrderNo from stk_stocktransaction t
where (t.SourceType = 34)
and (t.TransactionType = 'EXTCST')
and (t.Ref
> Now, I wonder if something similar could actually be done internally by
> Firebird for the general like case?
That gets my vote!
Hi Guys,
Question regarding preparing queries inside stored procedures, inspired by:
"Re: Query is running slowly for the first time."
In that post, Thomas( I think ?) reckoned the slow down on large tables, is
form the query becoming unprepared.
I have a selectable stored procedure, that call
>
>
>
>
>> Also. I know windows 8 isn't officially out yet, but was Firebird
>> running on the beta versions of windows 8?
>
> I am not sure if anyone has tested on Win8.
>
>
> I have Windows 8 and Firebird on my home PC, and so far haven't found any
> issues
>> Thanks for the heads up, Interbase seems to be getting more popular
>> again. I found have IB services running on my PC, and I have no idea
>> what software put it there!
>RAD Studio XE3 does that, AFAICS.
Thanks, but this is on my laptop, which doesn't have Delphi on it. It doesn't
have mu
>>We have some problem with gds32.dll that it installed on my pc. It doesn't
>>work with firebird 2.1.x
Thanks for the heads up, Interbase seems to be getting more popular again. I
found have IB services running on my PC, and I have no idea what software put
it there! What error message do you
Attributes force write, no reserve
>>> Having "no reserve" isn't a good option for a regular read/write production
>>> database, because this basically prevents having back record versions on
>>> the same page as the primary record version, thus additional page reads
>> Attributes force write, no reserve
>Having "no reserve" isn't a good option for a regular read/write production
>database, because this basically prevents having back record versions on the
>same page as the primary record version, thus additional page reads are
>necess
>> >Get the statistics on the various runs of the sub-procedure- reads,
>> writes, fetches, and marks.
>> No writes. 47 000 indexed reads.
>Lets try that one again. I'd like both reads and fetches, with statistics
>for a run of the subquery that's fast and one that's slow. Did you mean
>that c
>> If I run the sub proc to get the balance one by one though, it runs
>> at
>> 1-2 seconds. So, using IBExpert, I manually worked my way through
>> the list, and on the 5th account - 22 seconds to execute. I try the
>> very same account again, and it takes 1 seconds. I need to carry on
>> d
>> If I run the sub proc to get the balance one by one though, it runs at
>> 1-2 seconds. So, using IBExpert, I manually worked my way through the
>> list, and on the 5th account - 22 seconds to execute. I try the very
>> same account again, and it takes 1 seconds. I need to carry on down
>>
>>How can I get the folder where FireBird is installed.
>>I use Delphi and I want to copy the UDF folder after a silent installation.
Not sure if this is the best method, but I read the DefaultInstance registry
key. It work for me for FB 1.5, 2.1 and 2.5:
RootKey := HKEY_LOCAL_MACHINE;
Found
>>I have a stored proc that calculates account balances. Most of the time it
>>takes between 1-2 seconds, but every now and then it takes 22 seconds.
>>It seems to be approx. every 5 or 6 records.
>>Ie. I have a stores procedure that returns a list of 6 accounts and their
>>balances.
>>I have
>>I'm evaluating the choice of migrating a 1.5 Firebird database either to 2.1
>>or to 2.5. I've readed the release notes of both versions but I can't find no
>>elements that help to decide which one could be best choice.
Personally, I upgraded from 2.1 to 2.5.1 because of the new features I ne
Hi,
I have a stored proc that calculates account balances. Most of the time it
takes between 1-2 seconds, but every now and then it takes 22 seconds.
It seems to be approx. every 5 or 6 records.
Ie. I have a stores procedure that returns a list of 6 accounts and their
balances.
I have a sub p
Hi,
According to Stefan Heymann's post here:
http://www.destructor.de/firebird/minimumclient.htm
The minimum required for a client installation, is just FBClient.dll (and the
MS visual studio runtimes)
I am getting a lot of Firebird.msg not found errors though, when I try deploy
just the 2.5.
>> I was under the impression that copying a database that is currently
>> in use can cause a corruption because windows xcopy might deny the
>> Firebird server access to that file momentarily. I was looking for an
>> official statement on this for a customer of mine and found this:
>> http://w
Hi,
I was under the impression that copying a database that is currently in use can
cause a corruption because windows xcopy might deny the Firebird server access
to that file momentarily. I was looking for an official statement on this for a
customer of mine and found this:
http://www.firebir
>Another observation that we made is with transactions. Tae following example -
> 1> insert hundred records in a table
> 2> after each record is inserted, commit the transaction.
>Now these steps give different output on different machines. On Windows 7 and
>XP they give results in 1.5 seconds.
>>Until recently I was using version 2.0 and now I use 2.5. I encountered a
>>problem that bothers me, is about the message when they enrolled in >>the
>>database when the condition is not met foreign key. In version 2.0 messages
>>contained the names of tables and foreign key, now in >>version
>>isql.exe -database
BG4WS0531:D:\RiTEQFireBird\FB_Backup\TIMETEQ-FIREBIRD-28082011-CLEAN.fdb
-user 'sysdba' -password 'masterke' -i
c:\Scripts\spLabourBudget_Assign.sql;
>>while executing the script I am getting "Unable to open
>>c:\Scripts\spLabourBudget_Assign.sql;" , please guide me why I
Hi,
Is it possible to indicate in a SQL statement whether a blob contains data or
not, without retrieving the entire blob, and thereby making the query very slow?
Eg. If I did:
select
S.StockCode,
(case S.StockImageBlob when null then 'No' else 'Yes') as HasImage,
(case S.StockMemoBlob whe
71 matches
Mail list logo
