problem editing

classic Classic list List threaded Threaded
29 messages Options
12
Reply | Threaded
Open this post in threaded view
|

problem editing

Mark Neher

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Simon Grantham
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" <[hidden email]> 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
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Mark Neher
In reply to this post by Mark Neher

No, all the tables in our schema have a primary key.  Thanks for the idea,
though.

Mark


>From: "Simon Grantham" <[hidden email]>
>To: <[hidden email]>,<[hidden email]>
>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" <[hidden email]> 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
>[hidden email]
>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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Gerd Wagner
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" <[hidden email]>
>> To: <[hidden email]>,<[hidden email]>
>> 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" <[hidden email]> 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
>> [hidden email]
>> 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
> [hidden email]
> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Glenn Griffin
In reply to this post by Mark Neher
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" <[hidden email]>
>> To: <[hidden email]>,<[hidden email]>
>> 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" <[hidden email]> 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
>> [hidden email]
>> 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
> [hidden email]
> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Maury Hammel
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Re: problem editing

Rob Manning
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



-------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Re: problem editing

Glenn Griffin
In reply to this post by Maury Hammel
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
> [hidden email]
> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Mark Neher
In reply to this post by Glenn Griffin

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




-------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Rob Manning
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


-------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Mark Neher
In reply to this post by Gerd Wagner
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 <[hidden email]>
>To: Mark Neher <[hidden email]>
>CC: [hidden email],  [hidden email]
>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" <[hidden email]>
>>>To: <[hidden email]>,<[hidden email]>
>>>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" <[hidden email]> 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
>>>[hidden email]
>>>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
>>[hidden email]
>>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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Maury Hammel

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Mark Neher
In reply to this post by Mark Neher
>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



You're right !!  That does make a difference.  Regrettably, that doesn't
solve my problem, as we do store time information in our DATE fields.

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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Re: problem editing

Maury Hammel-2
Mark:

> You're right !!  That does make a difference.  Regrettably, that doesn't
> solve my problem, as we do store time information in our DATE fields.

Yes, it's going to be a problem with just about every Oracle database --
I haven't seen too many that actually use the TIMESTAMP data type.
Unfortunately, the Oracle JDBC driver identifies a DATE column as a
java.sql.Date, so SquirreL handles them that way.

I don't think it will be able to be fixed without some Oracle-specific
coding -- maybe that could be added to the Oracle plug-in?


Maury
--

Maury Hammel
Technical Associate

Cronus Technologies Inc. (CTI)
250 - 2nd Ave. South
Saskatoon, SK  S7K 1K9  Canada

t. 306.652.5798 x107

http://www.cfactor.net



-------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Re: problem editing

Maury Hammel
In reply to this post by Mark Neher
Mark:

> You're right !!  That does make a difference.  Regrettably, that doesn't
> solve my problem, as we do store time information in our DATE fields.

Yes, it's going to be a problem with just about every Oracle database --
I haven't seen too many that actually use the TIMESTAMP data type.
Unfortunately, the Oracle JDBC driver identifies a DATE column as a
java.sql.Date, so SquirreL handles them that way.

I don't think it will be able to be fixed without some Oracle-specific
coding -- maybe that could be added to the Oracle plug-in?


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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Jack Eidsness
In reply to this post by Maury Hammel
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
> [hidden email]
> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Maury Hammel
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Jack Eidsness
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
> [hidden email]
> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Rob Manning
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


-------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: problem editing

Gerd Wagner
In reply to this post by Mark Neher
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 <[hidden email]>
>> To: Mark Neher <[hidden email]>
>> CC: [hidden email],  [hidden email]
>> 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" <[hidden email]>
>>>> To: <[hidden email]>,<[hidden email]>
>>>> 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" <[hidden email]> 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
>>>> [hidden email]
>>>> 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
>>> [hidden email]
>>> 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
> [hidden email]
> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
12