Is there a way to print out SQL queries made using DatabaseLink?

10

3

I have a Mathematica batch script which makes some MySQL queries using DatabaseLink to store parameters of the run in a database. Recently, I had a run where these queries failed, and there was no output to indicate why. In the past, when I was using the SQLite interface, I had much success debugging (or at least recovering from errors) by printing out every SQL query made to the log file, but with functions like SQLUpdate and SQLInsert I don't get access to the raw queries. Is there a way I can get DatabaseLink to print them out for me, or at least get access to the raw query so I can print it myself?

David Z

Posted 2012-04-18T20:03:56.913

Reputation: 4 561

2

I don't have time right now to fully describe the procedure, but you can do this using p6spy. The idea is that you have to place the p6spy jar into some place where Mathematica is looking for JDBC drivers, indicate it as a driver for your SQL connection, and configure it such that it uses the real MySQL JDBC driver (using p6spy config file). I used p6spy successfully with Oracle database before, although not in the context of DatabaseLink (I used it with Hibernate). If no one comes with a better answer, I will try to give more details tomorrow.

– Leonid Shifrin – 2012-04-18T20:54:32.040

Huh, sounds a little messy. I will definitely appreciate you posting this answer when you have a chance, but I may just wind up doing everything manually with SQLExecute in this particular case. – David Z – 2012-04-18T20:59:22.763

2In your case, this sounds like a sensible option. When using JDBC directly in Java, one usually uses PreparedStatement-s, so the full SQL query is not seen exactly as it stands. With various ORM-s like Hibernate, this is even worse, since they provide some sort of SQL-like DSLs, and there are even more layers between your code and the final SQL statement. But I guess, for your application, you may just use SQLExecute, as you suggested. – Leonid Shifrin – 2012-04-18T21:10:05.707

Answers

5

If you have full control over the MySQL database I think it lets you log every SQL statement from every client (query-log), which probably is the most simple way to get that information. You could also try to look at or even manipulate the sources, it looks like the relevant code is delivered as clear text in the following file in the Mathematica directory: SystemFiles/Links/DatabaseLink/Kernel/SQL.m. It isn't an easy read though and some of the relevant stuff might be buried in one of the java classes, but there seem to be sources also for those, if you really want to dig deep...

Albert Retey

Posted 2012-04-18T20:03:56.913

Reputation: 22 455

1+1. I am not totally sure, but I am afraid that DatabaseLink implementation (Java part) might use things like PreparedStatement and the like, in which case, there isn't a point in the code where you can intercept the full query. This does not invalidate your query-log suggestion though. – Leonid Shifrin – 2012-04-18T21:12:56.560

@Leonid That's a very good point that I hadn't considered. Fortunately it appears that, as a practical matter, PreparedStatement isn't used in the current version of DatabaseLink, except for postprocessing queries passed in from the Mathematica side. Thus this suggestion (and my implementation given in my answer) should be valid. – Oleksandr R. – 2012-04-18T21:25:55.373

@Oleksandr R. Sorry to disappoint you, but it is. Have a look at the class SQLStatementProcessor.java (which you mentioned), and you will see PreparedStatement used and populated there. This is logical, because not using it in general is considered a bad style in Java (JDBC), and often leads to overhead and errors. – Leonid Shifrin – 2012-04-18T21:32:00.013

@Oleksandr R. Yes, but post-processing is non-trivial, becuase queries sent to PreparedStatement are not complete - they have question marks in places where actual parameters are inserted (and those inserted parameters are often "dressed" in additional quotation marks or otherwise processed). So, in summary, you don't get a full query this way, which you could then execute stand-alone, e.g. through the DB administration UI or command-line. I've worked with JDBC quite a bit in the past, was bitten by this before :) – Leonid Shifrin – 2012-04-18T21:35:34.253

@Leonid Yes, I saw that, but the extent of its usage only goes as far as setting a few query properties such as timeout and validating/substituting in the correct values of the parameters. Hopefully, this part will not go wrong, so since no changes are made to the SQL statement and no prepared statements are used, this usage is IMO not significant. – Oleksandr R. – 2012-04-18T21:36:23.037

@Leonid I see. My unfamiliarity with SQL and JDBC is showing. I should delete my answer. – Oleksandr R. – 2012-04-18T21:37:44.610

3@Oleksandr R. You may be better off not being too familiar with this. I did (and still do) use Java professionally and it is very useful at times (mostly because so many Java libraries are written literally for anything), but I am not fond of it as a language, and IMO it kills the brain if you start using it exclusively and for too long (no intention to start a flame war with anyone reading this, just a personal opinion). – Leonid Shifrin – 2012-04-18T21:42:18.410