dBASE SQL Servers - Starting with Firebird

Author

Starting with Firebird

Andrew Camien

2005-10-20, 8:26 pm

I am just starting with Firebird. I have gotten to the point of being able to double-click a table in the navigator to open it, (I cannot edit fields, only add) but trying to use the command window, after setting the database, 'select * from table1' res
ults in '"Database Engine Error: Table does not exist." I have gotten this with local and network databases. I tried 'list x' and got no result, then tried 'list x for true' and got a listing. Thereafter, 'list x' worked. BDE open mode is read//write.
Anybody know what I'm missing? Thanks

Ivar B. Jessen

2005-10-20, 8:26 pm

On Thu, 20 Oct 2005 14:43:32 -0400, Andrew Camien
<acamien@irvine-sensors.com> wrote:

>I am just starting with Firebird. I have gotten to the point of being able to double-click a table in the navigator to open it, (I cannot edit fields, only add) but trying to use the command window, after setting the database, 'select * from table1' re
sults in '"Database Engine Error: Table does not exist." I have gotten this with local and network databases. I tried 'list x' and got no result, then tried 'list x for true' and got a listing. Thereafter, 'list x' worked. BDE open mode is read//write
. Anybody know what I'm missing? Thanks

Firebird comes with an example database name employee. With the ODBC
Administrator create a system DSN for said database, call it for example
FDBemployee. Check that you can connect to the database.

In the BDE administrator find the newly created database alias FDBemploye,
open it and set the language driver to 'WEurope'Ansi and SQLQRYMODE to
SERVER, save and close the BDE.

In the Navigator select the 'Tables' tab, open the 'Look in:' drop down and
select FDBemployee. Go to the command pane and type,

Use employee
list

and you should get all records in the employee table.

Now type CLOSE DATABASES and check in the navigator that the database is
closed.

Then in the command pane type,

open database FDBemployee
select * from :FDBemployee:employe
e
browse

and the table should open in a browse window.

Ivar B. Jessen

Roland Wingerter

2005-10-29, 11:23 am

Andrew Camien wrote

>I am just starting with Firebird. I have gotten to the point of being able
>to double-click a table in the navigator to open it, (I cannot edit fields,
>only add) but trying to use the command window, after setting the database,
>'select * from table1' results in '"Database Engine Error: Table does not
>exist." I have gotten this with local and network databases. I tried
>'list x' and got no result, then tried 'list x for true' and got a listing.
>Thereafter, 'list x' worked. BDE open mode is read//write. Anybody know
>what I'm missing? Thanks
------
I believe table names and field names in Firebird are case sensitive by
default. Try uppercase table name...

Roland

*Lysander*

2005-10-31, 3:23 am

In article <Y5EjTsJ3FHA.1236@news-server>, ich@hier.de says...

> I believe table names and field names in Firebird are case sensitive by=
=20
> default. Try uppercase table name...

identifiers like table names and procedure names are case_IN_sensitive=20

by default. Only when using the IBPhoenix-ODBC-driver the option for=20
"quoted identifier" is checked. But the database remains in case-
insensitive mode unless you really start using case sensitive=20
identifiers.

Andrew did not report success or failure to Ivars very good hint, so I=20
suppose the problem got solved in between.

--=20
ciao,
Andr=E9

Roland Wingerter

2005-10-31, 9:23 am

Hallo André,

> Roland schrieb
> I believe table names and field names in Firebird are case sensitive by
> default. Try uppercase table name...
------
I had the same problem with EMPLOYEE.FDB.

"use CUSTOMER" works
"use customer" did not work.
[color=darkred]
by default. Only when using the IBPhoenix-ODBC-driver the option for
"quoted identifier" is checked. But the database remains in case-
insensitive mode unless you really start using case sensitive
identifiers.
------
Thanks for the explanation. I believe I created a test table with a lower
case tablename.
I unchecked quoted identifier in the ODBC administrator and now "use
customer" works, too.
Is this the setting you recommend or am I heading for trouble?

Roland

*Lysander*

2005-10-31, 9:23 am

In article <q8BMNfi3FHA.1352@news-server>, ich@hier.de says...

> Is this the setting you recommend or am I heading for trouble?

glad it works.

I definitely recommend to not use any "quoted" identifiers if you can=20
somehow avoid it. As you saw, the trouble only starts when starting with=20
quoted identifiers.

Just give reasonable names to your tables.

Canadian_orders instead of "canadian orders".

As long as you keep identifiers unquoted, the following always has the=20
same effect:
- select * from MYTABLE
- select * from mytable
- select * from MyTable
Data from only 1 table will be selected.

