RE: [firebird-support] Subqueries from FB1.5 don't work in 2.0 or 2.1

[Svein Erling Tysvær]

Hi Paul!

> I'm porting an application from FB1.5 to 2.x and a query that worked on 1.5
> doesn't work on 2.0 or 2.1 (I haven't tried 2.5)
>
> The query is:
>
> SELECT u.Id, u.Name, u.Email FROM Punter u
> WHERE u.ALIVE = 1
>  /* and they haven't blacklisted this issue */
>  AND u.Id not IN (SELECT bl.UserId FROM IssueBlacklist bl 
>   WHERE bl.IssueId = 105)
>
>  /* and subsribed via the product */
>  AND  u.id in (SELECT ps.UserId FROM ProductSubscription ps
>JOIN IssueProduct ip ON (ps.ProductId = ip.ProductId)
>WHERE ip.IssueId = 105 )
>
> With my test data I'm expecting 1 result, however I always get an empty
> result.
>
> If I replace the first subquery with a list of id's say 21,22,23 the query
> behaves as expected.
> If I remove the join in the second subquery the query behaves as expected.
>
> The query always behaves as I'd expect on FB 1.5
>
> I'm comparing Firebird 1.5.6 and 2.0.6
> I'm executing the query in FlameRobin, running on WinXP, and for ease of
> the testing I'm running Firbird as an application.
> 
> I can add a sql script to create a test database if anyone is interested.

I would have written your query slightly differently with Fb 2, probably 
similar to (uncertain whether I would use DISTINCT in the CTE or use EXISTS 
rather than JOIN to TMP):

WITH TMP AS
(SELECT DISTINCT ps.UserId 
   FROM ProductSubscription ps
   JOIN IssueProduct ip ON ps.ProductId = ip.ProductId
   LEFT JOIN IssueBlacklist bl on ps.UserId = bl.UserId
  and ip.IssueId = bl.IssueId
 WHERE ip.IssueId = 105
   AND bl.UserId IS NULL)

SELECT u.Id, u.Name, u.Email 
  FROM Punter u 
  JOIN TMP t ON u.Id = t.UserId
WHERE u.ALIVE = 1

However, I cannot see why the query you use should produce different results on 
Fb 1.5 and newer versions, particularly not when you say that it works if you 
remove JOIN IssueProduct. So, although I doubt I could shed any more light to 
your problem, it would be interesting to see your SQL script.

Set

[firebird-support] Modified date of Tables or Stored Procedure.

[Mahesh Pratihari]

Hi All,

 

How to Identify the Modified and Created Date of Tables or Stored
Procedure in Firebird.

 

Expecting early reply on this.

 

Thanks,

Mahesh Pratihari

Sonata Software Limited 

Phone   : +91 80 3097 1570

Mobile  : +91 99808 37446

www.sonata-software.com

 

Please don't print this email unless you really need to. This will
preserve trees on our planet.

 



[Non-text portions of this message have been removed]

Re: [firebird-support] Modified date of Tables or Stored Procedure.

[Dmitry Kuzmenko]

Hello, Mahesh!

Monday, October 31, 2011, 12:02:24 PM, you wrote:

MP> How to Identify the Modified and Created Date of Tables or Stored
MP> Procedure in Firebird.

this information is not stored in db anywhere.

MP> Expecting early reply on this.

you could look by yourself into rdb$procedures, rdb$triggers,
rdb$relations, etc. no timestamp fields at all.

Some tools for developers allows to store that information
in their tables, and, moreover, store "version info" and other
log data allowing to track who and when created/modified DB objects.

Check functionality of tool that you use. If you work just with isql,
decide to use such tool I've mentioned.

-- 
Dmitry Kuzmenko, www.ib-aid.com

Re: [firebird-support] Programatically detect shutdown state of database? IBOAdmin?

[Ismael L. Donis Garcia]

SELECT *
FROM MON$ATTACHMENTS c WHERE c.MON$ATTACHMENT_ID <> CURRENT_CONNECTION

if row_count > 0 exists other user connect

Best Regards
=
|| ISMAEL ||
=
  - Original Message - 
  From: jacobhavkrog 
  To: firebird-support@yahoogroups.com 
  Sent: Wednesday, October 19, 2011 11:16 AM
  Subject: [firebird-support] Programatically detect shutdown state of 
