[firebird-support] IMPORTANT MESSGAGE: This group is moving

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Hello Firebird-support members,

The firebird-support list is moving to Google Groups. The new location is
https://groups.google.com/d/forum/firebird-support

Unfortunately we cannot migrate the current Yahoo! subscribers, and you will
need to resubscribe yourself, in one of two ways:

(1) If you have a Google account, log in to Google, go to the group's home page
(https://groups.google.com/forum/#!forum/firebird-support;context-place=overview),
Click the link labelled 'Join this group' and follow instructions there.

(2) Without a Google account, you can apply to join by emailing this
address using the email account you want to use for communicating with
the group:
firebird-support+subscr...@googlegroups.com
You should receive a confirmation email.  Respond as directed to
complete. (this does not require a Google account).

For posting to the list you can use firebird-supp...@googlegroups.com

Note, if you are subscribed to more than one Firebird-related Yahoo!
group, your will need to go through a similar process for each of your
groups.

Helen Borrie
for Firebird Project



-- 
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] run firebird engine without fbguard

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Yassir Kozha wrote:

>  We currently use fbguard to run the firebird engine as recommended in the 
> docs of firebird.

> Systemd service unit:
>  ‘ExecStart=/usr/sbin/fbguard -daemon -onetime’

>  

> The problem with this approach is that when killing the firebird
> engine process ‘SIGKILL’, the fbguard exites with ‘0’. In this case
> systemd doesn’t get that the firebird process was terminated abnormally.

[..]
>  

> Is it possible to run firebird engine as a daemon without the use of fbguard?

The Guardian is an optional process for Superserver and SuperClassic.
If enabled, it watches the firebird server process and restarts it if
it fails for some reason.  Guardian is not applicable to Classic at
all.

I don't have the knowledge to give you the script to run the server
without fbguard in the Linux/systemd environment but, on Windows, you
simply remove the Guardian service and the Firebird server process
will then run independently.  The way to achieve the same in your
environment is probably just as simple - I hope you will ask
again with a targeted question if you need help with this. I think you
might get some clues from the install script, if you installed from
the official Firebird kit.

Helen


>  

> Kind regards,
> Yassir

>  
>
>
>  
> 



-- 
Best regards,
 Helenmailto:hele...@tpg.com.au


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Connecting to database on remote server

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Tony Christiansen wrote:

> I am running an app on my PC that connects to a database on my firebird
> server FIREBIRDSERVER

> The path is \\FIREBIRDSERVER\E:\DB.FDB

> If my pc has an E: drive (ie I plug in a usb) I can connect to the database.

> If my pc has no E: drive then I cannot connect to the database and get
> the error - unable to complete network request to host "e"

> Why does it matter what physical drives I have on my local machine? Is
> this a quirk or design (I am no DBA expert)

For a remote connection, it is irrelevant what physical drives are on
the client machine - unless, of course, the client application is
running from the host server itself.  There are two ways to make a
"remote" connection in this environment: through the local TCP/IP
loopback (localhost, IP address 127.0.0.1) or through the XNET
server.  But, if I read you right, you are trying to connect to a
server from an application running on a PC that is physically remote
from FIREBIRDSERVER.
.

> Where is an explanation of the different connection strings.

In the Quick Start guide (in the \doc sub-directory) amongst many
others.

> The path is \\FIREBIRDSERVER\E:\DB.FDB

That connection string tries to use the WNET (Windows Networking)
transport layer.  The equivalent TCP/IP path is

FIREBIRDSERVER:E:\DB.FDB

> I am using a TIBDatabase in Delphi
> With databasename property set to \\FIREBIRDSERVER\E:\DB.FDBit connects
> With databasename property set to FIREBIRDSERVER:E:\DB.FDBit does
> not connect with error  unable to complete network request to host 
> "FIREBIRDSERVER"

> My environment is windows and without the \\ the client cannot find the host.

It looks as though your application is compiled with the wrong
transport layer settings in TIBDatabase (the Protocol property), which
would be set to one of cpLocal, cpNetBEUI or cpTCP_IP.

cpLocal represents the old interface known in Delphi circles as
"Windows local". That transport in Firebird is known as XNET, and Fb3
does not support it directly any more from the combination of Protocol
cpLocal and the local pathname.  The string now has to be like this:

xnet://FIREBIRDSERVER:E:\DB.FDB

However, that is a "by-the-by", since you are not aiming
(intentionally) to make an XNET connection. So my guess is that your
application is trying to connect using the cpLocal transport - hence
the reason it is barfing on the path to an E: drive that it cannot
find on the local box; and succeeding when attempted locally on the
server host.  And, of course, when it does find an E: drive on the
client machine, there is no Firebird server running locally and no
database to connect to.  So you get 'unable to complete network
request to host "FIREBIRDSERVER".

If you want to make a WNET connection, using the string you have been
using, set the application's Protocol property to cpNetBEUI.  TCP/IP
(cpTCP_IP) is preferred, however, as WNET is notoriously noisy.  You
must set up the Protocol and DatabaseName properties as appropriate to
each other and to the transport you want to use.  If you get it right
on the application side, Fb3 should be able to sort it out on the
server side.

Helen




---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] FB 2.5.8 - Next transaction value growing too much.

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Hernando Duque wrote:

> I've working around tuning up a database speed and found that gstat
> -h shows valuable information about transactions. Also that gfix
> -sweep removes garbage from the database.

Sweep (whether automatic or manual) removes garbage that is able to be
removed.

> I understood that when Next transaction grows too much from Oldest
> active transaction, "sweeping" the database makes this difference become 
> smaller.

No, the 'Next' transaction is not relevant.  It is simply the ID that
will be assigned to the next transaction that will be started.  Of
course, gstat only provides you with a snapshot of the state of
transactions, as read from the transaction inventory page (TIP) when
gstat was run.

> But this is not the case with my database.

It would not be the case with any database that was being used, except
perhaps right after a restore from a gbak backup.  In a newly restored
database, transaction id numbers start from 1.

> What am I doing wrong?

Let's look at your gstat -h results and decide whether you are
actually doing anything wrong. ;-)

> this is the gstat -h from the database:
> *

> Database "d:\med6\ibdata\sistmed6.fdb"
> Gstat execution time Mon Jul 01 10:39:55 2019

> Database header page information:
> Flags   0
> Checksum  !  ; 12345
> Generation  236628301
> Page size   4096
> ODS version 11.2
> Oldest transaction  236413964
This is the "oldest interesting transaction". a.k.a., OIT. An
"interesting transaction" is one that, although already committed or
rolled back, involves records that are still being actively worked on
by other transactions.  As long as a transaction remains
"interesting", the records associated with it cannot be
garbage-collected.

> Oldest active   236413965
a.k.a. OAT, this is the oldest transaction that was still active when
gstat -h was run.  An "active" transaction is one that has not yet
been committed or rolled back.

> Oldest snapshot 236413965
This is the value of the OAT the last time garbage collection (GC)
happened.  On successive runs of gstat -h, the difference between it
and the OIT (known as "the gap") is an indicator of how effectively
the transactions are being managed by your applications.  The bigger
the gap, the more likely it is that oneor more long-running
transactions are inhibiting GC.
> Next transaction236627938
> Bumped transaction  1
> Sequence number 0
> Next attachment ID  31194
>!   Implementation ID   16
> Shadow count0
> Page buffers0
> Next header page0
> Database dialect3
> Creation date   Mar 24, 2018 10:27:14
> Attributes  force write

> Variable header data:
> Sweep interval: 2
The sweep interval determines how big the "gap" should be in order for
the engine to register that an automatic sweep is needed.  With this
(the default) configuration, the need for the auto-sweep will be
registered when the gap hits 20,000.  Auto-sweeping is done by a
worker thread and will run when the engine detects a suitable lull in
operations.  In a well-tempered database (as yours appears to be), an
autosweep will never occur because that gap simply never reaches that
size.
> *END*
> *

Your numbers, as shown here, look fine, especially considering you
have not done a restore for 15 months. ;-)

BTW, sweeping is not garbage collection.  It is a process that marks
old record versions for GC, which will be carried out subsequently by
a GC worker thread (on Superserver) or by cooperative GC, or both,
depending on how your GCPolicy is set up in firebird.conf.  GC happens
automatically, one way or another. A gbak backup also causes
cooperative GC, unless you run gbak with the -[no_]g[arbage_collect]
switch.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird 3.0 performance, Windows 10 vs Mint, big different

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Anto wrote:

> Thanks for your info.

> Downloaded ver 1.52, now it worked.

> On Process tab of RAMMap, firebird.exe took 2,183,484K.
> On File details tab, database file took 2,603,168K.

> Don't know, if this means file cached work or not.

> If file cached  worked, then there is something else that make
> Firebird runs slower on Windows 10.

It is worth reading this:
http://dyemanov.blogspot.com/2012/03/firebird-vs-windows-file-system-caching.html

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ** NOT ** Re: [firebird-support] Firebird upgrade advise

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Rita Liotta wrote:

> Hi, Helen:
>   
> Can you name some of these tools? I'm just floundering try to find one.
>   
Only you can know what you need, according to your expertise with
databases and SQL and your comprehension of the drivers needed to use
a particular toolset.  As a rule, the free tools and editions will have
fewer (or no) features for someone with minimal understanding.
Commercial products will be more likely to be helpful to such users.
Database Workbench has a long history and a good reputation and offers
a trial version and user support.

Tools recommendations are off-topic in this list, which is for actual
database support.  You might like to join the firebird-tools list
(this URL, scroll down: https://firebirdsql.org/en/mailing-lists/).
Try to explain what you need and someone there may be able to point
you where you need to go.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

** NOT ** Re: [firebird-support] Firebird upgrade advise

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Rita Liotta wrote:

> I don't know what happened, but this isn't my question.

What happened is that you replied to a thread that was on a different
subject - it is called "hijacking".  You should have started a new
topic.

> What I asked was
> if there was any way I can access and sort the attached file without 
> having Firebird installed.

There is no "attached file".  This list does not accept attachments.

> I am not a Firebird user. I simply have an
> ...fdb file that I would like to alphabetize.

Your Firebird file contains a relational database built with the
Firebird engine.  A database is not a list of things that could be
"alphabetized".  It is very different from a spreadsheet, being
entirely abstract, not human-readable, and managed by the Relational
Database Management System (RDBMS) which, in this case, is Firebird.

Data are extracted from this abstract system using a formal query
language called SQL.  One uses SQL queries to define the properties of
sets of data for extraction as sets of rows made up of one or more
columns (a.k.a. fields).  "Alphabetization" of an output set, for
example, can be requested using an ORDER BY clause on an alphanumeric
column (field) in an underlying table.

> I don't have Firebird, just
> the file. The fdb is from an entirely different app.

If one understands the structure of a relational database and knows
Firebird's SQL language, one can use a tool to connect to a database
and query one or more tables in various ways -- but not, however,
without the client application or tool connecting to the database
through the Firebird engine.  There are various client tools out there
through which you can run queries and do stuff with the output;  some
will even enable you to compose a query interactively.  See
https://firebirdsql.org/en/third-party-tools/. Some tools can
even cross the boundaries of different database engines, making it
possible, e.g., to read a Firebird database from a MSAccess client,
through an ODBC driver layer.

Ultimately, though, some sort of driver connects to the Firebird API and
the Firebird API manages the connection of the database to the
Firebird engine.  There is no workaround to avoid having the Firebird
engine in some form at the back end.

Helen



---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: Issue with large table in FB 1.5 and 2.5

[Helen Borrie hele...@tpg.com.au [firebird-support]]

> Also one further question  Do later versions of Firebird (ie. 3
> or 4) have any performance increase for cooperative garbage
> collection at all?

No.  GC requires reading the version chain of all records in the
contexts of their transactions.  It takes as long as it takes.

> Would I expect to see any performance improvement by any newer
> version,

No.
> or different server implementation here?

Possibly...with Superserver you can can configure for GC to run in
background;  or as a combination of background and co-op.  Its
performance would have to be tested with your machine's resources,
otherwise it's just one of those "How long is a piece of string?"
questions.

One idea that might give you some time saving (again, testing with
actual data is the only way you're going to find out) is to define
your table as a GTT with the same metadata as the one you are
currently using and the ON COMMIT PRESERVE ROWS property.  Remove the
dependencies from the current table and just use it for data collection.

Before you run your stored procs, feed the data from the collection
table into the GTT and have the SPs read from that instead.  Deleting
records and dropping the GTT is not in the picture, as the data in the
GTT will simply disappear when the connection detaches.  Then you can
just drop and recreate the collection table at will without affecting
any dependencies.

Of course, if you are still using Fb 1.5 for some sites, it's not
going to work for them.  GTTs didn't come on the scene till 2.1. Also,
it won't work in 2.1 +, either, if you have not upgraded the ODS of
the databases to 11.1 or higher.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Issue with large table in FB 1.5 and 2.5

[Helen Borrie hele...@tpg.com.au [firebird-support]]

[..]
> My code that loads the data commits the transactions after the
> DELETE statement, and then at each 5,000 rows loaded.  I have
> checked this by watching the load and using IBExpert to count
> records, so I can confirm that the commits are happening.

> After the loading has completed, what then occurs is strange.  Any
> attempt to interact with that table (and only that table) forces
> some form of sweep or some delay that shows up as a process on the
> server running at near 100% CPU, for about 4-5 hours.  Then it calms
> down and access to the table is pretty much instantaneous.  I
> thought that this might be a sweep that is occurring after the load,
> so I set the database to not auto sweep.

No, it is not sweeping, it is cooperative garbage collection and
that's exactly how coop GC works.  In the daily ebb and flow of your
production tables, it is barely noticeable but GC on those bulk
deletes will hit the next transaction that reads from your offending
table.

BTW, background GC (memntioned by Mark) is not available in Classic or
Superclassic, so configuring for this won't do anything.

> I have force dropped the  table, and rebuilt it.

That is your solution - but *do not delete those records*.

> I have done a backup with sweep and a restore to refresh the database.

That's extreme unless you do a daily backup-and-restore anyway...but
if you are restoring the database anyway, you don't need to have the
backup doing GC - use the -[no_]g[arbage_collect] switch.  On Linux
you would also need to make sure that subsequent connections are made
to the restored DB, not continuing to be done on the old one.

> Nothing seems to make any difference.

No, none of those things would make any difference as long as you are
deleting those records and thus creating a large number of old record
versions.

Incidentally, if this table is the only one you have that is building
up that level of old record versions, setting the sweep interval to 0
is the right thing to do, regardless.

Then, of course, you must ensure that backups (without the -g switch)
and/or sweeps are done often enough to keep the "gap" in a comfortable
state.  Also, you don't need to do *both* a backup (sans -g) and a
sweep, since the backup achieves the same thing as sweep at the end of
it all.  Of course, it is moot as to whether sweeping before the
backup, thus taking the GC out of gbak's hands, would gain you
anything in terms of the time this housekeeping would take.

Helen



---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: Engine12 32 64 bit question

[Helen Borrie hele...@tpg.com.au [firebird-support]]

a...@liberov.net wrote:

> Thanks a lot! 

> When everything else fails read the manual. 

> I put xnet:// in front of the local path and it connects now.
> The IBDAC has a property to specify library location. I did put the
> path to 32 bit fbclient, but until I put xnet:// in front of the
> path the wrong engine12 would be used.

Actually, what threw everyone into confusion was your references to
"local connection".  When I read your second posting yesterday morning
the penny dropped, that you were talking about an XNET connection. I
was about to jump in when I saw that Vlad had spotted what your real
problem was and had steered you in the right direction.

The "local" argument for the connection protocol Delphi database is
traditional "Borland-speak" for a simulated network connection that is
constructed in the client's user space.  Delphi abd CBuilder folk
interpret "local" in a specific way: others would be less likely to
get what you were actually talking about.

In Firebird, XNET replaced the Borland model some versions ago (2.1?
2.5?) but the "serverless" connection string remained valid up to the
end of the 2.x series.  In Fb3 (as you would have discovered by
studying the release notes sooner!) the implementation of Embedded was
unified across all platforms, for the first time, i.e., the
"serverless connection string" no longer directs the client to connect
via this simulated network connection:  it is now exclusive to
embedded in Windows, as it was previously on other platforms.

Hence the introduction of that XNET:// prefacer for your database file
path (or alias) in Fb 3 when you want to use XNET protocol...along
with a handful of other prefacers applicable to other protocols.

You are not the first to bump into this with Fb3.  Last year, Vlad and
I put together a tech topic on the subject.  In case you want a bit
more information, here's the URL:

https://www.ibphoenix.com/files/ConnectionStrings_Fb3.pdf

Cheers,
Helen





---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Engine12 32 64 bit question

[Helen Borrie hele...@tpg.com.au [firebird-support]]

a...@liberov.net wrote:


> I'm developing a 32 bit application running on 64 bit Windows with 64 bit 
> Firebird 3 server.


> I use 32 bit library to connect to Firebird 


> C:\Program Files\Firebird\Firebird_3_0\WOW64\fbclient.dll

> I'm getting the engine12 Win32 error 193. This means some bitness
> mismatch. The message lists what seems to be a 64 bit dll. 


> Are there any settings to specify to enable such a connection?

No - such a connection is totally allowed already, no special
configuration required.  The server does not know (nor care) whether
the client is 32 or 64-bit.

> There are other 64 bit applications using the server, but this application 
> has to be 32 bit.

Somehow, Windows is finding the wrong client version, via some PATH
setting, which presumably your 64-bit clients are finding correctly.

Place a copy of the 32-bit fbclient.dll in the same directory where the
client application executable is located, to ensure that Windows will
find it first and not seek it out via the PATH variable.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird 4 and RDB$ERROR

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Steve Bailey wrote:

> I am using Firebird 4.0 Beta 1.

So - off-topic in Firebird Support.  But, anyway...

> The release notes describe a new system function RDB$ERROR:

> "The function RDB$ERROR() takes a PSQL error context as input and
> returns the specific context of the active
> exception. Its scope is confined to the context of the
> exception-handling block in PSQL. Outside the exception
> handling block, RDB$ERROR always returns NULL"

>  To test this in iSQL I have tried: select rdb$error(gdscode) from 
> rdb$database

> This results in:
> Statement failed, SQLSTATE = 42000
> Dynamic SQL Error
> -SQL error code = -104
> -Token unknown
> -GDSCODE

> I was expecting NULL to be returned. Have I misunderstood?
>
Yes.  You read that text in the chapter entitled 'PSQL'.  That's
procedural SQL, the language for writing stored procedures, triggers
and executable blocks.  And it's about the exception-handling block in
PSQL - meaning you will get null if you try to access it from a part
of your PSQL code that is not an exception handling block.  Logically,
that's because the current exception is visible only within the EH
block and, of course, only if an exception actually occurred that
threw execution into the EH block.

Nowhere does it suggest that you could expect anything by trying to
use the function in DSQL.

Helen



---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] unsubscribe

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Antonio BIANCA wrote:

> What means this email?

> I've not understood!!!


> Il 07/05/2019 08:26, Brian Dunstan brian.duns...@health.telstra.com 
> [firebird-support] ha scritto:
>>

Don't worry - it doesn't mean anything.  Someone (wrongly) thought
this was the right way to remove himself from the group.

Helen (Moderator)


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] insert data two databases different

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Joel Moo wrote:

