Re: [firebird-support] What key word specifies a search for an entire word?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 12/2/19 5:06 AM, Tjioe Hian Pin thp_p...@yahoo.com [firebird-support]
wrote:
>  
> what about:
>
> SELECT fstName, fiKeyID FROM Members WHERE ' '||fstName||' '
> containing ' '||@po||' '
>
> Cheers,
> Tjioe
>
Again, that is likely prevent use of an index to find the record, and
force a table scan. Why not just use WHERE fstName = @po

-- 
Richard Damon

Re: [firebird-support] What key word specifies a search for an entire word?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 11/29/19 4:32 PM, Clyde Eisenbeis cte...@gmail.com [firebird-support]
wrote:
>  
> Not sure what pattern is.  If I am searching for two words I use OR or
> AND. 
>
> "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE lower(
> @p0 ) AND lower(fstName) LIKE lower( @p1 ) "
>
> I prefer the word LIKE.  Is easier to avoid errors vs. using '='.  Are
> there advantages to using '='?
>
LIKE, because it is designed as a partial match, can't use a normal
index, so often ends up doing a table scan.

= since it only matches the full field, can easily use an index, and if
an appropriate index is available, can avoid scanning the whole table.


Also, in your above sample, fstName needs to match BOTH p0 and p1, which
isn't usual unless you are looking for the presence of both of two
strings in any order.

-- 
Richard Damon

Re: [firebird-support] What key word specifies a search for an entire word?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 11/28/19 11:30 AM, cte...@gmail.com [firebird-support] wrote:
>  
> I have this:
>
> "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE
> lower('%' || @p0 || '%')"
>
> Assume fstName includes "Richard" and "Rich".  If I search for "Rich"
> it finds both names. 
>
> What do I modify so it finds only "Rich"?

if you only want the case that fstName is exactly Rich, then don't use
like but use equals (=)

If you want Rich as part of the string but not Richard, what is allowed
before/after Rich that makes it a 'word'?

-- 
Richard Damon

Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 11/13/19 9:51 AM, blackfalconsoftw...@outlook.com [firebird-support]
wrote:
>  
> Thank you Anne, for your succinct comments regarding the differences
> between the efficiencies of a CHAR and VARCHAR field definitions.
>
> Human observation cannot actually elicit any observable effects
> between the two. 
>
> However, internally, the CHAR field definition is more efficient as
> the database does not have to perform any field size calculations at
> the moment that data is being updated to a VARCHAR field.  Again, this
> is not noticeable to a user unless the data that is being updated into
> a VARCHAR field is quite large.
>
> Steve Naidamast
> Sr. Software Engineer

By the SQL standard, there are some noticeable effects. CHAR is defined
as a fixed width, so data is padded with blanks to reach that width, and
then the blanks are removed on retrieval. Because of this, a CHAR field
can not store a value with trailing blanks. On the other hand, since
VARCHAR doesn't need to pad the field, it doesn't need to trim trailing
blanks, so the field CAN store data with trailing blanks.

Firebird might not implement this distinction since it doesn't need to
pad CHAR fields.

-- 
Richard Damon

Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 11/12/19 12:38 PM, cte...@gmail.com [firebird-support] wrote:
>  
>
> What are the trade-offs of CHAR vs. VARCHAR?  I know that VARCHAR
> consumes less space.  Anything thing else (are VARCHAR searches slower)?
>
In some implementations of SQL (I don't know if firebird is one of
them), a row without any variable length items (like VARCHAR) and thus
of fixed length could be stored in a somewhat optimized way making its
access somewhat faster because all the records were the same size.

VARCHAR also doesn't always take less space, as very short CHAR fields
can be smaller than the overhead of a VARCHAR, and if the CHAR field is
storing a value that is always the same length (like maybe a hash code)
the overhead of VARCHAR is just wasted.

-- 
Richard Damon

Re: [firebird-support] Basic Re-Start

[Richard Damon rich...@damon-family.org [firebird-support]]

