Error getting tags :
error 404Error getting tags :
error 404 revDataFromQuery | revDocs | RunRev
Welcome Guest (Log in)
Product Edition
Version
revDataFromQuery
Basics
Expander triangle
Type
Function
Syntax

revDataFromQuery([columnDelim],[rowDelim],databaseID,SQLQuery[,varsList])

Introduced
1.1.1
Changed
2.9
Environment
Desktop, Web and Server
Platform Support
MacOS,Mac OS X,Windows,Linux
Security
Disk,Network
Summary
Gets records from a database according to a SQL query and places the resulting data in a variable, without creating a record set (database cursor).
Examples

revDataFromQuery(, , tConnectionId, the text of field "Query")
get revDataFromQuery(comma, return, tConnectionId, tQuery)
get revDataFromQuery(tab, return, tConnectionId, "SELECT * FROM myTable WHERE id = :1", "tCustomerDetails[id]")

Additional Comments
Expander triangle

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

Parameters:

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

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

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

The SQLQuery is a string in Structured Query Language.

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.

Value:

The revDataFromQuery function returns the data in the records selected by the SQLQuery, with the records delimited by the rowDelim and the database fields within each record delimited by the columnDelim.

Comments:

It is convenient to use the revDataFromQuery function, instead of revDatabaseQuery, when you want to obtain the data for use but don't need to retain a reference to the records that the data came from. The revDataFromQuery function executes the SQLQuery, gets the records found by the query, closes the record set created by the 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 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 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 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 revDataFromQueryfunction is part of the Database library. To ensure that the function works in a standalone application, you must include this custom library when you create your standalone. In the Inclusions section of the General screen of the Standalone Application Settings window, make sure the "Database Support" checkbox is checked and the database drivers you are using are selected in the list of database drivers.

Changes to Revolution:

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

The revDataFromQuery synonym was added in version 2.0.

User Comments
Expander triangle