Hi

Before I set off down a blind alley!

I am contemplating an applet that will use a local database on the
local machine, but which could have a need to select data from a
file-server database on another machine for insertion into the local
database.

I believe I would have to use 2 sessions, one for each of the databases
in this configuration.  But I also think that precludes simple data
transfer.   Or does it?

I doubt I can even do an export/import as the necessary stores are
config objects - can they be shared between sessions?

Would I have to run a select in one session, write the resulting
records to a file/filestream/memorystream, and then from the other
session load the data?

Or has Tim given us an easier way?

--

Malcolm


<<I believe I would have to use 2 sessions, one for each of the databases
in this configuration.  But I also think that precludes simple data
transfer.   Or does it?>>

I think the answer is probably

You'll need a separate session for the local and file-server databases if other apps also have the file-server database open at the same time. As far as I know the key bit is you can only have one configuration file having a database (or a number of databases) open at a time. So if the file-server database is only used by the app using the local database at the time you could have both as part of the same config and then no problem you can open them in the same session.

If that's not possible then your statement is partly right - simple data transfer using only SQL isn't possible - and even that's not totally true.

You could publish the data from the file-server database and then import that - stores are essentially pointers to directories so you could have a store in both local and file-server databases pointing to the same directory.

You could export the data from the file-server and then import into the local database.

You could also use a mixture of sql and table. There's nothing to stop you running a query on the file-server database and then navigating through the result set populating a table in the local database.

Roy Lambert [Team Elevate]

Thanks for that Roy.

Having read all that, my first reaction was to use a query .. but I
still have to navigate the result set to insert it which is a minor
pain.  

So I am now thinking about:
* an export from the remote(!), local session into one of its existing
stores
* then in the local(!), local session creating a store pointing at the
same folder
* running an import
* then dropping the store being carefull to 'keep contents'!

I don't think 'publish' will be any help to me.

Now for some food and reflection...

--

Malcolm


Just remember that exported data is in plain text!

I think in your case I would go for query and navigate. If the file structures of the query and destination table are the same its a simple bit of looping code and if there are no visual components attached to either query or table its pretty fast.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

> Malcolm
>
>
> Just remember that exported data is in plain text!
>
> I think in your case I would go for query and navigate. If the file
> structures of the query and destination table are the same its a
> simple bit of looping code and if there are no visual components
> attached to either query or table its pretty fast.
>
> Roy Lambert [Team Elevate]

Hi Roy

So long as it is practical I shall proceed - already doing so.
Phew, I am glad the export is plain text!  It is not confidential
and I do import/export all the time.

I already have all the working code, except the inter-database
transfer, embedded in my main application but I believe that if I can
strip it out into a separate app it will be more flexible (can run
multiple instances) as well as simplifying the main app.

The new code will all relate to managing the two sessions and grabbing
the required data.
--

Malcolm


Its dead easy but you know where I am if you need any help. I built export/import into my app ages ago so I could get data from live to development easily now I can no longer just copy the tables.

Roy Lambert

Malcolm,

<< So I am now thinking about:
* an export from the remote(!), local session into one of its existing
stores
* then in the local(!), local session creating a store pointing at the same
folder
* running an import
* then dropping the store being carefull to 'keep contents'! >>

That will work, but if the "remote" folder never changes, then I would just
keep the "local" store that points to it, and not keep creating/dropping it.

<< don't think 'publish' will be any help to me. >>

The replication will work for you if the idea is to synchronize all/some of
the inserts, updates, and deletes to the local database.  As of 2.05, there
are now LOAD triggers that get called while the LOAD UPDATES is occurring,
and they will allow you to skip a specific load of an INSERT, UPDATE, or
DELETE if it doesn't match the criteria that you're looking for.  Of course,
if you do skip certain updates, then you risk the chance of causing an error
later on in the load due to a dependency (update on a row that should have
just been added), but that really depends upon how you're excluding the
rows.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> That will work, but if the "remote" folder never changes, then I
> would just keep the "local" store that points to it, and not keep
> creating/dropping it.
>

Yes, I was conscious of that option and may decide I can set up the
store on the first call and then just be sure to drop it when about to
close the database.

>
> << don't think 'publish' will be any help to me. >>
>
> The replication will work for you if the idea is to synchronize
> all/some of the inserts, updates, and deletes to the local database.
> As of 2.05, there are now LOAD triggers that get called while the
> LOAD UPDATES is occurring, and they will allow you to skip a specific
> load of an INSERT, UPDATE, or DELETE if it doesn't match the criteria
> that you're looking for.  Of course, if you do skip certain updates,
> then you risk the chance of causing an error later on in the load due
> to a dependency (update on a row that should have just been added),
> but that really depends upon how you're excluding the rows.

No, that is not the scenario.  It will be a user option to grab
specific sets of rows for a handful of tables if required.  It is not a
matter of synchronising, rather it is getting some data then using it
for a different and self contained purpose .. and eventually it will
simply be deleted, possibly after being archived in a backup.

--