Greetings all!

To give a quick bit of background : I've been using DBISAM since v2 - v4 and
am now looking at migrating to ElevateDB. I've been a Delphi programmer
since version 1 was released 1995 (can't believe that was 14 years ago!) but
I moved over to C# about a year ago and am now using Visual Studio 2008.

My current project is called ClinicOffice
(http://www.pioneersoftware.co.uk/cov4.htm) which is written in C# and uses
a DBISAM 4 database engine with ContextSoft's DBISAM ADO.NET provider. The
DBISAM ADO.NET provider can only work in C/S mode so I've written a small
DBISAM Server program (in Delphi 7) which runs in the background, so the
architecture goes like this :-

ClinicOffice -> DBISAM ADO.NET -> Local DBISAM Server App -> database files

We have several 100 users and while many are fine, a growing number are
running into problems - some performance related, others find that
ClinicOffice keeps losing the connection to the local Server App. It's hard
to say what's at the cause of the problems, but my money would be on
ContextSoft's ADO.NET provider. Unfortunately, their support is appalling
and there are several bugs in their provider which I have to work round.

Bottom line? I've decided to switch to ElevateDB so that I can take
ContextSoft out of the equation. i.e.

ClinicOffice -> EDB ADO.NET -> database files

I've been trying to get my head around the differences between DBISAM and
EDB though and I've come up with a number of questions. I'll be using
ElevateDB in LOCAL mode (not C/S) I need to keep this as simple as possible
for my users, so I don't want to have to install use the ElevateDB Manager
on 100s of customers machines to migrate them over - ideally I would like to
do it all from code.

Q1. Can I create and edit EDB SESSIONs using .NET?
If so, then how? If not, then I'll need to create a small command-line
Delphi App (which can be called from my C# app) to do this. Will the VCL
trial license let me do this or will I have to purchase a VCL license in
addition to the ADO.NET license which I have already purchased?

Q2. Has support for "IF EXISTS" and "IF NOT EXISTS" been put in yet? I
noticed on the NGs that this was dropped in ElevateDB? (I use it a lot!)

Q3. DBISAM's deployment flexibility was one of its greatest strengths
(IMHO). It used to be great to be able to drop a folder full of '.dat',
'.idx', '.blb' files and then simply connect to that folder. What is the
equivalent with EDB? If I drop a new database folder to a customer's
computer, presumably I need to tell the active TEDBSession about the new
database, but there's no obvious TEDBSession.AddDatabase method to call?

Sorry for the long post! I'm sure there'll be lots more questions as I get
deeper into this.

TIA,

Hedley

Hedley


I can't answer the .net stuff since I've never used it. Sorry.

>Q2. Has support for "IF EXISTS" and "IF NOT EXISTS" been put in yet? I
>noticed on the NGs that this was dropped in ElevateDB? (I use it a lot!)

No and will not be. You now have to either know what's there or query the information tables to build suitable sql, or for some bits as long as the sql is in a script use the exception handling.

>Q3. DBISAM's deployment flexibility was one of its greatest strengths
>(IMHO). It used to be great to be able to drop a folder full of '.dat',
>'.idx', '.blb' files and then simply connect to that folder. What is the
>equivalent with EDB? If I drop a new database folder to a customer's
>computer, presumably I need to tell the active TEDBSession about the new
>database, but there's no obvious TEDBSession.AddDatabase method to call?

Again I don't know about .net but I'd assume its pretty much like the delphi approach. What you'll need is:

1 x EDBEngine per application. Its main use is to set UseLocalSessionEngineSettings to True in the IDE, if you don't drop one on a datamodule set this in code.

1 x EDBSession - use this to define where the configuration file is located, and add in the logon user & password

1 x EDBDatabase - you link the session to a database that you've already created in the configuration file here (use sql or EDBManager to create the database) eg localdatabase.sessionname := sessionname

n x tables or queries - you link the session and database to the tables and queries.

I'm sure its different in .net, but if you have a look at Tim's CD collection example it will give you some ideas.

Other things you'll need to watch out for are:

NULL <> emptystring as it does in DBISAM

If setting VARCHARs using SQL if the data is longer than the field its no longer trimmed - you'll get an exception.

VARCHARs are no longer RTRIMed on posting

You can't edit canned (non sensitive) result sets

SELECT INTO to create a table has been replaced by CREATE TABLE AS

Full Text indexing has changed

The way in which case is handled in queries has changed you no longer use UPPER but COLLATE

There is no specialised memory database anymore - any database can be specified to be held in memory or disk, but if you want to use a memory database you have to have created it first.

I'm positive I'll have forgotten some but that should get you over the worst of the differences.


Roy Lambert [Team Elevate]

Hi Roy - thanks for the quick reply!

> 1 x EDBDatabase - you link the session to a database that you've already
> created in
> the configuration file here (use sql or EDBManager to create the database)
> eg localdatabase.sessionname := sessionname

I'm still a bit confused! Let's say I copy a database folder from one
computer to another and I drop the database folder to "C:\Temp\DB". Now I
want my application (let's say it's a Delphi app for simplicity) to access
the database. How would I proceed?

Presuming that I've already got the EDBEngine and EDBSession set up, how do
I add the database in "C:\Temp\DB" to the Session?

Thanks!

Hedley


>I'm still a bit confused! Let's say I copy a database folder from one
>computer to another and I drop the database folder to "C:\Temp\DB". Now I
>want my application (let's say it's a Delphi app for simplicity) to access
>the database. How would I proceed?
>
>Presuming that I've already got the EDBEngine and EDBSession set up, how do
>I add the database in "C:\Temp\DB" to the Session?

I don't think you do, or at least I don't know a way.

Its a bit more complicated than with DBISAM. First thing to keep in mind is that ElevateDB operates through catalogs. These store all the table metadata, stored procedures etc. DBISAM stored the metadata in the table headers so moving a table was simple - copy the 2 / 3 files involved. With ElevateDB you have to move the table files and the catalog AND you can't just move a single table. Also you have to watch out for version differences between the catalog and the tables.

That said it isn't difficult. Firstly you need a database defined in the configuration file. You have to use SQL (if you use EDBManager that's what it does). You can use the session's execute method eg

session.Execute('CREATE DATABASE "fred" PATH 'C;\TempzDB');

When that's done, and assuming you have a TEDBDatabase component defined - dbcomp - all you then have to do is

dbcomp.SessionName := session.sessionname;
dbcomp.Database := 'fred';
dbcomp.DatabaseName := 'dbname';

IE you don't add a database to the session you specify the session for the database.

Just a bit more gibbering. In DBISAM you had tables (either 2 or 3 physical files each). Iin ElevateDB you have a configuration file (three physical files - configuration, lock and log) a catalog file (2 physical files - catalog and lock) and then tables (either 2 or 3 physical files). All are needed if the system is to work. The catalog and the tables are best regarded as a single entity. If you copy the catalog without the tables and then open one in an app ElevateDB will create blank files for you.

So rather than shipping blank tables or sql to create them you can just ship the catalog and the tables will be created.

Think as though the tables reside in the catalog and the physical files are just containers for the data. It might help. You CANNOT just copy the data files around anymore.

HTH

Roy Lambert [Team Elevate]

Thanks so much for taking the time to explain all that Roy - it's a great
help, even if it is a little disappointing that EDB is not quite so easy to
work with as DBISAM.

Tim - can I just ask for your input on this question please?

> Q1. Can I create and edit EDB SESSIONs using .NET?
> If so, then how? If not, then I'll need to create a small command-line
> Delphi App (which can be called from my C# app) to do this. Will the VCL
> trial license let me do this or will I have to purchase a VCL license in
> addition to the ADO.NET license which I have already purchased?

Thanks!

Hi Hedley,

Have you tried just using the DBISAM ODBC driver, if you have existing
data and customers you could just change to this internally and the
change over would be invisible to you customers.

Chris Holland
[Team Elevate]

Hedley Muscroft wrote:
> Greetings all!
>
> To give a quick bit of background : I've been using DBISAM since v2 - v4
> and am now looking at migrating to ElevateDB. I've been a Delphi
> programmer since version 1 was released 1995 (can't believe that was 14
> years ago!) but I moved over to C# about a year ago and am now using
> Visual Studio 2008.
>
> My current project is called ClinicOffice
> (http://www.pioneersoftware.co.uk/cov4.htm) which is written in C# and
> uses a DBISAM 4 database engine with ContextSoft's DBISAM ADO.NET
> provider. The DBISAM ADO.NET provider can only work in C/S mode so I've
> written a small DBISAM Server program (in Delphi 7) which runs in the
> background, so the architecture goes like this :-
>
> ClinicOffice -> DBISAM ADO.NET -> Local DBISAM Server App -> database files
>
> We have several 100 users and while many are fine, a growing number are
> running into problems - some performance related, others find that
> ClinicOffice keeps losing the connection to the local Server App. It's
> hard to say what's at the cause of the problems, but my money would be
> on ContextSoft's ADO.NET provider. Unfortunately, their support is
> appalling and there are several bugs in their provider which I have to
> work round.
>
> Bottom line? I've decided to switch to ElevateDB so that I can take
> ContextSoft out of the equation. i.e.
>
> ClinicOffice -> EDB ADO.NET -> database files
>
> I've been trying to get my head around the differences between DBISAM
> and EDB though and I've come up with a number of questions. I'll be
> using ElevateDB in LOCAL mode (not C/S) I need to keep this as simple as
> possible for my users, so I don't want to have to install use the
> ElevateDB Manager on 100s of customers machines to migrate them over -
> ideally I would like to do it all from code.
>
> Q1. Can I create and edit EDB SESSIONs using .NET?
> If so, then how? If not, then I'll need to create a small command-line
> Delphi App (which can be called from my C# app) to do this. Will the VCL
> trial license let me do this or will I have to purchase a VCL license in
> addition to the ADO.NET license which I have already purchased?
>
> Q2. Has support for "IF EXISTS" and "IF NOT EXISTS" been put in yet? I
> noticed on the NGs that this was dropped in ElevateDB? (I use it a lot!)
>
> Q3. DBISAM's deployment flexibility was one of its greatest strengths
> (IMHO). It used to be great to be able to drop a folder full of '.dat',
> '.idx', '.blb' files and then simply connect to that folder. What is the
> equivalent with EDB? If I drop a new database folder to a customer's
> computer, presumably I need to tell the active TEDBSession about the new
> database, but there's no obvious TEDBSession.AddDatabase method to call?
>
> Sorry for the long post! I'm sure there'll be lots more questions as I
> get deeper into this.
>
> TIA,
>
> Hedley

Hi Chris,

Thanks for the suggestion. I've always tried to stay away from ODBC drivers
(if I'm honest, this is mainly because I've never really taken the time to
understand them properly!) but I have read about problems with ODBC and 64
bit OS's (not sure if DBISAM ODBC has any problem in that area?).

Aside from that, I need to use VIEWs and TRIGGERs so I figure moving to EDB
is the best option.

Can any of the ElevateDB staff please answer my question about ADO.NET /
licensing? To re-iterate :-

Can I create and edit EDB SESSIONs using .NET?
If so, then how? If not, then I will have to create a small command-line
Delphi App (which can be called from my C# app) to do this. Does this mean
that I have to purchase a VCL license in addition to the ADO.NET license
which I have already purchased?

TIA.

Hedley,

<< Q1. Can I create and edit EDB SESSIONs using .NET? >>

Not exactly.  .NET uses the EDBConnection, which is a combination of the
session and database concepts in the VCL.  Functionally, the EDBConnection
is very similar to a TEDBSession component and simply adds on the
TEDBDatabase functionality also, so that the EDBConnection has the concept
of a "current database":

http://www.elevatesoft.com/manual?action=mantopic&id=edb2dac&category=3&topic=5

<< Q2. Has support for "IF EXISTS" and "IF NOT EXISTS" been put in yet? I
noticed on the NGs that this was dropped in ElevateDB? (I use it a lot!) >>

No, IF EXISTS and IF NOT EXISTS has been replaced with normal catalog
queries to determine the existence of catalog objects.

<< Q3. DBISAM's deployment flexibility was one of its greatest strengths
(IMHO). It used to be great to be able to drop a folder full of '.dat',
'.idx', '.blb' files and then simply connect to that folder. What is the
equivalent with EDB? >>

It's the same principle.  However, you need to also include the
edbdatabase.edbcat file (database catalog) and make sure that you define the
database in the configuration on the target computer using the CREATE
DATABASE or ALTER DATABASE statements:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=10&topic=92

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=10&topic=93

Think of the CREATE/ALTER DATABASE statements as assigning the
TDBISAMDatabase.Directory property.  The only difference is that it is done
in the configuration file so that all accessing applications have the same
information about the location of the database.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hedley,

<< Can any of the ElevateDB staff please answer my question about ADO.NET /
licensing? >>

I'm not sure if you got our emails regarding this, but I was out sick the
prior week, which is the reason for the delay in responding.  Also, the
newsgroups are a free peer support forum now as of the switch-over to paid
support plans (March 2008), so a couple of days may go by before I check
them.  If you need an answer quickly regarding an issue/question, then you
should email or phone us using the contact information here:

http://www.elevatesoft.com/contacts.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim - thanks for the reply and glad to hear you're feeling better!

> Functionally, the EDBConnection is very similar to a TEDBSession component
> and
> simply adds on the TEDBDatabase functionality also...

Ok... that makes sense - thanks. I've been playing about with it for the
last few hours and I'm finally starting to get my head around how the
EDBConnection object is implemented.

I notice that if I create a new EDBConnection object and point the
ConfigPath to a new empty folder, it creates the EDBConfig file in there for
me. I can then connect to the "Configuration" database using the default
Administrator / EDBDefault user, and call "CREATE DATABASE xxx" to create a
new database in a sub-folder and then I'm good to go...

As my application allows users to create multiple databases which are
entirely separate from each other, does it matter if I create a new
EDBConfig file (which=a session?) for each database, rather than having a
single EDBConfig file?