Tell me more ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.
up vote 10 down vote favorite
2

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?

share|improve this question
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 Apr 18 '12 at 20:54
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 Zaslavsky Apr 18 '12 at 20:59
2  
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. – Leonid Shifrin Apr 18 '12 at 21:10

1 Answer

up vote 5 down vote accepted

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

share|improve this answer
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 Apr 18 '12 at 21:12
@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. Apr 18 '12 at 21:25
@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 Apr 18 '12 at 21:32
@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 Apr 18 '12 at 21:35
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 Apr 18 '12 at 21:42
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.