Answers.Body: <p>Please create Extension using civix (civix generate:module)</p>
  
  <p>After creating an Extension just Add a database upgrader / Installer / uninstaller</p>
  
  <p>me@civisys:/var/www/extensions/com.example.myextension$ civix generate:upgrader</p>
  
  <p>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</p>
  
  <p>each SQL line should end with semi-colon </p>
  
  <p>Now uninstall you extension and then install it again all SQL would have been executed </p>
  
  <p>Please refer the below link for more Explanation
  <a href="http://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Module+Extension" rel="nofollow">http://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Module+Extension</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-14T19:32:55.533Z
Answers.Text: Please don't hijack [civix's autogenerated `onInstall()`](https://github.com/totten/civix/blob/2e7975d9274f0920597604e785b61835145e3f1f/src/CRM/CivixBundle/Resources/views/Code/upgrader-base.php.php#L246) 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.
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-15T10:20:44.343Z
Answers.Text: Thanks! That certainly works as a one-time thing, but I'm really after something that'll let me do it regularly.
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-15T10:25:47.570Z
Answers.Text: What exactly are you trying to do
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-15T10:27:31.093Z
Answers.Text: 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.
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-15T10:29:00.243Z
Answers.Text: So you are trying to run a set of query using civicrm Front-end and you dont want to use DB - right
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-15T10:33:20.740Z
Answers.Text: Well, the queries are one part of the extension - just to keep its data tidy. I could set up a cron to run a totally separate SQL query that runs direct on the db, but the queries take a little while to run and I sometimes get locked table problems.
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-15T10:36:23.877Z
Answers.Text: Then why dont you create an API - set a scheduled JOB call the function CRM_Test_Upgrader_Base::onInstall() - this will pickup the file and will execute whenever you are running the scheduled JOB -
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-15T09:10:02.400Z
Answers.LastActivityDate: 2015-09-15T09:10:02.400Z
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.Body: <p>Yes, there is such a function; but you shouldn't use it.</p>
  
  <p>Make each query separately and check the result.</p>
  
  <p>Avoid functions which make multiple SQL queries in one action, as they make it harder for your application logic to handle unexpected cases.</p>
  
  <ul>
  <li><code>CRM_Core_DAO::executeQuery()</code> executes a single query against the DB - <em>use this.</em></li>
  <li><code>CRM_Utils_File::sourceSQLFile()</code> does what your question asks - <em>but don't use this.</em></li>
  </ul>
  
  <p><strong>Why not <code>CRM_Utils_File::sourceSQLFile()</code>?</strong></p>
  
  <p>The pattern of loading a <code>.sql</code> file in as a single set of queries via <a href="https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/File.php#L285" rel="nofollow"><code>CRM_Utils_File::sourceSQLFile();</code></a> has issues IMO. It's one of the brittle points where I've most commonly seen sites (especially customised ones) fail on upgrades.</p>
  
  <p>The cause for this is that it's a context switch in your code - when you load multiple queries in via <code>sourceSQLFile()</code>, 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, <code>sourceSQLFile</code> is going to do no more than echo back a failure message OR <code>die()</code> - it doesn't return a result or throw an exception.</p>
  
  <p>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 <code>$fileName</code> doesn't exist).</p>
  
  <p><strong>Why is calling each query separately, eg via <a href="https://github.com/civicrm/civicrm-core/blob/master/CRM/Core/DAO.php#L1152" rel="nofollow"><code>CRM_Core_DAO::executeQuery()</code></a>, better?</strong></p>
  
  <p>In contrast, calling each query via <code>CRM_Core_DAO::executeQuery()</code> (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. </p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-14T19:30:12.433Z
Answers.LastActivityDate: 2015-11-14T21:57:33.857Z
Answers.LastEditDate: 2015-11-14T21:57:33.857Z
Answers.UserDisplayName: Chris Burgess
Body: <p>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?</p>
  
Tags: <sql>
Title: Is there a function that will execute multiple SQL statements?
ThreadText: # Is there a function that will execute multiple SQL statements?
  
  (Asked by: username_0 on 2015-09-14)
  
  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?
  
  **Tags:** sql
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2015-11-14. Score: 3 | Accepted answer
  
  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();`](https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/File.php#L285) 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()`](https://github.com/civicrm/civicrm-core/blob/master/CRM/Core/DAO.php#L1152), 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 by username_1 on 2015-09-15. Score: 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>
  
  > **Comments:**
  > **username_0**: Thanks! That certainly works as a one-time thing, but I'm really after something that'll let me do it regularly.  
  > **username_1**: What exactly are you trying to do  
  > **username_0**: 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.  
  > **username_1**: So you are trying to run a set of query using civicrm Front-end and you dont want to use DB - right  
  > **username_0**: Well, the queries are one part of the extension - just to keep its data tidy. I could set up a cron to run a totally separate SQL query that runs direct on the db, but the queries take a little while to run and I sometimes get locked table problems.  
  > **username_1**: Then why dont you create an API - set a scheduled JOB call the function CRM_Test_Upgrader_Base::onInstall() - this will pickup the file and will execute whenever you are running the scheduled JOB -  
  > **username_2**: Please don't hijack [civix's autogenerated `onInstall()`](https://github.com/totten/civix/blob/2e7975d9274f0920597604e785b61835145e3f1f/src/CRM/CivixBundle/Resources/views/Code/upgrader-base.php.php#L246) 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.  
  
  ---
  
  