How to use database-linked controls

[Jan Schenkel]

--- Bill <bill at bluewatermaritime.com> wrote:
> > You can only pick a format when the "Update after
> > editing" button is checked.
> > What was in the failed query? The only thing I can
> > think of right away is that you didn't set the
> primary
> > key in the query builder.
> 
> The primary key is set (there is a nice informative
> error which tells you to
> set the primary key when you forget) and the field I
> was trying to update
> has "the update after editing" button checked. I
> would want all 64 of the
> fields to use this feature but I am only testing a
> couple of the fields now.
> 
> I tested the exact same query manually and it worked
> perfectly.
> 
> Error message is: "Update failed on the SQL command:
> UPDATE voyages SET
> v_arrival_draft =:1 WHERE voyageID= '150-03-001'
> 
> Revdb error:
> (then nothing more)
> 
> The manual SQL command that works is exactly the
> same except I replace the
> cryptic ":1" as above with the actual draft of the
> ship: "19.3' ". I imagine
> that ":1" is some kind of variable.
> 

The :1 is best explained by the Transcript Dictionary
entry for the 'revdb_query' function. Basically these
are replaced by the content of variables, passed by
their names.
A quick trip to the code leads me to believe that you
will only receive an empty error like this, if the
database fails to return the number of affected rows
-- I don't recall which database you are using ?

> > While it is entirely possible to start from
> scratch,
> > and this will give you full control ovr every
> aspect
> > of the SQL -- but don't completely ignore the
> > database-linked fields, as they give you a quick
> > method to hook a series of fields up to a
> database.
> 
> I agree. This is a truly great time saver and I will
> keep trying to use it
> especially now that I am getting such informative
> answers to help in trouble
> shooting. I also need to know how to insert a
> variable into the Database
> Query Builder so that the record set returned will
> be dependent on a where
> clause that includes that variable. When you
> experiment with putting a
> variable in there it goes into an annoying loop
> telling you that there is no
> such column name. It is difficult to try different
> things because of the
> error loop. Perhaps the variable needs to be in
> quotes or identified
> somehow.
> 

The error loop is high on my to-do list, but I'm doing
this on top of another demanding job so I'm not sure
when I'll get around to it -- hopefully in time for
the next major update.
Variables in SQL queries is not yet possible at this
point. We're looking into a way to provide an
interface for setting this type of dependencies.

> How do I get the database ID thot the automatic
> database-linked query gets
> (which I set to on open card) because if I can grab
> that I can avoid making
> a new connection in the manual queries I am making
> (I tried left joins in
> the Database Query Builder and they didn't work) --
> the combination of
> manual queries and the database-linked fields seems
> to be the best solution
> if I can get the update feature to work.
> 

There is an undocumented function called
'revConnectionOfQuery' which does what you want :
--
on mouseUp
  put "SELECT * FROM foo" into tQuery
  put revConnectionOfQuery("MyQueryName") \
        into tConnection
  put revdb_querylist(tab,return,tConnection,tQuery) \
        into tQueryResult
end mouseUp
--

If you want to learn more about the inner workings of
the database-linked controls, have a look at the
'revDatabase' frontscript :
- open the message box
- use the buttons at the top to go to the 'Front
Scripts'
- turn on the checkbox 'Show Revolution UI Front
Scripts'
- doubleclick the line <button "revDatabase">
But don't rely on these too much, as the main reason
these calls are going to remain undocumented for now,
is that we're contemplating a complete rewrite of the
system, which is bound to break stacks that rely on (a
subset of) these undocumented calls.

Hope this helped,

Jan Schenkel.

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


	
		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail