Mathematica SQL command that counts records in table

0

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

Ariel Sepulveda

Posted 2015-06-01T21:01:11.227

Reputation: 1 617

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. – Gordon Coale – 2015-06-01T21:28:39.613

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. – Ariel Sepulveda – 2015-06-01T21:41:26.307

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.

– Gordon Coale – 2015-06-01T22:05:28.403

>

  • Do you think that DatabaseExplorer is good for editing data bases? 2. It would be good to have a more detailed description of what you mean by "serious work".
  • < – Ariel Sepulveda – 2015-06-01T22:17:24.840

    Depends if by editing databases you mean DML or DDL. MMA isn't the right tool for the job for anything other than building a mathe apical a – Gordon Coale – 2015-06-01T22:22:46.480

    It's one of those classic cases where just because you can do something it doesn't mean you should do something. Something I have seen in R that's more relevant is some visual analysis of database stats, query performance, table space stats etc. that would be playing to MMA's strengths a DB editor would not. – Gordon Coale – 2015-06-01T22:30:00.970

    1Re. 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. – C. E. – 2015-06-01T22:39:06.023

    1I 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 ;) – Ariel Sepulveda – 2015-06-01T23:04:29.623

    @Gordon Coale I promised to keep you posted, so you can now take a look at BEST DB Editor (http://www.wolfram.com/products/applications/bestdbeditor/) which before bening a product at the Wolfram Store is an application that I have already used for three different real-life problems in manufacturing, research, and retail analytics.

    – Ariel Sepulveda – 2016-05-11T20:53:15.643

    No answers