Re: [firebird-support] FB 2.5.4 - new handling of IN SUBSELECT?

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

On 5.11.2015 19:38, setysvar setys...@gmail.com [firebird-support] wrote:
>
> Den 05.11.2015 12:12, skrev Josef Kokeš j.ko...@apatykaservis.cz
> [firebird-support]:
> > SELECT COUNT(*)
> > FROM a_complex_view
> > WHERE field1=1 AND field2<>2
> > AND key_field IN (SELECT key_field FROM key_field_list)
> >
> > went from 2 seconds in FB 2.5.0-2.5.3 to 4 minutes in FB 2.5.4. After
> > rewriting the last condition to EXISTS:
> Nevertheless, I am surprised about the difference between 2.5.3 and
> 2.5.4 that you report. Your subselect is not correlated to the main
> select (i.e. you have no reference to a_complex_view inside your
> subselect), so in theory I'd expect it to be possible for the optimizer
> to change it to an EXISTS as part of its optimization (or isn't
> key_field_list a normal table?).
>

key_field_list in this case is a normal table.

I am not worried about the optimizer failing to convert the SELECT to 
EXISTS - as far as I am concerned, that's primarily my responsibility 
and I am perfectly willing to fix that. What worries me is that the 
behavior changed so drastically in a minor upgrade to Firebird and I 
can't find any reason what could have caused that.

Josef

Re: [firebird-support] Re: FB 2.5.4 - new handling of IN SUBSELECT?

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

On 5.11.2015 13:25, Dmitry Yemanov dim...@users.sourceforge.net 
[firebird-support] wrote:
>
> 05.11.2015 14:12, Josef Kokeš j.ko...@apatykaservis.cz wrote:
> >
> > I wonder: Was there any change in FB 2.5.4 which would slow down
> > processing of WHERE field IN (subselect) a lot, compared to FB 2.5.3? I
> > have been performing tests because a user of mine complained about slow
> > speed of my database, and found out that my query in the form of:
> >
> > SELECT COUNT(*)
> > FROM a_complex_view
> > WHERE field1=1 AND field2<>2
> > AND key_field IN (SELECT key_field FROM key_field_list)
> >
> > went from 2 seconds in FB 2.5.0-2.5.3 to 4 minutes in FB 2.5.4.
>
> What is execution plan for v2.5.3 and v2.5.4? Also worth trying a v2.5.5
> snapshot.
>

Hi Dmitry,

the execution plans were exactly the same (diff) for all tested 
versions: a long chain of PLAN JOIN (JOIN (... INDEX (name1)) INDEX 
(name2)).

I will see about 2.5.5 how it performs.

Josef

[firebird-support] FB 2.5.4 - new handling of IN SUBSELECT?

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

Hi!

I wonder: Was there any change in FB 2.5.4 which would slow down 
processing of WHERE field IN (subselect) a lot, compared to FB 2.5.3? I 
have been performing tests because a user of mine complained about slow 
speed of my database, and found out that my query in the form of:

SELECT COUNT(*)
FROM a_complex_view
WHERE field1=1 AND field2<>2
   AND key_field IN (SELECT key_field FROM key_field_list)

went from 2 seconds in FB 2.5.0-2.5.3 to 4 minutes in FB 2.5.4. After 
rewriting the last condition to EXISTS:

SELECT COUNT(*)
FROM a_complex_view
WHERE field1=1 AND field2<>2
   AND EXISTS (SELECT key_field FROM key_field_list WHERE 
key_field=a_complex_view.key_field)

the speed returned to 2 seconds even in FB 2.5.4. But what could be the 
cause? I went over all changes described in the 2.5.4 Release Notes and 
couldn't find anything which would seem relevant.

Thanks,

Josef

Re: [firebird-support] GBAK - validation error for column X, value "*** null ***"

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

Hi!

Thanks for the answer. Luckily, I still have the FDB file, so I can get 
rid of the bad records easily. What has me concerned is, what if I only 
had the backup? Your trick with pumping seems plausible, I will have to 
give it a try. IBBackupSurgeon looks promising, too.

Josef

On 15.5.2015 9:14, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote:
>
> Hi Josef,
>
> Such error is the consequence of the corruption - in the original 
> database there was a NULL in the field (most likely, all fields in 
> that record are NULL).
> gbak does not check constraints when reading.
>
> The best way to fix it is to find record with NULLs in the original 
> database and delete it, then repeat backup/restore.
> If you don't have original records, it can be a problem.
> Try to restore database till the privileges error and then pump data 
> from partially restored database to the new empty database with the 
> same structure - you can use free tool IBDataPump for it.
> If problems continue, you can export data using our proprietary 
> IBBackupSurgeon tool (http://ib-aid.com/en/ibbackupsurgeon/), which 
> can export data on low-level.
>
> Regards,
> Alexey Kovyazin
> IBSurgeon

[firebird-support] GBAK - validation error for column X, value "*** null ***"

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

Hi!

What would be a proper way of dealing with GBAK restore error 
'validation error for column X, value "*** null ***"'? This error 
appears in one table, where X is the primary key. The database is 
Firebird 2.5.4 in the superserver mode.

1) How do I properly restore this backup? Neither -INACTIVE nor 
-NO_VALIDITY (nor both at the same time) help, the restore stops as soon 
as the first error is encountered. -ONE_AT_A_TIME gets me to the end of 
table data, but then I get another error at the "restoring privilege for 
user Y" "action cancelled by trigger (3) to preserve data integrity" 
"table/procedure has non-SQL security class defined".

2) Why doesn't GBAK complain during backup? I would expect that if a 
backup is successfully made, I will be able to restore it.

Thanks,

Josef

[firebird-support] Request depth exceeded problem

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

Hi!

I am having trouble with one installation of my application: Whenever I
try to create a table, I receive error 335544351 "unsuccessful metadata
update\nrequest depth exceeded. (Recursive definition?)". I cannot
figure what could possibly be causing this, as the CREATE TABLE
statement is extremely simple:

CREATE DOMAIN "TID" AS INTEGER;
CREATE TABLE sometable (
  table_id TID NOT NULL,
  value1 INTEGER,
  value2 TIMESTAMP,
  value3 DOUBLE PRECISION,
  CONSTRAINT pk_sometable PRIMARY KEY (table_id)
);

(Note: The script is "anonymized", but right as I am writing this, I am
trying to create precisely this table, named "sometable"

If I remove the constraint, the table gets created OK, but of course, I
rather need the primary key. If I try to add it separately (ALTER TABLE
sometable ADD PRIMARY KEY (table_id))

I tried the following database-wide settings:

- Start/stop the server.
- Upgrade Firebird to version 2.5.3.
- Make sure the application is using a fbclient.dll (gds32.dll) from
version 2.5.3.
- Use SuperServer (fbserver.exe).
- Use a default firebird.conf.
- Run a backup/restore cycle.
- Compare that the database structure is the same as in a good
installation (where table creation works fine).
- Grab a structure from a good installation, copy all data from the
"bad" database to this correct structure, run a backup/restore cycle.

None of them helped. This one instance just won't create tables :-(

GSTAT says:

Database header page information:
Flags   0
Checksum12345
Generation  1673822
Page size   8192
ODS version 11.2
Oldest transaction  169804
Oldest active   1672359
Oldest snapshot 1672279
Next transaction1673795
Bumped transaction  1
Sequence number 0
Next attachment ID  136502
Implementation ID   16
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date   Nov 15, 2014 7:43:26
Attributes  

I find it strange that I have such a difference between Oldest
transaction, Oldest active and Next transaction, as I am convinced that
my application is closing transactions correctly, but perhaps that is
the result of the failed table-creation attempts.

Can anybody suggest a course that I could try? I am running out of
ideas. Thank you.

Josef

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

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

On 27.10.2014 10:35, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote:
>  
> 
>>Hi!
>>
>> I tried this approach before, but it violates my "only run GET_DATA
>>once" requirement :-(
> 
> Well, with EXECUTE BLOCK and ORDER BY it may be possible to satisfy your
> MIN requirement, but I can't logically understand how it even
> theoretically wold be possible to know the SUM before having gone
> through all records. Window functions in Fb 3 is probably the best

I don't mind going through the recordset twice, but I do mind creating
two recordsets. The temp-table approach satisfies my needs nicely, but I
was hoping there is a more elegant solution.

> Not that I think it matters too much, I assume the first pass to be more
> costly than the second. Have you measured how much more time is required

It's not a matter of time. The second pass would generate completely
different data (think a random number generator).

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

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

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

Hi!

I tried this approach before, but it violates my "only run GET_DATA
once" requirement :-(

Josef

On 21.10.2014 21:15, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote:
>  
> 
> What about
> 
> FOR WITH TMP(MyDateTime, MySum) as
> (SELECT MIN(dateandtime), SUM(Value)
> FROM gen_data(...))
> SELECT g.id, g.name, g.dateandtime, g.value, t.MyDateTime, tMySum
> FROM gen_data(...) g
> CROSS JOIN tmp t
> 
> Of course, you may want a different join to CROSS JOIN.
> 
> HTH,
> Set
> 
> Fra: firebird-support@yahoogroups.com [firebird-support@yahoogroups.com]
> Sendt: 21. oktober 2014 09:39
> Til: Firebird Support
> Emne: [firebird-support] PSQL: Getting both aggregation and individual rows
> 
> 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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

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

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

On 21.10.2014 20:48, 'Edward Mendez' emendez...@nc.rr.com
[firebird-support] wrote:
>  
> 
> 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

Hi!

Thanks for the suggestion. Unfortunately, this runs GEN_DATA twice, and
that it a problem. I don't mind a performance penalty, but I can't run
the data generating procedure twice.

Josef

> 
>> -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
>>
>>
>>
> 
> 







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

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

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

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

[Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]]

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Store Procedure for updating data, why it is need to commit?

