SQL Workbench formatter

35 views
Skip to first unread message

Akash Nath

unread,
Oct 7, 2021, 4:41:21 AM10/7/21
to SQL Workbench/J - DBMS independent SQL tool
Hi Thomas,

I use the SQL formatter all the time and it was working perfectly. Off late I have noticed that formatting does not seem to work for create table command:

BEFORE FORMATTING:

CREATE OR REPLACE TABLE ECP_DEV.STAGE_DIMS.THL_2020 
(
  PY                                                           VARCHAR(20),
  CCN                                                        VARCHAR(20),
  TIN                                                          VARCHAR(20),
  PECOS_HOSPITAL_NAME                  VARCHAR(255),
  PECOS_LEGAL_BUSINESS_NAME    VARCHAR(255),
  PECOS_STREET_ADDRESS                VARCHAR(255),
  PECOS_PO_BOX                                   VARCHAR(20)
);

AFTER FORMATTING (CTRL + R):

CREATE OR REPLACE TABLE ECP_DEV.STAGE_DIMS.THL_2020 (PY VARCHAR(20),CCN VARCHAR(20),TIN VARCHAR(20),PECOS_HOSPITAL_NAME VARCHAR(255),PECOS_LEGAL_BUSINESS_NAME VARCHAR(255),PECOS_STREET_ADDRESS VARCHAR(255),PECOS_PO_BOX VARCHAR(20));


Has anything changed?

I am using 127 and Java 11.0.6

Thank you
Akash

Thomas Kellerer

unread,
Oct 11, 2021, 2:39:59 AM10/11/21
to sql-wo...@googlegroups.com
Hmm, I can not reproduce this.

Maybe there are some hidden special characters in the text that for some reason confuse the parser/formatter.

Regards
Thomas

Akash Nath