As soon as you start quoting identifiers, the 3 examples above would=20
select data from 3 different tables. Quite uncomfortable, as I think.

--=20
ciao,
Andr=E9

*Lysander*

2005-10-31, 11:23 am

In article <q8BMNfi3FHA.1352@news-server>, ich@hier.de says...

> Is this the setting you recommend or am I heading for trouble?

Oh... see also Part I of the HTML-documentation, which I prepared for=20
dBKon 2005.
I think that you received this from HPN last week also, didn't you?

There you will find this recommendation under: "Konfiguration/ODBC-DSN"=20
on the bottom of the page.

--=20
ciao,
Andr=E9

Roland Wingerter

2005-10-31, 11:23 am

Hallo Andé,

thanks for the explanation. I too prefer "mytable" and "MYTABLE" to be the
same.

So far I like Firebird and IB Expert, but there are pitfalls. E.g. in the
SQL editor, the following query throws an error "Column does not belong to
referenced table".

SELECT * FROM customer where customer.customer like "D%"

It took me quite a while to find out that the double quotes are the problem,
I have to put the string argument in single quotes -- Aaargh.

I sure will be back with more questions.

Greetings

Roland

*Lysander* wrote

> Is this the setting you recommend or am I heading for trouble?

Roland Wingerter

2005-10-31, 11:23 am

*Lysander* schrieb
[color=darkred]
dBKon 2005.
I think that you received this from HPN last week also, didn't you?
------
Ja, hab ich bekommen, danke!
[color=darkred]
on the bottom of the page.
------
Hab alles gelesen, aber nur im Trockenkurs, statt gleich die Einstellungen
vorzunehmen. Das kommt davon...

Gruß

Roland

*Lysander*

2005-10-31, 11:23 am

In article <92DB8Jj3FHA.1036@news-server>, ich@hier.de says...

> I sure will be back with more questions.

You will be welcome. I hope you will find some questions that are also=20
important for me, without that I have been aware of them - so far :)

--=20
ciao,
Andr=E9

David Kerber

2005-10-31, 8:23 pm

In article <92DB8Jj3FHA.1036@news-server>, ich@hier.de says...
> Hallo And=E9,
>=20
> thanks for the explanation. I too prefer "mytable" and "MYTABLE" to be th=
e=20
> same.
>=20
> So far I like Firebird and IB Expert, but there are pitfalls. E.g. in th=
e=20
> SQL editor, the following query throws an error "Column does not belong t=
o=20
> referenced table".
>=20
> SELECT * FROM customer where customer.customer like "D%"
>=20
> It took me quite a while to find out that the double quotes are the probl=
em,=20
> I have to put the string argument in single quotes -- Aaargh.

Get used to it ;-P Many client-server databases work that way.

.....

--=20
Remove the ns_ from if replying by e-mail (but keep posts in the=20
newsgroups if possible).

Roland Wingerter

2005-10-31, 8:23 pm

David Kerber wrote[color=darkred]

> I have to put the string argument in single quotes -- Aaargh.
--------
I will - Thanks for the tip!

Roland

Ivar B. Jessen

2005-11-01, 3:23 am

On Mon, 31 Oct 2005 16:42:10 +0100, "Roland Wingerter" <ich@hier.de> wrote:

>So far I like Firebird and IB Expert, but there are pitfalls. E.g. in the
>SQL editor, the following query throws an error "Column does not belong to
>referenced table".
>
>SELECT * FROM customer where customer.customer like "D%"
>
>It took me quite a while to find out that the double quotes are the problem,
>I have to put the string argument in single quotes -- Aaargh.
>
>I sure will be back with more questions.
>
>Greetings
>
>Roland
>
>*Lysander* wrote
>

See documentation to Interbase6, Getting Started (GetStart.pdf):

MIGRATION ISSUES - Delimited identifiers

To increase compliance with the SQL 92 standard, InterBase 6 introduces
delimited identifiers. An identifier is the name of any database object;
for instance a table, a column, or a trigger. A delimited identifier is an
identifier that is enclosed in double quotes. Because the quotes delimit
the boundaries of the name, the possibilities for object names are greatly
expanded from previous versions of InterBase. Object names can now:

Be a keyword

Include non-ASCII characters

Include spaces

Be case sensitive

The double-quotes issue

Up to and including version 5, InterBase allowed the use of either single
or double quotes around string constants. The concept of delimited
identifiers did not exist. InterBase 6 supports delimited identifiers,
which means that double quotes must be used only for delimited identifiers.

SINGLE QUOTES AND DOUBLE QUOTES: SUMMARY

In all versions of InterBase, anything delimited by single quotes is
treated as a string constant.