[Josef Kokeš]

> Hi guys,
> 
> I have a simple stored procedure to update data, like this :
> 
> SET TERM ^ ;
> CREATE PROCEDURE REG 
> AS
> BEGIN
> UPDATE table a set a.field1 = '123';
> exit;
> END^
> 
> SET TERM ; ^
> 
> When I run that procedure on flamerobin with this command:
> 
> execute procedure Reg.
> 
> It runs ok, but without a commit command, the field1 on table won't get
> update.
> 
> I rarely use store procedure to do update / insert / delete, but is this
> a normal behavior?

Yes.

> Is there any commands to add so commit is no needed?

You could perform a commit in the procedure (via EXECUTE STATEMENT
'commit'), but it's not a good idea - inside the procedure, you don't
know how much data you are committing (what was left uncommitted from
before calling the procedure).

You could also use autocommit (also not recommended).

Josef

Re: [firebird-support] Efficient subselects

[Josef Kokeš]

On 18.4.2014 11:10, Thomas Beckmann wrote:
> Hi Josef,
> 
> what I figured out to be quite handy, is to write something like:
> 
> select
>   MASTER.*,
>   cast(left(S, 10) as bigint),
>   cast(substring(S from 10 for 10) as bigint),
>   cast(right(S, 10) as bigint),
> from (SELECT MASTER.ID,
> (SELECT
>   lpad(SUM(VALUE1), 10) ||
>   lpad(MAX(VALUE2), 10) ||
>   lpad(MIN(VALUE1), 10)
>  FROM DETAIL
>  WHERE DETAIL_ID=MASTER.ID) as S
>   FROM MASTER
>   WHERE NAME STARTING WITH 'A') s
> join MASTER m on m.ID = s.ID
> 
> Thomas

Interesting approach. I will consider it, maybe it would be sufficient
for my needs.

Thanks!

Josef





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Efficient subselects

[Josef Kokeš]

> I would consider the this second option, but I would change the join:
> 
> SELECT ...
> FROM DETAIL
> LEFT JOIN MASTER
> 
> Check on your real structure and data if it helps

Hi!

Unfortunately, this is not applicable to my case, as I do have MASTERs
which have no DETAIL (yet).

Josef

[firebird-support] Efficient subselects

[Josef Kokeš]

Hi!

I am struggling with a particular select, trying to get it to execute as
efficiently as possible. I have tables MASTER(ID, NAME, ...) and
DETAIL(ID, MASTER_ID, VALUE1, VALUE2, VALUE3). The SELECT I want to get
would display all fields from MASTER and an aggregation of fields from
DETAIL, e.g. something like:

SELECT MASTER.*,
   (SELECT SUM(VALUE1) FROM DETAIL WHERE DETAIL_ID=MASTER.ID),
   (SELECT MAX(VALUE2) FROM DETAIL WHERE DETAIL_ID=MASTER.ID),
   (SELECT MIN(VALUE1) FROM DETAIL WHERE DETAIL_ID=MASTER.ID)
FROM MASTER
WHERE NAME STARTING WITH 'A'

I would like to replace the three similar subselects with one
three-field subselect, but my attempts at using CTE rather failed:

WITH DETAIL_AGGREGATE (MASTER_ID, VALUE1, VALUE2, VALUE3)
  AS (SELECT MASTER_ID, SUM(VALUE1), MAX(VALUE2), MIN(VALUE3)
  FROM DETAIL
  GROUP BY MASTER_ID)
SELECT MASTER.*, DETAIL_AGGREGATE.*
FROM MASTER
JOIN DETAIL_AGGREGATE ON MASTER_ID=MASTER.ID
WHERE NAME STARTING WITH 'A'

This is very slow, presumably because it calculates the aggregates over
all rows in DETAIL, regardless of whether the rows are actually included
in the result.

I could probably write a traditional:

SELECT MASTER.ID, MASTER.NAZEV, MASTER.xyz, ...,
   SUM(DETAIL.VALUE1), MAX(DETAIL.VALUE2), MIN(DETAIL.VALUE3)
FROM MASTER
LEFT JOIN DETAIL ON DETAIL.MASTER_ID=MASTER.ID
WHERE MASTER.NAME STARTING WITH 'A'
GROUP BY 1, 2, 3, ...

But there are specific conditions which make this kind of SELECT rather
undesirable.

I still think that CTEs are the way to go, but I can't figure out how to
write them efficiently.

Can anyone help, please?

Thanks,

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] toggle betweenn 0 and 1

[Josef Kokeš]

On 2.4.2014 9:26, Bogdan Mihalache wrote:
>  
> 
> Update table set *column = Mod(column+1,2)* where id = :id

UPDATE table SET column=1-column WHERE id=:id

Doesn't need any functions, built-in or UDF.

Josef Kokes

Re: [firebird-support] Performance Question

[Josef Kokeš]

> Hello everybody,
> 
> we plan a web-Application with a firebird database. Now I have two
> options to prepare the data for the web-client (HTML…).
> 
> Option A – a view – returns Data from all Users and the client selects
> itself
> 
> Option B – a stored procedure with input parameters, the client gets
> only the matching records.
> 
> What should be the better option?

A is usually faster and easier to modify to match new requirements.

B is probably safer, provided that the user doesn't have credentials of
the database owner.

Personally, I go with B, because I am way more concerned about the
security than the speed. But that's just my personal deformation, it may
not be valid for your case.

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] LEFT JOIN much faster than JOIN

[Josef Kokeš]

On 2.8.2013 13:59, Alexis Diel wrote:
>  
> 
> but LEFT JOIN is diferente of JOIN...
> - LEFT JOIN brings all the data filtered from the FROM table, and
> - JOIN brings only the data that have the ON condition in the JOINED table.

Which should make JOIN faster than LEFT JOIN, if anything, because it
can (potentionally) filter out some records.

Josef

Re: [firebird-support] LEFT JOIN much faster than JOIN

[Josef Kokeš]

> Hello Josef,
>  
> I've never had that issue using Firebird, but some time ago I had a
> similar behavior with SQL Server and the responsible was a fragmented
> index. May be you should take a look to your indexes design or rebuild
> them via backup/restore.

Hi!

That should not be the case here, as all my tests were performed right
after a backup/restore cycle.

Josef

[firebird-support] LEFT JOIN much faster than JOIN

[Josef Kokeš]

Hi!

For a long time I have been using LEFT JOINs rather than JOINs
everywhere, because of some past experience with Firebird 1.5 that LEFT
JOIN is much faster than JOIN, even if I have to use WHERE
key_of_the_joined_table IS NULL afterwards. Today I encountered a case
where this is the case even with both Firebird 2.1 and 2.5. What could
possibly be the cause? Should I stick to LEFT JOINs?

The query is:

SELECT MAX(date_of_operation)
FROM table1
[LEFT] JOIN view1 ON view1.foreign_key=table1.primary_key
WHERE table1.some_field=1234

with view1 defined as a simple sequence of LEFT JOINs:

SELECT ...
FROM table2
LEFT JOIN table3 ON table3.primary_key=table2.foreign_key1
LEFT JOIN table4 ON table4.primary_key=table2.foreign_key2
...
LEFT JOIN tableX ON tableX.primary_key=table4.foreign_key

The LEFT JOIN version takes some milliseconds to perform, the JOIN
version takes over 25 seconds. The difference in plans is, as far as I
can tell, that the LEFT JOIN version first selects the relevant records
from table1 and then joins the matching records from view1 to it, while
the JOIN version first builds the view1 (all records), then joins table1
to it, and then performs the WHERE:

