RE: [firebird-support] Error 335544528 in classic mode and multiple users

2014-06-25 Thread 'Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Could that Database be in single user mode? When you try to connect are you 
using SYSDBA, but your client is using another login.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, June 25, 2014 10:56 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Error 335544528 in classic mode and multiple users

 

  

I have a web server suing Firebird. I installed it using Classic mode because 
it seems more robust.
Usually, databases have between 1 to 3 users concurrently as maximum. However, 
there are 50 databases and eventually many users could be using their database.

When I open the database, it works fine. Then, if I open the same database from 
another client , I get ISC ERROR 335544528, shutdown.

Is it related with Classic mode? I understand that Classic mode creates a new 
process for each client, but does it mean that the same database cannot be 
shared by 2 clients?

Thank you

RE: [firebird-support] CrossTab Select

2014-06-25 Thread 'Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
How do you want the results  to show if you have a different produto for the 
same Hora? Or will you only have one Produto for one HORA?

 

06/23/2014 09:00 CEBOLA

06/23/2014 09:00 AJO

 

Thanks,

 

-Ed 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, June 25, 2014 6:29 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] CrossTab Select

 

  

Partners'm racking my brain on a SQL code, does anyone could give a force 

I made the following SP

CREATE PROCEDURE PRODS(
PAC INTEGER)
RETURNS(
HOR TYPE OF HORA,
SEG TYPE OF DESCRICAO,
TER TYPE OF DESCRICAO,
QUA TYPE OF DESCRICAO,
QUI TYPE OF DESCRICAO,
SEX TYPE OF DESCRICAO,
SAB TYPE OF DESCRICAO,
DOM TYPE OF DESCRICAO)
AS
BEGIN
FOR SELECT HORA, (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO 
else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 
0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 
END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 
END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 
END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 
END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END) 
from TABELAB
WHERE TABELAB.DATABBETWEEN '23.06.2014' AND '28.06.2014'
GROUP BY HORA, PRODUTO, DATAB


INTO HOR, :DOM, :SEG, :TER, :QUA, :QUI, :SEX, :SAB DO
SUSPEND; 

END

Correct Return

#Código


1

2

3

HORA  SEG   TERQUA   QUI
  SEX  SAB  DOM

09:00 CEBOLA  CEBOLA CEBOLACEBOLA  CEBOLA   
CEBOLA  0

09:10 CEBOLA  0 00  
00



Incorrect Return

#Código


1

2

3

4

5

6

7

HORA  SEG  TERQUAQUI
 SEXSAB DOM

09:00 0 0  0 0  
  0CEBOLA   

09:00 0 0  0 0  
  CEBOLA00

09:00 0 0  0 CEBOLA 
  000

09:00 0 0  CEBOLA   0   
 000

09:00 0 CEBOLA   0 0
000

09:00 CEBOLA0   0 0 
   000

RE: [firebird-support] Database Forensics

2014-07-09 Thread 'Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Hi All,

 

I have a situation where I might have to open up a FIREBIRD Database file that 
I didn’t create or maintain. I probably will not know the exact version of 
FIREBIRD this file was used with. It can be anything from FIREBIRD 2.1.4 up to 
2.5.2 

 

The database file will be given to me on a read only external HD. It will be 
accessed from a windows 7 PC. Everything I will need must already be 
preinstalled on this PC before taking possession of this Database File. (i.e. 
this PC will not be connected to the internet in order to install new 
software.) I can have an installer reside on a folder for install if I need it.

 

One question I have was that if I install a FB 2.5.2 Server  on this windows PC 
would I be able to open a Database that has been created using a 2.1.4 server? 

Are there any programs out there that can open up a Firebird DB locally without 
a server/embedded installed?

 

My plan was to… 

1)  Keep a copy of the msi installers for Firebird server 2.1.4/5 & 2.52 
somewhere on the Disk of this PC.

2) Install a copy of IBEXPERT free edition on the PC to view the data/schema

3) Install FBExport & FBCopy in case I need to export the data into another 
format.

 

Is there something I might have missed or something that will work better than 
what I plan to use.

 

Thank you in advance for any information on this matter.

 

-Ed M.

RE: [firebird-support] Database Forensics

2014-07-10 Thread 'Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
 

Thank you for the response. 

At the moment will need to open the Database and view the data contents and DDL 
of the objects and compare it to another database. The Database file we will be 
receiving should not be corrupt, but I don’t want to rule out that possibility. 
I will add IBSurgeon FirstAID to my list. 

 

Thank you.

 

-Ed M.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, July 10, 2014 1:34 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Database Forensics

 

  

On 9-7-2014 21:34, 'Edward Mendez' emendez...@nc.rr.com 
[firebird-support] wrote:
> I have a situation where I might have to open up a FIREBIRD Database
> file that I didn’t create or maintain. I probably will not know the
> exact version of FIREBIRD this file was used with. It can be anything
> from FIREBIRD 2.1.4 up to 2.5.2
>
> The database file will be given to me on a read only external HD. It
> will be accessed from a windows 7 PC. Everything I will need must
> already be preinstalled on this PC before taking possession of this
> Database File. (i.e. this PC will not be connected to the internet in
> order to install new software.) I can have an installer reside on a
> folder for install if I need it.

> One question I have was that if I install a FB 2.5.2 Server on this
> windows PC would I be able to open a Database that has been created
> using a 2.1.4 server?

Yes, a Firebird 2.5 can open database from Firebird 2.1. Upgrading the 
database by backup and restore is a best practice (it upgrades the ODS 
so new features are available), but it is not strictly necessary. With 
Firebird 3 this is going to change!

> Are there any programs out there that can open up a Firebird DB locally
> without a server/embedded installed?

At a minimum you will need a program that uses Firebird embedded.

> My plan was to…
>
> 1)Keep a copy of the msi installers for Firebird server 2.1.4/5 & 2.52
> somewhere on the Disk of this PC.

That sounds like the wisest course of action.

> 2)Install a copy of IBEXPERT free edition on the PC to view the data/schema
>
> 3)Install FBExport & FBCopy in case I need to export the data into
> another format.
>
> Is there something I might have missed or something that will work
> better than what I plan to use.

