Re: Database GUI logic

[hershrev]

Thanks, Jan.
On Sunday, February 8, 2004, at 07:37 AM, Jan Schenkel wrote:
--- hershrev <[EMAIL PROTECTED]> 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.
Query 1
SELECT * FROM dataBase A
Field 1= last_name Field 2=  first_name
Field 1, Connected to last name and selected last_name , field 2, 
connected to first name and selected first_name.
Now if update is checked , when modified the text of one of the fields 
it is modified in the db's, if not checked update db, and do it by 
button
I just connect the fields and create 3 buttons, 1 go to previous, 2 go 
to next  and 3 update , I change the info , click the update button and 
the info doesn't change in the db.

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
Qk question, there is already a Query in the query building, what is 
the point of rewriting a Query ? should be
revExecuteWithQuery( "Snafu")  with the query it already contains ?
--

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.
What I meant is , if connecting every field to a Query , and writing 
Query's for them , wouldn't be better to just write a Query to store in 
variable and then in every field put a
on event
   get item 1(for every field a different item)
end event
or for every field a Query for itself e.g. put revFormQuery 
(,,myDbid,"SELECT last_name FROM database WHERE pk ="& myPk)
the pro's , portable , the cons I don't know . If there are please 
enlighten me
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
See what youre trying to do , but isn't cREVGereral [] a profile 
property? because I set a field , connected to the db ,tried it but 
wondering..
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
And finally, I looked over this script a couple of times , I just don't 
grasp exactly what your doing over-here . Yes I got the point . 
concatenating the field names with the data and creating an SQL 
statement. but not the detail  how it should be done , somehow I didn't 
get it . Currently I'm focusing on doing it not with connected fields 
for the reason mentioned above. AND every thing is geared towards a 
standalone app.
I was thinking of some kind of on closeField to put the field name 
after fieldnameVar and the data after dataVar  and like to concatenate 
line by line . The question how do I match both lines of both var's ? I 
sure you have something better then this.
Thanks a million hershrev.
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
_______________________________________________
use-revolution mailing list
[EMAIL PROTECTED]
http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________
use-revolution mailing list
[EMAIL PROTECTED]
http://lists.runrev.com/mailman/listinfo/use-revolution