database? IBOAdmin?



  Hi - 

  How can I from my Delphi application detect the shutdown state (normal/multi 
user maintenance/single user maintenance/full) of a database?

  Like what the gstat command line tool can tell me.

  I need it for a remote database too.

  IBOAdmin can be used to do shut down a database and bring it back online, but 
can it also tell me the current state? 

  I want to do a programmatic restore, so I want to shut down the database 
before using gbak. But I cant find a way to make sure that a shut down has 
completed sucessfully..

  Thanks
  Jacob



  

[Non-text portions of this message have been removed]

[firebird-support] Core-3549?

[kasparovimnot]

We had a client running fb 2.51 on CentoOS 5.6 over the weekend end up 
corrupted and we had to restore from backup.  No power loss to the server or 
anything, but noticed it when starting firebird server after our backup 
routine.  Saw the following:

usr/lib/firebird/bin/gfix -user sysdba -pass XXX /var/opt/thgg/data/db.gdb 
-v -f 
database file appears corrupt (/var/opt/thgg/data/thggERROR.gdb) 
-wrong page type 
-page 2675062 is of wrong type (expected 3, found 7) 

I did some checking and saw this:
http://tracker.firebirdsql.org/browse/CORE-3549

It looks to be the same error, but it says issue is resolved and fix version is 
same as reported version?  Not sure what this means?

Re: [firebird-support] Importing with a Date type column

[Milan Babuskov]

Tupy... nambá wrote:
> I´m experiencing problems trying to import a date column to a Firebird db.
> I used many possibilities, but with none of them I got to import this column.

Import from what? Text file, CSV file, XML file, sql file?

What does your input look like?

> I also tried FBExport but the orientations were not enough to resolve.

FBExport is not really good for importing, it's meant to export data. To 
import data you should try XMLWizard instead:

http://www.guacosoft.com/xmlwizard