Can't think of anything else right now.

However, the big question is: what is it you need to do with the 
database? If the database is damaged and the needs to be repaired, then 
Firebird will not be able to (correctly) read the file, and you'd sooner 
need something like IBSurgeon FirstAID (assuming simple fixes with gfix 
won't work).

Mark

-- 
Mark Rotteveel

RE: [firebird-support] Terminal Services

2014-07-22 Thread 'Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Jake,

 

If could be that your Database is in single user mode (exclusive mode) and is 
only allowing one connection.

 

Try this from the command prompt;

\gfix –online \.fdb 

 

Thanks,

Ed

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Tuesday, July 22, 2014 9:05 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Terminal Services

 

  

Hey pro's,

I am sorry to ask you a question which may seem stupid but I have paged over 
forums and online articles with no joy.I am trying to run an application for 
the SPCA on a Windows MultiPoint Server 2011 (running 2008 RC2) but it will 
only work on one terminal at a time. I have contacted the software vendor and 
he tells me that the problem is with the FireBird Database. I confirmed that I  
installed the application as the Administrator in Service Mode so that I am not 
running an RDP session. I have enabled port 3050 on the server, no luck.

I modified the IPC to Firebird\Global but I am not sure what to do next. Is 
there any link you can point me to or someone I can chat to that I am sure will 
sort the problem out in a mere second.


Thanks

Jake

RE: [firebird-support] Invalid request BLR execting UNION of views

2014-08-12 Thread 'Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
 

If this is being executed from within a stored procedure, then maybe you are 
missing the SUSPEND keyword that returns the results;

 

 

 

Regards,

 

Ed Mendez

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Tuesday, August 12, 2014 12:27 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Invalid request BLR execting UNION of views

 

  

In a database, I execute the following code:

select *
from customer
union
select * from customer

The above does not make sense, it is a reduced query to show the error.

I get "Invalid token. invalid request BLR at offset 4275. BLR syntax error: 
expected record selection expression clause at offset 4276, encountered 24."

The first thought was a database corruption, but I have restored the database 
and passed the IBFirstAID application and no errors are detected. I use 
Firebird 2.5.

What else can I try?

Thank you

RE: [firebird-support] Date literals

2014-08-14 Thread 'Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Lutz,

 

The following Format ‘-MM-dd’ should work. 

Select CAST( LEFT( '20140814',4) ||'-'|| substring('20140814' from 5 for 2) 
||'-'||RIGHT('20140814',2) as DATE) from RDB$DATABASE

 

In the past I had some obstacles converting datetime values from another source 
to the latest Firebird DB. 

FireBird’s Dialect 1 will store the Date & Time value in one field of data type 
“DATE”.  Using Dialect 3, the Data Type “DATE” only stores the Date part of the 
value. You would need to parse it and store the Time part of the field to a 
field of Data Type TIME, or use the TIMESTAMP data Type which handles the Date 
& Time. 

 

Thanks,

Ed Mendez

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, August 14, 2014 4:18 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Date literals

 

  

Hi, I'm in the progress of converting an existing application from Sybase SQL 
(11.0.3 - from the stoneage) to Firebird.From Sybase (as well as some other 
databases I work with) I'm used to entering date literals as 'mmdd'.I keep 
getting "Overflow occured during data type conversion".Is it true, that this 
date format isn't recognized by firebird?Which is the most common date format, 
can you recommend any format?Thanks Lutz

RE: [firebird-support] CrossTab Select

2014-08-19 Thread 'Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
I am I including the original question in case someone has better answer

 

I am assuming that if you have 2 produtos for the same time period that you 
will want both of them displayed in the same row like so “CEBOLA,AJO”.

 

SELECT HORA, COALESCE( LIST(SEG),0) seg, COALESCE( LIST(TER),0) ter, COALESCE( 
LIST(QUA),0) qua, COALESCE( LIST(QUI),0) qui, COALESCE( LIST(SEX),0) sex, 
COALESCE( LIST(SAB),0) sab, COALESCE( LIST(DOM),0)dom from (

Select HORA, produto, MAX(SEG) seg, MAX(TER) ter, MAX(QUA) qua, MAX(QUI) qui, 
MAX(SEX) sex, MAX(SAB) sab, MAX(DOM) dom from (

SELECT HORA, PRODUTO,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 0 THEN PRODUTO ELSE NULL 
END) SEG,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 1 THEN PRODUTO ELSE NULL 
END) TER,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 2 THEN PRODUTO ELSE NULL 
END) QUA,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 3 THEN PRODUTO ELSE NULL 
END) QUI,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 4 THEN PRODUTO ELSE NULL 
END) SEX,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB)  WHEN 5 THEN PRODUTO ELSE 
NULL END) SAB,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 6 THEN PRODUTO ELSE NULL 
END) DOM

FROM TABLEAB

WHERE TABLEAB.DATAB BETWEEN '06/22/2014'

  AND '06/28/2014' ) T1

GROUP BY PRODUTO, HORA ) T2

GROUP BY  HORA

 

Here is the data I am using for this example.

 

TABLEAB_IDDATAB HORA   PRODUTO

  06/22/2014 9:00:00 AMCEBOLA

  06/23/2014 9:00:00 AMCEBOLA

  06/24/2014 9:00:00 AMCEBOLA

  06/25/2014 9:00:00 AMCEBOLA

  06/26/2014 9:00:00 AMCEBOLA

  06/27/2014 9:00:00 AMCEBOLA

  06/28/2014 9:00:00 AMCEBOLA

  16/22/2014 9:10:00 AMCEBOLA

  26/25/2014 9:00:00 AMAJO

 

Below is the output

 

HORA|SEG|TER|QUA|QUI|SEX|SAB|DOM

09:00:00|CEBOLA|CEBOLA|CEBOLA|CEBOLA,AJO|CEBOLA|CEBOLA|CEBOLA

09:10:00|CEBOLA|0|0|0|0|0|0

 

If you want different produtos on different rows then run the query below;

 

