2

I'm writing an extension that needs to make a bunch of temporary tables and copy data around. I think CRM_Core_DAO::executeQuery() only executes one semicolon-delimited statement at a time. Is there a function somewhere that'll let me run them all in one go?

2

Yes, there is such a function; but you shouldn't use it.

Make each query separately and check the result.

Avoid functions which make multiple SQL queries in one action, as they make it harder for your application logic to handle unexpected cases.

  • CRM_Core_DAO::executeQuery() executes a single query against the DB - use this.
  • CRM_Utils_File::sourceSQLFile() does what your question asks - but don't use this.

Why not CRM_Utils_File::sourceSQLFile()?

The pattern of loading a .sql file in as a single set of queries via CRM_Utils_File::sourceSQLFile(); has issues IMO. It's one of the brittle points where I've most commonly seen sites (especially customised ones) fail on upgrades.

The cause for this is that it's a context switch in your code - when you load multiple queries in via sourceSQLFile(), you lose the ability to respond to a failure in any given one. Whether it's the first query which fails, or the second to last, sourceSQLFile is going to do no more than echo back a failure message OR die() - it doesn't return a result or throw an exception.

Code using this approach has to do extra work to handle failures - or assume they never happen, which is what this function IMO encourages. Looks like there are other things to be wary of there (eg no warning if $fileName doesn't exist).

Why is calling each query separately, eg via CRM_Core_DAO::executeQuery(), better?

In contrast, calling each query via CRM_Core_DAO::executeQuery() (or its future replacement) means your code can check the result of each query, and based on that information proceed with further queries OR handle the case of a failure in that executed query.

I think you'll be better served by taking a more diligent approach and calling each SQL query with context to handle the result each time.

0

Please create Extension using civix (civix generate:module)

After creating an Extension just Add a database upgrader / Installer / uninstaller

me@civisys:/var/www/extensions/com.example.myextension$ civix generate:upgrader

In this process you can find a SQL folder in the Extension just add all your SQL query against this folder with file ending with _install.sql

each SQL line should end with semi-colon

Now uninstall you extension and then install it again all SQL would have been executed

Please refer the below link for more Explanation http://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Module+Extension

  • Thanks! That certainly works as a one-time thing, but I'm really after something that'll let me do it regularly. – Andrew West Sep 15 '15 at 10:20
  • What exactly are you trying to do – Ramesh - ARTECH Consultancy Sep 15 '15 at 10:25
  • It's some maintenance queries on custom tables. A bunch of separate queries to check for specific problems. No big deal to split them into separate statements, but I just wondered if it could all be run in one go. – Andrew West Sep 15 '15 at 10:27
  • So you are trying to run a set of query using civicrm Front-end and you dont want to use DB - right – Ramesh - ARTECH Consultancy Sep 15 '15 at 10:29
  • 1
    Please don't hijack civix's autogenerated onInstall() for this purpose - you're using it to call CRM_Utils_File::sourceSQLFile() (see my other answer for why this is not ideal), and as a result you're also going to trigger other install-related code which could have unexpected results for you in the future. – Chris Burgess Nov 14 '15 at 19:32

Your Answer

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

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