Executing a SQL query remotely

This might be one of the most basic questions, but I am a bit of a HeidiSQL noob so I hope you'll bear with me. I am running a batch file that needs to do some stuff with a database on a server not on a local machine. I need to change a value in the database before I can do that, so that the site the database uses can tell it's temporarily unavailable. I used to do that with Python, but keeping (and updating) Python just for that one task is just too much of a hassle.

Here's the query I need to run before the batch file does its main task:

UPDATE cursing.version SET Accessible = 1 WHERE version.Accessible = 0 LIMIT 1 ;

and here's the query I need to run after the work is done:

UPDATE cursing.version SET Update = " + time.strftime("%d %B %Y") + ", Accessible = 0 WHERE version.Accessible = 1 LIMIT 1 ;

(the time.strftime thing is Python for writing the current date in the database so the site can display when it was last updated)

So how would I go about this in HeidiSQL using its CLI possibilities?

I realise I did't say I actually do not want the GUI to display.

HeidiSQL cannot execute a query with a pure command line. You need at least one click or keyboard shortcut to do that. Also, the GUI is always there.

I'd say you would be more happy when using "mysql.exe" command line. That supports executing queries, on remote servers, and does not have a GUI.

Is that a Windows executable, and where may it be found? Or is it part of HeidiSQL? Apologies for my noob questions ;-)

Not part of HeidiSQL. That's part of the MySQL and MariaDB server. Just download the zip file, extract it, and execute bin\mysql.exe from a shell.

Sorry, here's the link: https://dlm.mariadb.com/browse/mariadb_server/192/1288/winx64-packages/

Thank you for your time and patience!

Here's an example command line which logs in and executes some queries:

mysql -h127.0.0.1 -uroot --execute"UPDATE cursing.version SET...;"

You can also give it a password, although doing that on the command line creates some security issues:

mysql -h127.0.0.1 -uMyUser -pMyPassWord --execute"UPDATE cursing.version SET...;"

Might I be to bold as to ask you how I would be able to do the time.strftime("%d %B %Y") thing in a DOS batch file?

Just use the DATE_FORMAT SQL function instead. I suppose this is what you want:

... SET Update = DATE_FORMAT(NOW(), '%d %M %Y') WHERE ...

That gives me "19 May 2022" here, similar to your strftime() call.

Once again, thanks! I can handle some above-basic SQL stuff but the information you gave me is kind of above my pay grade as it were.

One more question though:

mysql.exe --host=xxxxx --user=xxxxx --password=xxxxx --database=xxxxx --execute="UPDATE `cursing`.`version` SET `Update` = DATE_FORMAT(NOW(), '%d %M %Y'), `Accessible` = '0' WHERE `version`.`Accessible` = '1' LIMIT 1

does not actually write "19 May 2022" in the cell. In fact, it writes "M Y" there. The cell is a 32 length VARCHAR.

What am I doing wrong?

Oh the percent characters are treated by the shell (cmd), at least if you doing that in a .cmd or .bat file?

In a script file, you need to duplicate any percent character, unless it's a shell variable:

mysql.exe --host=xxxxx --user=xxxxx --password=xxxxx --database=xxxxx --execute="UPDATE `cursing`.`version` SET `Update` = DATE_FORMAT(NOW(), '%%d %%M %%Y'), `Accessible` = '0' WHERE `version`.`Accessible` = '1' LIMIT 1

Right, that works (I am using a .bat file). Works fine now. Again, thanks very much for helping me with stuff that isn't even directly HeidiSQL-related. I'll do a donation soon.