SELECT HORA, COALESCE( LIST(SEG),0) seg, COALESCE( LIST(TER),0) ter, COALESCE( 
LIST(QUA),0) qua, COALESCE( LIST(QUI),0) qui, COALESCE( LIST(SEX),0) sex, 
COALESCE( LIST(SAB),0) sab, COALESCE( LIST(DOM),0) dom from (

SELECT HORA, PRODUTO,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 0 THEN PRODUTO ELSE NULL 
END) SEG,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 1 THEN PRODUTO ELSE NULL 
END) TER,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 2 THEN PRODUTO ELSE NULL 
END) QUA,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 3 THEN PRODUTO ELSE NULL 
END) QUI,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 4 THEN PRODUTO ELSE NULL 
END) SEX,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB)  WHEN 5 THEN PRODUTO ELSE 
NULL END) SAB,

   ( CASE EXTRACT(WEEKDAY FROM TABLEAB.DATAB) WHEN 6 THEN PRODUTO ELSE NULL 
END) DOM

FROM TABLEAB

WHERE TABLEAB.DATAB BETWEEN '06/22/2014'

  AND '06/28/2014' ) T1

GROUP BY PRODUTO, HORA

 

With the following output

 

HORA|SEG|TER|QUA|QUI|SEX|SAB|DOM

09:00:00|0|0|0|AJO|0|0|0

09:00:00|CEBOLA|CEBOLA|CEBOLA|CEBOLA|CEBOLA|CEBOLA|CEBOLA

09:10:00|CEBOLA|0|0|0|0|0|0

 

I hope this helps.

 

Thanks,

 

Ed Mendez

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, June 25, 2014 6:29 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] CrossTab Select

 

  

Partners'm racking my brain on a SQL code, does anyone could give a force 

I made the following SP

CREATE PROCEDURE PRODS(
PAC INTEGER)
RETURNS(
HOR TYPE OF HORA,
SEG TYPE OF DESCRICAO,
TER TYPE OF DESCRICAO,
QUA TYPE OF DESCRICAO,
QUI TYPE OF DESCRICAO,
SEX TYPE OF DESCRICAO,
SAB TYPE OF DESCRICAO,
DOM TYPE OF DESCRICAO)
AS
BEGIN
FOR SELECT HORA, (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO 
else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 
0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 
END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 
END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 
END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 
END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END) 
from TABELAB
WHERE TABELAB.DATABBETWEEN '23.06.2014' AND '28.06.2014'
GROUP BY HORA, PRODUTO, DATAB


INTO HOR, :DOM, :SEG, :TER, :QUA, :QUI, :SEX, :SAB DO
SUSPEND; 

END

Correct Return

#Código


1

2

3

HORA  SEG   TERQUA   QUI
  SEX  SAB  DOM

09:00 CEBOLA  CEBOLA CEBOLACEBOLA  CEBOLA   
CEBOLA  0

09:10 CEBOLA  0 00  
0

RE: [firebird-support] How merge two queries

2014-09-10 Thread 'Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Luigi,

Give this a try. 

Select ANNO, MAX(IMPONIBILE), MAX(IMPOSTA) FROM (
select
   EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
   SUM(DC.IMPORTO) AS IMPONIBILE,
   0 as IMPOSTA
from
   DOC_CORPO DC,
   DOC_TESTA DT,
   VOCI V
WHERE
   DT.ID = DOC_TESTA_ID
   AND DT.DOCUMENTO_ID <> 'PRO'
   AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
   AND DC.VOCE_ID = V.ID
   AND V.CONTRIBUTI = 1
GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) UNION ALL
select
   EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
   0 as IMPONIBILE,
   SUM(DC.IMPORTO) AS IMPOSTA
from
   DOC_CORPO DC,
   DOC_TESTA DT
WHERE
   DT.ID = DOC_TESTA_ID
   AND DT.DOCUMENTO_ID <> 'PRO'
   AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
   AND DC.VOCE_ID = 'ENPACL'
GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) ) T1
GROUP BY ANNO;

Thanks,
Ed Mendez

> -Original Message-
> From: firebird-support@yahoogroups.com [mailto:firebird-
> supp...@yahoogroups.com]
> Sent: Wednesday, September 10, 2014 12:02 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] How merge two queries
> 
> Hallo,
>I have two queries:
> 
> First (results: ANNO, IMPONIBILE):
> select
>EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
>SUM(DC.IMPORTO) AS IMPONIBILE
> from
>DOC_CORPO DC,
>DOC_TESTA DT,
>VOCI V
> WHERE
>DT.ID = DOC_TESTA_ID
>AND DT.DOCUMENTO_ID <> 'PRO'
>AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
>AND DC.VOCE_ID = V.ID
>AND V.CONTRIBUTI = 1
> GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO)
> 
> 
> Second (results: ANNO, IMPOSTA):
> select
>EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
>SUM(DC.IMPORTO) AS IMPOSTA
> from
>DOC_CORPO DC,
>DOC_TESTA DT
> WHERE
>DT.ID = DOC_TESTA_ID
>AND DT.DOCUMENTO_ID <> 'PRO'
>AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
>AND DC.VOCE_ID = 'ENPACL'
> GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO)
> 
> There is a way to merge those two queries to obtain the same result in one
> query that results: ANNO, IMPONIBILE, IMPOSTA?
> 
> Thanks.
> 
> --
> 
> Luigi Siciliano
> --
> 
> 
> 
> 
> 
> 
> 
> 
> ++
> 
> Visit http://www.firebirdsql.org and click the Documentation item on the
main
> (top) menu.  Try FAQ and other links from the left-side menu there.
> 
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
> 
> 
> ++
> 
> 
> Yahoo Groups Links
> 
> 
>

RE: [firebird-support] Help With External Table Issue

2014-09-11 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Hello All,

 

We have a Firebird 2.1.4 running on CentOS 5.5 

 

I am having a problem trying to insert data into an External Table and then 
read it back.

 

Here is out output from fbsvcmgr;

fbsvcmgr service_mgr -user sysdba -password masterke  -info_server_version 
-info_implementation

Server version: LI-V2.1.3.18185 Firebird 2.1

Server implementation: Firebird/linux AMD64

 

And from uname –a

