Programming with dBASE - firebird and dbase

Author

firebird and dbase

Igor

2005-09-16, 7:23 am

Hi,
I would like to know how I can connect to firebird database using dBase 2.6?

I tried to do it by going to BDE Administrator - Databases and adding new database and selecting firebird driver, but when I try to connect to it in a form by setting database to active I would get the following error: Database engine error: "Invalid conf
iguration parameter".

I would appreciate any support around this issue.

Thanks

Igor

*Lysander*

2005-09-16, 9:23 am

In article <s91ShHruFHA.304@news-server>, dauphinyu@hotmail.com says...

> I would appreciate any support around this issue.

I am working on a step-by-step intro covering all details about using=20
Firebird from within dBase.
Unfortunately it will not be ready before 11.th of November... (see=20
below).

Until then I can give to you some first-step-hints:
( I suppose that you already installed the server?? If not, write back=20
here and I'll explain how to do this )

1.)
To use Firebird with dBase, you should not use the native BDE-driver.
Use the bridge "dBase-BDE-ODBC-Firebird".
Go to www.ibphoenix.com and get the odbc-driver 1.02.0070 and install it=20
on your pc.
2.)
from the same website, get the client-library "fbclient.dll" and put it=20
into your system32-directory
3.)
create an "ODBC user DSN" (check ODBC-administrator on your pc for this,=20
odbcad32.exe)
- give a name for the connection/DSN
- the path must be relative to the server, not to the client
- If you installed firebird server on "servermachine" in drive "d" and=20
your database is "employee.fdb" then the string is:
" servermachine:d:\emp
loyee.fdb"
- client is "fbclient.dll"
- langdriver (for starting) is "ISO8859_1"
- do NOT chose any options of the case-sensitivity
- first user is "SYSDBA", first password is "masterkey"
- press test-button to see if you get connection from your client to the=20
server
4.)
BDE was already automatically creating an alias for your ODBC DSN. Chose=20
this alias and set the property "langdriver" to "WEuropeAnsi", set the=20
property "SQLQueryMode" to "Server".
5.)
Now you can go to the navigation-centre of dBase (or to dQuery) and open=20
the tables in the employee-database.

Ask all further questions about Firebird in the newsgroup=20
"dbase.com/sqlservers"
=20

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de

paolo bentivegna

2005-09-16, 9:23 am

hi,
you say
'To use Firebird with dBase, you should not use the native BDE-driver'
why?

2005-09-16, 8:23 pm

(Perchè funziona male...)
Because is not working well...
It is not evident at first sight, but strange behaviours will surface soon:
for example the connection under internet sometimes fail, or while in dQuery
you get the name of database in the list on the left but you can't open it,
or when you open it you get half of the data.
Just changin the driver make the whole thing work well.

Firebird 1.5.2 + Firebird ODBC 1.02.00.69

Just remember to specify in the ODBC box that you want "fbclient.dll" as
client otherwise you'll get the same problem as the native dBase driver.

Probably using Interbase 6 you won't run into this problem (at least I
didn't noticed them when I was using it - I got it togheter with my old
Visual dBase 7).

GN

"paolo bentivegna" <ken_bp@libero.it> ha scritto nel messaggio
news:AdeD2osuFHA.1044@news-server...
> hi,
> you say
> 'To use Firebird with dBase, you should not use the native BDE-driver'
> why?
>
>

*Lysander*

2005-09-19, 3:23 am

In article <AdeD2osuFHA.1044@news-server>, ken_bp@libero.it says...

> 'To use Firebird with dBase, you should not use the native BDE-driver'
> why?=20

1.)
It is not running stable with Firebird or with Interbase 6.5 and higher.
You will notice crashes mostly when using StoredProc-objects; but stored=20
procedures are the salt in the SQL-soup.

