I am writing a program to migrate a DBISAM3 databases to ElevateDB and hit a
problem ... a "hitting my head against a brick wall" sort problem.

I want to do it in code rather than a bunch of scripts because I have dozens
of databases to convert over a short period of time.  It is also more than a
straight migration, with various database changes and field name changes
etc.  I also want to hand the conversion task over to a non technical person
who will just run one exe and that's it.

I have been working through the manual and bumped into a problem straight
away.  Here is the first bit of code:- the CREATE MODULE

=========================
   qry.SQL.Text :=
     'SELECT * FROM Migrators WHERE ModuleName = ''edbmigratedbisam3''';
   qry.ExecSQL;
   if qry.RecordCount = 0 then
   begin
     s := 'CREATE MODULE "DBISAM3" ' + 'PATH ' + Engine.QuotedSQLStr
       (IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName))
+
       'edbmigratedbisam3.dll') + ' DESCRIPTION ''DBISAM 3 Migrator''';
     Session.Execute(s);
==========================

The CREATE MODULE worked, without the first 3 lines of code, the first time
I ran it, but failed subsequently because the module was already created.
I added the first 3 lines to test for the presence of the module and they
work OK in EDBMgr without any Databases defined.

It fails with "ElevateDB Error #401 The database DB does not exist."  OK -
that's obvious and I know I could wrap my CREATE in "try except end" and
swallow the error as I'm sure Roy will suggest but apart from being biased
against that approach, I can see there are other things I want to do in
these "System" tables possibly before creating the database.  Like testing
the presence of a database and confirming with the user that the database
can be dropped and replaced.

Conclusion - I must be doing something silly or overlooking the obvious.
EDBMgr can do it, so I should be able to do it too!

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff


Well eating the execption is one way to do it it wasn't the way I went <vbg>

It was a long time ago and because I was making a LOT of changes to the structures and content I did all of the real conversion work using DBISAM only converting to ElevateDB at the end and then doing a bit of tidying up. It made sense since back then I was reasonably good with DBISAM and knew very little about ElevateDB.

I also had to run things multiple times until I got it right so what I did was have a front screen which asked for old and new locations, copied the data from old to new, ran the changes and deleted the new DBISAM tables.

One reason for doing that was that the application was designed to allow multiple databases (especially a training database and live one).

If you want a copy of the code I used I'll be happy to email it to you.

Roy Lambert [Team Elevate]

On 13/08/2013 6:25 PM, Roy Lambert wrote:
> Jeff
>

>
> It was a long time ago and because I was making a LOT of changes to the structures and content I did

all of the real conversion work using DBISAM only converting to
ElevateDB at the end and then doing a bit of tidying up.

It made sense since back then I was reasonably good with DBISAM and knew
very little about ElevateDB.
>

Obviously I don't know your specific situation but Roy's approach is
probably the one I would take.

Look out for another post very soon from me about migrating from DBISAM 3.

Regards,
  Peter Evans

On 13/08/2013 6:25 PM, Roy Lambert wrote:
> Jeff
>

>
> It was a long time ago and because I was making a LOT of changes to the structures and content I did

all of the real conversion work using DBISAM only converting to
ElevateDB at the end and then doing a bit of tidying up.

It made sense since back then I was reasonably good with DBISAM and knew
very little about ElevateDB.
>

Obviously I don't know your specific situation but Roy's approach is
probably the one I would take.

Look out for another post very soon from me about migrating from DBISAM 3.

Regards,
  Peter Evans

On 13/08/2013 2:18 PM, Jeff Cook wrote:

>
> I want to do it in code rather than a bunch of scripts because I have dozens
> of databases to convert over a short period of time.  It is also more than a
> straight migration, with various database changes and field name changes

I realise that at the moment your code changes are at the source DBISAM
3 end and not the target ElevateDB end.

However, here are some Procedures that I have found useful in my
migration. Note that I am using ElevateDB Unicode and not all the
routines have been tested properly. If you use them and are able to
improve them then please reply at some time. The quotes are from Elevate
Software documents. Thanks to Roy and others for inspiration.

Regards,
  Peter Evans


interface

function CreateDB(const Session1      : TEDBSession;
                  const DBName        : String;
                  const DBPath        : String;
                  const DBDescription : String) : Boolean;
  {Creates a database}

function CreateDBMemory(const Session1      : TEDBSession;
                        const DBName        : String;
                        const DBDescription : String) : Boolean;
  {Creates a database in memory.
  Thus there is no need for a path}

