Database GUI logic

[hershrev]

Thanks, Jan.
On Sunday, February 8, 2004, at 07:37 AM, Jan Schenkel wrote:

> --- 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.
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
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>