> Hi guys, i have a situation, I work with a database name database1.fdb,
> and so, i do maintanance over this databases weekly.

> I backup this database before maintenance, but the maintenance failed,
> so the .fdb file in which the maintenance failed will rename it to 
> database_2, and then restore the database1.fdb file.
> 2 days later for some reason the information was inserted in database 
> database_2 and not in database1.fdb. It is worth mentioning that these
> files and the database.config were not modified again

> Any advice or experience with a similar situation?

Well, first of all, there is no way that the Firebird engine can
perform any operation on a database to which it is not connected.  If
your application seems to have updated database_2 then, some
unplanned things occurred.

The best guess (from the information you have given) is that
the renaming of the original copy of database1.fdb to database_2 did
not happen.

How could that be?  Well, if you ask the operating system to
rename a file that is open, it will not work. A database file is open
if any application is connected to it.  Your script has not
picked up that error condition so it continued onward, with the original
database1.fdb still named "database1.fdb".  So, the restore from the
backup also failed, since gbak restore will not overwrite an existing
database file of the same name unless you specify OVERWRITE explicitly
with the -r[ecreate_database] switch.

Meanwhile, at some point during thos two days, after someone had
inserted some data into database1.fdb, someone else came along and
renamed the database to database_2. Gremlins, maybe?

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Help with Firebird date ranges.

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Hello traceya...@yahoo.com.au,

Thursday, May 2, 2019, 11:15:24 PM, you wrote:

> I am a complete noobie in regards Firebird, :-( and I have 1 system with that 
> has Firebird installed.
> I need to be able to report on the last 7 days data from the date a report is 
> run.

> Currently i have managed to cobble together (from a script, i have
> found) a script that will run and give me the current 'weeks' data.
> Sadly this only starts from the Monday of the week. However, as
> stated, I need the data to be the previous 7 days.

> Is anyone able to advise where i am going wrong, and possibly offer up a 
> solution?

Well, sorry, but it's quite unclear what you expect as output, so I
will guess that you want a set consisting of strings made up from the
various text fields in your log.

The alternative is that you just want a list of workstations (which is
what your current query returns, if it works at all.)

> The script i have is:

> OUTPUT'Macrologs.csv' DELIMITER ',';

This isn't a command in Firebird (or other standard) SQL.  You call
this a "script".  A script for what?  You can use OUTPUT in isql to
channel the screen output to a file - but you will need the full file
path and there is no DELIMITER argument.

Whatever...if you are after that complex string with field delimiters
for text then this is probably something like what you want:

SELECT
  '"'|| MACRO_KEY ||'",'
  '"'|| cast (TIMEREF as varchar(10)) ||'",' /* see note below */
  '"'|| MACRO ||'",'
  '"'|| DESCRIPTION ||'",'
  '"'|| OPERATOR ||'",'
  '"'|| REASON || ||'",'
  '"'|| SITENUM ||'",'
  '"'|| REPLICATED ||'",'
  '"'|| WORKSTATION ||'"'

FROM MACROLOG

WHERE TIMEREF >= (CURRENT_DATE - 7)
ORDER BY TIMEREF

Note, if TIMEREF is a TIMESTAMP, not a DATE, then your varchar for the
cast will need to be varchar(25).

If this isn't what you are looking for, come back with some more
detail about the expected output, environment, etc.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Issues restoring a Firebird Database

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Scott Marcellus wrote:
> I have been trying to use different restores for the
> past couple of weeks for one specific database and it seems I am
> able to restore the database with data in the database restores
> tables but it does not have any procedures or views or anything like
> that. I am able to restore other databases fine and I am using the
> same command for all. The screen output and execution for the
> troublesome database appears to just stop. Is anyone able to give me insight 
> into this issue?

> Here is the command I am using:

> gbak -create -recreate -replace -verify -user XXX -password 
> "c:\somepath\somefile.bak" Server:c:\somepath\somefile.fdb

First of all, the -create, -recreate_database and -replace_database
switches are mutually exclusive.  However, if that worked for some of
the restores, it is likely gbak is just ignoring the others.

Still, decide which one you need and use it alone.  Note that you will
need the OVERWRITE argument with the replace_database switch if the
database exists already in the specified location. The -create switch
won't work at all if that database is already there.

> Here is the restore log for the problematic db restore:

> gbak:activating and creating deferred index FK_x
> gbak:activating and creating deferred index FK_y
> gbak:activating and creating deferred index FK_z
> gbak:committing metadata

Secondly, you need to look in the firebird.log for some clue as to
what went wrong during the commit of the metadata. If an out-of-disk
condition was encountered, it would be logged there. What you won't
see, though, is a situation where another program has locked the file
that the database is being restored to - think backup programs, antivirus,
Windows VSS.

Is there a possibility that you are trying to restore from a backup
that didn't complete?  Or from a metadata-only backup?  Do you have
access to the database so that you can make a fresh backup in the
original server environment and monitor it yourself?

Another factor could be that the backup was from a database with
on-disk structure (ODS) 11.0 or older being restored to ODS 11.1 or
higher.  You may need to include the -fix_fss_M switch to have text in
metadata (such as CHECK constraints) converted to unicode_fss.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] re: Can't get Firbird 3.x embedded to work in Delphi 10.3 update 1?

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Chris LeFebvre wrote:

> One thing, my application is 64bit and I do have the 64bit
> fbclient.dll in the Debug / application so can you connect to an
> embedded database at design time by checking the Connected property
> of the TFDConnection component? I remember reading somewhere that
> during design time that since the IDE is 32bit that in certain
> instances there may be a bitness problem between design time and run
> time? But regardless of if it’s designed time or run time I still get 
> “Database unavailable”?

1.  Only one application can be connected to a particular database with an
embedded connection.  So, if your Delphi project is still open
when you try to run your executable, you will get "Database
unavailable" in your executable.  (If you are running a database
tool such as isql or FlameRobin that's connected to that database,
the problem is the same).

2.  Those guys on the idera forum have got it wrong.  In Firebird 3,
(fbclient.dll + engine12.dll) are equivalent to fbembed.dll in the
older Fb versions.  You DO NOT rename anything to fbembed.dll.

3.  The VendorLib is fbclient.dll in both environments.  The
bitness-appropriate DLL should be in the application directory, so if
the Delphi IDE really is still 32-bit then you need the 32-bit DLL at
design time, as fbclient.dll is where the API is implemented.

OTOH, if you compiled you executable as 64-bit, then it is is the
64-bit fbclient.dll you want in the application directory of your
executable.  Engine12.dll (in the \plugins subdir beneath your
executable) is the Firebird database engine, through which your client
will connect to databases.  It can be 32-bit or 64-bit on a 64-bit box
- your choice.

4.  I think it's a mistake to try and do a custom Firebird install for
your early testing.  Just unzip the whole thing and then go back later
and get rid of the pieces you don't need.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com







++

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:
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] Start/Stopping service

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Hello m_brahi...@yahoo.fr,

> Thank you for answer,

> Yes the service is present in Local Services and the file
> executable file in the area  is
> C:\Firebird\Firebird_3_0\firebird.exe -s firebird3 
> anyway I have no problem with managing service to access my
> application but I don't know if it is correct firebird.exe instead
> of instsvc in the service path area.
>
I don't understand what you wrote there regarding 'instsvc in the
service path area'.  The service will execute the server process from
the installed path C:\Firebird\Firebird_3_0\firebird.exe -s.

> So I need to know please If the service name
> "FirebirdServerfirebird3" is always the same in any windows version
> because I must manage FB service within delphi routine like checking service 
> status, start, stop.

Guessing that you installed the Fb service with a non-default service
name - "FirebirdServerfirebird3" - because you have another version of
Firebird running on the same host machine and you want to call instsvc
from your application to check the status of the Fb3 server process...

to ensure that it runs the correct version if instsvc for the
installed Fb3 process, your application will need to use the full path
C:\Firebird\Firebird_3_0\instsvc.exe with the desired switches.  Maybe
your problems arose from depending on the PATH variable that was
pointing to an older version of instsvc.exe.

Because instsvc requires 'Run as Adminstrator' you will want the
required Windows API call in your application.  But I guess you have
already done that in the past.

Apart from the progressively stricter access permissions in Windows
versions newer than XP, I don't know of any reason for differences
between different Windows versions if the service was configured the
same way on each server.  Of course, we are talking about the
Professional versions, not Home, etc., that do not support services.

I don't know whether this answers your question, though.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Start/Stopping service

[Helen Borrie hele...@tpg.com.au [firebird-support]]

Hello m_brahi...@yahoo.fr,

> I am using Firebird-3.0.3.32900_0_Win32.exe on XP when I try to
> start service in installed FB folder with
> C:\Firebird\Firebird_3_0>instsvc sta or instsvc sto
> I get that message

>  "Error occured during OpenService"
> The specified service doens't exists as installed service (sorry I had to 
> translate from french)
> Usually I start service from .bat file with net start "Firebird Server - 
> firebird3"

> Where is the problem please ?

It is not clear whether you actually have the Firebird server service
installed. Instsvc.exe *can* be used to start and stop the service but
it won't work if the service is not installed already.  In fact, if
you ran the Firebird-3.0.3.32900_0_Win32.exe kit, the installer would
have run instreg.exe and installed the service for you.

Check the Local Services (Services Locales?) with your administrative
tools to see if 'Firebird Server - DefaultInstance' is showing there.
You can right click on that entry to access the Properties of the
service. If you did the standard install, then the service name should
be 'FirebirdServerDefaultInstance'.  That is the argument you should
use with NET START and NET STOP.

"Run as Administrator" is required for these commands and/or for your
batch file, obviously.

Instsvc.exe is really needed only to i[nstall] or uninstall (r[emove])
the service.  It would be simpler just to use NET START | STOP in your
batch file.

If you want to have Fb3 with a non-default service name, you can use
the -n[ame] argument with the i, r, sto and sta switches;  also with
NET START | STOP.  For example, this installs superserver in "manual"
mode with your non-default service name.

instsvc i -s -d -n "FirebirdServer - firebird3"

(you don't need the double-quotes if the name has no spaces.)

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Strange behavior on very large table

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Myles Wakeham wrote:

> The issue is with one very large table that contains about 900,000
> rows.  This table is used as a temporary stora  ge of data that is
> loaded every 24 hours from a CSV file, via an external program.  The
> loading takes about an hour to run, but works reliably.  We are not
> seeing this process changing in terms of time when it is run.

> What we are seeing is that for a period of about 3-4 hours after
> the morning data load is completed (about 4AM our time), any attempt
> to use that table seems to be triggering some very long loading or
> reindexing process.

Given that this table is "temporary" storage, one supposes that you
are deleting rows from it regularly.  Do you happen to be deleting
900,000 rows each day before you load up the latest batch of 900,000?
With Classic, you have cooperative garbage collection, which means the
first transaction that selects on that table following the completion
of the bulk delete will get hit with GC of all those old back
versions.

> This appears on doing certain queries that
> involve indexes, or to re-create an index.

It would be hard to say whether the dog is wagging its tail or the
tail is wagging the dog.  You don't exactly say how this table ebbs
and flows but bulk inserts of course will cause the indexes to be
updated, along with clearing out the junk in the indexes that was
created by deletes.  Indexes are not recreated;  nor are they created
until the engine is instructed to do so.  Indexes are *rebuilt* when a
database is restored from backup and also by
  ALTER INDEX  INACTIVE
followed by
  ALTER INDEX  ACTIVE
which you are probably doing regularly if it's true that this table is
constantly being subjected to bulk deletes and inserts.

The engine doesn't otherwise mess with indexes.

If you're not doing any particular housework on it (restoring from
backup and/or resetting the indexes periodically), then it would be
normal to expect degrading performance until the next time that
housekeeping is done.

> It seems to show some
> sort of caching between RAM and disk going on.  I have set the sort
> size in firebird.conf from the default of 500mb to about 5GB to
> handle this, but that does not seem to have made a difference.

On Classic, there's no point in this.  If anything, it will make it
worse if you have more than a handful of concurrent users.  The sort
files on a big table are going to go to disk regardless, one way or
another.  The engine can't split a sort file so that part of it is in
RAM and part in TempDirectories.  The operating system might decide to
page out some RAM to disk - I seem to recall that's possible on some
systems with older Fb versions but I can't swear to it.

> All other tables are working just fine.  It is only this one table
> and this problem only has appeared in the past 3 weeks or so.& 
> nbsp; Prior to that, it ran without issue.  This suggests to me that
> it has something to do with us reaching some critical volume (as the
> row count has increased at about 20% per year and continues to).

There's nothing like that and 900K rows is not an excessive size.
Rebuilding the indexes on that table would be an easy thing to try
during some time when the table isn't in use. It would be worth
considering how long it has been since the last backup-and-restore
cycle, too.

Cheers,
Helen



---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ODP: [firebird-support] Query optimization on FB3

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> However, could you clarify what you mean between a session or a transaction?

Session == connection
I guess you know what a transaction is.

You can define a GTT with a life that lasts as long as the transaction
in which it is instantiated, i.e.,

CREATE GLOBAL TEMPORARY TABLE name
  ( [, { | } ...])
  [ON COMMIT {DELETE | PRESERVE} ROWS]

So, ON COMMIT DELETE ROWS empties the GTT instance when the
trasnaction commits (the default), while ON COMMIT PRESERVE ROWS keeps
the data until the session (connection) ends.

For more info, see

https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-gtt

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ODP: [firebird-support] Query optimization on FB3

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> By using a global table in Firebird, could not multiple users cause
> a conflict if two such users were to issue the same query against the global 
> table?

No.  A GTT definition is persistent, of course, but persistent data
are not stored in it. A GTT instance is created for use within a
single session or transaction and dies when the session or transaction
ends.  One instance has no knowledge of another - other than multiple
GTTs within the same session or transaction - depending on the life
defined for that GTT.

> Coming from a SQL Server background, I am used to using local
> temporary tables, which are isolated on a query by query basis...

Not the same thing.  The life of a GTT instance can be transaction or
session. (Life depends on a property in the definition.)

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] change a trigger that I create with RDB $ SYSTEM_FLAG = 1.

[Helen Borrie hele...@iinet.net.au [firebird-support]]

luisalej...@hotmail.com,

> how can I change a trigger that I create with RDB $ SYSTEM_FLAG = 1.

You can't.  RDB$SYSTEM_FLAG is an internal setting and, as you have
discovered, trying to set it yourself by modifying the metadata table
has corrupted the metadata.

> He believes it in this way so as not to allow the modification of
> the data of a table that stores the log.

He believes wrongly.  The flag indicates to the engine whether the
object (in this case, the trigger) was created by the engine in
support of some metadata operation.  It has nothing to do with
logging.

> Now I know it's a mistake to place the field RDB $ SYSTEM_FLAG = 1,
> but I need to reset it to 0 to be able to modify it.

Get all users off line (to empty the cache), then log in as the table
owner.  Save the trigger code to a text file, then DROP the trigger.
Recreate the trigger using the PSQL code you saved.

If you are using Firebird prior to Fb3 then GET THE MESSAGE that you
should **NEVER** try to modify the system tables.  From FB3 onwards,
those tables are read-only but with older versions you can still shoot
yourself in the foot.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird 3.0 client does not work with remote Firebird 2.5 database

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Mark wrote:

> On 20-1-2019 22:35, Helen Borrie hele...@iinet.net.au [firebird-support]
> wrote:
>> Firebird 3 allows configuration at both client and server for some
>> parameters, including these two.  Changes to these parameters must be
>> done at the client side for your situation. If you plan to use the
>> same Firebird setup on the Linus server as a client for databases on
>> both 2.5 and 3.0 servers, you'll need to configure records there in
>> databases.conf, specific to each.  (You could set them globally in
>> firebird.conf but that would, of course, restrict the options for
>> accessing diverse remote databases.)

> Testing on Windows, the Firebird client makes no attempt to read any 
> databases.conf (checked with procmon). As far as I can tell, 
> databases.conf is purely for configuring Firebird server and has no 
> influence on configuring the client.

> Why do you think that databases.conf can also be used to configure the
> client?

I thought so, because that was my understanding from Alex P. when we
were writing that Authentication article.  It's long enough since
then, that I can't recall the discussions in detail.  It possibly came
up in response to a question regarding a scenario where the same Fb
client file structure (e.g., a Fb3 server root dir) was being used to
access local and remote databases of differing ODS and one didn't want
to limit those options by configuring those client-side parameters globally.

It is well possible that I've misunderstood that and configuring each
option in databases.conf is *not* the solution;  meaning that the
client side files, including a custom firebird.conf, should be located
in the client's application root dir, as ever and it is only for local
databases that databases.conf is available to override settings in
firebird.conf.

> And if it should, but doesn't seem read it on Windows, is that a
> bug?

I guess that, if it is a bug, we'd know about it by now.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird 3.0 client does not work with remote Firebird 2.5 database

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Michael Fox wrote:

> It’s the default package of firebird-dev 3.0.1.32609.ds4-14 on
> Stretch which doesn’t work.

Ancient.  Current sub-release is 3.0.4. Given that there were some
humps and bumps in the early releases, you might well be encountering
bugs that have since been fixed.

> The previous version of firebird-dev
> 2.5.3.26778.ds4-5+deb8u1 on Jessie works fine with the same
> credentials (note: I’m using PHP’s Interbase extension to connect
> but I don’t think it’s the cause of the issue).
>
Maybe, maybe not.  There are some bugs around with that driver.  But
if it is working OK for you with 2.5.3 (also ancient!!) it might not
be a factor in this issue.

> The config settings are the default ones. For the two settings you mentioned:

> AuthClient = Srp, Win_Sspi, Legacy_Auth
> WireCrypt = Enabled (for client) / Required (for server)

Here's the thing.  The defaults shown here for these two parameters
represent the default configs for a Fb3 client and a Fb3 server. Since
they are Fb 3 settings, the 2.5 server doesn't know about them.
Specifically, they are telling the Fb3 client to encrypt the wire
transmissions but of course Fb 2.5 doesn't support wire encryption.
Likewise, Fb 2.5 doesn't support SRP authentication;  nor does the
Linux cclient support Win_Sspi at all.