function DBDrop(const Session1 : TEDBSession;
                const DBName   : String) : Boolean;
  {This routine deletes the database.
  "Dropping a database will drop all tables and the entire catalog for the
  database specified. This means that all data and metadata for the
database
  will be permanently deleted"}

function DBExists(const Session1 : TEDBSession;
                  const DBName   : String) : Boolean;
  {Returns True if the database exists.}

function NumTablesInDB(const Session1 : TEDBSession;
                       const DBName   : String) : Integer;
  {Returns the number of tables in a database.
  Example of use is when you have a memory database. You have just deleted
  a memory table. If there are no tables remaining you may want to
delete the
  memory database}

procedure TableDrop(const ThisDatabase : TEDBDatabase;
                    const ThisTable    : String);
  {This routine deletes the table from the database}

function TableExists(const ThisDatabase : TEDBDatabase;
                     const ThisTable    : String) : Boolean;
  {Returns True if the table exists in the database.}

implementation



function CreateDB(const Session1      : TEDBSession;
                  const DBName        : String;
                  const DBPath        : String;
                  const DBDescription : String) : Boolean;
var
  Str : String;
begin
  TRY
    Str := 'CREATE DATABASE "' + DBName + '"' +
           ' PATH ' + '''' + DBPath + '''' +
           ' DESCRIPTION ' + '''' + DBDescription + '''';
    Session1.Execute(Str);
    Result := True
  EXCEPT
    Result := False;
  END;
end;

function CreateDBMemory(const Session1      : TEDBSession;
                        const DBName        : String;
                        const DBDescription : String) : Boolean;
var
  Str : String;
begin
  TRY
    Str := 'CREATE DATABASE "' + DBName + '"' +
           ' IN MEMORY ' +
           ' DESCRIPTION ' + '''' + DBDescription + '''';
    Session1.Execute(Str);
    Result := True
  EXCEPT
    Result := False;
  END;
end;

function DBDrop(const Session1 : TEDBSession;
                const DBName   : String) : Boolean;
var
  Str : String;
begin
  Result := False;
  Str := 'DROP DATABASE "' + DBName + '"';
  if Session1.Execute(Str) = 1 then
    Result := True
  else
    Result := False;
end;

function DBExists(const Session1 : TEDBSession;
                  const DBName   : String) : Boolean;
begin
  if Session1.Execute(
        'SELECT * FROM Databases ' +
        'WHERE Name=' + Engine.QuotedSQLStr(DBName)
                      ) = 1 then
    Result := True
  else
    Result := False;
end;

function NumTablesInDB(const Session1 : TEDBSession;
                       const DBName   : String) : Integer;
{"The Execute method returns the number of rows affected or returned by a
particular SQL statement, so you can use the return value of an indication
of whether any rows exist for the SELECT statement on the Configuration
database"}
var
  Str : String;
begin
  Str := 'SELECT * FROM Databases ' +
         'WHERE Name = ' + Engine.QuotedSQLStr(DBName);
  Result := Session1.Execute(Str);
end;

procedure TableDrop(const ThisDatabase : TEDBDatabase;
                    const ThisTable    : String);
begin
  TRY
    ThisDatabase.Execute(
           'DROP TABLE "' + ThisTable + '"'
                         );
  EXCEPT
  END;
end;

function TableExists(const ThisDatabase : TEDBDatabase;
                     const ThisTable    : String) : Boolean;
begin
  Result := False;
  IF ( ThisDatabase.Execute(
         'SELECT * FROM Information.Tables ' +
         'WHERE Name=' + Engine.QuotedSQLStr(ThisTable)
                            )) = 1 THEN
    Result := True;
end;

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:773CF82E-A461-4EAF-A2DD-77E19EE4C95A@news.elevatesoft.com...
>
> If you want a copy of the code I used I'll be happy to email it to you.
>
Thanks for the offer Roy - I might take you up on that if my current plan
turns to custard.  Meanwhile reading the help properly has solved the
problem per my answer to Peter Evans' post.

Cheers

Jeff

Hi Peter

Your code examples have lead me to reading the help again and realising the
errors of my ways

I read the code for your "function DBExists" and saw that Session.Execute
returns an integer.

Read the help and see that this is the number of rows.

AND "Any SQL statement executed using this method is automatically executed
from the context of the system-created Configuration database"

So my code becomes simply:-

-----------------------------------
   if Session.Execute
     ('SELECT * FROM Migrators WHERE ModuleName = ''edbmigratedbisam3''')
     = 0 then
   begin
     s := 'CREATE MODULE "DBISAM3" ' + 'PATH ' + Engine.QuotedSQLStr
       (IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName))
+
       'edbmigratedbisam3.dll') + ' DESCRIPTION ''DBISAM 3 Migrator''';
     Session.Execute(s);
   end;
-------------------------------

