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