Firebird 3 allows configuration at both client and server for some
parameters, including these two.  Changes to these parameters must be
done at the client side for your situation. If you plan to use the
same Firebird setup on the Linus server as a client for databases on
both 2.5 and 3.0 servers, you'll need to configure records there in
databases.conf, specific to each.  (You could set them globally in
firebird.conf but that would, of course, restrict the options for
accessing diverse remote databases.)

> I’ve played around with different permutations without success.

So - you have done settings in databases.conf?

> Maybe worth noting that the credentials for the remote server are in the 
> format:

> XX.XXX.X.XXX/45731:d:/Database/P2Gold data.ib

It no longer makes good sense to avoid using aliases.

On the 2.5 server side, have the third party make an alias in
aliases.conf, e.g.,

p2gold = "d:/Database/P2Gold data.ib"

(You may or may not need the double quotes, try it both ways.)

At the Fb3 client side, make an entry in databases.conf for the
same alias.  That's just for syntactic completeness: the client
doesn't do anything with the path, though of course this would be an
invalid entry if you tried to use it locally!  You can study the notes
in databases.conf for the syntax of an entry.  For this alias you will
need to set AuthClient = Legacy_Auth and WireCrpyt = Disabled.

Once saved, the config should work.  The same isn't true for reconfigs
of server-side parameters, though - that requires a server restart.
Likewise, on the Fb 2.5 side, the alias should be available once
aliases.conf is saved, whereas anything reconfigured in firebird.conf
will necessitate a server restart before it is visible.

You can find more details in this article:
https://www.ibphoenix.com/files/Authentication_FB3.pdf

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Problems with creating new users (no permission for INSERT access to TABLE PLG$VIEW_USERS)

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Bryan,

Maybe something in here will provide that ah-haa moment. ;-)

C:\Programs64\Firebird_3_0_embedded>isql employee -user sysdba
Database: employee, User: SYSDBA
SQL> show version;
ISQL Version: WI-V3.0.4.33054 Firebird 3.0
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V3.0.4.33054 
Firebird 3.0"
on disk structure version 12.0
SQL> create user test password 'testuser' grant admin role;
SQL> -- This statement looks like DDL, so isql should auto-commit it, right?
CON> -- But, under the surface, it is DML (on the security database, natch!)
CON> -- which isql does not autocommit, so
CON> -- we need to commit that DML explicitly before it can be available
CON> -- to grant it the RDB$ADMIN role in the current database;
SQL> --;
SQL> commit;
SQL> select sec$user_name, sec$active, sec$admin, sec$plugin from sec$users;
SEC$USER_NAME   SEC$ACTIVE SEC$ADMIN SEC$PLUGIN 
=== == = 
===
SYSDBA   Srp
DEVELOPER   Srp
TEST Srp
 

SQL> -- Now the user exists, we grant the role to it; 
SQL> grant rdb$admin to test;
SQL> commit;
SQL> select rdb$user, rdb$privilege, rdb$relation_name from rdb$user_privileges
CON> where rdb$user = 'test'; 
SQL> -- no result there:
CON> -- RDB$USER in RDB$USER_PRIVILEGES is stored here as string.  Engine 
CON> -- always stores it in upper case and of course it is case-sensitive.; 
SQL> --
SQL> select rdb$user, rdb$privilege, rdb$relation_name from rdb$user_privileges
CON> where rdb$user = 'TEST';

RDB$USERRDB$PRIVILEGE RDB$RELATION_NAME
=== = ===
TESTM RDB$ADMIN

SQL>quit;

C:\Programs64\Firebird_3_0_embedded>isql employee -user test2 -role rdb$admin
Database: employee, User: TEST, Role: RDB$ADMIN

SQL> create user test2 password 'test2user';
SQL> select sec$user_name, sec$active, sec$admin, sec$plugin from sec$users;

SEC$USER_NAME   SEC$ACTIVE SEC$ADMIN SEC$PLUGIN
=== == = 
===
SYSDBA   Srp
DEVELOPER   Srp
TEST Srp
TEST2   Srp

Take careful note:  the GRANT ADMIN ROLE gives user management privileges to 
the user in ANY database on the server, whereas the RDB$ADMIN role applies 
only in the database where it was granted.  In other words, the user must

1) be committed first into the security database with the 
   GRANT ADMIN ROLE (and don't forget, you have to commit 
   it if you add it later with ALTER USER as well) 
2) be granted RDB$ADMIN in all the databases where you want it 
   to have user management privileges
3) be logged in to a database with the RDB$ADMIN role

In Fb 3 and Fb 4-alpha, you must have *both* and perform your login with 
the role.  In Fb 4 beta 1, a user with the GRANT ADMIN ROLE (SEC$ADMIN TRUE) 
does not need to log in with the RDB$ADMIN role.  I don't know whether this 
is going to be backported to Fb 3.0.5.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Informing the devs about optimizer issues / alternative plans

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Rudi Feijó wrote:

> We have been trying to optimize one of our largest databases (firebird
> 3.0.4) , and in doing so, we managed to greatly improve the execution of one
> specific query by manually changing the PLAN.

>  

> I am wondering if the firebird team is interested in receiving detailed
> feedback of such cases for dev purposes, and if that’s the case, what is the
> procedure I should follow to share the database and the query.

1. Create an account at http://tracker.firebirdsql.org and create an
"improvement" ticket in the CORE category. Provide as much information
there as you can.

and

2. Subscribe to firebird-devel list - you can do it from this page:
https://www.firebirdsql.org/en/mailing-lists/ (scroll down) for
discussion of your ideas.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Converting dates and the ISO-8601 format

[Helen Borrie hele...@iinet.net.au [firebird-support]]

i...@synapsesoftware.co.uk wrote:

  >I use Firebird primarily with Delphi and have had no problems with
  >using and displaying dates (suitable for my country - UK.)

  First thing to understand is that Firebird does not store dates
  as strings.  It can recognise string inputs for dates according to a
  number of conventions but, at the back, it converts them to one or a
  pair of numbers calculated from starting points on timescales.

  What gets returned to the client when dates are read depends, as you
  rightly guessed, on what the operating system is set up to display
  and what the client interface offers for massaging date/time data
  into strings.  Most (if not all) Delphi interfaces use the Windows
  regional settings.

  >However, it seems to be a big problem with Firebird and dates when
  >working with PhP with  firebird driver. I have a number of fields that
  >hold information in a field defined to be of type  "date" in Firebird.
  >When I use PhP to query my database I get the fields formatted in the
  >ISO-8601 format (year/month/day.. etc).

Other than a UDF, there is nothing you can do in your SQL to influence
what string the client delivers to your interface.  You should ask on the
firebird-php list for advice about this.  PHP has so many little
functions to massage data that I'd be surprised if you didn't have
at least one convert option!

  >I really dont know how to format the results so that they appear
  >"normal" for my region - ie the dd/mm/ format. I also am not sure
  >where the problem originates. It seems to depend on the client
  >software I am using to view the data (which seems to imply its the
  >client software that is responsible for rendering the data.)

True.  Although there is the (not highly recommended) option of
using an external function ("UDF") to convert your dates to strings.
There are various UDF libraries around:  look at
   https://www.ibphoenix.com/download/tools/udf
for links to a selection of such libraries.  You might find one that
does what you want.  Of course, strings are not going to be a lot of
use to you if your applications want to do calculations on dates.

  >Here is a summary of softwa re I have tried and the results

  >Programresult
  >FlameRobin   Formatted correctly (except using the "." notation - so dd.mm.yy
Written in C++ using an interface called IBPP, which probably has a
function to massage dates into a format that is compatible with the
regional context of the host (or maybe the client).

  >DBeaverFormatted incorrect for my region ( ISO-8601 format)
Depends on the driver you have behind this, I guess, and probably also
the regional settings on client or host or both. Firebird has lists
for both Java and ODBC/JDBC so it's worth asking on the appropriate
list.

  >PhP  Formatted incorrect for my region ( ISO-8601 format)
As above.
  >Delphi  (VCL) Formatted correctly dd/mm/
Follows the regional setting.  This is easy to test.

  >My question can be  sumerised as - How to format the results so they
  >are correct for a specific region (in this case the UK )? Should the
  >client software (PhP/DBeaver etc) have a setting that allows you to
  >format the results correctly ? Becasue I cant find any global setting
  >in the various clients that will allow this.

  >If we do have to explicitly cast / format the data - this adds a lot
  >of work on the server / software and surely must have performance issues ?

Cast - no.  But if you go the UDF route and export your dates as
strings then data over the wire will be fatter but work at the client
will be thinner.  That's true of anything you convert, of course.
Can't avoid it.

  >My goal is to format the result from a PhP request so the dates are
  >displayed correclty. I understand that Firebird does not have
  >the"convert" function - so I have tried using the "CAST t;
  >function - but this seems to do very little
  >
  >for example..
  >cast(TRANSDATE AS DATE) as TRANS_CREATED
  >
  >This seems to do nothing. I have tried looking through the PhP.ini
  >file for some way to influence how the data is formatted and cannot
  >find anything. I have even tried using ..
  >
  >ini_set('date.timezone', 'Europe/London');
  >
  >But this also did nothing.

I hope you understand a bit more now about what's going on in these
interfaces. Good luck.

Helen




---
This email has been checked for viruses by AVG.
https://www.avg.com

[firebird-support] Off-topic discussions

[Helen Borrie hele...@iinet.net.au [firebird-support]]

All,

Please be reminded that the firebird-support list is for topics that
are about your questions or problems concerning your work with
released versions of Firebird.

The recent thread "Firebird vs. PostgreSQL" should have been launched
in firebird-general.  It's too late to divert it now, but please be
mindful of this for future discussions.

If it is not directly a support topic, then please take it to
firebird-general.

If it is a technical question about a feature in a future release,
including alphas and betas, take it to firebird-devel.

Helen (Moderator)


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Possible bug in 2.5.1.26351

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Mark,

Tuesday, October 30, 2018, 8:20:51 AM, you wrote:

> On 29-10-2018 02:06, Helen Borrie hele...@iinet.net.au 
> [firebird-support] wrote:
>> Character set NONE is a bare-bones ASCII set and does not provide
>> intrinsic support for any characters beyond the 128 characters that
>> provide the US-Ascii upper and lower case, numerals and the basic
>> diacritic symbols.  You can store anything in charset NONE but nothing
>> in the least useful can be done with those incompatible characters.

> NONE only means store the bytes accepted and return them as is, and if a
> connection character set is specified, try to convert the stored bytes
> to that character set.

That much is true - but only if the first 128 characters of the
incoming set are compatible with those of US-Ascii.
> It does not mean or assume ASCII at all.

"At all" is too sweeping.  The recognised alpha characters in NONE are
the unaccented a-z and A-Z of us-Ascii.  Hence, if you are feeding in
strings that involve only those characters, you can UPPER and LOWER
them, and concatenate them.

> It just
> happens that most (all?) Firebird supported character sets (including 
> UTF-8) use ASCII as the base for the first 128 characters.

Considering all supported character sets, not "most", not "all".  It's
only true of character sets that can recognise those 7-bit characters
and can read the bytes in left-to-right order.
 So -- most (if not all) Western character sets, few if any Eastern
 ones that are stored in NONE.

 Helen




---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird 2.5: first insert into table takes ages to complete

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Dominik Psenner wrote:

> we are observing an interesting issue and hope for interesting
> insights what could cause the symptoms. First of all a few numbers
> about the database and the firebird instance in question:


> · Database contains several hundred tables

> · Few tables contain up to 10.000.000 records

> · Database size on disk is roughly 3GB

> · Firebird is 2.5 with SuperServer flavour

Which sub-release?

> · Page size is 16384

> · Page buffers is configured to 2048

> · Forced writes is enabled

> We noticed that the very first insert after a large migration that
> changed the table layout (new columns and such) takes a large amount
> of time to complete. About 50 seconds on a laptop machine with an
> i7, 8gb memory and an SSD. This first insert reads more than 6
> pages from disk to cache and writes almost the same amount of pages
> from cache to disk. Following insert commands, even after a restart
> or rollback of the previous insert, read only about 70 pages and
> write only 3 to 5 pages from cache to disk and take 5ms to 40ms to
> complete. Following inserts behave, regardless of whether the first
> insert command transaction was rolled back or the firebird process
> was restarted. Doing a backup of the database and restoring the
> database changes the observed behaviour of the first insert
> statement to behave like the previously mentioned subsequent insert 
> statements.

> Observations:


> · This issue does not appear related to disk page caching
> or firebird page caches because it survives computer reboots and firebird 
> server restarts.

> · This issue appears to be related to the persistent state
> of the firebird database because after a backup to gbk and restore to fdb the 
> symptom disappears.

Yes.  The symptoms suggest you have a large number of record versions
that are waiting for garbage collection.  New records cannot be
written to existing pages until after that garbage has been cleared.

> Is it known and considered "normal" that some insert statements may
> cause the firebird server to read and write several thousand pages
> and taking ages (50 seconds vs 40ms)? Are there any known causes for this 
> symptom?

See above. After a backup and restore, there is no garbage.  However,
the first operation on a dirty table will cause a garbage collection -
hence the long time taken for this first insert.

Run gstat -h on the database when you start to notice these delays.
Check the values of the various ' ... Transaction' reports and copy
them back here.

As to the cause, it is totally due to inadequate management of
transactions.  This style of poor management commonly comes from
applications that keep read-write transaction open for long periods
and never committing them.  Are your apps written in Delphi?

HB

p.s. Would you please strip out your company's footer details when you
post to the lists.  The warnings have absolutely no point in a
mailserve list and they take up a lot of space on subscribers' disks.



---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Custom error message from procedure/trigger code in Firebird 3.x dialect 3

[Helen Borrie hele...@iinet.net.au [firebird-support]]

jonatan.laurit...@yahoo.dk wrote:

> Sometimes I need to write extended error message from the SQL
> procedure and trigger code. So far I have used the following procedure:

> create or alter procedure REPORT_ERROR (MSG varchar(400))as
> BEGIN

>   update rdb$exceptions set 

> rdb$message=:MSG  where rdb$exception_name='MY_ERROR'; 
>   exception ERRMSG_;
> END

This was always a hack...

> But in Firebird 3.x UTF8/dialect 3 I am receiving the error message
> while using this function (even as SYSDBA user):

> UPDATE operation is not allowed for system table RDB$EXCEPTIONS.

And here's where the hack comes back to bite you.  System tables from
v.3.0 onward are read-only, meaning, amongst other things, you can no
longer circumvent the restriction on performing DDL operations in PSQL
by hitting the system tables directly.

> So - how can I report custom errors from the SQL procedures and
> triggers, it would be nice the generate some information in
> procedures and triggers and let the Delphi error-handling code to
> process this information for the user presentation.

Since around v.2.0, you have been able to replace the default message
associated with an exception with a run-time message of up to approx.
1000 characters (ascii) or around 250 (UTF-8).  See
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-handleexceptions.html#fblangref25-psql-exception



---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Very slow response after massive delete on table. (FB3 on Win 64)

[Helen Borrie hele...@iinet.net.au [firebird-support]]

fabia...@itbizolutions.com.au wrote:

> I've noticed the following strange behavior on Firebird 3 latest
> release, 64 bits, on Windows 2012, 24 processor server, 200 GB ram, magnetic 
> HDDs (no SSDs):

> 1) After executing and commiting a massive delete on a table, any
> select or insert takes time to execute, as if FB was not reclaiming
> the deleted "space" on the table, and had to read each deleted
> record to filter and find the response.

Well, that's not quite the process but it's certainly the result.
Nothing happens, of course, until the transaction commits and the
engine finishes writing the delete stubs for those records.
"Reclaiming the deleted space" doesn't happen at all until a garbage
collection process has been through and flagged them all.  The timing
of that depends on how GCPolicy is configured: background or
cooperative. For background, which applies only to Servermode Super, a
GC thread starts up periodically to do that flagging. For cooperative,
the first transaction that hits that table after the bulk delete
transaction commits gets the load.

If the Servermode is Super then by default GCPolicy is combined, i.e.,
the engine will try both ways - useful with short transactions, not so
much with bulk updates and deletes.  For Classic and SC modes, only
cooperative is possible, i.e., background and combined are ignored.

The quickest way to clear the decks after a bulk delete is to do a
SELECT COUNT(*) FROM , which will touch every record in
the table and flag the delete stubs for GC.  If the bulk delete is
deleting ALL of the rows, then dropping and recreating the table is
faster.

> 2) While a big transaction is occurring (inserting into a table the
> results of a big select via an SP), another DB on the same server
> becomes unresponsive for about a minute. I thought each database had
> it's own "deamon / process / thread" assigned and the locks were
> exclusive to each DB, however it appears there is some interaction
> between the two DB performance other than just the shared 
> OS/HDDs/Memory/Processors.

Again, the locking behaviour depends on the server mode.  Super is one
single engine process with connections running in threads of the main
process.  Lock files are not shared across databases, though.  Classic
is your one-process-per-connection mode, with each connection having
its own lock file.  SC is a listener process that starts a thread for
each connection, each having its own lock file.

The reason for the slowdown in the second database probably has to do
with the availability of resources on the machine while your bulk
delete in the other database is either still under way or is being
cleaned up by a GC process.

> And   a question for the experts would it be possible to install
> multiple instances of FB 3 on the same OS instance, to ensure a
> better level of independence between the DBs response time? This was
> possible on FB2.5

The mechanism is the same in both versions.  Only Superserver (Fb3
Servermode Super) has shared resources.  If you were running two
instances of SS in 2.5 and you observed some benefit from doing so,
there's no reason, architecturally, for SS on 3.0 to behave
differently.  Still talking about SS, if you have plenty of RAM, do
you have pages caches in both databases that are big enough to keep
things flowing?  Or too big to be accommodated in RAM while you have
something big going on in one or both databases?

> however I believe on FB 3 it did not make sense
> because FB3 assigns a processor per "request / connection",
> perhaps I got that wrong?

Both v.2.5 and v.3.0 will assign a connection to an available CPU,
if CpuAffinityMask (in SS) is set to zero. SC and Classic don't care
about CPUAffinityMask.
Neither version assigns CPU at request level (i.e., requests within a
connection).

There's probably more to be said - that's just a few things I can
think of now.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird - GRANT edit/recreate access to specific users and block some users from editing/recreating the procedure

[Helen Borrie hele...@iinet.net.au [firebird-support]]

antoedinchar...@gmail.com wrote:

> Have planned the upgrade of FB, and the works are going on.
>
Firebird 2.5.2 to Fb 2.5.8 is not an upgrade you have to plan.  It is
a point release, i.e., the same software with patches.  You need to do
this. Just keep safe copies of security2.fdb, firebird.conf and
aliases.conf, so you can copy them back over the new ones.

Regarding your original question, see
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-security-auth.html#fblangref25-security-rdbadmin

Grant the RDB$ADMIN role to the user(s) you want to have SYSDBA rights
in the database.  When that user logs in with that role s/he can do
what you asked for.  If s/he logs in without that role, s/he will have
just the rights that have been otherwise granted.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird - GRANT edit/recreate access to specific users and block some users from editing/recreating the procedure