LEFT JOIN: PLAN JOIN (table1 INDEX (index_fk1), JOIN(JOIN(...)))
JOIN: PLAN JOIN(JOIN(JOIN(...)), table1 INDEX (index_pk))

(Note: all the joins are indexed)

In this case I will simply rewrite the query to use the LEFT JOIN and
probably keep using LEFT JOINs in all future queries, even if the JOIN
might seem more suitable to the query, but I am really curious why is
there such a difference in the optimizer.

Thanks,

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Rename a key through write to a system table

[Josef Kokeš]

On 18.7.2013 7:08, Josef Kokeš wrote:
> Hi!
> 
> I realize that the official answer to "can I rename an index" is "no",
> and that directly writing to the system tables is generally a poor idea.
> But still: How much damage can I cause by UPDATE on RDB$INDEX_NAME in
> RDB$INDICES and RDB$INDEX_SEGMENTS? I have this itch I would like to
> scratch, where I created a primary key of a table with a bad name...
> 
> The target Firebird versions are 2.1 and 2.5.
> 
> Thanks,
> 
> Pepak

Never mind, I actually *can* drop a primary key and create it again.
That solves the problem nicely.

Pepak






++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Rename a key through write to a system table

[Josef Kokeš]

Hi!

I realize that the official answer to "can I rename an index" is "no",
and that directly writing to the system tables is generally a poor idea.
But still: How much damage can I cause by UPDATE on RDB$INDEX_NAME in
RDB$INDICES and RDB$INDEX_SEGMENTS? I have this itch I would like to
scratch, where I created a primary key of a table with a bad name...

The target Firebird versions are 2.1 and 2.5.

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] How do return the number of object in a Firebird database

[Josef Kokeš]

On 17.7.2013 13:55, Softtech Support wrote:
>  
> 
> Greetings All,
> 
> v1.5.3
> 
> I need to know how many tables, indicies, functions, stored procedures,
> etc are in my Firebird database.
> 
> Is there a quick way to determine this?

-- stored procedures
SELECT COUNT(*)
FROM rdb$procedures
WHERE rdb$system_flag=0

-- functions
SELECT COUNT(*)
FROM rdb$functions
WHERE rdb$system_flag=0

-- indices
SELECT COUNT(*)
FROM rdb$indices
WHERE rdb$system_flag=0

-- tables
SELECT COUNT(*)
FROM rdb$relations
WHERE rdb$system_flag=0
  AND rdb$view_blr IS NULL

-- views
SELECT COUNT(*)
FROM rdb$relations
WHERE rdb$system_flag=0
  AND rdb$view_blr IS NOT NULL

Josef





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] NOT in Firebird

[Josef Kokeš]

> On Thu, 4 Jul 2013 10:34:21 +0200, "Olaf Kluge"  >
> wrote:
>> for example there are two variables.
>>
>> Var1 = 0
>>
>> Now I would like to negate the Var1 to Var2. If var1 = 0, then should
> Var2
>> =
>> 1 and in the other direction.
>>
>> Ist it possible Var2 = invert(var1)??
> 
> Is this just about 0 and 1 or other values as well, if so what is the
> exact transformation you need?
> 
> In this specific case, assuming var1 is always 1 or 0 you can use
> BIN_XOR(var1, 1) to achieve the desired effect.

I would generally recommend something along the lines of

CASE WHEN var=0 THEN 1 ELSE 0 END

This will work even if the variable contains unexpected values (I know,
it can't happen...).

Josef

Re: [firebird-support] Re: Values of RDB$TRIGGERS.RDB$TRIGGER_TYPE

[Josef Kokeš]

On 22.3.2013 16:13, Dmitry Yemanov wrote:
> 22.03.2013 18:44, Josef Kokeš wrote:
>>
>> I wonder, is there a documentation of the various
>> RDB$TRIGGERS.RDB$TRIGGER_TYPE values somewhere?
>
> In your Firebird distribution:
> /doc/sql.extentions/README.universal_triggers
>
> Or the same file in the SVN repository:
> http://svn.code.sf.net/p/firebird/code/firebird/trunk/doc/sql.extensions/README.universal_triggers

Got it!

Thanks.

Josef





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Values of RDB$TRIGGERS.RDB$TRIGGER_TYPE

[Josef Kokeš]

>  > Hi!
>  >
>  > I wonder, is there a documentation of the various
>  > RDB$TRIGGERS.RDB$TRIGGER_TYPE values somewhere? The Interbase 6
>  > documentation (Language Reference, specifically) lists the values for
>  > the regular BEFORE/AFTER INSERT/UPDATE/DELETE, but I can't find values
>  > for multi-event triggers, e.g. BEFORE INSERT OR UPDATE. Is there a
>  > better way to find them than to create all possible trigger types and
>  > see what appears in RDB$TRIGGERS?
>  >
>  > (I am using these values to translate a database structure to a
>  > human-readable form. But even for this limited purpose, I would feel
>  > more comfortable with the documented values than experimentally
> obtained.)
>
> Take a look at the content of RDB$TYPES.

I didn't remember about this table! Thanks!

Unfortunately, it only lists the normal (single-action) triggers and the 
database-level triggers. At least in all my databases (ranging from FB 
1.5 to FB 2.5).

Josef

[firebird-support] Values of RDB$TRIGGERS.RDB$TRIGGER_TYPE

[Josef Kokeš]

Hi!

I wonder, is there a documentation of the various 
RDB$TRIGGERS.RDB$TRIGGER_TYPE values somewhere? The Interbase 6 
documentation (Language Reference, specifically) lists the values for 
the regular BEFORE/AFTER INSERT/UPDATE/DELETE, but I can't find values 
for multi-event triggers, e.g. BEFORE INSERT OR UPDATE. Is there a 
better way to find them than to create all possible trigger types and 
see what appears in RDB$TRIGGERS?

(I am using these values to translate a database structure to a 
human-readable form. But even for this limited purpose, I would feel 
more comfortable with the documented values than experimentally obtained.)

Thank you,

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] How to migrate to new database Server?

[Josef Kokeš]

> 1. Could I install Firebird2 64 bits on a database server and access to
> the database from 32 bits web application on another machine?

Yes.

> 2. What is the process to convert Firebird 1.5 database to Firebird 2
> database?

Make a backup on the old (FB1.5) server. Uninstall the old server. 
Install the new server. Restore the backup on the new server. Check all 
of your functionality for new bugs.

Upgrading from FB1.5, you need to deal with the fact that the old 
TIMESTAMPs only used seconds precision while they use miliseconds in 
FB2. Some language constructs which were valid (though suspect) in FB1.5 
no longer work in FB2 (e.g. duplication of columns in INSERT, and I 
think in UPDATE too).

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Some alternative to ZeBeDee?

[Josef Kokeš]

On 7.2.2013 7:04, W O wrote:
> Hello everybody
>
> In some articles I saw that people use ZeBeDee for Internet communication
> with Firebird, however that product seems discontinued since the year 2005.
>
> Do you know another software similar to ZeBeDee which works well with
> Firebird and it's free?

Any tunnelling software will work for you, e.g. STunnel.

Josef





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: RES: [firebird-support] Slow connection to firebird server with win 7- 32 bits machines

[Josef Kokeš]

> The way I understood it, is each 32 bit process running on 64 bit
> windows can access a max of 2 gig each. So if you are running 3 memory
> intensive 32 bit applications they each be able to access a max of 2
> gig, utilising a total of 6 gig?

Correct.

With my selection of applications, I prefer this approach to a 
pure-64bit setup.

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] First query very slow

[Josef Kokeš]

> 1) Why do you group on something not an output field? I didn't think
> that was allowed.

It is. I am not sure if it has any reasonable use, but it is possible.

> 2) LEFT JOIN followed by referencing to a field in the table being NOT
> NULL makes the LEFT JOIN in reality become a (inner) JOIN, sometimes
> that's OK for optimization, but you haven't mentioned that being the
> case here.

Maybe my queries are wrong, but my experience has been that LEFT JOIN + 
IS NOT NULL is very often far faster than an (INNER) JOIN. These days I 
just use LEFT JOIN + IS NOT NULL everywhere because the speed tends to 
be good all the time, unlike JOIN (where I may get a good speed in one 
query and horrible speed in another; incidentally, LEFT JOIN + IS NOT 
NULL doesn't seem to be any slower than a JOIN on my queries).

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Side by side install 2.1 and 2.5?

[Josef Kokeš]

> On a Windows server with FB 2.1 I want to try using FB 2.5 for some
> purposes, so I want to do a side by side install of 2.5. The installer
> asks me to stop the existing 2.1 instances, so I suppose it cannot be
> done using the installer?

Download the ZIP archive. Then read the file 
install_windows_manually.txt inside.

> So how should I do it? get 2.5 zip package and just unpack it in a
> separate folder? I don't really like to do that under "Program files"...

Basically, yes. You will need to call instsvc.exe, too.

It doesn't have to be Program Files.

Josef





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Interbase/Firebird interaction?

[Josef Kokeš]

> This raises a couple of questions:
> 1) Are there any adverse conflicts between IB6.5 and Firebird in a
> situation like this ... i.e., would running AT against an IB6.5 engine
> ultimately damage the data?

