<doc>
  <legacy_id>1965</legacy_id>
  <name>revDataFromQuery</name>
  <type>function</type>
  <syntax>
    <example>revDataFromQuery([<i>columnDelim</i>],[<i>rowDelim</i>],<i>databaseID</i>,<i>SQLQuery</i>[,<i>varsList</i>])</example>
  </syntax>
  <library>Database library</library>
  <objects>
  </objects>
  <synonyms>
    <synonym>revdb_querylist</synonym>
  </synonyms>
  <classification>
    <category>Database Operations</category>
    <category>Standalone Applications</category>
  </classification>
  <references>
    <command tag="revCloseCursor">revCloseCursor Command</command>
    <command tag="revExecuteSQL">revExecuteSQL Command</command>
  </references>
  <history>
    <introduced version="1.1.1">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>Gets <glossary tag="record">records</glossary> from a <glossary tag="database">database</glossary> according to a <glossary tag="SQL query">SQL query</glossary> and places the resulting data in a <glossary tag="variable">variable</glossary>, without creating a <glossary tag="record set (database cursor)">record set (database cursor)</glossary>.</summary>
  <examples>
    <example>revDataFromQuery(, , tConnectionId, the text of field "Query")</example>
    <example>get revDataFromQuery(comma, return, tConnectionId, tQuery)</example>
    <example>get revDataFromQuery(tab, return, tConnectionId, "SELECT * FROM myTable WHERE id = :1", "tCustomerDetails[id]")</example>
  </examples>
  <description>
    <p>Use the <b>revDataFromQuery</b> <control_st tag="function">function</control_st> when you want to use or display data from a database, but not continue to work with the <glossary tag="record">records</glossary> that contain it.</p><p/><p><b>Parameters:</b></p><p>The <i>columnDelim</i> is a <keyword tag="character">character</keyword>, or an <glossary tag="expression">expression</glossary> that <glossary tag="evaluate">evaluates</glossary> to a <keyword tag="character">character</keyword>. If no <i>columnDelim</i> is specified, columns are delimited by the <constant tag="tab">tab</constant> <keyword tag="character">character</keyword>.</p><p/><p>The <i>rowDelim</i> is a <keyword tag="character">character</keyword>, or an <glossary tag="expression">expression</glossary> that <glossary tag="evaluate">evaluates</glossary> to a <keyword tag="character">character</keyword>. If no <i>rowDelim</i> is specified, rows are delimited by the <constant tag="return">return</constant> <keyword tag="character">character</keyword>.</p><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 <glossary tag="database">database</glossary> was opened.</p><p/><p>The <i>SQLQuery</i> is a <keyword tag="string">string</keyword> in <glossary tag="SQL">Structured Query Language</glossary>.</p><p/><p>The <i>varsList</i> consists of one or more <glossary tag="variable">variable</glossary> names (or expressions that evaluate to <glossary tag="variable">variable</glossary> names), separated by commas. The variable names may also be array elements. </p><p/><p><b>Value:</b></p><p>The <b>revDataFromQuery</b> <control_st tag="function">function</control_st> returns the data in the <glossary tag="record">records</glossary> selected by the <i>SQLQuery</i>, with the <glossary tag="record">records</glossary> <glossary tag="delimit">delimited</glossary> by the <i>rowDelim</i> and the database fields within each <glossary tag="record">record</glossary> <glossary tag="delimit">delimited</glossary> by the <i>columnDelim</i>.</p><p/><p><b>Comments:</b></p><p>It is convenient to use the <b>revDataFromQuery</b> <control_st tag="function">function</control_st>, instead of revDatabaseQuery, when you want to obtain the data for use but don't need to retain a reference to the <glossary tag="record">records</glossary> that the data came from. The <b>revDataFromQuery</b> <control_st tag="function">function</control_st> executes the <i>SQLQuery</i>, gets the <glossary tag="record">records</glossary> found by the <glossary tag="SQL query">query</glossary>, closes the <glossary tag="record set (database cursor)">record set</glossary> created by the <glossary tag="SQL query">query</glossary>, and returns the data.</p><p/><p><b>Important</b>: The <b>revDataFromQuery</b> function should not be used if any of the data being retrieved is binary, doing so will probably produce unexpected results. If you wish to use this function to return things like image data, the data should be encoded before being stored in the database, this could for example be done with the <function tag="base64Encode">base64Encode function</function>. Also remember to specify a <i>columDelim</i> and <i>rowDelim</i> that will not appear in the data. Alternatively, both these problems can be avoided by using the <function tag="revQueryDatabase">revQueryDatabase function</function> to generate a record set, then using <function tag="revDatabaseColumnNamed">revDatabaseColumnNamed</function> to retrieve each field individually.</p><p/><p>The <i>SQLQuery</i> may contain one or more placeholders, which are sequential numbers <glossary tag="prepend">prepended</glossary> by a colon. The <b>revDataFromQuery</b> <control_st tag="function">function</control_st> substitutes the corresponding variable name 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>SQLQuery</i> that includes placeholders as follows:</p><p/><p>get revDataFromQuery(, , myID,"SELECT x,y FROM test WHERE x = :1 AND y = :2", "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>To pass binary data in a variable in the <i>variablesList</i>, <glossary tag="prepend">prepend</glossary> "*b" to the variable name. The <b>revDataFromQuery</b> <control_st tag="function">function</control_st> strips the binary marker "*b" and passes it to the database as binary data, rather than text data.</p><p/><p>local tImageData</p><p>put the text of image "MyImage" into tImageData</p><p>get revDataFromQuery(, , myID, "SELECT size FROM images WHERE imagedata = :1", "*btImageData")</p><p/><p>You can also use the name of a numerically indexed array, instead of a list of variable names. In this case, the elements of the array are substituted for the corresponding placeholders. To pass binary data in an array element, prepend "*b" to the element's <b>value</b>.</p><p/><p>local tImageDataArray</p><p>put "*b" &amp; the text of image "MyImage" into tImageDataArray[1]</p><p>get revDataFromQuery(, , myId, "SELECT size FROM images WHERE imagedata = :1", "tImageDataArray")</p><p/><p>To pass an asterisk as part of the query, substitute a percent sign (%). For example, to use the query "SELECT * FROM Customers WHERE Cust.Name Like '*Inc.'", use a statement like the following:</p><p/><p>get revDataFromQuery(2, "SELECT * FROM Customers WHERE Cust.Name Like '%Inc.'")</p><p/><p>If the query is not successful, the <b>revDataFromQuery</b> <control_st tag="function">function</control_st> <glossary tag="return">returns</glossary> an error message beginning with the <keyword tag="string">string</keyword> "revdberr,". You can test for success by checking whether the first item of the returned value is "revdberr".</p><p/><p><img src="202653"/> <b>Important!</b> The <b>revDataFromQuery</b> <control_st tag="function">function</control_st> 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:</b></p><p>The ability to specify array elements in the <i>varsList</i> was added in 2.9.</p><p>The <b>revDataFromQuery</b> synonym was added in version 2.0.</p>
  </description>
</doc>