[Helen Borrie hele...@iinet.net.au [firebird-support]]

antoedinchar...@gmail.com :

Not enough information.  What version of Firebird are you using?

> I am trying to GRANT edit/recreate procedures for manager level
> users and blocking the same from normal users.

Explain exactly what you mean by "manager level users".

> I was able to do the same in the case of Tables, but for triggers
> and procedures i am not able to limit the modification rights.

Procedures can be modified only by the owner (the user that created the
procedure) and the SYSDBA or a user with elevated privileges.

Likewise triggers;  although the owner in the case of triggers is the
user that created the table (for table-level triggers) or the database
owner (for database-level triggers).

> please help me on this.

You can help others to help you by providing sufficient information
about your environment.  There may be more you can do to set up what
you want - it depends on the Firebird version.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Antonio,

> I don't understand as you have been able to believe that I have treated
> a database as was a spreadsheet!!! I know what is a table, a database 
> (database as "set of tables", database as "file of type database"), a 
> relation, a key, an inedex, a... I've studied on your book.

> When I speak "to draw a table" for a program, I intend to set various 
> "tables" with the appropriate fields (name and type) and the 
> relationships with the fields of other "tables": so the database is the
> whole organized of these "tables" and relations. Right?

So far, so good.

> if it is not correct, I go to be a carpenter...
> else
> - a client ask me to write a program for a  single scope, the archives
> (the "tables" in the database!) must be able to contain the data related
> to that type of activity and I draw (create, write) a dabatase with the
> correct tables;

Yes, one client (Company A) one database

> - different clients ask me different types of programs: FOR ME, a 
> database must be created (drawn) for each type of program;

For everyone else, a database is created for each client organisation
(one database for Company A, one database for Company B, and so on.

> - two or more programs can have some identical "table" (e.g. 
> municipalities);
> - (1) I can create apart a unique database of "common tables" for the 
> "tables" used by all the programs;

The database for Company A should have all the tables used by the
users in Company A, including the low-volatility ones like your
Municipalities table.  If Comapny B needs Municipalities, it should
have its own copy.  You can create a DML script for this
low-volatility table and run it over each database;  or use a
replication tool if it is called for.

> (2) the same "table" can be repeated
> in all the databases;

That is what I referred to as "spreadsheet".  Those old-style desktop
fielsystems predated spreadsheets and lent themselves to that model of
application, due to explicit table locking.  That does not make it a
sensible or correct approach for a transactional RDBMS such as
Firebird.

> - it could be created an only database for all the programs; for every
> new program, new "tables" can be added in this unique database, while 
> the "table" existing can be used by the new program;

No, all tables should be available to all progrms and to one another.
If you have the same data in more than one table, you have redundancy,
the big enemy in data management. Related to this, if you are using
actual data as keys, you have intrinsic redundancy.

If you want to prevent certain USERs or ROLEs from accessing certain
tables, you use SQL privileges.

> - if you write dozens of programs (very small, normally, very great), 
> the only database becomes not manageable, *but it doesn't behave problems*.
> "not manageable" FOR ME as administration of the variations to the 
> single fields: Firebird is able of to manage millions of tables and 
> relationships, I can't, is its purpose!!

Firebird is designed to ensure the ACID rules (Atomicity, Consistency,
Isolation, Durability). it won't prevent you from breaking those rules
but it won't heal your wounded data for you, either.

> You pursue a purpose of general relations, that is the purpose of DBRMS;
> I pursue a purpose of management USING Firebird and its capability to 
> relate: it is not the same thing! FOR ME, naturally!!

Actually, every database is part of a management system of one kind or
another, so you are not alone in this world.  You can design a system
to store data according to rules and structures that make it safe and
smart: THAT is the purpose of a RDBMS.  Apparently, you want to follow
the spreadsheet model, in which a single set of data is exclusive to a
single application.  So - you maintain multiple instances of the same
data by hand and say your prayers.

>  From this my problem is born, but I can change my formulations.
> I need to think.

If you are unfamiliar with the ACID rules and normalization, look them up.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Antonio,

> I'm agree with you: all tables in a unique firebird file is the best choice.

> My problem is that I've numerous programs that utilize one firebird file
> each.

That problem is easy to solve.  Place all of the tables in one
database and use an alias for that database in every application.

> There are tables that are drawn for a specific program, but there are 
> some tables that are "common" to all programs (example: 
> "municipalities").  An user can utilize one program, two programs, four
> programs, and so on, on the same computer and each program hav its 
> database.fdb: if I must vary a value of one "common tables", I must send
> an update for every program and to each user; but if the "common tables"
> are in a unique, separate database, I can update one file only.

This is not a database design.  You are treating "database" and
"spreadsheet" as though they were the same animal.  Very definitely,
they are not the same.

Think "relational", because Firebird is a relational database system.
Tables (a.k.a. relations) can be *related* to one another by way of
foreign keys.  Data from tables containing compatible fields (columns)
can be connected during run-time queries by JOINs or UNIONs.

> There are also "static" tables containing storical movements, and is not
> necessary periodically to back-up them.

At the same time, it does no harm for them to be included in the
backup of your current data.  It makes sense for them to be in the
same database as the active tables if you need to refer to them from
your applications.

> These are the reasons that push me to look for a solution, I hope to 
> have been clear.

The thing that seems clear to me is your confusing "tables" with
"databases".  The whole point of using a relational database is to
have all of the interrelated data available to each individual client
connection.

> For release 2.5 and not 3.o, I have been studyng Firebird for some 
> months, and I've not experience: I now plan the job to develop, later I
> will verify the new releases.

The question of whether you use 2.5 or 3.0 is not relevant to your
problem.  Spend some more time understanding how a relational database
works - and forget your preconceptions from previous work you have
done using spreadsheets or fiel-based data storage systems.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: Sequential auto incremental numbering

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Lester wrote:

> On 03/09/18 07:29, 'Christian Giesen' ch...@xt.co.za [firebird-support]
> wrote:
>>    NextInvNo = (SELECT MAX(INVOICE_NO) FROM INVOICE) + 1;

> Christian ... there was many years ago a very nice article on this very
> problem.

I think this is the article Lester was referring to:

http://www.ibobjects.com/docs/ti_AuditableSeries.ZIP

I wrote it.  It was a long time ago but it still applies. The thing
about generators is not that you can't rely on uniqueness (you
absolutely can!) but that you can't go back and *reuse* a number if your
transaction is rolled back for some reason.  Back then, accountants
used to insist that accounting documents have unique numbers *in an
unbroken sequence*.  Some countries still require this, so this
article will help you if you are stuck with this limitation.

But definitely, the moment you start meddling with the values of
generators, you are making a bed of nails.  Incidentally, there *are*
ways to turn back generators so, theoretically, you could "re-use" a
missing value.  In practice, you must not try to.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Unable to connect to the employee database

[Helen Borrie hele...@iinet.net.au [firebird-support]]

karen_den...@ymail.com wrote:

> Following the Quick Start Guide's instructions on pg. 26, I typed the 
> following into isql:

>
SQL>> connect xnet://employee user sysdba password mynewpassword;


> And it responded, thus:


> Statement failed, SQLSTATE = 08006
> Unable to complete network request to host "xnet".
> -Failed to establish a connection.

The QSG may need some correction if it is offering that exact string
as an example.  Missing here is a hostname, viz.

INET://machost:employee user sysdba password mynewpassword;

Depending on your network configuration, or if you don't know your
Mac's network name, you could do

INET://localhost:employee user sysdba password mynewpassword;

or, more simply:

localhost:employee user sysdba password mynewpassword;

> What should I be doing differently?

For an embedded ("hostless") connection using the syntax you chose,
you need an extra forward slash in the string:

connect inet:///employee user sysdba password mynewpassword;

But you can do the same thing without that extra layer and you won't
need the password:

connect employee user sysdba;

> I am on a Mac, if that makes a difference.

It doesn't, provided you have taken care of the filesystem
permissions.


Quoting from another of your posts:

> My reason for attempting to use Firebird is that LO is transitioning
> to an embedded Firebird database, which is still, as I understand,
> fairly buggy. A knowledgable person on their forum suggested I try
> the Firebird 3 Server.

OK, the "buggy" thing would be the Firebird driver and interface that
is under development by some LO guys and a Firebird guy, Marius Popa.
Firebird Embedded itself is simply one of several "server modes"
available for deploying Firebird.  It's not something special that has
been churned out specifically for Libre Office.

Perhaps I can help you to position yourself in some kind of developer
spectrum by explaining a little about the structure of a database
application.

At the top level is the client application, typically written in a
computer language such as C++, ObjectPascal, C#, Java.

At the next level is a language-specific driver.  This layer acts as a
"translator" between the language of the client application and the
application programming interface (API).  This layer lives on the
client machine.  On a Mac, its name is fbclient.dylib.  You will find
a copy of it in your /bin directory.  For some languages, such as Java
or the .NET languages, this layer is not loaded dynamically but it
coded natively in the driver layer.

Right at the back-end - on the server that hosts Firebird - is the
Firebird Server, which can be configured to accept either network
connections or embedded connections or both. Embedded connections are
possible only from the same machine where the Firebird server and the
databases are located.

LO Base is a client application.  The transition project is to use the
Firebird server in embedded mode:  I doubt there is a plan to use LO
Base as a remote client - but I really do not know!

So, essentially, the LO model is a local client application intended
to give you an interface through which you can create and work with
Firebird databases in embedded mode.  I don't know anything about the
design details but it seems you are expecting that it will give you
some kind of interface that avoids your having to know anything about
databases, nor how to write database applications, in order to create
and maintain database objects. If so, it seems to me that by going the
route you are going, you are showing great courage!

To get "with it" and stick with it, it seems you might have some work
to do to get down and dirty and get your head around the file systems,
user/group permissions, network configurations and text editors, along
with the bash shell and some system tools at the command-line level.

Helen






---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] How does a Mac OS user get started?

[Helen Borrie hele...@iinet.net.au [firebird-support]]

 kam3...@gmail.com wrote:

> I downloaded Firebird 3.0.3 and installed it on my Mac. In Library
> > Frameworks, I do see Firebird.framework and it has many folders inside.

That's the Firebird root on MacOSX.

> I read the Quick Start Guide, but all the instructions pertain to
> Windows and Unix users. Is there any documentation for Mac users?

Regarding...what?  The instructions for Firebird are the same on both
Windows and POSIX. Linux and MacOSX are both POSIX. It's not a
different application, pertaining to Mac in particular, though of
course the various kits are compiled for the particular platforms.
Just follow the Quick Start Guide (in the /doc folder). You will also
find it useful to have the release notes on hand as there are particular
instructions for configuration that might not be fully covered in the
QSG.

BTW, we don't provide a how-to for working with a Mac framework...or a
Linux installation...or any particular release of Windows...or any
particular platform.  It's assumed you know how to work with the
platform-specific tools and text editors. Anything in Firebird's
workings that is specific to a platform will be covered somewhere - if
not in the QSG then in the release notes. Apart from the obvious
differences like file and network systems and their specific syntaxes,
operational differences are rare.

The tool you'll want for setting up your user accounts, including the
initial password for the sysadmin (SYSDBA user) is isql, a
command-line app that you'll find in the /bin directory.  You can use
that to connect to the employee database (which is aliased in
databases.conf), as you need to be connected to a database to work on
user accounts. Documentation for isql can be found in the
documentation library of the Firebird web site.  You might want to
pick up the Mac version of Flamerobin at some point, if you prefer a
graphical interface.

Hope this helps.  Use this list if you bump into something that
doesn't seem to work properly for you.

Helen










>   
>
>  
> 



-- 
Kind regards,
 Helen Borrie


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: Mailing list change?

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Doug wrote:

> In any event, the admins have made it clear they are not looking to
> move so we can drop it for the moment.

Actually, I'm the admin and I haven't said a lot about the current
glitches so far. ;-)  And you're an occasional moderator of this list
too, so it's fair you should have your say.

> I just do not think Yahoo has
> been a reliable or easy to use platform over the years.

I don't agree that Yahoo hasn't been reliable.  Sure, over the years,
they have made changes to the admin interface without announcing them,
but overall it has been reliable. I mind all the lists, including the
newer Google ones, and they all have their glitches.

> Adter being
> hosted here for so long it seems prudent to take a serious look at
> the alternatives, especially since these glitches keep coming back
> to Yahoo. Lots has changed over the past few years.

These glitches are infrequent, although I can't disagree that they
have happened.  This current one of duplicating messages and of having
replies appear before the orginal posts is annoying but, like Mark, I
think we'd have to be seriously put out to consider shifting a list
with an 18-year archive.

The likelihood of leaping out of the frying pan into the fire is
fairly strong. The firebird-net-provider list was moved from
Sourceforge to Google Groups six months ago and the Subscribe entry on
the website was updated at the time.  It's a low-traffic list compared
to this one but I'm still having to moderate subscribe requests and
first posts.

Let's suppose that River's post yesterday put a fairly accurate finger
on the current problems.  Let's weather it for a week or two and see
how things shake out.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Multiple copies of the same post

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tomasz wrote:

> I know it's off-topic, but does anyone else experience receiving
> multiple copies of some posts on this list, or is it just me?
> Sometimes I have 2, 3 or 4 identical copies in my inbox (like Hugo's 
> from yesterday - 2 copies). They seem identical except for their X-UIDL,
> but AFAIK UIDLs are being assigned by the receving e-mail server, so 
> that doesn't prove anything.
> Sorry to bother you with this, maybe someone just clicks "send" with a
> shaky hand, but maybe my mailbox is messed up. No duplicates from other
> lists, though.

I've been seeing the same thing in this and other Yahoo lists.
Also, as list admin, some other behaviour, such as getting a 404 error
when I click through the supplied link to a post that requires
moderation.  The latter problem went on for a week but it seems to
have been resolved now.  I wonder whether Mark saw the same thing with
the Firebird-Java list.

Yahoo recently underwent an ownership change: it belongs to Verisign
now.  I concluded that our list problems were probably due to new
hands on old software as the Verisign people gradually move the Groups
archives to their own servers.  There's also the activities of
U.S.-based list providers having to become compliant with the EU
privacy laws by the July 30 deadline.  It's probably a mix of
circumstances.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] GRANT Privileges

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Lee,

> I’m struggling to grant any privileges on a .fdb file database.

> I have created a separate user as the SYSDBA reported “Your login
> SYSDBA is same as one of the SQL role name. Ask your database
> administrator to set up a valid Firebird Login”.

The SYSDBA user exists already.  The password is 'masterkey' but you
should change this immediately.

> When I use ISQL to run a select statement on the database in question I get 
> the response:

> “Statement failed, SQLSTATE = 28000
> No permission for read/select access to TABLE ZIEKTE”

> Then I try to GRANT SELECT on TABLE ZIEKTE to USER DW_EXTRACT” however I get 
> the response:

> “Statement failed, SQLSTATE = 28000
> Unsuccessful metadata update
> -no S privilege with grant option on table/view ZIEKTE”

You need to be logged in as SYSDBA or the database owner.  An ordinary
user can't grant permissions to self nor to any other user.

> I’ve tried numerous tables but I get the same response.
> I have tested the user on the Example database “EMPLOYEE” and have
> no problems accessing any table on that.

AFAIR, all the tables in the employee database have rights granted to
PUBLIC.  This isn't something you should do with a real-life database,
though.

> Can you possibly help me or point me in the right direction?

If you don't know the db owner's credentials then log in as SYSDBA.

If you post again, would you please exclude all the company stuff in
the sig of your posting.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: tempdirectories

[Helen Borrie hele...@iinet.net.au [firebird-support]]

wobble...@yahoo.co.uk,

> is there a good and up to date official source of documentation on
> firebird.conf

The firebird.conf file itself, version current for the Fb version you
are using.  You won't get more up to date than that anywhere.
For Fb3, which allows per-database config for a lot of
parameters, see the release notes.  Some things are (or can be)
configured at the client side via a config file now, too.

> and other admin?

What's "other admin"?  The command-line tools are all documented but
some could do with updating, admittedly.  You can catch up with any
new switches in those apps by running them with the -? switch.

Vol. 3 of TFB2e covers them all in more detail, up to the earlier
releases of Fb 2.5, but it's not free.

HB




---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: tempdirectories

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Ian wrote:

> We got the idea from The Firebird Book, and after that I can't find
> mention of it being deprecated in 2.0 release notes or later.

Not deprecated but abolished. If something is "deprecated", it is
still valid but likely to be dropped in a subsequent release.  From Fb
2 onward, Fb was made more tolerant of directory names with spaces,
hence the need to drop such embellishments.

(Not that made anything like sufficientt impact on my consciousness in
2013-4 to remember it affected TempDirectories - q.v.)

> From
> the 2004 book, page 745 (I know it's older than my daughter ;-):


> "TempDirectories


> Version 1.5 forward


> Supply a list of one or more directories, separated by semicolons
> (;), under which sort files may be stored. Each item may include an
> optional size argument. in bytes, to limit its storage.


> TempDirectories = userdata\sortfiles 500
> "

It has subsequently become a myth that has survived into TFB Second
Edition. I can fix that.

> And also in the 1.5.6 release notes
> https://firebirdsql.org/rlsnotesh/config-fb-conf.html, and then
> hanging around in something that claims to be the Firebird 2
> Administrators manual, but now I look closer it isn't obvi  ously a
> core fb site:
> http://www.janus-software.com/fbmanual/manual.php?book=admin=42

No, it's not.


> I can't find it mentioned anywhere else though, except in things
> like http://tracker.firebirdsql.org/browse/CORE-2151 where the author was 
> still using it...

The author of that ticket was the developer who wrote the parser code.
;-)  It looks as though he'd discovered unintended behaviour when the
parser landed on a space in a directory name...the older code to
truncate out anything following a space (i.e., assumed to be the
now-invalid allocation size) was gaily truncating forward from the
first space in the directory name itself. (That was an amazing piece
of searching that dug that up!)

Helen





---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: tempdirectories

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> 27.07.2018 21:41, Helen Borrie hele...@iinet.net.au [firebird-support] wrote:
>> I don't know where you got the idea that you could specify the size of
>> a temp directory.

Dmitry replied

>It is an Interbase feature.

Uh...ok... @_@

If you want to allocate specific spaces for your temp directories on
the hard disks, just create a couple of partitions of the sizes you
had in mind.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: tempdirectories

[Helen Borrie hele...@iinet.net.au [firebird-support]]

wobble...@yahoo.co.uk wrote:


> I've just tried 


> TempDirectories = /location1 5000 ;/location2  ; /location3


> And location1 is ignored, but 2 and 3 used!

Which tells you something. ;-)
The engine doesn't recognise the format of the first member of the
list but it's OK with the second and third because they are legal.