I had some significant issues if I used FB to access an IB database 
file. It was imperative to backup the IB database and restore it as FB 
database. This might or might not be an issue for you if you distribute 
a prepared database file (with all tables, procedures, triggers etc. 
created); if you instead create the database file from scratch, then you 
should be OK.

Also, some statements behave differently under IB and FB (e.g. raise an 
error in one and work normally in the other - some CASTs, I think, and 
perhaps AS used in certain situations).

> 2) Is there some way we can determine at startup time whether there is a
> Firbird engine running or an IB engine? We might want to ensure that AT
> is running in an environment served by Firebird only.

It is definitely possible to detect a version of Firebird by querying 
various system tables (e.g. if RDB$GENERATORS has a RDB$DESCRIPTION 
field, then the database is at least FB 2.0). I suppose you could do the 
same for IB (check whether a field or table exists, which only exists in 
one of the engines).

Josef





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Create a role that has all rights for a database

[Josef Kokeš]

Hi!

I have a database which was never intended to be used by more than one 
user, so it never concerned itself with access rights - basically, all 
objects were always created by that one user, who thus had a full access 
to them.

Now I find myself in need of using another user (because I need to write 
a trigger that behaves differently in two different situations, and I 
can't distinguish between the situations by using a field; so I plan to 
use a IF (CURRENT_USER='USER1') THEN ...). What would be the easiest way 
to give the new user full rights to all objects, and preferably also to 
all objects which will be created in the future?

It seems to me that I would have to create a role, give full access to 
this role (how?), give this role to both my users, and then in the 
future keep adding rights to this role.

It might actually be more feasible to say that one of those users will 
be a SYSDBA and do an IF (CURRENT_USER='SYSDBA') THEN ...

Thanks,

Josef






++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Unsuccessful metadata update,request depth exceeded

[Josef Kokeš]

Hi!

This is a first for me. When creating a new table on one Firebird 2.5 
installation, I am getting the following error:

ISC ERROR CODE:335544351

ISC ERROR MESSAGE:
unsuccessful metadata update
request depth exceeded. (Recursive definition?)

Statement: CREATE TABLE v9karty_log (
  v9_klic TID NOT NULL,
  v9_vytvoril TID,
  v9_vytvoril_kdy TDATETIME,
  v9_zmenil TID,
  v9_zmenil_kdy TDATETIME,
  v9_stav TSTAV,
  v9_zrusil TBOOLEAN,
  v9_typ TCHAR,
  v9_datum TDATETIME,
  v9_id_karty TID,
  v9_id_expedice TID,
  v9_osoba TID,
  v9_stanice TID,
  v9_pobocka TTEXT5,
  v9_zmena_bodu TCENA,
  v9_zmena_slevy TCENA,
  v9_poskytnuta_sleva TCENA,
  v9_body_bef TCENA,
  v9_sleva_bef TCENA,
  v9_body_aft TCENA,
  v9_sleva_aft TCENA,
  CONSTRAINT pk_v9kartylog PRIMARY KEY (v9_klic)
)

Search through Google revealed that this error might happen if

a) Too many metadata updates occured without a backup/restore cycle. 
This is unlikely in this case, but I will perform a backup/restore just 
to be certain.

b) There really is a recursive element in my query. But if this is the 
case, I just don't see it - there is certainly nothing in the CREATE 
TABLE itself, and as the database doesn't use foreign keys at all, I 
would also rule out recursive cascades.

c) CORE-3763. But I don't use computed_by indexes.

What else could possibly be the problem?

Thanks,

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Re: Get Installed Folder

[Josef Kokeš]

On 13.9.2012 12:59, Allan_Fernandes wrote:
>
> Can we also understand if Firebird is installed via this method.
> I too always install Firebird as Service.
> Could you also give me sample of Service Checking please.

function SvcList(List: TStringList): boolean;
var
   ServiceManager: SC_Handle;
   P, Status: PEnumServiceStatus;
   BufSize, BytesNeeded, ServicesReturned: DWORD;
   ResumeHandle: {$IFDEF DELPHIXE2_UP} DWORD {$ELSE} THandle {$ENDIF} ;
   OK: boolean;
begin
   Result := False;
   ServiceManager := OpenSCManager(nil, nil, SC_MANAGER_CONNECT or 
SC_MANAGER_ENUMERATE_SERVICE);
   if ServiceManager <> 0 then
 try
   ResumeHandle := 0;
   BufSize := 65536;
   GetMem(Status, BufSize);
   repeat
 OK := EnumServicesStatus(ServiceManager, SERVICE_WIN32, 
SERVICE_ACTIVE or SERVICE_INACTIVE, Status^, BufSize, BytesNeeded, 
ServicesReturned, ResumeHandle);
 if not OK then
   if GetLastError = ERROR_MORE_DATA then
 begin
 BufSize := BytesNeeded;
 ReallocMem(Status, BufSize);
 OK := EnumServicesStatus(ServiceManager, SERVICE_WIN32, 
SERVICE_ACTIVE or SERVICE_INACTIVE, Status^, BufSize, BytesNeeded, 
ServicesReturned, ResumeHandle);
 end;
 if OK then
   begin
   P := Status;
   while ServicesReturned > 0 do
 begin
 List.Add(string(P^.lpServiceName));
 Inc(P);
 Dec(ServicesReturned);
 end;
   end;
   until (not OK) or (ResumeHandle = 0);
   Result := True;
 finally
   CloseServiceHandle(ServiceManager);
   end;
end;

You can read more info from P^.

Josef





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Get Installed Folder

[Josef Kokeš]

>  >>How can I get the folder where FireBird is installed.
>  >>I use Delphi and I want to copy the UDF folder after a silent
> installation.
>
> Not sure if this is the best method, but I read the DefaultInstance
> registry key. It work for me for FB 1.5, 2.1 and 2.5:
>
> RootKey := HKEY_LOCAL_MACHINE;
> Found := MyRegistry.OpenKeyReadOnly('\SOFTWARE\Firebird Project\Firebird
> Server\Instances');
> if not Found then
> Found := MyRegistry.OpenKeyReadOnly('\SOFTWARE\Wow6432Node\Firebird
> Project\Firebird Server\Instances');
>
> If Found then
> Result := MyRegistry.ReadString('DefaultInstance');
>
> Regards
> Maya

As I only run Firebird as a service, I run through a list of all 
installed services and take the binary path of all services whose 
service name (not DisplayName!) begins with "FirebirdServer". Seems to 
work OK for me, updates my UDF for all installed Firebirds - regardless 
of whether they have a key in registry or not.

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Establishing a database with Firebird

[Josef Kokeš]

> My aims are (i) establishing a new database on the new computer and (ii)
> ensuring that the old records are safely transferred. My first question
> is whether Firebird 1.5.6 is compatible to Window 7. In addition, would

Yes. Even Windows x64 are compatible with 32bit Firebird 1.5.6.

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Known issues between FB2.5 and Windows XP Firewall?

[Josef Kokeš]

Hi!

Are there some known issues between Firebird 2.5 (SuperServer, default 
install) and Windows Firewall (from WinXP)? Today is the second time I 
encountered some serious slowdowns while the XP's firewall was running 
which went away when I disabled the firewall - a simple query, which on 
another computer (or the same computer, but with a different version of 
Firebird) would take 0.1 seconds, would run for 15 to 18 seconds with 
the firewall enabled, *but only in some applications* (e.g., if I 
executed the same query from IBOConsole, it would run in the expected 
time). I am particularly baffled by two things:

1) That the communication doesn't get blocked completely but instead 
just slowed down by two or three orders of magnitude.

2) That this slowdown would appear in some client applications but not 
in others, despite the fact that all of them connect through TCP/IP.

If it happened once, I would brush it off as a random craziness of 
Windows, but the second time has me worried. Especially since the second 
case appeared suddenly, without any obvious reason and after running for 
quite some time without any issues. Any ideas?

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] UDF - reading BLOB length

[Josef Kokeš]

On 31.7.2012 14:12, Josef Kokeš wrote:
> Nice - this will also fail with the same error:
>
> function BLOBLEN(Value: PBlob): Integer; cdecl;
> begin
> Result := 1;
> end;
>
> I admit I find this very mystifying.

