OK, sounds like you have to update each record individually based on a product code of some sort which I'd guess is probably the primary key. If that's the case, updating should be very fast. Don't know if updating 1 record at a time would be faster than batching the updates into 10/20/30 at a time. Pete Molly's Revenge <http://www.mollysrevenge.com>
On Wed, May 18, 2011 at 1:42 PM, Matthias Rebbe <runrev260...@m-r-d.de>wrote: > 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-request@lists.runrev.comhet > >> 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 > > _______________________________________________ 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
