Database GUI logic

[Jan Schenkel]

--- hershrev <hershbp at realtorsgroup.us> wrote:
> All functions work except update. (PostgreSQL)

Can you pinpoint under which circumstances it doesn't
work? A recipe or even a sample stack would help the
RunRev crew fix the problem.

> How do switch between query's ? In other words once
> I need a SELECT and 
> sometime I need an INSERT and so on.

If you need to execute a query without changing the
sql that is already in the query, use the undocumented
command
  revExecuteWithQuery <pQueryName>,<pSQL>
For example, suppose we have a query named "Snafu",
and we want to execute an SQL statement in the
database it is connected to :
--
  put "INSERT INTO Foo VALUES('BAR')" into tSQL
  revExecuteWithQuery "Snafu",tSQL
--

> Also the bottom line about the same code needs to be
> written as put 
> revSelect(or Form)Query(......).into field a put 
> revSelectQuery(....).into field b and so on and then
> to write an UPDATE 
> SQL on all concatenated fields with revExecuteSQL
> and with some playing 
> around its also portable to different db's.
> Or maybe to put all the info at once into a variable
> or an Array and 
> the select from it cell by cell?

I'm not sure I quite understand the first part of what
you're getting at, but it is always possible to save
data in an array or a custom property set as the user
makes changes.
Then at the click of a button, you can go through the
values in the array or custom property set, and build
an sql statement that you can then execute to make all
the changes at once.

Put the following into your card script :
--
on closeField
  # first check if it's a db-linked field
  if the cREVGeneral["database"] of the target is true
then
    # extract which field it is linked to
    put the cREVDatabase["linkcolumn"] of the target
into tColumnName
    # now save the new data in custom prop set
    set the uDBChanges[tColumnName] of me to the text
of the target
  end if
end closeField

on UpdateDB
  # save the currently selected custom prop set
  put the customPropertySet of me into tOldCPSet
  set the customPropertySet of me to "uDBChanges"
  # check which fields have been changed
  put the customKeys of me into tColumnNames
  # prepare data for the final sql statement
  repeat for each line tColumnName in tColumnNames
    put the uDBChanges[tColumnName] of me into
tNewData
    put tColumName & "=" & tNewData & comma after
tFieldsAndValuesList
  end repeat
  delete char -1 of tFieldsAndValuesList
  # exercise : get the primary keys and table
  put "FOO" into tTable
  put "foo_id" into tPrimaryKey
  put "12345" into tPKValue
  # now merge all this into the sql statement
  put merge("UPDATE [[tTable]] SET
[[tFieldsAndValuesList]] WHERE
[[tPrimaryKey]]=[[tPKValue]]") into tSQL
  # finally execute the sql statement
  revExecuteWithQuery "Snafu",tSQL
  # restore the selected custom prop set
  set the customPropertySet of me to tOldCPSet
end UpdateDB
--

I'll leave it as an exercise to react to mouseUp on
checkboxes, combo boxes, etc. While the above is
geared at using the built-in database-linked controls,
you can use this technique to collect data for UPDATE
and INSERT statements.
The sql statements built this way can then be executed
with the 'revExecuteSQL' command, as long as you feed
it an existing databaseID.

> Thanks hershrev
> 

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!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html