Never mind, found it - the problem was not in the function, but in the 
SQL declaration - I need "RETURNS INTEGER BY VALUE" rather than "RETURNS 
INTEGER".

Josef





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] UDF - reading BLOB length

[Josef Kokeš]

Nice - this will also fail with the same error:

function BLOBLEN(Value: PBlob): Integer; cdecl;
begin
   Result := 1;
end;

I admit I find this very mystifying.

Josef

> Hi!
>
> What could possibly be wrong with the following UDF?
>
> * Function *
>
> type
> TBlob = {packed} record
>   GetSegment: TISC_BlobGetSegment;
>   BlobHandle: PInt;
>   SegmentCount: LongInt;
>   MaxSegmentLength: LongInt;
>   TotalSize: LongInt;
>   PutSegment: TISC_BlobPutSegment;
> end;
> PBlob = ^TBlob;
>
> function BLOBLEN(Value: PBlob): Integer; cdecl;
> begin
> if Value = nil then
>   Result := 0
> else
>   Result := Value^.TotalSize;
> end;
>
> * Declaration *
>
> DECLARE EXTERNAL FUNCTION BLOBLEN
> BLOB
> RETURNS INTEGER
> ENTRY_POINT 'BLOBLEN' MODULE_NAME 'pwIBUDF';
>
> * Usage *
>
> SELECT BLOBLEN(someblobfield) FROM sometable
>
> I expect to get a list of lengths of a particular BLOB field in the
> table. In reality, I get an error on Firebird 1.5, 2.1 and 2.5. Error
> log of FB 2.5 says:
>
>   The user defined function:  BLOBLEN
>  referencing entrypoint:  BLOBLEN
>   in module:  pwIBUDF
>   caused the fatal exception: Access violation.
>   The code attempted to access a virtual
>   address without privilege to do so.
>   This exception will cause the Firebird server
>   to terminate abnormally.
>
> Which seems rather strange, considering that Firebird was supposed to
> pass me a valid BLOB descriptor - and in fact, in a similar function
> (but one which returns BLOB contents as a string) apparently does.
>
> I'd welcome any pointers (no pun intended).
>
> Thanks,
>
> Josef
>
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++
> Yahoo! Groups Links
>
>
>





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] UDF - reading BLOB length

[Josef Kokeš]

Hi!

What could possibly be wrong with the following UDF?

* Function *

type
   TBlob = {packed} record
 GetSegment: TISC_BlobGetSegment;
 BlobHandle: PInt;
 SegmentCount: LongInt;
 MaxSegmentLength: LongInt;
 TotalSize: LongInt;
 PutSegment: TISC_BlobPutSegment;
   end;
   PBlob = ^TBlob;

function BLOBLEN(Value: PBlob): Integer; cdecl;
begin
   if Value = nil then
 Result := 0
   else
 Result := Value^.TotalSize;
end;

* Declaration *

DECLARE EXTERNAL FUNCTION BLOBLEN
BLOB
RETURNS INTEGER
ENTRY_POINT 'BLOBLEN' MODULE_NAME 'pwIBUDF';

* Usage *

SELECT BLOBLEN(someblobfield) FROM sometable

I expect to get a list of lengths of a particular BLOB field in the 
table. In reality, I get an error on Firebird 1.5, 2.1 and 2.5. Error 
log of FB 2.5 says:

The user defined function:  BLOBLEN
   referencing entrypoint:  BLOBLEN
in module:  pwIBUDF
caused the fatal exception: Access violation.
The code attempted to access a virtual
address without privilege to do so.
This exception will cause the Firebird server
to terminate abnormally.

Which seems rather strange, considering that Firebird was supposed to 
pass me a valid BLOB descriptor - and in fact, in a similar function 
(but one which returns BLOB contents as a string) apparently does.

I'd welcome any pointers (no pun intended).

Thanks,

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Perl, Firebird, and empty Where clause

[Josef Kokeš]

> I'm trying to have an existing perl program, that is using the DBI
> system for theoretical database interoperability, to use Firebird. I've
> discovered that, as a standard, this program executes statements such as,
>
> select * from table where pkey=''
>
> That apparently works with Mysql - doing such a select with a specified
> empty where clause will return the full table. Firebird - probably more
> correctly - does a comparison for empty/null and returns...nothing.
> Without changing the perl script, is there a way I can have Firebird
> actually return the table using the above SQL?

I suppose that you could change the field to NOT NULL DEFAULT ''. Then 
would a comparison with empty string work.

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Firebird's default settings

[Josef Kokeš]

> The problem is that SuperServer is multi-threaded but not
> interlocked, so only one thread runs at a time so it cannot take
> advantage of multiple cores. Some operating systems move the process
> from one core to another in an attempt to balance load. That degrades
> performance, but can be prevented by setting the CPU affinity to a
> specific core.

Ah. I see I misunderstood the release notes completely, then: The 
superserver is multithreaded, but it only applies to separate databases, 
so I would need to use 2 (4) databases AND set CPU affinity to get the 
benefit. Correct?

Further reading seems to indicate that Firebird can't use multiple CPU 
cores for a single database in any threading mode, except for 
miscellaneous tasks such as sweep. Is this also correct?

>> Again, if I increase the number to say 131072 pages (8192 bytes
>> page size, one database only) on a 2.5 GB RAM system, can I expect
>> any adverse effects?
>
> Assuming you're using a 64 bit architecture for server and operating
> system, it should help. However, the usefulness of a large cache is
> somewhat application and data dependent, so I'd try it on a test
> machine rather than launching it directly in production.

Could you please elaborate on the "assuming you're using a 64 bit 
architecture" part? If I calculate correctly, 128K cache pages with 8K 
page size should amount to 1G memory, which is safely withing reach of a 
32bit application. Why would I need to use a 64bit system in this 
scenario? Note: I am assuming a dedicated server with only one database.

Another question: I can see why a large cache fail to help performance 
in some cases, but, assuming there is enough memory for the cache, can 
it actually harm performance?

Thanks for your answers.

Pepak

[firebird-support] Firebird's default settings

[Josef Kokeš]

Hi!

I have never tried to change Firebird's default settings, because the 
server has always been "good enough" and "if it isn't broken, don't fix 
it". But I can't help but wonder about some of the settings, because I 
don't understand their default values. Specifically, assuming Firebird 
2.5 in the default install (which, I believe, is using SuperServer):

- CpuAffinityMask - I can see why the value 1 would be a safe lower 
bound, but why doesn't the server default to a value which would use all 
available CPU cores? (That is, if CpuAffinityMask is not explicitly set, 
use all available CPUs.) Can I expect some adverse effects from setting 
CpuAffinityMask to 3 on a dual core system?

- DefaultDbCachePages - why the value 2048? Given the usual settings, 
that means only 8 MB is used for database cache (per database). Today's 
computers tend to have 2 or more GB RAM, wouldn't it make more sense to 
default to 65536 or even 262144 pages? Again, if I increase the number 
to say 131072 pages (8192 bytes page size, one database only) on a 2.5 
GB RAM system, can I expect any adverse effects?

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Manipulating BLOBs in a x64 UDF compiled with Delphi XE2

[Josef Kokeš]

Hi!

> Hello, Josef!
>
> Monday, June 18, 2012, 10:18:57 AM, you wrote:
>
> JK> function STRBLOB(Value: PAnsiChar; Dest: PBlob): PBlob;
> cdecl

Yeah, I realized too late that I should have used the declaration from 
interface, not from implementation. I have that.

> JK> The blob record (probably wrong for x64):
>
> JK> TISC_BlobGetSegment = function(BlobHandle: PInt;
> JK> Buffer: PAnsiChar;
> JK> BufferSize: LongInt;
> JK> var ResultLength: LongInt): SmallInt; cdecl;
>
> no,
> buffersize: word; // two unsigned bytes, NOT longint.
> resultlength: word: // the same two unsigned bytes

I will fix this. It should not matter, though, as I am not using 
GetSegment at all. But of course it's a good idea to have a correct 
declaration, even if I don't need it right at the moment.

Is it possible that the access violation is caused by e.g. alignment 
issues? I tried to use a packed record without success, though.

Josef Kokes




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Manipulating BLOBs in a x64 UDF compiled with Delphi XE2

[Josef Kokeš]

> The function:
>
> function STRBLOB(Value: PAnsiChar; Dest: PBlob): PBlob;
> begin
> Result := Dest;
> Dest^.PutSegment(Dest^.BlobHandle, Value, StrLen(Value))
> end;

That's CDECL, too. I copied it from the implementation rather than from 
the interface.

Josef Kokes




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Manipulating BLOBs in a x64 UDF compiled with Delphi XE2

[Josef Kokeš]

Hi!