I don't know where you got the idea that you could specify the size of
a temp directory.  It's never been a thing and it wouldn't make
sense if it were.  The idea of being able to specify multiple
locations is that you can avoid an out-of-space crash if the engine
has somewehere else to go when it needs to put temp files to disk and
the first (or default) temp space is used up.  Another point is that,
if your user environment has a lot of users doing lots of ordered
queries and keeping them alive for long periods, you can consider
having a dedicated disk, such as a fast SSD, for TempDirectories.

The engine holds the temp file structures in RAM if it can: it only
puts them to disk if available RAM is too low.  Hence, allocating a
RAM disk for TempDirectories doesn't make sense, either.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] characterset issue in asp.net core

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Issam Boughanmi wrote:

> hi,
> i have some issues related to character sets in an asp.net core
> application using 6.1 provider and EF .

> special characters like 'µ' or french characters for example don't show up 
> correctly .

> i have another web application using 4.10 provider and ef with the
> same database that work perfectly .
> the charset of the database is NONE .

> any help is welcome, because i am out of ideas now ;)

This is a driver issue, not a database support matter.  Please join
the Firebird-net-provider group and post your question there:

https://groups.google.com/forum/#!forum/firebird-net-provider

Helen (Moderator)


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] firebird 3.0 Not binding on ipv4 on windows xp machine

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Taylor Stewart wrote:

> Installed firebird 3.0.3.32900 on windows xp with ipv6 enabled. and it will 
> only listen on :: ipv6

> if i manually adjust RemoteBindAddress to 0.0.0.0 it listens on ipv4 fine.

Looks as if you have discovered a workaround for behaviour on an
obsolete operating system.  It would be safer to use the host's own
Ipv4 address or host address, though.

You could also try the alternative syntax

 INET4://winserver:d:/databases/mydatabase.fdb

to avoid your NOS even polling for an IPv6 address.

> Is this normal? 
> On windows 10 it listens on all by default with no configuration changes.

Perhaps.  I don't think our project-based QA testing extends to
testing on XP, Windows 98 or Windows 3 these days. ;-)  It might be just
what you need to do to achieve what you want on that old OS.

Make sure the clients are using the Fb3 client to ensure that
they are using a network protocol version that supports Fb3's
behaviour.

It's also worth checking whether you inadvertently configured
IPv6V6Only to true in firebird.conf.  You want that false ( = 0) to
allow clients to poll for IPv4. Don't forget that, if you
reconfigure anything in there, you need to restart the Firebird
service for it to take effect.

Helen









-- 
Kind regards,
 Helen Borrie


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] URGENT - Database performance is very slow

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Vishal Tiwari wrote:

> It's urgent...
> We are facing a database performance issue. Currently, we are using firebird 
> version 2.5.2 ODS 11.2.
> If we try to run a simple SQL i.e. Select * from  using
> FlameRobin, it is taking time and next time the time span increases.
> Same happens from all of our applications side as well. We have
> Desktop applications build using Delphi.
> Kindly refer to below snap.

It's no good trying to send screenshots or any kind of attachments to
the list. They are dropped automatically.

First, v.2.5.2 is buggy and very old.  Replace it with v.2.5.8.  You
don't need any migration for this;  just keep copies of your security
database, firebird.conf and aliases.conf so you can copy them back
into your new installation.

Then, run gstat -h on the database and copy the output into your next
post. (Rt-click on the title bar of the command window, select Edit
then Select All.  Hit enter to copy, Ctrl-V to paste into your email.)

A typical reason for Delphi applications to exhibit progressive
slowdowns is poor transaction management, specifically those written
with long-running read-write transactions that never get committed.
Garbage builds up and builds up in the database just from everyday
usage, in the form of thousands of old record versions that can't be
flagged "uninteresting" because of all those uncommitted transactions.
You observe the slowdown in FlameRobin, isql, et al., because all that
garbage is trapped in the database.

You can improve that permanently by rewriting the offending
application code. In the meantime, do regular sweeps or gbak backups
in (gfix) shut -single mode to clear out the trash. If you are already
doing that, then you probably need to do it more often.

So first things first - update Firebird and let's look at those
transaction statistics from the database header.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: Firebird 3: Not able to UPDATE RDB$Procedures

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> Also, apparently, this is not a blanket restriction, since I can   
> successfully run the UPDATE RDB$PROCEDURES on at least 1 other  
> converted DB (that is essentially the same as the DB I cannot runthis 
> UPDATE on).

Smells like a privileges problem.  The updater has to be SYSDBA,
rdb$owner_name or (on POSIX) root.  Could also be a regular user that
has been granted and is logged in under the RDB$ADMIN role, or one that
has been granted metadata privs on procedures through user name or
role.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Error -902 after Update Windows 10

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Mark wrote:


> The problem I have with this solution, is that when using a Firebird 
> database, you shouldn't be accessing a Firebird dstabase through a 
> share, and Firebird should disallow that by default.

And it does.  It would require RemoteFileOpenAbility to be true.  By
default it is false.  It should NOT be set true for any database that
is not in ReadOnly mode, nor, as you pointed out, for a database that
you want to keep safe.

> In other words: did you really access a Firebird database through a
> share? Or where you using the WNET (NetBEUI) protocol to connect to 
> Firebird?

> Because if that is the actual problem,



Having the database is on a share does not seem to be the problem.
Most people know not to do that. But it is a pretty common practice in
networks to place the client application software on a share and have
all the users run it from the same place.  This is where things have
come unstuck for at least some of those reporting a similar problem.

HB

Re: [firebird-support] Error -902 after Update Windows 10

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Eduard Calveras wrote:

> Since few days ago, after Windows 10 update, some applications
> writes in Delphi can not connect to network  Firebird 2.5.5 server.
> These applications can connect with BDE, InterBase, FIB, FireDac... but all 
> are using the GDS32.dll

> In other side, applications as FlameRobin, are using the same
> library GDS32.dll and connects successfully.
> Clients with previous Windows versions works fine too.

> These are the errors messages:  Interbase:

> 22/05/2018 17:48:03  DBUG:  ; ¬[Application: ]¬[Error] 
> -902 335544721 Unable to complete network request to host   
> "192.168.1.100".¬Failed to establish a connection


> BDE:

> OpenConfigDatabase DbConnection Exception. Error Opening   
> Configured Database $AUTO_DATA, Error was: Unknown   database..
> Unable to complete network request to host "192.168.1.100".
> Failed to establish a connection.


> The problem is a kind of local issue, because other Pc-clients
> works fine. But certainly is the gds32.dll that cannot connect
> with server and I not known why. 

Last week, Martijn Tonies found this blog, which helped some people
with this problem:
""
Could this be related to:
https://blog.mertech.com/windows-10-version-1803-breaks-some-shared-folder-applications

"This means that applications started from these environments can’t open a 
connection to a database server."
""

>From the forensics (sketchy as they are!) it appears that, if your
network was using Homegroup for file sharing, then SMB would have been
already disabled *before* the update.  The Fall Creators' Update has
removed Homegroup file sharing from Win 10 completely:
https://www.howtogeek.com/fyi/microsoft-just-removed-homegroups-from-windows-10/

If your network is relying on a Homegroup for sharing files, there is
nothing you can do to fix this, as it is gone forever.

Meanwhile, of course, your Win 10 clients are left without file
sharing until SMB is (re-)enabled.

Go to
  Control Panel
 Programs and Features
   Turn Windows features On or Off

and check on 'SMB 1.0/CIFS file sharing support'.

How Microsoft lights up our lives!

HB

Re: [firebird-support] FB3 alter domain colltion

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Karol Bieniaszewski wrote:

> when we create dommain we can do:
> CREATE DOMAIN XXX TYPE VARCHAR(50) COLLATE PXW_PLK;
>  
> but how to alter this domain to have still collation?
>  
> ALTER DOMAIN XXX TYPE VARCHAR(150) COLLATE PXW_PLK;
>  
>  
> Engine Code: 335544569
> Engine Message :
> Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 1, column 38
> COLLATE
>  
> is this possible or i should create ticket/or it already exists?

It is not clear why you included a COLLATE clause in an ALTER DOMAIN
statement.  You can't change the COLLATE property of a domain, which
is why you got the error: COLLATE is not expected in an ALTER DOMAIN
statement.  But you were not trying to change that, anyway.

An ALTER DOMAIN statement does not drop the existing domain.  It
allows valid changes to properties that can be changed.  All you
needed here was:

ALTER DOMAIN XXX TYPE VARCHAR(150)

It is allowed, because (1) VARCHAR and VARCHAR are compatible and (2)
the new size is longer than the old size.  No other properties are
affected.

HB

Re: [firebird-support] Firebird ADO.NET: ExecuteNonQuery Returns -1 On Successful Insert

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> I am using the Firebird 2.5 Embedded Edition with the Firebird
> ADO.NET provider version 5.5 or 5.7.  I am not sure which, since it
> has been so long since I set up the references for my project and
> the assembly being used is not taken from the actual provider
> library that has all the files in it..


> In any event, during a test of my current application development I
> successfully inserted a record into a table using a Firebird stored
> procedure with the ADO.NET ExecuteNonQuery method, which should
> return a "records affected" count of "1".


> Instead, I am finding that the returned "records affected" count is "-1".

You posted this to the database support list.  You want to post it to
the firebird-net-provider list (google group, not sourceforge) so that
someone who knows can tell you what the expectations are in ADO for
RecordsAffected by an insert from a PSQL module.

> I have never seen this before with all the other database engines I
> have worked with over the years.  Nonetheless, I believe I have seen
> this issue raised with Firebird ADO.NET before but some time ago.


> Does anyone have any idea as to what is causing this?

Well, I don't do ADO but to me, logically, a record can't be affected
if it didn't exist before the DML operation, so only reows affectd by
update or delete operations would be counted, if any such count were
available. I think that's how it works in MSSQL.
The context variable ROW_COUNT is available in PSQL for you to do
something with *inside* the module, e.g., to pass it to an output
parameter.  It does give you a row count for each DML operation as it
completes. Of course, this means its value changes in a module that
has multiple DML statements.

I don't know of any other way to get row counts from executing a PSQL
module.  Maybe the Firebird ADO.NET provider is able to wrap such
output from an ExecuteNonQuery into this RowsAffected valuewild
guess here. Ask on the right forum.

HB

Re: [firebird-support] Doubts regarding statistics of indexes and tables...

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Javier wrote:
>
> Ok, but how do I restore the statistics?  Using "update" statements?

You do not "restore the statistics".  The statistic that gets updated
by a SET STATISTICS call is the selectivity of the index.  Low value =
high selectivity = Good;  high value = low selectivity = bad.  The
value immediately after the call reflects the state of the index at
that point.  So, at that point, the optimizer has the best chance to
prepare the most efficient plan by deciding which indexes will be the
most helpful.

After that, the actual condition of an index selected for the plan may
degrade gradually as rows are inserted and deleted. When you start to
notice a decline in the performance of queries over a table with a
high level of inserts and deletes, it is probably time to run SET
STATISTICS again.  Selectivity is not static - it changes
infinitessimally with each insertion and deletion, or dramatically
with a major bulk insert or delete.  Eventually, the statistic may
tend to become too outdated to obtain the same level of performance as
when the numbers were fresh.

The optimizer always calculates a plan based on the most recently
calculated statistics. What you can do is run the queries in isql with
SET PLANONLY while the statistics are fresh.  That will tell you the
optimizer's choices based on the current selectivity and record count.

You can save that plan in a text file.  If you decide from your test
results that you like it, you can copy it into your application
queries using the optional PLAN clause. If you supply a plan in your
select query, the optimizer will not try to create one.  That might or
might not work positively over time, of course.

On the whole, the optimizer is going to produce the most effective
plan, since it is aware of the current size if the table and may be
able to make dynamic adjustments that your static plan cannot take
into account.

Hth.
Helen

Re: [firebird-support] Re: Firebird Embedded - Will a 3.0 version be released???

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> I am currently using Firebird Embedded edition for a military
> simulation I have been working on.

> I have seen recently that Firebird 3.0 has just been released with
> a brand new User-Guide.

Actually, Firebird 3.0 was released two years ago.  The latest
sub-release is 3.0.3.

> Along with this, I noticed that Firebird
> 4.0 is now on the books for development.

True:  it is in Alpha.

> However, in the interim will the Firebird Team be releasing a 3.0
> version of their embedded database engine to match the internals of
> their recent release for their server engine?

With the fully unified engine, Firebird 3 embedded is no longer
distributed as a separate application.  You use configuration and
protocol to set up and use embedded and/or any of the other models
from the single installation.

You really need to study the release notes closely to see how things
work now.

> Or has Firebird Embedded Edition reached a level of maturity that
> it requires no further development at this time?

Embedded always was one choice from four server models.  That has not
changed.

Helen

Re: [firebird-support] order by 1, 2 - fb 32990

[Helen Borrie hele...@iinet.net.au [firebird-support]]

acgomes2...@yahoo.com.br wrote:

> select

> A.Codigo

> 'DINHEIRO' FORMA_PAGTO

> FROM NF A

> where A.dataEmissao between '01.01.2018' and '31.01.2018'

>  and A.Modelo = '55'

This should work fine if you place a comma after A.Codigo.  It does
not make any sense to ORDER BY 2 (nor ORDER BY 1,2) as your literal
field FORMA_PAGTO has the same value in every row.  So:

 select
 A.Codigo,   /* << */
 'DINHEIRO' FORMA_PAGTO
 FROM NF A
 where A.dataEmissao between '01.01.2018' and '31.01.2018'
  and A.Modelo = '55'
 order by 1

HB

Re: [firebird-support] no permission for INSERT access to TABLE PLG$SRP_VIEW

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Todd,

Todd Brasseur wrote:

> Having Issues with 'create user' with Firebird 3.0

> It works fine on one computer where we are testing but not the other. 
> We think we did the same thing on both computers.

> Installed Firebird

> Created SYSDBA Account

> Created PRIVATEADMIN Account

> Granted Role RDB$ADMIN to PRIVATEADMIN (in security3.fdb)

> Log into our database as PRIVATEADMIN with ROLE RDB$ADMIN. Where 
> committing the Create User command, I get the error in the subject line

> no permission for INSERT access to TABLE PLG$SRP_VIEW

> The other computer adds the user without a problem.

Don't know why it does.

> What am I doing wrong?

>From the Language Reference:

Granting the RDB$ADMIN Role in the Security Database

Since nobody—not even SYSDBA— can connect to the security database,
the GRANT and REVOKE statements are of no use for this task. Instead,
the RDB$ADMIN role is granted and revoked using the SQL statements for
user management:

CREATE USER new_user
PASSWORD 'password'
GRANT ADMIN ROLE

or

ALTER USER existing_user
GRANT ADMIN ROLE

ALTER USER existing_user
REVOKE ADMIN ROLE
  

Note

GRANT ADMIN ROLE and REVOKE ADMIN ROLE are not statements in the GRANT
and REVOKE lexicon. They are three-word parameters to the statements
CREATE USER and ALTER USER.

HB

Re: [firebird-support] BLOB Not Found error

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> 16.03.2018 14:47, shg_siste...@yahoo.com.ar [firebird-support] wrote:
>> Since some days ago I'm getting (it seems very occasionally and randomly) a 
>> "blob not 
>> found" error.
>> As far as I remember the problem happens during read operations: with 
>> "locate" commands or 
>> inside a "while not eof" loop...

Dmitry S. wrote

>You should use "Repeatable Read" or "Snapshot" transaction isolation mode. 
> In "Read
> Committed" you can get this error if record has been deleted and garbage 
> collected before
> you start reading the BLOB.

Also bear in mind that:

1.  If no blob has ever been stored for that field in a record, then
no blob id exists for it.  For editing, you should test for NULL at
some appropriate point in your query or your client code and handle it.

2.  When you "edit" a blob, the original blob is copied in memory to a
new blob with a temporary blob id.  The temporary blob is visible only
to the transaction in which it is created and, within that
transaction, the original blob content is invalidated as garbage by
the Post action from the client application.

3. On commit, the temporary blob is copied to a new location, with a
new blob id, and becomes visible to other transactions from that
point. It will not go to the same physical location as the original
blob, because that location remains "interesting" to the garbage
collector until it is able to be released by a future GC.

4. Be aware that, if you try to edit the same blob field more than
once in the same transaction, the initial view of the original blob is
not there any more.  AFAIR, the Firebird engine began enforcing
read-only on changed records from v.2.5 onward, so you would encounter
a different error if your app was trying to post multiple edits of
non-blob fields from the same transaction.  I can't say for sure
whether that restriction could apply to blob edits since the new blob
id is not known until the commit has occurred.  Also, I don't know
whether your IBDAC components are aware of the restriction on multiple
edits. It is probably something you should ask Devart.

Helen

Re: [firebird-support] External Tables

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Saturday, March 10 alansbr...@gmail.com wrote:

> I'm trying to create an external table with a normal user and I'm getting 
> this error:

> This operation is not defined for system tables.

> unsuccessful metadata update.

> CREATE TABLE EXT_CTB_LANCAMENTO failed.

> There is no privilege for this operation.

> How do I grant permission to a regular user to create external
> tables? During my tests only sysdba could create them.

Access to the database via external tables is a potential
vulnerability.  If a non-privileged user is allowed to create and
manipulate the metadata of database objects, the vulnerabilty
escalates.  Hence the restriction you are encountering when a
non-privileged user tries to create a table.

If you are using Firebird 3, you can grant permission to any ordinary
user (or a user with a specific role) to create tables.  With that
privilege, that user can create and manipulate metadata for *any*
table. You would have to feel very sure that the appointed user, or
users with that role, would not mess with any other tables or abuse
the privilege in any way.

You can read about metadata privileges (sometimes nicknamed "DDL
privileges") here:

https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-access-sql.html#rnfb30-security-metadataprivs

You should restrict the location of external files as heavily as
possible, by setting the allowed location in firebird.conf, via the
ExternalFileAccess parameter with the RESTRICT argument.  Don't forget
that you have to restart Firebird after changes to firebird.conf or
databases.conf.

Helen

Re: [firebird-support] Firebird 3 Developer's Guide released

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Saturday, March 3, 2018, 3:20:25 PM, DougC wrote:

> Very glad this got done! Thanks to everyone who made it possible.


> However, the PDF option yields a 404 error.

Fixed - sorry!

HB

[firebird-support] Firebird 3 Developer's Guide released

[Helen Borrie hele...@iinet.net.au [firebird-support]]

The Documentation team is proud to announce that the Firebird 3
Developer's Guide, by Denis Simonov, is now available in English:

https://www.firebirdsql.org/en/reference-manuals/#fbdevgd30

This volume consists of chapters that walk through the development of
a simple application for several language platforms, notably Delphi,
Microsoft Entity Framework and MVC.NET (“Model-View-Controller”) for
web applications, PHP and Java with the Spring framework.

Translating this guide from Russian and preparing it for publication
in our library were made possible by a crowd funding campaign last
year.  Warm thanks to all of you who contributed.

---
Firebird Documentation Team







++

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:
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] Cannot open database file in local hard disk

