I'd like to see a CreateTableFrom method that would create a database table from a TDataset. It would create the table structure from any aDataset.

kbmMemTable has something similar called "CreateTableAs(aDataset, Options)" that works very well.

Of course it would not create the indexes since the TDataset does not reference indexes.
If you prefer to move it to a separate component or even a routine, that would be fine too. Maybe create a routine that gets passed a TDataset and it would return the SQL needed to create the table (which is what I'm writing now). Put in conditional code for 3rd party databases so it would also build the indexes too.

It would make moving data into EDB a whole lot easier.

Barry

Barry,

<< I'd like to see a CreateTableFrom method that would create a database
table from a TDataset. It would create the table structure from any
aDataset.  >>

Noted.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Barry


Have a look at the code I posted in the dbisam.general newsgroup - thread Best Way to Use Memory Tables. It may help.

Roy Lambert

>Have a look at the code I posted in the dbisam.general newsgroup - thread Best Way to Use Memory Tables. It may help.<

Roy,
   Now you tell me. LOL

I've finished my rtns already to "clone" a TDataset. Your code is a bit more extensive than mine because you handle indexes and I left out creating indexes because I don't need them right now. My rtn will copy data from any 3rd party database tables/queries because I'm cloning a TDataset.  You are cloning a TDBISAMTable so your code is more specific.

What I'd like to see from Tim is the ability to clone any TDataset (like TDBISAMTable, TkbmMemTable, TTable, T_xTable, TIBTable, TIBQuery etc.) and with the appropriate {$IFDEF UseKBM) activated in the code, it would handle indexes too. This would be a godsend for the people who are importing data from another database, and everyone needs to do that one time or another *inside* their program. (I don't like to use standalone applications because I lose the ability to handle exceptions and flag rows that couldn't be imported and I can't automate it.)

A single source code Unit to do this is all that is needed (I don't think a component is necessary). I was thinking of implementing exporting data from EDB tables/queries as well as importing data to it.  I could finish my rtns but my butt is up against the wall for a deadline with a client, and I know that the last 10% of the project takes 90% of the work.

Barry

Barry

> Now you tell me. LOL

Sorry - I typed as fast as I could.I also realised this morning I should have posted the ElevateDB code.

>I've finished my rtns already to "clone" a TDataset. Your code is a bit more extensive than mine because you handle indexes and I left out creating indexes because I don't need them right now. My rtn will copy data from any 3rd party database tables/queries because I'm cloning a TDataset. You are cloning a TDBISAMTable so your code is more specific.

Yup - TDBISAMTable because it was just intended to stuff the table into memory

>What I'd like to see from Tim is the ability to clone any TDataset (like TDBISAMTable, TkbmMemTable, TTable, T_xTable, TIBTable, TIBQuery etc.) and with the appropriate {$IFDEF UseKBM) activated in the code, it would handle indexes too. This would be a godsend for the people who are importing data from another database, and everyone needs to do that one time or another *inside* their program. (I don't like to use standalone applications because I lose the ability to handle exceptions and flag rows that couldn't be imported and I can't automate it.)

That, to me, seems a lot more than you originally asked for. It may just be my interpretations but initially I thought you were just asking Tim for a utility to replicate the structure and now you're asking for him to handle the data as well. If I'm right I'd say people are better off writing their own utilities, or buying something like SMImport from Scalabium.

