Re: Reverse Intersect
Bob- Here's another idea: IIRC you've got a remote MySQL database and a local SQLite database. How about converting the local one to MySQL and then you could issue a single SQL query joining the two databases? Seems like that would simplify things, cut down on the amount of data transferred, and let you work with smaller recordsets... -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Reverse Intersect
Hi Mark. That would work too I suppose. I thought about it, but remember that I want this method to be portable, that is I want it to work no matter what two databases are used, and no matter what the amount of data is. Importing a HUGE mySQL database every sync cycle would not be efficient. And once the sync occurs for the first time, the actual amount of data I will need to import is minimal. Trevor found an issue with sqlYoga where named database objects were reloading the database schema every time a call was made using the object. He has fixed that so speeds using sqlYoga are not noticeably any slower than the native Rev API calls. Thanks again Trevor for getting right on that. Because of that my paging method is working really well in conjunction with sqlYoga. My next big hurdle is issuing one query that will update multiple records at a time. I know with SQL I can separate multiple commands with the semi-colon. But I think I read somewhere that I cannot pass a semi-colon to the Rev API, which will mean I will have to update records one at a time. I hope that is not the case, because that will make any method I use with Rev unscalable for large databases. I will have to experiment on that today. Bob On May 27, 2010, at 8:47 AM, Mark Wieder wrote: Bob- Here's another idea: IIRC you've got a remote MySQL database and a local SQLite database. How about converting the local one to MySQL and then you could issue a single SQL query joining the two databases? Seems like that would simplify things, cut down on the amount of data transferred, and let you work with smaller recordsets... -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Reverse Intersect
perhaps COMMIT and ROLLBACK for multiple transactions that may need to be undone. On 27 May 2010 11:33, Bob Sneidar b...@twft.com wrote: Hi Mark. That would work too I suppose. I thought about it, but remember that I want this method to be portable, that is I want it to work no matter what two databases are used, and no matter what the amount of data is. Importing a HUGE mySQL database every sync cycle would not be efficient. And once the sync occurs for the first time, the actual amount of data I will need to import is minimal. Trevor found an issue with sqlYoga where named database objects were reloading the database schema every time a call was made using the object. He has fixed that so speeds using sqlYoga are not noticeably any slower than the native Rev API calls. Thanks again Trevor for getting right on that. Because of that my paging method is working really well in conjunction with sqlYoga. My next big hurdle is issuing one query that will update multiple records at a time. I know with SQL I can separate multiple commands with the semi-colon. But I think I read somewhere that I cannot pass a semi-colon to the Rev API, which will mean I will have to update records one at a time. I hope that is not the case, because that will make any method I use with Rev unscalable for large databases. I will have to experiment on that today. Bob On May 27, 2010, at 8:47 AM, Mark Wieder wrote: Bob- Here's another idea: IIRC you've got a remote MySQL database and a local SQLite database. How about converting the local one to MySQL and then you could issue a single SQL query joining the two databases? Seems like that would simplify things, cut down on the amount of data transferred, and let you work with smaller recordsets... -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution -- - Stephen Barncard San Francisco ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Reverse Intersect
Hi all. I know the Intersect command removes keys in array-1 that don't exist in array-2. What I need however is a command that removes keys in array-1 that DO exist in array-2, so that array-1 contains all the keys I need to add. Why you ask? Why not just use the Union command? Because I need to find all the records in table-a that need to be added to table b. And since I am paging through table a a limited number of records at a time, (to prevent Rev memory overflow for large tables) I cannot simply use an SQL query join, and as I said in a prior post the tables are in disconnected databases. So what I am doing is getting 100 records from table-a as a string, massaging it a bit to form a comma delimited list, then using the SQL IN operator to get what records there are in table-b that are already in table-a. At that point I need to eliminate the table-b keys from the table-a keys, and what I will have left are the keys that need to be inserted into table-b. Simple. See? ;-) So is there any way to do this without a repeat loop? I can always do a repeat loop, but they weary me. sigh ;-) Bob ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Reverse Intersect
if you want to remove duplicate keys - put the imported data immediately into an array. Duplicates are automatically eliminated. On 25 May 2010 14:45, Bob Sneidar b...@twft.com wrote: Hi all. I know the Intersect command removes keys in array-1 that don't exist in array-2. What I need however is a command that removes keys in array-1 that DO exist in array-2, so that array-1 contains all the keys I need to add. Why you ask? Why not just use the Union command? Because I need to find all the records in table-a that need to be added to table b. And since I am paging through table a a limited number of records at a time, (to prevent Rev memory overflow for large tables) I cannot simply use an SQL query join, and as I said in a prior post the tables are in disconnected databases. So what I am doing is getting 100 records from table-a as a string, massaging it a bit to form a comma delimited list, then using the SQL IN operator to get what records there are in table-b that are already in table-a. At that point I need to eliminate the table-b keys from the table-a keys, and what I will have left are the keys that need to be inserted into table-b. Simple. See? ;-) So is there any way to do this without a repeat loop? I can always do a repeat loop, but they weary me. sigh ;-) Bob ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution -- - Stephen Barncard San Francisco ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Reverse Intersect
stephen- Tuesday, May 25, 2010, 3:08:33 PM, you wrote: if you want to remove duplicate keys - put the imported data immediately into an array. Duplicates are automatically eliminated. I was going to suggest something similar, but then thought this seems like a long way to go around for the desired result. Why not take the recordsets from the two database queries and use them on the fly to populate tables in a new database? Then you can run your sql matching statement on the new tables and delete the temporary database when you're done. -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Reverse Intersect
Thanks for the reply Stephen. What I want to end up with is an array of ONLY the keys that exist in the first array that do NOT exist in the second. Otherwise I simply end up with identical arrays that both contain all the keys from the first and the second arrays. I should mention that it is the nature of the data that the second array is some subset of the first, and the goal is to find the records in the first array that are not in the second. Bob On May 25, 2010, at 3:08 PM, stephen barncard wrote: if you want to remove duplicate keys - put the imported data immediately into an array. Duplicates are automatically eliminated. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Reverse Intersect
Hi Mark. Thanks for the interest. My apologies to everyone for the length of this post. First, remember that I am working with paged data. The reason is this: Let's say the source database has a million records. Since I cannot use a join due to the fact they are dissimilar databases that are not connected in any way, I am going to have to query both databases seperately, yes? If I query the destination database for all it's keys I want to use to find records in the source database, on the first pass with an empty destination database I am going to return every single record! Huge dataset, Revolution crashes! But let's say I get away with it and Revolution does not crash (yet). Now I have to issue an SQL statement to the destination database something like SELECT sw_id FROM devices WHERE IN (insert huge million item comma delimited string here). If I don't kill the SQL server, I will probably kill Revolution this time with yet another 1 million record data set! You may say I could use WHERE NOT IN (insert huge million item comma delimited string here), but it doesn't solve my huge dataset and query problem. If I am paging through the data, then I will get every record not in the page, even if they exist in both databases. The solution? Page through the source database a reasonable number of records at a time, say 100 or 1000 or 10,000 depending on what the limits of Revolution and SQL are. Now my second query will return a dataset with matching records, excluding the records that haven't been added yet. At this point I have a dataset with all the records in my page, and another dataset with all the records in my destination database with matching keys, a subset of my paged data. Now I need to find out what those keys are that are in my page dataset that are not in my destination dataset. Hence my need for a reverse intersect command. My strategy of working with one record at a time proved to be cumbersome, especially with a remote SQL server where internet lag plays a part. Whether I use a temporary table or memory variables to work with the data seems irrelevant, except that the SQL database is remote, remember? Lag issues. The less individual queries I make, the better the performance will be. Bob On May 25, 2010, at 3:18 PM, Mark Wieder wrote: stephen- Tuesday, May 25, 2010, 3:08:33 PM, you wrote: if you want to remove duplicate keys - put the imported data immediately into an array. Duplicates are automatically eliminated. I was going to suggest something similar, but then thought this seems like a long way to go around for the desired result. Why not take the recordsets from the two database queries and use them on the fly to populate tables in a new database? Then you can run your sql matching statement on the new tables and delete the temporary database when you're done. -- -Mark Wieder mwie...@ahsoftware.net ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Reverse Intersect
In the time it took to write the original email you could have put the keys of array1 into keysOne combine array2 using cr and tab set the itemDel to tab repeat for each line LNN in array2 if item 1 of LNN is not among the lines of keysOne then put LNN cr after newArray2 end repeat filter newArray2 without empty split newArray2 using cr and tab -- and of course - put getResidualArray(array2, array1) into array2 function getResidualArray array2, array1 code return newArray2 end getResidualKeys Hope this helps J On May 25, 2010, at 2:45 PM, Bob Sneidar wrote: I know the Intersect command removes keys in array-1 that don't exist in array-2. What I need however is a command that removes keys in array-1 that DO exist in array-2, so that array-1 contains all the keys I need to add. Why you ask? Why not just use the Union command? Because I need to find all the records in table-a that need to be added to table b. And since I am paging through table a a limited number of records at a time, (to prevent Rev memory overflow for large tables) I cannot simply use an SQL query join, and as I said in a prior post the tables are in disconnected databases. So what I am doing is getting 100 records from table-a as a string, massaging it a bit to form a comma delimited list, then using the SQL IN operator to get what records there are in table-b that are already in table-a. At that point I need to eliminate the table-b keys from the table-a keys, and what I will have left are the keys that need to be inserted into table-b. Simple. See? ;-) So is there any way to do this without a repeat loop? I can always do a repeat loop, but they weary me. sigh ;-) Bob Jim Ault Las Vegas ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
