Icon View Thread

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

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Norman


I think your basic problem stems from the fact that you are expecting DBISAM to somehow transfer TEMPTABLE to the location of session2. It won't. TEMPTABLE is created and will stay in the directory associated with session1. The whole point of using a stream is to give you an easy way of transferring the data using memory that can be "shared" between the two sessions. Think of it as creating a stringlist, writing the code to take the data from session1.query, load it into the stringlist then write the code to take the data from the stringlist and load it into a existing session2.table.

You could equally well do the following

1. make sure session2.XSERVICES, and that it has the same fields as in session1.query (more or less fields means a bit more programming)
2. run the query - don't bother about TEMPTABLE - just do a standard SELECT
3. write a small loop to copy the data from session1.query to session2.XSERVICES

something like (totally untested)

procedure DoTheMove;
var
Cntr:integer;
begin
session1.query.sql.text := 'SELECT * FROM XSERVICES WHERE whatever';
session1.query.execsql;
session2.XSERVICES.EmptyTable;
session1.query.first;
while not session1.query.eof do begin
session2.XSERVICES.Insert;
for Cntr := 0 to session1.query.Fields.Count-1 do session2.Fields[Cntr].Assign(session1.query.Fields[Cntr]);
session2.XSERVICES.Post;
session1.query.next;
end;
end;

Roy Lambert
Fri, May 22 2020 1:33 AMPermanent Link

Norman Rorke

Hi Roy & Raul,

Thanks to you both.

Following on Raul's suggestion this is how I solved my issue. In short I used a query to load the required on-line data into memory stream and then transferred the data into a temporary table. I am still interested to know why I could not use a query to retrieve the memory-stream data. The documentation says it is possible but supplied no examples.

//**** Save required data to stream *****//
aSql := 'SELECT S.*, C.ACCT_CODE, C.CATEGORY FROM XSERVICE S  '+
      'JOIN XCLIENTS C ON C.ACCT_D = S.ACCT_ID  +   aWhereClause;

ds := TDBISAMQuery.Create(nil);
try
qry := TDBISAMQuery(ds);
with qry do
begin
  SessionName := serverRemote.sessionname;
  DatabaseName := dbRemote;
  SQL.Clear;
  SQL.Add(aSQL);
  Active := True;
  SaveToStream(aStream);
end;
finally
ds.Free;
end;


//*** Retrieve data from stream ***//
tempTable := CreateFrom(XSERVICE);
with tblBakery do
begin
  DatabaseName:= aLocalDB;
  TableName:=  tempTable;
  LoadFromStream(aStream);
end;

ExecQuery('INSERT INTO XSERVICE SELECT * FROM ' + tempTable);
Fri, May 22 2020 3:44 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Norman


I believe its because the query you attempted to run was invalid - the table TEMPTABLE did not exist in session2

Roy Lambert
Fri, May 22 2020 7:34 AMPermanent Link

Norman Rorke

Roy,

Can you give me a simple example of how I can use a query with the LoadFromStream function.

In my code above I used a query to store cloud data into a stream. How can I use a query to transfer the data into a local table?
Fri, May 22 2020 8:12 AMPermanent Link

Raul

Globestar Systems

Team Elevate Team Elevate

On 5/22/2020 7:34 AM, Norman Rorke wrote:
> Roy,
>
> Can you give me a simple example of how I can use a query with the LoadFromStream function.
>
> In my code above I used a query to store cloud data into a stream. How can I use a query to transfer the data into a local table?
>

Norman,

Only usage with query i can think of is that you can have a live query
referencing table you want to load into  (i.e. select * from xservice)
and use LoadFromStream on that (LoadFromStream is in common base class
of both TDBISAMTable and TDBISAMQuery).

While data is in the stream it's not available for SQL commands since
it's a binary stream of bytes.

https://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphiwin32&version=10R&topic=Loading_Saving_Streams

Raul
Fri, May 22 2020 9:07 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Raul