Having bought Delphi XE2, I tried to compile my old (well, very old) UDF 
in a 64bit mode. I expected a total fail, because the UDF was written in 
Delphi 5 for Interbase 6.0, but after I fixed string types, the UDF 
actually works with Firebird 2.1 x64. With one exception - a function 
which converts VARCHAR to BLOB causes an Access Violation in the server. 
I suspect this might have something to do with the fact that x64 
pointers are 64 bits long, which causes the blob record to be of 
incorrect lenght, but I would like to get a confirmation (possibly even 
a solution :-)) from people more experienced with UDF writing that I am.

The SQL declaration:

DECLARE EXTERNAL FUNCTION STRBLOB
CSTRING(32765), BLOB
RETURNS PARAMETER 2
ENTRY_POINT 'STRBLOB' MODULE_NAME 'pwIBUDF';

The function:

function STRBLOB(Value: PAnsiChar; Dest: PBlob): PBlob;
begin
   Result := Dest;
   Dest^.PutSegment(Dest^.BlobHandle, Value, StrLen(Value))
end;

The blob record (probably wrong for x64):

   TISC_BlobGetSegment = function(BlobHandle: PInt;
 Buffer: PAnsiChar;
 BufferSize: LongInt;
 var ResultLength: LongInt): SmallInt; cdecl;
   TISC_BlobPutSegment = procedure(BlobHandle: PInt;
 Buffer: PAnsiChar;
 BufferLength: SmallInt); cdecl;
   TBlob = record
 GetSegment: TISC_BlobGetSegment;
 BlobHandle: PInt;
 SegmentCount: LongInt;
 MaxSegmentLength: LongInt;
 TotalSize: LongInt;
 PutSegment: TISC_BlobPutSegment;
   end;
   PBlob = ^TBlob;

Did anyone try to work with BLOBs in a x64 UDF already?

Thanks,

Josef Kokes




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Getting database creation date programatically

[Josef Kokeš]

 I need to find the date of last database restore. GSTAT -h can print
 this information as "Creation date". Does anyone know how can I get this
 information programatically?
>>>
>>> If monitoring tables are available, it might be easier to query
>>> MON$DATABASE with e.g.:
>>>
>>> select mon$creation_date from mon$database
>>
>> Unfortunately, I need this information for Firebird 1.0, too :-(
>
> In general, you can use the Services API to fetch the header page and
> then parse the content to get the creation date.
>
> Depending on your used database access layer product/components,
> especially in the Delphi world, they usually offer components which wrap
> the Services API calls/layer into an ease to use object model.
>
> So, what database access layer do you use?
>

I am working in a very heterogenous environment. The worst components 
which I still need to support are a heavily modified ancient version of 
ZeosLib for Delphi 5, and some unnamed C library used by a Linux 
version. So I think using the API directly and not depending on 
components is my best option.

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Getting database creation date programatically

[Josef Kokeš]

On 15.6.2012 9:19, Thomas Steinmaurer wrote:
>> I need to find the date of last database restore. GSTAT -h can print
>> this information as "Creation date". Does anyone know how can I get this
>> information programatically?
>
> If monitoring tables are available, it might be easier to query
> MON$DATABASE with e.g.:
>
> select mon$creation_date from mon$database

Unfortunately, I need this information for Firebird 1.0, too :-(

But in the meantime I will query mon$database on my newer servers. Thanks.

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Getting database creation date programatically

[Josef Kokeš]

Hi!

I need to find the date of last database restore. GSTAT -h can print 
this information as "Creation date". Does anyone know how can I get this 
information programatically?

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Question about user rights and procedures

[Josef Kokeš]

Hi!

I feel a bit overwhelmed by user rights in a database in respect to 
stored procedures.

I have a database owned by user OWNER. I want to give limited access to 
user ATTACKER, so that he can read and write the database through a 
stored procedure MODIFY_DATABASE. So I grant:

GRANT EXECUTE ON PROCEDURE modify_database TO attacker;

But I also need to grant required rights to the procedure:

GRANT SELECT ON readonly_table TO PROCEDURE modify_database;
GRANT SELECT, INSERT, UPDATE ON other_table TO PROCEDURE modify_database;

Suppose the procedure MODIFY_DATABASE calls other procedures to read or 
write the database. That would mean that I would have to add execute 
rigths on those procedures to MODIFY_DATABASE, and grant 
EXECUTE/SELECT/INSERT etc. rights to those procedures and the procedures 
called by them, etc. Can I somehow avoid this? I mean, somehow make 
MODIFY_DATABASE to have all rights on all objects, as if it were called 
by OWNER and not ATTACKER?

Thanks,

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Identifying damanged pages

[Josef Kokeš]

Dne 29.3.2012 18:26, Ann Harrison napsal(a):
> 2012/3/29 Josef Kokeš
>
>>
>> Due to hardware malfunction, my database got damaged. GFIX (-v -f -i)
>> tells me that a database has 1 "record level error", 9 "data page
>> errors", 41 "index page errors" and 322 "database page errors".
>>
>
> IBFirstAid is your friend, as is backup.  But before you backup the
> database, make a copy, use gfix to set it to read only, and back it
> up with gbak, using the -g switch to suppress garbage collection.
>
> If that works, you're unlikely to have lost data.  Backup walks each
> table in storage order.  The structures are linked, so a lost or damaged
> data page will show up.

Luckily, the damaged table was one which rarely changes, so I was able 
to restore it from backup with good results. But I am noting down this 
suggestion for future reference. THanks for the suggestion!

Josef




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Identifying damanged pages

[Josef Kokeš]

Hi!

Due to hardware malfunction, my database got damaged. GFIX (-v -f -i) 
tells me that a database has 1 "record level error", 9 "data page 
errors", 41 "index page errors" and 322 "database page errors".

I am not concerned about Index page errors - is seems obvious these will 
get fixed by a backup/restore cycle. But I would like to know:

1) What do the individual error types mean?
2) Is there a way to identify which tables were affected? I can live 
with damage to many tables, but not all of them.

Thanks,

Josef Kokes




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] How to drop an inconsistent table

[Josef Kokeš]

Hi!

This is a new one for me: I have a Firebird 2.5 database file with 
corruption not in records, but in table structures themselves. After I 
used "GFIX -v -f", "GFIX -m" and backup/restore, I am left with a 
database which basically works, but as soon as I try to access certain 
tables, I get an "Internal Firebird consistency check (cannot find 
record back version (291))"; from this time on, until I restart the 
Firebird service, just about every access to the database file raises 
the consistency error.

As luck would have it, the damaged tables are non-essential: As far as 
useful data are concerned, I could drop the tables and create them again 
and all would be fine. Except that I can't, because these tables have 
triggers attached and as soon as I try to drop the triggers, I get the 
consistency error.

Can anyone suggest a way to fix this database? The only solution that I 
can think of involves creating a new database with the same structure 
and moving data from all non-damaged tables to it. I am not too happy 
with this solution, though, as moving that many records around would 
take up some serious time.

Another question: As far as I was able to determine, this problem arose 
when the user rebooted the server while database operations were 
running. It's been my impression that transactions (with forced writes) 
should pretty much prevent any problem with abuse of this kind, and even 
if they couldn't, why would an unfortunate reboot damage several 
unrelated tables, most of which were almost certainly not being used at 
the time of the reboot?

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Use of column aliases in a SELECT

[Josef Kokeš]

>> Hi!
>>
>> I find myself repeatedly writing code such as
>>
>> SELECT (a+b)*c AS value1,
>>   some_function((a+b)*c) AS value2,
>>   some_other_function((a+b)*c) AS value 3
>> FROM ...
>>
>> Is there any other way to "reuse" a calculated value by referencing its
>> alias than through views? E.g.
>>
>> CREATE VIEW valueview AS SELECT SELECT (a+b)*c AS value1 FROM ...
>>
>> SELECT value1, some_function(value1) AS value2 ...
>>
>> I know I can reference the column by its position in GROUP BY, but I
>> need to reference it in the field list as well as in the WHERE condition.
>>
>> Thanks,
>>
>> Pepak
> How about derived tables? - (supported from version 2.0 onwards)
>
> http://www.firebirdsql.org/refdocs/langrefupd21-select.html#langrefupd21-derived-tables

I guess. I am just not very familiar with them yet, as I still need to 
support Firebird 1.5. But it's definitely something to keep in mind once 
I get rid of those.

Thanks,

Pepak





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Use of column aliases in a SELECT

[Josef Kokeš]

Hi!

I find myself repeatedly writing code such as

SELECT (a+b)*c AS value1,
some_function((a+b)*c) AS value2,
some_other_function((a+b)*c) AS value 3
FROM ...

Is there any other way to "reuse" a calculated value by referencing its 
alias than through views? E.g.

CREATE VIEW valueview AS SELECT SELECT (a+b)*c AS value1 FROM ...

