Hi,
Great effort with this software. We have installed it at my company but the above issue has been preventing us from using it as intended. We were glad to see the new release 3.5.0, however the issue that prevents table fields being edited directly (after selecting 'Make Editable' from the menu) has still not been fixed. I've googled for this and it seems to be a known issue with Squirrel. It only happens when the row to be edited contains a date field.
A warning is displayed which reads:
"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?"
The row is not updated on preceeding.
Screenshot attached
Hope you can spare the time to look into this.
Regards,
Sean Beckles
I can assure you that this is not a common problem which means to help I'd need information about your database product and the column types you use.
Best would be if you could provide me with a CREATE TABLE / INSERT script to reproduce the problem.
Gerd
Hi Gert,
Thanks for getting back to me so quickly. I've been away for a while so
sorry to take such a long time responding.
The problem we're experiencing is similar to that described in this link:
http://sourceforge.net/p/squirrel-sql/bugs/928/
But for us it occurs both in the object browser and in created tables.
We are using Ingres version 9.2, with the included JDBC driver for this
version.
In our case, date column types are set to be INGRESDATE (Ingres can use
this or ANSI dates).
For us the main problem is that we can't quickly change values in tables
after we look them up in the Object Browser. Ingres is the back end of the
Housing Management system that we use, and it would be really useful for
our team to be able to do this without having to keep writing update
statements for single values.
I understand that this may not be a common problem, but I have found other
instances online for users with other databases.
In any case, I must congratulate you and your team on the effort, time and
skill put into SQuirreL. We really appreciate it.
Thanks,
Sean
On Thu, May 16, 2013 at 8:45 PM, Gerd Wagner gerdwagner@users.sf.netwrote:
Related
Bugs: #1086
I'm sorry, I don't have access to Ingres. But I just committed the following change:
Made data editing problems that concern update row counts more transparent by printing
out details to SQuirreL's message panel. This may help to fix bugs like #1086.
Please either check out the change from our Git repo or wait for next snapshot to be released. Perhaps with the help of this change we may be able to get some more detailed information about your problem.
Here is a link to the aforementioned snapshot build:
https://oss.sonatype.org/content/repositories/snapshots/net/sf/squirrel-sql/squirrelsql-other-installer/3.6.0-SNAPSHOT/squirrelsql-other-installer-3.6.0-20130612.212334-8-standard.jar
Hi Gerds
Thank you for looking into this. Please see below for the output I get from
the message panel when attempting to edit a value.
Regards,
Sean
Output:
Editing led to a warning message because the number of rows that would be
updated was found to be 0 instead of 1.
Here's some information about the query that was used to predict the number
of rows that would be affected:
select count(*) from "dbadmin"."tenancy" WHERE propref = ? AND tensuffix =
? AND checkdigit = ? AND tenind = ? AND tentypecode = ? AND tencount = ?
AND tenstartdate={ts '1985-04-01 00:00:00.0'} AND tenenddate={ts
'1994-02-13 00:00:00.0'} AND fixedenddate={ts '1970-01-01 00:00:00.0'} AND
tenstopcode = ? AND paymthdcode = ? AND tenlinkind = ? AND tencurrbal = ?
AND tenarrsbal = ? AND lastreddate={ts '1994-02-24 00:00:00.0'} AND
arrhistind = ? AND tenaltkey = ? AND contactflag = ? AND contactdate={ts
'1970-01-01 00:00:00.0'} AND contaddract = ? AND contaddr1 = ? AND
contaddr2 = ? AND contaddr3 = ? AND contaddr4 = ? AND contpstcde = ? AND
conttelno = ? AND hometelno = ? AND worktelno = ? AND printbook = ? AND
printstatemt = ? AND mnodecode = ? AND rentgpcode = ? AND rtbref = ? AND
currgrdeb = ? AND currhb = ? AND ten1sname = ? AND ten1inits = ? AND
ten1title = ? AND ten2sname = ? AND ten2inits = ? AND ten2title = ? AND
udd03 = ? AND udd04 = ? AND stateend={ts '1970-01-01 00:00:00.0'} AND
statebal = ? AND contactname = ? AND servchgind = ? AND tenstartcode = ?
AND origtenstart={ts '1970-01-01 00:00:00.0'} AND nosp_effdate={ts
'1970-01-01 00:00:00.0'} AND nosp_code = ? AND nosp_expdate={ts '1970-01-01
00:00:00.0'} AND nosp_valdate={ts '1970-01-01 00:00:00.0'} AND baactgrdeb =
? AND baactcurrhb = ? AND securepasswd = ? AND swipeno = ? AND
swipedate={ts '2000-06-29 00:00:00.0'} AND ethniccode = ? AND mobileno = ?
AND emailid = ? AND faxno = ? AND commformat = ? AND commmedia = ? AND
firstlanguage = ? AND currsp = ? AND baactcurrsp = ? AND spmainrecovind = ?
AND arrearcatcode = ? AND arrsofficer = ? AND repairscontacttel = ? AND
accountr0 = ? AND accountr1 = ? AND accountr2 = ? AND accountr3 = ? AND
accountr4 = ? AND accountr5 = ? AND accountr6 = ? AND accountr7 = ? AND
accountr8 = ? AND accountr9 = ? AND choicebasedlettings = ? AND
lastauditdt={ts '1970-01-01 00:00:00.0'} AND audittype = ? AND auditsource
= ? AND audituser = ? AND officerid = ?
propref = ? with parameter value: "1157800010 " of type:
java.lang.String
tensuffix = ? with parameter value: "1" of type: java.lang.Integer
checkdigit = ? with parameter value: "9" of type: java.lang.String
tenind = ? with parameter value: "F" of type: java.lang.String
tentypecode = ? with parameter value: "ST" of type: java.lang.String
tencount = ? with parameter value: "1" of type: java.lang.Byte
tenstopcode = ? with parameter value: "TT" of type: java.lang.String
paymthdcode = ? with parameter value: "COLL" of type: java.lang.String
tenlinkind = ? with parameter value: " " of type: java.lang.String
tencurrbal = ? with parameter value: "0.00" of type: java.math.BigDecimal
tenarrsbal = ? with parameter value: "0.00" of type: java.math.BigDecimal
arrhistind = ? with parameter value: "Y" of type: java.lang.String
tenaltkey = ? with parameter value: "1" of type: java.lang.Integer
contactflag = ? with parameter value: "Y" of type: java.lang.String
contaddract = ? with parameter value: "N" of type: java.lang.String
contaddr1 = ? with parameter value: " " of
type: java.lang.String
contaddr2 = ? with parameter value: " " of
type: java.lang.String
contaddr3 = ? with parameter value: " " of
type: java.lang.String
contaddr4 = ? with parameter value: " " of
type: java.lang.String
contpstcde = ? with parameter value: " " of type: java.lang.String
conttelno = ? with parameter value: "" of type: java.lang.String
hometelno = ? with parameter value: "" of type: java.lang.String
worktelno = ? with parameter value: "" of type: java.lang.String
printbook = ? with parameter value: "N" of type: java.lang.String
printstatemt = ? with parameter value: "N" of type: java.lang.String
mnodecode = ? with parameter value: "111141" of type: java.lang.String
rentgpcode = ? with parameter value: "01" of type: java.lang.String
rtbref = ? with parameter value: "SCH5322 " of type: java.lang.String
currgrdeb = ? with parameter value: "0.00" of type: java.math.BigDecimal
currhb = ? with parameter value: "0.00" of type: java.math.BigDecimal
ten1sname = ? with parameter value: "SALT " of type:
java.lang.String
ten1inits = ? with parameter value: "R " of type: java.lang.String
ten1title = ? with parameter value: "MR " of type: java.lang.String
ten2sname = ? with parameter value: " " of type:
java.lang.String
ten2inits = ? with parameter value: " " of type: java.lang.String
ten2title = ? with parameter value: " " of type: java.lang.String
udd03 = ? with parameter value: " " of type: java.lang.String
udd04 = ? with parameter value: " " of type: java.lang.String
statebal = ? with parameter value: "0.00" of type: java.math.BigDecimal
contactname = ? with parameter value: " " of
type: java.lang.String
servchgind = ? with parameter value: " " of type: java.lang.String
tenstartcode = ? with parameter value: "NN" of type: java.lang.String
nosp_code = ? with parameter value: " " of type: java.lang.String
baactgrdeb = ? with parameter value: "0.00" of type: java.math.BigDecimal
baactcurrhb = ? with parameter value: "0.00" of type: java.math.BigDecimal
securepasswd = ? with parameter value: " " of type:
java.lang.String
swipeno = ? with parameter value: "000000001" of type: java.lang.String
ethniccode = ? with parameter value: "17 " of type: java.lang.String
mobileno = ? with parameter value: "" of type: java.lang.String
emailid = ? with parameter value: "" of type: java.lang.String
faxno = ? with parameter value: "" of type: java.lang.String
commformat = ? with parameter value: "SD" of type: java.lang.String
commmedia = ? with parameter value: "PR" of type: java.lang.String
firstlanguage = ? with parameter value: "NN" of type: java.lang.String
currsp = ? with parameter value: "0.00" of type: java.math.BigDecimal
baactcurrsp = ? with parameter value: "0.00" of type: java.math.BigDecimal
spmainrecovind = ? with parameter value: " " of type: java.lang.String
arrearcatcode = ? with parameter value: " " of type: java.lang.String
arrsofficer = ? with parameter value: "" of type: java.lang.String
repairscontacttel = ? with parameter value: " " of type:
java.lang.String
accountr0 = ? with parameter value: " " of type: java.lang.String
accountr1 = ? with parameter value: " " of type: java.lang.String
accountr2 = ? with parameter value: " " of type: java.lang.String
accountr3 = ? with parameter value: " " of type: java.lang.String
accountr4 = ? with parameter value: " " of type: java.lang.String
accountr5 = ? with parameter value: " " of type: java.lang.String
accountr6 = ? with parameter value: " " of type: java.lang.String
accountr7 = ? with parameter value: " " of type: java.lang.String
accountr8 = ? with parameter value: " " of type: java.lang.String
accountr9 = ? with parameter value: " " of type: java.lang.String
choicebasedlettings = ? with parameter value: " " of type: java.lang.String
audittype = ? with parameter value: "" of type: java.lang.String
auditsource = ? with parameter value: "" of type: java.lang.String
audituser = ? with parameter value: "" of type: java.lang.String
officerid = ? with parameter value: "" of type: java.lang.String
On Wed, Jun 12, 2013 at 10:41 PM, Gerd Wagner gerdwagner@users.sf.netwrote:
Related
Bugs: #1086
Sorry, I've gotten your name wrong twice now - I meant to write Gerd.
Thanks,
Sean
On Thu, Jun 13, 2013 at 6:13 PM, sean sean.beckles@gmail.com wrote:
Related
Bugs: #1086
From the message itself I can't see where the problem is. But you have the chance to figure it out as follows:
--> If not the problem already lies in the date columns
--> If yes begin to uncomment AND-lines and replace the question mark by the appropriate value. Start with the ones that look most suspicious to you for example empty strings or blanks.
This way you should come to an SQL where nor results are returned.
This is an intermittent problem for me also, in v3.6 of Squirrel SQL. Sometimes it works, sometimes not. I suspect it has something to do with timezone.
Using squirrel 3.7.1 with a Derby 10.11.1 from Java 8 I see similar problems.
In my case there are 3 TIMESTAMP (DATA_TYPE=93) columns some more simple columns (CHAR, INTEGER, VARCHAR etc) a nullable CLOB (DT=2005) and a nullable BLOB (DT=2004) as last column.
Every row has a unique ID.
The SQL verify query used by SQuirreL (message panel) is along these lines:
select count(*) from "APP"."TABLE1"
WHERE ID = ?
... a lot of AND branches for other columns
AND MYBLOB is null
The last clause with the 'is null' is the culprit here.
Whenever my BLOB is null I'm able to edit any of the other columns (even the CLOB) in the row with
no problems. But as soon as the BLOB is filled nothing works and I see the message of the thread opener.
I can understand that editing the BLOB itself may not be the best thing to do. But why is a filled BLOB hindering other edits?
Another note:
It seems not possible to edit the BLOB if it has data set (only white instead of yellow edit pane). One should at least be able to erase that data if it is a nullable BLOB.
I'm not sure if editability depends on the content of data bytes. In my case it is zip compressed stuff.
It seems to be possible to (somehow) edit the BLOB if it is null. After setting for instance a 0x41 there this entry remains editable.