10
$\begingroup$

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?

Improve this question
$\endgroup$
3
  • 2
    $\begingroup$ 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. $\endgroup$ Commented Apr 18, 2012 at 20:54
  • $\begingroup$ 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. $\endgroup$ Commented Apr 18, 2012 at 20:59
  • 2
    $\begingroup$ In 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. $\endgroup$ Commented Apr 18, 2012 at 21:10

1 Answer 1

Reset to default
5
$\begingroup$

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...

Improve this answer
$\endgroup$
7
  • 1
    $\begingroup$ +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. $\endgroup$ Commented Apr 18, 2012 at 21:12
  • $\begingroup$ @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. $\endgroup$ Commented Apr 18, 2012 at 21:25
  • $\begingroup$ @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. $\endgroup$ Commented Apr 18, 2012 at 21:32
  • $\begingroup$ @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 :) $\endgroup$ Commented Apr 18, 2012 at 21:35
  • 3
    $\begingroup$ @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). $\endgroup$ Commented Apr 18, 2012 at 21:42

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.