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

Terry Vogelaar tvogelaar at de-mare.nl
Wed May 18 02:56:44 EDT 2011


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




More information about the use-livecode mailing list