2.)
It is only supporting the so called "dialect 1", where the ODBC-driver=20
supports also "dialect 3". "3" is much more advanced than "1". The=20
dialect can be understood like a set of commands which the server does=20
understand, including a set of datatypes that you can use.
For example, there is not date and not time in dialect 1; only datetime=20
which you must seperate into date and time yourself by code.
Also, a lot of VERY supportive language-additions are missing in dialect=20
1.

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de

paolo bentivegna

2005-09-19, 7:23 am

thx

Eliseo

2005-10-03, 3:23 am

I know this is the wrong NG but I'm continuing the subject.

Following your instructions (thank you!) I have successfull configured the
OBDC driver for the Interbase-DBplus connection.

I would test it in a already full functional application that I realized
using the native Interbase driver (Firebird, BDE, Dbplus 2.21).
Obviously the dialect I used was the dialect 1.

During the test (after the successfull connection with the dbatabase and
some other small tests) I tryed to open the same database by an old form.
Then I have some error telling that 'the index are not present '.
'Re-designing' the form and chosing again the index the error disappairs.
The strange is that the syntax (source code) was un-changed!

Could you explain this behavior?

Thanks
Eliseo

"*Lysander*" <nobody@nowhere.com> ha scritto nel messaggio
news:MPG. 1d94d9ce361f14479898
d7@news.dbase.com...
In article <s91ShHruFHA.304@news-server>, dauphinyu@hotmail.com says...

> I would appreciate any support around this issue.

I am working on a step-by-step intro covering all details about using
Firebird from within dBase.
Unfortunately it will not be ready before 11.th of November... (see
below).

Until then I can give to you some first-step-hints:
( I suppose that you already installed the server?? If not, write back
here and I'll explain how to do this )

1.)
To use Firebird with dBase, you should not use the native BDE-driver.
Use the bridge "dBase-BDE-ODBC-Firebird".
Go to www.ibphoenix.com and get the odbc-driver 1.02.0070 and install it
on your pc.
2.)
from the same website, get the client-library "fbclient.dll" and put it
into your system32-directory
3.)
create an "ODBC user DSN" (check ODBC-administrator on your pc for this,
odbcad32.exe)
- give a name for the connection/DSN
- the path must be relative to the server, not to the client
- If you installed firebird server on "servermachine" in drive "d" and
your database is "employee.fdb" then the string is:
" servermachine:d:\emp
loyee.fdb"
- client is "fbclient.dll"
- langdriver (for starting) is "ISO8859_1"
- do NOT chose any options of the case-sensitivity
- first user is "SYSDBA", first password is "masterkey"
- press test-button to see if you get connection from your client to the
server
4.)
BDE was already automatically creating an alias for your ODBC DSN. Chose
this alias and set the property "langdriver" to "WEuropeAnsi", set the
property "SQLQueryMode" to "Server".
5.)
Now you can go to the navigation-centre of dBase (or to dQuery) and open
the tables in the employee-database.

Ask all further questions about Firebird in the newsgroup
"dbase.com/sqlservers"

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de

*Lysander*

2005-10-07, 3:26 am

In article <37G$8o#xFHA.1992@news-server>, visca@frascati.enea.it=20
says...

> Could you explain this behavior?

No, I never saw such.

Would be interesting to see, at which place exactly in your code you was=20
getting your error-message.

In general, I would recommend to not work with "index-names" when=20
working with SQL-Servers (besides Oracle, maybe...).

Some SQL-servers, Firebird amongst them, have a behaviour first to allow=20
"free" names for indexes, but sometimes are parsing only the "internal"=20
name to the calling function.

So, if you are using an index "custno_ordnerno" in some situations (not=20
always!) your client-object might get only the info about the internal=20
name which might be "integ_234".

Then of course, a statement like this:
this.rowset.indexname :=3D 'custno_ordnerno'
must fail.

I noticed this, but did not go deeper into it, because I am today=20
nowhere using the rowset.indexname properties.

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de

Eliseo

2005-10-07, 3:26 am

Thank you for the answer!
Regarding the error I will be more precise with another message because I
made the test with another PC.

