Database Query functions and commands.

phaworth · Post by **phaworth** » Tue Jul 07, 2009 7:35 pm

Trying to use some of the database query functions and commands listed in the user guide without much luck.

I don't see any of the functions ending in "OfQuery" listed in the Dictionary and they don't appear to do anything. For example, if I execute the following code

put revCurrentRecordOfQuery into tnumber
answer information tnumber

... tnumber contains the string revCurrentRecordOfQuery.

Janschenkel · Post by **Janschenkel** » Tue Jul 07, 2009 9:13 pm

You have to pass the name of an automated query that you setup beforehand; open your stack, go to the Tools menu, pick the Database Query Builder item and you get a wizard screen to setup your automted queries.
Click the '+' button to add a new query; then setup the connection parameters (Rev actually pools the connections, so don't worry about having to create multiple queries to the same database); when the connection works, go to the next tab and enter a SELECT query.
Now you can drag fields and other controls onto your stack, and use the Database panel in the Inspector palette to hook your controls to the automated query that you setup earlier. And it is those queries that they refer to in the User Guide, when describing the revCurrentRecordOfQuery and other functions.
So assumling you have setup an automated query "AllCustomers", you would

Code: Select all

answer information revCurrentRecordOfQuery("AllCustomers")

I hope this quick tour helped to clarify the use of the functions with an 'OfQuery' suffix; automated queries are a very fast way to setup a frontend to browse the contents of a database.

Jan Schenkel.

phaworth · Post by **phaworth** » Tue Jul 07, 2009 9:26 pm

Uhh, I already did all that and data is being displayed on my card from the database. Maybe the way I tried to get the current record (using the put command) is why I'm having problems?
Pete

Janschenkel · Post by **Janschenkel** » Wed Jul 08, 2009 5:54 am

Well, your original post had the following code:

put revCurrentRecordOfQuery into tnumber
answer information tnumber

And this will indeed return just "revCurrentRecordOfQuery" because the Revolution compiler doesn't see it as a function call, but as a string literal variable, creating a new variable named "revCurrentRecordOfQuery" with content "revCurrentRecordOfQuery".
So I suggested that you needed to pass the name of your automated query:

answer information revCurrentRecordOfQuery("AllCustomers")

Unfortunately, I got a little carried away about the automated queries and obfuscated my actual reply, I guess

Jan Schenkel.

phaworth · Post by **phaworth** » Wed Jul 08, 2009 6:26 am

Thanks. I thought it might be something like that. I'll give that a try. Still not sure why these functions aren't listed in the dictionary thought. I looked under the Database object - maybe they're somewhere else?
Pete

Janschenkel · Post by **Janschenkel** » Wed Jul 08, 2009 1:21 pm

Well, the UserGuide was the first to mention these functions and commands - my theory is that RunRev was reluctant to open up the API for the database linked controls, as they are/were considering changes to this API.
It's a powerful tool, but there's room for improvement and expansion, which would take quite some time to get it "just right" - and with the browser plug-in and other new features in version 4, I'm sure they have their work cut out for them...

Jan Schenkel.

phaworth · Post by **phaworth** » Wed Jul 08, 2009 8:10 pm

Making some progress here but still not working for me.

I set up a Query named "All Products". In the card script, I have the following code to try to figure out what's hapening:

Code: Select all

global gConID
global gRsetID
on openCard
global gConID
global gRsetID
on openCard
   put revConnectionOfQuery("All Products") into gConID
   put revCursorOfQuery("All Products") into gRsetID
   answer information revCurrentRecordOfQuery("All Products")
   revMoveToPreviousRecord gRsetID
   answer information revCurrentRecordOfQuery("All Products")
   revGoToRecordOfQuery "All Products",10
   answer information revCurrentRecordOfQuery("All Products")
end openCardend openCard

The first answer command shows the current record to be 94 (the number of records in the query).

The second answer shows the record number to be 93, as expected after moving to the previous record but the data on the card is still from record 94.

The third answer shows the record number back to 94 again .I expected it to be 10 because of the revGoToRecordOfQuery command.

I think I got all this right per the manual, but obviously I'm doing something wrong!

Pete

phaworth · Post by **phaworth** » Wed Jul 08, 2009 11:45 pm

I have an update to my last post. It seems that all the revMoveTo.... commands work just fine but the "...OfQuery" don't.

Even when I move the to a different record successfully with something like revMoveToRecord, the fields on my card that are Linked to the query and column don't change.

I've also found that referring to a query to populate a pulldown button menu doesn't work. I followed the instructions in the manual for doing this but what appears in the pulldown menu is whatever is specified in the Basic Properties Menu Items box. Trying to do this is further complicated because the Database tab of the properties inspector contains a Link section and a Menu Items section, both of which allow a Query and Column to be specified. The manual says to fill in the Menu Item section, but this is greyed out and only the Link section is available.

It seems like I will have to code the SQL in a script and not use the Query Builder - kinda disappointing because that's a lot more work. Unfortuantely, the development system I came form allowed all these things, Queries, fields linked to columns, and menu population, to wobe specified very simply and reliably.

It's still possible I'm doing something wrong since I am very new to Revolution but I can't for the life of me figure out what!

Pete

Janschenkel · Post by **Janschenkel** » Thu Jul 09, 2009 6:11 am

Be very careful when mixing the 'query' commands and functions with the 'standard' database library calls - for instance, getting the cursor of a query and moving it back and forth directly may seriously confuse the automated query and prevent it from refreshing the linked controls.
Try quitting Revolution, reopen your stack, and only use the commands revGoFirstRecordOfQuery, revGoPrevRecordOfQuery, revGoNextRecordOfQuery, revGoLastRecordOfQuery, revGoToRecordOfQuery to traverse the query result set. Using those commands will ensure that linked controls are refreshed - and you can even link buttons to do the First/Prev/Next/Last movements.

The problem with the pulldown menu buttons is an annoying bug in the Inspector palette. See this forum post for a patch: http://forums.runrev.com/phpBB2/viewtopic.php?t=2014

Jan Schenkel.

phaworth · Post by **phaworth** » Thu Jul 09, 2009 7:11 am

Thanks Jan. Unfortunately, most of the commands you mention are not documented in the User Guide or the Dictionary so I'm not sure how to use them. I've tried them with the name of the query as a paramter and also with the Recordset ID - either way, they have no effect, the current record remains the last one in the record set.

Haven't looked at the patch link yet but will try that also.

I've reported all this toi customer support.

Thanks for your help.

Pete

phaworth · Post by **phaworth** » Thu Jul 09, 2009 6:54 pm

I got a response from customer support that I'm running into a bug with the Query Builder when used with SQLite and to either use a different db or program the SQL commands directly.
Pete