|
From: Simon G. <SGR...@fl...> - 2006-02-08 19:53:19
|
I'm guessing that the table you are trying to update has no primary key. In this circumstance, squirrel has no real way of knowing which record it is supposed to update. That is, you are blocked because an update command might update multiple records instead of just the one you edited. Just a guess though. Simon >>> "Mark Neher" <mar...@ho...> 08/02/2006 11:46 am >>> Hey All, I am having trouble editing in SQuirreL. If I make a table editable, it allows me to click in a cell and make changes. When I try to save the changes, a panel pops up saying "This row in the Database has been changed since you refreshed the data. No rows will be updated by this operation. Do you wish to proceed?" Clicking yes yields "No rows updated."; no cancels. Trying to edit using the popup rather than in the cell gives the same results. I don't see any mention of this in the help, or in the mailing lists online. Any ideas ?? BTW, I am running version 2.1 final on a Linux box, and attaching to an Oracle database. Thanks, Mark ------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Do you grep through log files for problems? Stop! Download the new AJAX search engine that makes searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 _______________________________________________ Squirrel-sql-users mailing list Squ...@li... https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
|
From: Mark N. <mar...@ho...> - 2006-02-08 21:00:42
|
No, all the tables in our schema have a primary key. Thanks for the idea, though. Mark >From: "Simon Grantham" <SGR...@fl...> >To: <mar...@ho...>,<squ...@li...> >Subject: Re: [Squirrel-sql-users] problem editing >Date: Wed, 08 Feb 2006 14:53:02 -0500 > >I'm guessing that the table you are trying to update has no primary key. > In this circumstance, squirrel has no real way of knowing which record >it is supposed to update. That is, you are blocked because an update >command might update multiple records instead of just the one you >edited. > >Just a guess though. > >Simon > > > >>> "Mark Neher" <mar...@ho...> 08/02/2006 11:46 am >>> > >Hey All, > >I am having trouble editing in SQuirreL. If I make a table editable, >it >allows me to click in a cell and make changes. When I try to save the > >changes, a panel pops up saying > >"This row in the Database has been changed since you refreshed the >data. >No rows will be updated by this operation. >Do you wish to proceed?" > >Clicking yes yields "No rows updated."; no cancels. Trying to edit >using >the popup rather than in the cell gives the same results. > >I don't see any mention of this in the help, or in the mailing lists >online. > Any ideas ?? BTW, I am running version 2.1 final on a Linux box, and > >attaching to an Oracle database. > >Thanks, > >Mark > > > > >------------------------------------------------------- >This SF.net email is sponsored by: Splunk Inc. Do you grep through log >files >for problems? Stop! Download the new AJAX search engine that makes >searching your log files as easy as surfing the web. DOWNLOAD >SPLUNK! >http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 > >_______________________________________________ >Squirrel-sql-users mailing list >Squ...@li... >https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
|
From: Gerd W. <bir...@t-...> - 2006-02-08 21:26:24
|
Hi Mark, could please create a script of your table together with some inserts that would allow me to reproduce your problem. To create the table DDL script right mouse click the table in the Object Tree and choose the "Create Table Script" menu. To create the insert script write a "SELECT * FROM <yourTable> ... " statement that selects a few rows from your table, place the caret in the statement, hit ctrl+t and choose the sql2ins entry in the tools popup. Thanks Gerd Mark Neher wrote: > > No, all the tables in our schema have a primary key. Thanks for the > idea, though. > > Mark > > >> From: "Simon Grantham" <SGR...@fl...> >> To: <mar...@ho...>,<squ...@li...> >> Subject: Re: [Squirrel-sql-users] problem editing >> Date: Wed, 08 Feb 2006 14:53:02 -0500 >> >> I'm guessing that the table you are trying to update has no primary key. >> In this circumstance, squirrel has no real way of knowing which record >> it is supposed to update. That is, you are blocked because an update >> command might update multiple records instead of just the one you >> edited. >> >> Just a guess though. >> >> Simon >> >> >> >>> "Mark Neher" <mar...@ho...> 08/02/2006 11:46 am >>> >> >> Hey All, >> >> I am having trouble editing in SQuirreL. If I make a table editable, >> it >> allows me to click in a cell and make changes. When I try to save the >> >> changes, a panel pops up saying >> >> "This row in the Database has been changed since you refreshed the >> data. >> No rows will be updated by this operation. >> Do you wish to proceed?" >> >> Clicking yes yields "No rows updated."; no cancels. Trying to edit >> using >> the popup rather than in the cell gives the same results. >> >> I don't see any mention of this in the help, or in the mailing lists >> online. >> Any ideas ?? BTW, I am running version 2.1 final on a Linux box, and >> >> attaching to an Oracle database. >> >> Thanks, >> >> Mark >> >> >> >> >> ------------------------------------------------------- >> This SF.net email is sponsored by: Splunk Inc. Do you grep through log >> files >> for problems? Stop! Download the new AJAX search engine that makes >> searching your log files as easy as surfing the web. DOWNLOAD >> SPLUNK! >> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 >> >> _______________________________________________ >> Squirrel-sql-users mailing list >> Squ...@li... >> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users > > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through log > files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 > _______________________________________________ > Squirrel-sql-users mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users > |
|
From: Mark N. <mar...@ho...> - 2006-02-17 15:26:09
|
I'm back !! And I've found a reproducible bare bones case. The common
thing in all of the rows that I can't edit is a non-null DATE field. Using
the following stripped down table definition:
CREATE TABLE LICENSE
(
NAME VARCHAR2(80),
FILE_UPDATE_DATE DATE
);
and the following data:
insert into license values ('test', null);
insert into license values ('junk', sysdate);
I can edit the 'test' row, as the DATE is null, but I get the previously
mentioned errors when I try to save an edit in the 'junk' row. This is
reproducible in each table in our schema that has a DATE field. BTW, the
same problem doesn't affect TIMESTAMP fields; I can edit rows with non-null
TIMESTAMP data.
Hope this helps,
Mark
>From: Gerd Wagner <bir...@t-...>
>To: Mark Neher <mar...@ho...>
>CC: SGR...@fl..., squ...@li...
>Subject: Re: [Squirrel-sql-users] problem editing
>Date: Wed, 08 Feb 2006 22:26:54 +0100
>
>Hi Mark,
>
>could please create a script of your table together with some inserts that
>would allow me to reproduce your problem.
>
>To create the table DDL script right mouse click the table in the Object
>Tree and choose the "Create Table Script" menu.
>
>To create the insert script write a "SELECT * FROM <yourTable> ... "
>statement that selects a few rows from your table, place the caret in the
>statement, hit ctrl+t and choose the sql2ins entry in the tools popup.
>
>Thanks
>
>Gerd
>
>Mark Neher wrote:
>>
>>No, all the tables in our schema have a primary key. Thanks for the idea,
>>though.
>>
>>Mark
>>
>>
>>>From: "Simon Grantham" <SGR...@fl...>
>>>To: <mar...@ho...>,<squ...@li...>
>>>Subject: Re: [Squirrel-sql-users] problem editing
>>>Date: Wed, 08 Feb 2006 14:53:02 -0500
>>>
>>>I'm guessing that the table you are trying to update has no primary key.
>>> In this circumstance, squirrel has no real way of knowing which record
>>>it is supposed to update. That is, you are blocked because an update
>>>command might update multiple records instead of just the one you
>>>edited.
>>>
>>>Just a guess though.
>>>
>>>Simon
>>>
>>>
>>> >>> "Mark Neher" <mar...@ho...> 08/02/2006 11:46 am >>>
>>>
>>>Hey All,
>>>
>>>I am having trouble editing in SQuirreL. If I make a table editable,
>>>it
>>>allows me to click in a cell and make changes. When I try to save the
>>>
>>>changes, a panel pops up saying
>>>
>>>"This row in the Database has been changed since you refreshed the
>>>data.
>>>No rows will be updated by this operation.
>>>Do you wish to proceed?"
>>>
>>>Clicking yes yields "No rows updated."; no cancels. Trying to edit
>>>using
>>>the popup rather than in the cell gives the same results.
>>>
>>>I don't see any mention of this in the help, or in the mailing lists
>>>online.
>>> Any ideas ?? BTW, I am running version 2.1 final on a Linux box, and
>>>
>>>attaching to an Oracle database.
>>>
>>>Thanks,
>>>
>>>Mark
>>>
>>>
>>>
>>>
>>>-------------------------------------------------------
>>>This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>>>files
>>>for problems? Stop! Download the new AJAX search engine that makes
>>>searching your log files as easy as surfing the web. DOWNLOAD
>>>SPLUNK!
>>>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>
>>>_______________________________________________
>>>Squirrel-sql-users mailing list
>>>Squ...@li...
>>>https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>
>>
>>
>>
>>-------------------------------------------------------
>>This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>>files
>>for problems? Stop! Download the new AJAX search engine that makes
>>searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
>>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>_______________________________________________
>>Squirrel-sql-users mailing list
>>Squ...@li...
>>https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>
>
|
|
From: Maury H. <ml1...@sh...> - 2006-02-17 15:33:57
|
Mark Neher wrote:
> I'm back !! And I've found a reproducible bare bones case. The common
> thing in all of the rows that I can't edit is a non-null DATE field.
> Using the following stripped down table definition:
>
> CREATE TABLE LICENSE
> (
> NAME VARCHAR2(80),
> FILE_UPDATE_DATE DATE
> );
>
> and the following data:
>
> insert into license values ('test', null);
> insert into license values ('junk', sysdate);
>
> I can edit the 'test' row, as the DATE is null, but I get the previously
> mentioned errors when I try to save an edit in the 'junk' row. This is
> reproducible in each table in our schema that has a DATE field. BTW,
> the same problem doesn't affect TIMESTAMP fields; I can edit rows with
> non-null TIMESTAMP data.
This is probably due to the disagreement between Oracle and Java/JDBC
about dates. In Java/JDBC, a DATE (i.e. java.sql.Date) doesn't have a
time value. In Oracle it can. I'm guessing that that it would work if
you changed the insert to:
insert into license values ('junk', trunc(sysdate));
Maury
|
|
From: Jack E. <ja...@pe...> - 2006-02-17 16:32:17
|
I don't have any real help for your actual problem, but as a matter of
nitpicking, java.sql.Date granularity down to milliseconds.
-Jack Eidsness
Maury Hammel wrote:
>
> Mark Neher wrote:
>> I'm back !! And I've found a reproducible bare bones case. The
>> common thing in all of the rows that I can't edit is a non-null DATE
>> field. Using the following stripped down table definition:
>>
>> CREATE TABLE LICENSE
>> (
>> NAME VARCHAR2(80),
>> FILE_UPDATE_DATE DATE
>> );
>>
>> and the following data:
>>
>> insert into license values ('test', null);
>> insert into license values ('junk', sysdate);
>>
>> I can edit the 'test' row, as the DATE is null, but I get the
>> previously mentioned errors when I try to save an edit in the 'junk'
>> row. This is reproducible in each table in our schema that has a DATE
>> field. BTW, the same problem doesn't affect TIMESTAMP fields; I can
>> edit rows with non-null TIMESTAMP data.
>
> This is probably due to the disagreement between Oracle and Java/JDBC
> about dates. In Java/JDBC, a DATE (i.e. java.sql.Date) doesn't have a
> time value. In Oracle it can. I'm guessing that that it would work if
> you changed the insert to:
>
> insert into license values ('junk', trunc(sysdate));
>
>
> Maury
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc. Do you grep through log
> files
> for problems? Stop! Download the new AJAX search engine that makes
> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
> _______________________________________________
> Squirrel-sql-users mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
|
|
From: Maury H. <ml1...@sh...> - 2006-02-17 16:52:11
|
Jack Eidsness wrote: > I don't have any real help for your actual problem, but as a matter of > nitpicking, java.sql.Date granularity down to milliseconds. Yep, you're right. Maybe I was thinking of java.sql.Types.DATE? Anyway, I know Glenn and I had a discussion about this when he implemented the extra stuff in the Data Type Controls tab in the Global Preferences. The Oracle DATE column is identified as a type 91, and a type 91 doesn't allow time in the formatting. Maury |
|
From: Jack E. <ja...@pe...> - 2006-02-17 17:22:17
|
Perhaps with another RDBMS, java.sql.Date ends up working as if it has no time-of-day. Almost all of my experience is with Oracle. After sending my previous mail, I noticed this text in the javadocs: "To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated." I assume they are referring to one of the SQL standards out there, but this normalization is apparently up to individual java developers (or the jdbc driver developers?), and in the case of oracle jdbc, undesirable. -Jack Eidsness Maury Hammel wrote: > Jack Eidsness wrote: >> I don't have any real help for your actual problem, but as a matter of >> nitpicking, java.sql.Date granularity down to milliseconds. > > Yep, you're right. Maybe I was thinking of java.sql.Types.DATE? > > Anyway, I know Glenn and I had a discussion about this when he > implemented the extra stuff in the Data Type Controls tab in the Global > Preferences. The Oracle DATE column is identified as a type 91, and a > type 91 doesn't allow time in the formatting. > > > Maury > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through log > files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 > _______________________________________________ > Squirrel-sql-users mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
|
From: Rob M. <Rob...@co...> - 2006-02-18 14:54:33
|
Jack Eidsness wrote: > > I assume they are referring to one of the SQL standards out there, but > this normalization is apparently up to individual java developers (or > the jdbc driver developers?), and in the case of oracle jdbc, > undesirable. > > -Jack Eidsness Jack, According to SQL-92 the datetime types are: DATE - Year, Month and Day components TIME - Hour, Minute and Second components TIMESTAMP - Year, Month, Day, Hour, Minute, Second components. It seems correct then, that the hour, minute and second fields would be "0" when one uses an Oracle's Date datatype. I can recall switching to TIMESTAMPs in the past when I needed this granularity. Either that, or simply storing millis in a number field. Rob |
|
From: Gerd W. <bir...@t-...> - 2006-02-18 16:33:12
|
Hi all,
Perhaps just another detail:
The where clause that SQuirreL generates to do the update and some
checks is:
WHERE NAME='junk' AND FILE_UPDATE_DATE={d '2006-02-18'}
A select with this where clause doesn't return a row (of course I did
the insert using sysdate on 2006-02-18).
Since the JDBC driver says the type of the FILE_UPDATE_DATE column is
java.sql.Types.DATE I'm pretty sure the where clause not matching is a
violation of the JDBC standard.
By the way:
If you do a
insert into license values ('junk', {d '2006-02-18'})
instead of
insert into license values ('junk', sysdate)
editing works.
Gerd
Mark Neher wrote:
> I'm back !! And I've found a reproducible bare bones case. The common
> thing in all of the rows that I can't edit is a non-null DATE field.
> Using the following stripped down table definition:
>
> CREATE TABLE LICENSE
> (
> NAME VARCHAR2(80),
> FILE_UPDATE_DATE DATE
> );
>
> and the following data:
>
> insert into license values ('test', null);
> insert into license values ('junk', sysdate);
>
> I can edit the 'test' row, as the DATE is null, but I get the previously
> mentioned errors when I try to save an edit in the 'junk' row. This is
> reproducible in each table in our schema that has a DATE field. BTW,
> the same problem doesn't affect TIMESTAMP fields; I can edit rows with
> non-null TIMESTAMP data.
>
> Hope this helps,
>
> Mark
>
>
>
>
>
>> From: Gerd Wagner <bir...@t-...>
>> To: Mark Neher <mar...@ho...>
>> CC: SGR...@fl..., squ...@li...
>> Subject: Re: [Squirrel-sql-users] problem editing
>> Date: Wed, 08 Feb 2006 22:26:54 +0100
>>
>> Hi Mark,
>>
>> could please create a script of your table together with some inserts
>> that would allow me to reproduce your problem.
>>
>> To create the table DDL script right mouse click the table in the
>> Object Tree and choose the "Create Table Script" menu.
>>
>> To create the insert script write a "SELECT * FROM <yourTable> ... "
>> statement that selects a few rows from your table, place the caret in
>> the statement, hit ctrl+t and choose the sql2ins entry in the tools
>> popup.
>>
>> Thanks
>>
>> Gerd
>>
>> Mark Neher wrote:
>>>
>>> No, all the tables in our schema have a primary key. Thanks for the
>>> idea, though.
>>>
>>> Mark
>>>
>>>
>>>> From: "Simon Grantham" <SGR...@fl...>
>>>> To: <mar...@ho...>,<squ...@li...>
>>>> Subject: Re: [Squirrel-sql-users] problem editing
>>>> Date: Wed, 08 Feb 2006 14:53:02 -0500
>>>>
>>>> I'm guessing that the table you are trying to update has no primary
>>>> key.
>>>> In this circumstance, squirrel has no real way of knowing which record
>>>> it is supposed to update. That is, you are blocked because an update
>>>> command might update multiple records instead of just the one you
>>>> edited.
>>>>
>>>> Just a guess though.
>>>>
>>>> Simon
>>>>
>>>>
>>>> >>> "Mark Neher" <mar...@ho...> 08/02/2006 11:46 am >>>
>>>>
>>>> Hey All,
>>>>
>>>> I am having trouble editing in SQuirreL. If I make a table editable,
>>>> it
>>>> allows me to click in a cell and make changes. When I try to save the
>>>>
>>>> changes, a panel pops up saying
>>>>
>>>> "This row in the Database has been changed since you refreshed the
>>>> data.
>>>> No rows will be updated by this operation.
>>>> Do you wish to proceed?"
>>>>
>>>> Clicking yes yields "No rows updated."; no cancels. Trying to edit
>>>> using
>>>> the popup rather than in the cell gives the same results.
>>>>
>>>> I don't see any mention of this in the help, or in the mailing lists
>>>> online.
>>>> Any ideas ?? BTW, I am running version 2.1 final on a Linux box, and
>>>>
>>>> attaching to an Oracle database.
>>>>
>>>> Thanks,
>>>>
>>>> Mark
>>>>
>>>>
>>>>
>>>>
>>>> -------------------------------------------------------
>>>> This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>>>> files
>>>> for problems? Stop! Download the new AJAX search engine that makes
>>>> searching your log files as easy as surfing the web. DOWNLOAD
>>>> SPLUNK!
>>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>>
>>>>
>>>> _______________________________________________
>>>> Squirrel-sql-users mailing list
>>>> Squ...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>
>>>
>>>
>>>
>>> -------------------------------------------------------
>>> This SF.net email is sponsored by: Splunk Inc. Do you grep through
>>> log files
>>> for problems? Stop! Download the new AJAX search engine that makes
>>> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>> _______________________________________________
>>> Squirrel-sql-users mailing list
>>> Squ...@li...
>>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>
>>
>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc. Do you grep through log
> files
> for problems? Stop! Download the new AJAX search engine that makes
> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
> _______________________________________________
> Squirrel-sql-users mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>
|
|
From: Gerd W. <bir...@t-...> - 2006-02-19 10:36:16
|
Hi again,
Mark, since it is Oracle and after a night of desperate doubt ;-) I
decided to introduce a bit of Oracle specific code: According to Maury's
suggestion now for Oracle the generated where clause will be
WHERE NAME='junk' AND trunc(FILE_UPDATE_DATE)={d '2006-02-19'}
This worked alright on my Oracle 10g database. It would be nice if you
could try out if it worked for you and tell us. You can get the new code
from CVS or from the next weekly snapshot.
Rob, could you please give Mark a pointer whether my code made it to
this weekends snapshot or not. Thanks!
Gerd
Gerd Wagner wrote:
> Hi all,
>
> Perhaps just another detail:
>
> The where clause that SQuirreL generates to do the update and some
> checks is:
>
> WHERE NAME='junk' AND FILE_UPDATE_DATE={d '2006-02-18'}
>
> A select with this where clause doesn't return a row (of course I did
> the insert using sysdate on 2006-02-18).
>
> Since the JDBC driver says the type of the FILE_UPDATE_DATE column is
> java.sql.Types.DATE I'm pretty sure the where clause not matching is a
> violation of the JDBC standard.
>
>
>
> By the way:
> If you do a
>
> insert into license values ('junk', {d '2006-02-18'})
>
> instead of
>
> insert into license values ('junk', sysdate)
>
> editing works.
>
> Gerd
>
> Mark Neher wrote:
>> I'm back !! And I've found a reproducible bare bones case. The
>> common thing in all of the rows that I can't edit is a non-null DATE
>> field. Using the following stripped down table definition:
>>
>> CREATE TABLE LICENSE
>> (
>> NAME VARCHAR2(80),
>> FILE_UPDATE_DATE DATE
>> );
>>
>> and the following data:
>>
>> insert into license values ('test', null);
>> insert into license values ('junk', sysdate);
>>
>> I can edit the 'test' row, as the DATE is null, but I get the
>> previously mentioned errors when I try to save an edit in the 'junk'
>> row. This is reproducible in each table in our schema that has a DATE
>> field. BTW, the same problem doesn't affect TIMESTAMP fields; I can
>> edit rows with non-null TIMESTAMP data.
>>
>> Hope this helps,
>>
>> Mark
>>
>>
>>
>>
>>
>>> From: Gerd Wagner <bir...@t-...>
>>> To: Mark Neher <mar...@ho...>
>>> CC: SGR...@fl..., squ...@li...
>>> Subject: Re: [Squirrel-sql-users] problem editing
>>> Date: Wed, 08 Feb 2006 22:26:54 +0100
>>>
>>> Hi Mark,
>>>
>>> could please create a script of your table together with some inserts
>>> that would allow me to reproduce your problem.
>>>
>>> To create the table DDL script right mouse click the table in the
>>> Object Tree and choose the "Create Table Script" menu.
>>>
>>> To create the insert script write a "SELECT * FROM <yourTable> ... "
>>> statement that selects a few rows from your table, place the caret in
>>> the statement, hit ctrl+t and choose the sql2ins entry in the tools
>>> popup.
>>>
>>> Thanks
>>>
>>> Gerd
>>>
>>> Mark Neher wrote:
>>>>
>>>> No, all the tables in our schema have a primary key. Thanks for the
>>>> idea, though.
>>>>
>>>> Mark
>>>>
>>>>
>>>>> From: "Simon Grantham" <SGR...@fl...>
>>>>> To: <mar...@ho...>,<squ...@li...>
>>>>> Subject: Re: [Squirrel-sql-users] problem editing
>>>>> Date: Wed, 08 Feb 2006 14:53:02 -0500
>>>>>
>>>>> I'm guessing that the table you are trying to update has no primary
>>>>> key.
>>>>> In this circumstance, squirrel has no real way of knowing which
>>>>> record
>>>>> it is supposed to update. That is, you are blocked because an update
>>>>> command might update multiple records instead of just the one you
>>>>> edited.
>>>>>
>>>>> Just a guess though.
>>>>>
>>>>> Simon
>>>>>
>>>>>
>>>>> >>> "Mark Neher" <mar...@ho...> 08/02/2006 11:46 am >>>
>>>>>
>>>>> Hey All,
>>>>>
>>>>> I am having trouble editing in SQuirreL. If I make a table editable,
>>>>> it
>>>>> allows me to click in a cell and make changes. When I try to save the
>>>>>
>>>>> changes, a panel pops up saying
>>>>>
>>>>> "This row in the Database has been changed since you refreshed the
>>>>> data.
>>>>> No rows will be updated by this operation.
>>>>> Do you wish to proceed?"
>>>>>
>>>>> Clicking yes yields "No rows updated."; no cancels. Trying to edit
>>>>> using
>>>>> the popup rather than in the cell gives the same results.
>>>>>
>>>>> I don't see any mention of this in the help, or in the mailing lists
>>>>> online.
>>>>> Any ideas ?? BTW, I am running version 2.1 final on a Linux box,
>>>>> and
>>>>>
>>>>> attaching to an Oracle database.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Mark
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> -------------------------------------------------------
>>>>> This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>>>>> files
>>>>> for problems? Stop! Download the new AJAX search engine that makes
>>>>> searching your log files as easy as surfing the web. DOWNLOAD
>>>>> SPLUNK!
>>>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Squirrel-sql-users mailing list
>>>>> Squ...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>>
>>>>
>>>>
>>>>
>>>> -------------------------------------------------------
>>>> This SF.net email is sponsored by: Splunk Inc. Do you grep through
>>>> log files
>>>> for problems? Stop! Download the new AJAX search engine that makes
>>>> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
>>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>>
>>>> _______________________________________________
>>>> Squirrel-sql-users mailing list
>>>> Squ...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>>
>>>
>>
>>
>>
>>
>> -------------------------------------------------------
>> This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>> files
>> for problems? Stop! Download the new AJAX search engine that makes
>> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>> _______________________________________________
>> Squirrel-sql-users mailing list
>> Squ...@li...
>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc. Do you grep through log
> files
> for problems? Stop! Download the new AJAX search engine that makes
> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
> _______________________________________________
> Squirrel-sql-users mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>
|
|
From: Rob M. <Rob...@co...> - 2006-02-19 11:48:57
|
Gerd Wagner wrote:
> Hi again,
>
> Mark, since it is Oracle and after a night of desperate doubt ;-) I
> decided to introduce a bit of Oracle specific code: According to
> Maury's suggestion now for Oracle the generated where clause will be
>
> WHERE NAME='junk' AND trunc(FILE_UPDATE_DATE)={d '2006-02-19'}
>
> This worked alright on my Oracle 10g database. It would be nice if you
> could try out if it worked for you and tell us. You can get the new
> code from CVS or from the next weekly snapshot.
>
>
> Rob, could you please give Mark a pointer whether my code made it to
> this weekends snapshot or not. Thanks!
Mark,
The install jar with Gerd's changes can be found here:
http://prdownloads.sourceforge.net/squirrel-sql/squirrel-sql-Snapshot-20060219_1100-install.jar?download
Rob
|
|
From: Glenn G. <gw...@co...> - 2006-02-21 16:06:18
|
Hi All,
My recollection is that when I was building the DATE/TIME/TIMESTAMP data
types, someone pointed me to a wonderful web site (whose URL I cannot
find now) that described all the variations in DB implementations of SQL
for those types. The "{d 'date'}" form was supposedly the most
"standard" and seemed to work for everyone at the time.
My guess is that the code described below should be safe for all DBs,
and therefore should solve this Oracle-specific problem without causing
other problems, but only TIME will tell :-). (Sorry, I counldn't resist
the pun.)
Glenn
Gerd Wagner wrote:
> Hi again,
>
> Mark, since it is Oracle and after a night of desperate doubt ;-) I
> decided to introduce a bit of Oracle specific code: According to
> Maury's suggestion now for Oracle the generated where clause will be
>
> WHERE NAME='junk' AND trunc(FILE_UPDATE_DATE)={d '2006-02-19'}
>
> This worked alright on my Oracle 10g database. It would be nice if you
> could try out if it worked for you and tell us. You can get the new
> code from CVS or from the next weekly snapshot.
>
>
> Rob, could you please give Mark a pointer whether my code made it to
> this weekends snapshot or not. Thanks!
>
> Gerd
>
>
> Gerd Wagner wrote:
>
>> Hi all,
>>
>> Perhaps just another detail:
>>
>> The where clause that SQuirreL generates to do the update and some
>> checks is:
>>
>> WHERE NAME='junk' AND FILE_UPDATE_DATE={d '2006-02-18'}
>>
>> A select with this where clause doesn't return a row (of course I did
>> the insert using sysdate on 2006-02-18).
>>
>> Since the JDBC driver says the type of the FILE_UPDATE_DATE column is
>> java.sql.Types.DATE I'm pretty sure the where clause not matching is
>> a violation of the JDBC standard.
>>
>>
>>
>> By the way:
>> If you do a
>>
>> insert into license values ('junk', {d '2006-02-18'})
>>
>> instead of
>>
>> insert into license values ('junk', sysdate)
>>
>> editing works.
>>
>> Gerd
>>
>> Mark Neher wrote:
>>
>>> I'm back !! And I've found a reproducible bare bones case. The
>>> common thing in all of the rows that I can't edit is a non-null DATE
>>> field. Using the following stripped down table definition:
>>>
>>> CREATE TABLE LICENSE
>>> (
>>> NAME VARCHAR2(80),
>>> FILE_UPDATE_DATE DATE
>>> );
>>>
>>> and the following data:
>>>
>>> insert into license values ('test', null);
>>> insert into license values ('junk', sysdate);
>>>
>>> I can edit the 'test' row, as the DATE is null, but I get the
>>> previously mentioned errors when I try to save an edit in the 'junk'
>>> row. This is reproducible in each table in our schema that has a
>>> DATE field. BTW, the same problem doesn't affect TIMESTAMP fields;
>>> I can edit rows with non-null TIMESTAMP data.
>>>
>>> Hope this helps,
>>>
>>> Mark
>>>
>>>
>>>
>>>
>>>
>>>> From: Gerd Wagner <bir...@t-...>
>>>> To: Mark Neher <mar...@ho...>
>>>> CC: SGR...@fl..., squ...@li...
>>>> Subject: Re: [Squirrel-sql-users] problem editing
>>>> Date: Wed, 08 Feb 2006 22:26:54 +0100
>>>>
>>>> Hi Mark,
>>>>
>>>> could please create a script of your table together with some
>>>> inserts that would allow me to reproduce your problem.
>>>>
>>>> To create the table DDL script right mouse click the table in the
>>>> Object Tree and choose the "Create Table Script" menu.
>>>>
>>>> To create the insert script write a "SELECT * FROM <yourTable> ...
>>>> " statement that selects a few rows from your table, place the
>>>> caret in the statement, hit ctrl+t and choose the sql2ins entry in
>>>> the tools popup.
>>>>
>>>> Thanks
>>>>
>>>> Gerd
>>>>
>>>> Mark Neher wrote:
>>>>
>>>>>
>>>>> No, all the tables in our schema have a primary key. Thanks for
>>>>> the idea, though.
>>>>>
>>>>> Mark
>>>>>
>>>>>
>>>>>> From: "Simon Grantham" <SGR...@fl...>
>>>>>> To:
>>>>>> <mar...@ho...>,<squ...@li...>
>>>>>> Subject: Re: [Squirrel-sql-users] problem editing
>>>>>> Date: Wed, 08 Feb 2006 14:53:02 -0500
>>>>>>
>>>>>> I'm guessing that the table you are trying to update has no
>>>>>> primary key.
>>>>>> In this circumstance, squirrel has no real way of knowing which
>>>>>> record
>>>>>> it is supposed to update. That is, you are blocked because an
>>>>>> update
>>>>>> command might update multiple records instead of just the one you
>>>>>> edited.
>>>>>>
>>>>>> Just a guess though.
>>>>>>
>>>>>> Simon
>>>>>>
>>>>>>
>>>>>> >>> "Mark Neher" <mar...@ho...> 08/02/2006 11:46 am >>>
>>>>>>
>>>>>> Hey All,
>>>>>>
>>>>>> I am having trouble editing in SQuirreL. If I make a table
>>>>>> editable,
>>>>>> it
>>>>>> allows me to click in a cell and make changes. When I try to
>>>>>> save the
>>>>>>
>>>>>> changes, a panel pops up saying
>>>>>>
>>>>>> "This row in the Database has been changed since you refreshed the
>>>>>> data.
>>>>>> No rows will be updated by this operation.
>>>>>> Do you wish to proceed?"
>>>>>>
>>>>>> Clicking yes yields "No rows updated."; no cancels. Trying to edit
>>>>>> using
>>>>>> the popup rather than in the cell gives the same results.
>>>>>>
>>>>>> I don't see any mention of this in the help, or in the mailing lists
>>>>>> online.
>>>>>> Any ideas ?? BTW, I am running version 2.1 final on a Linux
>>>>>> box, and
>>>>>>
>>>>>> attaching to an Oracle database.
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Mark
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> -------------------------------------------------------
>>>>>> This SF.net email is sponsored by: Splunk Inc. Do you grep
>>>>>> through log
>>>>>> files
>>>>>> for problems? Stop! Download the new AJAX search engine that makes
>>>>>> searching your log files as easy as surfing the web. DOWNLOAD
>>>>>> SPLUNK!
>>>>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Squirrel-sql-users mailing list
>>>>>> Squ...@li...
>>>>>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> -------------------------------------------------------
>>>>> This SF.net email is sponsored by: Splunk Inc. Do you grep through
>>>>> log files
>>>>> for problems? Stop! Download the new AJAX search engine that makes
>>>>> searching your log files as easy as surfing the web. DOWNLOAD
>>>>> SPLUNK!
>>>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>>>
>>>>> _______________________________________________
>>>>> Squirrel-sql-users mailing list
>>>>> Squ...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>>>
>>>>
>>>
>>>
>>>
>>>
>>> -------------------------------------------------------
>>> This SF.net email is sponsored by: Splunk Inc. Do you grep through
>>> log files
>>> for problems? Stop! Download the new AJAX search engine that makes
>>> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>
>>> _______________________________________________
>>> Squirrel-sql-users mailing list
>>> Squ...@li...
>>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>
>>
>>
>>
>> -------------------------------------------------------
>> This SF.net email is sponsored by: Splunk Inc. Do you grep through
>> log files
>> for problems? Stop! Download the new AJAX search engine that makes
>> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>> _______________________________________________
>> Squirrel-sql-users mailing list
>> Squ...@li...
>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>
>
>
|
|
From: Mark N. <mar...@ho...> - 2006-02-21 18:02:01
|
Hi All,
I downloaded and installed the snapshot referenced. I have partial good
news -- some of the tables which I was unable to edit before work now
(including the minimal sample that I submitted last week), but I am still
unable to save an update to the rest. As I get a chance, I will try to
determine what data conditions might cause these differences.
In a (probably) unrelated note, when I run the new snapshot, there seems to
be some automatic formatting of numeric data. Number fields are now
formatted with commas (an ID field like 15000 now displays as 15,000) and
float fields don't display numbers after the decimal point. I don't
remember reading anything about this, and don't see anything in the help
screens about it. Is there a way to turn this off ?? I cut and paste
frequently and plain old numbers work better for that.
Thanks,
Mark
>From: Gerd Wagner <bir...@t-...>
>To: Gerd Wagner <bir...@t-...>
>CC: Mark Neher <mar...@ho...>, SGR...@fl...,
>squ...@li...,
>Rob...@co..., gw...@co...
>Subject: Re: [Squirrel-sql-users] problem editing
>Date: Sun, 19 Feb 2006 11:37:01 +0100
>
>Hi again,
>
>Mark, since it is Oracle and after a night of desperate doubt ;-) I decided
>to introduce a bit of Oracle specific code: According to Maury's suggestion
>now for Oracle the generated where clause will be
>
>WHERE NAME='junk' AND trunc(FILE_UPDATE_DATE)={d '2006-02-19'}
>
>This worked alright on my Oracle 10g database. It would be nice if you
>could try out if it worked for you and tell us. You can get the new code
>from CVS or from the next weekly snapshot.
>
>
>Rob, could you please give Mark a pointer whether my code made it to this
>weekends snapshot or not. Thanks!
>
>Gerd
>
>
>Gerd Wagner wrote:
>>Hi all,
>>
>>Perhaps just another detail:
>>
>>The where clause that SQuirreL generates to do the update and some checks
>>is:
>>
>>WHERE NAME='junk' AND FILE_UPDATE_DATE={d '2006-02-18'}
>>
>>A select with this where clause doesn't return a row (of course I did the
>>insert using sysdate on 2006-02-18).
>>
>>Since the JDBC driver says the type of the FILE_UPDATE_DATE column is
>>java.sql.Types.DATE I'm pretty sure the where clause not matching is a
>>violation of the JDBC standard.
>>
>>
>>
>>By the way:
>>If you do a
>>
>>insert into license values ('junk', {d '2006-02-18'})
>>
>>instead of
>>
>>insert into license values ('junk', sysdate)
>>
>>editing works.
>>
>>Gerd
>>
>>Mark Neher wrote:
>>>I'm back !! And I've found a reproducible bare bones case. The common
>>>thing in all of the rows that I can't edit is a non-null DATE field.
>>>Using the following stripped down table definition:
>>>
>>>CREATE TABLE LICENSE
>>>(
>>> NAME VARCHAR2(80),
>>> FILE_UPDATE_DATE DATE
>>>);
>>>
>>>and the following data:
>>>
>>>insert into license values ('test', null);
>>>insert into license values ('junk', sysdate);
>>>
>>>I can edit the 'test' row, as the DATE is null, but I get the previously
>>>mentioned errors when I try to save an edit in the 'junk' row. This is
>>>reproducible in each table in our schema that has a DATE field. BTW, the
>>>same problem doesn't affect TIMESTAMP fields; I can edit rows with
>>>non-null TIMESTAMP data.
>>>
>>>Hope this helps,
>>>
>>>Mark
>>>
>>>
>>>
>>>
>>>
>>>>From: Gerd Wagner <bir...@t-...>
>>>>To: Mark Neher <mar...@ho...>
>>>>CC: SGR...@fl..., squ...@li...
>>>>Subject: Re: [Squirrel-sql-users] problem editing
>>>>Date: Wed, 08 Feb 2006 22:26:54 +0100
>>>>
>>>>Hi Mark,
>>>>
>>>>could please create a script of your table together with some inserts
>>>>that would allow me to reproduce your problem.
>>>>
>>>>To create the table DDL script right mouse click the table in the Object
>>>>Tree and choose the "Create Table Script" menu.
>>>>
>>>>To create the insert script write a "SELECT * FROM <yourTable> ... "
>>>>statement that selects a few rows from your table, place the caret in
>>>>the statement, hit ctrl+t and choose the sql2ins entry in the tools
>>>>popup.
>>>>
>>>>Thanks
>>>>
>>>>Gerd
>>>>
>>>>Mark Neher wrote:
>>>>>
>>>>>No, all the tables in our schema have a primary key. Thanks for the
>>>>>idea, though.
>>>>>
>>>>>Mark
>>>>>
>>>>>
>>>>>>From: "Simon Grantham" <SGR...@fl...>
>>>>>>To: <mar...@ho...>,<squ...@li...>
>>>>>>Subject: Re: [Squirrel-sql-users] problem editing
>>>>>>Date: Wed, 08 Feb 2006 14:53:02 -0500
>>>>>>
>>>>>>I'm guessing that the table you are trying to update has no primary
>>>>>>key.
>>>>>> In this circumstance, squirrel has no real way of knowing which
>>>>>>record
>>>>>>it is supposed to update. That is, you are blocked because an update
>>>>>>command might update multiple records instead of just the one you
>>>>>>edited.
>>>>>>
>>>>>>Just a guess though.
>>>>>>
>>>>>>Simon
>>>>>>
>>>>>>
>>>>>> >>> "Mark Neher" <mar...@ho...> 08/02/2006 11:46 am >>>
>>>>>>
>>>>>>Hey All,
>>>>>>
>>>>>>I am having trouble editing in SQuirreL. If I make a table editable,
>>>>>>it
>>>>>>allows me to click in a cell and make changes. When I try to save the
>>>>>>
>>>>>>changes, a panel pops up saying
>>>>>>
>>>>>>"This row in the Database has been changed since you refreshed the
>>>>>>data.
>>>>>>No rows will be updated by this operation.
>>>>>>Do you wish to proceed?"
>>>>>>
>>>>>>Clicking yes yields "No rows updated."; no cancels. Trying to edit
>>>>>>using
>>>>>>the popup rather than in the cell gives the same results.
>>>>>>
>>>>>>I don't see any mention of this in the help, or in the mailing lists
>>>>>>online.
>>>>>> Any ideas ?? BTW, I am running version 2.1 final on a Linux box,
>>>>>>and
>>>>>>
>>>>>>attaching to an Oracle database.
>>>>>>
>>>>>>Thanks,
>>>>>>
>>>>>>Mark
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>-------------------------------------------------------
>>>>>>This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>>>>>>files
>>>>>>for problems? Stop! Download the new AJAX search engine that makes
>>>>>>searching your log files as easy as surfing the web. DOWNLOAD
>>>>>>SPLUNK!
>>>>>>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>>>>
>>>>>>
>>>>>>_______________________________________________
>>>>>>Squirrel-sql-users mailing list
>>>>>>Squ...@li...
>>>>>>https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>-------------------------------------------------------
>>>>>This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>>>>>files
>>>>>for problems? Stop! Download the new AJAX search engine that makes
>>>>>searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
>>>>>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>>>
>>>>>_______________________________________________
>>>>>Squirrel-sql-users mailing list
>>>>>Squ...@li...
>>>>>https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>>>
>>>>
>>>
>>>
>>>
>>>
>>>-------------------------------------------------------
>>>This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>>>files
>>>for problems? Stop! Download the new AJAX search engine that makes
>>>searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
>>>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>>_______________________________________________
>>>Squirrel-sql-users mailing list
>>>Squ...@li...
>>>https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>>
>>
>>
>>
>>-------------------------------------------------------
>>This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>>files
>>for problems? Stop! Download the new AJAX search engine that makes
>>searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
>>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>_______________________________________________
>>Squirrel-sql-users mailing list
>>Squ...@li...
>>https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>
>
|
|
From: Maury H. <ml1...@sh...> - 2006-02-21 18:15:28
|
Mark Neher wrote: > I downloaded and installed the snapshot referenced. I have partial good > news -- some of the tables which I was unable to edit before work now > (including the minimal sample that I submitted last week), but I am > still unable to save an update to the rest. As I get a chance, I will > try to determine what data conditions might cause these differences. If I understand the fix that Gerd implemented, then the tables where the update doesn't work are probably tables where you have multiple rows where date values that are on the same day (e.g. 2 Feb 2006 12:00:01 and 2 Feb 2006 11:48:33, etc.). The fix truncates the date when doing the comparison. In such cases, the code is going to find (what it thinks are) duplicate rows, so it will not do the update. Maury |
|
From: Robert M. <rob...@gm...> - 2006-02-21 18:24:48
|
Mark, As far as the number formatting, try this: General Preferences -> Data Type Controls In the section called FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL choose "use default format (3.14159)" Rob On 2/21/06, Maury Hammel <ml1...@sh...> wrote: > > Mark Neher wrote: > > > I downloaded and installed the snapshot referenced. I have partial goo= d > > news -- some of the tables which I was unable to edit before work now > > (including the minimal sample that I submitted last week), but I am > > still unable to save an update to the rest. As I get a chance, I will > > try to determine what data conditions might cause these differences. > > If I understand the fix that Gerd implemented, then the tables where the > update doesn't work are probably tables where you have multiple rows > where date values that are on the same day (e.g. 2 Feb 2006 12:00:01 and > 2 Feb 2006 11:48:33, etc.). > > The fix truncates the date when doing the comparison. In such cases, > the code is going to find (what it thinks are) duplicate rows, so it > will not do the update. > > > Maury > > > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through log > files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! > http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D103432&bid=3D230486&dat= =3D121642 > _______________________________________________ > Squirrel-sql-users mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users > |
|
From: Gerd W. <bir...@t-...> - 2006-02-21 19:46:00
|
Mark Neher wrote: > > > In a (probably) unrelated note, when I run the new snapshot, there seems > to be some automatic formatting of numeric data. Number fields are now > formatted with commas (an ID field like 15000 now displays as 15,000) > and float fields don't display numbers after the decimal point. I don't > remember reading anything about this, and don't see anything in the help > screens about it. It's in our change log. Goto http://cvs.sourceforge.net/viewcvs.py/squirrel-sql/sql12/doc/changes.txt?view=markup and look for the Fixed bugs #1043604 and #1043599: ... entry. > Is there a way to turn this off ?? I cut and paste > frequently and plain old numbers work better for that. See Rob's mail. > > Thanks, > > Mark |
|
From: Mark N. <mar...@ho...> - 2006-02-22 20:54:07
|
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 |
|
From: gwg <gw...@co...> - 2006-02-24 21:07:07
|
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 > > > |
|
From: Glenn G. <gw...@co...> - 2006-02-08 22:20:45
|
Hi Mark,
I can tell you what's going on inside Squirrel, but not why you are
seeing this result.
Squirrel's data editing is designed to work with any table, even those
that do not have primary keys. It does the edit by creating an UPDATE
statement with a gigantic WHERE clause using all of the columns in the
table with the values for the row containing the cell that you changed,
as in:
WHERE integerColumn1=5 AND stringColumn2='somedata' AND ....
Thus, as long as the row is unique in the DB, the update should update
only the one row.
However, before attempting the update we do some checks to make sure we
don't do something bad to your database. You are getting a message from
one of these checks. Specificly, before doing the update, we create a
WHERE clause containing all of the values from the row, including the
_original_ value in the field you just changed and do a SELECT. For the
update to work correctly, this should come up with exactly 1 row, and if
it does, then the update is done without further ado. However, if it
does not find the original row, then it figures that someone else might
have changed it since you read the contents, so it warns you.
What you are seeing is the following sequence:
- Squirrel read the table
- you edited some data and said "update it" (by leaving the cell)
- Squirrel looks for the row containing the original values and
cannot find it, so it warns you that something weird is happening.
One thing that I know that might cause this problem is if the only
fields in the table were of types that cannot be used in a WHERE clause,
such as BLOB or CLOB.
If you could post the schema for this one table, that might help us.
Glenn
Mark Neher wrote:
>
> No, all the tables in our schema have a primary key. Thanks for the
> idea, though.
>
> Mark
>
>
>> From: "Simon Grantham" <SGR...@fl...>
>> To: <mar...@ho...>,<squ...@li...>
>> Subject: Re: [Squirrel-sql-users] problem editing
>> Date: Wed, 08 Feb 2006 14:53:02 -0500
>>
>> I'm guessing that the table you are trying to update has no primary key.
>> In this circumstance, squirrel has no real way of knowing which record
>> it is supposed to update. That is, you are blocked because an update
>> command might update multiple records instead of just the one you
>> edited.
>>
>> Just a guess though.
>>
>> Simon
>>
>>
>> >>> "Mark Neher" <mar...@ho...> 08/02/2006 11:46 am >>>
>>
>> Hey All,
>>
>> I am having trouble editing in SQuirreL. If I make a table editable,
>> it
>> allows me to click in a cell and make changes. When I try to save the
>>
>> changes, a panel pops up saying
>>
>> "This row in the Database has been changed since you refreshed the
>> data.
>> No rows will be updated by this operation.
>> Do you wish to proceed?"
>>
>> Clicking yes yields "No rows updated."; no cancels. Trying to edit
>> using
>> the popup rather than in the cell gives the same results.
>>
>> I don't see any mention of this in the help, or in the mailing lists
>> online.
>> Any ideas ?? BTW, I am running version 2.1 final on a Linux box, and
>>
>> attaching to an Oracle database.
>>
>> Thanks,
>>
>> Mark
>>
>>
>>
>>
>> -------------------------------------------------------
>> This SF.net email is sponsored by: Splunk Inc. Do you grep through log
>> files
>> for problems? Stop! Download the new AJAX search engine that makes
>> searching your log files as easy as surfing the web. DOWNLOAD
>> SPLUNK!
>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
>>
>> _______________________________________________
>> Squirrel-sql-users mailing list
>> Squ...@li...
>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>
>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc. Do you grep through log
> files
> for problems? Stop! Download the new AJAX search engine that makes
> searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
> _______________________________________________
> Squirrel-sql-users mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>
|
|
From: Maury H. <ml1...@sh...> - 2006-02-09 00:03:30
|
Glenn: > I can tell you what's going on inside Squirrel, but not why you are > seeing this result. > [snip for brevity] Maybe I'm remembering this wrong, it has been quite a while since I've looked at the source, but I thought you had implemented (or what is it the Oracle plug-in?) something to use the ROWID (or other similar column for other databases) for updates? I know SQuirreL displays the ROWID in the Contents tab. Maury |
|
From: Rob M. <Rob...@co...> - 2006-02-09 01:02:30
|
Maury Hammel wrote: > Glenn: > >> I can tell you what's going on inside Squirrel, but not why you are >> seeing this result. >> > [snip for brevity] > > Maybe I'm remembering this wrong, it has been quite a while since I've > looked at the source, but I thought you had implemented (or what is it > the Oracle plug-in?) something to use the ROWID (or other similar > column for other databases) for updates? I know SQuirreL displays the > ROWID in the Contents tab. Maury, Looking at DataSetUpdateableTableModelImpl.getWhereClause it doesn't appear that anything Oracle-specific is being used. Mark, One other thing to note that no one else has mentioned here - there is a way to limit what columns are used in the where clause that is used to build the update statement. The "Limit cell edit WHERE clause size" can be used to restrict which columns are used. Right click on the table to access this menu item. Maybe just using the primary key field(s) in this case might help? Rob |
|
From: gwg <gw...@co...> - 2006-02-09 04:16:09
|
Hi Maury, I may be remembering this wrong myself, but I think what happens is that when you select "*" from a table that ROWID is one of the things returned. I do remember putting some special case code in that looked for two names (ROWID and OBJID?), but I think that was just to make them non-editable, or always put them at the right side of the table or some such. I'm fairly certain that ROWID is not used as a special case in the SELECT or UPDATE statements. It's just another column, though a particularly useful one :-). Glenn Maury Hammel wrote: > Glenn: > >> I can tell you what's going on inside Squirrel, but not why you are >> seeing this result. >> > [snip for brevity] > > Maybe I'm remembering this wrong, it has been quite a while since I've > looked at the source, but I thought you had implemented (or what is it > the Oracle plug-in?) something to use the ROWID (or other similar column > for other databases) for updates? I know SQuirreL displays the ROWID in > the Contents tab. > > > Maury > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through log > files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 > _______________________________________________ > Squirrel-sql-users mailing list > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users > > |
|
From: Mark N. <mar...@ho...> - 2006-02-09 15:47:58
|
Rob, I just tried this feature and set it to use the primary key only. This didn't make a difference, unfortunately. I'll try to post a table definition as a couple folks have suggested, but I probably won't get a chance to do so for a couple days. BTW, our schema contains some 300 tables, and I have only turned up a handful that have this problem; most of them I can edit and update just fine. I haven't been able to find a common thread among the ones that fail... Mark ========================================= Mark, One other thing to note that no one else has mentioned here - there is a way to limit what columns are used in the where clause that is used to build the update statement. The "Limit cell edit WHERE clause size" can be used to restrict which columns are used. Right click on the table to access this menu item. Maybe just using the primary key field(s) in this case might help? Rob |
|
From: Rob M. <Rob...@co...> - 2006-02-09 16:16:53
|
Mark Neher wrote: > > Rob, I just tried this feature and set it to use the primary key > only. This didn't make a difference, unfortunately. I'll try to post > a table definition as a couple folks have suggested, but I probably > won't get a chance to do so for a couple days. BTW, our schema > contains some 300 tables, and I have only turned up a handful that > have this problem; most of them I can edit and update just fine. I > haven't been able to find a common thread among the ones that fail... Yes, the table definition will be useful ( also a test row that fails would be very useful as well) That would allow us to step through the code and see exactly what is happening. Rob |