Personally, I can't think why anyone would want to stream into a query but this "To load data from a stream into a query result set, the TDBISAMQuery SQLproperty must be populated with a SELECT SQL statement and the Active property must be True." from the page that you reference suggests its possible. My guess is the opposite of yours ie I think you need a canned result set so it can be overridden.

My version of DBISAM is very old (4.26) so its not really sensible for me to experiment.

Roy Lambert
Fri, May 22 2020 9:09 AMPermanent Link

Raul

Globestar Systems

Team Elevate Team Elevate

On 5/22/2020 9:07 AM, Roy Lambert wrote:
> Personally, I can't think why anyone would want to stream into a query but this "To load data from a stream into a query result set, the TDBISAMQuery SQLproperty must be populated with a SELECT SQL statement and the Active property must be True." from the page that you reference suggests its possible. My guess is the opposite of yours ie I think you need a canned result set so it can be overridden.
>
> My version of DBISAM is very old (4.26) so its not really sensible for me to experiment.

Roy,

Good point - both i think might make sense.

Canned if you want to load and then manipulate data for some
intermediate result and it all goes away when you close the query.

Live if you want the actual table data to change.

I can give this a try on weekend or maybe Norman will

Raul
Sun, May 24 2020 1:52 AMPermanent Link

Norman Rorke


> Personally, I can't think why anyone would want to stream into a query.

2 points here:

1. The manual says it is possible. Is the manual wrong?

2. We use queries exclusively in our applications for the following reasons:
 - We often have to adapt our applications to work with whatever in-house back-end the customer is using. Our applications talk to a COM server that can connect to a number of common back-ends. This is only possible because of sql. The COM server adjusts the queries depending on the database it is connecting to.

 - Queries make it easier to diagnose database problem reported by users. Our applications contain a Trace function that is part of the business object. Once triggered, the Trace function will log all database activity (queries). The log is a bunch of queries that we can use offsite with a SQL Query tool to track what the users were doing.

Raul,
>I can give this a try on weekend or maybe Norman will

I have already tried without success.

Can I also respond to the following statement you make earlier:

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

My reasoning: Because a memory stream is raw binary you can us it to store whatever you like, including custom structures and hence DBIASM tables. After reading what the manual said about using queries to read from of previously created stream I assumed that this was what DBISAM was doing. But it seems that is not the case.

I will let the issue rest for the time being since Raul's suggestion has solved my immediate problem and I will only be using DBISAM for my current project.I will however be interested in any further thought you may have.

Thanks for your help.
Norman
Sun, May 24 2020 4:33 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Norman

>1. The manual says it is possible. Is the manual wrong?

Probably not - Tim, over the many years I've been using Elevatesoft products, has about the best set of documentation I've seen.  Generally covers what's needed and is pretty clear unlike some that swamp you with gibberish that only makes sense if you don't need to use the manual Smiley

>Can I also respond to the following statement you make earlier:
>
>> Treat the stream as "bunch of bytes" - there is no table name nor SQL really involved when loading - just open table/query and load.
>
>My reasoning: Because a memory stream is raw binary you can us it to store whatever you like, including custom structures and hence DBIASM tables. After reading what the manual said about using queries to read from of previously created stream I assumed that this was what DBISAM was doing. But it seems that is not the case.

Unfortunately, your reasoning is partly wrong. You can store whatever you like, but you also have to get it out.

If you read the manual it specifically states "Active property must be True". You can only open a query if the sql it contains is valid. DBISAM's error message (Table or backup file TEMPTABLE does not exist.) told you what the problem was. You seem to want to ignore that and my subsequent comments on the same issue. The query must be active because if it isn't there is no result set to be streamed into, and no structure available either. The reason following Raul's solution worked is that the table (XSERVICES) existed thus providing a receptacle of the right structure for the stream.

It may be possible to write a procedure to accept the stream, analyse it, create a temporary table with the right structure, and transfer the data into it. LoadFromStream is not that - it requires the table (temporary as in a query or permanent) with the correct structure to be there. I don't know if the structure is in the stream which would make this possible or if its just a wadge of raw bytes which would mean it won't be possible.

Roy
Previous PagePage 2 of 2
Jump to Page:  1 2
Image