SELECT value1, some_function(value1) AS value2 ...

I know I can reference the column by its position in GROUP BY, but I 
need to reference it in the field list as well as in the WHERE condition.

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Using FB another port than 3050

[Josef Kokeš]

Dne 25.1.2012 12:17, bennie_coetzer napsal(a):
> Hi, I am still battling with FB on another port. I have changed the
> firebird.conf file to use port 3051. This seems to work as the port is
> actively monitored when FB runs and not when it is stopped.I then used
> FlameRobin and created a host (LocalFB) on port 3051. When trying to
> create a database in this host I get the following error
> IBPP::SQLExceptionContext: Database::CreateMessage:
> isc_dsql_execute_immediate failed
> SQL Message: -902Unsuccessful execution caused by a system error that
> precludes successful execution of subsequent statements
> Engine Code: 335544721Engine Message:Unable to complete network request
> to host "LocalFB".Failed to locate host machine.
> However, when I create the database with the default LocalHost Server
> (No port is specified) it is successful. Does the unspecified port
> indicate that it will use the port as specified in the firebird.conf
> file or the default 3050.
> RegardsBennie

What does your connection string look like? Remember that on Windows at 
least you need to use something like this:

SERVER/3051:C:\Database.fdb

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Converting BLOB to long VARCHAR

[Josef Kokeš]

Dne 28.12.2011 17:21, Milan Babuskov napsal(a):
> Josef Kokeš wrote:
>> I am trying to convert a BLOB (unlimited size, theoretically) to a long
>> VARCHAR (say, VARCHAR(8192)). This should be easy enough to do:
>>
>> DECLARE VARIABLE b BLOB; /* input */
>
> Which subtype of BLOB? IIRC, BLOB subtype 1 (text) should support
> character sets, so you don't have to lose non-ASCII characters.
>
> If it's a binary blob (subtype 0) you can also try to dump the contents
> to a file (using FBExport or your code) and then import it back into a
> new subtype 1 blob.

I need to do it on the run, not once, so dump is not an option. With 
subtype 0, too.

Anyway, it turned out the UDF approach works after all, though I really 
don't see why the new version works while the version I posted in the 
original question doesn't - as far as I can tell the only difference is 
that I removed the initialization of the return field.

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Converting BLOB to long VARCHAR

[Josef Kokeš]

Hi!

I am trying to convert a BLOB (unlimited size, theoretically) to a long 
VARCHAR (say, VARCHAR(8192)). This should be easy enough to do:

DECLARE VARIABLE b BLOB; /* input */
DECLARE VARIABLE v VARCHAR(8192); /* output */
...
v = SUBSTRING(b, 1, 8192);

Except that as soon as I convert between BLOB and VARCHAR, I lose all 
high-ASCII characters (they get replaced by a dot). E.g., to expand the 
example above:

DECLARE VARIABLE b BLOB; /* input */
DECLARE VARIABLE b2 BLOB; /* output */
DECLARE VARIABLE v VARCHAR(8192); /* output */
...
v = SUBSTRING(b, 1, 8192);
b2 = SUBSTRING(b, 1, 8192);

When this code is run, both b2 and v contain the first 8192 characters 
from the input - but in v's case, the high-ascii characters get 
converted to a dot.

Supposedly this should be done using a UDF, but I just can't figure out 
how to write one (in Delphi):

function BLOBSTR(Value: PBlob; Size: Longint; Dest: PAnsiChar): 
PAnsiChar; cdecl;
begin
   Result := Dest;
   FillChar(Result^, Size, 'a');
end;

(I decided to remove the BLOB-reading code to make sure I have no errors 
in my parameter-passing code.)

But with a declaration:

DECLARE EXTERNAL FUNCTION BLOBSTR
BLOB, INTEGER, CSTRING(8192)
RETURNS PARAMETER 3
ENTRY_POINT 'BLOBSTR' MODULE_NAME 'pwIBUDF';

and a select:

SELECT BLOBSTR(blobfield, 10) FROM sometable WHERE id=1

I get an empty (zero-byte, NOT NULL) result. I would expect to get 
either a 10-byte result (if the buffer for parameter 3 gets filled with 
zeroes before the UDF is called) or something between 10 and 8192 byte 
result (if the buffer does not get initialized), but zero has me stumped.

I would appreciate any pointers.

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Fast massive delete with complex criteria

[Josef Kokeš]

> I find myself in a situation where I need to delete large amount of data
> from a huge (comparably) table, with relatively complex criteria on what
> stays and what gets deleted:
>
> - The table in question has about 6 million rows.
> - The rows contain a mix of integer, timestamp and blob columns, with
> primary key A (integer) and secondary keys B (integer), C (integer) and
> D (timestamp).
> - I want to keep at least one record for every combination of B and C:
> a) the newest (HAVING D = MAX(D))
> b) within the last 30 days ((D+30)>  'now')

Forgot to mention: the table and its indexes amount to some 10 GB or so.

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Fast massive delete with complex criteria

[Josef Kokeš]

Hi!

I find myself in a situation where I need to delete large amount of data 
from a huge (comparably) table, with relatively complex criteria on what 
stays and what gets deleted:

- The table in question has about 6 million rows.
- The rows contain a mix of integer, timestamp and blob columns, with 
primary key A (integer) and secondary keys B (integer), C (integer) and 
D (timestamp).
- I want to keep at least one record for every combination of B and C:
   a) the newest (HAVING D = MAX(D))
   b) within the last 30 days ((D+30) > 'now')

I have been trying a number of approaches, all of which turned out to be 
unacceptably slow:

a) A stored procedure which loops though a SELECT B, C, MAX(D) GROUP BY 
B, C, then DELETEs records with B=FoundB AND C=FoundC AND D='now'; #2: WHERE A IN (SELECT MAX(A) GROUP BY B, C)) 
to set it to 1 for my required records, DELETE all records with keep_me 
IS NULL, then drop the extra column. This is still running in the second 
UPDATE.

c) Apparently a DROP TABLE is fast enough, so my next approach (if b 
fails) is to create a new temporary table, INSERT records which I want 
to keep into it, DROP the original table, recreate it, INSERT records 
back and finally DROP the temporary table.

My questions are:

1) Am I overlooking some possible fast approach?
2) Do you think c has a reasonable chance of being the fastest?

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] PL/SQL vs nonexistent UDF

[Josef Kokeš]

>> Is it possible do declare a stored procedure which would use a
>> non-existent external function? That is, I can define an external
>> function using:
>>
>>  declare external function test
>>  returns integer
>>  entry_point 'TEST' module_name 'pepak.dll'
>>
>> This will succeed even if pepak.dll does not exist or does not export
>> function TEST. That's OK as long as I do not attempt to call that function.
>>
>> Now I would like to create a stored procedure which would use that function:
>>
>>  create procedure test
>>  returns ( a integer )
>>  as
>>  begin
>>a = test();
>>suspend;
>>  end
>>
>> But at this moment Firebird WILL check whether the function test exists
>> in pepak.dll and return an error if it does not. Is there some way to
>> suppress this error and create the procedure? I do not intend to use the
>> procedure until the application makes sure a required version of
>> pepak.dll is being used.
>
> There is no way to do that, AFAIK.
>
> You could reference another existing UDF with the same interface or have
> some kind of stub UDF with at least the proper function exports.

Thanks. For now I am using EXECUTE STATEMENT to overcome the problem. It 
works fine in my particular case, though if there was a solution which 
didn't require EXECUTE STATEMENT, I would happily switch to it.

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] PL/SQL vs nonexistent UDF

[Josef Kokeš]

Hi!

Is it possible do declare a stored procedure which would use a 
non-existent external function? That is, I can define an external 
function using:

   declare external function test
   returns integer
   entry_point 'TEST' module_name 'pepak.dll'

This will succeed even if pepak.dll does not exist or does not export 
function TEST. That's OK as long as I do not attempt to call that function.

Now I would like to create a stored procedure which would use that function:

   create procedure test
   returns ( a integer )
   as
   begin
 a = test();
 suspend;
   end

But at this moment Firebird WILL check whether the function test exists 
in pepak.dll and return an error if it does not. Is there some way to 
suppress this error and create the procedure? I do not intend to use the 
procedure until the application makes sure a required version of 
pepak.dll is being used.

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Order of rows returned by stored procedures

[Josef Kokeš]

>  >It's been my experience that if I have a stored procedure which returns
>  >rows in a particular order and perform a SELECT on this procedure, the
>  >row order of the SELECT matches that of the procedure. E.g.:
>  >
>  >What I would like to know, is this behavior documented or is it just a
>  >random occurence? I know that when SELECTing from tables/views I should
>  >always use ORDER BY if I need the records sorted in a particular order,
>  >but is the same thing true for selecting from stored procedures?
>
> Rows are returned in the exact order they are sent, i.e., the first
> SUSPEND sends the first row to the buffer, the second SUSPEND sends the
> second row, and so on.

