At 11:54 11/11/2003 +0000, you wrote:

>
>To unsubscribe from this group, send an email to:
>fir
> From: Uwe Oeder <uweo@...>
> 4. Re: Re: Turkish character set problem
> From: "Cavit Aladag" <aladag@...>
> 5. RE: Re: SubSelect Question
> From: "Alan McDonald" <alan@...>
> 6. Re: SQL Error
> From: Helen Borrie <helebor@...>
> 7. Re: SubSelect Question
> From: "Tom Frey" <tom@...>
> 8. RE: Re: SubSelect Question
> From: "Alan McDonald" <alan@...>
> 9. RE: Re: SubSelect Question
> From: "Alan McDonald" <alan@...>
> 10. Re: SubSelect Question
> From: "Tom Frey" <tom@...>
> 11. Re: Re: Zeos Library?
> From: Serg Vostrikov <sv@...>
> 12. Re: Turkish character set problem
> From: "peter_jacobi.rm" <peter_jacobi@...>
> 13. Re: Generators Associated with a Table
> From: "Svein Erling" <svein.erling.tysvaer@...>
> 14. Re: and again... SQL help needed
> From: "Svein Erling" <svein.erling.tysvaer@...>
> 15. Re: SubSelect Question
> From: "Svein Erling" <svein.erling.tysvaer@...>
> 16. Re: SQL ERROR Code = -104 user name required
> From: "Alexander V.Nevsky" <ded@...>
> 17. Re: Please Help: Function unknown upper??
> From: "Daniel" <dp@...>
> 18. Re: Re: Turkish character set problem
> From: "Cavit Aladag" <aladag@...>
> 19. Re: I am a new member
> From: "Alexander V.Nevsky" <ded@...>
> 20. RE: Re: SubSelect Question
> From: "Alan McDonald" <alan@...>
> 21. Re: Turkish character set problem
> From: "peter_jacobi.rm" <peter_jacobi@...>
> 22. Character Sets
> From: Uwe Oeder <uweo@...>
> 23. RE: Character Sets
> From: "Henrik Sitter" <henrik.sitter@...>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 1
> Date: Tue, 11 Nov 2003 18:26:05 +1100
> From: Helen Borrie <helebor@...>
>Subject: Re: Re: Improve the Performance of Select
>
>At 07:11 AM 11/11/2003 +0000, you wrote:
>
> >heLen, thanks for your advise. The select is faster now from 7 sec to
> >2 sec. Should I remove history_idx2 or leave it as inactive.
>
>Drop it.
>
> > If set
> >statistics on history_idx, will it cause the performance of insert
> >drop ?
>
>No, SET STATISTICS is a housekeeping function that you should do on all
>indexes on large, dynamic tables from time to time, to keep the optimizer
>up-to-date with their current state of distribution.
>
>heLen
>
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 2
> Date: Tue, 11 Nov 2003 08:45:50 +0100
> From: Bjoern Reimer <bjoern.reimer@...-erlangen.de>
>Subject: Re: Install Firebird SuperServer on Solaris 7 with Ultra Spark
>Enterprise 450 Server
>
>Hello,
>
>rkd> IBSERVER (Server) Thu Sep 18 12:07:50 2003
>rkd> SERVER/process_packet: connection rejected for interbase
>
>
> add localhost to /etc/hosts.equiv
>
> and don't call the user "interbase"! It's firebird! :-)
>
>
> regards
>
> Björn
>
>--
>Björn Reimer -- RRZE
>Tel: +49-9131 - 85-27809
>Fax: +49-9131 - 302941
>Martensstr. 1 / 2.021
>D - 9 1 0 5 8 Erlangen
>www.rrze.uni-erlangen.de
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 3
> Date: Tue, 11 Nov 2003 10:07:27 +0200
> From: Uwe Oeder <uweo@...>
>Subject: Re: SQL Error
>
>Sorry about should have probably have said. That I use the BDE to access my
>paradox tables. I have found the error. It occurred because of record 183
>which had a field value of the following :"Réunion"
>I hope you can see in this email that the 2nd letter is an e with a line on
>it. When recreated my table which had previously the ASCII characters set
>set to none it worked without problems. I am still not sure why ASCII wont
>take the "é" Alt-0233. Used following statement : DEFAULT CHARACTER SET
>ASCII ; Perhaps there is a bigger character set than ASCII. Because I am
>not sure if NONE will suffice.
>
>
>At 06:16 PM 11/11/2003 +1100, you wrote:
> >At 08:24 AM 11/11/2003 +0200, you wrote:
> > >I have the following problem. I am trying to convert my BDE application to
> > >a firebird application.
> >
> >"That does not compute". The BDE is not a DBMS and Firebird is not an
> >application platform.
> >
> >Do you mean you are converting your database from Paradox to Firebird?
> >
> > >So I created equal tables on the firebird side. I
> > >then send record for record over which then throws an error on the 182'th
> > >record. The recors is still inserted but it still pops the following error
> > >message :
> > >"
> > >'The SQL : INSERT INTO COUNTRY (Field1,Field2,Field3,Field4,Field5,Field6)
> > >VALUES(?,?,?,?,?,?) ; SQL Error : Arithmetic exception , numeric overflow
> > >or string truncation. Error Code: -802. Arithmetic overflow or division by
> > >zero has occured.'
> > >"
> > >
> > >Now I tried changing the connection protocol , deleting that specific
> > >record , my fields are all VARCHAR except for the primary key which is an
> > >integer. All the VARCHAR fields are either the same size or bigger.
> The BDE
> > >table contains no row where all the fields are NULL. Even if I run up to
> > >the 182'th record just before the insert and inspect the fields sent
> > >through they all contain values and it always throws the exception on the
> > >182'th record. Does anybody have more of a clue what is going on.
> >
> >I suspect that you recreated your Paradox indexes or keys in the new
> >Firebird tables, and that you are getting string overflows during the
> >creation of index nodes. Is this the case?
> >
> >A Firebird index has quite a low size limit - at most, 253 bytes. If you
> >have composite indexes, the limit gets smaller. If you are not using US
> >ASCII character set, the limit shrinks further. If there is a non-binary
> >collation sequence on any of those columns, the limit could be as small as
> >about 80 bytes.
> >
> >heLen
> >
> >
> >
> >
> >To unsubscribe from this group, send an email to:
> >firebird-support-unsubscribe@yahoogroups.com
> >
> >
> >
> >Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 4
> Date: Tue, 11 Nov 2003 10:09:14 +0200
> From: "Cavit Aladag" <aladag@...>
>Subject: Re: Re: Turkish character set problem
>
>Peter,
>Thank you for your reply. I had the test you had recommended. The result:
>I had inserted 4 rows with one Turkish character from command prompt isql.
>I cannot see turkish characters from isql (command prompt)
>I can see the characters from IBExpert and Delphi correctly.
>I still cannot get a correct order.
>
>What do you recommend now?
>Regards,
>Cavit Aladag
>Nicosia, Cyprus.
>
>
>[Non-text portions of this message have been removed]
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 5
> Date: Tue, 11 Nov 2003 19:21:06 +1100
> From: "Alan McDonald" <alan@...>
>Subject: RE: Re: SubSelect Question
>
>did you then run it by running the SP then the subselect query? i.e. change
>the order - did it make a difference?
>Interesting
>Alan
>
> > -----Original Message-----
> > From: Tom Frey [mailto:tom@...]
> > Sent: Tuesday, 11 November 2003 6:18 PM
> > To: firebird-support@yahoogroups.com
> > Subject: [firebird-support] Re: SubSelect Question
> >
> >
> > Your wish shall be fulfilled:
> >
> > I ran this Query:
> >
> > SELECT (SELECT avg(volume) FROM ABNTRADEDATA WHERE VOLUME >0), (SELECT
> > avg(price) FROM ABNTRADEDATA WHERE VOLUME >0), (SELECT avg(volume)
> > from ABNTRADEDATA WHERE VOLUME <0) FROM RDB$DATABASE;
> >
> > versus this stored procedure:
> >
> > CREATE PROCEDURE GET_STATS
> > RETURNS (
> > AVG_VOLUMEPOS INTEGER,
> > AVG_VOLUMENEG INTEGER,
> > AVG_PRICE DECIMAL (10, 3))
> > AS
> > BEGIN
> > SELECT AVG(VOLUME), AVG(PRICE) FROM ABNTRADEDATA WHERE VOLUME > 0 INTO
> > :AVG_VOLUMEPOS, :AVG_PRICE;
> > SELECT AVG(VOLUME) FROM ABNTRADEDATA WHERE VOLUME < 0 INTO :AVG_VOLUMENEG;
> > SUSPEND;
> >
> > END
> >
> > Result is that the storec procedure is almost twice as fast.
> >
> > First Log is from the Query, 2nd one from the stored procedure
> >
> > Test was run on Windows 2003, P4 2.8GHz, FirebirdSql 1.5RC7
> > Database contains 20,283 rows
> >
> > Query Time
> > ------------------------------------------------
> > Prepare : 15
> > Execute : 172
> > Avg fetch time: 172.00 ms
> >
> > Memory
> > ------------------------------------------------
> > Current: 1,099,248
> > Max : 1,196,224
> > Buffers: 2,048
> >
> > Operations
> > ------------------------------------------------
> > Read : 7,313
> > Writes : 4
> > Fetches: 136,397
> >
> > Plan:
> > ------------------------------------------------
> > PLAN (ABNTRADEDATA NATURAL)
> > PLAN (ABNTRADEDATA NATURAL)
> > PLAN (ABNTRADEDATA NATURAL)
> > PLAN (RDB$DATABASE NATURAL)
> >
> > Enchanced Info:
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | Table Name | Index | Non-Index | Updated | Deleted |
> > Inserted |
> > | | reads | reads | | |
> > |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | RDB$FIELDS| 13 | 0 | 0 | 0 |
> > 0 |
> > | RDB$INDEX_SEGMENTS| 1 | 0 | 0 | 0 |
> > 0 |
> > | RDB$RELATION_FIELDS| 13 | 0 | 0 | 0 |
> > 0 |
> > | RDB$RELATIONS| 2 | 0 | 0 | 0 |
> > 0 |
> > | RDB$RELATION_CONSTRAINTS| 1 | 0 | 0 | 0 |
> > 0 |
> > | RDB$DATABASE| 0 | 1 | 0 | 0 |
> > 0 |
> > | ABNTRADEDATA| 0 | 60,849 | 0 | 0 |
> > 0 |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> >
> >
> >
> >
> >
> > Query Time
> > ------------------------------------------------
> > Prepare : 15
> > Execute : 94
> > Avg fetch time: 94.00 ms
> >
> > Memory
> > ------------------------------------------------
> > Current: 1,065,624
> > Max : 1,196,224
> > Buffers: 2,048
> >
> > Operations
> > ------------------------------------------------
> > Read : 4,865
> > Writes : 4
> > Fetches: 90,887
> >
> > Plan:
> > ------------------------------------------------
> >
> > Enchanced Info:
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | Table Name | Index | Non-Index | Updated | Deleted |
> > Inserted |
> > | | reads | reads | | |
> > |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | RDB$PROCEDURE_PARAMETERS| 3 | 0 | 0 | 0 |
> > 0 |
> > | ABNTRADEDATA| 0 | 40,566 | 0 | 0 |
> > 0 |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> >
> > --- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
> > wrote:
> > > > A stored procedure.
> > > >
> > > > create procedure GetAverages
> > > > returns (
> > > > AvPriceForPos numeric(18,2),
> > > > AvVolPos numeric (18,2),
> > > > AvVolNeg numeric (18,2)
> > > > )
> > > > as
> > > > begin
> > > > select
> > > > avg(volume), avg(price) from abntradedata
> > > > where volume > 0
> > > > into :AvVolPos, :AvPriceForPos;
> > > > select avg(volume) from abntradedata
> > > > where volume < 0
> > > > into :AvVolNeg
> > > > end
> > > >
> > > > That's two queries but no subqueries to slow you down.
> > > >
> > >
> > > I wouldn't mind seeing a test result of this - I doubt that a
> > subquery would
> > > be any slower at all over this SP method
> > >
> > > Alan
> >
> >
> >
> > To unsubscribe from this group, send an email to:
> > firebird-support-unsubscribe@yahoogroups.com
> >
> >
> >
> > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
> >
> >
> >
> >
>
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 6
> Date: Tue, 11 Nov 2003 19:21:59 +1100
> From: Helen Borrie <helebor@...>
>Subject: Re: SQL Error
>
>At 10:07 AM 11/11/2003 +0200, you wrote:
> >Sorry about should have probably have said. That I use the BDE to access my
> >paradox tables. I have found the error. It occurred because of record 183
> >which had a field value of the following :"Réunion"
> >I hope you can see in this email that the 2nd letter is an e with a line on
> >it. When recreated my table which had previously the ASCII characters set
> >set to none it worked without problems. I am still not sure why ASCII wont
> >take the "é" Alt-0233. Used following statement : DEFAULT CHARACTER SET
> >ASCII ; Perhaps there is a bigger character set than ASCII. Because I am
> >not sure if NONE will suffice.
>
>No, NONE will not suffice if you are storing accented characters. US ASCII
>won't do either, because it's a subset that doesn't know about those
>characters. Possibly ISO8859_1 will do it for you. You really should have
>created the database with a default character set; and you would then need
>for your clients to connect to it using that character set as well.
>
>Why not post a question with the words "Character set" in the
>subject? Then one of the Europeans will notice it and give you advice
>about the best choice for the data you have to store.
>
>heLen
>
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 7
> Date: Tue, 11 Nov 2003 08:23:32 -0000
> From: "Tom Frey" <tom@...>
>Subject: Re: SubSelect Question
>
>I tried it back and forth several times. Didn't make any difference
>
>--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
>wrote:
> > did you then run it by running the SP then the subselect query? i.e.
>change
> > the order - did it make a difference?
> > Interesting
> > Alan
> >
> > > -----Original Message-----
> > > From: Tom Frey [mailto:tom@g...]
> > > Sent: Tuesday, 11 November 2003 6:18 PM
> > > To: firebird-support@yahoogroups.com
> > > Subject: [firebird-support] Re: SubSelect Question
> > >
> > >
> > > Your wish shall be fulfilled:
> > >
> > > I ran this Query:
> > >
> > > SELECT (SELECT avg(volume) FROM ABNTRADEDATA WHERE VOLUME >0), (SELECT
> > > avg(price) FROM ABNTRADEDATA WHERE VOLUME >0), (SELECT avg(volume)
> > > from ABNTRADEDATA WHERE VOLUME <0) FROM RDB$DATABASE;
> > >
> > > versus this stored procedure:
> > >
> > > CREATE PROCEDURE GET_STATS
> > > RETURNS (
> > > AVG_VOLUMEPOS INTEGER,
> > > AVG_VOLUMENEG INTEGER,
> > > AVG_PRICE DECIMAL (10, 3))
> > > AS
> > > BEGIN
> > > SELECT AVG(VOLUME), AVG(PRICE) FROM ABNTRADEDATA WHERE VOLUME > 0 INTO
> > > :AVG_VOLUMEPOS, :AVG_PRICE;
> > > SELECT AVG(VOLUME) FROM ABNTRADEDATA WHERE VOLUME < 0 INTO
>:AVG_VOLUMENEG;
> > > SUSPEND;
> > >
> > > END
> > >
> > > Result is that the storec procedure is almost twice as fast.
> > >
> > > First Log is from the Query, 2nd one from the stored procedure
> > >
> > > Test was run on Windows 2003, P4 2.8GHz, FirebirdSql 1.5RC7
> > > Database contains 20,283 rows
> > >
> > > Query Time
> > > ------------------------------------------------
> > > Prepare : 15
> > > Execute : 172
> > > Avg fetch time: 172.00 ms
> > >
> > > Memory
> > > ------------------------------------------------
> > > Current: 1,099,248
> > > Max : 1,196,224
> > > Buffers: 2,048
> > >
> > > Operations
> > > ------------------------------------------------
> > > Read : 7,313
> > > Writes : 4
> > > Fetches: 136,397
> > >
> > > Plan:
> > > ------------------------------------------------
> > > PLAN (ABNTRADEDATA NATURAL)
> > > PLAN (ABNTRADEDATA NATURAL)
> > > PLAN (ABNTRADEDATA NATURAL)
> > > PLAN (RDB$DATABASE NATURAL)
> > >
> > > Enchanced Info:
> > > +--------------------------+-------+-----------+---------+--------
> > > -+----------+
> > > | Table Name | Index | Non-Index | Updated | Deleted |
> > > Inserted |
> > > | | reads | reads | | |
> > > |
> > > +--------------------------+-------+-----------+---------+--------
> > > -+----------+
> > > | RDB$FIELDS| 13 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$INDEX_SEGMENTS| 1 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$RELATION_FIELDS| 13 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$RELATIONS| 2 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$RELATION_CONSTRAINTS| 1 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$DATABASE| 0 | 1 | 0 | 0 |
> > > 0 |
> > > | ABNTRADEDATA| 0 | 60,849 | 0 | 0 |
> > > 0 |
> > > +--------------------------+-------+-----------+---------+--------
> > > -+----------+
> > >
> > >
> > >
> > >
> > >
> > > Query Time
> > > ------------------------------------------------
> > > Prepare : 15
> > > Execute : 94
> > > Avg fetch time: 94.00 ms
> > >
> > > Memory
> > > ------------------------------------------------
> > > Current: 1,065,624
> > > Max : 1,196,224
> > > Buffers: 2,048
> > >
> > > Operations
> > > ------------------------------------------------
> > > Read : 4,865
> > > Writes : 4
> > > Fetches: 90,887
> > >
> > > Plan:
> > > ------------------------------------------------
> > >
> > > Enchanced Info:
> > > +--------------------------+-------+-----------+---------+--------
> > > -+----------+
> > > | Table Name | Index | Non-Index | Updated | Deleted |
> > > Inserted |
> > > | | reads | reads | | |
> > > |
> > > +--------------------------+-------+-----------+---------+--------
> > > -+----------+
> > > | RDB$PROCEDURE_PARAMETERS| 3 | 0 | 0 | 0 |
> > > 0 |
> > > | ABNTRADEDATA| 0 | 40,566 | 0 | 0 |
> > > 0 |
> > > +--------------------------+-------+-----------+---------+--------
> > > -+----------+
> > >
> > > --- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
> > > wrote:
> > > > > A stored procedure.
> > > > >
> > > > > create procedure GetAverages
> > > > > returns (
> > > > > AvPriceForPos numeric(18,2),
> > > > > AvVolPos numeric (18,2),
> > > > > AvVolNeg numeric (18,2)
> > > > > )
> > > > > as
> > > > > begin
> > > > > select
> > > > > avg(volume), avg(price) from abntradedata
> > > > > where volume > 0
> > > > > into :AvVolPos, :AvPriceForPos;
> > > > > select avg(volume) from abntradedata
> > > > > where volume < 0
> > > > > into :AvVolNeg
> > > > > end
> > > > >
> > > > > That's two queries but no subqueries to slow you down.
> > > > >
> > > >
> > > > I wouldn't mind seeing a test result of this - I doubt that a
> > > subquery would
> > > > be any slower at all over this SP method
> > > >
> > > > Alan
> > >
> > >
> > >
> > > To unsubscribe from this group, send an email to:
> > > firebird-support-unsubscribe@yahoogroups.com
> > >
> > >
> > >
> > > Your use of Yahoo! Groups is subject to
>http://docs.yahoo.com/info/terms/
> > >
> > >
> > >
> > >
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 8
> Date: Tue, 11 Nov 2003 19:30:55 +1100
> From: "Alan McDonald" <alan@...>
>Subject: RE: Re: SubSelect Question
>
> > I tried it back and forth several times. Didn't make any difference
> >
> > --- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
> > wrote:
> > > did you then run it by running the SP then the subselect query? i.e.
> > change
> > > the order - did it make a difference?
> > > Interesting
> > > Alan
> > >
>
>
>OK - there you go
>thanks
>ALan
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 9
> Date: Tue, 11 Nov 2003 19:32:57 +1100
> From: "Alan McDonald" <alan@...>
>Subject: RE: Re: SubSelect Question
>
> > Test was run on Windows 2003, P4 2.8GHz, FirebirdSql 1.5RC7
> > Database contains 20,283 rows
> >
> > Query Time
> > ------------------------------------------------
> > Prepare : 15
> > Execute : 172
> > Avg fetch time: 172.00 ms
> >
> > Memory
> > ------------------------------------------------
> > Current: 1,099,248
> > Max : 1,196,224
> > Buffers: 2,048
> >
> > Operations
> > ------------------------------------------------
> > Read : 7,313
> > Writes : 4
> > Fetches: 136,397
> >
> > Plan:
> > ------------------------------------------------
> > PLAN (ABNTRADEDATA NATURAL)
> > PLAN (ABNTRADEDATA NATURAL)
> > PLAN (ABNTRADEDATA NATURAL)
> > PLAN (RDB$DATABASE NATURAL)
> >
> > Enchanced Info:
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | Table Name | Index | Non-Index | Updated | Deleted |
> > Inserted |
> > | | reads | reads | | |
> > |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | RDB$FIELDS| 13 | 0 | 0 | 0 |
> > 0 |
> > | RDB$INDEX_SEGMENTS| 1 | 0 | 0 | 0 |
> > 0 |
> > | RDB$RELATION_FIELDS| 13 | 0 | 0 | 0 |
> > 0 |
> > | RDB$RELATIONS| 2 | 0 | 0 | 0 |
> > 0 |
> > | RDB$RELATION_CONSTRAINTS| 1 | 0 | 0 | 0 |
> > 0 |
> > | RDB$DATABASE| 0 | 1 | 0 | 0 |
> > 0 |
> > | ABNTRADEDATA| 0 | 60,849 | 0 | 0 |
> > 0 |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> >
> >
> >
> >
> >
> > Query Time
> > ------------------------------------------------
> > Prepare : 15
> > Execute : 94
> > Avg fetch time: 94.00 ms
> >
> > Memory
> > ------------------------------------------------
> > Current: 1,065,624
> > Max : 1,196,224
> > Buffers: 2,048
> >
> > Operations
> > ------------------------------------------------
> > Read : 4,865
> > Writes : 4
> > Fetches: 90,887
> >
> > Plan:
> > ------------------------------------------------
>
>
>What was the plan for the SP?
>Alan
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 10
> Date: Tue, 11 Nov 2003 08:40:06 -0000
> From: "Tom Frey" <tom@...>
>Subject: Re: SubSelect Question
>
>--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
>wrote:
> > > Test was run on Windows 2003, P4 2.8GHz, FirebirdSql 1.5RC7
> > > Database contains 20,283 rows
> > >
> > > Query Time
> > > ------------------------------------------------
> > > Prepare : 15
> > > Execute : 172
> > > Avg fetch time: 172.00 ms
> > >
> > > Memory
> > > ------------------------------------------------
> > > Current: 1,099,248
> > > Max : 1,196,224
> > > Buffers: 2,048
> > >
> > > Operations
> > > ------------------------------------------------
> > > Read : 7,313
> > > Writes : 4
> > > Fetches: 136,397
> > >
> > > Plan:
> > > ------------------------------------------------
> > > PLAN (ABNTRADEDATA NATURAL)
> > > PLAN (ABNTRADEDATA NATURAL)
> > > PLAN (ABNTRADEDATA NATURAL)
> > > PLAN (RDB$DATABASE NATURAL)
> > >
> > > Enchanced Info:
> > > +--------------------------+-------+-----------+---------+--------
> > > -+----------+
> > > | Table Name | Index | Non-Index | Updated | Deleted |
> > > Inserted |
> > > | | reads | reads | | |
> > > |
> > > +--------------------------+-------+-----------+---------+--------
> > > -+----------+
> > > | RDB$FIELDS| 13 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$INDEX_SEGMENTS| 1 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$RELATION_FIELDS| 13 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$RELATIONS| 2 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$RELATION_CONSTRAINTS| 1 | 0 | 0 | 0 |
> > > 0 |
> > > | RDB$DATABASE| 0 | 1 | 0 | 0 |
> > > 0 |
> > > | ABNTRADEDATA| 0 | 60,849 | 0 | 0 |
> > > 0 |
> > > +--------------------------+-------+-----------+---------+--------
> > > -+----------+
> > >
> > >
> > >
> > >
> > >
> > > Query Time
> > > ------------------------------------------------
> > > Prepare : 15
> > > Execute : 94
> > > Avg fetch time: 94.00 ms
> > >
> > > Memory
> > > ------------------------------------------------
> > > Current: 1,065,624
> > > Max : 1,196,224
> > > Buffers: 2,048
> > >
> > > Operations
> > > ------------------------------------------------
> > > Read : 4,865
> > > Writes : 4
> > > Fetches: 90,887
> > >
> > > Plan:
> > > ------------------------------------------------
> >
> >
> > What was the plan for the SP?
>
>hmmmm ... it is not available
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 11
> Date: Tue, 11 Nov 2003 11:37:49 +0300
> From: Serg Vostrikov <sv@...>
>Subject: Re: Re: Zeos Library?
>
>Dear robert_hollay,
>
>Thursday, November 6, 2003, 1:15:09 PM, you wrote:
>
>r> It compares FIBPlus, IBO, IBX and Zeos in speed, memory usage etc.
>r> Of course, you must have in your mind that this is not an impartial
>r> test app from an independent tester. :).
>No, you must have in your mind that this application comes with
>sources and you can set your own options, etc :)
>
>--
>Sincerely yours,
> Serg
>
>Devrace - software for developers!
>__________________________________
>e-mail: sv@...
>url : http://www.devrace.com/, http://www.devrace.de/
> http://www.fibplus.net/, http://www.athlant.com/
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 12
> Date: Tue, 11 Nov 2003 08:47:33 -0000
> From: "peter_jacobi.rm" <peter_jacobi@...>
>Subject: Re: Turkish character set problem
>
>Hi Cavit!
>
>Did you find out which codepage
>is used by normal console window
>(by entering the command 'chcp')?
>
>If that codepage is not 1254, did you
>change it to 1254 by 'chcp 1254'?
>
>Peter
>
>
>--- In firebird-support@yahoogroups.com, "Cavit Aladag" <aladag@a...>
> > Thank you for your reply. I had the test you had recommended. The
>result:
> > I had inserted 4 rows with one Turkish character from command prompt
>isql.
> > I cannot see turkish characters from isql (command prompt)
> > I can see the characters from IBExpert and Delphi correctly.
> > I still cannot get a correct order.
>
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 13
> Date: Tue, 11 Nov 2003 08:48:45 -0000
> From: "Svein Erling" <svein.erling.tysvaer@...>
>Subject: Re: Generators Associated with a Table
>
>It is quite common to have triggers firing and inserting the PK
>(through a generator) when the column is NULL. If the table is
>designed this way, you could simply drop the PK column from your
>insert, things will get updated and the trigger used.
>
>Set
>- I support Firebird, I am a FirebirdSQL Foundation member.
>- Join today at http://www.firebirdsql.org/ff/foundation
>
>--- In firebird-support@yahoogroups.com, Lee Jenkins wrote:
> > Thomas Steinmaurer wrote:
> >
> > > Lee,
> > >
> > >>Is there a way to query the system tables to retrieve the
> > >>generator (if any) that is being used by that table?
> > >
> > > You probably use a generator to increment the value of
> > > a primary key column for your table, but the generator
> > > doesn't stand in any relationship to a table.
> > >
> > > That means, you can create a generator without having
> > > any table in your database.
> >
> > So no way to figure out which generators, if any, a table is using.
> > Makes sense, given the nature of generators. Hmmm. Maybe I could
> > just use a separate table and store the values of the generator
> > names there.
> >
> > I'm thinking about building an import utility that will bulk process
> > INSERTS into a DB but don't want to get in trouble with the
> > generators lagging behind cause PK violations.
> >
> > Lee
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 14
> Date: Tue, 11 Nov 2003 09:05:35 -0000
> From: "Svein Erling" <svein.erling.tysvaer@...>
>Subject: Re: and again... SQL help needed
>
>Hi Yves!
>
>--- In firebird-support@yahoogroups.com, Yves Glodt <yg@m...> wrote:
> > Hello,
> >
> > please consider this query:
> >
> > SELECT
> > WT_CORR.EFFECT_DATE,
> > WT_CORR.CORR_TYPE,
> > WT_CORR.CORR_STATE,
> > WT_CORR.CORR_INFO_1,
> > WT_CORR.CORR_INFO_2,
> > WT_CORR.CORR_INFO_3,
> > WT_CORR.TYPE_CORR,
> > WT_CORR.CREATOR,
> > WT_NOMS.NAME_NAME,
> > WT_MOTIFS.REASON_COLOUR,
> > WT_MOTIFS.REASON_TYPE
> > FROM WT_CORR,WT_NOMS,WT_MOTIFS
> > WHERE WT_CORR.IPN=31 AND
> > WT_CORR.EFFECT_DATE >= $start AND WT_CORR.EFFECT_DATE <= $end
>AND
> > WT_NOMS.NAME_TYPE=1400 AND
> > (WT_CORR.CORR_TYPE = WT_NOMS NAME_NUMBER) AND
> > (WT_CORR.CORR_TYPE = WT_NOMS.REASON_NUMBER)
> > ORDER BY WT_CORR.EFFECT_DATE
> >
> >
> > This is a rough draft, but it works well.
> > The 9th column it returns is the textual name of the numeric value
>from
> > column 2. Names are stored in the WT_NOMS table, and in this query
>are
> > linked through the section number "WT_NOMS.NAME_TYPE = 1400"
> >
> >
> > But I need to get another name from the WT_NOMS table, in a section
>that
> > I get in the 10th column, which of course will vary for each
>returned
> > row...
> >
> > Is there a way to use values returned in a row in a WHERE clause in
>the
> > same query, like "where WT_NOMS.NAME_TYPE=$(value_from_column #10)"
> > I was thinkin about a subquery, but I would also need a former
>result in
> > the WHERE.
> >
> > Is this possible at all with bare sql?
>
>Probably, the reason that you haven't got any reply may be that we do
>not quite understand what you want to do.
>
>Try something like:
>
>SELECT
>WT_CORR.EFFECT_DATE,
>WT_CORR.CORR_TYPE,
>WT_CORR.CORR_STATE,
>WT_CORR.CORR_INFO_1,
>WT_CORR.CORR_INFO_2,
>WT_CORR.CORR_INFO_3,
>WT_CORR.TYPE_CORR,
>WT_CORR.CREATOR,
>WN1.NAME_NAME,
>WT_MOTIFS.REASON_COLOUR,
>WN2.NAME_NAME as COLOURNAME
>WT_MOTIFS.REASON_TYPE
>FROM WT_CORR
>JOIN WT_NOMS WN1 ON
> WN1.NAME_NUMBER = WT_CORR.CORR_TYPE
>JOIN WT_NOMS WN2 ON
> WN2.NAME_NUMBER = WT_MOTIFS.REASON_COLOUR
>JOIN WT_MOTIFS ON
> WT_CORR.CORR_TYPE = WT_MOTIFS.REASON_NUMBER
>WHERE
> WT_CORR.IPN=31 AND
> WT_CORR.EFFECT_DATE >= $start AND WT_CORR.EFFECT_DATE <= $end AND
> WN1.NAME_TYPE=1400 AND
> WN2.NAME_TYPE=1400 /*I don't know whether you want this
>ORDER BY WT_CORR.EFFECT_DATE
>
>As you see, I've also rewritten your query from SQL-89 to SQL-92 (i.e.
>used JOIN). This query should work whether or not you do that, but in
>general it is preferable to use JOIN.
>
>HTH,
>Set
>
>- I support Firebird, I am a FirebirdSQL Foundation member.
>- Join today at http://www.firebirdsql.org/ff/foundation
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 15
> Date: Tue, 11 Nov 2003 09:18:42 -0000
> From: "Svein Erling" <svein.erling.tysvaer@...>
>Subject: Re: SubSelect Question
>
>It shouldn't be a big surprise that it is slower, after all the stored
>procedure gets two values in one select, whereas the query needs two
>subselects to do the same thing. I am a bit surprised that the
>difference is that big, though, especially since it does not use any
>index.
>
> > Query:
> > stored procedure:
> > Query Time
> > ------------------------------------------------
> > Prepare : 15
> > Execute : 172
> > Avg fetch time: 172.00 ms
> >
> > Query Time
> > ------------------------------------------------
> > Prepare : 15
> > Execute : 94
> > Avg fetch time: 94.00 ms
>
>Set
>- I support Firebird, I am a FirebirdSQL Foundation member.
>- Join today at http://www.firebirdsql.org/ff/foundation
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 16
> Date: Tue, 11 Nov 2003 09:50:31 -0000
> From: "Alexander V.Nevsky" <ded@...>
>Subject: Re: SQL ERROR Code = -104 user name required
>
>--- In firebird-support@yahoogroups.com, Criptyk Hayz <kinghayz@s...>
>wrote:
> > SQL ERROR Code = -104 user name required on this statement:
> >
> > select s.pick, s.style, s.color, s.fgsize, sum(s.quantity)
> > from pick_summary s
> > where s.pick = '0187'
> > group by s.pick, s.style, s.color, s.fgsize
> > order by s.pick, s.style, s.color, s.sizesubscript
> >
> > Any ideas?
>
> You should install proper client software version including
>firebird.msg and you'll see real exception message. You can't order by
>column not included in group by clause.
>
>Best regards,
>Alexander
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 17
> Date: Tue, 11 Nov 2003 11:45:50 +0200
> From: "Daniel" <dp@...>
>Subject: Re: Please Help: Function unknown upper??
>
>Hi David
>
>Thank you very much for looking into this. We are using utility classes to
>connect efficiently to the database. Your reply encouraged me to bypass
>these classes to be able to copy and paste something understandable into
>the reply - and wow it works. The actual problem was that around function
>names one of the utility classes put double quotes. This should happened
>only on table and field names.
>
>For other Java users the main method of my test class might be helpful:
>
> public static void main(String[] args){
> Connection conn;
> String url =
> "jdbc:firebirdsql:localhost/3050:c:/backoffice/db/OFFICE.DB";
> DriverManager.setLoginTimeout(10);
>
> try{
> Class.forName("org.firebirdsql.jdbc.FBDriver");
> conn = DriverManager.getConnection(url, "SYSDBA", "Y83rUopA");
>
> String query = "select \"Name\", \"FirstName\" from \"Party\"
> where upper(\"Name\") like 'SANT%'";
> CallableStatement cs = conn.prepareCall(query);
> ResultSet rs = cs.executeQuery();
>
> while(rs.next()){
> System.out.println("Name: " + rs.getString(1) + " First
> Name: " + rs.getString(2));
> }
> }catch(SQLException exc){
> System.out.println("TestDatabaseConnection >> exc.getSQLState()
> = " + exc.getSQLState());
> exc.printStackTrace();
> }catch(Exception sqex){
> sqex.printStackTrace();
> }finally{
> System.out.println("...Testing Done");
> System.exit(0);
> }
> }
>
>
>Problem is solved, thank you very much and regards
>Daniel
> ----- Original Message -----
> From: dhay@...
> To: firebird-support@yahoogroups.com
> Sent: Monday, November 10, 2003 9:12 PM
> Subject: Re: [firebird-support] Please Help: Function unknown upper??
>
>
>
> Errr...not sure what the problem is because I always use the upper function
> through JDBC with no problems.
>
> What sql statements are you using? Can you use isql or another tool and
> execute it okay?
>
> cheers,
>
> David
>
>
>
>
> "Daniel" <dp@...> on 10/11/2003 12:38:45 PM
>
> Please respond to firebird-support@yahoogroups.com
>
> To: "Mailing List: firebird-support" <firebird-support@yahoogroups.com>
> cc:
> Subject: [firebird-support] Please Help: Function unknown upper??
>
>
> Hi All
>
> I urgently need to get functions working in Firebird. If this does not work
> soon I have to look for another database - although I started really liking
> Firebird. This might fit more into the Java Mailing List but there is
> definitely something wrong with that list. All 4 emails I posted (even
> through the Yahoo Web site directly) did not appear. Also Helen's promise 4
> days ago that it just takes time can not be true because 4 days is just too
> long and they are still not there.
>
> Where can I start looking into the problem? I am using Win2K with JayBird
> JCA/JDBC Driver version 1.0.1., JRE 1.3.1 and Firebird 1.5 RC6.
> I tried different folders, setting the path to all relevant folders. No
> matter what but functions just do not work via JDBC. Does anybody know
> where
> to get the source code for JayBird, which might provide some answers. It
> seams not to be available via the usual channels.
>
> I am quite frustrated and I highly appreciate any hint.
>
> Regards
> Daniel
>
>
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>[Non-text portions of this message have been removed]
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 18
> Date: Tue, 11 Nov 2003 12:03:13 +0200
> From: "Cavit Aladag" <aladag@...>
>Subject: Re: Re: Turkish character set problem
>
>Peter,
>
>Yes I checked the codepage it was 857 and then changed it to 1254. then in
>isql 'set names WIN1254'.
>
>Cavit.
>
>[Non-text portions of this message have been removed]
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 19
> Date: Tue, 11 Nov 2003 10:07:40 -0000
> From: "Alexander V.Nevsky" <ded@...>
>Subject: Re: I am a new member
>
>--- In firebird-support@yahoogroups.com, Carlos J Bracho M
><carlos_bracho_1@u...> wrote:
> > Hi friends...
> > I just enjoied this group because I am working in an application
>which uses
> > FireBird like DBMS.
> > I read some pdf and they really helped me out.
> > But now I have some questions for you:
> >
> > 1. How can I know the current user? I mean the user who is logged
>into the data
> > base... I have know the username of the current user, how can I know
>it?
>
> If I understand you right and you want to know in stored procedure
>or trigger who called them, read this, if not - read Alan's post.
>There are system variables CURRENT_USER (or simply USER) and
>CURRENT_ROLE. In PSQL (stored procedures and triggers language) you
>can access them directly like
>
>If (CURRENT_USER='CARLOS') then ...
>
>You can include this variables into your client application queries
>too, like
>
>Select Column1, Column2, Current_User
> >From MyTable
>
>and use as default value in table columns like
>
>Create MyTable
>(ID Int Not Null,
> Who_Created_Row VarChar(128) Default Current_User)
>
> > 2. When I connect into the database I have to write the role of the
>user who
> > will log in. "connect data.fdb user yahoo password yahoo role XXX".
>How can I
> > know the user's role without log into the database like sysdba. I
>mean, I do
> > not the role of the user who will log in.
>
> Not sure I understand you. You is'nt DUE to specify role, you CAN
>use roles if you want. If so, you should know what do you want and
>specify appropriate role. Anyway, system tables are accessible for
>reading for any user and you can connect without role and examine
>RDB$ROLES and RDB$USER_PRIVILEGES for roles granted to user.
>
> > 3. How can I know the system date-time?
>
> Answered by Alan.
>
>Best regards,
>Alexander.
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 20
> Date: Tue, 11 Nov 2003 21:13:45 +1100
> From: "Alan McDonald" <alan@...>
>Subject: RE: Re: SubSelect Question
>
> > > What was the plan for the SP?
> >
> > hmmmm ... it is not available
>
>Spose that would be right since it comes out of an SP..
>You'd need to make each select separately and see the plan for each
>Alan
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 21
> Date: Tue, 11 Nov 2003 10:47:36 -0000
> From: "peter_jacobi.rm" <peter_jacobi@...>
>Subject: Re: Turkish character set problem
>
>Hi Cavit,
>
>Find a test script at
>http://groups.yahoo.com/group/firebird-support/files/testcases/
>
>Download and unzip.
>
>Set your console to codepage 1254. Set the buffer length
>long enough (1000) to catch all output.
>
>Display the unpacked SQL script with 'type' to
>verify display of Turkish character set. Dotless
>small i at 253 is an easy marker.
>
>Create a fresh database and edit the t1254.sql script
>to match database name, user and password with your
>actual settings.
>
>Execute the script with:
>isql -page 999 -e -i t1254.sql
>
>At my system (this is W2K, BTW), this gives
>the right result.
>
>Regards,
>Peter Jacobi
>
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 22
> Date: Tue, 11 Nov 2003 13:10:25 +0200
> From: Uwe Oeder <uweo@...>
>Subject: Character Sets
>
>Which character set can somebody recommend for this "é" type of characters.
>What about UNICODE ? What happens if I choose a multiple byte character
>set. Would that not mean that if I have a Field of VARCHAR with 100 length
>then the actual bytes stored would be amount of multiple bytes * length of
>VARCHAR field ?
>
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>Message: 23
> Date: Tue, 11 Nov 2003 12:32:13 +0100
> From: "Henrik Sitter" <henrik.sitter@...>
>Subject: RE: Character Sets
>
>Hi,
>
>I use ISO8859_1.
>
>Henrik
>
>-----Original Message-----
>From: Uwe Oeder [mailto:uweo@...]
>Sent: 11. november 2003 12:10
>To: firebird-support@yahoogroups.com
>Subject: [firebird-support] Character Sets
>
>Which character set can somebody recommend for this "é" type of
>characters.
>What about UNICODE ? What happens if I choose a multiple byte character
>set. Would that not mean that if I have a Field of VARCHAR with 100
>length
>then the actual bytes stored would be amount of multiple bytes * length
>of
>VARCHAR field ?
>
>
>
>
>To unsubscribe from this group, send an email to:
>firebird-support-unsubscribe@yahoogroups.com
>
>
>
>Your use of Yahoo! Groups is subject to
>http://docs.yahoo.com/info/terms/
>
>
>
>
>
>
>
>________________________________________________________________________
>________________________________________________________________________
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/