The ContentsTab editing mechanism cannot be used as-is in the SQL tab
because:
- it assumes that only one table it being displayed, and
- it assumes that all columns in the table are being displayed, as well
as any pseudo-column key fields (e.g. ROWID, OID, etc).
When a cell is edited, it uses a prepared statement of the form
"UPDATE <table> SET <column>=<value> WHERE <list all fields with
current values>"
There are some gotchas here, such as fields that cannot be used in WHERE
clauses, but I'm confident that this will work 99% of the time, and
those times when there is a problem will be caught (e.g. "This will
update 3 rows. Do you wish to proceed?"). This approach was used
because people told us that the updateable result set feature was not
implemented in all DBMSs.
While that mechanism is adaquate for the ContentsTab, it obviously won't
work in the SQL tab because the user can do joins or limit the output to
only a few columns in a table. Given that kind of SQL statement, I
might (or might not - I haven't thought about it) be able to identify
which table to update, but I'm pretty sure that we would not be able to
limit the update to the desired row(s).
If the library uses updateable result sets, then we might want to
include it and find a way to disable editing when that feature is not
available in the DBMS. If it uses another mechanism, I'll be interested
in learning how it works.
In any case, I think it would be nice if we could make the user
interface work the same for editing the tables in the SQL tab as it is
in ContentsTab. The ContentsTab now handles data in a
data-type-specific way, assists with editing, allows export/import and
operating on data with an external program, etc. We can re-use many of
the classes to do the same thing in the SQL tab, but we will need a
different mechanism for checking whether the update is safe and for
actually doing the update.
So that's what's up with the ContentsTab editing and why it can't be
immediately used for the SQL tab. I've described these issues in a
separate email to Stan, and since your note showed an interest I thought
I'ld fill you in too.
Glenn
Maury Hammel wrote:
> Stanislaw:
>
>> We have a little library here that we have built, that can be used to
>> display the results of a select querry in JTable and allows to edit the
>> data, cell-by-cell.
>>
>> I plan to create a plugin out of it. There is no data edit plugin so
>> far, is there?
>
>
> There isn't a plugin for that, but it is similar to what is currently
> developed in the core of SQuirreL itself (editing from the Contents tab).
>
> It sounds like your plugin will allow you to edit the results of a SQL
> statement entered in the SQL tab. Is that correct? If that is so, and
> the Contents tab editing capability can't/isn't planned to be added to
> the SQL tab results, it would be useful.
>
>
> Maury...
|
