[Zope] ZSQL update operations

[Lee Marzke]

On 13-Mar-01 Casey Duncan wrote:
> Lee Marzke wrote:
>> 
>> On 12-Mar-01 Casey Duncan wrote:
>> > Lee Marzke wrote:
>> >>
>> >> Hi,
>> >>
>> >> I'm displaying a set of data from a ZSQL method.  The showStatus
>> >> method below works just fine.  I would now like to have a link
>> >> on each data item allowing an ZSQL update or delete operation.
>> >>
>> >> Can this be done by passing args to the ZSQL method vai the
>> >> URL?    I'm a beginner with Zope/ZSQL btw...
>> >>
>> >> I am partly unsure when the ZSQL method should define a parameter
>> >> ( for instance to pass something by a URL, does that variable need
>> >>   to be a paramenter )
>> >>
>> >> Thanks,
>> >>
>> >> Lee Marzke <lmarzke@kns.com>
>> >>
>> >> == statusALL  DTML method ==
>> >> <dtml-in "objectValues('Folder')">
>> >>   <dtml-var showStatus>
>> >> </dtml-in>
>> >>
>> >> The following method shows data where the revname column is equal
>> >> == showStatus DTML Method ==
>> >> <h3><!--#var title_or_id--></h3>
>> >> <table width="80%" border=0 cellpadding=5 cellspacing=0>
>> >> <tr><th>ID</th><th>Name</th><th>Begin</th><th>End</th><th>Status</th></tr>
>> >> <!--#in "show_pending()" -->
>> >>    <!--#if sequence-even -->
>> >>      <tr bgcolor="#eeeeee">
>> >>    <!--#else -->
>> >>      <tr bgcolor="#ffffff">
>> >>    <!--#/if -->
>> >>       <td><!--#var requestid --></td>
>> >>       <td><!--#var username --></td>
>> >>       <td><!--#var starttime --></td>
>> >>       <td><!--#var endtime --></td>
>> >>       <td><!--#var comment --></td>
>> >>       <td><a href="<!--#var
>> >>       "updateRequest.absolute_url()"-->">Update</a></td>
>> >>   </tr>
>> >> <!--#/in -->
>> >> </table>
>> >>
>> >> ===showPending()  ZSQL method ===
>> >> SELECT requestid, revname, username, starttime, endtime, comment, status
>> >> FROM tableR <!--#sqlgroup where-->
>> >>   <!--#sqltest revname op=eq type=string -->
>> >>   <!--#and -->
>> >>   status = 0
>> >> <!--#/sqlgroup-->
>> >>
>> >> Lee Marzke <lmarzke@kns.com>             12-Mar-01, 10:58:07
>> >> Kulicke & Soffa Industries
>> >> 2101 Blair Mill Rd                       +1 215 784-6217
>> >> Willow Grove, PA 19090                   +1 215 784-6014 fax
>> >>
>> >
>> > Anything you want passed to your ZSQL method must be defined as an
>> > argument. They come from the REQUEST object, so they can be passed
>> > directly from a URL defined variable.
>> >
>> 
>> Unfortunatly, since the "Update" links are created in a loop I can't
>> rely on passin or pushing any variable.  I think I need a way of adding
>> this information onto the links URL, but I don't know how to
>> get this to work.
>> 
> 
> Sorry I didn't get back to you yesterday, things got busy late. Anyhow,
> I
> think this is very much doable.
> 
> The update operation would likely need to got to a form populated from
> your
> existing query. The link would look like this:
> 
> <a href="&dtml.url-updateRequest;?requestid=&dtml-requestid;">Update</a>
> 
> My recommendation for the delete link would be to create a short DTML
> method
> called deleteRequest that calls an SQL method to delete the record then
> redirects
> back to the showStatus page (or whatever page it came from). This can be
> accomplished
> with a bit of URL mangling like so:
> 
> <a href="&dtml-URL;/deleteRequest?requestid=&dtml-requestid;">Delete</a>
> 
> The deleteRequest method would look like so:
> 
> <dtml-call name="sqlDeleteRequest">
> <dtml-call expr="RESPONSE.redirect(URL1)">
> 
> You would then need to create an ZSQL method sqlDeleteRequest with a
> single argument: requestid
> 
> DELETE FROM tableR
> WHERE <dtml-sqltest name="requestid" type="int">
> 

Thanks Casey,

I had almost figured out this myself.  I was origionally trying the 
traversal mangling ( e.g. using "/" instead of "&" and "=" ) and that
along with other error was not going anywhere.

Lee