|
From: Colin B. <col...@te...> - 2002-10-01 03:53:58
|
> > 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 > constructslike rowID, why not go have a look at the metadata and > get the primary key > column(s) and base the query on that? Two main problems. Firstly you wouldn't believe how many people create tables and don't define primary keys for them and secondly there are a couple of drivers that don't return primary key information in the metadata. The second problem will go away with time but the first problem unfortunately is here to stay :-( -- Colin Bell http://squirrel-sql.sf.net ----- Original Message ----- From: "Maury Hammel" <mh...@cr...> Date: Tuesday, October 1, 2002 2:25 am Subject: [Squirrel-sql-develop] Re: Suggestions for improving Squirrel > >> - 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 > constructslike 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 > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf > _______________________________________________ > Squirrel-sql-develop mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop > |
