Set,

Thanks for the reply. I should've added that on firebird 1.5.2, the optimizer
correctly identifies the index for use by the 'order by'. It takes sub-second
(compared to 45 or so on 1.0.3).

I see all your points; I'm naturally averse to subselects! Your query seemed to
produce same results, but did take longer than when 1.5.2 used the index. Isn't
the subselect going to be run for every row that 'hits' in the main query?
Won't the join syntax be generally faster (given the index being used, of
course)?

On the 'LEFT JOIN' point, isn't specifying 'LEFT' just syntactically complete?
i.e. without it, it's a presumed 'LEFT' join, right ?

Thanks very much for the help!
Kevin

> Subject: Re: Query plan not using index
>
>
> Hi Kevin!
>
> --- In firebird-support@yahoogroups.com, "Kevin Herrmann" wrote:
> > This query is horribly slow on Firebird 1.0.3 UNTIL I remove the
> > 'order by'. I think I've included all things I need.
>
> Well, maybe you've included all things you need, but not everything I
> need - you forgot the plan when you remove ORDER BY.
>
> > Here is the relevant Info:
> >
> > select d.*, e.NAME, e.GIVENNAME, a.EVENTTYPE from documents d
> > left join entity e on d.subject_ID=e.id
> > left join auditmessage a on d.id = a.id1 and a.eventtype = 19
> > where d.doc_type in (5,6,7)
> > and e.status=0
> > and e.name <> 'MACRO'
> > and e.AttendingID=2
> > and a.ID1 is null
> > order by d.doc_category;
>
> Hmm, strange query. Why select a.EVENTTYPE when it will always be
> null? Later on you demand that a.ID1 is null, something which means
> (in my eyes, you better check) that your query can be rewritten as:
>
> select d.*, e.NAME, e.GIVENNAME from documents d
> join entity e on d.subject_ID=e.id
> where d.doc_type in (5,6,7)
> and e.status=0
> and e.name <> 'MACRO'
> and e.AttendingID=2
> and not exists(select * from auditmessage a
> where d.id = a.id1 and a.eventtype = 19)
> order by d.doc_category;
>
> As you can see, I also removed 'left' from entity, I don't think there
> should be any difference in the result set between a left [outer] join
> and an [inner] join in this case (again, I may be wrong).
>
> > There is an index on doc_category, but the optimizer doesn't want to
> > use it. Here is the Plan:
>
> Well, why should it? The return set isn't limited to documents, and
> the result set has already been limited from that table due to the
> doc_type being between 5 and 7. But you do limit the choices of the
> optimizer by using left join rather than inner joins.
>
> HTH,
> Set
>
> > PLAN SORT (JOIN (JOIN (D INDEX
> > (DOCUMENTS_TYP_X,DOCUMENTS_TYP_X,DOCUMENTS_TYP_X),E INDEX
> > (RDB$PRIMARY8)),A INDEX (AUDITMESSAGE_EVENTTYPE_X,
> > AUDITMESSAGE_ID1_X)))
> >
> > Here is the DDL:
> >
> > CREATE TABLE DOCUMENTS (
> > ID INTEGER NOT NULL,
> > DOC_TYPE SMALLINT NOT NULL,
> > SUBJECT_ID INTEGER,
> > SESSION_ID INTEGER,
> > CREATED DATE NOT NULL,
> > METADOCUMENT_ID INTEGER,
> > ATTRIBUTES INTEGER,
> > DESCRIPTION VARCHAR(250),
> > DOC_CATEGORY SMALLINT
> > );
> > ALTER TABLE DOCUMENTS ADD PRIMARY KEY (ID);
> > CREATE INDEX DOCUMENTS_CAT_X ON DOCUMENTS (DOC_CATEGORY);
> > CREATE INDEX DOCUMENTS_SID_X ON DOCUMENTS (SUBJECT_ID);
> > CREATE INDEX DOCUMENTS_TYP_X ON DOCUMENTS (DOC_TYPE);
> >
> > CREATE TABLE ENTITY (
> > ID ENIGMA_ID /* INTEGER NOT NULL */ NOT
> NULL,
> > ENTITYTYPE SMALLINT NOT NULL,
> > NAME VARCHAR(48) NOT NULL,
> > ROLE_IDS VARCHAR(64),
> > BIRTH DATE,
> > MRNUM VARCHAR(16),
> > SS CHAR(9),
> > SEX CHAR(1),
> > RACE VARCHAR(16),
> > STATUS INTEGER,
> > PAGER VARCHAR(20),
> > EMAIL VARCHAR(48),
> > TITLE VARCHAR(48),
> > DEPARTMENT VARCHAR(48),
> > COMPANY VARCHAR(48),
> > ISPATIENT CHAR(1),
> > ISEMPLOYEE CHAR(1),
> > ISPHYSICIAN CHAR(1),
> > PHOTO BLOB SUB_TYPE -3 SEGMENT SIZE 1024,
> > HOME_ID ENIGMA_ID /* INTEGER NOT NULL */,
> > WORK_ID ENIGMA_ID /* INTEGER NOT NULL */,
> > DEA VARCHAR(20),
> > AMA VARCHAR(20),
> > PREFIX SMALLINT,
> > SUFFIX CHAR(16),
> > IVERSION SMALLINT NOT NULL,
> > IOBSOLETE CHAR(1),
> > CLASSSIGNATURE VARCHAR(64),
> > PHYSICIANS VARCHAR(128),
> > USEROID INTEGER,
> > ATTENDINGID INTEGER,
> > MIDDLE VARCHAR(16),
> > OTHERMRNUMS VARCHAR(40),
> > NOTEBOOK VARCHAR(2048),
> > PHARMACY_NAME VARCHAR(64),
> > PHARMACY_PHONE CHAR(20),
> > PHARMACY_FAX CHAR(20),
> > USER_SETTINGS BLOB SUB_TYPE 0 SEGMENT SIZE 80,
> > ISGUARANTOR CHAR(1),
> > GUARANTORID INTEGER,
> > GUARANTORRELATIONSHIP CHAR(32),
> > INSURANCEID_1 INTEGER,
> > INSURANCEID_2 INTEGER,
> > INSURANCEID_3 INTEGER,
> > SIGNATURE BLOB SUB_TYPE 0 SEGMENT SIZE 80,
> > GIVENNAME VARCHAR(64)
> > );
> > ALTER TABLE ENTITY ADD PRIMARY KEY (ID);
> > CREATE INDEX ENTITY_ATTENDING ON ENTITY (ATTENDINGID);
> > CREATE INDEX ENTITY_GIVENNAME ON ENTITY (GIVENNAME);
> > CREATE INDEX ENTITY_NAME ON ENTITY (NAME);
> > CREATE INDEX ENTITY_ROLE ON ENTITY (ISPATIENT, ISEMPLOYEE,
> ISPHYSICIAN);
> > CREATE INDEX ENTITY_STATUS ON ENTITY (STATUS);
> > CREATE INDEX ENTITY_USEROID ON ENTITY (USEROID);
> >
> > CREATE TABLE AUDITMESSAGE (
> > EVENTTYPE SMALLINT,
> > EVENTDATE DATE,
> > USERNAME CHAR(16),
> > ID1 INTEGER,
> > ID2 INTEGER,
> > AUDITTEXT VARCHAR(255)
> > );
> > CREATE INDEX AUDITMESSAGE_EVENTDATE2_X ON AUDITMESSAGE (EVENTDATE);
> > CREATE DESCENDING INDEX AUDITMESSAGE_EVENTDATE_X ON AUDITMESSAGE
> > (EVENTDATE);
> > CREATE INDEX AUDITMESSAGE_EVENTTYPE_X ON AUDITMESSAGE (EVENTTYPE);
> > CREATE INDEX AUDITMESSAGE_ID1_X ON AUDITMESSAGE (ID1);
> > CREATE INDEX AUDITMESSAGE_USERNAME_X ON AUDITMESSAGE (USERNAME);
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 3
> Date: Fri, 04 Feb 2005 10:48:32 +0000
> From: Lester Caine <lester@...>
> Subject: Re: Metadata update - table in use problem
>
> EEref Atak wrote:
>
> > From: "Martijn Tonies" m.tonies@...
> >
> >>To create a foreign key, there can be only 1 connection
> >>to a database.
> >
> >
> > sorry...
> > odd, complicate.
>
> But essential to maintain data integrity. If one connection changes a
> fundamental link, while the other is removing the validated data ....
>
> --
> Lester Caine
> -----------------------------
> L.S.Caine Electronic Services
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 4
> Date: Fri, 04 Feb 2005 10:52:42 -0000
> From: "dirknaudts" <dirk.naudts@...>
> Subject: REMOTE INTERFACE/gds__detach: Unsuccesful detach from database
>
>
> Hi,
>
> I've got a FB 1.5 Db running on a XP PRO machine (my dev pc)
> to which 5 clients are connected (2 running from my own pc, other
> running on other pc's).
>
> When I run my 'application-performance' test. All goes relatively
> well, up until a certain moment where all applications stop, giving
> the message unable to complete network request to host <myIp> Error
> writing data to the connection. Unknown Win32 Error 10054. (So
> disconnected TCP) same message is showing for the apps running on
> local machine.
>
> Firebird.log states :
>
> DN (Client) Fri Feb 04 11:03:19 2005
> REMOTE INTERFACE/gds__detach: Unsuccesful detach from
> database.
> Uncommitted work may have been lost
>
> DN (Client) Fri Feb 04 11:03:19 2005
> INET/inet_error: send errno = 10054
>
> Searching the net for the gds_detach message didn't return anything
> usefull.
> Restarting apps seems to solve problem until again a while later same
> behaviour reappears.
>
> Software is developed in D7 IBO43a and another application (also
> connected) in Kylix using DBExpress components
> (vendorlib=libfbclient.so, libraryname=libsqlib.so)
>
> Anybody any clue as to what's causing this behaviour ?
>
>
> Thanks for reading, best regards,
>
> Dirk Naudts.
>
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 5
> Date: Fri, 04 Feb 2005 11:00:11 -0000
> From: "kokok_kokok" <kokok_kokok@...>
> Subject: Union + order by 1 fails
>
>
>
> I have use FB 1.5.2
>
> the following sentence does not work properply and returns any order:
>
> select entrydate, id from customers where type=2
> union
> select entrydate, id from customers where type=3
> order by 2 asc, 2 asc
>
> I expect that the above sentence sort by Id column but it does not work.
> If I replace "order by 2 asc, 2 asc" by "order by 2 asc, 1 asc" or by
> "order by 2 asc", then it works fine. Also if it works if I do not use
> "union"
>
>
> I know that to type "2 asc" 2 times is stupid, but the sql sentence is
> created from a sql wizard. I have solved to cleaning the second "2
> asc" if it is the same, but in any case I think that Firebird should
> work fine in all cases.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 6
> Date: Fri, 04 Feb 2005 11:57:12 -0000
> From: "esrefatak" <liste@...>
> Subject: Re: Metadata update - table in use problem
>
>
>
> --- In firebird-support@yahoogroups.com, Lester Caine <lester@l...> wrote:
> > EEref Atak wrote:
> >
> > > From: "Martijn Tonies" m.tonies@u...
> > >
> > >>To create a foreign key, there can be only 1 connection
> > >>to a database.
> > >
> > >
> > > sorry...
> > > odd, complicate.
> >
> > But essential to maintain data integrity. If one connection changes a
> > fundamental link, while the other is removing the validated data ....
> >
>
> But, Firebird is RDBMS like PostgreSQL, Oracle etc.
> It should resolve.
>
> > --
> > Lester Caine
> > -----------------------------
> > L.S.Caine Electronic Services
>
> Regards,
> esrefatak
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 7
> Date: Fri, 04 Feb 2005 12:53:22 +0000
> From: Lester Caine <lester@...>
> Subject: Re: Re: Metadata update - table in use problem
>
> esrefatak wrote:
>
> >>But essential to maintain data integrity. If one connection changes a
> >>fundamental link, while the other is removing the validated data ....
> >
> > But, Firebird is RDBMS like PostgreSQL, Oracle etc.
> > It should resolve.
>
> As I understand it, PostgreSQL will allow changes that break the
> database and it's up to you to sort the mess out! Personally I do not go
> for making changes to the structure of a live database anyway, but
> ensuring that the database does not get screwed IF you change the
> structure seems like a good idea to me ?
>
> Preventing TWO people making structural changes at the same time is not
> easy to do, so restricting those changes to an engineering mode where
> there can only be one set of changes to be managed seems a better idea
> than allowing potentially conflicting activity - which in many cases can
> NOT be resolved by commit/rollback activity. As I understand it.
>
> --
> Lester Caine
> -----------------------------
> L.S.Caine Electronic Services
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 8
> Date: Fri, 4 Feb 2005 08:58:43 -0400
> From: Daniel Rail <daniel@...>
> Subject: Re: Support for Firebird on MONO for LINUX
>
> Hi,
>
> At February 4, 2005, 06:15, Adrian Wreyford wrote:
>
>
> > Does anybody know when we can expect such code. It appears someone is
> > working on it?
>
> The support is through the Firebird .Net data provider, which you can
> download from:
> http://www.firebirdsql.org/index.php?op=files&id=netprovider
>
> --
> Best regards,
> Daniel Rail
> Senior System Engineer
> ACCRA Group Inc. (www.accra.ca)
> ACCRA Med Software Inc. (www.filopto.com)
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 9
> Date: Fri, 4 Feb 2005 15:05:13 +0200
> From: "Adrian Wreyford" <wreymed@...>
> Subject: RE: Support for Firebird on MONO for LINUX
>
> Thanks Daniel
>
> -----Original Message-----
> From: Daniel Rail [mailto:daniel@...]
> Sent: 04 February 2005 02:59 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Support for Firebird on MONO for LINUX
>
>
> Hi,
>
> At February 4, 2005, 06:15, Adrian Wreyford wrote:
>
>
> > Does anybody know when we can expect such code. It appears someone is
> > working on it?
>
> The support is through the Firebird .Net data provider, which you can
> download from:
> http://www.firebirdsql.org/index.php?op=files&id=netprovider
>
> --
> Best regards,
> Daniel Rail
> Senior System Engineer
> ACCRA Group Inc. (www.accra.ca)
> ACCRA Med Software Inc. (www.filopto.com)
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 10
> Date: Fri, 04 Feb 2005 12:40:09 -0000
> From: "mfgsb" <mfgsb@...>
> Subject: FB on Slackware
>
>
>
> Hi,
> first of all, sorry for my poor english.
> In the past I installed FB 1.5 in RedHat and Debian with no
> problems, but in Slackware, after the installation, we can't connect
> to the server, after isql we write
>
> connect localhost:employee.fdb;
>
> and the message is
>
> Statement failed ( -902)
> Unable to complete request to localhost
>
> The installation add gds_db to /etc/services, but make no changes
> in /etc/inetd.conf
>
> If someone could help me...
> Thanks
>
>
>
>
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 11
> Date: Fri, 4 Feb 2005 07:28:57 -0700
> From: "Myles Wakeham" <myles@...>
> Subject: Re: FB on Slackware
>
> > On 04-Feb-2005 05:40:09, mfgsb wrote:
> > connect localhost:employee.fdb;
>
> As a hint, wouldn't you need to provide an absolute path to the FDB file
> here? Something more like:
>
> Connect localhost:/opt/firebird/employee.fdb
>
> Or whatever is appropriate to your installation?
>
> Regards,
> Myles
>
> ===============================
> Myles Wakeham
> Director of Engineering
> Tech Solutions US, Inc.
> (480) 451-7440
> www.techsol.org
>
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 12
> Date: Fri, 04 Feb 2005 14:46:09 -0000
> From: "mfgsb" <mfgsb@...>
> Subject: Re: FB on Slackware
>
>
> Thanks, but the error is the same ( the full path
> is /opt/firebird/examples/employee.fdb), cause employee.fdb is an
> alias for /opt/firebird/examples/employee.fdb.
>
> > > On 04-Feb-2005 05:40:09, mfgsb wrote:
> > > connect localhost:employee.fdb;
> >
> > As a hint, wouldn't you need to provide an absolute path to the FDB
> file
> > here? Something more like:
> >
> > Connect localhost:/opt/firebird/employee.fdb
> >
> > Or whatever is appropriate to your installation?
> >
> > Regards,
> > Myles
> >
> > ===============================
> > Myles Wakeham
> > Director of Engineering
> > Tech Solutions US, Inc.
> > (480) 451-7440
> > www.techsol.org
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 13
> Date: Fri, 4 Feb 2005 08:09:17 -0700
> From: "Myles Wakeham" <myles@...>
> Subject: Re: Re: FB on Slackware
>
> > On 04-Feb-2005 07:46:09, mfgsb wrote:
> > Thanks, but the error is the same ( the full path
> > is /opt/firebird/examples/employee.fdb), cause employee.fdb is an
> > alias for /opt/firebird/examples/employee.fdb.
>
> Sounds like you don't have the necessary entries in your hosts file to
> resolve 'localhost'. Have you tried this with 127.0.0.1:/opt/....
>
> To see if that works?
>
> Regards,
> Myles
>
> ===============================
> Myles Wakeham
> Director of Engineering
> Tech Solutions US, Inc.
> (480) 451-7440
> www.techsol.org
>
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 14
> Date: Fri, 04 Feb 2005 15:10:41 -0000
> From: "hcarvajalsy" <hcarvajalsy@...>
> Subject: Re: secondary server attachments cannot validate databases
>
>
> Thank you, Ann.
> I then followed the procedure to recover a corrupt database found at
> the Firebird site but the gbak crashed. Since I was able to connect
> to the database what I did was to extract the missing data and
> insert it into a backup database. Another process that worked was
> the DBack from Geoff Worboys using the Restore option.
> thanks again
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 15
> Date: Fri, 4 Feb 2005 12:16:50 -0300
> From: "Cesar L. Meloni" <cesar@...>
> Subject: Re: Re: FB on Slackware
>
> I'm run FB SS1.5.2 on Slack 9.1 and 10, very fine!!! but touch any manualy
> in instalation process...
>
> Version of firebird and Slack?
> FB Server is started?
> System user "firebird" have permision for employee.fdb?
>
> Good day!!!
>
> (Sorry my english!!!!)
>
>
>
> ----- Original Message -----
> From: "mfgsb" <mfgsb@...>
> To: <firebird-support@yahoogroups.com>
> Sent: Friday, February 04, 2005 11:46 AM
> Subject: [firebird-support] Re: FB on Slackware
>
>
> >
> >
> > Thanks, but the error is the same ( the full path
> > is /opt/firebird/examples/employee.fdb), cause employee.fdb is an
> > alias for /opt/firebird/examples/employee.fdb.
> >
> > > > On 04-Feb-2005 05:40:09, mfgsb wrote:
> > > > connect localhost:employee.fdb;
> > >
> > > As a hint, wouldn't you need to provide an absolute path to the FDB
> > file
> > > here? Something more like:
> > >
> > > Connect localhost:/opt/firebird/employee.fdb
> > >
> > > Or whatever is appropriate to your installation?
> > >
> > > Regards,
> > > Myles
> > >
> > > ===============================
> > > Myles Wakeham
> > > Director of Engineering
> > > Tech Solutions US, Inc.
> > > (480) 451-7440
> > > www.techsol.org
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 16
> Date: Fri, 04 Feb 2005 15:30:27 -0000
> From: "mfgsb" <mfgsb@...>
> Subject: Re: FB on Slackware
>
>
> C�sar,
> Slack is 10
> FB is SS-1.5.2.4731
> FB server is not running (that's the problem). If I start it
> manually the error is the same.
> user firebird has permission for employee.fdb
>
> I'm interested in the "touch any manualy..." if you remember what you
> did.
>
> (Si hablas espa�ol entonces nos manejaremos mejor seguramente)
> Muchas gracias !
>
> <cesar@i...> wrote:
> > I'm run FB SS1.5.2 on Slack 9.1 and 10, very fine!!! but touch any
> manualy
> > in instalation process...
> >
> > Version of firebird and Slack?
> > FB Server is started?
> > System user "firebird" have permision for employee.fdb?
> >
> > Good day!!!
> >
> > (Sorry my english!!!!)
> >
> >
> >
> > ----- Original Message -----
> > From: "mfgsb" <mfgsb@y...>
> > To: <firebird-support@yahoogroups.com>
> > Sent: Friday, February 04, 2005 11:46 AM
> > Subject: [firebird-support] Re: FB on Slackware
> >
> >
> > >
> > >
> > > Thanks, but the error is the same ( the full path
> > > is /opt/firebird/examples/employee.fdb), cause employee.fdb is an
> > > alias for /opt/firebird/examples/employee.fdb.
> > >
> > > > > On 04-Feb-2005 05:40:09, mfgsb wrote:
> > > > > connect localhost:employee.fdb;
> > > >
> > > > As a hint, wouldn't you need to provide an absolute path to the
> FDB
> > > file
> > > > here? Something more like:
> > > >
> > > > Connect localhost:/opt/firebird/employee.fdb
> > > >
> > > > Or whatever is appropriate to your installation?
> > > >
> > > > Regards,
> > > > Myles
> > > >
> > > > ===============================
> > > > Myles Wakeham
> > > > Director of Engineering
> > > > Tech Solutions US, Inc.
> > > > (480) 451-7440
> > > > www.techsol.org
> > >
> > >
> > >
> > >
> > >
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 17
> Date: Fri, 04 Feb 2005 11:02:48 -0500
> From: "Ann W. Harrison" <aharrison@...>
> Subject: Re: aggregate product() or equivalent
>
> unordained wrote:
> > Is there an aggregate function that multiplies all non-null values to
> provide a result?
>
> No...
> > If not, is
> > it possible to write aggregate functions as UDF's?
>
> Not really. UDF's work on a single set of inputs - they can't accept
> input record streams.
> > This would be
> > to use in a select with group-by, to find total ratios for several items at
> once, based on the
> > product of component ratios.
> >
>
> You're probably going to have to put the whole operation in a stored
> procedure.
>
> Regards,
>
>
> Ann
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 18
> Date: Fri, 04 Feb 2005 17:12:29 +0100
> From: developer <developer@...>
> Subject: Any available documentation on Plan Analyzer?
>
>
> Hi,
>
> Is there any available information on how the Firebird database
> works when chooses an access plan to return the result of a query? Are
> they diferent optimization modes?
>
> I would like to understand the plan analyzer and the performance
> analyzer to be able to improve my sql queries.
>
> Any help on that?
>
> Thanks in advance.
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 19
> Date: Fri, 04 Feb 2005 17:01:49 +0100
> From: developer <developer@...>
> Subject: How can I see if there are invalid objects in my db?
>
>
> Hi,
>
> Can any one tell me, how can I see if a view, trigger or stored
> procedure has become invalid, that is to say, when it must be recompiled
> o recreated.
>
> Is there any field in database catalog (RDB$ tables) which tells me
> this? Any script or tool which can help with this. Even more, how can I
> make this automatically after modifying database objects.
>
> We are working with Firebird 1.0.3 and Delphi 6 and sometimes our
> application doesn't work till we recompile a certain stored procedure.
>
> Any help about this?
>
> Thanks in advance.
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 20
> Date: Fri, 04 Feb 2005 16:30:47 -0000
> From: "cecil1_5" <acecil@...>
> Subject: Unavailable Database
>
>
>
> I am a new and trying-to-be user of Firebird. (and an old
> experienced Sql server DBA/user).
>
> I downloaded the zip file onto a nice clear machine running windows
> server 2003. I printed out the tons of release notes, read all the
> readmes, and ran the install_super (because I didn't realize the
> fancy windows installer was in a separate zip). then I migrated to
> the directory where the security fdb was, ran isql.exe, added my
> user, etc. opened a private db, and so on. Everything worked fine.
>
> Next day, the sys admin brought down the machine, unplugged it, and
> moved it into the official, air-conditioned server room. The
> Firebird service would no longer start. I uninstalled, reran the
> install -- appeared to work fine, but any connect gets error -904
> unavailable database.
>
> I read all the messages on the support line, and tried what the other
> guy did: I have now installed with the fancy installer, and then
> uninstalled, installed from the zip again, uninstalled, deleted and
> removed everything, tried again -- each time the service comes up,
> the registry is fine, etc, but: unavailable database.
>
> Clearly it is not corruption, because I wiped out and redownloaded
> the security fdb. Would something in a network card cause this?
>
> I am stumped.
>
> Ann Cecil
> InterScope Technologies
>
>
>
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 21
> Date: Fri, 04 Feb 2005 16:57:02 -0000
> From: "perevilas" <pvilas@...>
> Subject: Amazing news about open-source DB
>
>
> Hello,
>
> This seems to be interesing news in the open source database world
>
> http://news.com.com/Sun+floats+open-source+database+idea/2100-7344_3-
> 5562799.html?tag=st.num
>
> Could FB be the core for this project?.
>
> Regards,
> Pere Vilas.
> http://www.besinformatica.es
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 22
> Date: Fri, 04 Feb 2005 12:00:19 -0500
> From: "Ann W. Harrison" <aharrison@...>
> Subject: Re: IB 6.0 Client & FB Server Compatability
>
> Norman McFarlane wrote:
> >
> > I've just installed the IB 6.0 Client on my new HDD (had a crash two weeks
> > ago and lost the lot). I have Firebird 1.5 ... when I try to retrieve a
> list fo
> > tables, or pros, or any other metadata from the DB (using IBConsole) I get
> > the following error message:
> >
> > Dynamic SQL Error
> > SQL error code = -901
> > feature is not supported
> >
> > Is this due to an incompatibility between the IB6.0 client and the Firebird
> > Server, or am I missing something her?
>
> As Helen pointed out, Firebird is not InterBase and vice-versa. That
> problem is going to get worse over time. However, InterBase 6.0 and
> Firebird 1.5 are pretty close. I suspect the culprit is IBConsole.
> What version are you using?
>
> Regards
>
>
> Ann
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 23
> Date: Fri, 4 Feb 2005 18:31:45 +0100
> From: "Martijn Tonies" <m.tonies@...>
> Subject: Re: How can I see if there are invalid objects in my db?
>
> Hi,
> >
> > Can any one tell me, how can I see if a view, trigger or stored
> > procedure has become invalid, that is to say, when it must be recompiled
> > o recreated.
>
> Can't be. Firebird doesn't have the concept of "invalid objects".
>
> > Is there any field in database catalog (RDB$ tables) which tells me
> > this? Any script or tool which can help with this. Even more, how can I
> > make this automatically after modifying database objects.
> >
> > We are working with Firebird 1.0.3 and Delphi 6 and sometimes our
> > application doesn't work till we recompile a certain stored procedure.
> >
> > Any help about this?
>
> Well, give us more info :-)
>
> With regards,
>
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 24
> Date: Fri, 4 Feb 2005 14:47:20 -0300
> From: "Cesar L. Meloni" <cesar@...>
> Subject: Re: Re: FB on Slackware
>
> Ok: In english (I need traning...)
>
> In instalation step this is output:
>
> Firebird super 1.5.2.4731-0.i686 Installation
>
> Press Enter to start installation or ^C to abort
> Extracting install data
> cp: missing destination file
> Try `cp --help' for more information.
> chown: too few arguments
> Try `chown --help' for more information.
> chmod: too few arguments
> Try `chmod --help' for more information.
>
> Please enter new password for SYSDBA user: masterkey
>
> Install completed
> -------------------------------------------------
>
> And I "touch" manualy:
>
> #cp /opt/firebird/misc/firebird.init.d.generic
> /etc/rc.d/rc.firebird
> #chown root:root /etc/rc.d/rc.firebird
> #chmod ug=rx,o /etc/rc.d/rc.firebird
> #usermod -s /bin/sh firebird
> #/etc/rc.d/rc.firebird start
> #cd /opt/firebird/bin
> #./isql
> Use CONNECT or CREATE DATABASE to specify a database
> SQL> connect /opt/firebird/examples/employee.fdb
> CON> ;
> Database: /opt/firebird/examples/employee.fdb
> SQL> show tables;
> COUNTRY
> CUSTOMER
> DEPARTMENT
> EMPLOYEE
> EMPLOYEE_PROJECT JOB
> PHONE_LIST
> PROJECT
> PROJ_DEPT_BUDGET
> SALARY_HISTORY
> SALES
> SQL>
> ---------------------------------------------------
>
> A SYSDBA password is "masterkey"!!!
> If you change it, remember change /etc/rc.d/rc.firebird!!!
>
> Suerte!!!!
>
>
>
> ----- Original Message -----
> From: "mfgsb" <mfgsb@...>
> To: <firebird-support@yahoogroups.com>
> Sent: Friday, February 04, 2005 12:30 PM
> Subject: [firebird-support] Re: FB on Slackware
>
>
>
>
> C�sar,
> Slack is 10
> FB is SS-1.5.2.4731
> FB server is not running (that's the problem). If I start it
> manually the error is the same.
> user firebird has permission for employee.fdb
>
> I'm interested in the "touch any manualy..." if you remember what you
> did.
>
> (Si hablas espa�ol entonces nos manejaremos mejor seguramente)
> Muchas gracias !
>
> <cesar@i...> wrote:
> > I'm run FB SS1.5.2 on Slack 9.1 and 10, very fine!!! but touch any
> manualy
> > in instalation process...
> >
> > Version of firebird and Slack?
> > FB Server is started?
> > System user "firebird" have permision for employee.fdb?
> >
> > Good day!!!
> >
> > (Sorry my english!!!!)
> >
> >
> >
> > ----- Original Message -----
> > From: "mfgsb" <mfgsb@y...>
> > To: <firebird-support@yahoogroups.com>
> > Sent: Friday, February 04, 2005 11:46 AM
> > Subject: [firebird-support] Re: FB on Slackware
> >
> >
> > >
> > >
> > > Thanks, but the error is the same ( the full path
> > > is /opt/firebird/examples/employee.fdb), cause employee.fdb is an
> > > alias for /opt/firebird/examples/employee.fdb.
> > >
> > > > > On 04-Feb-2005 05:40:09, mfgsb wrote:
> > > > > connect localhost:employee.fdb;
> > > >
> > > > As a hint, wouldn't you need to provide an absolute path to the
> FDB
> > > file
> > > > here? Something more like:
> > > >
> > > > Connect localhost:/opt/firebird/employee.fdb
> > > >
> > > > Or whatever is appropriate to your installation?
> > > >
> > > > Regards,
> > > > Myles
> > > >
> > > > ===============================
> > > > Myles Wakeham
> > > > Director of Engineering
> > > > Tech Solutions US, Inc.
> > > > (480) 451-7440
> > > > www.techsol.org
> > >
> > >
> > >
> > >
> > >
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
> Message: 25
> Date: Fri, 04 Feb 2005 17:54:26 -0000
> From: "flipmooooo" <flipmooooo@...>
> Subject: Stored Procedures
>
>
> Hi,
>
> I'm a new in using stored procedures in firebird. I'm trying to catch
> 'Exceptions' in a stored procedure in such way it will skip rows for
> which
> the exception occured but will continue to loop the remaining rows.
> The body
> of the procedure consists of 2 updates on 2 different tables. I have
> 3 rows
> in table 1 and 3 rows in table 2. When fi row 2 from table 2 is in a
> state
> that it has been updated but not committed a deadlock will occur when
> the
> procedure is executed by some other user. After executing the
> procedure I
> get 3 updated rows in table 1 and 2 updated rows in table 2, so I have
> inconsistent data in my db. I thought that everything within the
> begin..end
> block would be 'undone' when an exception occured. What i want to
> achieve is
> to loop each row in table 1, do the updates, if all updates succeeded,
> continue looping. When some exception occured within the for select
> loop,
> ALL changes have to be 'undone' and continue looping.
>
> Greetings,
> Filip Moons
>
> CREATE PROCEDURE SET_STATUS (
> RETURNERRORS SMALLINT)
> RETURNS (
> ERRORCODE VARCHAR(20),
> ERRORVALUE INTEGER,
> ERRORTEXT VARCHAR(80))
> AS
> DECLARE VARIABLE AID INTEGER;
> DECLARE VARIABLE ACODE VARCHAR(8);
> DECLARE VARIABLE ABEDRAG NUMERIC(15,2);
> DECLARE VARIABLE ASTATUS SMALLINT;
> begin
> FOR
> SELECT TSTID,TSTCODE,TSTBEDRAG,TSTSTATUS
> FROM TEST
> ORDER BY TSTID
> INTO AID,ACODE,ABEDRAG,ASTATUS
> DO
> BEGIN
> UPDATE TEST
> SET
> TSTSTATUS = 1
> WHERE
> TSTID = :AID;
>
> UPDATE TEST_CODE
> SET
> TCBEDRAG = TCBEDRAG + :ABEDRAG
> WHERE
> TCCODE = :ACODE;
>
> WHEN ANY DO
> BEGIN
> if (RETURNERRORS <> 0) then
> BEGIN
> ERRORCODE = 'SKIPPED';
> ErrorValue = GDSCODE;
> ErrorText = CAST(AID as VARCHAR(80));
> SUSPEND;
> END
> END
> END
> end
>
>
>
>
>
>
> ________________________________________________________________________
> ________________________________________________________________________
>
>
>
> ------------------------------------------------------------------------
> Yahoo! Groups Links
>
>
>
>
> ------------------------------------------------------------------------
>
>
>
>
>