<doc>
  <legacy_id>1492</legacy_id>
  <name>revExecuteSQL</name>
  <type>command</type>
  <syntax>
    <example>revExecuteSQL <i>databaseID</i>,<i>SQLStatement </i>[,{<i>variablesList</i> | <i>arrayName</i>}]</example>
  </syntax>
  <library>Database library</library>
  <objects>
  </objects>
  <synonyms>
  </synonyms>
  <classification>
    <category>Database Operations</category>
    <category>Standalone Applications</category>
  </classification>
  <references>
    <function tag="revOpenDatabase">revOpenDatabase Function</function>
    <function tag="revQueryDatabase">revQueryDatabase Function</function>
    <function tag="revDataFromQuery">revDataFromQuery Function</function>
    <function tag="revdb_execute">revdb_execute Function</function>
    <function tag="revDatabaseTableNames">revDatabaseTableNames Function</function>
  </references>
  <history>
    <introduced version="2.0">Added.</introduced>
    <changed version="2.9"></changed>
  </history>
  <platforms>
    <mac/>
    <windows/>
    <linux/>
    <ios/>
    <android/>
  </platforms>
  <classes>
    <desktop/>
    <server/>
    <web/>
    <mobile/>
  </classes>
  <security>
    <disk access="???"/>
    <network/>
  </security>
  <summary>Executes a <glossary tag="SQL">SQL</glossary> statement on a <glossary tag="database">database</glossary>.</summary>
  <examples>
    <example>revExecuteSQL myDatabaseID, the text of field "Query", "*bMyVar"</example>
    <example>revExecuteSQL 12, builtQuery, "someArray"</example>
  </examples>
  <description>
    <p>Use the <b>revExecuteSQL</b> <glossary tag="command">command</glossary> to execute a <glossary tag="SQL query">SQL query</glossary> without selecting <glossary tag="record">records</glossary>.</p><p/><p><b>Parameters:</b></p><p>The <i>databaseID</i> is the number <glossary tag="return">returned</glossary> by the <function tag="revOpenDatabase">revOpenDatabase</function> <control_st tag="function">function</control_st> when the database was opened.</p><p/><p>The <i>SQLStatement</i> is a <keyword tag="string">string</keyword> in <glossary tag="SQL">Structured Query Language</glossary>. (Do not include a semicolon at the end of the <i>SQLStatement</i>.)</p><p/><p><b>Note:</b> Some database systems, such as Oracle, are capable of handling a multiple-line <glossary tag="SQL">SQL</glossary> statement.</p><p/><p>The <i>variablesList</i> consists of one or more <glossary tag="variable">variable</glossary> names (or <glossary tag="expression">expressions</glossary> that <glossary tag="evaluate">evaluate</glossary> to <glossary tag="variable">variable</glossary> names), separated by commas. Each variable name may also be the name of an array element, for example "tInputData[id]".</p><p/><p>The <i>arrayName</i> is the name of a single <glossary tag="array">array</glossary> <glossary tag="variable">variable</glossary> whose <function tag="keys">keys</function> are sequential numbers.</p><p/><p><b>Note:</b> The <glossary tag="variable">variable</glossary> names or <i>arrayName</i> must be enclosed in quotes; otherwise, the <glossary tag="variable">variable's value</glossary> rather than its name is passed to the <b>revExecuteSQL</b> <glossary tag="command">command</glossary>.</p><p/><p><b>Value:</b></p><p>The <b>revExecuteSQL</b> command places a return value into the <function tag="result">result</function>, to indicate the outcome of the query.</p><p/><p>For successful queries, the <b>revExecuteSQL</b> command returns the number of rows affected for INSERT, UPDATE and DELETE statements. For all other statements, 0 is returned.</p><p/><p>For unsuccessful queries, an error string is returned, describing the problem.</p><p/><p><b>Comments:</b></p><p>The <i>SQLStatement</i> may contain one or more placeholders, which are sequential numbers <glossary tag="prepend">prepended</glossary> by a colon. The <b>revExecuteSQL</b> <glossary tag="command">command</glossary> substitutes the corresponding item in the <i>variablesList</i> for each of these placeholders. For example, if you have two <glossary tag="variable">variables</glossary> called "valueX" and "valueY", you can use a <i>SQLStatement</i> that includes placeholders as follows:</p><p/><p>revExecuteSQL myID, "insert into mytable values(:1,:2,:1)", "valueX","valueY"</p><p/><p>The content of the variable valueX is substituted for the ":1" in the <i>SQLQuery</i> (in both places where ":1" appears), and the content of valueY is substituted for ":2".</p><p/><p><b>Note:</b> Placeholders can currently only represent <b>data</b> values in the query. The following line for example is incorrect and will cause a syntax error to be returned by the database because a placeholder is being used to represent a column name (:1).</p><p/><p>-- Invalid, will cause SQL syntax error</p><p>revExecuteSQL myID, "insert into mytable(:1, name, date) values (:2, :3, :2)", "tColumnName", "tValueX", "tValueY"</p><p/><p>If you specify an <i>arrayName</i> rather than a list of ordinary <glossary tag="variable">variables</glossary>, the <b>revExecuteSQL</b> <glossary tag="command">command</glossary> substitutes the corresponding <keyword tag="element">element</keyword> of the <glossary tag="array">array</glossary> for each of the placeholders in the query:</p><p/><p>revExecuteSQL myID,"insert into mytable values(:1,:2,:1)","myArray"</p><p/><p>The content of the element myArray[1] is substituted for the ":1" in the <i>SQLQuery</i> (in both places where ":1" appears), and the content of myArray[2] is substituted for ":2".</p><p/><p>To pass binary data in a variable in the <i>variablesList</i>, <glossary tag="prepend">prepend</glossary> "*b" to the variable name. The <b>revExecuteSQL</b> <glossary tag="command">command</glossary> strips the binary marker "*b" and passes it to the database as <glossary tag="binary data">binary data</glossary>, rather than text data.</p><p/><p>To pass binary data in an array element, prepend "*b" to the element's <b>key</b>.</p><p/><p><img src="202653"/> <b>Important!</b> The <b>revExecuteSQL</b> <glossary tag="command">command</glossary> is part of the <glossary tag="Database library">Database library</glossary>. To ensure that the <control_st tag="function">function</control_st> works in a <glossary tag="standalone application">standalone application</glossary>, you must include this <glossary tag="LiveCode custom library">custom library</glossary> when you create your <glossary tag="standalone application">standalone</glossary>. In the Inclusions section of the General screen of the <glossary tag="Standalone Application Settings">Standalone Application Settings</glossary> window, make sure the "Database Support" checkbox is checked and the database drivers you are using are selected in the list of database drivers.</p><p/><p><b>Changes to LiveCode</b></p><p>The ability to specify array elements in the <i>variablesList</i> was added in 2.9</p>
  </description>
</doc>