Re: Database GUI logic
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 ,
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
checkboxe
Re: Database GUI logic
--- 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.
> 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 ,
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
___
use-revolution mailing list
[EMAIL PROTECTED]
http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Database GUI logic
On Friday, February 6, 2004, at 05:44 AM, Jan Schenkel wrote: --- hershrev <[EMAIL PROTECTED]> wrote: Hi every one. What is the best way to select a DB record into a form view window with multiple fields ? (not with the db manager ,because I don't want to give the user access to the data itself to tamper with, to avoid corruption.) Thanks hershrev Hi Hershrev, Using the database-linked fields doesn't mean you _have_to_ update each field as soon as it is changed. Just link all the fields, checkboxes and option menus, but don't set them to update after editing. Then add a button, go to the Database panel of the property palette, and set its action to 'Update Record' -- and presto, when the user edits data, nothing is saved, until he clicks this button. All functions work except update. (PostgreSQL) How do switch between query's ? In other words once I need a SELECT and sometime I need an INSERT and so on. 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? Hope this helped, Thanks hershrev 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
Database GUI logic
Hi every one. What is the best way to select a DB record into a form view window with multiple fields ? (not with the db manager ,because I don't want to give the user access to the data itself to tamper with, to avoid corruption.) Thanks hershrev ___ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