Linux   2.6.18-194.3.1.el5 #1 SMP Thu May 13 13:08:30 EDT 2010 x86_64 
x86_64 x86_64 GNU/Linux

 

So it looks like I have 64 bit OS and 64 bit Firebird install

 

I have an external table definition of about 176 fields in this particular 
external table. 88 real fields and 88 additional fields used to track NULL 
status for each field.

 

I am populating the table as so…

 

INSERT INTO EXT_TABLE ( Field1, field2, field3…)

Select Field1, field2, field3… FROM SRC_TABLE;

 

 

There should be about 1.4 Million rows written out to the External Table.

The weird thing is that my insert statement completes successfully. I get no 
Errors. 

But when I try to do an select count(*) on the external table it bombs with the 
following error message.

 

 Unsuccessful execution caused by a system error the precludes successful 
execution of subsequent statements.

 

 I/O error for file “@2”.

Error while trying to open file.

Invalid Argument.

 

On the Linux server it wrote out a file about 4.3 GBs.

 

If I do a select count(*) it bombs, but if loop via the FOR SELECT and add a 
WHEN ANY CLAUSE I get a count of about 667,000 row count.

 

I saw documentation for External Tables being 2GB for 32bit implementations, 
but the I can’t find one for firebird 2.1.4 - 64bit.

 

Maybe I might be hitting a Linux Limitation, but our FIREBIRD Database on this 
same server is 240GB. 

 

Any help would be greatly appreciated.

Thank you,

 

Ed Mendez

RE: [firebird-support] Help With External Table Issue

2014-09-11 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Hi All, 

 

I think I found out why I am having the problem. 

 

I found release notes on Firebird 2.5 and saw this following…

 

Large External Table Support Enabled

Vlad Khorsun

Previous Firebird versions used 32-bit I/O when working with external tables, 
limiting the size of the external

file to < 2 GB. The mechanism has been enhanced to use 64-bit I/O on 
filesystems that support it, effectively

eliminating the 2 GB limit.

Tracker reference CORE-2492.

 

So I am guessing that even though FB 2.1.4 DB is 64bit architecture, it is 
still using the 32 bit I/O for external Tables.

 

I was able to verify that this works well in FB 2.5.3.

 

Thanks,

 

Ed Mendez

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, September 11, 2014 5:51 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Help With External Table Issue

 

  

Hello All,

 

We have a Firebird 2.1.4 running on CentOS 5.5 

 

I am having a problem trying to insert data into an External Table and then 
read it back.

 

Here is out output from fbsvcmgr;

fbsvcmgr service_mgr -user sysdba -password masterke  -info_server_version 
-info_implementation

Server version: LI-V2.1.3.18185 Firebird 2.1

Server implementation: Firebird/linux AMD64

 

And from uname –a

Linux   2.6.18-194.3.1.el5 #1 SMP Thu May 13 13:08:30 EDT 2010 x86_64 
x86_64 x86_64 GNU/Linux

 

So it looks like I have 64 bit OS and 64 bit Firebird install

 

I have an external table definition of about 176 fields in this particular 
external table. 88 real fields and 88 additional fields used to track NULL 
status for each field.

 

I am populating the table as so…

 

INSERT INTO EXT_TABLE ( Field1, field2, field3…)

Select Field1, field2, field3… FROM SRC_TABLE;

 

 

There should be about 1.4 Million rows written out to the External Table.

The weird thing is that my insert statement completes successfully. I get no 
Errors. 

But when I try to do an select count(*) on the external table it bombs with the 
following error message.

 

 Unsuccessful execution caused by a system error the precludes successful 
execution of subsequent statements.

 

 I/O error for file “@2”.

Error while trying to open file.

Invalid Argument.

 

On the Linux server it wrote out a file about 4.3 GBs.

 

If I do a select count(*) it bombs, but if loop via the FOR SELECT and add a 
WHEN ANY CLAUSE I get a count of about 667,000 row count.

 

I saw documentation for External Tables being 2GB for 32bit implementations, 
but the I can’t find one for firebird 2.1.4 - 64bit.

 

Maybe I might be hitting a Linux Limitation, but our FIREBIRD Database on this 
same server is 240GB. 

 

Any help would be greatly appreciated.

Thank you,

 

Ed Mendez

RE: [firebird-support] Problem with Firebird 2.5.3 disconnects

2014-10-08 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Hello,

In our production environment we have a Firebird 2.14 running on CentOS 5.5 
64bit with some Delphi FrontEnds applications. 

 

I recently setup a CentOS 6.5 64bit with Firebird SuperClassic 2.5.3 and 
restored a backup from the Production server to this new Server. We have 
reconfigured one of the Delphi front ends to connect to this new Firebird 
server. Now when we are having multiple connections trying to run basic 
reports/queries the server kept crashing and fbGuardian kept restarting the 
server randomly. I thought it was the superclassic, so I uninstalled it and 
installed the classic 2.5.3 server. 

I still am getting sessions to Disconnect with the error “Error reading data 
from the connection”. I even tried using IBEXPERT to run some queries with the 
same results. The FrontEnd server is a windows 2003 server and a user 
connections to it via RDP and then runs the Delphi Frontend application. 

I don’t see anything significant in the firebird.log or the messages log in 
/var/log. Both the frontend and the server are on the same network. 

 

The Firebird server is a virtualized server, but we have most of our Firebird 
Production servers virtualized also. 

 

I wonder if this has something to do with the immediate detection of client 
loss of connectivity. That Firebird somehow thinks the client stopped 
responding and disconnects it.  

One thing in common that all the disconnected sessions have is that they were 
running a query on the server (nothing coming back to the client yet). But I 
doubt it is a timeout issue because sometimes it disconnects after 5 minutes 
and sometimes after 20 minutes. 

 

My Boss is considering going back to 2.1.x if we don’t resolve this. 

 

Any feedback would be greatly appreciated.

 

Thank you in advance,

Edward Mendez

RE: [firebird-support] PSQL: Getting both aggregation and individual rows

2014-10-21 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Josef,

Not sure if this will be efficient depending on number of rows, but this is
another way without using a TEMP_TABLE.

