Name: revDataFromQuery

Synonyms: revdb_querylist

Type: function

Syntax: revDataFromQuery([<columnDelim>],[<rowDelim>], <databaseID>, <SQLQuery> [, <varsList>])

Summary:
Gets <record|records> from a <database> according to a <SQL query> and
places the resulting data in a <variable>, without creating a 
<record set>.

Associations: database library

Introduced: 1.1.1

OS: mac, windows, linux, ios, android

Platforms: desktop, server, mobile

Security: disk, network

Example:
revDataFromQuery(, , tConnectionId, the text of field "Query")

Example:
get revDataFromQuery(comma, return, tConnectionId, tQuery)

Example:
get revDataFromQuery(tab, return, tConnectionId, \
"SELECT * FROM myTable WHERE id = :1", "tCustomerDetails[id]")

Parameters:
columnDelim:
A character, or an expression that evaluates to a character. If no
columnDelim is specified, columns are delimited by the tab character.

rowDelim:
A character, or an expression that evaluates to a character. If no
rowDelim is specified, rows are delimited by the return character.

databaseID:
The number returned by the revOpenDatabase function when the database
was opened.

SQLQuery (string):
A string in Structured Query Language.

varsList:
The varsList consists of one or more variable names (or expressions that
evaluate to variable names), separated by commas. The variable names may
also be array elements.

Returns:
The <revDataFromQuery> <function> returns the data in the
<record|records> selected by the <SQLQuery>, with the <record|records>
<delimit|delimited> by the <rowDelim> and the database fields within
each <record> <delimit|delimited> by the <columnDelim>.

Description:
Use the <revDataFromQuery> <function> when you want to use or display
data from a database, but not continue to work with the <record|records>
that contain it.

It is convenient to use the <revDataFromQuery> <function>, instead of
revQueryDatabase, when you want to obtain the data for use but don't
need to retain a reference to the <record|records> that the data came
from. The <revDataFromQuery> <function> executes the <SQLQuery>, gets
the <record|records> found by the <SQL query|query>, closes the 
<record set> created by the <SQL query|query>, and returns the data. 
>*Important*: The <revDataFromQuery> 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
> <base64Encode> <function>. Also remember to specify a columDelim and
> <rowDelim> that will not appear in the data. Alternatively, both these
> problems can be avoided by using the <revQueryDatabase> <function> to
> generate a record set, then using <revDatabaseColumnNamed> to retrieve
> each field individually.

The <SQLQuery> may contain one or more placeholders, which are
sequential numbers <prepend|prepended> by a colon. The
<revDataFromQuery> <function> substitutes the corresponding variable
name in the variablesList for each of these placeholders. For example,
if you have two <variable|variables> called "valueX" and "valueY", you
can use a <SQLQuery> that includes placeholders as follows:

    get revDataFromQuery(, , myID, "SELECT x,y FROM test " && \
    "WHERE x = :1 AND y = :2", "valueX", "valueY" )

The content of the variable valueX is substituted for the ":1" in the
<SQLQuery> (in both places where ":1" appears), and the content of
valueY is substituted for ":2".

To pass binary data in a variable in the variablesList, <prepend> `"*b"`
to the variable name. The <revDataFromQuery> <function> strips the
binary marker `"*b"` and passes it to the database as binary data, rather
than text data.

    local tImageData
    put the text of image "MyImage" into tImageData
    get revDataFromQuery(, , myID, \
    "SELECT size FROM images WHERE imagedata = :1", "*btImageData" )

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 value.

    local tImageDataArray
    put "*b" & the text of image "MyImage" into tImageDataArray[1]
    get revDataFromQuery(, , myId, \
    "SELECT size FROM images WHERE imagedata = :1", "tImageDataArray" )

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:

    get revDataFromQuery(2, \
    "SELECT * FROM Customers WHERE Cust.Name Like '%Inc.'" )

If the query is not successful, the <revDataFromQuery> <function>
<return|returns> an error message beginning with the <string>
"revdberr,". You can test for success by checking whether the first item
of the returned value is "revdberr".

>*Important:*  The <revDataFromQuery> <function> is part of the 
> <Database library>. To ensure that the <function> works in a 
> <standalone application>, you must include this 
> <LiveCode custom library|custom library> when you create your 
> <standalone application|standalone>. In the Inclusions pane of the 
> <Standalone Application Settings> window, make sure both the 
> "Database" library checkbox and those of the database drivers you are 
> using are checked.

Changes:
The ability to specify array elements in the varsList was added in 2.9.

References: revCloseCursor (command), revExecuteSQL (command),
function (control structure), base64Encode (function), function (glossary),
revQueryDatabase (function), revDatabaseColumnNamed (function),
LiveCode custom library (glossary), return (glossary),
variable (glossary), database (glossary),
record set (glossary), record (glossary),
Standalone Application Settings (glossary),
standalone application (glossary), SQL query (glossary),
delimit (glossary), prepend (glossary), string (keyword),
Database library (library)

Tags: database