> Does someone also have experienced this problem and got a solution ?
>  
> I got a message wich says "conversion error from string " (#-413)

Is this a real error message? Looks like you are trying to convert 
"" which is not really a date.

 From what you wrote, it seems like you are trying to run a batch of SQL 
statements. In that case, make sure date format is one that Firebird 
supports:

http://www.firebirdfaq.org/faq137

HTH

-- 
Milan Babuskov

==
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==

Re: [firebird-support] Is faster hardisk really a matter for Firebird performance?

[Milan Babuskov]

trskopo wrote:
> I just doing a little experiment to find out database performance on RAM vs 
> on Disk.

Which operating system do you use?

-- 
Milan Babuskov

==
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==

Re: [firebird-support] How can you change the transaction level to READ_ONLY in SQL

[Milan Babuskov]

dsaunders1971 wrote:
> I have tried this sort of syntax but just get a dynamic sql error
> 
> SET TRANSACTION READ ONLY;
> select * from "Booking"

AFAIK, this is not available in DSQL.

> What's the full and proper format for this?

It depends on the library you use to access Firebird?

For example, in PHP it would be a parameter in ibase_prepare() function, 
and in C++ using IBPP library, it would be a parameter in 
TransactionFactory constructor.

What programming language / data access layer are you using?

-- 
Milan Babuskov

==
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==

[firebird-support] Help with trigger syntax

[grip_2ls]

Once again I am struggling with the syntax of a trigger. When I run the
following I get the error:
Error at line 3: Dynamic SQL ErrorSQL error code = -104Token unknown -
line 19, column 25;
There isn't even a semi-colon on line 19!
Any advice gratefully received.
Neil
SET TERM ^ ;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER TRGAIBOOKING_STAGE_HISTORY FOR
BOOKING_STAGE_HISTORYACTIVE AFTER INSERT POSITION 0AS
  DECLARE EMAIL VARCHAR(255); DECLARE DESCRIPTION VARCHAR(50);
BEGIN SELECT BST.EMAIL, BST.DESCRIPTION FROM BOOKING_STAGE BST WHERE
BST.ID = NEW.STAGEID INTO :email, :description;
  SELECT SendMail('ale...@2ls.com', :email,
'Change of status for booking '||NEW.BOOKINGID,
'Just to let you know that booking '||NEW.BOOKINGID||' has now reached
stage '||:description, 'auth.smtp.1and1.co.uk',
'25', 'b...@2ls.com',
'8Burgess')   FROM rdb$database;
END ^
COMMIT WORK ^SET TERM ;^


[Non-text portions of this message have been removed]

Re: [firebird-support] Help with trigger syntax

[Mark Rotteveel]

On Mon, 31 Oct 2011 15:19:30 -, "grip_2ls"  wrote:
> Once again I am struggling with the syntax of a trigger. When I run the
> following I get the error:
> Error at line 3: Dynamic SQL ErrorSQL error code = -104Token unknown -
> line 19, column 25;
> There isn't even a semi-colon on line 19!
> Any advice gratefully received.
> Neil
> SET TERM ^ ;
> /* Triggers only will work for SQL triggers */
> CREATE TRIGGER TRGAIBOOKING_STAGE_HISTORY FOR
> BOOKING_STAGE_HISTORYACTIVE AFTER INSERT POSITION 0AS
>   DECLARE EMAIL VARCHAR(255); DECLARE DESCRIPTION VARCHAR(50);
> BEGIN SELECT BST.EMAIL, BST.DESCRIPTION FROM BOOKING_STAGE BST WHERE
> BST.ID = NEW.STAGEID INTO :email, :description;
>   SELECT SendMail('ale...@2ls.com', :email,
> 'Change of status for booking '||NEW.BOOKINGID,
> 'Just to let you know that booking '||NEW.BOOKINGID||' has now reached
> stage '||:description, 'auth.smtp.1and1.co.uk',
> '25', 'b...@2ls.com',
> '8Burgess')   FROM rdb$database;
> END ^
> COMMIT WORK ^SET TERM ;^

Could you post it with a better formatted version of both the error
message and the query, and indicate what is line 19, column 25?

BTW: It looks like you posted the username and password for the
mailaccount that can be used for bulk-emailing at your ISP. If that is the
case you might want to change the password of account b...@21s.com before
it gets abused.

[firebird-support] nbackup failure - cause?

[Nick Upson]

Hi,

I have recently started using nbackup and have a system where, at the
end of an attempt to do a level 0 I got

Fatal lock manager error: invalid lock id (158048), errno: 22
--Invalid argument

attempts to do subsequent level 1 backups produced:


unsuccessful metadata update
-Database is already in the physical backup mode
Failure: Database error
[
PROBLEM ON "begin backup".
SQLCODE:-607
]

I've never seen anything like this in my earlier testing, can anyone
tell me what happened (firebird.log has nothing at these times)

[firebird-support] Re: Help with trigger syntax

[grip_2ls]

Mark

For some reason I cannot get the pre/code html tags to work so I have put the 
code here:

http://pastebin.com/p3kt7bud

Line 19/25 is the second character of the smtp server.

Thanks for pointing out my lack of security - I will change that now.

Thanks

N
--- In firebird-support@yahoogroups.com, Mark Rotteveel  wrote:
>
> On Mon, 31 Oct 2011 15:19:30 -, "grip_2ls"  wrote:
> > Once again I am struggling with the syntax of a trigger. When I run the
> > following I get the error:
> > Error at line 3: Dynamic SQL ErrorSQL error code = -104Token unknown -
> > line 19, column 25;
> > There isn't even a semi-colon on line 19!
> > Any advice gratefully received.
> > Neil
> > SET TERM ^ ;
> > /* Triggers only will work for SQL triggers */
> > CREATE TRIGGER TRGAIBOOKING_STAGE_HISTORY FOR
> > BOOKING_STAGE_HISTORYACTIVE AFTER INSERT POSITION 0AS
> >   DECLARE EMAIL VARCHAR(255); DECLARE DESCRIPTION VARCHAR(50);
> > BEGIN SELECT BST.EMAIL, BST.DESCRIPTION FROM BOOKING_STAGE BST WHERE
> > BST.ID = NEW.STAGEID INTO :email, :description;
> >   SELECT SendMail('alerts@...', :email,
> > 'Change of status for booking '||NEW.BOOKINGID,
> > 'Just to let you know that booking '||NEW.BOOKINGID||' has now reached
> > stage '||:description, 'auth.smtp.1and1.co.uk',
> > '25', 'bulk@...',
> > '8Burgess')   FROM rdb$database;
> > END ^
> > COMMIT WORK ^SET TERM ;^
> 
> Could you post it with a better formatted version of both the error
> message and the query, and indicate what is line 19, column 25?
> 
> BTW: It looks like you posted the username and password for the
> mailaccount that can be used for bulk-emailing at your ISP. If that is the
> case you might want to change the password of account bulk@... before
> it gets abused.
>

Re: [firebird-support] Re: Help with trigger syntax

[Mark Rotteveel]

On Mon, 31 Oct 2011 15:57:27 -, "grip_2ls"  wrote:
> Mark
> 
> For some reason I cannot get the pre/code html tags to work so I have
put
> the code here:
> 
> http://pastebin.com/p3kt7bud

I am a bit rusty in my PSQL and trigger code, but as far as I know you can
do SELECT without INTO like you are doing when calling that SendEmail
function (or procedure?).

You probably just need to get rid of the SELECT  FROM RDB$DATABASE
stuff around the SendEmail call, but maybe someone with more knowledge of
PSQL knows more.

> 
> Line 19/25 is the second character of the smtp server.

Looks off to me ;)

