Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 19 total
Thread Help with using LoadFromStream & SaveToStream with Query
Thu, May 21 2020 4:13 AMPermanent Link

Norman Rorke

I am trying to move data between two servers using a query. Saving to stream is working but I cannot get LoadFromStream to work. I get the error below when I set Active = True;
My code is below. Can someone help please.

//**** Saving to stream *****//
ds := TDBISAMQuery.Create(nil);
try
 qry := TDBISAMQuery(ds);
 with qry do
 begin
   SessionName := server1.sessionname;
   DatabaseName := database;
   SQL.Clear;
   SQL.Add("SELECT * INTO TEMPTABLE FROM XSERVICES");
   Active := True;
   SaveToStream(aStream);
 end;
finally
 ds.Free;
end;

//*** Loading from stream ***//
ds := TDBISAMQuery.Create(nil);
try
 qry := TDBISAMQuery(ds);
 with qry do
 begin
   SessionName := server2.sessionname;
   DatabaseName := database
   SQL.Clear;
   SQL.Add("SELECT * INTO XSERVICES FROM TEMPTABLE");
   Active := True;  //***   At this point I get error => Table or backup file TEMPTABLE does not exist.
   LoadFromStream(aStream);
 end;
finally
  ds.Free;
end;
Thu, May 21 2020 4:50 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Norman

Not something I've ever done so I could be totally wrong but here goes .....


Are session1 and session2 in different directories? If so then that's why session2 can't see TEMPTABLE.

I assume that this line

SQL.Add("SELECT * INTO XSERVICES FROM TEMPTABLE");

is simply to create XSERVICES so you can stream the data into it. Logic says if this would work then you wouldn't have to stream the data because the statement would fill XSERVICES from TEMPTABLE. What I think you'll need to do is create XSERVICES some other way.

Roy Lambert
Thu, May 21 2020 8:18 AMPermanent Link

Norman Rorke

Hi Roy,
Thanks for responding.

I should have provided more background.

What I am trying to do is to move data from a cloud-based C/S database into an identical local database. Both environments use a C/S DBISAM back-end . I used session1 & session2 in my example to imply respective sessions at either end.

The table XSERVICE exists in both databases. What does not exist in either database is the table TEMPTABLE. I am making the assumption that SaveToStream would create and store the table in the resulting stream which can the be accessed subsequently when doing LoadFromStream.
Thu, May 21 2020 8:28 AMPermanent Link

Norman Rorke

Norman Rorke wrote:

It just occurred to me that I should be using the script "INSERT INTO XSERVICE SELECT * FROM TEMPTABLE' instead of 'SELECT * INTO XSERVICE FROM TEMPTABLE' in the LoadFromStream attempt. However I think I would get the same error regardless.
Thu, May 21 2020 8:34 AMPermanent Link

Raul

Team Elevate Team Elevate

On 5/21/2020 4:13 AM, Norman Rorke wrote:
> I am trying to move data between two servers using a query. Saving to stream is working but I cannot get LoadFromStream to work. I get the error below when I set Active = True;
> My code is below. Can someone help please.
>
> //*** Loading from stream ***//
> ds := TDBISAMQuery.Create(nil);
> try
>    qry := TDBISAMQuery(ds);
>    with qry do
>    begin
>      SessionName := server2.sessionname;
>      DatabaseName := database
>      SQL.Clear;
>      SQL.Add("SELECT * INTO XSERVICES FROM TEMPTABLE");
>      Active := True;  //***   At this point I get error => Table or backup file TEMPTABLE does not exist.
>      LoadFromStream(aStream);
>    end;
> finally
>     ds.Free;
> end;
>

Treat the stream as "bunch of bytes" - there is no table name nor SQL
really involved when loading - just open table/query and load.

I usually use a table instance in which case it's similar to this (note
that if that table already has data you might want to empty if before
loading)

