Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Support

Thread: Varchar(MAX) inline editing?


Permlink Replies: 7 - Pages: 1 - Last Post: Dec 1, 2011 4:11 PM Last Post By: Vince Swann
Vince Swann

Posts: 169
Registered: 03/27/07
Varchar(MAX) inline editing?
Posted: Nov 4, 2011 5:14 PM
   thread.click_reply Reply
Why can't I edit SQL Server's varchar(MAX) fields inline?
I have to open the Cell editor for each, which is getting painfully slow when I have to make a lot of edits.
Roger Bjarevall


Posts: 5,042
Registered: 12/17/04
Re: Varchar(MAX) inline editing?
Posted: Nov 7, 2011 1:20 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

Do you get any confirmation window when you try to edit the value inline?

In the result set grid, right-click and select "Describe Data" . What is the value for "Type (Java)" for the VARCHAR(MAX) column?

Regards

Roger
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Varchar(MAX) inline editing?
Posted: Nov 9, 2011 2:00 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
 global.attachment temp2.png (10.4 KB )
 global.attachment temp.png (10.0 KB )
Hi Roger,

That's a very good question. I'm not sure how to find out as I don't see Type(Java) in either the table's Columns tab nor by expanding the table to see the columns and looking at its properties. I can't even see an indication of it in the debug window.

However, the situation is easily replicated by creating the following database on SQL Server 2008:

create database tstdb
go
use tstdb
go
create table foo (
id int
, smalltxt varchar(255)
, bigtxt varchar(max)
)

Then simply go to the data tab for the foo table and try to insert a row. You can edit the smalltxt column inline but typing text into the bigtxt column results in nothing being entered. There is no feedback to say why.

Double-clicking the smalltxt column results in an inline cursor showing in that cell, allowing editing. Double-clicking the bigtxt column results in the cell editor being opened instead.

I attach the column details of the smalltxt and bigtxt columns for comparison. The most likely culprit to me is that the smalltxt column has a SQL_DATA_TYPE of 12 and the bigtxt column has a SQL_DATA_TYPE of -1. The COLUMN_SIZE of bigtxt is 2147483647, which might also be a problem.

I presume DBVisualizer is opening the cell editor either if the data type isn't in a list of known char types or if the column size exceeds a certain value. I think VARCHAR(MAX) fields should be an exception here as they can hold very large chunks of text but that doesn't mean they actually will do when edited.
Roger Bjarevall


Posts: 5,042
Registered: 12/17/04
Re: Varchar(MAX) inline editing?
Posted: Nov 9, 2011 3:17 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

Thanks!

The Java Type is listed when you right-click in the result set grid and choose "Describe Data". You find it among the information being presented.

Anyway, I think it will say java.lang.String.

What version of DbVisualizer are you using and what JDBC driver and version?

Regards

Roger
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Varchar(MAX) inline editing?
Posted: Nov 9, 2011 5:32 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
 global.attachment temp.png (7.6 KB )
Thanks Roger, that cleared up my confusion.

Unlike the VARCHAR(255) column, the VARCHAR(MAX)one has a Java type of com.onseven.dbvis.sql.TextData.

See attached image of the Describe Data dialog.

I am using DBVisualiser 8.0.5 and jTDS 1.2.

I guess VARCHAR(MAX) is being treated the same as TEXT. I would think both those types should be editable inline unless the existing data is very large or contains carriage return characters.
Roger Bjarevall


Posts: 5,042
Registered: 12/17/04
Re: Varchar(MAX) inline editing?
Posted: Nov 10, 2011 1:13 AM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
Hi,

Just discovered that there is a difference in how the MS SQL Server driver handles VARCHAR(max) (which was the driver I tried with) compared with jTDS.

The MS driver reports the column as a standard VARCHAR which is editable inline in DbVisualizer.
jTDS however treats the column as a LOB and inline editing is then disabled in DbVisualizer (by design).

To workaround this set the "USELOBS" driver property for jTDS to "false" and then re-connect.

Regards

Roger
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Varchar(MAX) inline editing?
Posted: Nov 10, 2011 11:49 AM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
Thanks Roger,

That workaround works perfectly.
Vince Swann

Posts: 169
Registered: 03/27/07
Re: Varchar(MAX) inline editing?
Posted: Dec 1, 2011 4:11 PM    global.in_response_to.tooltip in response to: Vince Swann
   thread.click_reply Reply
 global.attachment temp2.png (5.5 KB )
 global.attachment temp.png (19.6 KB )
I just wanted to add that this same workaround also fixes a similar issue, where the option to show CLOBs by value in the preferences is ignored if browsing or editing the row in a window.

Again, just add UseLOBs=false to the connection string and it works as expected.

Example attached which shows the output prior to adding UseLOBs=false.

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