OT] how to update 2 values in each of 3000 records in MySQL

[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 <tvogelaar at 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 at 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 at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>