Greetings,

I'll start out by saying that I'm "a bit" slow off the mark in getting going with Elevate DB - I purchased v1 and v2 when they came out and have yet to dig into them.  I've been a satisfied user of DBISAM for a long time.

I am looking to rewrite one of my larger applications down the road, but I thought I would start with a smaller app that I did a while ago.  This app doesn't need things like database users, admin tools and the like, as many of my target audience are computer iliterate.  In a nutshell, I want my program to be self-contained, accessing the data locally (i.e. no database server).  At startup, the program will need to be able to detect missing tables and create them on the fly and go from there.  

I also need to be able to create in-memory scratch tables when I need them and then toss them when done.

A quick glance through the documentation tells me that this isn't necessarily as straight forward as it is in DBISAM.  

Could someone give me a kick in the right direction?  It would be very much appreciated.,

Regards,

>>> Rick <<<

Rick


>I'll start out by saying that I'm "a bit" slow off the mark in getting going with Elevate DB - I purchased v1 and v2 when they came out and have yet to dig into them. I've been a satisfied user of DBISAM for a long time.

Welcome to the learning curve <vbcg>

The real differences are in queries and scripts not tables so if you don't use sql much 1) you'll have to learn but 2) it won't be as difficult as unlearning DBISAM sql

>I am looking to rewrite one of my larger applications down the road, but I thought I would start with a smaller app that I did a while ago. This app doesn't need things like database users, admin tools and the like, as many of my target audience are computer iliterate. In a nutshell, I want my program to be self-contained, accessing the data locally (i.e. no database server). At startup, the program will need to be able to detect missing tables and create them on the fly and go from there.

An advantage of ElevateDB is that the tables are defined in the catalog. The stuff on disk is "just" data plus a few bits like version number. If you try and open a table for which the data files don't exist, but the definition is in the catalog, ElevateDB will simply create it.

You can try this out in EDBManager - create a database and a few tables, enter some data, close EDBManager, go and delete the disk files, reopen EDBManager and you'll still be able to open your tables. They just won't have any data in them!

>I also need to be able to create in-memory scratch tables when I need them and then toss them when done.
>A quick glance through the documentation tells me that this isn't necessarily as straight forward as it is in DBISAM

This is both easy and difficult. I used and still use a number of memory tables in DBISAM I simply "SELECT....INTO...." to create one. In ElevateDB this syntax is gone. An in-memory database is now the same as a disk database (apart from the fact that its in memory) and a new syntax has to be used. Way back Tim gave me a script (ElevateDB scripts are TOTALLY different to DBISAM ones) to mimic the old behaviour. I think I've posted it before, but if you want a copy just ask. You should also check out temporary tables in ElevateDB. They have much of the same behaviour as I use memory tables for but lack the definitive automatic clean up (ie if the app crashes they'll probably be left behind) of an in-memory table.

There's a few other factors to bear in mind:

NULL and an empty string are no longer the same. SQL NULL behaviour is now in accord with the standard

depending on the version of DBISAM you're using you may have fun with the use of single vs double quotes (single for data double for system objects)

Canned (new jargon is insensitive) result sets are no longer editable.

Dates/timestamps/times if stuffed in as text require a DATE, TIME, or TIMESTAMP keyword prefacing the data. Arithmetic now operates using INTERVALS.

If you had customised functions in the engine they'll have to be pulled out and put into the database.

Full text indexing is a bit different. TEXTSEARCH has been replaced by the CONTAINS keyword which only works on indexed columns.

Probably the single biggest conceptual change is the switch from a database being a collection of files in a directory to being a set of definitions in a catalog.

>Could someone give me a kick in the right direction? It would be very much appreciated.,

All of the above taken together may seem a bit (or a lot) scary, but its not that bad now there are a lot of us who have been through the pain barrier, made the mistakes, come out the other side, and are willing to help.

Get started, ask questions when needed and it won't be that bad.

Good enough kick to get you started?

Roy Lambert [Team Elevate]

Also I believe (I am sure Roy or Tim can confirm or deny this) that the
IF NOT EXISTS from DBIsam is not available in ElevateDB

This is something that I use a lot to restructure table in DBIsam and I
think in ElevateDB you have to use some sort of function to achieve the
same result.

Chris Holland [Team Elevate]

