[Squirrel-sql-develop] Re: Suggestions for improving Squirrel

[Maury H. <mh...@cr...>]

>> - Edit the data in the table:
>> The archives mentioned that there were problems with doing this.
>> I saw
>> only two problems mentioned, and I have proposed solutions to both...
>>  - How to activate/de-activate the feature?  My suggestion would
>> be to use
>> a checkbox in the Session's SQL Preferences.  Alternatively, we could
>> use two radio buttons labeled "Edit in table" and "Read-only".
>>  - Some DBs do not support updateable ResultSets.  My solution is
>> to not
>> use updateable ResultSets at all!  In my previous tool, my method of
>> updating the DB based on changes to the table was to generate an SQL
>> statement of the form:
>>  UPDATE <table> set <updatedColumn> = <newValue> WHERE
>>  <column1>=<column1Value> AND <column2>=<column2Value> AND ...
>> This should work correctly except in the case where the DB
>> contains 2
>> identical rows.  To minimize that problem, we may be able to
>> include the
>> rowID for DBs that support that feature (I vaguely remember that some
>> DBs do not have that capability).  There may also be a problem
>> with
>> editing BLOBs, CLOBs, and the other binary data types.
>> Are these solutions acceptable?  Are there other problems that I have
>> not recognized?
>
> I like the idea of generating the update statement rather than using
> updateable result sets. For the duplicate row issue it might be worth
> issuing a select count(*) statement prior to the update (with the same
> where clause including rowids if the db supports them) and if the returned
> count != 1 then give an error msg.

Rather than doing the update based on (potentially) propriatory constructs
like rowID, why not go have a look at the metadata and get the primary key
column(s) and base the query on that?


Maury...

--
Maury Hammel
Cronus Technologies Inc.

http://www.cronustech.com