Database GUI logic
hershbp at realtorsgroup.us
Sun Feb 22 17:41:59 CST 2004
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.
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
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
> 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
get item 1(for every field a different item)
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
> 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
> # 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
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
> put tColumName & "=" & tNewData & comma after
> 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.
> use-revolution mailing list
> use-revolution at lists.runrev.com
More information about the use-livecode