在命令行中使用WbExport时缺少数据和SQL脚本文件 [英] Data and sql script files are missing while using WbExport in command line

问题描述

从命令行运行包含WbExport(使用Select语句)的sql脚本时，我遇到一个问题.目标文件夹中缺少txt文件和导入sql脚本 命令提示符显示该语句已执行，但我认为执行该命令时select语句未运行.

I am facing one issue while running a sql script which contains WbExport (using Select statement) from command line. The txt file and import sql script is missing from the destination folder The command prompt displays that statements are executed but I think the select statement is not running when I execute the command.

在不使用Select语句的情况下，我正在获取所需的输出文件，但是Import SQL脚本中缺少列名，这就是我在WbExport中尝试Select语句方法的原因.

Without using Select statement I am getting the required output files but column names are missing from the Import SQL script thats why I tried Select statement method in WbExport.

命令:

java -jar sqlworkbench.jar -profile=New_profile -script=cmd_test.sql 

sql脚本(cmd_test.sql):

sql script(cmd_test.sql):

WbExport 
    -type=text
    -file='D:\Migration\CMD_TEST\employee.txt'
    -delimiter='~'
    -quotechar='^'
    -encoding=UTF8
    -quoteCharEscaping=duplicate
    -formatFile=postgres
    -header=true
    -decimal='.'
    -dateFormat='yyyy-MM-dd'
    -replaceExpression='(\n|\r\n)' -replaceWith='';
select * from ces_prj_raymond_poc.dbo.employee; ------------I think this statement is not executing.

TXT数据文件和表的导入sql脚本

Txt data file and import sql script for table

推荐答案

已交叉发布到SQL Workbench/J

This has been cross posted to the SQL Workbench/J support group

问题是新的Microsoft JDBC驱动程序错误地对待某些JDBC API调用的参数，并且不再支持通配符. SQL Workbench/J在调用例如DatabaseMetaData.getColumns().显然，从7.4版开始的Microsoft JDBC驱动程序不再使用LIKE语句，因此方法调用(使用转义的通配符)不会返回任何内容.

The problem is that the new Microsoft JDBC driver treats parameters to some JDBC API calls incorrectly and does not support wildcards any more. SQL Workbench/J escapes standard SQL wildcard characters before calling e.g. DatabaseMetaData.getColumns(). Apparently the Microsoft JDBC driver starting with version 7.4 does not use a LIKE statement anymore and thus the method call (using escaped wildcards) does not return anything.

可以通过设置配置属性在SQL Workbench/J中禁用转义通配符.通过手动编辑workbench.settings并添加:

Escaping wildcard characters can be disabled inside SQL Workbench/J by setting a configuration property. Either by editing workbench.settings manually and adding:

workbench.microsoft_sql_server.metadata.retrieval.wildcards=false

在编辑文件之前，请确保SQL Workbench/J已关闭.

Before editing the file, make sure SQL Workbench/J is closed.

这也可以在不重新启动应用程序的情况下进行配置.

This can also be configured without restaring the application.

连接到SQL Server数据库时，也可以通过运行以下Workbench命令在SQL Workbench/J中完成此操作:

When being connected to a SQL Server database, this can also be done from within SQL Workbench/J by running the following Workbench command:

WbSetDBconfig metadata.retrieval.wildcards=false;

这篇关于在命令行中使用WbExport时缺少数据和SQL脚本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！