On 9/28/19 8:52 AM, Clyde Eisenbeis cte...@gmail.com [firebird-support]
wrote:
>  
> I'll be using Firebird on my personal laptop. My program (that uses
> that database) will be used often
> every day.
>
> I'm puzzled by some of the questions as I have assumed Firebird is a
> dormant database file that my program accesses.  It sounds like
> Firebird is a program that needs to run to work.
>
Firebird is a Database Client/Server application. You program is linked
to the client side, which talks to the server side (and the server side
works the actually database file). This is the way many Database systems
work, and has advantages in that the server side has the ability to
enforce certain sharing and access rules on the database. It does mean
you need to start the 'server' app to access the database.

There are other database systems where the client library directly
accesses the database file(s), SQLite works that way, Microsoft Access
can work that way, the ancient xBase worked that way. These databases
can be converted into a Client/Server system by wrapping them with a
Client/Server wrapper layer, but tend not to provide a lot of access
control, since if the user application is directly accessing the file,
there isn't much the database system can do to 'protect' itself.

-- 
Richard Damon

Re: [firebird-support] Re: Is this a bug in ROUND function?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 8/29/19 3:14 PM, 'Alan McDonald' a...@meta.com.au [firebird-support]
wrote:
>  
>
> On 8/29/19 4:45 AM, m.djo...@gmail.com [firebird-support] wrote:
> >
> > In binary representation of the values maybe 4.72 is the closest, but
> > we are talking about mathematic.
> > This is from the description of the ROUND function in the documentation:
> > Rounds a number to the nearest integer. If the fractional part is
> > exactly 0.5, rounding is upward for positive numbers and downward for
> > negative numbers.
> > So .725 is all cases is rounded up to .73 as it should be, but in one
> > case it is not.
> >
> The issue is that when you write: cast(2.725 as double precision) you
> aren't dealing with 'mathematics' anymore, or even the value 2.725
>
> The value that you get at that point will be the nearest value to
> 2.725 that is expressible as a double precision floating point number
> which will be slightly different since 2.725 is NOT exactly
> representable as double precision floating point number. The number
> you get is allowed to be either the representable value just below or
> just above, the value, though the preference is the closer one. If the
> number you get is something like 2.72499...xx then round needs to
> round down, if you get something like 2.72500..xx then round needs to
> round up.
>
> If the numbers you had WERE exactly representable, like 2.625 (21/8)
> then the rules on how to round would matter, but since the number you
> have, after being made representable, isn't exactly 0.5 in the
> fractional part, that clause doesn't apply.
>
> --
> Richard Damon
>
> What about:
> select
> round(cast(0.725 as double precision), 2),
> round(cast(1.725 as double precision), 2),
> round(cast(2.725 as double precision), 2),
> round(cast(3.725 as double precision), 2),
> round(cast(4.725 as numeric(18,3)), 2),
> round(cast(5.725 as double precision), 2),
> round(cast(6.725 as double precision), 2),
> round(cast(7.725 as double precision), 2),
> round(cast(8.725 as double precision), 2),
> round(cast(9.725 as double precision), 2),
> round(cast(10.725 as double precision), 2) from
> rdb$database
>
Since numeric is defined by internally scaling up by the power of 10
specified, it should work. (I would probably try to be consistent and
make ALL of them use numeric.)

One question is to make sure that round understands numeric types (I
would think it would).

numeric(18, 3) will use a double for its internal representation, but I
would think it should still work. It might make more sense to use a
width of 9 or less, unless elsewhere you are actually using bigger numbers.

Note that unless you started with numeric, if the value was actually
stored in the database or somehow computed, one danger with this method
is that (assuming casting to numeric rounds) this will cause a double
rounding causing values above x.xx45 to round up.

-- 
Richard Damon

Re: [firebird-support] Re: Is this a bug in ROUND function?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 8/29/19 4:45 AM, m.djo...@gmail.com [firebird-support] wrote:
>  
> In binary representation of the values maybe 4.72 is the closest, but
> we are talking about mathematic.
> This is from the description of the ROUND function in the documentation:
> Rounds a number to the nearest integer. If the fractional part is
> exactly 0.5, rounding is upward for positive numbers and downward for
> negative numbers.
> So .725 is all cases is rounded up to .73 as it should be, but in one
> case it is not.
>
The issue is that when you write: cast(2.725 as double precision) you
aren't dealing with 'mathematics' anymore, or even the value 2.725

