Executing a SQL query remotely

Cronos's profile image Cronos posted 2 years ago in General Permalink

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?

Cronos's profile image Cronos posted 2 years ago Permalink

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

ansgar's profile image ansgar posted 2 years ago Permalink

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.

Cronos's profile image Cronos posted 2 years ago Permalink

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

ansgar's profile image ansgar posted 2 years ago Permalink

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.

ansgar's profile image ansgar posted 2 years ago Permalink

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

Cronos's profile image Cronos posted 2 years ago Permalink

Thank you for your time and patience!

ansgar's profile image ansgar posted 2 years ago Permalink

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...;"
Cronos's profile image Cronos posted 2 years ago Permalink

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?

ansgar's profile image ansgar posted 2 years ago Permalink

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.

Cronos's profile image Cronos posted 2 years ago Permalink

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.

Cronos's profile image Cronos posted 2 years ago Permalink

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?

ansgar's profile image ansgar posted 2 years ago Permalink

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
Cronos's profile image Cronos posted 2 years ago Permalink

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.

Please login to leave a reply, or register at first.