Regarding the indexes I have an application that use them extensively
(Firebird) and I have till now no problems at all.
As far as I know to obtain a writable rowset with a 'select' that use the
order by clause an index is needed.
In my mind the use of indexes should be faster expecially when you need to
search for some record and after you need to edit it.

The usual code I use (i.e.) is the following:

this.SCT1 = new DATABASE()
this.SCT1.parent = this
with (this.SCT1)
left = 48.0
top = 14.5
databaseName = "SCTIB"
active = true
endwith

this.ISTRU021 = new QUERY()
this.ISTRU021.parent = this
with (this.ISTRU021)
left = 85.0
top = 1.0
database = form.sct1
sql = "select * from Istru02"
requestLive = false
active = true
endwith

with (this.ISTRU021.rowset)
indexName = "istru02_CODICE"
endwith

****************** END

CIAO!!!!!!!!!
Eliseo
"*Lysander*" <nobody@nowhere.com> ha scritto nel messaggio
news:MPG. 1dac41d2b3960a629898
f3@news.dbase.com...
In article <37G$8o#xFHA.1992@news-server>, visca@frascati.enea.it
says...

> Could you explain this behavior?

No, I never saw such.

Would be interesting to see, at which place exactly in your code you was
getting your error-message.

In general, I would recommend to not work with "index-names" when
working with SQL-Servers (besides Oracle, maybe...).

Some SQL-servers, Firebird amongst them, have a behaviour first to allow
"free" names for indexes, but sometimes are parsing only the "internal"
name to the calling function.

So, if you are using an index "custno_ordnerno" in some situations (not
always!) your client-object might get only the info about the internal
name which might be "integ_234".

Then of course, a statement like this:
this.rowset.indexname := 'custno_ordnerno'
must fail.

I noticed this, but did not go deeper into it, because I am today
nowhere using the rowset.indexname properties.

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de

*Lysander*

2005-10-07, 3:26 am

In article <BpsO8mMyFHA.1232@news-server>, visca@frascati.enea.it=20
says...

> Regarding the indexes I have an application that use them extensively
> (Firebird) and I have till now no problems at all.
Then most likely until now you are only working with "power"-users with=20
full rights on all and everything in the firebird-database.
The correct indexnames are streamed to the client-object every time,=20
when this client-object has ALL privileges. In other cases, as I told, I=20
found randomly missing/exchanged indexnames.

> As far as I know to obtain a writable rowset with a 'select' that use the
> order by clause an index is needed.
Noooo! :-)
This is only for dBase-tables.
And this is one big advantage of firebird-tables, that you can use an=20
order by without having an index on the column.

> In my mind the use of indexes should be faster expecially when you need t=
o
> search for some record and after you need to edit it.

This has been discussed before in the sql-servers-NG.
There are cases, when (though it sounds strange) an index at the wrong=20
time in the wrong place can slow down your complete databases.
comment of the experts "use indexes like a good spice".

> this.ISTRU021 =3D new QUERY()
> this.ISTRU021.parent =3D this
> with (this.ISTRU021)
> left =3D 85.0
> top =3D 1.0
> database =3D form.sct1
> sql =3D "select * from Istru02"
> requestLive =3D false
> active =3D true
> endwith

if the query is "requestlive=3Dfalse" you will not be able to write=20
anything to it.

one hint: never use an sql-server-query withOUT a WHERE-clause.
There are not many situations in which a client would need the complete=20
table at once.

There is a serious background for this. All clients of sql-servers are=20
optimized for smaller portions of recordsets. They are getting records=20
much faster from the server than dBase-tables can do, but if you=20
transport too much, they can even be slower than dBase-tables.

Also, if not absolutely necessary (for example if you do not need the=20
indexnames) don't use the asterisk (*) for loading all fields. Select=20
just the fields that you need in this special query. This can speed up=20
your connection sometimes by more than 100% percent!

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de

Eliseo

2005-10-07, 3:26 am