SELECT gen_data.id, gen_data.name, gen_data.dateandtime, gen_data.value,
T1.min_dateandtime, T1.total_value
   FROM gen_data(...)
   INNER JOIN (SELECT name, min(dateandtime) min_dateandtime,
sum(value) total_value from Gen_Data(...) group by name ) T1 on (T1.name =
gen_data.name)
INTO...
DO...

Thanks,

Edward Mendez

> -Original Message-
> From: firebird-support@yahoogroups.com [mailto:firebird-
> supp...@yahoogroups.com]
> Sent: Tuesday, October 21, 2014 4:12 AM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] PSQL: Getting both aggregation and
individual
> rows
> 
> Seems like I will have to use a temporary table for this purpose. But if
there
> is another way, I would be interested in knowing it.
> 
> Josef
> 
> > Hi!
> >
> > Is there a simple way to get an aggregation of some query as well as
> > the individual rows from within a PSQL? I.e., I have a stored
> > procedure GEN_DATA which produces individual rows (ID, Name,
> DateAndTime, Value).
> > I am processing the output of GEN_DATA in another stored procedure
> > PROCESS_DATA:
> >
> >   FOR SELECT id, name, dateandtime, value
> >   FROM gen_data(...)
> >   INTO ...
> >   DO ...
> >
> > Within this loop, I need the individual records, but I also need some
> > aggregations on them, i.e. MIN(dateandtime) or SUM(value). If I could
> > run GEN_DATA twice, then it would be easy (in the first run I would
> > calculate the aggregations, in the second run the individual values),
> > but I can't. If I could perform the processing in my application
> > (rather than the stored procedure), it would be easy, too (I would
> > simply traverse the result dataset of GEN_DATA twice), but again I
> > can't do that. Would Common Table Expressions or perhaps Derived
> Tables help me?
> >
> > Thanks,
> >
> > Josef
> 
> 
> 
> 
> 
> 
> 
> 
> ++
> 
> Visit http://www.firebirdsql.org and click the Documentation item on the
> main (top) menu.  Try FAQ and other links from the left-side menu there.
> 
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
> 
> 
> ++
> 
> 
> Yahoo Groups Links
> 
> 
>

RE: [firebird-support] Re: In Firebird can you tell when a Stored procedure has been compiled

2014-12-18 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Hi All,

 

Can you tell in Firebird 2.1.x or 2.5.x when a stored procedure has been
compiled? 

 

Thank you,

 

Edward Mendez

RE: [firebird-support] Re: In Firebird can you tell when a Stored procedure has been compiled

2014-12-20 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Mark,

 

Thank you, I kind of figured that. 

 

Maybe I should have elaborated on my situation. I was given a FB Database and I 
need to verify if any of the stored procedures have been compiled/changed after 
a certain date. From what I can gather from the RDB$ tables, it looks like this 
is not feasible. But I needed to make sure.

 

Thanks,

 

Edward Mendez

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Friday, December 19, 2014 2:18 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: In Firebird can you tell when a Stored 
procedure has been compiled

 

  

On Thu, 18 Dec 2014 18:28:56 -0500, "'Edward Mendez' emendez...@nc.rr.com
[firebird-support]"  wrote:
> Can you tell in Firebird 2.1.x or 2.5.x when a stored procedure has been
> compiled? 

It is "compiled" (to BLR) at the moment you execute the CREATE, ALTER,
CREATE OR ALTER or RECREATE statement.

Mark

RE: [firebird-support] Problem with GTT, Firebird 2.5.4 64 bit

2015-04-14 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Trond,

 

I had similar problems in the past and the culprit was with the TEMP
directory locations. 

 

I would check in the firebird.conf where the Temp directory is set to. 

I would also check the TEMP and TMP variables and see where they are
pointing to. 

Do you have a C:\TEMP  on that machine?

 

I hope this helps

 

Thanks,

 

Edward Mendez

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Tuesday, April 14, 2015 11:07 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Problem with GTT, Firebird 2.5.4 64 bit

 

  

Hello, I have a problem accessing Global temorary table. 

Environment: Firebird 2.5.4 64bit. on Windows 8.1
Connection : 127.0.0.1:database

Defining a GTT is OK 
eks:
CREATE GLOBAL TEMPORARY TABLE GTT_Test
create global temporary table gtt_Test
(
NR integer not null,
TEKST varchar(50) collate no_no
)
on commit preserve rows;
create index gtt_debseek_idxTekst on gtt_debseek (TEKST);


Is OK, but when i try to use this table
eks:
Select * from gtt_test (The error happens during prepare)
I get an error:

Unsuccessful execution caused by a system error that precludes successful
execution of subsequent statements.
I/O error during "CreateFile (create)" operation for file "".
Error while trying to create file.

The system can not find the path specified. .



The Same database works fine on other servers.

-- 

Mvh
Trond Dammen

RE: [firebird-support] Re: Problem with GTT, Firebird 2.5.4 64 bit

2015-04-15 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Dmitry,

 

I always wondered about that.  If I have set in my “firebird.conf” a list of 
directories, but my FIREBIRD_TMP environment variable has one, do you know if 
one setting take precedence over the other?

 

Thank you,

 

-Edward Mendez

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Tuesday, April 14, 2015 3:56 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Problem with GTT, Firebird 2.5.4 64 bit

 

  

14.04.2015 18:56, Trond tro-d...@online.no [firebird-support] wrote:
>
> The environment variables are as follow
> TEMP and TMP is F:\TEMP
> and FIREBIRD_TMP is F:\TEMP;C:\UTVIKLING\TEMP
>
> Temporary directories in firebird.conf are all commented out.

TempDirectories in firebird.conf supports directory lists (i.e. multiple 
directories separated by semicolon), but the FIREBIRD_TMP envvar *does 
not*. It must contain a single directory.

Dmitry

RE: [firebird-support] C:\Program Files\Firebird\Firebird_2_0\bin\fbserver.exe: terminated abnormally (4294967295)

2015-06-10 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Manny,

 

Since I haven’t seen any responses to your question I figure I’d submit my 
experience with that error.

 

This is probably a shot in the dark, be we had an issue with our Firebird 
Superserver restarting in Linux repeatedly. The cause if the issues were our 
UDFs.

 

