Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Support

Thread: Save/Update Message Popups -- incorrect Records Affected info

This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 10 - Pages: 1 - Last Post: Mar 16, 2010 8:17 AM Last Post By: Roger Bjarevall
AlexML

Posts: 407
Registered: 05/04/06
Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 5, 2010 4:43 PM
 
   thread.click_reply Reply
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
Roger Bjarevall


Posts: 5,087
Registered: 12/17/04
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 5, 2010 4:49 PM    global.in_response_to.tooltip in response to: AlexML
 
   thread.click_reply Reply
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
AlexML

Posts: 407
Registered: 05/04/06
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 5, 2010 5:05 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply

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 Bjarevall


Posts: 5,087
Registered: 12/17/04
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 5, 2010 5:36 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Alex,

Related to the editing issue, what happen if you re-start DbVisualizer and then perform the edit?

Regards

Roger
AlexML

Posts: 407
Registered: 05/04/06
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 5, 2010 5:41 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Roger,

I opened a new dbVis session and edits now work... What are your thoughts?
Roger Bjarevall


Posts: 5,087
Registered: 12/17/04
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 6, 2010 11:59 AM    global.in_response_to.tooltip in response to: AlexML
 
   thread.click_reply Reply
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
AlexML

Posts: 407
Registered: 05/04/06
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 8, 2010 1:28 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
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 Bjarevall


Posts: 5,087
Registered: 12/17/04
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 9, 2010 2:26 PM    global.in_response_to.tooltip in response to: AlexML
 
   thread.click_reply Reply
Hi Alex,

Yes I hope it is related.

Best Regards

Roger
AlexML

Posts: 407
Registered: 05/04/06
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 9, 2010 4:13 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Thanks Roger. Looking forward to you fixing this! Also, can you please comment on the second issue of this post ( issue with a go )
Hans Bergsten


Posts: 1,571
Registered: 10/11/06
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 9, 2010 8:28 PM    global.in_response_to.tooltip in response to: AlexML
 
   thread.click_reply Reply
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
Roger Bjarevall


Posts: 5,087
Registered: 12/17/04
Re: Save/Update Message Popups -- incorrect Records Affected info
Posted: Mar 16, 2010 8:17 AM    global.in_response_to.tooltip in response to: Hans Bergsten
 
   thread.click_reply Reply
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

Point your RSS reader here for a feed of the latest messages in all forums