Save/Update Message Popups -- incorrect Records Affected info
a
anonymous
started a topic
over 14 years ago
[This topic is migrated from our old forums. The original author name has been removed]
Very interesting:
I am trying to update a row via the grid. I get this 'Save Results' popup saying that nothing was updated.
+Row 4+
+DML UPDATE+
+Message Error! No rows were affected by this edit.+
+SQL update "dbo"."apps_UTS_ACCF" set "TrdCnt" = 12345 where "WHRecordID" = 10830818 and "TradeDate" = '2010-03-03 00:00:00.0' and "SystemID" = 'FAST' and "FastODT" = '2010-03-03 09:10:59.0' and "FastOID" = '00003745442ORNY0'+
After clicking Close button and refreshing the grid, the new value (12345) is there...
Decided to do it via UPDATE using exact sql syntax provided in the popup window:
update "dbo"."apps_UTS_ACCF" set *"TrdCnt" = 321* where "WHRecordID" = 10830818 and "TradeDate" = '2010-03-03 00:00:00.0' and "SystemID" = 'FAST' and "FastODT" = '2010-03-03 09:10:59.0' and "FastOID" = '00003745442ORNY0';
10:39:12 [UPDATE - 0 row(s), 0.000 secs] Command processed. No rows were affected
... 1 statement(s) executed, *0 row(s) affected*, exec/fetch time: 0.000/0.000 sec [0 successful, 1 warnings, 0 errors]
Doing this now: (changed UPDATE to SELECT)
*select* TrdCnt from "dbo"."apps_UTS_ACCF" where "WHRecordID" = 10830818 and "TradeDate" = '2010-03-03 00:00:00.0' and "SystemID" = 'FAST' and "FastODT" = '2010-03-03 09:10:59.0' and "FastOID" = '00003745442ORNY0'
--------
Result: 321
========
Sybase ASE jdbc driver
========
Product: DbVisualizer Personal 7.0.4
Build: #1494 (2010/02/24 08:28)
Java VM: Java HotSpot(TM) Client VM
Java Version: 1.6.0_13
Java Vendor: Sun Microsystems Inc.
OS Name: Windows XP
OS Arch: x86
OS Version: 5.1
Re: Save/Update Message Popups -- incorrect Records Affected info
Hi,
The editing and "go" problems has been fixed in the just released 7.0.5 version.
For editing, there is now a "Key Column Chooser" button in the edit result window. Press it to refine what columns should compose the WHERE clause.
Regards
Roger
Hans Bergsten
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: Save/Update Message Popups -- incorrect Records Affected info
Hi Alex,
I have verified that there is a problem with comments next to the "go" keyword and will look for a way to fix it. Thanks for reporting this.
Best Regards,
Hans
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Save/Update Message Popups -- incorrect Records Affected info
Thanks Roger. Looking forward to you fixing this! Also, can you please comment on the second issue of this post ( issue with a +go+ )
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: Save/Update Message Popups -- incorrect Records Affected info
Hi Alex,
Yes I hope it is related.
Best Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Save/Update Message Popups -- incorrect Records Affected info
Roger - thanks. Does the same also apply when I was issuing the UPDATE statement directly in the sql editor?
Because, like I mentioned before, it also said that 0-rows were updated, but in-fact, updates did occur.
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: Save/Update Message Popups -- incorrect Records Affected info
Hi Alex,
Great it works!
The editing functionality has been running for more than 2 years and on the same day two different users report similar problems...
The problem is that the assumed key columns forming the WHERE clause for the table are cached automatically to increase performance at save. These key columns are cached per table when the SELECT is first executed. If at a later stage another SELECT (with different columns in the select list) is executed and a row is edited, DbVisualizer will use the first columns cached and that may then lead to the actual DB table row being updated cannot be determined.
The solution here, (not very obvious) is to open the grid right-click menu and choose "Edit Table Data->Key Column Chooser to alter the key columns or re-start DbVisualizer to clear the cache.
I have registered this as an issue and we will look into making this more intuitive or even clearing the cache automatically.
Best Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Save/Update Message Popups -- incorrect Records Affected info
Roger,
I opened a new dbVis session and edits now work... What are your thoughts?
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: Save/Update Message Popups -- incorrect Records Affected info
Alex,
Related to the editing issue, what happen if you re-start DbVisualizer and then perform the edit?
Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Save/Update Message Popups -- incorrect Records Affected info
Roger, here:
create table apps_UTS_ACCF
(
WHRecordID numeric(15,0) not null,
TradeDate datetime not null,
SystemID varchar(5) not null,
FastODT datetime not null,
FastOID varchar(16) not null,
Quantity int not null,
AvgPx real not null,
Ntnl float not null,
TrdCnt int not null,
MiscInfo varchar(255) null,
DtSpanCde int not null,
create_date smalldatetime not null,
update_date smalldatetime null,
UpdateBy int null,
FlagID int not null
)
lock datapages
go
create unique clustered index idx1
on apps_UTS_ACCF (TradeDate, FastOID, WHRecordID)
go
create index idx_WHRecordID
on apps_UTS_ACCF (WHRecordID)
go
grant select, insert, update, delete on apps_UTS_ACCF to etma_prod
go
grant select on apps_UTS_ACCF to etma_cemr, etma_cnr, etma_uscerf
go
*On a different note*, (and a different subject:) ) if you add a comment (--) right after +lock datapages, or event on a next line+ , dbVis complains....:
looks like when the comment keyword (--) is removed, it also removed the CRLF, which places +go+ on the same like as
+lock datapages+ directive ...
--------------------------- Debug Printouts --------------------------
11:00:37 [DEBUG pool-2-thread-401 E.?] DefaultEditor-582: SybDatabaseMetaData.getUserName()
11:00:37 [DEBUG Thread-2854 I.execute] Executing...
11:00:37 [DEBUG Thread-2854 I.setCurrentCatalog] DbConnection='ETMA_NYDEV_DS (dbo)' Catalog='tss_ds_dev2' Schema='null' NewCatalog='tss_ds_dev2'
11:00:37 [DEBUG pool-2-thread-401 E.?] DefaultEditor-582: SybDatabaseMetaData.getUserName()
11:00:37 [DEBUG Thread-2854 I.setCurrentSchema] DbConnection='ETMA_NYDEV_DS (dbo)' Catalog='null' Schema='dbo' NewSchema='dbo'
11:00:37 [DEBUG pool-2-thread-401 C.?] DefaultEditor-582: SybConnection.createStatement()
11:00:37 [DEBUG pool-2-thread-401 B.?] DefaultEditor-582: SybStatement.execute("use tss_ds_dev2")
11:00:37 [DEBUG Thread-2854 I.execute] Executing...
11:00:37 [DEBUG pool-2-thread-401 C.?] DefaultEditor-582: SybConnection.createStatement()
11:00:37 [DEBUG pool-2-thread-401 B.?] DefaultEditor-582: SybStatement.execute("create table apps_UTS_ACCF
(
WHRecordID numeric(15,0) not null,
TradeDate datetime not null, SystemID varchar(5) not null,
FastODT datetime not null, FastOID varchar(16) not null,
Quantity int not null, AvgPx real not null, Ntnl float not null, TrdCnt int not null, MiscInfo varchar(255) null, DtSpanCde int not null,
create_date smalldatetime not null, update_date smalldatetime null, UpdateBy int null,
FlagID int not null )
*lock datapages go*
create unique clustered index idx1
on apps_UTS_ACCF (TradeDate, FastOID, WHRecordID)")
*11:00:37 [DEBUG pool-2-thread-401 B.?] DefaultEditor-582: EXCEPTION -> com.sybase.jdbc3.jdbc.SybSQLException: Incorrect syntax near 'go'.*
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: Save/Update Message Popups -- incorrect Records Affected info
Hi Alex,
Please post or email the complete CREATE TABLE DDL so that we can create it locally. Make sure indexes/primary key DDL is included.
Best Regards
Roger
anonymous