Whenever a user tried to run a process that tried to access the bad UDFs, the 
server crashed. In Our case we moved to a new server and had to recompile the 
UDFs and it was recompiled incorrectly. The server started successfully, but 
crashed as soon as we tried to access the UDF. 

 

The Firebird log didn’t record anything other than that “Abnormal Termination” 
message. In our Linux environment I had to use the strace command to capture 
additional debug info and saw that the server was trying to access a DLL which 
should have been compiled into the UDFs.

 

If this doesn’t help, maybe you’d want to run a similar tracing utility on the 
Database server instance. Below is a link to Stackoverflow site for strace 
equivalents for the windows OS.

 

http://stackoverflow.com/questions/3847745/systrace-for-windows

 

 

I hope this is useful.

 

Ed Mendez

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Monday, June 8, 2015 6:36 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] C:\Program 
Files\Firebird\Firebird_2_0\bin\fbserver.exe: terminated abnormally (4294967295)

 

  

About 2 months ago we are having multiple clients report issues with firebird 
restarting overnight or during the day randomly. For some this happens about 10 
time a week and others 10 a day.

We use Firebird 2.0 and 2.5 and this issue is happening on both versions, but 
it is only happening to about 5% of our clients.

I have verified the power settings are correct, firewalls are open and 
anti-virus have exceptions to firebird.
Any direction and or help will be greatly appreciated.


Log Name:  Application
Source:FirebirdGuardianDefaultInstance
Date:  6/8/2015 11:40:47 AM
Event ID:  281
Task Category: None
Level: Error
Keywords:  Classic
User:  N/A
Computer:  PC
Description:
The description for Event ID 281 from source FirebirdGuardianDefaultInstance 
cannot be found. Either the component that raises this event is not installed 
on your local computer or the installation is corrupted. You can install or 
repair the component on the local computer.

If the event originated on another computer, the display information had to be 
saved with the event.

The following information was included with the event: 

Abnormal Termination: C:\Program Files\Firebird\Firebird_2_0\bin\fbserver.exe: 
terminated abnormally (4294967295)

Event Xml:
http://schemas.microsoft.com/win/2004/08/events/event";>
  

281
2
0
0x80

299675
Application
PC

  
  
Abnormal Termination: C:\Program 
Files\Firebird\Firebird_2_0\bin\fbserver.exe: terminated abnormally 
(4294967295)
  


 





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

RE: [firebird-support] Re: invalid request BLR at offset 667 bad parameter number

2015-10-20 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
 

Are Vorgid and the field in Assignment table, OrdId both defined as BigInt?

 

Also, if LevelCheck is not equal to 1 Vorgid is not initialized and will 
contain null.

 

Thanks,

 

Ed Mendez

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Tuesday, October 20, 2015 12:00 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: invalid request BLR at offset 667 bad parameter 
number

 

  

I've tracked down the problem to the variable Vorgid inside the insert into 
statement.  This is a variable that I have declared at the beginning of the 
procedure and I am using a select statement to set it's value.  So the question 
is what is the correct way to use this variable with the Insert statement.

RE: [firebird-support] MON$ATTACHMENTS.MON$REMOTE_ADDRESS showing as 0.0.0.0 in FIREBIRD 2.5.4 on CENTOS 7

2015-11-02 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Hello,

 

I have an issue with the MON$REMOTE_ADDRESS column in the MON$ATTACHMENTS 
table. The IP Address is coming back as 0.0.0.0. 

The server is running LI-V6.3.4.26856 Firebird 2.5.4

And it is running 64 bit CENTOS 7 all patched up.

 

I have disabled IPV6 and rebooted and the problem still persists.

 

I also want to mention that we have FIREBIRD 2.5.4 running on a CENTOS 6.7 and 
that is working correctly. So the issue seems something with CENTOS 7.

 

I came across the following thread that details this issue, but it doesn’t seem 
to have a resolution.  
 
https://www.mail-archive.com/firebird-support@yahoogroups.com/msg12297.html 

 

 

Any feedback would be greatly appreciated.

 

Thank you,

 

Ed Mendez

RE: [firebird-support] MON$ATTACHMENTS.MON$REMOTE_ADDRESS showing as 0.0.0.0 in FIREBIRD 2.5.4 on CENTOS 7

2015-11-02 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Hello,

 

I guess this is not a common problem, but I believe I was able to resolve this 
issue.

 

I am posting my resolution incase others are encountering the same problem.

CentOS 7 now uses systemd  instead of init,  I had to look at the corresponding 
configuration files for firebird. The systemd configuration files for firebird, 
firebird-classic@.service &  firebird-classic.socket are located in 
/usr/lib/systemd/system. 

 

#cat firebird-classic.socket

[Unit]

Description=Firebird Classic Activation Socket

 

[Socket]

ListenStream=3050

 

I changed the ListenStream to 

 

ListenStream=0.0.0.0:3050

 

and restarted the service and now firebird is listening on 0.0.0.0:3050 and not 
:::3050, which means it is listening on IPV4 protocol. 

I was able to verify that the REMOTE IP ADDRESS is returning correctly on the 
MON$ATTACHMENTS table.

 

Thank you,

 

Ed Mendez

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Monday, November 2, 2015 10:11 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] MON$ATTACHMENTS.MON$REMOTE_ADDRESS showing as 
0.0.0.0 in FIREBIRD 2.5.4 on CENTOS 7

 

  

Hello,

 

I have an issue with the MON$REMOTE_ADDRESS column in the MON$ATTACHMENTS 
table. The IP Address is coming back as 0.0.0.0. 

The server is running LI-V6.3.4.26856 Firebird 2.5.4

And it is running 64 bit CENTOS 7 all patched up.

 

I have disabled IPV6 and rebooted and the problem still persists.

 

I also want to mention that we have FIREBIRD 2.5.4 running on a CENTOS 6.7 and 
that is working correctly. So the issue seems something with CENTOS 7.

 

I came across the following thread that details this issue, but it doesn’t seem 
to have a resolution.  
 
https://www.mail-archive.com/firebird-support@yahoogroups.com/msg12297.html 

 

 

