Updating or Inserting as Necessary

In this step, we'll create an update SQL method and show how to selectively update or insert data.

A step-by-step lesson in using PloneFormGen to read, insert and update rows in a single SQL table.

Page 5 of 6.

Using the ZMI, create a Z SQL Method inside your form folder with the id testUpdateRow. For its parameters, set:

Connection ID: Choose your test database connection.
Arguments: Add "uid", "string1" and "string2" on separate lines, without quotes.

Then, specify the SQL code:

UPDATE simple_db
SET
    <dtml-sqltest string1 type=string>,
    <dtml-sqltest string2 type=string>
WHERE <dtml-sqltest uid type=int>

Notice the use <dtml-sqltest ...> for the SQL set id=value lines. This is just a hack that uses sqltest where we could have instead written lines like "string1=<dtml-sqlvar string1 type=string>".

Now, we've got to solve a simple problem. How do we update our table under some circumstances, and insert new values under others?

Remember how we set "-1" as the default value of our hidden "uid" form field? If we've read a record, uid will have changed to match a real row. If it's "-1", that means that we started with a clean form rather than values read from a table row.

Let's use that knowledge in a simple switchboard script with the id doUpdateInsert:

request = container.REQUEST
form = request.form

if int(form.get('uid', '-1')) >= 0:
    # we have a real uid, so update
    context.testUpdateRow()
else:
    context.testCreateRow()

Now, go to the [overrides] pane of your form folder and set "here/doUpdateInsert" as the AfterValidationScript.

Believe it or not ... you're done. Time to go back and repeat the process with your own table. Don't forget to add lots of sanity-checking code along the way.

Next: A Note on Security

Previous: Reading a Row, Filling in the Fields

Sections

Personal tools

Updating or Inserting as Necessary