The value that you get at that point will be the nearest value to 2.725
that is expressible as a double precision floating point number which
will be slightly different since 2.725 is NOT exactly representable as
double precision floating point number. The number you get is allowed to
be either the representable value just below or just above, the value,
though the preference is the closer one. If the number you get is
something like 2.72499...xx then round needs to round down, if you get
something like 2.72500..xx then round needs to round up.

If the numbers you had WERE exactly representable, like 2.625 (21/8)
then the rules on how to round would matter, but since the number you
have, after being made representable, isn't exactly 0.5 in the
fractional part, that clause doesn't apply.


-- 
Richard Damon

Re: [firebird-support] SQL

[Richard Damon rich...@damon-family.org [firebird-support]]

On 8/5/19 4:20 AM, mohamed hamza medh...@hotmail.com [firebird-support]
wrote:
>  
>
> Hi All,
>
>   I am new to sql,  I am coming  from  Xbase  system .
>  I want to know if it 's  possible to execute query using last query
> result?
>
> EXAMPLE
>   
> 1  select * from persons where state='CA'
>
> 2 select * from persons where age> 18       but  we  have to use the
> result of 1
>
> I DO NOT WANT TO DO AN AND        (  STATE = 'CA'    AND   AGE > 18  ) ;
>
> Best Regards

Maybe something like:

SELECT * FROM ( SELECT * FROM persons where state = 'CA') where age > 18;

But using AND will generally be faster.

As far as I know, you can't just reference the previous query, unless
you explicitly put that result into a temporary table.

-- 
Richard Damon

Re: [firebird-support] Is this a bug in ROUND function?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 8/19/19 4:46 AM, m.djo...@gmail.com [firebird-support] wrote:
>  
>
> The following query shows that ROUND function does not give similar
> results for similar values:
>
>
> select
>     round(cast(0.725 as double precision), 2),
>     round(cast(1.725 as double precision), 2),
>     round(cast(2.725 as double precision), 2),
>     round(cast(3.725 as double precision), 2),
>     round(cast(4.725 as double precision), 2),
>     round(cast(5.725 as double precision), 2),
>     round(cast(6.725 as double precision), 2),
>     round(cast(7.725 as double precision), 2),
>     round(cast(8.725 as double precision), 2),
>     round(cast(9.725 as double precision), 2),
>     round(cast(10.725 as double precision), 2)
> from
>     rdb$database
>
>
> All statements give result X.73, only 4.725 gives 4.72
>
>
> I know that floating point values have their speciality, but still the
> function should work the same way for all values.
>
> The query is run with IBExpert, but I don't think it's the problem in
> that case.
>
>
> What do you think Should I report it as a bug?
>
X.725 has no exact binary representation, so each of the number will
actually be something slightly different. If the 'closest' represented
value is greater than the specified number, it will round up to