Any feedback would be greatly appreciated.

 

Thank you,

 

Ed Mendez

RE: [firebird-support] How to return a count using a SQL Select Statment

2015-11-12 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Mike,

 

You should be able to accomplish this with the use of a group by query.

 

SELECT WORKLIST_NAME,

   COUNT(*) WORKLIST_COUNT

FROM DOCKET_WORKLIST

GROUP BY WORKLIST_NAME  

 

Thanks,

Ed Mendez

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, November 12, 2015 9:23 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] How to return a count using a SQL Select Statment

 

  

This stored procedure returns:

PREP Shawnee  KS 10/01/2015 - 10/06/2015 248

RES Wyandotte  KS 10/01/2015 - 10/15/2015 4 

 

SET TERM ^^ ;

CREATE PROCEDURE SPS_DOCKET_WORKLIST_LIST returns (

  WORKLIST_NAME VarChar(60), 

  WORKLIST_COUNT SmallInt)

AS

begin

  FOR SELECT DISTINCT WORKLIST_NAME 

FROM DOCKET_WORKLIST

INTO :WORKLIST_NAME DO

BEGIN

  SELECT COUNT(*) 

FROM DOCKET_WORKLIST

   WHERE WORKLIST_NAME = :WORKLIST_NAME

INTO :WORKLIST_COUNT;  



  SUSPEND;  

END

end ^^

SET TERM ; ^^

 

I know there is a way to do this with a SQL select statement.  Can someone 
share how?

 

Thanks,

Mike

RE: [firebird-support] How do find duplicates in a table?

2016-02-04 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Also,  if you want to get back the PERSON records instead of the SOC_SEC_NO
try.

 

WITH CTE

AS (SELECT SOC_SEC_NO

FROM PERSON 

GROUP BY SOC_SEC_NO

HAVING COUNT(*) > 1)

SELECT PERSON.*

FROM PERSON 

INNER JOIN CTE ON (CTE.SOC_SEC_NO = PERSON.SOC_SEC_NO)

 

 

Hope this is useful,

 

Edward 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, February 4, 2016 3:09 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] How do find duplicates in a table?

 

  

Thanks Woody much simpler.

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, February 04, 2016 1:42 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How do find duplicates in a table?

Try this instead:

Select Soc_Sec_No, count(*) from Person
group by Soc_Sec_No
having count(*) > 1

This will list the social security numbers and the count if there are 
more than one without
all the additional selects.

HTH
Woody (TMW)

[firebird-support] RE: Moving DB from FB 2.5.3 to FB 2.5.5

2016-06-02 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Hello all,

 

I have 2 database servers running on Linux. One is running FB 2.5.3 and the
other is running FB 2.5.5. The hardware is the same 64bit architecture for
both servers.

 

I need to move some databases from the 2.5.3 server to the 2.5.5 server.
Provided the FB service is down so the DBs aren't corrupted, can I do a copy
of the DBs I need to move, or do I need to do a backup and restore instead
of the copy.

 

Thank you in advance,

Edward Mendez

RE: [firebird-support] QUERY QUESTION

2017-03-02 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Stef,

 

Maybe this query might work for you.

 

WITH CTE

AS (SELECT J0.*,

   (SELECT COUNT(*)

FROM TEST_TABLE S1

-- LEFT OUTER JOIN TEST_TABLE S2 ON S1.ITEMNR = S2.ITEMNR AND
S1.DATEUSED = S2.DATEUSED - 1

WHERE S1.DATEUSED >= J0.START_DATE

  AND S1.ITEMNR = J0.ITEMNR

  AND S1.DATEUSED < J0.END_DATE) + 1 DAYS

FROM (SELECT DISTINCT J1.ITEMNR,

  J1.DATEUSED START_DATE,

  (SELECT FIRST 1 S1.DATEUSED

   FROM TEST_TABLE S1

   LEFT OUTER JOIN TEST_TABLE S2 ON S1.ITEMNR =
S2.ITEMNR AND S1.DATEUSED = S2.DATEUSED - 1

   WHERE S2.ITEMNR IS NULL

 AND S1.DATEUSED > J1.DATEUSED

 AND S1.ITEMNR = J1.ITEMNR

   ORDER BY 1) END_DATE

  FROM TEST_TABLE J1

  LEFT OUTER JOIN TEST_TABLE J2 ON J1.ITEMNR = J2.ITEMNR AND
J1.DATEUSED = J2.DATEUSED + 1

  WHERE J2.ITEMNR IS NULL

  ORDER BY 1, 2) J0),

CTE_SUM

AS (SELECT ITEMNR,

   MAX(DAYS) DAYS

FROM CTE

GROUP BY ITEMNR)

SELECT CTE.*

FROM CTE

INNER JOIN CTE_SUM ON CTE.ITEMNR = CTE_SUM.ITEMNR AND CTE.DAYS =
CTE_SUM.DAYS

 

I hope this helps,

 

Edward Mendez

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, March 2, 2017 11:37 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] QUERY QUESTION
Importance: High

 

  

Good day,

I have a question for you SQL boffins, how can I query a table to get the
count of consecutive days found ?

A Table contains rows with dates where an item(s) has been added for every
day this item was used. 

i.e.

itemnr dateused

abc 2017/02/01

abc 2017/02/02

abc 2017/02/25

abc 2017/02/25

abc 2017/02/25

abc 2017/02/26

abc 2017/02/27

abc 2017/02/28

abc 2017/03/01

abc 2017/03/02

The result from above sample should be 6 as there is a break between
2017/02/02 and 2017/02/25?

Regards

Stef

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

RE: [firebird-support] QUERY QUESTION

2017-03-03 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Stef,

 

Will this work.

 

 

WITH CTE