I am not so sure that a 'select field1,field2 from pippo where
filed=something order by field3' results in a writeable rowset in a firebird
environmental.

But, I will test it again.

Another question about the MS ODBC is that in my application usually I use
to choose the working year (2005, 2004 ecc) at start up and after the
choice I re-create the dbealias accordingly by point to a sub-directory that
contains the year-related database (firebird).

Using the OBDC the path is contained in the ODBC configuration, then I
cannot use my method anymore.
How can manage the years?

Thank you in advance.

Ciao
Eliseo

*Lysander*

2005-10-07, 3:26 am

In article <hhsAtZwyFHA.1232@news-server>, visca@frascati.enea.it=20
says...
> I am not so sure that a 'select field1,field2 from pippo where
> filed=3Dsomething order by field3' results in a writeable rowset in a fir=
ebird

> environmental.

I am. :)

Even a lot of sets constructed by JOINS are "updatable", depending on=20
the underlying field-structure. Even Views are in a lot of cases=20
updatable.

If you have any problems, consider also looking very closely at your=20
query-object/rowset-object. Sometimes you manually have to specify=20
requestlive=3Dtrue.

> Using the OBDC the path is contained in the ODBC configuration, then I
> cannot use my method anymore.
> How can manage the years?

Oh, this should be easy.
You can use the similar way. I don't have an actual test for this, but=20
was testing this about 1 year ago.

The BDE-Alias has a property "Database Name".
Usually, this is empty. Then the alias will take the information=20
streamed by the ODBC-DSN.
But, if you alternatively give there a name, then this value will=20
override the value of the ODBC-DSN.

Example:
in your DSN you have databasename " Server1:accounting20
04"
in your Alias you have database name empty.
opening the alias will make a connection to accounting2004

now you will write into the alias-property "databasename" the value=20
" server1:accounting20
05".

opening the alias again will now make a connection to accounting2005.

If it does not work, consider to empty the value in the DSN before,=20
because I remember that this definitely worked.

also consider that you can have multiple aliases in the BDE for 1 DSN=20
this way. Chosing another alias could be more efficient in your case=20
than changing the properties of one alias again and again.
=20

--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de

Eliseo

2005-10-11, 7:27 am

Thank you for the precise answer!

Bye
Eliseo

"*Lysander*" <nobody@nowhere.com> ha scritto nel messaggio
news:MPG. 1db0454be4b28ca98990
7@news.dbase.com...
In article <hhsAtZwyFHA.1232@news-server>, visca@frascati.enea.it
says...
> I am not so sure that a 'select field1,field2 from pippo where
> filed=something order by field3' results in a writeable rowset in a
firebird
> environmental.

I am. :)

Even a lot of sets constructed by JOINS are "updatable", depending on
the underlying field-structure. Even Views are in a lot of cases
updatable.

If you have any problems, consider also looking very closely at your
query-object/rowset-object. Sometimes you manually have to specify
requestlive=true.

> Using the OBDC the path is contained in the ODBC configuration, then I
> cannot use my method anymore.
> How can manage the years?

Oh, this should be easy.
You can use the similar way. I don't have an actual test for this, but
was testing this about 1 year ago.

The BDE-Alias has a property "Database Name".
Usually, this is empty. Then the alias will take the information
streamed by the ODBC-DSN.
But, if you alternatively give there a name, then this value will
override the value of the ODBC-DSN.

Example:
in your DSN you have databasename " Server1:accounting20
04"
in your Alias you have database name empty.
opening the alias will make a connection to accounting2004

now you will write into the alias-property "databasename" the value
" server1:accounting20
05".

opening the alias again will now make a connection to accounting2005.

If it does not work, consider to empty the value in the DSN before,
because I remember that this definitely worked.

also consider that you can have multiple aliases in the BDE for 1 DSN
this way. Chosing another alias could be more efficient in your case
than changing the properties of one alias again and again.

--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de

Sponsored Links