DBeaver :- Generate Schema – Datatypes and Column Widths – Error – “Cannot specify a column width on data type”

Background

Let us stay with Dbeaver and point out a slightly different issue.

 

Sample Data

SQL Server

DDL

DDL – Create Schema and Table


use [tempdb]
go

/*
    drop schema [datatype];
*/

if schema_id('datatype') is null
begin

    exec('create schema [datatype] authorization [dbo]');

end
go

/*
    drop table [datatype].[personImage]
*/
if object_id('[datatype].[personImage]') is null
begin

    create table [datatype].[personImage]
    (

          [id]              int not null
                            identity(1,1)

        , [tag]             binary(20) not null

        , [fingerPrint]     varbinary(4000) null

        , [signature]       binary null

        , [picture]         image

        , [ts]              timestamp not null

    )

end
go

DBeaver

Launched Dbeaver and worked towards scripting the object’s DDL.

Outline

Here are the steps :-

  1. Connect to the Database
  2. Transverse the Database Navigator outline and select Instance\Database\Schema\Tables
  3. Select the specific Table
  4. Right click on the selected table
    • From the dropdown menu, please choose to “Generate SQL” \ DDL

Images

Image – Database Navigator

databaseNavigator.01.20200326.0519AM

Generated SQL

Here is the generated SQL


-- tempdb.datatype.personImage definition

-- Drop table

-- DROP TABLE tempdb.datatype.personImage GO

CREATE TABLE tempdb.datatype.personImage (
	id int IDENTITY(1,1) NOT NULL,
	tag binary(20) NOT NULL,
	fingerPrint varbinary(4000) NULL,
	signature binary(1) NULL,
	picture image(16) NULL,
	ts timestamp(8) NOT NULL
) GO;

Apply SQL

The next step is to apply the SQL to a targeted system.

Error

Error – Incorrect syntax near ‘GO’.

Error Image

incorrectSyntaxNearGo.01.20200326.0526AM.PNG

Error Text


Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'GO'.

Error – Cannot specify a column width on data type image.

Error Image

cannotSpecifyAColumnWidthOnDatatypeImage.01.20200326.0528AM

Error Text


Msg 2716, Level 16, State 1, Line 7
Column, parameter, or variable #5: Cannot specify a column width on data type image.

Error – Cannot specify a column width on data type timestamp

Error Image

cannotSpecifyAColumnWidthOnDatatypeTimestamp.01.20200326.0531AM

Error Text


Msg 2716, Level 16, State 1, Line 7
Column, parameter, or variable #6: Cannot specify a column width on data type timestamp.

Remediation

Strip off column widths

Outline

Using a simple text editor look for the extra specifications, and replace them with an empty string.

Sample

picture image(16) NULL
ts timestamp(8) NOT NULL

Find & Replace

  1. image(16) with image
  2. timestamp(8) with timestamp

 

Versions Tested

Here are the versions we tested against :-

  1. DBeaver
    • 7.0.1
      • Date Released :- 2020-March-
    • 6.2.4

Bug Filed

  1. Dbeaver
    • Generated DDL against SQL Server adds column width to certain datatypes though not applicable #8282
      • Bug # :- 8282
      • Link :- Link

Dedication

In service to Serge Rider and the rest of the crew; I will never argue for a more deserving group.

 

Addendum

Addendum – 2020-04-27

Link

Here is the web link.

Transcript

Development

Ready for QA
Notes
  1. serge-rider added a commit that referenced this issue 3 hours ago
    @serge-rider
  2. #8282 Column type modifiers fix (picture, timestamp)
  3. serge-rider moved this from Backlog to Ready for QA in DBeaver board 3 hours ago
Image

script.table.sergeRider.01.20200427.0910AM.PNG

QA

QA – Comment – Still Reproducible
Notes

@uslss uslss moved this from Ready for QA to In Progress in DBeaver board 30 minutes ago

Image

script.table.01.20200427.0906AM

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s