X.73, if the 'closest' value is less, than you will get X.72. (And
standard precision rules don't actually require getting the absolutly
closest value, normally both the value just less or just greater are
considered 'good enough'.

Therefore, this really isn't a bug.

-- 
Richard Damon

Re: [firebird-support] Re: wrong ordering in WITH RECURSIVE query

[Richard Damon rich...@damon-family.org [firebird-support]]

On Apr 16, 2019, at 4:40 AM, respekt...@post.cz [firebird-support] 
 wrote:
> 
> Hi, thank you for reply
> 
> But this ordering totaly damage the tree structure
> 
> Because PORADI is only relative to parent node, so all child nodes starting 
> with 1
> 
> If I change the query as you recommend, the set is sorted in way
> 1. all first nodes
> 2. all second nodes
> 3. all third nodes
> 
> 
> Any other advices??
> 
> Thank you,
> Tomas
It sounds like you are forgetting that in SQL, the order of rows is considered 
unimportant unless there is an explicit order specified AT THAT QUERY (and not 
in a sub-query used to build that query). That is the nature of the language. 
If you want the rows in a particular order, then you need to generate something 
that give it that order.

Any order outside that rule is just a coincidence.

For instance, a sort code for the 3rd node below the 2nd node below node 1 
could be 1.0203 (assuming that no node has more than 99 children). Or, if no 
node has more than 26 children, something like ABC

Re: [firebird-support] Silly selection problem ...

[Richard Damon rich...@damon-family.org [firebird-support]]

On 3/2/19 11:06 AM, Lester Caine les...@lsces.co.uk [firebird-support]
wrote:
>  
>
> I think I will probably have to add some extra logic into this problem,
> but it SHOULD be simple enough ...
>
> Table with three fields - Gallery_No, Image_No, Item_position
>
> A gallery can consist of any number of images but we normally only
> display a selection of up to 30. Item_position is a FLOAT which allows
> several styles ordering, with one style 'page.pos' so one can group
> pictures to a variable length page. The bottom line here is that
> 'Item_position' is not sequential ... and that is the rub. I now need to
> identify the record before and the record after for a library system
> where each gallery is a collection of pdf articles and the client would
> like to be able to navigate back and forward through each volume.
>
> In the past ( 20 years ago ;) ) I would simply have added a second
> position column which is consecutive numbers and reordered them when
> changes are made, but I'm wondering if with all the things that have
> been added since then has provided an SQL means of returning the before
> and after value directly from the current data?
>
> -- 
> Lester Caine - G8HFL
>
I would think one way to get the previous item from a give one would be
a select on items with Item_position less than the current
Item_position, in decreasing order, with a limit of 1 item (and the
reverse for the one after it). If there is an index on Item_position,
that should be a fairly efficient query.

-- 
Richard Damon

Re: [firebird-support] embedded database with Chinese path

[Richard Damon rich...@damon-family.org [firebird-support]]

On 10/4/17 11:53 PM, Hamish Moffatt ham...@risingsoftware.com 
[firebird-support] wrote:
>
> On 04/10/17 22:15, DougC d...@moosemail.net [firebird-support] wrote:
>> Hamish-
>>
>> Windows short path names are decidedly NOT for apps that cannot 
>> handle Unicode. They were introduced far earlier than that and were 
>> intended for programs that could not handle anything but the short 
>> 8.3 limits for any given file or folder name. That they often help 
>> with avoiding unicode is a side effect.
>>
>> Sound like your misunderstanding of this may be contributing to your 
>> frustration.
>
> Doug,
>
> I'm aware that short filenames are a work around for applications 
> which can't support long filenames (which date from Windows 95). They 
> also seem to nicely work around applications which are too dumb to 
> support valid Windows 16-bit filenames, like Firebird. I don't agree 
> that it's just a side effect that they avoid unicode, since I had 
> unicode parts that fit in the 8.3 short filename format.
>
> I realise that Firebird needs to use either long or short names 
> consistently so as to avoid inadvertently opening the same file by 
> different names (with locking issues and the like), but the solution 
> is to use only short names or handle long names properly.
>
> Hamish
>
One thing to note, the Short File Names (SFN) can't use 'Unicode' 
characters, only the Extended Ascii character set of the current code 
page (so it may seem you can put some unicode in them). SFNs can't be 
UTF-8. Note, that this also means that changing the system code page 
might change the apparent name of a file that used the extended 
character set, even the separator might change (on the Japanese code 
page, that is the Yen symbol).

The big selling point of Long File Names (LFN) was that they could be 
mostly arbitrary phrases in the users own language, breaking BOTH the 8 
character limit AND the 8 bit char restriction (using 16 bit wchar to 
encode them). There is no such beast as a 8 bit only LFN, or a 16 bit 
(Unicode) encoded SFN.

Every file has a SFN just so that applications (like it appears that 
Firebird is) that don't support LFNs can still access the files.

-- 
Richard Damon

Re: [firebird-support] embedded database with Chinese path

[Richard Damon rich...@damon-family.org [firebird-support]]

Short File names are to allow apps that can't support long file names 
(LFN) to access a file. Long filename support includes wchar encoding. 
If Firebird can't support  wchar filenames, then it should NOT convert a 
short filename to its LFN equivalent (unless there is some other very 
important reason it needs the LFN).