[Helen Borrie hele...@iinet.net.au [firebird-support]]

>  I use Firebird 2.1 and in some computers I cannot open a  
> database file which is in the local hard disk.
>   
>  For example, let's suppose I have the database
> C:\Test\MYDATABASE.FDB and I try to open it with IB_SQL (from
> IBObjects).
>   
>  If I use C:\Test\MYDATABASE.FDB I get "unavailable database".
>  If I use SERVERNAME:C:\Test\MYDATABASE.FDB I get "unavailable
>  database".
>  If I use \\SERVERNAME\C:\Test\MYDATABASE.FDB I get "unavailable  
>database".
>   
>  If I modify [firebird.conf] aliases.conf creating the alias
> MYDATABASE=C:\Test\MYDATABASE.FDB, then using MYDATABASE I can   open the 
> database.

This is the intended behaviour if you have DatabaseAccess = None
configured in firebird.conf.

HB

Re: [firebird-support] Firebird 3 - Auto Garbage collection with Sweep interval = 0

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Arnaldo,
Friday, February 16, 2018, 2:19:19 AM, Arnaldo wrote:

> maybe i have always misunderstood the garbage collection mechanism, the
> sweep interval, etc.. and i'm realising only now, but:

> I have this database (Firebird 3.03 SuperServer x64)

> My question is : why garbage collection mechanism starts automatically
> when i browse these tables, despite the fact that i have Sweep Interval
> = 0 ? What is the point that i'm missing ?

Some more thoughts to add to Norm's comments...

Garbage collection (GC) and sweeping are separate, although
interrelated, processes.  By setting the sweep interval to 0, you
disable automatic sweeping (according to the conditions described by
Norm) but you get GC regardless of the sweep interval setting.  The
sweep interval setting has no effect on the GC that is being performed
constantly by the engine.

A GC run updates the record statistics but it does not remove the
stubs of deleted records.

There are three possible settings for the GcPolicy parameter in
firebird.conf to govern how GC is performed: background, cooperative
and combined.  The GC mechanism described by Norm is 'cooperative':
each time a user opens a table, old record versions from others' work
will be flagged if they are no longer "interesting" and previously
flagged versions will be removed.

Superserver can also do 'background':  that is, a worker thread that
wakes up periodically and runs in the background, doing the same
tasks that cooperative does. Background GC is not available in Classic
or Superclassic.

For SS, the default GcPolicy setting is 'combined':  both background and
cooperative mechanisms are used.  You can change this to 'background' or
'cooperative' if you want to test whether unexpected slowdowns are being
caused by one or other of the two GC mechanisms.

The GcPolicy setting has no effect on either the sweep interval or the
way the engine performs a sweep.

Periodic sweeping will be needed if auto sweeping is disabled.  Watch
the statistics to determine how often you might need to do it. An "ad
hoc" manual sweep might be needed after a large batch of deletes, to
get rid of those delete stubs.

A gbak backup does a sweep by default, unless you run it with the -g
switch ( [no_]g[arbage-collection] ).   If you have a regular backup
routine with the default settings, you might not need to run manual
sweeps at all. However, you will need them if you are relying only on
nBackup for your backups, as nBackup does not touch garbage.

HB

Re: [firebird-support] Re: Running with two different embedded versions of Firebird DB

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Ed Dressel wrote:

> Any suggestions?
>
Several days ago, you wrote:

> We are in the middle of letter our users upgrade their database from
> 1.56 to 3.x at their will. Users can upgrade when they want to--so
> we install 1.56 embedded db as gds32.dll and 3.x as fblcinet.dll in
> the same directory (using IBObjects, we can select which DLL is
> used). Almost all of our users use the embedded version.

> The problem is when a database exception occurs--the "firebird.msg"
> file is used. Is there a way to configure the name that the DLL uses
> for the firebird.msg file? Or should the FB databases be installed
> in separate sub directories? Any suggestions here would be appreciated.

When I read that, I groaned and let it go.  This is not a sane way to
deploy an upgrade from a 15-year-old engine to Fb3 (or any full
upgrade, come to that!).  So, to avoid trying to write a small book,
I'll make a few suggestions in the hope that you'll start to see that
this isn't a task for the Tooth Fairy.

1) It's not a question of where the databases are installed.  The ODS
10.1 and ODS 12 dbs could be in the same subdir, if their names are
different.  Use aliases in the respective .conf files to distinguish
them, so that your application will connect to the right one.  But
deploying the databases in separate subdirs won't solve the problems
you're planning to create here.

2) I suppose you are aware that the Fb3 engine can't connect to a
sub-ODS 12 database.

3) The rules for deploying embedded apps haven't changed.  You still
have to have completely separate and self-contained file structures
for each embedded app, with the Firebird components in their correct
places relative to the application executable.  These relative places
are not the same for v.1.5 and v.3.0 so you'll need to study release
notes very thoroughly and TEST your assumptions.

> Almost all of our users use the embedded version

4) Taking 3) into account, if you are deploying the software in
company with full server versions, this whole thing could become a
total mess really quickly.

5) Give up on this idea of letting the users choose.  Give them test
versions beforehand if necessary, but have them make the choice before
you deploy;  test what they have chosen to make sure it works as
expected;   then deploy just that.

HB

Re: [firebird-support] Adding a field with NOT NULL constraint

[Helen Borrie hele...@iinet.net.au [firebird-support]]

>   Your last advice concerns me a bit. Is it also valid for  
> changing stored procedures or triggers ?

As an abiding principle - yes.  But, for SPs and triggers, the effect
varies according to a few factors.  The BLR for these modules is
cached on first use.  Changes conducted whilst the module is in cache
will not take effect until the cached copy is removed.

For Classic and Superclassic, each user has a private cache that
disappears when that user detaches from the database.  For
Superserver, the cache is shared, so the changes will not take effect
for any user until all users detach.
>
>   Should I have always to disconnect every client before   executing DDL 
> sentences ?

My advice is "Yes, always".  There might be some conditions where
changing things while users are online is plain sailing but how would
you know for certain?  Whilst the engine may allow you to effect
changes without throwing errors or corrupting on-disk structures, it
would be difficult to assure yourself that you are not going to
corrupt the in-memory structures that users already have in place.

And, when all is said and done, assumptions about the structure of
the database objects are made in the client application and any active
request refers to the status quo when that client connected.

HB








++

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:
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] Adding a field with NOT NULL constraint

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Aldo,

> My questions are the following:

> 1) Is the intended effect to fill behind the scenes a newly created 
> field with its default value when there is a not null constraint ?

No.  Only inserts subsequent to the commit of the DDL for the new
field will use the default in the case where no value is provided.

Note, also, that default values apply only to inserts and only where
the field is absent from the field list for the insert.

> 2) Could this behind the scenes filling fail because of an update or 
> insert of another concurrent transaction ?

There is no "behind the scenes filling".  If you add a NOT NULL field
to an existing table, or change a nullable field to NOT NULL, then you
are responsible for filling the field yourself, immediately after the
DDL is committed.

  update mytable set newfield = 1 where newfield is null

  update mytable set existingfield = 1 where existingfield is null

As for the effect on concurrent transactions, you should not be
attempting to change the structure of a table while it is in use.

HB








> 
> Posted by: Aldo Caruso <aldo.car...@argencasas.com>
> 

> ++

> 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






-- 
Kind regards,
 Helen Borrie

Re: [firebird-support] Miscalculation of floating points in Firebird

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Salim Naufal wrote:

> I have noticed the following inconsistencies in Firebird 3.02:

[..]

> I then tried:
> SELECT CAST(1 AS NUMERIC(18,2)) * (1.00 - (1.00/1.11)) FROM
> RDB$DATABASE and got a much better result of 991.0

> Finally, the correct result is retrieved using:

> SELECT CAST(1 AS NUMERIC(18,2)) * (CAST(1 AS DOUBLE PRECISION)
> - (CAST(1 AS DOUBLE PRECISION)/1.11)) FROM RDB$DATABASE


> I am not familiar with the details of the SQL standard, is this the
> proper was to round and calculate numeric values in SQL? 

You really do need to familiarise yourself with the way SQL handles
numbers, especially division.  In short, with implicit casting:

- the result of division of an integer by a number of any type is
always integer, with banker's rounding

- the result of fixed numeric/fixed numeric gives FN where the scale
of the decimal part is the sum of the scales of the operands

- the result of FN/floating point or FN/FP gives FP

Hence the reason why these last two examples got closer to the result
you wanted while performing that integer division messed it up.  I
suggest you play around with your formula in isql until you get the
precision and scale that you desire in your result.  Don't try to use
a GUI tool for such testing as it will probably "tidy up" the output
for you and confuse the outcome.

Note, if you match the scale of your operands throughout, you might
get away with not having to cast anything.   Implicit casting casts
non-integer numbers as double precision.  Any operands that are
database columns are not cast implicitly - they use the type defined
for them.

Helen

Re: [firebird-support]Engine Error - Partner Index

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Nico,

Friday, December 22, 2017, 10:29:05 PM, you wrote:

> Hello,

> I have a problem with inserting a row in table
> OrderTypeDescriptions. Here you can find the sql script for the
> tables OrderTypes and OrderTypeDescriptions.


> CREATE TABLE ORDERTYPES (

>   ID  INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,

>   STOCKORDER  BOOLEAN DEFAULT FALSE NOT NULL,

>   ROWVERSION  TIMESTAMP,

>   /* Keys */

>   CONSTRAINT PK_ORDERTYPES

> PRIMARY KEY (ID)

> );



> CREATE TABLE ORDERTYPEDESCRIPTIONS (

>   IDINTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,

>   ORDERTYPEID   INTEGER NOT NULL,

>   ORDERTYPEDESCRIPTION  VARCHAR(100) COLLATE UNICODE_CI_AI,

>   LANGUAGEIDINTEGER NOT NULL,

>   ROWVERSIONTIMESTAMP,

>   /* Keys */

>   CONSTRAINT PK_ORDERTYPEDESCRIPTIONS

> PRIMARY KEY (ID),

>   /* Foreign keys */

>   CONSTRAINT FK_ORDERTYPEDESC_LANGUAGEID

> FOREIGN KEY (LANGUAGEID)

> REFERENCES LANGUAGES(ID) <--

> ON DELETE CASCADE,

>   CONSTRAINT FK_ORDERTYPEDESC_ORDERTYPEID

> FOREIGN KEY (ORDERTYPEID)

> REFERENCES ORDERTYPES(ID)

> ON DELETE CASCADE

> );



> CREATE INDEX IX_ORDERTYPEDESC_DESCRIPTION

>   ON ORDERTYPEDESCRIPTIONS

>   (ORDERTYPEID, LANGUAGEID);

> If I insert a row in table OrderType there is no problem.
> Id = 1
> Stockorder = true

> If I post a new row in table OrderTypeDescriptions I got a problem

Insert into OrderTypeDescriptions (OrderTypeId, OrderTypeDescription, 
LanguageId)

values (1, 'Test', 1)

> Problem
> Engine Error (code = 335544333): internal Firebird consistency
> check (partner index description not found (175), file: idx.cpp line: 1271).
> SQL error (code = -902): Unsuccessful execution caused by a system
> error that precludes successful execution of subsequent statements.

Where is the insert for the master record in LANGUAGES with
LANGUAGEID=1?

Also, don't forget that you must commit the DDL for the master tables
before you can define foreign keys that refer to them.  If you are
using isql then the default behaviour is to autocommit DDL statements.
 The same is likely to be untrue if you are using a third-party tool.

 After that, if you have uncommitted inserts to a referenced table in
one transaction, then those uncommitted records are not visible to a
transaction that is trying to insert into the detail table.

Note that isql does not autocommit DML statements.

Helen

Re: [firebird-support] Info

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Nico,

Wednesday, December 20, 2017, 4:34:09 AM, you wrote:

> CREATE TABLE ADDRESSES (
>   IDINTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
>   ADDRESSNAME   VARCHAR(100) COLLATE UNICODE_CI_AI,


> Id = 1 / AddressName = Test / ..
> Id = 2 / AddressName = Test2 / ..

> I want to know the next Id number in my table Addresses without an
> insert. In my example it will be 3. I want to reserve (without an
> insert) Id number 3 because someone else can also ask the next Id
> number. In that case it must be id  number = 4, because you've got
> Number 1 and 2 in the table and number 3 is reserved.

IDENTITY is just lexical candy, useful when you have a generated id
that fires only on inserts.  You can mess around with the start number
to some degree, using DDL commands, but otherwise the underlying
generator (sequence) cannot be manipulated, as the name of the internal
generator for the column is unknown to clients. This is what IDENTITY
is about, in fact.

For any id's that you want to manipulate the way you describe, create
an explicit generator for it, along with a BI trigger for the table to
set the conditions for firing the generator.  You can use the NEXT
VALUE FOR  command when you want to capture the next
value without firing the trigger.  Generators fire outside the
transaction context of the request so, once a value is fetched, the
generator cannot return the same value again.

Of course, you don't want the trigger to fetch another new value and
overwrite the one you reserved, so you must make your BI trigger
detect whether the INSERT command supplies a value.

Helen Borrie

Re: [firebird-support] Re: Deadlock error from firebird during midnight

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello LtColRDSChauhan,

The net-provider list that you have been posting to on Sourceforge is
inactive.  Any postings there are ignored.

You need to go to this URL:

https://groups.google.com/forum/#!forum/firebird-net-provider

and join the "new" group.  It has been "new" for more than two months.

HB

Re: [firebird-support] Trouble establishing a connection to Firebird database files.

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tuesday, November 28, 2017, 12:06:27 PM, Justis wrote:

>I’m running firebird 3.0; I’m not sure what version of firebird the database 
>files were generated with.

You can find out, using another command line tool.  Open a command
shell and go to the Firebird root directory.

Then do

gstat -h d:\path\to\your\database

where d:\path\to\your\database is the absolute (i.e., not relative)
file path to your database

If you do that right, and the database was created or restored under
Fb 3, you should see output similar to this:

C:\Programs64\Firebird_3_0>gstat -h 
c:\programs64\firebird_3_0\examples\empbuild\employee.fdb

Database "C:\PROGRAMS64\FIREBIRD_3_0\EXAMPLES\EMPBUILD\EMPLOYEE.FDB"
Database header page information:
Flags   0
Generation  168
System Change Number0
Page size   8192
ODS version 12.0
Oldest transaction  160
Oldest active   161
Oldest snapshot 161
Next transaction161
Sequence number 0
Next attachment ID  21
Implementation  HW=AMD/Intel/x64 little-endian OS=Windows 
CC=MSVC
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date   Mar 21, 2017 10:15:35
Attributes

Variable header data:
*END*

If it was created or restored under an older Firebird, you will see
this:

C:\Programs64\Firebird_3_0>gstat -h 
c:\programs64\firebird_2_5\examples\empbuild\employee.fdb

Wrong ODS version, expected 12, encountered 11

ODS means "on-disk structure".  Each major verson (1.0, 1,5, 2.0, 2.1,
2.5, 3.0, 4.0) has its own ODS:
ODS 10.0 ~~ Fb 1.0  ODS 10.1 ~~ Fb 1.5
ODS 11.0 ~~ Fb 2.0  ODS 11.1 ~~ Fb 2.1
ODS 11.2 ~~ Fb 2.5  ODS 12.0 ~~ Fb 3.0

So, take note of the 'encountered' ODS if you get that message, as it
will tell you what version of Fb you need to install instead.

(Actually, this just revealed a bug in Fb3's gstat.  It should have
reported '11.2', not '11'.)

BTW, Fb 2.5 and below *can* attach to databases with older ODS, so if
Fb 3 gives you the Wrong ODS version error, try installing V.2.5, as
its gstat -h and iqsl tools will read 11.0, 11.1 and 11.2.

However, if you need to make a gbak backup of your database, in order
ro restore it as ODS 12 under Fb 3, you will need to run the backup on
the actual Firebird server version that created it.

>I’m using the JDBC driver to connect from InfoCaptor.

>When using the ISQL tool to try and connect, this is what I see:
https://gyazo.com/b122c8c6de54be1d07a5240dc65c4ff6
>I don’t even get an error.

> I’m not sure if that means the connection was successful or unsuccessful.

 It means neither.  The CON> prompt there means isql is waiting for a
semi-colon, which is isql's line terminator.  But that is not a valid
CONNECT statement you have there, anyway, even with the semi-colon.
If you supply the user and/or password arguments to the CONNECT
statement, the syntax is:

SQL> CONNECT 'localhost:employee' USER sysdba PASSWORD 'masterkey';

Note that employee is an alias for
c:\programs64\firebird_3_0\examples\empbuild\employee.fdb. Read up
about database aliases - they are very friendly to have around.

As you did it, in Fb 3, with an embedded connect, you don't need
the PASSWORD argument at all.  You will need the USER argument as,
otherwise, you will be logged in under your Windows user name, which
may not, at this point, have the permissions you need to access
objects in the database.

>I was attempting to follow this tutorial:
>https://www.firebirdsql.org/pdfmanual/html/isql-connect-database.html

>When I try and connect with the same credentials with InfoCaptor via
>the JDBC driver, the error I get is simply “null”.
>The JDBC url I’m using is
>“jdbc:firebirdsqlLlocalhost/3050:T:/Database/ejdb.fdb”. And the user
>and password are the same as in the above screenshot.

3050 is the default port so you don't need it.  But that URI looks
weird, anyway.

Try

"jdbc:firebirdsql://localhost:T:/Database/ejdb.fdb"

But it can't work if drive T: is not a physical drive wired onto the
server that hosts Firebird.  What I'm saying is that, if you have only
drives C:, E: and F: on the machine, your databases must be on the
native path of one or more of those, not on some mapped network
address.  So if you have drive T: mapped to some network or local
address then drive T: is NOT a phsysical drive.

May I suggest that, given your uncertainty about the properties of
your databases, you uninstall Fb 3 and install v.2.5?
And don't try to run before you can walk.  Use isql or some Firebird-dedicated
third-party tool, rather than one from "out there" that treats all
database engines as created equal.

HB

Re: [firebird-support] Trouble establishing a connection to Firebird database files.

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Monday, November 27, 2017, 11:35:47 PM, Justis wrote:

> I’m running the latest version of Firebird on my windows PC, and
> I’ve got some .fbd files on my F:// Drive.
> I’m trying to connect to them via Firebird’s JDBC driver, but I’m getting 
> absolutely nothing.

The JDBC driver is that - a driver.  It is for connecting a Java
application to a database, through the Firebird server.  Unless you
have such an application, you don't have a starting point.

> I’ve tried restarting firebird, the machine.

You don't have to restart Firebird or the machine if you fail to
connect.  Firebird will just keep waiting until to offer a connection
string it likes.

> This is my first time with Firebird, so I apologize if I’m a bit
> slow to understand where to even start with trying to explain what’s 
> happening.
> What should I be checking first?

Well, Java is only one driver out of many.  Your best place to start,
AFTER reading the Quick Start Guide that is in the doc directory of
your Firebird installation, will probably be with the isql
command-line tool.  It is a native application that doesn't need a
driver, as it its interface is stitched directly into the fringes of
the Firebird engine.

If you are a total newbie, you should also grab the isql manual from
the Firebird documentation library.

> What info am I missing from this description in order to make assisting me 
> easier?

If you read the guff and still can't connect, you can copy/paste any
error messages from isql into your next message to this list.  Then,
everyone will know what kind of help you need. If/when you do, tell us
what version of Firebird you are trying to use.  That really matters,
as Firebird 3 cannot connect to databases made by earlier Firebird
versions.

Also, provide the connection string you use to request the connection,
e.g.

localhost:f:\databases\mydb.fdb -user sysdba -password masterkey

For issues trying to connect an app with your database through the
JDBC driver, there is a dedicated list.

HB

Re: [firebird-support] Moving DB from 32 bits to 64 bits

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Wednesday, November 15, 2017, 5:54:05 AM, m_brahi...@yahoo.fr wrote:

> I would like to know please, if we can use a DB in win64 but built
> under win32 . The client app is built with delphi 32 bits so the
> clients will connect to that DB in 64 bits. Is it possible ? Thanks
>   
1. Your database should be fine if moved from win32 to win64.  It is
recommended that you back up the database on the old platform with
gbak and restore it with gbak on the new. as a good housekeeping
measure.

2.  Firebird servers don't care whether the client is 32-bit or
64-bit.

2.  Clients must use the fbclient.dll that matches the bitness of the
*application*.  If your database is going to be restored for use under
Fb3 then you will need to download the win32 zip kit and extract the
32-bit fbclient.dll from it.  In the Fb2.5 win64 kit, there should be
a copy of the 32-bit client in the system32 subdirectory.  I don't
know why it is omitted from the Fb3 kits.

Helen

Re: [firebird-support] string right truncation error with LIKE predicate and UTF8 DB

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, November 3, 2017, 3:12:37 AM, Rustam wrote:

> I got an error executing SELECT query with LIKE predicate in case of UTF8 
> database,
> non-UTF8 connection charset and national characters in LIKE
> argument without mask symbols '%' and '_'

> Error is

> "arithmetic exception, numeric overflow, or string truncation
> -string right truncation"

> 1) Create database with default character set = 'UTF8'
> 2) Connect to DB with ANSI character set, e.g. 'WIN1251'