Re: [firebird-support] Re: Help with trigger syntax

[Mark Rotteveel]

On Mon, 31 Oct 2011 17:06:34 +0100, Mark Rotteveel 
wrote:
> I am a bit rusty in my PSQL and trigger code, but as far as I know you
can
> do SELECT without INTO like you are doing when calling that SendEmail
> function (or procedure?).

Uhm, that should have been '.. you can't do SELECT without INTO'. I really
need to learn to proofread before clicking send.

Mark

[firebird-support] Re: Help with trigger syntax

[grip_2ls]

Hi Mark

Thanks for the advise but I don't think that is the issue. If you remove the 
second select statement it compiles and runs fine.

N

--- In firebird-support@yahoogroups.com, Mark Rotteveel  wrote:
>
> On Mon, 31 Oct 2011 17:06:34 +0100, Mark Rotteveel 
> wrote:
> > I am a bit rusty in my PSQL and trigger code, but as far as I know you
> can
> > do SELECT without INTO like you are doing when calling that SendEmail
> > function (or procedure?).
> 
> Uhm, that should have been '.. you can't do SELECT without INTO'. I really
> need to learn to proofread before clicking send.
> 
> Mark
>

RE: [firebird-support] Re: Help with trigger syntax - Email found in subject

[Leyne, Sean]

> Thanks for the advise but I don't think that is the issue. If you remove the
> second select statement it compiles and runs fine.

Actually, that is the issue.  

It is the 2nd SELECT which is the problem, so by removing it, you removed the 
problem.

To invoke the SendMail UDF, you should define a dummy variable, and then either:

  SELECT SendMail(...) FROM RDB$Database INTO dummyvar;

OR

  dummyvar = SendMail( ...);


Sean

Re: [firebird-support] nbackup failure - cause?

[Alexey Kovyazin]

Hello Nick,

It seems like you have database corruption.
What is Firebird version?

Regards,
Alexey Kovyazin
IBSurgeon (www.ib-aid.com)


> Hi,
>
> I have recently started using nbackup and have a system where, at the
> end of an attempt to do a level 0 I got
>
> Fatal lock manager error: invalid lock id (158048), errno: 22
> --Invalid argument
>
> attempts to do subsequent level 1 backups produced:
>
> unsuccessful metadata update
> -Database is already in the physical backup mode
> Failure: Database error
> [
> PROBLEM ON "begin backup".
> SQLCODE:-607
> ]
>
> I've never seen anything like this in my earlier testing, can anyone
> tell me what happened (firebird.log has nothing at these times)
>
> 



[Non-text portions of this message have been removed]

[firebird-support] FB 2.0.5 & forced writes

[sboydlns]

I have been having database corruption problems with a Firebird 2.0.5 database 
on Windows 2008 32-bit.  I checked the forced writes setting and forced writes 
were disabled.  I says to myself I says, AHA this is the problem.  So I use 
gfix to enabled forced writes.  I check that there were enabled using gstat.  
Everything looks good.  If I wait a couple of seconds and check using gstat 
again, forced writes are once more disabled.

That the heck is going on?  We certainly don't have any processes that are 
disabling forced writes.

[firebird-support] Re: FB 2.0.5 & forced writes

[kasparovimnot]

Did you cycle the server after setting forced writes?  I'm pretty sure that's 
required.

--- In firebird-support@yahoogroups.com, "sboydlns"  wrote:
>
> I have been having database corruption problems with a Firebird 2.0.5 
> database on Windows 2008 32-bit.  I checked the forced writes setting and 
> forced writes were disabled.  I says to myself I says, AHA this is the 
> problem.  So I use gfix to enabled forced writes.  I check that there were 
> enabled using gstat.  Everything looks good.  If I wait a couple of seconds 
> and check using gstat again, forced writes are once more disabled.
> 
> That the heck is going on?  We certainly don't have any processes that are 
> disabling forced writes.
>

