DATABASE FUNCTIONS

[Wayne McKenzie]

Let me start by saying I have very little knowledge of mySQL, so what I  
am about to say is probably NOT the best way to do things, but having  
said that, if I with my little knowledge have been able to 'get full  
control' of a mySQL database via Rev, you shouldn't have too much  
trouble.

I've noticed in Rev 2.5 it takes a few more clicks to rummage through  
the inline (not online) Help documentation to get to the details on  
database functions. From the Help menu, select Documentation, click on  
the Objects button at the top of the window. In the left field click on  
the triangle next to Libraries. From here open the 'Database library'  
triangle the click on the 'function' triangle. Terms of most interest  
are likely to be:

revCurrentRecord(recordSetID)
revCurrentRecordIsFirst(recordSetID)
revCurrentRecordIsLast(recordSetID)
revDatabaseColumnNamed(recordSetID,columnName[,holderVariable])
revDatabaseColumnNames(recordSetID)
revDatabaseCursors(databaseID)
revDataFromQuery([columnDelim],[rowDelim],databaseID,SQLQuery[,varsList] 
)
revdb_movenext(recordSetID)
revdb_moveprev(recordSetID)
revNumberOfRecords(recordSetID)
revOpenDatabase(databaseType,host[:port],databaseName, \
    [userName],[password] [, useSSL | \
    valentinaCacheSize,valentinaMacSerial,valentinaWindowsSerial])
revOpenDatabases()
revCloseCursor recordSetID
revCloseDatabase databaseID

and most definitely

revExecuteSQL databaseID,SQLStatement

Once you get into this I am sure it will make what everyone else has  
written 'gel' and you'll quickly be able to pinpoint the functions you  
need to convert your stack from Database Builder to direct function  
calls.

I'll offer two tips:

1) Updating or Inserting Data
To overcome the problem you noted about not knowing whether the data  
you just entered has actually been updated into SQL, I
	i) save the SQLStatement I used in the initial 'revExecuteSQL  
databaseID,SQLStatement' to a global variable
	ii) save the field containing the table primary key to a variable  
(savedPrimaryKeyValue)
	iii) close the current query with 'revCloseCursor recordSetID'
	iv) open a new query with 'revExecuteSQL databaseID,SQLStatement'  
using the same query I saved as a global variable - ie it will match  
the same data assuming your updated or inserted data does not fall  
outside the query range
	v) use 'revdb_movefirst(recordSetID)' to ensure that I am at the first  
record
	vi) return to the record you just updated with a repeat loop like this:

revDatabaseColumnNamed(recordSetID,columnName,currentPrimaryKeyValue)
repeat until currentPrimaryKeyValue = savedPrimaryKeyValue
	revdb_movenext(recordSetID)
	revDatabaseColumnNamed(recordSetID,columnName,currentPrimaryKeyValue)
end repeat

	vii) go about placing the data into the applicable fields

2) Large Amounts of Text
If you are dealing with a lot of text you'll probably find it helpful  
if you 'replace' tabs and carriage returns when you put data into mySQL  
and replace them when you extract the data. I simply do this:

prior to sending the data to mySQL
replace return with " nnn " in field "lots of text" of stack  
"Encyclopedia"
replace tab with " ttt " in field "lots of text" of stack "Encyclopedia"

after querying the database
replace " nnn " with return in field "lots of text" of stack  
"Encyclopedia"
replace " ttt " with tab in field "lots of text" of stack "Encyclopedia"

By doing the above you can use the SQL statement below to create a tab  
delimited text file which you can usefully manipulate with any  
spreadsheet program, or even better open it with a heavy duty text  
editor like BBEdit and run Perl scripts on it. If you leave the tabs  
and carriage returns in place you'll end up with your data spread all  
over the place and unable to do any grep work on it.

SELECT * INTO OUTFILE tab_delimted_text_file FROM really_big_table

You might scratch your head and wonder why you would want to do that  
when all that I could possibly do with spreadsheets and or Perl you  
could do with SQL. I offer the following excuses:

1) I have worked a lot more with Perl, whilst I've only just started  
experimenting with mySQL so I find I can quickly think up a Perl  
solution
2) If I get it wrong and completely stuff up the text file, it doesn't  
matter, the database is untouched - I know I could duplicate tables and  
work on copies to achieve the same, but with a text file I can 'combine  
fields' and change formats without the worry that I'm going to get an  
SQL error or have data disappear (because I've put 15 chars into a  
field I only defined to hold 12).
3) If you want to see the 'Big Picture' and view an entire table at  
once, you could emulate this using a Rev field in table mode, but it is  
very Very VERY slow; much slower than just opening a tab delimited text  
file in a spreadsheet program. Using a Terminal program might be fast,  
but the format is not as nice as a single record per row text file or  
spreadsheet. Also if you update or insert data, you have to do another  
select call and the have to scroll back to where you were. With a text  
file you edit, you save, the cursor remains in the same place.

Hope there is something here you'll find of value

Wayne
"Never ask a man what computer he uses. If it's a Mac, he'll tell you.
If it's not, why embarrass him?" - Tom Clancy