On 06/01/2012 09:49, Roy Lambert wrote:
> Rick
>
>
>> I'll start out by saying that I'm "a bit" slow off the mark in getting going with Elevate DB - I purchased v1 and v2 when they came out and have yet to dig into them. I've been a satisfied user of DBISAM for a long time.
> Welcome to the learning curve<vbcg>
>
> The real differences are in queries and scripts not tables so if you don't use sql much 1) you'll have to learn but 2) it won't be as difficult as unlearning DBISAM sql
>
>> I am looking to rewrite one of my larger applications down the road, but I thought I would start with a smaller app that I did a while ago. This app doesn't need things like database users, admin tools and the like, as many of my target audience are computer iliterate. In a nutshell, I want my program to be self-contained, accessing the data locally (i.e. no database server). At startup, the program will need to be able to detect missing tables and create them on the fly and go from there.
> An advantage of ElevateDB is that the tables are defined in the catalog. The stuff on disk is "just" data plus a few bits like version number. If you try and open a table for which the data files don't exist, but the definition is in the catalog, ElevateDB will simply create it.
>
> You can try this out in EDBManager - create a database and a few tables, enter some data, close EDBManager, go and delete the disk files, reopen EDBManager and you'll still be able to open your tables. They just won't have any data in them!
>
>> I also need to be able to create in-memory scratch tables when I need them and then toss them when done.
>> A quick glance through the documentation tells me that this isn't necessarily as straight forward as it is in DBISAM
> This is both easy and difficult. I used and still use a number of memory tables in DBISAM I simply "SELECT....INTO...." to create one. In ElevateDB this syntax is gone. An in-memory database is now the same as a disk database (apart from the fact that its in memory) and a new syntax has to be used. Way back Tim gave me a script (ElevateDB scripts are TOTALLY different to DBISAM ones) to mimic the old behaviour. I think I've posted it before, but if you want a copy just ask. You should also check out temporary tables in ElevateDB. They have much of the same behaviour as I use memory tables for but lack the definitive automatic clean up (ie if the app crashes they'll probably be left behind) of an in-memory table.
>
> There's a few other factors to bear in mind:
>
> NULL and an empty string are no longer the same. SQL NULL behaviour is now in accord with the standard
>
> depending on the version of DBISAM you're using you may have fun with the use of single vs double quotes (single for data double for system objects)
>
> Canned (new jargon is insensitive) result sets are no longer editable.
>
> Dates/timestamps/times if stuffed in as text require a DATE, TIME, or TIMESTAMP keyword prefacing the data. Arithmetic now operates using INTERVALS.
>
> If you had customised functions in the engine they'll have to be pulled out and put into the database.
>
> Full text indexing is a bit different. TEXTSEARCH has been replaced by the CONTAINS keyword which only works on indexed columns.
>
> Probably the single biggest conceptual change is the switch from a database being a collection of files in a directory to being a set of definitions in a catalog.
>
>> Could someone give me a kick in the right direction? It would be very much appreciated.,
> All of the above taken together may seem a bit (or a lot) scary, but its not that bad now there are a lot of us who have been through the pain barrier, made the mistakes, come out the other side, and are willing to help.
>
> Get started, ask questions when needed and it won't be that bad.
>
> Good enough kick to get you started?
>
> Roy Lambert [Team Elevate]

Chris


>Also I believe (I am sure Roy or Tim can confirm or deny this) that the
>IF NOT EXISTS from DBIsam is not available in ElevateDB

I can indeed confirm it no longer exists!!!!!

Roy Lambert [Team Elevate]

>>I also need to be able to create in-memory scratch tables when
>>I need them and then toss them when done.

There is quite a big learning curve here ... but once you get the hang of it you'll love the power of EDB.

The  EDB SQL "Script" (rather than "Statement") is actually a well formed, powerful language in its own right. You can write things like:

SCRIPT
BEGIN

EXECUTE IMMEDIATE
 '
 CREATE TEMPORARY TABLE <tablename>
 <your table definition here>
 ';

EXECUTE IMMEDIATE
 '
 INSERT INTO <tablename>
 (<your field list here>) VALUES (<your values>)
 ';

END

Each "DBISAM-Like-SQL" statement is wrapped in the form:

EXECUTE IMMEDIATE
 '
 ';

To produce longer scripts which do serious work. Within the "EXECUTE IMMEDIATE" strings have to be commented with two single quotes,
i.e.

EXECUTE IMMEDIATE
 '
 UPDATE  <TableName>
 SET Name = ''Tom Smith''
 ';