Re: [firebird-support] FB 2.0.5 & forced writes

[Thomas Steinmaurer]

> I have been having database corruption problems with a Firebird 2.0.5 
> database on Windows 2008 32-bit.  I checked the forced writes setting and 
> forced writes were disabled.  I says to myself I says, AHA this is the 
> problem.  So I use gfix to enabled forced writes.  I check that there were 
> enabled using gstat.  Everything looks good.  If I wait a couple of seconds 
> and check using gstat again, forced writes are once more disabled.
>
> That the heck is going on?  We certainly don't have any processes that are 
> disabling forced writes.

What access layer do you use? For example, IBObjects has a forced writes 
property at TIB_Connection level, if not set to "default", it allows you 
to set forced writes on/off.


-- 
With regards,
Thomas Steinmaurer

* Firebird Foundation Committee Member
http://www.firebirdsql.org/en/firebird-foundation/

* Upscene Productions - Database Tools for Developers
http://www.upscene.com/

* My Blog
http://blog.upscene.com/thomas/index.php

Re: [firebird-support] Re: Help with trigger syntax

[Mark Rotteveel]

On 31-10-2011 17:46, grip_2ls wrote:
> Hi Mark
>
> Thanks for the advise but I don't think that is the issue. If you remove the 
> second select statement it compiles and runs fine.

That was exactly the issue I was pointing out. You are using SELECT 
without INTO to call SendMail. That doesn't work as Sean also pointed out.

Mark
-- 
Mark Rotteveel

[firebird-support] Re: FB 2.0.5 & forced writes

[sboydlns]

--- In firebird-support@yahoogroups.com, Thomas Steinmaurer  wrote:
>
> 
> What access layer do you use? For example, IBObjects has a forced writes 
> property at TIB_Connection level, if not set to "default", it allows you 
> to set forced writes on/off.
> 

THANK YOU!  I had forgotten about that.

I don't know how that property keeps getting set in my projects because AFAIK 
I'm not doing it deliberately. I have had to go as far as clearing the Params 
property and setting all the proper values in code to make sure that no design 
time garbage gets carried over.

Thanks again.

RE: [firebird-support] Subqueries from FB1.5 don't work in 2.0 or 2.1

[Paul Faid]

The following script works on FB1.5 but not FB 2.0+

I'm not sure if I can attach a file so I'll also embed the sql script in
the email. I run the script using isql. The script creates the database so
the ods should be correct for each ver of FB.

cheers
Paul

/**Script Start ***/
/*
 Stripped out  script to test subqueries in select stmt for FB 2.0
 This works as expected on FB1.5, but not on 2.0
 */

CONNECT './testsq.fdb' USER 'sysdba' PASSWORD 'masterkey';

DROP DATABASE;

CREATE DATABASE './testsq.fdb' USER 'sysdba' PASSWORD 'masterkey';

SET SQL DIALECT 3;


CREATE TABLE Punter(
  Id INTEGER NOT NULL PRIMARY KEY,
  Name VARCHAR(80) NOT NULL CONSTRAINT Pu_Name_Unique UNIQUE,
  Email VARCHAR(80) NOT NULL CONSTRAINT Email_Unique UNIQUE,
  Alive SMALLINT DEFAULT 1
);


CREATE TABLE Issue(
  Id INTEGER NOT NULL PRIMARY KEY,
  Summary VARCHAR(256)
);

CREATE TABLE Product(
  Id INTEGER NOT NULL PRIMARY KEY,
  Name VARCHAR(20)
);

CREATE TABLE IssueProduct(
  IssueId INTEGER NOT NULL CONSTRAINT IP_IssueId REFERENCES Issue(Id)
ON UPDATE CASCADE
ON DELETE CASCADE,
  ProductId INTEGER NOT NULL CONSTRAINT IP_ProductId REFERENCES Product(Id)
ON UPDATE CASCADE
ON DELETE NO ACTION,
  PRIMARY KEY (IssueId, ProductId)
);