*Returned* is obvious. But if I *read* these returned rows and output 
them again, is it a documented behavior that the output will reflect the 
ordering from the stored procedure?

> It is much more efficient to have your SSP work with an ordered set, as
> the FOR SELECT inside the SP can use indexes. Ordering the set of
> virtual output is a sloow process and of course there are no indexes
> to call on.

Of course. But that's not the issue here. The issue is, if my procedure 
returns a growing sequence, can I depend on it always being growing or 
should I make sure by an explicit order outside of the procedure?

Thanks,

Pepa




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Order of rows returned by stored procedures

[Josef Kokeš]

Hi!

It's been my experience that if I have a stored procedure which returns 
rows in a particular order and perform a SELECT on this procedure, the 
row order of the SELECT matches that of the procedure. E.g.:

CREATE PROCEDURE test
RETURNS ( value INTEGER )
AS
BEGIN
   value = 1;
   SUSPEND;
   value = 2;
   SUSPEND;
   value = 3;
   SUSPEND;
END

SELECT * FROM test

value
=
1
2
3

What I would like to know, is this behavior documented or is it just a 
random occurence? I know that when SELECTing from tables/views I should 
always use ORDER BY if I need the records sorted in a particular order, 
but is the same thing true for selecting from stored procedures?

Thanks,

Pepa




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Great variance in speed of an INSERT INTO ... SELECT

[Josef Kokeš]

> OK, then we'll need to look for less common problems. When the query
> is slow, are you seeing lots of page faults? Process size growth?
> What is the rate of reads/writes/marks/fetches? Do you have any guess
> as to where the time is going?

Hi Ann,

I am afraid most of the values you are asking me about are outside of my 
ability to gather, at least until I learn where to find them.

Well, I do see a strange behavior. When the process started, Firebird 
fully utilized the CPU core it was running on, with memory requirements 
being very moderate (the highest I have seen is 70 MB). A few minutes 
into the operation, the Firebird's CPU load dropped to some 3-5%, with 
memory load at less than 20 MB. It doesn't seem to change anymore.

Process Explorer reveals:

- The most active thread seems to be a thread in state Wait:WrResource
- I/O bytes fluctuate a lot between 100 and 800 KB
- I do not see any open handles to temporary files, only the databases, 
the lock file, the message file and expected system libraries and devices

It seems very much as if Firebird thinks someone is holding a required 
resource and is waiting until that resource is freed, but I have no idea 
which resource it could be.

Well, well, well... Just as I was finishing the previous sentence, 
Firebird suddenly once again utilized the CPU completely and a few 
seconds after that finished the job.

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Great variance in speed of an INSERT INTO ... SELECT

[Josef Kokeš]

Hi Ann,

> The WHERE 1 = 1 is unnecessary, but doen't do any harm. There maybe
> databases that require a WHERE clause, but Firebird doesn't. But that's not
> the problem.

I know. But I wanted to emphasise that there are no limitations on the 
rows, so no index should come into play.

> The normal cause of wild variations in the performance of queries is garbage
> collection. For example, if you did this query twice, deleting all rows in
> backuptable, then resetting the generator backupgen to 1, the second run
> would have to remove all the old rows and deleted stubs and clean up the
> indexes (assuming that source_key is actually a unique key).

Garbage collection doesn't seem to play any significant part. I even 
deleted the whole backup table, performed the backup/restore cycle, and 
still got long times. What *did* help was limiting the SELECT from 
sourcetable to "significant records", some 6000 of them (a reduction of 
two orders of magnitude). But it still seems strange that Firebird would 
choke on INSERTs, even in what I perceive to be the ideal condictions to 
the server.

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Great variance in speed of an INSERT INTO ... SELECT

[Josef Kokeš]

Hi!

I wonder - what could possibly cause a great variance in speeds of this 
statement?

INSERT INTO backuptable (
   backup_key,
   original_key, original_value1, ..., original_valuen
)
SELECT GEN_ID(backupgen,1),
   source_key, source_value1, ..., source_valuen
FROM sourcetable
WHERE 1=1 -- all records

The statement takes anything between minutes and hours (in different 
databases, the same database usually takes approximately the same time), 
and I just can't figure out what could cause this variance. The SELECT 
itself is quite fast (seconds), traversing its resultset as well (tens 
of seconds). Source_value* is either a DOUBLE PRECISION or an INTEGER. 
The backuptable only has three indices including the primary key (the 
other two cover INTEGERs). The size of the sourcetable is approximately 
600K records, the size of backuptable approximately 26M records. The 
Firebird version (1.5, 2.1, 2.5) doesn't seem to have a significant 
effect on this).

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Stored procedure with blobs and FB1.5

[Josef Kokeš]

Looks like I can solve it by "real" conversion to BLOB using an UDF; 
mere assignment from VARCHAR-type variable to a BLOB-type one is not 
sufficient, at least not in Firebird 1.5 (even though this article 
claims it should work since Firebird 0.9.4: 
http://www.volny.cz/iprenosil/interbase/ip_ib_strings.htm)

Pepak

> Hi!
>
> I have just encountered a strange problem with Firebird 1.5:
>
> SET TERM /;
> CREATE TABLE test_table (
> something BLOB
> )
> /
> CREATE PROCEDURE test_write (
> input_value BLOB
> ) AS
> BEGIN
> INSERT INTO test_table VALUES (:input_value);
> END
> /
> EXECUTE PROCEDURE test_write('1')
> /
> EXECUTE PROCEDURE test_write('2')
> /
> EXECUTE PROCEDURE test_write('3')
> /
> EXECUTE PROCEDURE test_write('4')
> /
> EXECUTE PROCEDURE test_write('5')
> /
> DROP PROCEDURE test_write
> /
> DROP TABLE test_table
> /
>
> For some reason, the "EXECUTE PROCEDURE test_write" statements fail
> ("internal error", "Statement failed SQLCODE = -902") on Firebird 1.5.5
> but work OK under 2.1 (and presumably 2.0).
>
> The same thing happens if I declare the input parameter of test_write as
> VARCHAR and convert it to a BLOB before INSERT.
>
> Now I know FB 1.5 is discontinued (and 1.5.5 is not even the latest
> version of that branch) so if it is a bug, it won't be fixed, but as a
> great number of my users still have this version, I wonder if I could
> change the statements somehow to make them work for them. I can use a
> custom UDF for that, if necessary, but I can't update the Firebird itself.




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[firebird-support] Stored procedure with blobs and FB1.5

[Josef Kokeš]

Hi!

I have just encountered a strange problem with Firebird 1.5:

SET TERM /;
CREATE TABLE test_table (
   something BLOB
)
/
CREATE PROCEDURE test_write (
   input_value BLOB
) AS
BEGIN
   INSERT INTO test_table VALUES (:input_value);
END
/
EXECUTE PROCEDURE test_write('1')
/
EXECUTE PROCEDURE test_write('2')
/
EXECUTE PROCEDURE test_write('3')
/
EXECUTE PROCEDURE test_write('4')
/
EXECUTE PROCEDURE test_write('5')
/
DROP PROCEDURE test_write
/
DROP TABLE test_table
/

For some reason, the "EXECUTE PROCEDURE test_write" statements fail 
("internal error", "Statement failed SQLCODE = -902") on Firebird 1.5.5 
but work OK under 2.1 (and presumably 2.0).

The same thing happens if I declare the input parameter of test_write as 
VARCHAR and convert it to a BLOB before INSERT.

Now I know FB 1.5 is discontinued (and 1.5.5 is not even the latest 
version of that branch) so if it is a bug, it won't be fixed, but as a 
great number of my users still have this version, I wonder if I could 
change the statements somehow to make them work for them. I can use a 
custom UDF for that, if necessary, but I can't update the Firebird itself.

Thanks,

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] UDF

[Josef Kokeš]

> Hello, this is related to my previous post... I'm trying to write a UDF
> in Delphi... It's declared like this:
>
> function Descuentos(var Suma:currency;Descuentos:pchar):double; cdecl;
> export;
>
> DECLARE EXTERNAL FUNCTION SG_DES
> DOUBLE PRECISION,
> CSTRING(50)
> RETURNS DOUBLE PRECISION BY VALUE
> ENTRY_POINT 'Descuentos' MODULE_NAME 'SGUDF';

Delphi type Currency is not the same thing as Firebird type DOUBLE 
PRECISION. Change the Delphi declaration to:

function Descuentos(var Suma:double;Descuentos:pchar):double; cdecl; export;

Pepak




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/