> isql -ch WIN1251

> 3) Execute SELECT query on any table with VARCHAR fields of any length
> with LIKE predicate on VARCHAR field, that meet following criterias:
> - argument not contain mask symbols '%' and '_'
> -   argument length greater then 1 symbol
> -   argument contain national symbols

> EXAMPLE:

 > Change console window font to Lucida Console (to allow cp1251 characters in 
 > console)

> Start ISQL:
> isql -ch WIN1251

> create database 'c:\databases\bug.fdb' user 'SYSDBA' password 'masterkey'
> default character set UTF8 collation UNICODE_CI_AI;


> create table TABLE1 (
>  FIELD1 varchar(50)
>
> insert into TABLE1 (FIELD1) values ('абвгде'); -- WIN1251 characters string

'абвгде' is transliterated to charset UTF8.

> This queries work fine:

> select * from TABLE1 where FIELD1 like 'abc%';
> select * from TABLE1 where FIELD1 like 'abc';
> select * from TABLE1 where FIELD1 like 'абв%';

As expected.  Client's WIN1251 input is transliterated to UTF8.

> This query fails:

> select * from TABLE1 where FIELD1 like 'абв';

The "bug" here is that the message for all string input errors is the
same.  Transliteration of the input argument in the failing query
would occur as in your third query above if the LIKE argument was
valid.  IMHO, it should give an invalid DSQL error, not that generic
string error.

> In last 2 queries in LIKE argument used string with national
> characters (in this case - WIN1251)

The difference between the two results confirms that transliteration
is healthy when you provide a valid argument for LIKE, i.e., provide a
wildcard character.

If you wanted to pass a query like that, you should use STARTING
[WITH]:

select * from TABLE1 where FIELD1 STARTING WITH 'абв';

Helen

Re: [firebird-support] Creating a conditional "order by" statement

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tuesday, October 24, 2017, 1:37:01 AM, j...@acousticdesign.co.uk wrote:

> I have a stored procedure along the lines of

> SELECT ID, Amount1, Amount2 ..

> FROM Table1.
> UNION
> SELECT ID, Amount1, Amount2...
> FROM Table2
> ORDER BY 1

> Is it possible to construct a conditional ORDER By clause that
> orders by Amount1 under certain conditions, and order by Amount2 under other 
> conditions?

Using DSQL, no, since direct DSQL statements are compiled in the BLR
code of the stored procedure.  You need to avail yourself of EXECUTE
STATEMENT. It's the sort of thing that this command is for.

> At the moment, the only way around it I have found (and I spent
> most of the night trying!) is to use an If clause and duplicate nearly all 
> the SQL

> IF (Condition1) THEN
> SELECT ID, Amount1, Amount2 ..
> FROM Table1.
> UNION
> SELECT ID, Amount1, Amount2...
> FROM Table2
> ORDER BY 1

> ELSE IF (Condition2) THEN
> SELECT ID, Amount1, Amount2 ..
> FROM Table1.
> UNION
> SELECT ID, Amount1, Amount2...
> FROM Table2
> ORDER BY 2

> This seems a crazy way of having to achieve my aim!

Yep.

> Any help gratefully received.

create procedure my proc
  (orderingcolumn char /* pass '2' or '3' depending on user input */
  )
  returns (
id bigint,
amount1 decimal,
etc. [columns you want in the left-to-right order you want]
)
as
declare variable execstring varchar (1000); /* whatever max. length you need */
begin
  execstring = 'SELECT ID, Amount1, Amount2 ..'
  || ' FROM Table1'
  || ' UNION'
  || ' SELECT ID, Amount1, Amount2...'
  || ' FROM Table2'
  || ' ORDER BY ' || :orderingcolumm ;

  FOR execute statement execstring
  into :ID, :AMOUNT1, etc. [your output variables]
  DO SUSPEND;
end

Looking at this problem from another angle...your examples are
just examples, of course...but on the surface it looks as if you would
better serve your need by using a DSQL statement directly, rather than
trying to bury it in a SP.  Or you could define a view, which would be
unordered, and supply the order by clause when selecting from it.

Helen

Re: [firebird-support] Wirecrypt

[Helen Borrie hele...@iinet.net.au [firebird-support]]

I wrote:
>> The Firebird 3 installer script didn't put it there but possibly
>> FlameRobin or Jaybird did.  Some client interfaces need to read
>> firebird.conf.  It won't conflict with the server's firebird.conf, per
>> se, but it would pay to keep the one used by the client app consistent
>> with the one used by the server.

Thursday, October 19, 2017, 9:13:39 PM, Mark wrote:

> Jaybird doesn't install anything, and AFAIK, neither does Flamerobin do
> anything with with firebird.conf. It is more likely the layout of the 
> specific firebird install on his Linux system.

More likely some other client app, then, as he seems to have installed
Firebird from the project package - since it is installed in
/opt/firebird.

FWIW, I'm on a Ubuntu-based Linux and their platform-specific Firebird
packages are a nightmare and usually horribly old.  I always install
from our own tar.gz because it just works, it's the latest sub-release
and I don't get files being spread all over the system.

Helen

Re: [firebird-support] Wirecrypt

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Wednesday, October 18, 2017, 9:52:36 AM, dferg...@gmail.com wrote:

> I am a newbie, like a total newbie just exploring databases for the
> first time.  I am using Lazarus and they all seem to prefer Firebird
> and lot of the documentation is geared that way.  I have
> successfully downloaded and installed Firebird onto ElementaryOS
> Loki.  I am trying to use DB Scema but can't connect to firebird
> from there.  I can connect to sqlite from dbscema and I can
>  connect  to firebird from Flamerobin.  I get the error:

> ncompatible wire encryption levels requested on client and server
> Incompatible wire encryption levels requested on client and server
> [SQLState:28000, ISC error code:335545064]
>
You CAN or you cannot connect from FlameRobin?

> I then followed these instruction and changed my firebird.conf file
> to "Wirecrypte = Enabled" as seen here:

Hopefully you didn't add that extraneous letter 'e' to the parameter
name.  And you removed the '#' character from that line.

>   - https://www.firebirdsql.org/ file/documentation/drivers_
> documentation/java/3.0.2/ release_notes.html#notes-on- firebird-3-support

If you want to enable wire encryption, you will need to have two
things in addition to enabling it in firebird.conf:

1.  the fbclient.so that comes with Firebird 3.  Older Firebird
versions do not support wire encryption.

2.  a language interface layer that knows about Fb3 wire encryption so
that the application can pass it in the API.

For FlameRobin, you need fbclient.so:  the 64-bit version if you are
using 64-bit FR;  otherwise, you need the 32-bit version.  You match
the bitness of the client to the bitness of your application, not the
bitness of the Firebird server.
Unfortunately, if you have installed the 64-bit Firebird server and
need the 32-bit client, you'll have to grab the x86 kit from the
download area and extract the client from it.  For some reason that
totally escapes me, we still do not provide any client-only kits in
the download area.

> But I still can't connect.  I also tested with DBerver but have the
> same problem.  I downloaded and installed the java driver to version
> 3.0.2.  But still no success at all.  I have no idea what t  o do. 
> All the programs I have tried to use to connect to Firebird don't
> work and they all tell me to come here.

You've named some applications I've never heard of.  Are you sure they
actually support Firebird?  If they claim to do so, check what driver
they need, e.g., ODBC, Python, etc.

In the case of Jaybird, you have both the client and the language
interface rolled into one.  Jaybird supports the API natively.
However, it appears Jaybird does not support wire encryption yet.  To
ask about that, subscribe to the firebird-java support list, see
https://www.firebirdsql.org/en/mailing-lists/

> I would attach the firebird.conf but there doesn't seem to be a way
> to do that.  the firebird conf that I found was in
> /etc/firebird/firebird3.0

The Firebird 3 installer script didn't put it there but possibly
FlameRobin or Jaybird did.  Some client interfaces need to read
firebird.conf.  It won't conflict with the server's firebird.conf, per
se, but it would pay to keep the one used by the client app consistent
with the one used by the server.

> I also found an other firebird.conf in  /opt/firebird/.

That's the one the installer put there.

> I h  ave no idea why there would be two of them.  I
> have only ever installed FB once.  I have tried changed the
> Wirecrypt on both, then on one then the other.

If you change a configuration setting you have to restart the Firebird
server, unless you are running Classic. (But, if you didn't change the
ServerMode parameter, then you are running Superserver and you DO need
that restart.)  If FR or another app is depending on the settings in
its own firebird.conf file then you;d probably need to restart that
app after changing that file, too.

Another possibility is that the FlameRobin package you installed is
pre-Firebird 3.0 and has installed an old fbclient.so in its WD.  So
check that as well.

I strongly recommend that you study the two PDF books in
/opt/firebird/doc.  There you have the Quick Start Guide and the
release notes.  Our release notes are very detailed.

Helen

Re: [firebird-support] embedded database with Chinese path

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Hamish,

Thursday, October 5, 2017, 4:53:24 PM, you wrote:

[...] all that

Have you considered trying a symbolic link?
https://www.howtogeek.com/howto/16226/complete-guide-to-symbolic-links-symlinks-on-windows-or-linux/

Re: [firebird-support] Cannot grant user by code

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tuesday, October 3, 2017, 5:02:21 AM, Pierre wrote:
> I want to add readonly user creation/granting after database initialisation.

> I create the database using a bunch of SQL scripts. After the last
> script runs, I create a user using "create user MYUSER password
> 'myPassword'" the I grant this user read only (select, references)
> access to every table of the database using EXECUTE BLOCK :

> https://gist.github.com/zedalaye/6300b679e56349c40b973d35bb4c91ee


> On Transaction Commit, I get this error :

> add record error violation of PRIMARY or UNIQUE KEY constraint
> "INTEG_2" on table "PLG$USERS" Problematic key value is
> ("PLG$USER_NAME" = 'RO_USER') Unsuccessful execution caused by
> system error that does not preclude succe  ssful execution of
> subsequent statements GDS Code: 336723987 - SQL Code: -901 - Error Code: 19.

CREATE USER adds users to the security database, whichever one you have
configured as security database.  The key violation occurs because
either (1) you already created that user using that plug-in or (2) the
user does not exist.

Regarding (1), you can have more than one user having the same user
name as long as they are created using different plug-ins.  For your
scripts it would be wise to specify the plug-in explicitly with a
USING PLUGIN clause, even if you want to use the default plug-in.

Regarding (2), remember that the CREATE USER command is run from the
connection to the application database, but under the surface, the
instructions are applied to PLG$USERS in the security database. Your
GRANT commands apply to the application database.  The CREATE (ALTER/DROP)
USER command needs to be committed for its effects to become visible
for reference by statements affecting the application database.

If your problem is caused by (2), I think you will need to isolate
your CREATE USER statement(s) into a different SP, to be run and
committed in a separate transaction, before you run the script
assigning the permissions in the application database.

Helen

Re: [firebird-support] Re: Installing Firebird on Fedora 26

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Allan,

Tuesday, October 3, 2017, 1:02:48 AM, Allan Jardine wrote:

> In the mean time I've tried installing the Firebird package from
> dnf (I actually tried this first and have just tried it again). It
> appears to install okay, but then any interaction I try to make with
> the database I get a "Install incomplete" message.


> $ isql-fb 
> Use CONNECT or CREATE DATABASE to specify a database
SQL>> connect localhost:employee user sysdba password masterkey;
> Statement failed, SQLSTATE = 28000
> Install incomplete, please read the Compatibility chapter in the release 
> notes for this version


> This happens with the cli, FlameRobin and PHP.

So - did you read the Compatibility chapter and follow the
instructions there?

HB

Re: [firebird-support] error 10054

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Thursday, September 21, 2017, 12:24:52 AM, "Sabbagh, Nariman" 
 wrote:

> I am using Ames application which is using firebird, Ames keeps
> hanging and it is because of firebird giving error 10054. Could you please 
> advise , thank you
>

Well, that is a network error, not coming from Firebird.  Firebird
will obligingly report and log network errors, but it has no control
over what happens in your network.

You say the AMES application "keeps hanging".  That is not "because of
Firebird" but will be due to one of two main sources of trouble:

1) The application crashes while a user at a client node is connected
to the database.  This may be due a bug in your software or it could
be just a careless or impatient user ending a slow session by just
switching off instead of exiting gracefully from the application.

2) Your network service is losing the connection between the Firebird
server and the user experiencing the problems, due to some fault in
the network (hardware or configuration).

>From your email address, I'm guessing that this "AMES" software is the
aircraft maintenance scheduling product from the Omega company in
Texas, U.S.A. If you are able to eliminate network faults then your
only option is to contact their software support, giving a better
description of the problem than the one you provided here.

Helen

Re: [firebird-support] file size and transactions

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello shg_siste...@yahoo.com.ar,

Friday, September 15, 2017, 2:53:19 AM, you wrote:

> I read somewhere that the "read only" transactions are absolutelly
> harmless to the GC and sweeping. Is that so?

It is true for READ ONLY transactions that are started in READ
COMMITTED isolation mode.  In other isolation modes, long-running
transactions in both READ ONLY and READ WRITE will block GC and evade
sweeps.

Helen

Re: [firebird-support] file size and transactions

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello shg_siste...@yahoo.com.ar,
 Thursday, September 14, 2017, 2:10:23 AM, you wrote:

> Hello! I'm noticing a small increase of my database size. Now it
> has 566,992 KB. After a backup/restore the size goes to 533,640 KB.
> I don't have long running read/write transactions (well... I think)

There is nothing wrong with your transaction statistics.
The reason why the database "grows" is this:

A Firebird database is stored in one file.  This file is made up of
"pages" - chunks of disk that the Fb engine keeps account of
individually.  In your case, each page is 4Kb.

The engine is constantly performing maintenance on these pages - this
is called garbage collection and sweeping.  GC and sweeping are
related to each other but they are not exactly the same.  Between
them, they identify pages that no longer contain record versions that
are needed for anything, so they release these pages for re-use.
Thus, changing or deleting records does NOT reduce the size of the
database file.

A newly restored database has no spare pages waiting for re-use.  That
is why the file has its "minimum" size immediately after a restore.
It has exactly the number of pages needed to store everything that was
backed up.  Garbage pages are not backed up at allwhich is why a
badly managed database can really only be properly cleaned up by a
full backup and restore.

At the same time as GC and sweeping are going on, the engine is
acquiring new pages (two at a time - in your case, 8 KB)) from the operating
system whenever it has insufficient empty pages available to store a
new record or record version from an insert or update.  Old record
versions, from updates and deletes, stay on their pages until the
GC/sweep process ultimately frees them.  When transactions are
not well managed, the housekeeping gets "stuck" on pages that the bad
management keeps in an "interesting" state.  If the programmer has not
taken good care of committing transactions that are finished, the
engine has no choice but to keep those pages in an "interesting"
state.  That is when you would see an increasingly large gap between
the oldest transaction (oldest interesting, or OIT) and the oldest
snapshot (OST).  Then, you can query MON$TRANSACTIONS to try to
discover where the problems are.  But your stats look fine.

Know, too, that the engine needs pages for other things besides data -
it maintains index pages, blob pages, etc.  Once a page has been used
as one type of page (data or index or blob) it cannot be reused for
another type of storage.  So some of your operations could be
consuming quite a lot of new pages and this is not an unhealthy sign,
either! ;-)

Helen

Re: [firebird-support] FB 3.0 Replacing ICU libraries

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Magnus,

Tuesday, September 12, 2017, 9:53:47 PM, you wrote:

> From what I have been able to read, the documentation that exists
> seems to be a mix on both 3.0, 2.x and even 1.5 making it a little unclear.

If you have the IBPhoenix DVD, you will find it documented in some
detail in Chapter 9 of The Firebird Book Second Edition eBook.  The
same in hard copy is in Chapter 8 of TFB2e Volume 1 (Firebird
Fundamentals).

Helen

Re: [firebird-support] Re: Firebird Alpha 4 Release Notes

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> 10.09.2017 02:13, Daniel Miller wrote:

>> The misunderstanding is probably mine - but I've never heard of 34-bit
>> values/precision before.  Is that a typo for either 32 or 64?  Or "34"
>> is correct?

