Hi, thanks so far to all for your suggestions.
The database is from an online shop (xtCommerce) I have to update the products table which has no indexes: 1. setting a product active/inactive 2. setting the shipping time of a product (on stock, not on stock, ordered) 3. adjust the available quantity of the product. What i have so far: I retrieve a list of all "active" products with available quantity and also the ordered quantity (if ordered from supplieer) from our local merchandise management system (MS SQL server). I retrieve a list of all "active" products with quantity and shipment status from the online store (MySQL server). I then compare both lists and create a list of products which have changed and so have to be updated. I will try both variants: A standalone with direct access to the db and also the combination standalone and .irev. And i will try updating record for record and also 10/20/30 in one step. Regards, Matthias Am 18.05.2011 um 18:08 schrieb Pete: > There shouldn't be any problem accessing a mysql database over a network if > the administrator has allowed you access to it. Hard to say whether that's > "the right way". Updating values in 3000 records shouldn't be a very time > consuming activity but as Terry said, it's hard to say without knowing a lot > more about your db structure. Are you putting the same values into all 3000 > records? If not, how many different values are there and how do you > identify which records are to be updated for each value? Probably would > also need to know if you have indexes set up in your database which would > speed up the retrieval of the records to be updated. > Pete > Molly's Revenge <http://www.mollysrevenge.com> > > > > > On Tue, May 17, 2011 at 11:56 PM, Terry Vogelaar <tvogel...@de-mare.nl>wrote: > >> Hi Matthias, >> >> I don't think you should call this OT; sounds on topic to me. >> >> Of course it is hard to come up with a solution when we know so little >> about this particular database and the goal you try to accomplish with it. >> But you might reconsider the architecture of the database. If the 3000 >> records have 3000 different values in these columns; your approach is fine. >> But when there is a lot of overlap in those values, you might be benefitted >> making optimal use of relational databases. For example, if you have 60 >> value pairs, repeated over and over again in these columns, you replace the >> 2 columns with 1, containing an ID number for another table. In that other >> table, you put 60 records with an ID and a value pair in each record. So >> when you change 1 record in this table, all of the 3000 records linked to >> this one, change with it. Again, it is hard to determine if this tip makes >> any sense in your case, but it might be useful. >> >> It should be possible to access an online DB from a standalone. Never done >> it personally, though. Using irev for it can be used as well. You can >> exchange data between the standalone and the irev file using POST (take a >> look at the post command in the LiveCode dictionary). In other words, you >> can fake a HTML form in the standalone. So the irev file handles the data as >> if it receives a filled in web form. It then can process it and put it in >> the database. >> >> Whether you change record after record, or 50 in one step, depends on the >> values. It is certainly possible to write a massive SQL instruction. But it >> might save your sanity when you change them one by one in a repeat loop, >> with a much simpler SQL query. However, if those 50 records can be filtered >> easily, and if they share values, combining might make sense. Hard to say. >> >> Terry >> >> Op 18 mei 2011, om 07:36 heeft use-livecode-requ...@lists.runrev.com het >> volgende geschreven: >> >>> Hi, >>> >>> I have to update 3000 records in a mysql db very often. I just have to >> change only 2 values in each of the 3000 records. >>> I have a desktop app, which creates the values which then have to be put >> in that mysql database. This db is not local, but resides on an on-rev >> server. >>> I could activate remote access for the mysql db and could limit this >> access to our static ip address which our isp assigne to us to let the >> standalone >>> app to update the values directly. But is that the right way? Should i >> do it directly from my standalone or should i do it through an irev script? >>> >>> If 2nd, how would i parse the values to the script. I know how to connect >> from an irev script to a mysql db. That would be no problem. >>> But i never sent data from a standalone to an irev script. >>> >>> Should i update record after record or can i update lets say 50 records >> in one step? Even with different values? >>> >>> Could someone help? >>> >>> >>> Regards, >>> >>> Matthias >> >> _______________________________________________ >> use-livecode mailing list >> use-livecode@lists.runrev.com >> Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >> http://lists.runrev.com/mailman/listinfo/use-livecode >> >> > _______________________________________________ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
