Re: [Squirrel-sql-users] problem editing

[gwg <gw...@co...>]

Hi Mark,
The WHERE clause is constructed in a class named ContentsTab (I think) 
somewhere under the app code.  For each column in the table, that class 
calls the appropriate class in the fw/dataviewer/cellcomponent (I think) 
directory, which decides how that column is to be handled.  For columns 
that cannot be used in a WHERE clause (eg: BLOB), it does not include 
it.  If a column type is not known to SQuirreL (eg: an Oracle-specific 
column that does not correspond to a standard SQL data type) the column 
is not included.  Thus, if you are using a lot of Oracle-specific data 
types, you might see a lot of variation in which columns are in the 
WHERE clause.  (By the way, the same approach is used for extracting 
data from the columns and for updating them.)

I am not familiar with the SDO GEOMETRY data type, so I am guessing that 
it is Oracle-specific.  If so, that might explain some of your problems.

At one point we were thinking about including the necessary hooks for 
DB-specific plugins to be able to include DB-specific data types.  I'm 
not absolutely certain, but I'm pretty sure that we did do that.  If so, 
then the problem could be fixed by updating the Oracle-specific plugin 
to tell SQuirreL how to handle the SDO GEOMETRY data type.

Glenn


Mark Neher wrote:

> Hi All,
> 
> I've been looking at the Oracle edit issues that various people have 
> been helping out with.  The common thread among rows that I can't update 
> at this point is a non-null SDO_GEOMETRY field (we use Oracle Spatial 
> heavily.)  I've found that for rows that I can't edit, I can make them 
> work by
> a) removing the geometry column
> b) setting the geometry column to null or
> c) removing the geometry from the where clause using the Squirrel GUI, 
> as someone suggested trying earlier while we were still puzzling out the 
> date issues.
> Obviously, only option c) is realistic in a real world scenario, but I'm 
> just exploring.
> 
> I had originally suspected the geometry fields as a possible issue, as 
> someone earlier in this thread had mentioned CLOBs and BLOBs as 
> potential problem areas for updating.  I had been able to successfully 
> update rows with non-null geometries, however.  Today, I have been able 
> to verify that the current snapshot fixes the date issue, but now trips 
> on (only some) rows containing non-null geometries.
> 
> I turned log4j up to debug, which dumps the sql being used to perform 
> the updates to the log.  Looking at these queries, there seems to be a 
> wide variation in the number of fields used in the where clause of these 
> updates, and the success of the updates has to do with (and likely did 
> even before the date fix) whether the geometry field is included.  Note 
> that I am not specifically excluding any fields from the where clause 
> with the GUI at this point.
> 
> As a couple of examples, I update a row of table AAA in my own schema.  
> It updates successfully, even with a non-null geometry, because the 
> where clause only contains one of the 24 fields.  When I update a row in 
> another copy of the schema, it also succeeds; this time, however, the 
> where clause contains 23 of the 24 fields, neatly excluding the geometry 
> field that would have caused trouble.  Again, I am excluding no fields 
> through the GUI.
> 
> Table BBB: I update in my own schema and succeed.  There are three 
> geometries, two of them non-null, and the where clause contains only 
> three of 40+ fields.  When I try to update the same table in another 
> schema, it fails.  Most, but not all, of the fields are included in the 
> where clause in this case; one of the geometries is included, and trips 
> me up.  If I exclude that field through the GUI, I can update successfully.
> 
> At this point, editing works great for me.  The date fix was the big 
> stumbling block; I can get around the current issue by excluding the 
> problem fields in the GUI.  I am curious, though, as to how the where 
> clause is constructed; it was mentioned in this thread that all of the 
> columns in the table are used to form a huge where clause, and looking 
> at the debug output there seems to be quite a bit of variation in the 
> fields selected instead of all columns all the time.
> 
> I don't imagine that Oracle Spatial support is a high priority at this 
> point; if anybody is working on it and wants some help with testing, you 
> can drop me an email.
> 
> Regards,
> 
> Mark
> 
> 
>