Is there a DatabaseLink command other than SQLExecute that can be used to count the total records in a database table?

  • $\begingroup$ I don't think so. You could load all the rows into MMA with SQLSelect but it would be stupidly inefficient unless you want all the rows anyway. SQLSelect doesn't appear to support Count or Group by operations. Why are you trying to avoid SQLExecute? It's likely to be the most efficient way to query any database as you can write SQL that's native to the source database. So for instance you could include Tuning hints. $\endgroup$ Jun 1 '15 at 21:28
  • $\begingroup$ I'm creating an application (a database editor) that must work on any type of database. Thus, I'm trying to take advantage of the fact that DatabaseLink commands should work on many databases. I think that this having this capability in the DatabaseLink shouldn't be that difficult and it would benefit many users. $\endgroup$ Jun 1 '15 at 21:41
  • $\begingroup$ I dont think it would benefit many at all tbh. There is already the database explorer, besides that anyone doing serious work will be using a database client like Toad. SQLExecute should still return reliable results if you stick to proper ANSI SQL-92 code. $\endgroup$ Jun 1 '15 at 22:05
  • 1
    $\begingroup$ Re. the question at hand SQLExecute["SELECT COUNT(*) FROM " <> tableName] should work in more or less all SQL dialects. If you want to work only through a database layer and not use any SQL at all you could use JLink and interface directly with JDBC or another database layer of your choice, but this is more complicated. $\endgroup$
    – C. E.
    Jun 1 '15 at 22:39
  • 1
    $\begingroup$ I think that you are trying to generalize from your experience, knowledge and personal view of what are the best possible uses for Mathematica. I would agree that using Mathematica for managing millions of records may not be a good idea in most cases. I would also agree with you if you mean that the current functions and tools provided by Mathematica are not attractive if you need automated or GUI based solutions. In fact, that makes my point: Mathematica is great for data analysis but is not that good at keeping data safe and organized. I'm working on that and I'll keep you posted ;) $\endgroup$ Jun 1 '15 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.