Subject | Re: [firebird-support] exists(EXECUTE STATEMENT ... |
---|---|
Author | Bjoern Reimer |
Post date | 2004-08-16T12:14:12Z |
HB> At 12:09 PM 16/08/2004 +0200, you wrote:
HB> STATEMENT....) is valid syntax. I don't remember the details of your
HB> statement now, but you could do something like this:
HB> ...
HB> declare truefalse smallint;
HB> ....
HB> sql = '(select '||fieldname||' from '||tablename|| ' ';
HB> sql = sql || 'where '||searchfield||' = '||variable||')';
HB> sql = 'select 1 from rdb$database where exists '||sql;
HB> EXECUTE STATEMENT SQL INTO truefalse;
HB> if (truefalse = 1) then
HB> .....
Ok, I'll change code to this!
HB> 1. When using *any* client interface you have to be a LOT careful, both to
HB> include the quotes and to get the case right, in EVERY reference to an
HB> object with quoted identifiers.
HB> 2. In Perl and PHP and several other languages, you have to escape double
HB> quotes in literal strings such as SQL statements.
Well, in languages embedding any other language like SQL or HTML or
XML you have to quote correctly.
HB> 3. Best practice in SQL is supply column lists for all SELECT statements,
HB> so that it is simpler to mend apps that you break by changing
HB> metadata. Apart from the untidiness, extra opportunity for errors, you add
HB> two extra bytes for every object referred to (and two extra hits on the
HB> Shift key!)
Well I have IBExpert generating field lists and a clipboard for
exchange to other apps. That helps me to avoid any errors by missing
the right key on my keyboard :-)
HB> 4. The length of a statement is limited so wasting bytes doesn't make
HB> sense. (The length of a plan is even more limited, so you don't want to
HB> waste bytes there, either).
HB> 5. For me (as a Pascal programmer) they break the standard Pascal coding
HB> conventions which I, along with most Pascal coders, follow. Java coding
HB> conventions are similar to Pascal's. I HATE having to handle code written
HB> by people whose only justification for using these infernal quotes is to
HB> enable case-sensitive identifiers. Non-C programmers get along admirably
HB> despite lacking the ability to code hidden traps like two variables named
HB> first_name and FIRST_NAME.
HB> Now, you tell me what's *good* about them.
Tat's not easy!
1. It's possible. Why? In Dialect 1 I didn't have a choice! In dialect
3 I do have.
2. I can use keywords as field names.
3. Mixed Case writing is visible in db documentation which increases
readability a lot (I'm using IBExperts "Generate HTML
documentation" feature)
4. I can avoid the separator "_" as I use "FirstName" not FIRST_NAME. On
longer identifiers as "PersonsFirstName" or PERSONS_FIRST_NAME
there is no difference in length any more.
But well, your arguments are a little bit stronger I fear...
Björn
--
Björn Reimer
>>Yes, and count(*) will always move through the whole result set.HB> It was right to use EXISTS, just wrong to suppose that EXISTS(EXECUTE
>>Therefore I wanted to use EXISTS.
HB> STATEMENT....) is valid syntax. I don't remember the details of your
HB> statement now, but you could do something like this:
HB> ...
HB> declare truefalse smallint;
HB> ....
HB> sql = '(select '||fieldname||' from '||tablename|| ' ';
HB> sql = sql || 'where '||searchfield||' = '||variable||')';
HB> sql = 'select 1 from rdb$database where exists '||sql;
HB> EXECUTE STATEMENT SQL INTO truefalse;
HB> if (truefalse = 1) then
HB> .....
Ok, I'll change code to this!
HB> 1. When using *any* client interface you have to be a LOT careful, both to
HB> include the quotes and to get the case right, in EVERY reference to an
HB> object with quoted identifiers.
HB> 2. In Perl and PHP and several other languages, you have to escape double
HB> quotes in literal strings such as SQL statements.
Well, in languages embedding any other language like SQL or HTML or
XML you have to quote correctly.
HB> 3. Best practice in SQL is supply column lists for all SELECT statements,
HB> so that it is simpler to mend apps that you break by changing
HB> metadata. Apart from the untidiness, extra opportunity for errors, you add
HB> two extra bytes for every object referred to (and two extra hits on the
HB> Shift key!)
Well I have IBExpert generating field lists and a clipboard for
exchange to other apps. That helps me to avoid any errors by missing
the right key on my keyboard :-)
HB> 4. The length of a statement is limited so wasting bytes doesn't make
HB> sense. (The length of a plan is even more limited, so you don't want to
HB> waste bytes there, either).
HB> 5. For me (as a Pascal programmer) they break the standard Pascal coding
HB> conventions which I, along with most Pascal coders, follow. Java coding
HB> conventions are similar to Pascal's. I HATE having to handle code written
HB> by people whose only justification for using these infernal quotes is to
HB> enable case-sensitive identifiers. Non-C programmers get along admirably
HB> despite lacking the ability to code hidden traps like two variables named
HB> first_name and FIRST_NAME.
HB> Now, you tell me what's *good* about them.
Tat's not easy!
1. It's possible. Why? In Dialect 1 I didn't have a choice! In dialect
3 I do have.
2. I can use keywords as field names.
3. Mixed Case writing is visible in db documentation which increases
readability a lot (I'm using IBExperts "Generate HTML
documentation" feature)
4. I can avoid the separator "_" as I use "FirstName" not FIRST_NAME. On
longer identifiers as "PersonsFirstName" or PERSONS_FIRST_NAME
there is no difference in length any more.
But well, your arguments are a little bit stronger I fear...
Björn
--
Björn Reimer