I was asked to implement a feature that allows a user to perform a database dump using a push button on one of the FactoryPMI screens. I’ve read up on the dumping process in general and have found that there is really no query to do it. All examples have referenced the ‘mysqldump’ application that comes with the server.
My question is: Am I able to send a system command to the computer running my FactoryPMI gateway, specifically the mysqldump command?
Also, if there is any way I can do this through a SQL query that would be preferred, as the mysqldump route would not provide any feedback to the user.
Yeah, mysqldump isn’t going to help you because you can’t get the client to invoke programs on the server machine.
The only way I can think of to do it remotely is to do a series of:SELECT * INTO OUTFILE 'file_name' FROM tbl_name for each table. Note that the file path will reference a file on the server, not the client machine.
This is my query: “SELECT * INTO OUTFILE ‘C:/manual_backups/2009-09-11, backup of alarmlog, by MK.sql’ FROM alarmlog”
I tried executing it with runQuery, runUpdateQuery, and runPrepStmt. Here are the following errors:
fpmi.db.runQuery
Traceback (innermost last):
File “event:actionPerformed”, line 12, in ?
ValueError: SQL error for “SELECT * INTO OUTFILE ‘C:/manual_backups/2009-09-11, backup of alarmlog, by MK.sql’ FROM alarmlog”: ResultSet is from UPDATE. No Data
fpmi.db.runUpdateQuery
See attached.
fpmi.db.runPrepStmt
See attached.
Now it seems that runQuery will create the file, but it breaks the for loop (I have this query executed multiple times for different tables).
Hmm, well that is a challenge. You should be running this via runQuery, but FactoryPMI expects anything run by runQuery to return a dataset, and SELECT INTO OUTFILE does not. JDBC is not allowing you to run the query as an UPDATE (see error #2). The only thing I can think of is to simply put a try…except inside your loop to catch that error for each query.
The try block didn’t suppress the SQL error. Is there anyway to execute a query and not care about the result set? The confusing thing is that it’s still creating the backup file, but breaking the loop.
or, is there I way I can somehow cause the query to not ultimately return an UPDATE set?
perhaps something like
SELECT * FROM (SELECT * INTO OUTFILE 'C:/manual_backups/2009-09-11, backup of alarmlog, by .sql' FROM alarmlog)
I know the syntax is wrong, but I think it illustrates my idea.
for table in mytables:
try:
fpmi.db.runQuery("SELECT * INTO OUTFILE...")
except ValueError:
pass
No, sorry. This seems consistent to me. Its only breaking after it returns successfully from the database and FactoryPMI is trying to pull a result set out of the respons that it breaks - so the backup has already been created. As for breaking the loop, see above.
[quote="karnick"]or, is there I way I can somehow cause the query to not ultimately return an UPDATE set?
perhaps something like ...[snip]...[/quote]
Interesting idea, it might just work. TIAS (try it and see)
Unfortunately since I cant access the result set I don’t know if the server was able to perform the backup. The only real issues I could run into are if the file already exists and if the disk is full. I’ll just make sure I timestamp everything down to the second to avoid this. Thanks guys.
