> John
>
>> That's more like a novel than a script
>
> Totally agree - and the plot stinks!

ROFL... Nice one Roy. I'll stick to database development and won't go
into novel writing then.

>> While it's not much use for a conversion I think it is technically possible to do that script in 1 EDB statement. I
>> wouldn't mind having a go if there was some data to test against.
>
> You sir are a grade A lunatic

OK - I'll see what I can get for you.

Cheers

Adam

Hi John and Roy,

I've sent through a private email to you both regarding where you can
download tables from to have a play.

If I can really work out a way to parse the data and make a TEDBAdam
query component that works with the original SQL, I'm going to be one
very happy man!

Thanks again for your help!

Cheers

Adam.

Hi Tim and Roy,

Thanks for your replies again. (and posting that converter application!)

I think the next thing I need to do is convert a copy of my data from
DBISam to EDB so I have something in the same structure to 'play with',
so I can trial things out.

At present it looks like I'm going to need to create some sort of
conversion routines so that anything that is a MEMORY table is changed
to a TEMPORARY table. (I only used memory tables in DBISAM as temporary
tables, and it looks as though EDB handles Temporary tables the way that
I need it to as well (ie, unique for each user, automatically removed at
end of session, etc)).

With that - I think I've been barking up the wrong tree to start with -
I need to focus on Temporary tables now, not memory tables. Thanks for
putting me straight!  

> I do love wishful thinking

Don't blame me. In the past - I've 'wished' many things could happen,
and Tim made it so! It's his fault  

Just thinking out loud:

I guess the other option is to just stay with DBISam at present for my
existing apps. I guess there's no real need for me to change to EDB.
(That I can see). DBISam does everything I need for these applications,
and maybe if it's not broke...?

I guess I like to be on the lastest stuff so I don't get 'left behind'
and want to make sure that my applications will be able to be supported
for the next 10 years. I've already made a couple of small applications
with EDB, but nothing that uses Temporary/Memory tables yet - all that
is in my larger apps that contains DBISam.

I am planning on developing a new larger application later this year
which I'll use EDB for, so maybe I'll be better off going down that
track first too, so I can 'learn' more about EDB progressively before
trying to change over some of my existing apps.

Thanks again for all your help!

Adam.

Adam

>Thanks for your replies again. (and posting that converter application!)

No problemo

>I think the next thing I need to do is convert a copy of my data from
>DBISam to EDB so I have something in the same structure to 'play with',
>so I can trial things out.

That should be easy if you use EDBManager

>At present it looks like I'm going to need to create some sort of
>conversion routines so that anything that is a MEMORY table is changed
>to a TEMPORARY table. (I only used memory tables in DBISAM as temporary
>tables, and it looks as though EDB handles Temporary tables the way that
>I need it to as well (ie, unique for each user, automatically removed at
>end of session, etc)).

Well you have most of the code supplied in the demo app. One thing you will need to do is identify any ORDER BY clauses used in the creation of the temporary tables and remove from the ElevateDB code. If all your SQL is written as the sample with the ORDER BY on its own line that will be a doddle.

>With that - I think I've been barking up the wrong tree to start with -
>I need to focus on Temporary tables now, not memory tables. Thanks for
>putting me straight!

Pretty much change Memory. to TEMPORARY and its done

>> I do love wishful thinking
>
>Don't blame me. In the past - I've 'wished' many things could happen,
>and Tim made it so! It's his fault

Yeah - but isn't it annoying when he doesn't

>Just thinking out loud:
>
>I guess the other option is to just stay with DBISam at present for my
>existing apps. I guess there's no real need for me to change to EDB.
>(That I can see). DBISam does everything I need for these applications,
>and maybe if it's not broke...?

The only reason to do so is maintenance and enhancement means you need to keep up with two DBs rather than one

>I guess I like to be on the lastest stuff so I don't get 'left behind'
>and want to make sure that my applications will be able to be supported
>for the next 10 years. I've already made a couple of small applications
>with EDB, but nothing that uses Temporary/Memory tables yet - all that
>is in my larger apps that contains DBISam.

I'm way behind - still on D2006, but if you read the CodeGear ngs you'll see there are a lot on D7, some on D5 and a few on earlier versions.