unread,
Oct 11, 2021, 6:29:11 AM10/11/21
to SQL Workbench/J - DBMS independent SQL tool
Its confusing :-(

I tried to type a simple create table statement:

CREATE OR REPLACE TEMP TABLE ECP_PROD.CORE.TEST1
(
  A VARCHAR(1),
  B VARCHAR(1),
  C VARCHAR(1),
  D VARCHAR(1),
  E INT
);

 and my Reformat SQL formats it as:

CREATE OR REPLACE TEMP TABLE ECP_PROD.CORE.TEST1 (A VARCHAR(1),B VARCHAR(1),C VARCHAR(1),D VARCHAR(1),E INT);

I guess its something to do with my settings, could you assist me figure it out? Thank you

Akash

workbench.editor.autocompletion.closewithsearch=true
workbench.editor.autocompletion.current.schema=false
workbench.editor.autocompletion.paste.case=upper
workbench.editor.autocompletion.paste.sort=name
workbench.editor.autojumpnext=false
workbench.editor.autosave=never
workbench.editor.bracket.hilite.both=false
workbench.editor.bracket.hilite.enable=true
workbench.editor.bracket.hilite.left=true
workbench.editor.bracket.hilite.rectangle=true
workbench.editor.color.comment1=255,102,102
workbench.editor.color.comment2=255,102,102
workbench.editor.color.cursor=255,255,255
workbench.editor.color.datatype=255,51,204
workbench.editor.color.error=255,0,0
workbench.editor.color.foreground=255,255,255
workbench.editor.color.gutter=51,51,51
workbench.editor.color.keyword1=0,153,153
workbench.editor.color.keyword2=51,51,255
workbench.editor.color.keyword3=0,153,0
workbench.editor.color.linenumber=255,255,255
workbench.editor.color.literal1=204,204,0
workbench.editor.color.literal2=255,153,255
workbench.editor.color.operator=255,153,0
workbench.editor.currentstmt.color=0,255,0
workbench.editor.electricscroll=0
workbench.editor.expand.macro.key=32,0
workbench.editor.format.list.maxelements.nonquoted=10
workbench.editor.format.list.maxelements.quoted=2
workbench.editor.highlightcurrent=true
workbench.editor.lineending.external=default
workbench.editor.lineending.internal=lf
workbench.editor.nowordsep=_$
workbench.editor.occurance.highlight.casesensitive=true
workbench.editor.occurance.highlight.color=255,255,0
workbench.editor.occurance.highlight.enable=true
workbench.editor.occurance.highlight.minlength=2
workbench.editor.occurance.highlight.nowhitespace=true
workbench.editor.plain.wordwrap=true
workbench.editor.rightclickmovescursor=false
workbench.editor.showlinenumber=true
workbench.editor.sql.emptyline.delimiter=false
workbench.editor.syntax.style.comment1=1
workbench.editor.syntax.style.comment2=1
workbench.editor.syntax.style.datatype=1
workbench.editor.syntax.style.keyword1=1
workbench.editor.syntax.style.keyword2=1
workbench.editor.syntax.style.keyword3=1
workbench.editor.syntax.style.literal1=0
workbench.editor.syntax.style.literal2=0
workbench.editor.syntax.style.operator=1
workbench.editor.tabwidth=3
workbench.editor.usetab=false

workbench.sql.create.view.columnlist=true

workbench.sql.formatter.comma.afterLineBreak=false
workbench.sql.formatter.comma.spaceAfterLineBreakComma=false
workbench.sql.formatter.comma.spaceafter=true
workbench.sql.formatter.datatype.case=upper
workbench.sql.formatter.functions.case=upper
workbench.sql.formatter.identifier.case=upper
workbench.sql.formatter.insert.columnsperline=1
workbench.sql.formatter.insert.values.columnname=false
workbench.sql.formatter.join.condition.wrapstyle=onlyMultiple
workbench.sql.formatter.keywords.case=upper
workbench.sql.formatter.select.columnsperline=1
workbench.sql.formatter.subselect.maxlength=60
workbench.sql.formatter.update.columnsperline=1

workbench.formatter.db2.enabled=false
workbench.formatter.db2.supports.scripts=false
workbench.formatter.default.enabled=false
workbench.formatter.default.supports.scripts=false
workbench.formatter.firebird.enabled=false
workbench.formatter.firebird.supports.scripts=false
workbench.formatter.h2.enabled=false
workbench.formatter.h2.supports.scripts=false
workbench.formatter.hsql_database_engine.enabled=false
workbench.formatter.hsql_database_engine.supports.scripts=false
workbench.formatter.informix_dynamic_server.enabled=false
workbench.formatter.informix_dynamic_server.supports.scripts=false
workbench.formatter.microsoft_sql_server.enabled=false
workbench.formatter.microsoft_sql_server.supports.scripts=false
workbench.formatter.mysql.enabled=false
workbench.formatter.mysql.supports.scripts=false
workbench.formatter.oracle.enabled=false
workbench.formatter.oracle.supports.scripts=false
workbench.formatter.postgresql.enabled=false
workbench.formatter.postgresql.supports.scripts=false



workbench.sql.generate.comment.includeempty=false
workbench.sql.generate.delete.doformat=true
workbench.sql.generate.insert.doformat=true
workbench.sql.generate.insert.ignoreidentity=true
workbench.sql.generate.table.case=upper
workbench.sql.generate.update.doformat=true
workbench.sql.historysize=15
workbench.sql.lastscriptdir=C:\\Users\\aknx\\Documents\\Python Scripts\\aknx\\Redshift DDL
workbench.sql.log.obfuscate=false
workbench.sql.log.statements=false
workbench.sql.replace.criteria.history="CDW_PROD";"ECP_PROD";"_DEV";"ECP_TEST";"ECP_DEV";"VARCHAR(255)";";";"NOT NULL"
workbench.sql.replace.criteria.lastvalue=CDW_PROD
workbench.sql.replace.ignoreCase=false
workbench.sql.replace.replacement.history="CDW_DEV";"ECP_DEV";"_TEST";"ECP_PROD";"ECP_TEST"
workbench.sql.replace.replacement.lastvalue=CDW_DEV
workbench.sql.replace.selectedText=false
workbench.sql.replace.useRegEx=false
workbench.sql.replace.wholeWord=false
workbench.sql.replace.window.1376x774.x=447
workbench.sql.replace.window.1376x774.y=247
workbench.sql.replace.wrapSearch=false
workbench.sql.search.history="with";"cast";"centris";"TOTAL_ATT";"total_atte";"xref";"roster";"NULL";"PROD.ECP_DEV_NAME";"group";"prod_g";"splt";"veeva";"CONTRACT";"contract";"coal";"null";"IDF.ORG.PRTY_ID";"information";"employee";"INTERACTIONCATEGORY";"attendees.meal_consumedy";"not in";"testing";"target"
workbench.sql.search.ignoreCase=true
workbench.sql.search.useRegEx=false
workbench.sql.search.wholeWord=false
workbench.sql.search.wrapSearch=false



workbench.gui.MainWindow.1376x774.height=747
workbench.gui.MainWindow.1376x774.width=1390
workbench.gui.MainWindow.1376x774.x=1368
workbench.gui.MainWindow.1376x774.y=-7
workbench.gui.MainWindow.macropopup.visible=false
workbench.gui.MainWindow.screen=\\Display0
workbench.gui.MainWindow.state=6
workbench.gui.autocompletion.cycle.popup=true
workbench.gui.autocompletion.filtersearch=true
workbench.gui.autocompletion.partialsearch=true
workbench.gui.autocompletion.sortcolumns=true
workbench.gui.autoconnect=true
workbench.gui.bookmarks.colwidths.save=false
workbench.gui.bookmarks.sort.save=false
workbench.gui.cancel.firstconnect.exit=false
workbench.gui.closebutton.right=true
workbench.gui.closetab.confirm=false
workbench.gui.closetab.multiple.confirm=true
workbench.gui.columnselector.clipboardcopy.formattext=false
workbench.gui.columnselector.clipboardcopy.includeheader=true
workbench.gui.columnselector.clipboardcopy.selectedonly=true
workbench.gui.completioncache.localstorage=always
workbench.gui.completioncache.localstorage.maxage=30d
workbench.gui.data.append.results=false
workbench.gui.data.column.header.includetable=false
workbench.gui.data.column.header.tablename.columnprefix=false
workbench.gui.data.maxrows=0
workbench.gui.data.resultname.firsttable=true
workbench.gui.data.selection.summary=true
workbench.gui.data.sql.tooltip=full
workbench.gui.dbexplorer.tablelist.sort=false
workbench.gui.dbobjects.ObjectScripterUI.1376x774.height=400
workbench.gui.dbobjects.ObjectScripterUI.1376x774.width=500
workbench.gui.dbobjects.ObjectScripterUI.1376x774.x=438
workbench.gui.dbobjects.ObjectScripterUI.1376x774.y=166
workbench.gui.dbobjects.autoselectdatapanel=false
workbench.gui.dbobjects.autoselectsrcpanel=true
workbench.gui.dbobjects.showfocus=false
workbench.gui.dbobjects.tabletabs=top
workbench.gui.dbtree.position=left
workbench.gui.dbtree.quickfilter=true
workbench.gui.dbtree.use.tab.connection=true
workbench.gui.desktop.scalefonts=true
workbench.gui.display.decimal.group=,
workbench.gui.display.multiline.editor.wrap=false
workbench.gui.display.multiline.renderer.wrap=false
workbench.gui.display.multilinethreshold=250
workbench.gui.display.name_at_end=false
workbench.gui.display.result.sql=false
workbench.gui.display.resulttab.closebutton=false
workbench.gui.display.rowheightresize=false
workbench.gui.display.showfilename=none
workbench.gui.display.showprofilegroup=true
workbench.gui.display.showpworkspace=true
workbench.gui.display.showurl=false
workbench.gui.display.showurl.includeuser=false
workbench.gui.display.sqltab.closebutton=false
workbench.gui.display.tab.closebutton.onlyactive=false
workbench.gui.display.timeformat=HH:mm:ss
workbench.gui.display.titlegroupbracket=[
workbench.gui.display.titlegroupsep=/
workbench.gui.edit.profile.sshconfig.1376x774.height=521
workbench.gui.edit.profile.sshconfig.1376x774.width=393
workbench.gui.edit.profile.sshconfig.last_version=127
workbench.gui.edit.requiredfield.color=255,100,100
workbench.gui.edit.requiredfield.dohighlight=true
workbench.gui.edit.toolbar.dialog.1376x774.height=522
workbench.gui.edit.toolbar.dialog.1376x774.width=972
workbench.gui.edit.warn.discard.changes=false
workbench.gui.editor.defaultdir=C:\\Users\\inital\\Documents\\SQL Workbench
workbench.gui.editor.errorjump=true
workbench.gui.editor.exec.disable=false
workbench.gui.editor.execute.highlighterror=true
workbench.gui.editor.execute.onlyselected=false
workbench.gui.editor.file.reloadtype=none
workbench.gui.editor.followfiledir=false
workbench.gui.editor.wheelscroll.units=4
workbench.gui.fontzoom.mousewheel=true
workbench.gui.form.fieldlines=5
workbench.gui.form.fieldwidth=30
workbench.gui.keep.currentsql.selection=true
workbench.gui.language=en
workbench.gui.log.consolidate=false
workbench.gui.lookandfeelclass=com.bulenkov.darcula.DarculaLaf
workbench.gui.macropopup.enter.run=true
workbench.gui.macropopup.esc.closes=false
workbench.gui.macropopup.show.filter=true
workbench.gui.macropopup.useworkspace=false
workbench.gui.mainwindow.showtoolbar=true
workbench.gui.mainwindow.tabpolicy=0
workbench.gui.maxrows.tooltipwarning=true
workbench.gui.maxrows.warning.show=true
workbench.gui.menu.showicons=true
workbench.gui.optimalrowheight.automatic=false
workbench.gui.optimalrowheight.ignore.emptylines=false
workbench.gui.optimalrowheight.maxlines=10
workbench.gui.optimalwidth.automatic=true
workbench.gui.optimalwidth.includeheader=true
workbench.gui.optimalwidth.maxsize=850
workbench.gui.optimalwidth.minsize=50
workbench.gui.profiles.DriverEditorDialog.1376x774.height=446
workbench.gui.profiles.DriverEditorDialog.1376x774.width=700
workbench.gui.profiles.DriverEditorDialog.last_version=127
workbench.gui.profiles.ProfileSelectionDialog.1376x774.height=628
workbench.gui.profiles.ProfileSelectionDialog.1376x774.width=1053
workbench.gui.profiles.ProfileSelectionDialog.last_version=127
workbench.gui.profiles.divider=282
workbench.gui.profiles.quickfilter=true
workbench.gui.profiles.quickfilter.initialfocus=true
workbench.gui.query.retrieve.comments=false
workbench.gui.quickfilter.useregex=true
workbench.gui.renderer.null.fontstyle=0
workbench.gui.renderer.numberalignment=right
workbench.gui.scale.menuicon=true
workbench.gui.script.alert=false
workbench.gui.script.alert.minduration=0
workbench.gui.script.alert.systemtray=false
workbench.gui.settings.SettingsPanel.1376x774.height=618
workbench.gui.settings.SettingsPanel.1376x774.width=944
workbench.gui.sql.current.line.statement=false
workbench.gui.sql.error.prompt=PromptWithErroressage
workbench.gui.sql.join.completion.prefer.using=false
workbench.gui.sql.join.completion.use.parenthesis=false
workbench.gui.sql.script.showtime=true
workbench.gui.sql.script.statement.feedback=true
workbench.gui.sql.script.statement.showtime=true
workbench.gui.table.alternate.use=false
workbench.gui.table.header.bold=true
workbench.gui.table.header.include.remarks=false
workbench.gui.table.header.include.type=true
workbench.gui.table.rownumber.show=true
workbench.gui.table.searchhighlite.color=255,255,0
workbench.gui.tabs.lru=true
workbench.gui.tabs.showindex=false
workbench.gui.updatecheck.interval=30
workbench.gui.updatecheck.lastcheck=2021-10-11
workbench.gui.values.creator.delimiter=,
workbench.gui.values.creator.emptystring.null=false
workbench.gui.values.creator.regex=false
workbench.gui.values.creator.trim.delimiter=true

Stephen Zander

unread,
Nov 10, 2021, 12:47:53 AM11/10/21
to SQL Workbench/J - DBMS independent SQL tool
For me (using Build128)

With a connection to AWS EMR Hive and `replace` is flagged as a function by the SQL highlighter, the formatter does not like `or replace` in that command (for me running Build128).  Remove `or replace` and the SQL formats.

With a connection to AWS RedShift and `replace` flagged as a keyword by the SQL highlighter, the formatter handles things just fine.
Reply all
Reply to author
Forward
0 new messages