LiveCode Forums.

Posted: **Sun Jul 01, 2012 10:37 pm**

Hello everyone,

I know it is possible to use the function getDataFromQuery to get the records from a database directly into a variable (I've already done that). Now I would like to do the same but using the command revExecuteSQL. Is it possible to execute a query using the mentioned command? I have tried but without any results. This is what I have:

Code: Select all

function databaseGetCountries
   put getDatabaseID() into tDatabaseID
   put "SELECT Country_German FROM country_info" into tSQL
   revExecuteSQL(tDatabaseID,tSQL)
   answer the result with "OK"
end databaseGetCountries

According to the documentation:

The revExecuteSQL command places a return value into the result, to indicate the outcome of the query. For successful queries, the revExecuteSQL command returns the number of rows affected for INSERT, UPDATE and DELETE statements. For all other statements, 0 is returned. For unsuccessful queries, an error string is returned, describing the problem.

I want to make sure that I correctly understood what is stated in the documentation: by 'query' they are referring to any SQL statements.
My questions are:

1.Would the result of an actual query statement be 0?
2. Is it possible to use this command to get something like a cursor or set of results?
3. Do you know a way to get the results of a query as a cursor or a record set?

Thank you very much,
Octavio.

Posted: **Sun Jul 01, 2012 11:00 pm**

You can't use a select statement with revexecutesql as you surmise. 0 will be returned.

look at these commands in the dictionary
revdatafromquery -- returns a flat list
revquerydatabase -- cursor method

as well as these (and many more)
revqueryisatend
revqueryisatstart
revclosecursor
revdatabasecursors

If you open the dictionary, and on the left pane click the library disclosure triangle, you can then select "database" and see what is available.

ohd.mailBUS11Ck wrote:Hello everyone,

I know it is possible to use the function getDataFromQuery to get the records from a database directly into a variable (I've already done that). Now I would like to do the same but using the command revExecuteSQL. Is it possible to execute a query using the mentioned command? I have tried but without any results. This is what I have:
Code: Select all
function databaseGetCountries
   put getDatabaseID() into tDatabaseID
   put "SELECT Country_German FROM country_info" into tSQL
   revExecuteSQL(tDatabaseID,tSQL)
   answer the result with "OK"
end databaseGetCountries
According to the documentation:
The revExecuteSQL command places a return value into the result, to indicate the outcome of the query. For successful queries, the revExecuteSQL command returns the number of rows affected for INSERT, UPDATE and DELETE statements. For all other statements, 0 is returned. For unsuccessful queries, an error string is returned, describing the problem.
I want to make sure that I correctly understood what is stated in the documentation: by 'query' they are referring to any SQL statements.
My questions are:

1.Would the result of an actual query statement be 0?
2. Is it possible to use this command to get something like a cursor or set of results?
3. Do you know a way to get the results of a query as a cursor or a record set?

Thank you very much,
Octavio.

Posted: **Mon Jul 02, 2012 7:45 am**

Code: Select all

revExecuteSQL(tDatabaseID,tSQL)

-> it's for an INSERT, UPDATE, DELETE etc.

To know if the query was successful :

Code: Select all

revExecuteSQL(tDatabaseID,tSQL)
if the Result is not a number then
answer warning "There is an error : "&the Result
else
answer info "Done"
end if

For a query without cusor :

Code: Select all

put "SELECT Country_German FROM country_info" into tSQL
put revDataFromQuery(,,tDatabaseID,tSQL) into myResult
answer myResult

Tip : you can change columns and record delimiters (by default : tab and return)

Code: Select all

put revDataFromQuery("*","|",tDatabaseID,tSQL) into myResult

LiveCode Forums.

Proper use of revExecuteSQL for making queries

Proper use of revExecuteSQL for making queries

Re: Proper use of revExecuteSQL for making queries

Re: Proper use of revExecuteSQL for making queries