Thanks for your help.

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Peter Evans" <pgevans@melbpc.org.au> wrote in message
news:1F707649-D842-46A5-A332-3D13047CD3C6@news.elevatesoft.com...
> On 13/08/2013 2:18 PM, Jeff Cook wrote:
>
>>
>> I want to do it in code rather than a bunch of scripts because I have
>> dozens
>> of databases to convert over a short period of time.  It is also more
>> than a
>> straight migration, with various database changes and field name changes
>
> I realise that at the moment your code changes are at the source DBISAM 3
> end and not the target ElevateDB end.
>
> However, here are some Procedures that I have found useful in my
> migration. Note that I am using ElevateDB Unicode and not all the routines
> have been tested properly. If you use them and are able to improve them
> then please reply at some time. The quotes are from Elevate Software
> documents. Thanks to Roy and others for inspiration.
>
> Regards,
>   Peter Evans
>
>
> interface
>
> function CreateDB(const Session1      : TEDBSession;
>                   const DBName        : String;
>                   const DBPath        : String;
>                   const DBDescription : String) : Boolean;
>   {Creates a database}
>
> function CreateDBMemory(const Session1      : TEDBSession;
>                         const DBName        : String;
>                         const DBDescription : String) : Boolean;
>   {Creates a database in memory.
>   Thus there is no need for a path}
>
> function DBDrop(const Session1 : TEDBSession;
>                 const DBName   : String) : Boolean;
>   {This routine deletes the database.
>   "Dropping a database will drop all tables and the entire catalog for the
>   database specified. This means that all data and metadata for the
> database
>   will be permanently deleted"}
>
> function DBExists(const Session1 : TEDBSession;
>                   const DBName   : String) : Boolean;
>   {Returns True if the database exists.}
>
> function NumTablesInDB(const Session1 : TEDBSession;
>                        const DBName   : String) : Integer;
>   {Returns the number of tables in a database.
>   Example of use is when you have a memory database. You have just deleted
>   a memory table. If there are no tables remaining you may want to delete
> the
>   memory database}
>
> procedure TableDrop(const ThisDatabase : TEDBDatabase;
>                     const ThisTable    : String);
>   {This routine deletes the table from the database}
>
> function TableExists(const ThisDatabase : TEDBDatabase;
>                      const ThisTable    : String) : Boolean;
>   {Returns True if the table exists in the database.}
>
> implementation
>
>
>
> function CreateDB(const Session1      : TEDBSession;
>                   const DBName        : String;
>                   const DBPath        : String;
>                   const DBDescription : String) : Boolean;
> var
>   Str : String;
> begin
>   TRY
>     Str := 'CREATE DATABASE "' + DBName + '"' +
>            ' PATH ' + '''' + DBPath + '''' +
>            ' DESCRIPTION ' + '''' + DBDescription + '''';
>     Session1.Execute(Str);
>     Result := True
>   EXCEPT
>     Result := False;
>   END;
> end;
>
> function CreateDBMemory(const Session1      : TEDBSession;
>                         const DBName        : String;
>                         const DBDescription : String) : Boolean;
> var
>   Str : String;
> begin
>   TRY
>     Str := 'CREATE DATABASE "' + DBName + '"' +
>            ' IN MEMORY ' +
>            ' DESCRIPTION ' + '''' + DBDescription + '''';
>     Session1.Execute(Str);
>     Result := True
>   EXCEPT
>     Result := False;
>   END;
> end;
>
> function DBDrop(const Session1 : TEDBSession;
>                 const DBName   : String) : Boolean;
> var
>   Str : String;
> begin
>   Result := False;
>   Str := 'DROP DATABASE "' + DBName + '"';
>   if Session1.Execute(Str) = 1 then
>     Result := True
>   else
>     Result := False;
> end;
>
> function DBExists(const Session1 : TEDBSession;
>                   const DBName   : String) : Boolean;
> begin
>   if Session1.Execute(
>         'SELECT * FROM Databases ' +
>         'WHERE Name=' + Engine.QuotedSQLStr(DBName)
>                       ) = 1 then
>     Result := True
>   else
>     Result := False;
> end;
>
> function NumTablesInDB(const Session1 : TEDBSession;
>                        const DBName   : String) : Integer;
> {"The Execute method returns the number of rows affected or returned by a
> particular SQL statement, so you can use the return value of an indication
> of whether any rows exist for the SELECT statement on the Configuration
> database"}
> var
>   Str : String;
> begin
>   Str := 'SELECT * FROM Databases ' +
>          'WHERE Name = ' + Engine.QuotedSQLStr(DBName);
>   Result := Session1.Execute(Str);
> end;
>
> procedure TableDrop(const ThisDatabase : TEDBDatabase;
>                     const ThisTable    : String);
> begin
>   TRY
>     ThisDatabase.Execute(
>            'DROP TABLE "' + ThisTable + '"'
>                          );
>   EXCEPT
>   END;
> end;
>
> function TableExists(const ThisDatabase : TEDBDatabase;
>                      const ThisTable    : String) : Boolean;
> begin
>   Result := False;
>   IF ( ThisDatabase.Execute(
>          'SELECT * FROM Information.Tables ' +
>          'WHERE Name=' + Engine.QuotedSQLStr(ThisTable)
>                             )) = 1 THEN
>     Result := True;
> end;

On 13/08/2013 10:04 PM, Peter Evans wrote:

>
> function NumTablesInDB(const Session1 : TEDBSession;
>                         const DBName   : String) : Integer;

That function did not work.

Its replacement is :-

function NumTablesInDB(const ThisDatabase : TEDBDatabase) : Integer;
begin
  Result := ThisDatabase.Execute('SELECT * FROM Information.Tables');
end;

Regards,
  Peter Evans