Is there a function that will execute multiple SQL statements?

Question

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?

Answer 1

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.

Answer 2

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