--

You can also use:

CREATE DATABASE <databasename>
IN MEMORY

and create the tables into this memory database.

... I guess if you put the above together it will get you going.

I don't think that the table creation/modification or using this version of SQL will be a problem.

My big application is nearly 1/2 million lines of code... it currently uses the BDE.  I plan on switching to DBISAM or to EDB, but I am converting a smaller application that I developed a while ago (uses DBISAM) to get my feet wet, as this rewrite is going to be a big undertaking.  With the smaller application, I don't want my users to have to maintain any sort of database engine - I don't need logins/passwords or security, just local tables - basically, if they can run the application, they should be able to access the tables that it uses.

With DBISAM, I could drop a table or a query component on the form, set the database property to the folder in which the tables reside (I want everything for this application to be in the folder from which the program runs to keep things simple) and go from there.

Please keep in mind that this is my first time really playing around with EDB, so consider me a newbie at the logistics of using it.   I'm probably out in left field somewhere at this point in time.

So in my first attempt, I drop a query component on the form and set the SQL property to

CREATE DATABASE 'Test'
PATH 'C:\Develop\TestApp'
DESCRIPTION 'Test Database'

When I do an ExecSQL on the component, I get an error that the configuration path is empty.

If I try to work backward, adding a TEDBDatabase component to the form, it is asking me to log in any time I change something.  

So I go back another step and add a TEDBSession to the form, but it is also asking me to log in whenever I try to make it active (connected).

Before I make this any longer and more confusing,

I realize that I can build a default log in in the session component, but I am somewhat of a loss as to what I am logging into, as I am not running the database server.

Heeeelllllllpppp... and thanks in advance.

Please ignore most of what I said in my previous post above... I think I have this figured out... but the login thing concerns me still... if I install my executable on a new computer, will the login always be the defaults if there is no database admin program available?

Now all I need to do is detect that the table does not exist and create it.

Keeping my fingers crossed...

>>> Rick <<<

Rick


>Please ignore most of what I said in my previous post above...

Its ignored. But I'm still going to tell you.

>I think I have this figured out... but the login thing concerns me still... if I install my executable on a new computer, will the login always be the defaults if there is no database admin program available?

NO! It will be whatever you set it to.

>Now all I need to do is detect that the table does not exist and create it.

WRONG!

Forget what you know about databases and how you access them in DBISAM. It will not work in ElevateDB. You have to get used to thinking about the catalog.

Firstly I suggest you have a look at Tim's CD Collector example (ignore his tase in music - its ghastly). It gives you the basics. The pdf manual also gives you a pretty good blow by blow account of setting up a database.

Moving on. In DBISAM you could get away with dropping a table or a query onto a form, setting the directory (ie path) and that was that. With ElevateDB you need:

TEDBEngine
TEDBSession
TEDBDatabase
TEDBTable/TEDBQuery

and things need setting in sequence or it will cause grief. For a simple local app the following will work

1. TEDBEngine - Set UseLocalSessionEngineSettings to True - that's all you want to do with this component
2. TEDBSession - Set:
                                LocalConfigPath (see later)
                                LoginPassword (whatever you want)
                                LoginUser                 "
                                SessionName           "
3. TEDBDatabase - Set:
                                SessionName (whatever you set above)
                                DatabaseName (whatever you want)
                                Database (if you got the above two right you'll a drop down list of available databases)
4. TEDBTable - Set
                                SessionName (whatever you set above)
                                DatabaseName              "
                                If those are right you can pick the table name and you're in business.


The LocalConfigPath is where your configuration file for the database is stored. You can define the tables using EDBManager in this and supply both the exe and the config file to your users. If you do that then you don't have to worry about the tables existing. ElevateDB will create the data files as and when necessary.

I'm being summoned - I'll continue later.

Roy Lambert

Rick


Another important difference I forgot is that ElevateDB no longer truncates or RTRIMs string data. If data is longer than the column size you'll get an exception, and if you leave spaces on the end of a string - that's your problem. I've created subclassed table and query components to essentially revert to the DBISAM approach. They're in the extensions ng.

Roy Lambert [Team Elevate]

Rick


Just remembered another one - in DBISAM you used UPPER or LOWER to indicate case insensitivity in a query - ElevateDB you can set the collation in the table creation or use COLLATE in a query.


Roy Lambert