revQueryDatabase / revCurrentRecord

[Jan Schenkel]

--- baleareninsel at gmx.net wrote:
> Holá everybody
> 
> As a new member of the RunRev users I try to learn
> for buildng up a running Aplication. I´m using the
> actual Studio-version
> 
> Since 3 weeks now I try to find out, how to get a
> field from a MySQL-Database into a variable without
> any success. Now I ask anybody of you to give me a
> helpfull hand.
> 
> What I do is:
> Put rev_dbconnect ("mySQL", "localhost",
> "mytest_DB", "root",) into DB_ID
> put revQueryDatabase (DB_ID, "SELECT * from Table_1
> where Number = 2") into var_1 ## Number is the first
> Field in the Table Table_1 which is ok
> Put revCurrentRecord (var_1) into var_2 ## Var_2 is
> always 0 (Zero) doesn´t matter which Number I
> select. Why???
> 
> Now I think next would be:
> put field_2 into Variable_1 ## field_2 is a field in
> the DB
> 
> But that does not work.
> 
> Please, for you it´s just a moment for me sonce now
> hours and days to solve this problem
> 
> Thank you in advanced
> 
> Horst Peters
> 

Hi Horst,

You're on the right track but seem to make a few
assumptions that make me think you're coming from a
Filemaker or FoxPro background ;-)

When you connect to the database, you receive a
connection ID (good) that you can use to read data
from the database or manipulate the data stored
therein.

There are two functions for getting information out of
the database:

1) The first function is 'revDataFromQuery', which
returns a single variable, with the selected fields of
the records that fit the criteria in your SQL query.

Example:
##
  put "SELECT cust_id,cust_name FROM Customers" into
tQuery
  put
revDataFromQuery(return,tab,tConnectionID,tQuery) into
tData
  put tData into field "Table Field"
##
You would have a return-and-tab-delimited list like
this:
000001<tab>Jane Doe<return>
000002<tab>Jeff Doe<return>
000003<tab>John Doe<return>
...

This function is great if you're looking to display
some data easily and quickly. But it would be hard to
parse out individual fields, and that's where the next
function comes to play.

2) The second function is 'revQueryDatabase', which
executes the query and returns a cursor ID - this
doesn't contain the data itself, but rather an
identifier for a result set - a collection of records.

To determine how many records there are and which is
the current one
- revNumberOfRecords(<cursor id>)
- revCurrentRecord(<cursor id>)

To navigate the records in the result set, you use the
commands:
- revMoveToFirstRecord <cursor id>
- revMoveToPreviousRecord <cursor id>
- revMoveToNextRecord <cursor id>
- revMoveToLastRecord <cursor id>

To determine what fields are in those records, you
use:
- revDatabaseColumnCount(<cursor id>)
- revDatabaseColumnNames(<cursor id>)

To fetch the individual fields of the current record,
you use:
- revDatabaseColumnNumbered(<cursor id>,<column
number>)
- revDatabaseColumnNamed(<cursor id>,<column name>)

To release the result set from memory, you use:
- revCloseCursor <cursor id>

It is important to understand that Revolution is
blissfully unaware of the data in record sets, and
doesn't automatically map field names onto variable
names

So you'll have to use the last two functions to get
the data from the current record and put it into a
variable for further processing.
So going back to the previous example:
##
  put "SELECT cust_id,cust_name FROM Customers" into
tQuery
  put revQueryDatabase(tConnectionID,tQuery) into
tCursorID
  put revDatabaseColumnNamed(tCursorID,"cust_id") into
tCustID
  put revDatabaseColumnNamed(tCursorID,"cust_name") \
        into tCustName
  answer "Customer ID:" && tCustID & return & \
        "Customer Name:" && tCustName
  -- do various other things with the data in the
cursor
  -- ...
  -- release the used resources
  revCloseCursor tCursorID
##

After reading the two fields from the current record
in the result set, you display an answer dialog box,
which would display something like:
    Customer ID: 000001
    Customer Name: Jane Doe

--

Another important thing to note is that you cannot
modify the records that are in the result set - you'll
need to use separate INSERT, UPDATE and DELETE queries
to modify what's in the database.

To do that, you'll use the 'revExecuteSQL' command.

For more information, the Revolution User Guide
describes the intricacies of database interaction -
starting on page 218.
You can access the User Guide from the Revolution
Documentation - it's a long PDF document well worth
the time to read.

The latest version of the guide can be downloaded from
this URL:
<http://downloads.runrev.com/userguide/userguide.pdf>

Of course, if anything is unclear, don't hesitate to
ask your question on this mailing list or the forum.
We're all here to help.

Hope this helped,

Jan Schenkel.

Quartam Reports for Revolution
<http://www.quartam.com>

=====
"As we grow older, we grow both wiser and more foolish at the same time."  (La Rochefoucauld)


 
____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com