In version 5 and older of InterBase, string constants could be delimited
by either double or single quotes. Since there was no concept of delimited
identifiers, double quotes were always interpreted as string constants.

Version 6 dialect 1 is a transition mode that behaves like older versions
of InterBase with respect to quote marks: it interprets strings within
double quotes as string constants and does not permit delimited
identifiers.

Version 6 dialect 3 uses double quotes only for delimited identifiers.
String constants must be delimited by single quotes, never double.

See Table 2.1 for how to handle quotations marks that occur within strings
or identifiers.

IMPORTANT In a future release, InterBase will allow only the dialect 3
behavior: double quotes will be interpreted only as delimited identifiers.
Only single quotes will delimit string constants.

DDL AND DML STATEMENTS
When version 6 servers detect that the client is dialect 1, they permit
client DML statement to contain double quotes and they correctly handle
these as string constants. However, they do not permit double quotes in
client DDL statements because that metadata would not be allowed in dialect
3. Version 6 servers all insist that string constants be delimited
with single quotes when clients create new metadata.

The DATE, TIME, and TIMESTAMP datatypes
InterBase 6 introduces two new datatypes: TIME and TIMESTAMP. In addition,
.......
.......

Ivar B. Jessen

Roland Wingerter

2005-11-01, 1:23 pm

Hello Ivar,

Ivar B. Jessen wrote

>
> See documentation to Interbase6, Getting Started (GetStart.pdf):
-------
Thanks for the pointer. Actually I had glanced through that document before
I started playing around, but learning the hard way I can remember better.
;-)

Roland

*Lysander*

2005-11-02, 3:23 am

In article <kudF27w3FHA.1036@news-server>, ich@hier.de says...

> Thanks for the pointer. Actually I had glanced through that document befo=
re=20

> I started playing around, but learning the hard way I can remember better=
..=20
> ;-)

From time to time, I am falling in the same trap again.
But then the quoting symbol will be the first place for me to look for=20
the error...
Here it is a help for me that I am also programming with Delphi... that=20
just does NOT allow double-quotes, and will send a shower of compiler=20
errors to you, if you dare using them :)
So I have a bit of training...

--=20
ciao,
Andr=E9

*Lysander*

2005-11-02, 3:23 am

In article < uibem1d30aqd3ll90g9o
jtcom188dn57n9@4ax.com>,=20
bergishagen@it.notthis.dk says...

> Be a keyword
>=20
> Include non-ASCII characters
>=20
> Include spaces
>=20
> Be case sensitive
>=20
> The double-quotes issue=20

The only excuse for such nonsense can be "I was taking over this=20
database from another developer". In any other case there is no sane=20
reason for using one of the 5 'features' above.

--=20
ciao,
Andr=E9

Roland Wingerter

2005-11-02, 3:23 am

Ivar B. Jessen wrote

>
> See documentation to Interbase6, Getting Started (GetStart.pdf):
-------
FWIW, "Firebird-1.5-QuickStart.pdf" has a concise description of string
delimiters in Firebird (see Chapter Firebird SQL).

Roland

David Kerber

2005-11-02, 7:23 am

In article <MPG. 1dd2953b4621b6429899
24@news.dbase.com>,
nobody@nowhere.com says...
> In article < uibem1d30aqd3ll90g9o
jtcom188dn57n9@4ax.com>,
> bergishagen@it.notthis.dk says...
>
>
> The only excuse for such nonsense can be "I was taking over this
> database from another developer". In any other case there is no sane
> reason for using one of the 5 'features' above.

Generally I would agree with you, except for the first one; it's pretty
easy to accidentally use a keyword as an identifier. I had named a
table "Type", and the project was too far along to change that before I
realized it was a keyword.

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

*Lysander*

2005-11-02, 11:23 am

In article <MPG. 1dd27f249846fab7989d
61@news.dbase.com>,=20
ns_dkerber@ns_Warren
RogersAssociates.com says...

> Generally I would agree with you, except for the first one; it's pretty=
=20
> easy to accidentally use a keyword as an identifier. I had named a=20
> table "Type", and the project was too far along to change that before I=
=20
> realized it was a keyword.

That's why I never give meaningful names to objects, but make use of=20
data-dictionaries. This, of course, became much easier with Firebird,=20
but also was possible with dBase.

If I really feel like giving meaningful names to objects, I at least put=20
a prefix to them, like "t_" for table.

T_Type would not have been a problem in your case, I guess.
Anyway... all such recommendations are only meant for the future use.

If you create a new database using Firebird, just do make sure to not=20
use any identifiers that could somehow be real_names, even if this is=20
only by setting a prefix to them, and in turn switch off this quoted-
identifier thing.
=20

--=20
ciao,
Andr=E9

Sponsored Links