AS (SELECT J0.*,

   (SELECT COUNT(*)

FROM TEST_TABLE S1

WHERE S1.DATEUSED >= J0.START_DATE

  AND S1.ITEMNR = J0.ITEMNR

  AND S1.DATEUSED < J0.END_DATE) + 1 DAYS

FROM (SELECT DISTINCT J1.ITEMNR,

  J1.DATEUSED START_DATE,

  (SELECT FIRST 1 S1.DATEUSED

   FROM TEST_TABLE S1

   LEFT OUTER JOIN TEST_TABLE S2 ON S1.ITEMNR =
S2.ITEMNR AND S1.DATEUSED = S2.DATEUSED - 1

   WHERE S2.ITEMNR IS NULL

 AND S1.DATEUSED > J1.DATEUSED

 AND S1.ITEMNR = J1.ITEMNR

   ORDER BY 1) END_DATE

  FROM TEST_TABLE J1

  LEFT OUTER JOIN TEST_TABLE J2 ON J1.ITEMNR = J2.ITEMNR AND
J1.DATEUSED = J2.DATEUSED + 1

  WHERE J2.ITEMNR IS NULL

  ORDER BY 1, 3 desc) J0),

CTE_SUM

AS (SELECT ITEMNR,

   MAX(END_DATE) LATEST_DATE

FROM CTE

GROUP BY ITEMNR)

SELECT CTE.*

FROM CTE

INNER JOIN CTE_SUM ON CTE.ITEMNR = CTE_SUM.ITEMNR AND CTE.END_DATE =
CTE_SUM.LATEST_DATE

 

I hope this is what you are looking for. 

 

Thanks,

 

Edward Mendez

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Friday, March 3, 2017 1:05 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] QUERY QUESTION
Importance: High

 

  

Edward

 

Thank you very much,  it is something like this that I am looking for.  

 

The only issue is that I only need to find the count for the latest
consecutive days used.  

 

i.e. If the itemnr was used for 12 weeks consecutively and then had a day(or
two) off and then used again for 5 days consecutively,  I need to see a
result of 5 and not 84. (MAX(DAYS) as per your query).

MIN(DAYS) will also not work as it will return the minimum consecutive days
worked and not the count of latest days.

 

I hope this makes sense.

 

Regards

 

Stef

 

 

From: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com] 
Sent: 03 March 2017 01:20 AM
To: firebird-support@yahoogroups.com
 
Subject: RE: [firebird-support] QUERY QUESTION

 

  

Stef,

 

Maybe this query might work for you.

 

WITH CTE

AS (SELECT J0.*,

   (SELECT COUNT(*)

FROM TEST_TABLE S1

-- LEFT OUTER JOIN TEST_TABLE S2 ON S1.ITEMNR = S2.ITEMNR AND
S1.DATEUSED = S2.DATEUSED - 1

WHERE S1.DATEUSED >= J0.START_DATE

  AND S1.ITEMNR = J0.ITEMNR

  AND S1.DATEUSED < J0.END_DATE) + 1 DAYS

FROM (SELECT DISTINCT J1.ITEMNR,

  J1.DATEUSED START_DATE,

  (SELECT FIRST 1 S1.DATEUSED

   FROM TEST_TABLE S1

   LEFT OUTER JOIN TEST_TABLE S2 ON S1.ITEMNR =
S2.ITEMNR AND S1.DATEUSED = S2.DATEUSED - 1

   WHERE S2.ITEMNR IS NULL

 AND S1.DATEUSED > J1.DATEUSED

 AND S1.ITEMNR = J1.ITEMNR

   ORDER BY 1) END_DATE

  FROM TEST_TABLE J1

  LEFT OUTER JOIN TEST_TABLE J2 ON J1.ITEMNR = J2.ITEMNR AND
J1.DATEUSED = J2.DATEUSED + 1

  WHERE J2.ITEMNR IS NULL

  ORDER BY 1, 2) J0),

CTE_SUM

AS (SELECT ITEMNR,

   MAX(DAYS) DAYS

FROM CTE

GROUP BY ITEMNR)

SELECT CTE.*

FROM CTE

INNER JOIN CTE_SUM ON CTE.ITEMNR = CTE_SUM.ITEMNR AND CTE.DAYS =
CTE_SUM.DAYS

 

I hope this helps,

 

Edward Mendez

 

From: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com]
  
Sent: Thursday, March 2, 2017 11:37 AM
To: firebird-support@yahoogroups.com
 
Subject: [firebird-support] QUERY QUESTION
Importance: High

 

  

Good day,

I have a question for you SQL boffins, how can I query a table to get the
count of consecutive days found ?

A Table contains rows with dates where an item(s) has been added for every
day this item was used. 

i.e.

itemnr dateused

abc 2017/02/01

abc 2017/02/02

abc 2017/02/25

abc 2017/02/25

abc 2017/02/25

abc 2017/02/26

abc 2017/02/27

abc 2017/02/28

abc 2017/03/01

abc 2017/03/02

The result from above sample should be 6 as there is a break between
2017/02/02 and 2017/02/25?

Regards

Stef

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

RE: [firebird-support] reset sysdba password without loose existing users

2017-03-23 Thread &#x27;Edward Mendez7; emendez...@nc.rr.com [firebird-support] 
Hi, 

 

What the person meant was that if a database is opened using a firebird server 
process, that process looks for a valid password for the sysdba user. This is 
the situation you are in now.  

 

But, If you take that Database and open it in embedded mode, meaning that it 
isn’t using the server process. The Embedded mode only looks for the user to 
exist. It doesn’t care about the password. Once you connect, you can then issue 
the appropriate command to change the password.

 

Thanks,

Ed Mendez 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, March 23, 2017 1:05 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] reset sysdba password without loose existing 
users

 

  

Hi,

Thanks for your reply,

I don't understand what you meansorry

As i said, i don't have sysdba passwordand i don't want to loose all
existing users account

Regards

-Message d'origine-
De : firebird-support@yahoogroups.com  
[mailto:firebird-support@yahoogroups.com] 
Envoyé : jeudi 23 mars 2017 17:29
À : firebird-support@yahoogroups.com  
Objet : Re: [firebird-support] reset sysdba password without loose existing
users

23.03.2017 17:27, startx252...@yahoo.fr   
[firebird-support] wrote:
> Anybody can help me to reset SYSDBA without loose all existing users in
Firebird

Attach in embedded mode with SYSDBA name and you can change the password
with ALTER 
USER command.

-- 
WBR, SD.





++

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