On 10/4/17 7:15 AM, DougC d...@moosemail.net [firebird-support] wrote:
> Hamish-
>
> Windows short path names are decidedly NOT for apps that cannot handle 
> Unicode. They were introduced far earlier than that and were intended 
> for programs that could not handle anything but the short 8.3 limits 
> for any given file or folder name. That they often help with avoiding 
> unicode is a side effect.
>
> Sound like your misunderstanding of this may be contributing to your 
> frustration.
>
> Doug C.
>
>
>  On Tue, 03 Oct 2017 23:25:23 -0400 *Hamish Moffatt 
> ham...@risingsoftware.com [firebird-support] 
> * wrote 
>
> On 03/10/17 19:59, Dimitry Sibiryakov s...@ibphoenix.com
> 
> [firebird-support] wrote:
> > 03.10.2017 2:25, Hamish Moffatt ham...@risingsoftware.com
>  [firebird-support] wrote:
> >> Any ideas?
> > Give up. Firebird does not support unicode characters in
> database name and path. Only ANSI.
> >
> >
>
> Thanks for confirming what I figured from the source. But this is
> exactly what the Windows short path names are for - if your
> application
> can't handle Unicode filenames, use the short names. I tried it
> and it
> fails. Firebird explicitly converts the short path name back to a
> long
> name it can't use?!
>
> It seems to mostly work with UTF-8 on linux, or at least I had it
> working a couple of years back.
>
>
> Hamish
>

-- 
Richard Damon

RE: [firebird-support] Re: detect duplicate blobs, how to?

['Richard Damon' rich...@damon-family.org [firebird-support]]

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, February 9, 2017 11:33 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: detect duplicate blobs, how to?

 

  



> You are aware of course that you can't use any hashing function on its own to 
> detect duplicates? - the best you can do is detect *probable* duplicates, 

Actually, if you choose the right hash function you can detect duplicates. 

If you create a UDF based on/using SHA256, the result would be unique (with a 
2^256 certainty) -- there is no known collision of a SHA256 hash 
(https://en.wikipedia.org/wiki/Hash_function_security_summary). 


Sean 

Even SHA256 can’t eliminate all possibility of a duplicate. If you have files 
of more than 256 bits in them, by the pigeon hole principle, there WILL be 
duplicates within the universe of all possible files. There HAS to be. The 
probability is very low (but not zero) if you keep you set of files below 2^128 
members, but it is NOT 0. The key property of a has like SHA256 is that given a 
hash value, you cannot create a file (other than by brute force) that will 
yield that hash value. When using a hash, you need to decide if the chance of a 
false positive on match. With a good large hash, that probability gets very 
small, so maybe you can assume it is perfect. I would likely still compare the 
files to be sure, since you will likely only occur that cost if you do have a 
duplicate.   ,_._,___

Re: [firebird-support] UTF8 charset with case insensitive match

[Richard Damon rich...@damon-family.org [firebird-support]]

On 12/8/15 2:26 PM, r...@seedsbydesign.com [firebird-support] wrote:
>
> I've used FB for years with ISO8859_1 charset and collation.
>
> Now I designing a new DB for a big app update in FB3 and wonder about 
> this charset and collation.
>
> For test purposes I am using UTF8 and UNICODE_AI_CI and I like that I 
> can now don't have to worry about casing comparisons nor accents or 
> other special characters.
>
> In fact though, all the data in will be in the regular english-us 
> alphabet with maybe a little in spanish for things like "japapeño", 
> but still all single byte chars.
>
> From what I read, only one byte is used for storing these characters, 
> so the same amount of space would be used as compared to ISO8859_1.
>
> What I am wondering before I get too far along, is there any downside 
> to using UTF8 and UNICODE_AI_CI?
>
> Regards,
>
> Rick
>
UTF-8 uses 1 byte for code points 0-127 (the basic ASCII Characters), 
and two or more bytes  for other characters (like ñ), so you may take a 
small hit is data size. Code pages like ISO8859-1 represent all 
characters in a single byte (for those they represent).


-- 
Richard Damon