with myTbl do
  SessionName = server2.sessionname;
  DatabaseName  = database
  TableName = 'XSERVICES';
  Open;
  LoadFromStream(aStream);
  ...


Query i assume should work as well and should be like this (not in front
of delphi right now so cant test) :

with qry do
begin
  SessionName := server2.sessionname;
  DatabaseName := database
  SQL.Clear;
  SQL.Add("SELECT * from XSERVICES");
  Active := True;
  LoadFromStream(aStream);
....

Raul



Thu, May 21 2020 8:45 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Norman


I've just had a quick read of the manual and essentially what you're trying to do would overwrite the resultset (ie a table) of an existing VALID query. The error you're getting is from the fact that the query isn't valid.

Create the table XSERVICES in code yourself and then load the stream into that - I could be wrong but I think it should work. Just make sure the structure is the same as session1.XSERVICES.

Roy Lambert
Thu, May 21 2020 8:50 AMPermanent Link

Norman Rorke

Raul,

A bit more background. The cloud-based XSERVICE table contains orders that have been logged by customer via a web-application. What I'm trying to do is to retrieve the latest on-line orders and add then to the local XSERVICE table without affecting existing records. I prefer to use a query because there is a complex WHERE clause in the retrieval of the cloud data.

Can you suggest a way of achieving the exercise with queries.

Thanks
Thu, May 21 2020 9:56 AMPermanent Link

Norman Rorke

Roy,

You are right about the script. I have corrected it as stated above (INSERT INTO XSERVICE). That is not the problem though. The problem is with the TEMPTABLE. I obviously have a wrong concept of how SaveToStream & LoadFromStream are supposed to work with queries and would like to see some examples.
Thu, May 21 2020 9:57 AMPermanent Link

Raul

Team Elevate Team Elevate

On 5/21/2020 8:50 AM, Norman Rorke wrote:
> Raul,
>
> A bit more background. The cloud-based XSERVICE table contains orders that have been logged by customer via a web-application. What I'm trying to do is to retrieve the latest on-line orders and add then to the local XSERVICE table without affecting existing records. I prefer to use a query because there is a complex WHERE clause in the retrieval of the cloud data.
>
> Can you suggest a way of achieving the exercise with queries.

You're basically on the right track. This is how i would do it in general.

1. You can use query of any complexity to get the source data (i.e. new
orders) and use that to SaveToStream (this i know already works for you)

2. Create a new temporary empty table locally that has the correct
schema (i would suggest same as XSERVICE to keep things simple). Or if
it already exists then just empty before load.

3. Use LoadFromStream into that temp table

(3b. optionally if you want you can even update the data if needed in
temp table using normal SQL statements etc)

4. use SQL to insert records into actual xservice table selecting from
temp table - you can insert as is or use any sql


Most of this can be queries though using table instance i think would be
easier in some steps


1. You have this already working so all OK and you can use query of course

2. I would personally use table instance here since dbisam has a very
handy CopyTable command that can duplicate existing table (without data)
i.e.
....
myTbl.TableName := 'XSERVICE';
myTbl.CopyTable('','tempxserviceload', False);
....

or if it exists you can use EmptyTable or SQL to delete records


3. I personally would use table instance again like in my previous posting

....
myTmpTbl.TableName = 'tempxserviceload';
myTmpTbl.Open;
myTmpTbl.LoadFromStream(aStream);


(3b. tempxserviceload is normal table so optionally run any sql you want
to update or clean it etc)

4. Insert using something like this

INSERT INTO XSERVICE (columns or omit if all)
SELECT (columns or * for all) FROM tempxserviceload


Raul
Thu, May 21 2020 10:02 AMPermanent Link

Norman Rorke

Raul,

Thanks for your thoughts. It seems to make sense.

I will give it a go first thing in the morning.

It is 2am in New Zealand.

Regards
Norman
Page 1 of 2Next Page »
Jump to Page:  1 2
Image