>I am planning on developing a new larger application later this year
>which I'll use EDB for, so maybe I'll be better off going down that
>track first too, so I can 'learn' more about EDB progressively before
>trying to change over some of my existing apps.

Sounds like a better plan than mine. I was converting my recruitment app - pretty much a total rewrite, massive changes to the table structures and switched from DBISAM to ElevateDB and started using the TMS components part way through the conversion. It was HARD WORK

Roy Lambert [Team Elevate]

Roy,

<< Not that difficult to mask out but I wonder what I was remembering. One
of the problems of search in the ngs is when all you can remember is "it was
something about ORDER BY" >>

The problem is that it really doesn't make much sense for CREATE TABLE.

--
Tim Young
Elevate Software
www.elevatesoft.com

Adam,

<< I guess the other option is to just stay with DBISam at present for my
existing apps. I guess there's no real need for me to change to EDB.
(That I can see). DBISam does everything I need for these applications, and
maybe if it's not broke...? >>

Besides the scripting and in-memory table issues, do you rely on any other
DBISAM-specific features like the automatic right-trimming of string fields,
or the empty string=NULL construct ?  The SQL can be modified in a way that
preserves the capabilities, but some of these other items require some more
thought if you rely on them a lot in the existing application.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< Not that difficult to mask out but I wonder what I was remembering. One
>of the problems of search in the ngs is when all you can remember is "it was
>something about ORDER BY" >>
>
>The problem is that it really doesn't make much sense for CREATE TABLE.

It does if you want to run through the created table in a particular sequence without creating an index.

Roy Lambert

Tim


>Besides the scripting and in-memory table issues, do you rely on any other
>DBISAM-specific features like the automatic right-trimming of string fields,
>or the empty string=NULL construct ? The SQL can be modified in a way that
>preserves the capabilities, but some of these other items require some more
>thought if you rely on them a lot in the existing application.

The other one I found whilst doing the conversion is dates.

The SQL Adam sent had bits like

M.StartDate > '2006-01-13'

I'm waiting for him to let me know if these are supplied as parameters or not.

Roy Lambert

Roy,

<< It does if you want to run through the created table in a particular
sequence without creating an index. >>

All you're doing with an ORDER BY is creating an index on the result set, so
there really is not difference except that in the case of manually creating
an index, you know the index name and can easily set it.  It's not so
apparent to people what index name to use when the ORDER BY creates it with
a funky internal name.

Also, you have to remember that any ORDER BY index will *not* be the active
index order when you open the table.  The natural insertion order primary
key (internal row ID) will be the active index order.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< It does if you want to run through the created table in a particular
>sequence without creating an index. >>
>
>All you're doing with an ORDER BY is creating an index on the result set, so
>there really is not difference except that in the case of manually creating
>an index, you know the index name and can easily set it. It's not so
>apparent to people what index name to use when the ORDER BY creates it with
>a funky internal name.

I presume that your perceptions of this at at least an order of magnitude greater than mine since you wrote the stuff. However, I have an almost entirely different viewpoint. To me the key fact is that the data is inserted into the new temporary/memory table in the order I want it. There's no funky internal name - in fact there's no index. OK you may have created some sort of index for ElevateDB's use but as far as the outside world is concerned there is no index.

>Also, you have to remember that any ORDER BY index will *not* be the active
>index order when you open the table. The natural insertion order primary
>key (internal row ID) will be the active index order.

Who cares?

Lets take a contrived but simple to explain example. I want to print an invoice which has multiple invoice lines. The way in which I've elected to do this (please don't suggest alternatives. I know I could use a simple query but assume there's good reasons - possibly stupidity -for not doing so) is to extract the invoice line data into a temporary table (in DBISAM it would have been a memory table). The important fact is that I want the invoice lines in product name order - I'm not interested in the internal invoice line number, sequence added to the invoice, date ordered or anything else.

I create my temporary table

CREATE TEMPORARY TABLE ILbyProduct AS (SELECT * FROM InvoiceLines ORDER BY ProductName) WITH DATA

I then pass ILbyProduct to my invoice printing program and it happily chunters through the temporary table. It doesn't need an index.

I know the behaviour isn't going to be changed but it does actually make sense. I also have some real life examples of things in my app before converting to ElevateDB but I'd have to write a "War and Peace" or several thousand lines of code to explain them.

Roy Lambert