DY replied:

> 34 *digit* precision. If "34-bit" is mentioned there, then it's a mistake.

It is a mistake.  It has been corrected in the online notes.

Any more discussion about anything in the Fb 4 Alpha, please take it
to firebird-devel.

^heLen^

Re: [firebird-support] FreeAdhocUDF and Firebird 3 not working

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, September 8, 2017, 9:08:00 PM, ke...@dojitraders.com wrote:

> Thank you for your reply. I mentioned that this problem was
> reported in the Tracker system (Bug CORE-5306) and my problem is just the 
> same.

>From that discussion, Mr da Costa and you are encountering the same
symptoms.  He was trying to use the Tracker to fix the symptoms.  It is
an old report and core devs were involved, meaning they investigated
it and couldn't reproduce it.

> Basically I get the following type of error

> invalid request BLR at offset 36.
> function EXTRACTDAY is not defined. 
> module name or entrypoint could not be found.

When you upgrade a database, objects are not recompiled (reconverted
to new BLR).  You have an error somewhere that 2.5 ignored, while 3.0
reacts to something in the legacy BLR that is not valid.  With that
error, a likely cause is wrong definition of the module name, i.e., it
was defined with the full name of the DLL instead of the 'name' portion
alone. That is not "newly wrong" - it always was - but it was not
necessarily enforced in the past.  My guess is that someone caught
this in some core code cleanup during implementation of stored
functions in Fb3 and you had historically faulty declarations that
became victim to an inherited hack.

Check that:
select * from rdb$functions

If you see anything except the name part of the DLL module, you have
found the problem.  Fixing it won't be so simple, as you cannot edit
the system tables in Fb 3.  You will have to find and eliminate any
dependencies, drop the offending functions and re-declare them.

If you have a trigger or a procedure that invokes the function, as a
reality check, you could apply a CREATE OR ALTER operation to it,
using the extracted source text.  If it refuses to compile, note the
error that is thrown.  Chances are, it will get you closer to the
source of your problem.

Make sure you give EXECUTE privs on the function to the trigger or
procedure first.

> I only have one user on all of my installs and that is SYSDBA.

So apparently you're not bothered by vulnerabilities.  Actually, if
you were, you would not even consider using UDFs.

Helen

Re: [firebird-support] Re: Problem with linked table

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, September 8, 2017, 9:31:43 PM, Adriano Novelli wrote:
> i use a odbc connection to connect to the database.

> on  every pc I have configured a odbc connection with the same name
> and  configured the database path by specifying the IP address of the server

> as I said before, if I do the db connection test from every single pc, 
> everything works.


> I repeat the steps to replicate the problem:

> i create a db access 2007 and i put it in a shared folder (every pc
> have a mapped network drive (Z) to access this database

> 1) from pc 1, i open the db and link some tables u  sing a odbc
> connection. if i open (in access 2007) the linked table, i read the data
> 2) from pc 2, i open the db shared and i view the linked tables. 
> if i open (in access 2007) the linked table, i have an odbc error "connection 
> failed".
> 3) from pc 2, i remove the linked tables. using a odbc connection,
> i re-link the tables and now (on pc 2) i read the data!
> 4) at the moment, on pc 1 i can not read it anymore data from the linked 
> tables

> in conclusion, I can read the tables data only on the pc that I use to link 
> the tables

This is the behaviour you would see if your clients are using Embedded
to connect to the database.  This works only for one connection:  all
others are blocked until the active client detaches from the database.

Please understand the architecture.  For multiple users, you need the
full Firebird server installed and running as a service on the host
machine where the database is physically located.

Each user must make a remote connection to the physical location, not
to a share.

Let's say this host machine has the network name 'nostroserver' and
the database bdati.fdb is located in c:\dati.  This must not be a
share.

The full path to your database from each remote client is:

nostroserver:C:\dati\bdati.fdb

On his/her own computer, each client needs
(1) a copy of your software
(2) The Firebird ODBC driver
(3) A data source configured to connect to the database using the full
path quoted above
(4) The Firebird client fbclient.dll, copied from the server
installation.

The client components can be accessed on a share, if you like.

Note:
You need the 32-bit client if your software and driver are 32-bit.
If your server installation is 64-bit then the fbclient.dll in the
\bin\ directory there will not work with 32-bit clients.  You can
extract the 32-bit client from the \bin\ directory of the 32-bit zip
kit.

Helen

Re: [firebird-support] FreeAdhocUDF and Firebird 3 not working

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello ke...@dojitraders.com,

Friday, September 8, 2017, 11:10:39 AM, you wrote:



> I am thinking FreeAdhocUDF is a dead project. It has not been updated for 
> many years.


> The main thing though is that these UDFs work ok in Firebird 2.5. I
> have also developed a UDF (DLL) in Delphi, and that does not work
> either. It used to in 2.5 (still does). I thought it better to
> mention the FreeAdhocUDF issue otherwise it would be my fault that my own UDF 
> does not work!


> There is obviously something different about V3 that is stopping
> some UDFs from working. In my own limited experience, it is stopping ALL UDFs 
> from working.

What does "not working" mean?  Do you get any exception messages?
Crash the server?  What?

It's possibly a permissions problem.  Fb3 introduced EXECUTE
privileges on UDFs.  The initial situation is that the user that
"owns" an externally defined UDF is the user that originally declared
it.  So, for example, if it was declared by SYSDBA, then only SYSDBA
can invoke it.  SYSDBA will need to grant the EXECUTE privilege on it
to any users who are going to need to invoke it.

You might care to check the setting for UDFAccess in the firebird.conf
of your V.2.5 installation.  It could be you configured that and then
forgot about it.  The default UDFAccess setting in Fb3 is Restrict
UDF.  That causes it to look only in Firebird's \UDF subdirectory for
the declared UDF modules.


> And this is not a trivial problem. I have spent days trying to
> remove UDFs from my existing 2.5 version databases, and it is not an easy job.

Well, it's hard to help with a solution to your non-trivial problem if
you provide only a trivial account of what goes wrong.

Helen

Re: [firebird-support] Implicit path for Database

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Saturday, September 2, 2017, 9:35:20 AM, Neagu wrote:

> In databases.conf I can put the lists of alias of Known databases, but I
> look for a way to specify only the alias of path where the databases are
> store.


> Now, If I not specify a path in connect string, the databases is search
> in C:\Windows\System32

And, on POSIX, it will search in the current working directory (cwd).

You can (and should) configure which directories the server is allowed
to search for databases to connect to.  The parameter for this, in
firebird.conf, is DatabaseAccess.  By default, it is set to 'Full',
meaning the server can accept connections to databases anywhere.  The
alternative settings are:

None.  This takes no arguments.  The server can connect only to
databases whose full paths are specified in databases.conf.

Restrict.  This takes a list of one or more filesystem paths where the
server may connect to databases located there.  If you have databases
in sub-directories of that path, you must list each path separately.
Use semicolons to separate multiple paths.

> Will be good also a way to specify the path where the databses are store
> if I not specify a path in connect string.

Something like this is possible, as long as you want to store the
database in Firebird's root directory *AND* that path is specified as
a parameter of DatabaseAccess Restrict (or DatabaseAccess FULL is the
current setting). In general, though, storing a database amongst the
software components is not a brilliant idea.

Read the built-in documentation for DatabaseAccess in firebird.conf.

Did you know that you can create an alias for a database that does not
yet exist?  For example.

myNewDB = d:\databases\development.fdb

After that is saved, you can create a new database named
'development.fdb' in d:\databases by passing the alias as the database
name parameter in your CREATE DATABASE statement.  In this case, your
DatabaseAccess configuration can be NONE (because the database is
known through databases.conf) or FULL (server does not care where a
database is located).  RESTRICT will work, too, if d:\databases is
included in the list of allowed directories.

Don't forget that any changes you do in firebird.conf will take effect
only after restarting the Firebird service.

Helen

Re: [firebird-support] OT: Firebird 4.0 xinetd Support on Linux Replaced

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, August 25, 2017, 3:36:51 AM, Ismael wrote:

> Sorry for the off topic, but I  have a doubt that maybe some of you can 
> clarify


> I was  reading Firebird 4.0 Release Notes and I found the following note:

Yes, any questions about alphas, betas, snapshots, pre-release
versions are right off topic here.  Take them to firebird-devel,
please, as instructed in the release notes.


> Tracker ticket CORE-5238
> On Linux, Firebird 4  uses the same network listener process
> (Firebird) for all architectures. For  Classic, the
> main (listener) process starts up via init/systemd, binds to the 
> 3050 port and spawns a worker firebird process
> for every connection—similarly  to what happens on Windows.

> This has caused me the following  doubt:
> With this modification Firebird 4.0 will work on  linux system that do not 
> use systemd? Yes or  no?

The notes say "...starts up via init/systemd..." which means one or
the other.  You're in trouble if your system does not use either one.
;-)

And no more Fb 4 questions here, please.

^heLen

Re: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> I’ve been having quite a few crashes on a new server with not much databases
> or requests. terminated abnormally (4294967295)

> It seems to be very random, there are not many different operations going
> on.

>  

> It’s a google cloud Windows vm, I’ve checked for hardware / driver problems
> but it seems to be ok.


> Firebird 3 Super

> Conf : 

> ServerMode = Super

> DefaultDbCachePages = 5

*EACH DATABASE* will be consuming up to (5 * page size) on cache.
Why do you set DefaultDbCachePages so high  The Firebird server
needs RAM for more things than just the cache.

Also, check whether you have enough space on disk to take the temp
files.  Even on a low-traffic system, one ordered or grouped query on
a long table could easily be too big to store intermediate files in
memory, especially as you are eating so much RAM with your caches.
Those files will go to disk and you simply might not have enough disk
space available.
Note, too, that on a VM it is strongly recommended that you configure
TempDirectories explicitly and not rely on a preconfigured temp or tmp
directory that might be missing or shared by greedy apps.

Helen

Re: [firebird-support] Multi-column Foreign key

[Helen Borrie hele...@iinet.net.au [firebird-support]]

>> Let's create two unites from one lot:
>> 
>> INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A1', 'L1', 'S1',
>> 'abc'); INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A2',
>> 'L1', 'S1', 'bcd');
>> 
>> I expected that the following instruction will not be exectued:
>> INSERT INTO LOCATION (ID, UCODE, ULOT, USITE, ZONE, WAREHOUSE)
>> VALUES (1, 'A3', NULL, NULL, NULL, NULL); because there is no A3 entry in
>> unites table, but it IS! Firebird allows to add record with value which 
>> doesn't
>> exist in master table.

> I believe that your schema for the LOCATION table is incorrect.  

> UCODE, ULOT and USITE should be described as NOT NULL, since a
> Location must relate to a UNITES (which has CODE, LOT and SITE described as 
> NOT NULL), no?

As Karol and Sean have noted.

Also, are you sure you have this relationship facing in the right
direction?  Do locations depend on units?  or do units depend on
locations?

Normally, a Location would be a primary entity and Unit a secondary
one. So a foreign key relationship wants to ensure that a Unit is not
booked in having a Location that does not exist, including any key
elements that are NULL.

Obviously, language differences are in play here, so your
conceptualization of the relationship might be correct for your case.
But if the dependency is as I described it, then the FK should be in
Unites, referencing the PK in Location.  And - simply - never allow
NULL to be written to a constraint element.

AFAIR, Firebird follows some standard that allows one record with one
NULL element, so with your 3-element key, you have the potential to
have three useless reference records.  It's one of those cases where
"just because you can does not mean you should".

Helen

Re: [firebird-support] Firebird 2.5 rejecting connections

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Monday, August 7, 2017, 5:54:06 AM, you wrote:


>>
>> Did you actually replace the client lib on the remote box with the
>> appropriate-bitness one from the installation?

> System is x64 but x86 version of firebird is required due to legacy UDF.
> I have tried deleting the DLLs from System32 and SysWOW64 to avoid PATH
> issues but that didn't have any effect.

So what are you talking about here?

You're running 32-bit Firebird on a 64-bit box.  That's fine.

Still talking about the server box:  you want the 32-bit client to run
the Fb system utilities because you installed 32-bit utilities.

For your own applications, running them on the server through TCP/IP
local loopback, you want the Fb client's bitness matching your
*application*, regardless of what bitness the Fb server is;  likewise
any driver layers such as ODBC.

On the remote client box, same story.  Match the client to the bitness
of the application and driver, not the remote server.  If you
installed a 32-bit kit, you would not get a copy of the 64-bit client.
 In the 64-bit kits you get both, with the 64-bit one in $firebird\bin
 directory and the 32-bit one in $firebird\WOW64.

 Also, considering the problem with localhost on the server box, have
 you checked your system variables to make sure FIREBIRD is
 pointing to the right place?  It's just a stray thought, as I've
 never heard of connection problems associated with that variable
 being missing or bad.  I'd think it would only be a problem if it was
 set previously for an older version;  while the newer version is
 located in a different subdir.

 Helen

Re: [firebird-support] Firebird 2.5 rejecting connections

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Sunday, August 6, 2017, 7:07:02 PM, you wrote:

> Hi all.

> On a brand new install for Firebird 2.5.7 (x86) I am having issues
> connecting to the server via TCP/IP.
> The error message is always the same "Connection rejected by remote
> interface" however I have tried several DB utilities and I can connect
> fine via local protocol but not via network address.

> I'm suspecting a library issue (conflicting fbclient.dll or gds32.dll) but
> for the love of God I have been unable to find it anywhere on the system.

> Anyone have any tips on the issue?

To ask the obvious, is port 3050 open on the server?

How would you recognise "a conflicting fbclient.dll or gds32.dll"?

Did you actually replace the client lib on the remote box with the
appropriate-bitness one from the installation?

Helen

Re: [firebird-support] unpacking backup with firebird version 1.5 (windows 7)

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, July 28, 2017, 9:03:19 PM, robert rottermann wrote:

> I proceeded as Helene suggested

> I  executed gbak.
> However this produced zillions of warnings of the form
> gback: do not recognize table attribute 0 -- continuing

> this goes on now for some 15 minutes.
> A database VAS.FDB was created
> I can open it from my linux box using flamerobin.
> The tables seem to exist all, their structure is ok as far as I can 
> judge from a first glance.
> But alas, they are empty!!

> please advice.

Something is wrong with that backup.
In your original message, you said:

>when I do [a restore) on my linux box

>sudo gbak -c VAS.bak vas_db/VAS.fdb

>this seems to work fine (alltough i can not use the result)


Questions:

1. What did you mean "I can not use the result" ?

2. Are you able to connect to the original database on Linux and see data
in the tables?  Meaning - do you have a "good" database somewhere?

3.  On Linux, what information does FlameRobin provide about the
on-disk structure and the status of the database?  (You can also get
this information using gstat -h, or using the SHOW DATABASE command in
isql.)

4.  Have you tried making your own gbak backup of the "good" database
on the Linux box?

5.  Have you tried file-copying the good database onto the Windows box
and connecting to it?

Helen

Re: [firebird-support] using embedded

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Thursday, July 27, 2017, 7:33:43 AM, prosse...@yahoo.com wrote:

> I want to get a delphi7 program that works with the full server to
> work with embeded... I am a getting 'database unavailable' whatever I try

> I have followed the instructions to the diligently - copying and
> renaming the files so what am I missing?

First things first:  embedded works differently depending on what
version of Firebird you are using.  Since you mentioned "copying and
renaming files" one assumes it's not Fb 3.  As a general comment, it
makes no sense to post a question without the basic information.

Next, you won't be able to run your app from within the Delphi IDE
using an embedded connection.  That's because the IDE is already
connected to the database in its own workspace, thus precluding any
other connections.  So, try running the executable directly,
with the Delphi project closed down.  It's my bet this is your
problem.  Don't make the mistake of treating embedded as a development
option.  It's intended for deployment.  Use the full server for
development and watch out that you don't hard-compile the connection
protocol.

Another cause could be that your renamed client can't load because a
DLL named fbclient.dll is already loaded - just not the one you need!

Helen

Re: [firebird-support] unpacking backup with firebird version 1.5 (windows 7)

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello robert,

Tuesday, July 25, 2017, 4:32:12 PM, you wrote:

> When I try to unpack it using the following command:

> c:\Program Files\Firebird..\bin\gback.exe -c VAS.bak VAS.fdb

1. Make sure the server is running.
2. You need the file path and, if using Classic mode, the hostname.

> This produces errors:

> gbak: Error: unavailable database VAS

> gbak: Error: failed create database VAS

> gbak: Error: Exiting before completion due to errors.


> when I do the same on my linux box

> sudo gbak -c VAS.bak vas_db/VAS.fdb

> this seems to work fine (alltough i can not use the result)

> can somebody please tell me, how to unpack that file on windos?

Let's assume you want the database to be in a directory called
"vas_db" and you have created this directory on drive D.  For
simplicity, let's say you have placed the backup there as well.

The backup is just a FILE so you don't need the host name for that.
You are restoring to a DATABASE and for that you need a host name on
Classic.

You want

gbak -c d:\vas_db\/VAS.bak localhost:d:\vas_db\VAS.fdb -user sysdba
-password yourpwd

The default sysdba password on a new installation is masterke, in case
you don't know that already.

The drive MUST be one that is physically connected to the host box.
It can't be a share or anything like that.

Helen

Re: [firebird-support] Firebird Connection String

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Lee,

Sunday, July 23, 2017, 5:28:05 AM, you wrote:

> Am working on an old VB6 project and need to connect to some Firebird Data.
> Having problem Connecting to database when it's a network address.

[..]

That's because Firebird (by design) does not connect to databases on
network drives.  The database must be on a drive that is physically
connected to the machine that is hosting the Firebird server.

> If my data is on network share at \\MyNetShare\data\sprox.gdb then
> what value do I use for dbServer and dbFileName?

None.  From a remote client, use the server's host name or internal IP
address with the file path or (better) a database alias that you have
created in aliases.conf (pre Fb3) or databases.conf (Fb3 onwards).
Here's an example of an alias entry:

sampledb = d:\data\sprox.gdb
(no quotes)

> Tried dbServer="" and dbName="\\MyNetShare\data\sprox.gdb" and it
> works but only if running program As Admin (Win10)

> Any suggestions???
On Windows, you can use either the Windows networking protocol (WNET)
(noisy, outdated)

\\hostname\d:\data\sprox.gdb
\\hostname\sampledb

or TCP/IP (preferred):

hostname:d:\data\sprox.gdb
hostname:sampledb

There are optional elements for more complicated connections, too.  You
should find them in the Quick Start Guide, in the \doc\ folder of your
server installation or in the Documentation library at the Fb we site.

Note, there is a setting you can configure in firebird.conf to enable
access to network drives but it should never be used to attempt access
to a read/write database.  It's a recipe for corruption.

Kind regards,
 Helen Borrie