>A single source code Unit to do this is all that is needed (I don't think a component is necessary). I was thinking of implementing exporting data from EDB tables/queries as well as importing data to it. I could finish my rtns but my butt is up against the wall for a deadline with a client, and I know that the last 10% of the project takes 90% of the work.

This really makes me think you're asking Tim to write data migration utilities for every and any database out there. I do hope I'm wrong.

Roy Lambert

Roy Lambert wrote:


>That, to me, seems a lot more than you originally asked for. It may just be my interpretations but initially I thought you were just asking Tim for a utility to replicate the structure and now you're asking for him to handle the data as well. If I'm right I'd say people are better off writing their own utilities, or buying something like SMImport from Scalabium.

This really makes me think you're asking Tim to write data migration utilities for every and any database out there. I do hope I'm wrong.
<

Of course the code would have to import the data. If your client is using an IB database and you want to import the data into EDB under program control (maybe he wants to keep using IB), then the unit uses TDataset as the source dataset like I have done, and it clones the table structure and imports the data (this I've already done). If you also want the unit to build the indexes, then {$IFDEF UseIB} would appear around the lines of code to clone the IB indexes to the EDB table.

There are 4 or 5 common 3rd party databases people use for Delphi. It is easy enough for the developer to add index building code for databases that aren't supported.

As far as SMImport is concerned, ironically I don't think they support EDB. (http://www.scalabium.com/smi/)

Barry

Barry


I am really baffled here. To get to the dataset you have to have (say) IB installed (unless you want Tim to write drivers for it as well) so why not just use it?

Roy Lambert

>I am really baffled here. To get to the dataset you have to have (say) IB installed (unless you want Tim to write drivers for it as well) so why not just use it?<

Sorry, I probably didn't explain myself properly.

The code to clone a table is stored in a single unit, and if it is to handle any type of 3rd party table (Advantage, NexusDb, Interbase, Firebird, Absolute etc.), we don't know ahead of time which databases the developer has installed on his machine. So we need to have a set of conditional variables defined at the start of the unit (or in an include file) to compile only the code for the database(s) he has installed in Delphi, otherwise he would get a compiler error.

Here is an example.

procedure CloneIndexes(    aSource: TDataset;
                                       aDest   : TDataset);

To create the table from the field definitions it is easy enough just to reference the taSource as a TDataset.
But to define the indexes for the destination table, we need to reference the source table by its class as in:

{$IFDEF UseIB}
if aDest is TIBTable then
 with TIBTable(aSource) do
    ....
   build aDest indexes here
   ......

{$ENDIF}

{$IFDEF Usekbm}
if aDest is TIBTable then
 with TkbmTable(aSource) do
    ....
   build aDest indexes here
   ......

{$ENDIF}

etc.

We only need to conditionally wrap about 10 lines of code to get at the index definitions of the taSource table so we can build the index for taDest.

If the user has a database that isn't handled, then it is easy enough for him to copy the conditional index code for a similar table and modify it. It is really quite simple.

What we end up with is a unit that can create a table, build its indexes and copy data from any 3rd party database. By doing it in code we can trap exceptions and pass column names we wish to ignore (not build in the taDest table) and other neat stuff.

It will take about 100-200 lines of code and 3 rtns: CloneTable, CloneIndexes, CloneData.
Only CloneIndexes needs conditional defines. The other two rtns can reference just the TDataset.

Barry

Barry


For this to work at all both ElevateDB and the other database have to be installed in the same app. I also wonder about differing datatypes.

Possibly I am being very dense here but I really don't see the point to this, or possibly I just don't understand what you're trying to achieve.

I can understand you not wanting to do your work in (and lets be really horrible and lets say) Paradox so you want to transfer the data over to ElevateDB and do your stuff in that but with your idea you'd need the BDE installed anyway. Having a utility to create the ElevateDB table(s) from the BDE ones every time the app is run doesn't seem like a good idea, and the amount of time needed to create the ElevateDB table not massive. Then add in the fact that, as you say, you may not want all the columns, you may want extra ones, you may/may not want the indices or want different ones and a generalised solution becomes a major app.

You can see my state of confusion from what I've written.

Are you wanting a super generalised fits all sizes migration utility to complement the DBISAM / BDE ones Tim's already given us or something else?

If the migration utility then a better idea might be to produce a skeleton which the user completes using components from both database engines.

If Tim owns copies of the other engines the skeleton could be pretty well fleshed out,

Roy Lambert

>For this to work at all both ElevateDB and the other database have to be installed in the same app.<
Correct. When you are importing data it is assumed your have the database components for both the taDest and taSource tables. Otherwise you'd have to import a text file like CSV or XML into EDB.

> I also wonder about differing datatypes.<
The datatypes are mapped in a Case statement but is easy enough because it is done in one location by referencing the TDataset. No conditional coding needed.

>Possibly I am being very dense here but I really don't see the point to this, or possibly I just don't understand what you're trying to achieve.

I can understand you not wanting to do your work in (and lets be really horrible and lets say) Paradox so you want to transfer the data over to ElevateDB and do your stuff in that but with your idea you'd need the BDE installed anyway. Having a utility to create the ElevateDB table(s) from the BDE ones every time the app is run doesn't seem like a good idea, and the amount of time needed to create the ElevateDB table not massive. Then add in the fact that, as you say, you may not want all the columns, you may want extra ones, you may/may not want the indices or want different ones and a generalised solution becomes a major app.

You can see my state of confusion from what I've written.<

If this was a one time thing, then sure a utility program can do it once and you're done with it. But let's say the client has his data stored in MySQL, and you need to import this data every day into an EDB database under program control. It runs at night and you're not there (hopefully) to watch the import process.

As far as excluding columns in taDest that are in taSource is trivial. I just add a parameter to the CloneTable rtn with a comma delimited list of column names to ignore. It will also ignore the index if the IndexFieldNames includes any of these ignored column names. As far as adding columns to taDest, this is easily done in the calling app using SQL. There is no need to add this complexity in this set of rtns.

>Are you wanting a super generalised fits all sizes migration utility to complement the DBISAM / BDE ones Tim's already given us or something else?<

I'm not familiar with the import code Tim has written for DBISAM.

>If the migration utility then a better idea might be to produce a skeleton which the user completes using components from both database engines.<
Correct-a-mundo!

But since 90% of the work can be done just by referencing the TDataset (building the table and copying the data), the only customization is building the indexes by referencing the taSource table with its native component like TkbmMemTable etc.. Build a few of these and the developer can easily customize it to include any 3rd party table that isn't already covered.

Barry