CREATE TABLE ProductSubscription(
ProductId INTEGER NOT NULL CONSTRAINT PIL_ProductId REFERENCES
Product(Id)
ON UPDATE CASCADE
ON DELETE CASCADE,
UserId INTEGER NOT NULL CONSTRAINT PIL_UserId REFERENCES Punter(Id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (ProductId, UserId )
);



CREATE TABLE ISSUEBLACKLIST(
  ISSUEID Integer NOT NULL CONSTRAINT IBL_ISSUEID REFERENCES ISSUE (ID) ON
UPDATE CASCADE ON DELETE CASCADE,
  USERID Integer NOT NULL CONSTRAINT IBL_USERID REFERENCES PUNTER (ID) ON
UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY (ISSUEID,USERID)
);

COMMIT;

/* Add the test records */

INSERT INTO  Product( Id,  Name)   VALUES ( 2,  'testProduct' );
INSERT INTO  Punter( Id,  Name, Email ) VALUES ( 3, 'BamBam','
bamb...@yahoo.co.nz');
INSERT INTO  ProductSubscription( ProductId, UserId) VALUES ( 2, 3);

INSERT INTO  Issue( Id, Summary) VALUES (105, 'testIssue');

INSERT INTO  IssueProduct( IssueId, ProductId) VALUES ( 105, 2);

Commit;

/* And now the offending query */

SHELL ECHO This *should* display a single result (and does on FB1.5):;

SELECT u.Id, u.Name, u.Email FROM Punter u
WHERE u.ALIVE = 1
  /* they haven't blacklisted this issue */
  AND u.Id not IN (SELECT bl.UserId FROM IssueBlacklist bl WHERE bl.IssueId
= 105)

  /* Subsribed via the product */
  AND  u.id in (SELECT ps.UserId FROM ProductSubscription ps
  JOIN IssueProduct ip ON (ps.ProductId
= ip.ProductId)
 WHERE ip.IssueId = 105 ) ;


/* By commenting out one of the subqueries it now works on both 1.5 and 2.0
*/

SHELL ECHO This will display a single result:;

SELECT u.Id, u.Name, u.Email FROM Punter u
WHERE u.ALIVE = 1
  /* they haven't blacklisted this issue */
  /*AND u.Id not IN (SELECT bl.UserId FROM IssueBlacklist bl WHERE
bl.IssueId = 105)  */

  /* Subsribed via the product */
  AND  u.id in (SELECT ps.UserId FROM ProductSubscription ps
  JOIN IssueProduct ip ON (ps.ProductId
= ip.ProductId)
 WHERE ip.IssueId = 105 ) ;

SHELL ECHO END.;

/**Script End***/


[Non-text portions of this message have been removed]

Re: [firebird-support] Re: Help with trigger syntax

[Helen Borrie]

At 07:56 AM 1/11/2011, you wrote:
>On 31-10-2011 17:46, grip_2ls wrote:
>> Hi Mark
>>
>> Thanks for the advise but I don't think that is the issue. If you remove the 
>> second select statement it compiles and runs fine.
>
>That was exactly the issue I was pointing out. You are using SELECT 
>without INTO to call SendMail. That doesn't work as Sean also pointed out.

Furthermore, invoking that Sendmail procedure as a select procedure is not 
sensible in this environment or any other.  Make it an executable procedure 
and, in the trigger code, call it with EXECUTE PROCEDURE (:var1, :var2, etc.)  
Selectable procedures are intended to return sets and shouldn't be used to 
execute DML.

./heLen

RE: [firebird-support] Subqueries from FB1.5 don't work in 2.0 or 2.1

[Leyne, Sean]

Paul,

> /* And now the offending query */
> 
> SHELL ECHO This *should* display a single result (and does on FB1.5):;
> 
> SELECT u.Id, u.Name, u.Email FROM Punter u WHERE u.ALIVE = 1
>   /* they haven't blacklisted this issue */
>   AND u.Id not IN (SELECT bl.UserId FROM IssueBlacklist bl WHERE bl.IssueId =
> 105)
> 
>   /* Subsribed via the product */
>   AND  u.id in (SELECT ps.UserId FROM ProductSubscription ps
>   JOIN IssueProduct ip ON (ps.ProductId = 
> ip.ProductId)
>  WHERE ip.IssueId = 105 ) ;

Does this query work?

SELECT u.Id, u.Name, u.Email FROM Punter u WHERE u.ALIVE = 1
  /* they haven't blacklisted this issue */
  AND u.Id not IN (SELECT bl.UserId FROM IssueBlacklist bl WHERE bl.IssueId = 
105)


>From a strictly performance perspective, I am sure that you would find the 
>following SQL to be much faster in execution (and it will work correctly for 
>all FB versions):

SELECT u.Id, u.Name, u.Email 
FROM Punter u 
WHERE 
  u.ALIVE = 1
  /* they haven't blacklisted this issue */
  AND NOT EXISTS (
SELECT 1 FROM IssueBlacklist bl 
WHERE
  bl.UserID = u.Id and bl.IssueId = 105
  )
  /* Subsribed via the product */
  AND EXISTS (
SELECT 1 FROM ProductSubscription ps
  JOIN IssueProduct ip ON (ps.ProductId = ip.ProductId)
WHERE 
  ps.UserId = u.Id and ip.IssueId = 105
  );

RE: [firebird-support] Subqueries from FB1.5 don't work in 2.0 or 2.1

[Paul Faid]

Thanks Sean,

Your suggested query works correctly on Fb 1.5 and FB 2.1. I'll modify the
queries in our application code and see if our tests pass.

I don't know enough about sql to be able to guess why the original query
doesn't work. In assembling the test scripts, it actually worked ok before
I added all the column constraints?

cheers
Paul


[Non-text portions of this message have been removed]

[firebird-support] Re: Is faster hardisk really a matter for Firebird performance?

[trskopo]

Hi Milan,

I use Win 7 64, 8GB RAM

Incendio

--- In firebird-support@yahoogroups.com, Milan Babuskov  wrote:
>
> trskopo wrote:
> > I just doing a little experiment to find out database performance on RAM vs 
> > on Disk.
> 
> Which operating system do you use?
> 
> -- 
> Milan Babuskov
> 
> ==
> The easiest way to import XML, CSV
> and textual files into Firebird:
> http://www.guacosoft.com/xmlwizard
> ==
>

[firebird-support] How to calculate RAM needed for Firebird?

[trskopo]

Hi all,

Suppose I have this setting :
- Firebird in SuperServer mode
- a Database with Page Size 8192, Page Buffers 2048
- Database cache in firebird.conf = 2048 

How many RAM needed per one connection? Is this depend on OS?

Thanks in advance and best regards,
Incendio

Re: [firebird-support] Re: How to insert only if a matching row does not exist?

[Gordon Niessen]

On 10/20/2011 11:28 AM, gastrocus wrote:
>
>
>
> --- In firebird-support@yahoogroups.com 
> , Helen Borrie 
>  wrote:
> >
> > >What is the analogous way to achieve this in Firebird (2.5) ?
> >
> > INSERT INTO emp (fruits) values ('mango')
> > where not exists (select 1 from emp where fruits = 'mango')
>
> Thanks for the quick reply.
>
> Strange... when I try to do this (via flamerobin or ibexpert) it does 
> not recognize the "Where" token (Dynamic SQL error -104, Token 
> unknown, where ). Must be something simple :(
>
> Here is what I am trying:
>
> I have a table like the following:
> CREATE TABLE T1 (
> ID INTEGER NOT NULL,
> TYPE INTEGER,
> NAME VARCHAR(128),
> SYSID INTEGER,
> FLAG INTEGER
> );
>
> ID has a generator so I don't want to insert a value for it.
>
> Here is the query I tried
>
> INSERT INTO T1 (type, name, sysid, flag) values (1, 'Z', 1, 0)
> WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z')
>
> It
> >
> > Look up INSERT OR UPDATE and also MERGE in the Language Reference 
> Update. One or both might do what you want, more efficiently than NOT 
> EXISTS. Not enough info here to guess what you're really going to do 
> with it.
> >
>
> My application is really to write a script that will be run once on a 
> smallish dataset (on the order of 1000 rows) so performance isn't 
> really a big deal.
>
> Thanks for the pointer. I did look at INSERT OR UPDATE prior to 
> posting but I do not want to UPDATE if there is a match so I didn't 
> think this applied.
>
>
>
> 
INSERT INTO EMP SELECT 'mango' from RDB$DATABASE
where not EXISTS (SELECT 1 from emp where fruits = 'mango');

or in your example:
INSERT INTO T1 (type, name, sysid, flag) SELECT 1, 'Z', 1, 0
FROM RDB$DATABASE
WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z');

-- 
Thanks,

Gordon



[Non-text portions of this message have been removed]