Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 11 to 19 of 19 total |
Help with using LoadFromStream & SaveToStream with Query |
Thu, May 21 2020 11:17 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 AM | Permanent Link |
Raul 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Raul 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 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 >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 Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, March 28, 2024 at 06:05 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |