You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by wt <wa...@gmail.com> on 2018/08/20 10:25:07 UTC
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
i am having trouble with the auto generated code from the web console. There
are tables with columns that contain values with special characters like
",". If i load a table that i know doesn't have special characters it works.
Does anyone know where in the load code i need to look and what sort of
change might be needed to allow special characters?
example of a value
Twenty-First Century Fox, Inc.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I have filed an issue against JIRA:
https://issues.apache.org/jira/browse/IGNITE-9334
Regards,
--
Ilya Kasnacheev
2018-08-20 18:39 GMT+03:00 wt <wa...@gmail.com>:
> i was literally about to respond with the same findings . tested it and it
> works although i should point out that it is not in an obvious place. It is
> not in the schema section but in the cache section under Store. Thanks for
> your help on this.
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by wt <wa...@gmail.com>.
i was literally about to respond with the same findings . tested it and it
works although i should point out that it is not in an obvious place. It is
not in the schema section but in the cache section under Store. Thanks for
your help on this.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
So I have found this 'sqlEscapeAll' flag that you can try and set, with
hopes that your problem will go away:
<property name="sqlEscapeAll" value="true"/>
I have attached config file with this property already set, can you replace
your own with this one, try again?
Regards,
--
Ilya Kasnacheev
2018-08-20 15:41 GMT+03:00 wt <wa...@gmail.com>:
> That column with the : is also an issue but in SQL server you can either
> quote columns in quotes or square braces. I would have expected Ignite to
> generate code with quoted columns because even a simple space in the name
> is
> a problem in the current statement regardless of the column with : in it.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by wt <wa...@gmail.com>.
That column with the : is also an issue but in SQL server you can either
quote columns in quotes or square braces. I would have expected Ignite to
generate code with quoted columns because even a simple space in the name is
a problem in the current statement regardless of the column with : in it.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
It's strange, but my expectation here is that "Exchange:Ticker" is not a
proper name for a column. Is it? I expect it to be [Exchange:Ticker] if
it's real.
SQLServerDialect should be able to quote column names so I'm having trouble
understanding what happens here.
Can anybody with SQL Server instance chime in?
Regards,
--
Ilya Kasnacheev
2018-08-20 14:58 GMT+03:00 wt <wa...@gmail.com>:
> here is the exact query intercepted
>
> declare @p1 int
> set @p1=NULL
> exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1
> nvarchar(4000)',N'SELECT CompanyId,CompanyName,Country,Region,Peer
> Group,Company Type,ExtractionDate,Exchange:Ticker,Exchange,Ticker,Legal
> Entity Identifier,Bloomberg Ticker,Bloomberg Global Composite ID,Bloomberg
> Global ID,Bloomberg Share Class BBGID,Bloomberg Unique ID,ISIN,CUSIP,Entity
> Type,Research Entity ID,Research Entity Name,Research
> type,Record_Valid_From,Record_Valid_To FROM SustainAnalytics.Company WHERE
> (CompanyId > @P0 ) AND (CompanyId <= @P1 )
> ',N'1007910068',N'1007990548'
> select @p1
>
>
> here is the error returned when i run that which fits the exception thrown
> when the load code is called
>
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by wt <wa...@gmail.com>.
here is the exact query intercepted
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1
nvarchar(4000)',N'SELECT CompanyId,CompanyName,Country,Region,Peer
Group,Company Type,ExtractionDate,Exchange:Ticker,Exchange,Ticker,Legal
Entity Identifier,Bloomberg Ticker,Bloomberg Global Composite ID,Bloomberg
Global ID,Bloomberg Share Class BBGID,Bloomberg Unique ID,ISIN,CUSIP,Entity
Type,Research Entity ID,Research Entity Name,Research
type,Record_Valid_From,Record_Valid_To FROM SustainAnalytics.Company WHERE
(CompanyId > @P0 ) AND (CompanyId <= @P1 )
',N'1007910068',N'1007990548'
select @p1
here is the error returned when i run that which fits the exception thrown
when the load code is called
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Is it possible to intercept the problematic query as sent to SQL server,
paste it into e-mail?
Regards,
--
Ilya Kasnacheev
2018-08-20 14:42 GMT+03:00 wt <wa...@gmail.com>:
> i think i found the issue it is related to the select not quoting columns.
> I
> ran a trace against the database and executed each query generated
> separately. Is this a driver issue or an ignite issue?
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by wt <wa...@gmail.com>.
i think i found the issue it is related to the select not quoting columns. I
ran a trace against the database and executed each query generated
separately. Is this a driver issue or an ignite issue?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by wt <wa...@gmail.com>.
When i load the cache for an integrated rmdbs with a table that doesn't have
values with special characters then the table loads fine. If i try the same
with a table that has values with special characters i get the following
error:
>>> Loading caches...
>>> Loading cache: CompanyCache
[12:17:33,489][SEVERE][mgmt-#40%POC%][GridTaskWorker] Failed to obtain
remote job result policy for result from ComputeTask.result(..) method (will
fail the whole task): GridJobResultImpl [job=C2 [c=LoadCacheJobV2
[keepBinary=false]], sib=GridJobSiblingImpl
[sesId=f5821f65561-d78827cc-54c1-48e4-9f5b-d99176da716a,
jobId=06821f65561-d78827cc-54c1-48e4-9f5b-d99176da716a,
nodeId=9a26a915-f108-4c59-bbef-e5cb86d662ec, isJobDone=false],
jobCtx=GridJobContextImpl
[jobId=06821f65561-d78827cc-54c1-48e4-9f5b-d99176da716a, timeoutObj=null,
attrs={}], node=TcpDiscoveryNode [id=9a26a915-f108-4c59-bbef-e5cb86d662ec,
addrs=[0:0:0:0:0:0:0:1, 127.0.0.1, 172.19.112.175],
sockAddrs=[/0:0:0:0:0:0:0:1:47500, /127.0.0.1:47500,
PC50439.oa.pnrad.net/172.19.112.175:47500], discPort=47500, order=1,
intOrder=1, lastExchangeTime=1534761975200, loc=false,
ver=2.6.0#20180710-sha1:669feacc, isClient=false], ex=class
o.a.i.IgniteException: Failed to load cache: CompanyCache, hasRes=true,
isCancelled=false, isOccupied=true]
class org.apache.ignite.IgniteException: Remote job threw user exception
(override or implement ComputeTask.result(..) method if you would like to
have automatic failover for this exception): Failed to load cache:
CompanyCache
at
org.apache.ignite.compute.ComputeTaskAdapter.result(ComputeTaskAdapter.java:102)
at
org.apache.ignite.internal.processors.task.GridTaskWorker$5.apply(GridTaskWorker.java:1047)
at
org.apache.ignite.internal.processors.task.GridTaskWorker$5.apply(GridTaskWorker.java:1040)
at
org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6727)
at
org.apache.ignite.internal.processors.task.GridTaskWorker.result(GridTaskWorker.java:1040)
at
org.apache.ignite.internal.processors.task.GridTaskWorker.onResponse(GridTaskWorker.java:858)
at
org.apache.ignite.internal.processors.task.GridTaskProcessor.processJobExecuteResponse(GridTaskProcessor.java:1077)
at
org.apache.ignite.internal.processors.task.GridTaskProcessor$JobMessageListener.onMessage(GridTaskProcessor.java:1312)
at
org.apache.ignite.internal.managers.communication.GridIoManager.invokeListener(GridIoManager.java:1556)
at
org.apache.ignite.internal.managers.communication.GridIoManager.processRegularMessage0(GridIoManager.java:1184)
at
org.apache.ignite.internal.managers.communication.GridIoManager.access$4200(GridIoManager.java:125)
at
org.apache.ignite.internal.managers.communication.GridIoManager$9.run(GridIoManager.java:1091)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: class org.apache.ignite.IgniteException: Failed to load cache:
CompanyCache
at
org.apache.ignite.internal.processors.closure.GridClosureProcessor$C2.execute(GridClosureProcessor.java:1858)
at
org.apache.ignite.internal.processors.job.GridJobWorker$2.call(GridJobWorker.java:568)
at
org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6695)
at
org.apache.ignite.internal.processors.job.GridJobWorker.execute0(GridJobWorker.java:562)
at
org.apache.ignite.internal.processors.job.GridJobWorker.body(GridJobWorker.java:491)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
at
org.apache.ignite.internal.processors.job.GridJobProcessor.processJobExecuteRequest(GridJobProcessor.java:1123)
at
org.apache.ignite.internal.processors.job.GridJobProcessor$JobExecutionListener.onMessage(GridJobProcessor.java:1921)
... 7 more
Caused by: class org.apache.ignite.IgniteException: Failed to load cache:
CompanyCache
at
org.apache.ignite.internal.util.IgniteUtils.convertException(IgniteUtils.java:990)
at
org.apache.ignite.internal.processors.cache.GridCacheAdapter$LoadCacheJob.localExecute(GridCacheAdapter.java:5517)
at
org.apache.ignite.internal.processors.cache.GridCacheAdapter$LoadCacheJobV2.localExecute(GridCacheAdapter.java:5561)
at
org.apache.ignite.internal.processors.cache.GridCacheAdapter$TopologyVersionAwareJob.execute(GridCacheAdapter.java:6176)
at
org.apache.ignite.compute.ComputeJobAdapter.call(ComputeJobAdapter.java:132)
at
org.apache.ignite.internal.processors.closure.GridClosureProcessor$C2.execute(GridClosureProcessor.java:1855)
... 14 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to load
cache: CompanyCache
at
org.apache.ignite.internal.processors.cache.store.GridCacheStoreManagerAdapter.loadCache(GridCacheStoreManagerAdapter.java:543)
at
org.apache.ignite.internal.processors.cache.distributed.dht.GridDhtCacheAdapter.localLoadCache(GridDhtCacheAdapter.java:608)
at
org.apache.ignite.internal.processors.cache.GridCacheProxyImpl.localLoadCache(GridCacheProxyImpl.java:217)
at
org.apache.ignite.internal.processors.cache.GridCacheAdapter$LoadCacheJob.localExecute(GridCacheAdapter.java:5512)
... 18 more
Caused by: javax.cache.integration.CacheLoaderException: Failed to load
cache: CompanyCache
at
org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore.loadCache(CacheAbstractJdbcStore.java:819)
at
org.apache.ignite.internal.processors.cache.store.GridCacheStoreManagerAdapter.loadCache(GridCacheStoreManagerAdapter.java:520)
... 21 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to load
cache
at
org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore$1.call(CacheAbstractJdbcStore.java:474)
at
org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore$1.call(CacheAbstractJdbcStore.java:434)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
... 3 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax
near ','.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)
at
org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore$1.call(CacheAbstractJdbcStore.java:461)
... 5 more
[12:17:55] Ignite node stopped OK [name=POC, uptime=00:31:38.670]
Exception in thread "main" javax.cache.integration.CacheLoaderException:
Failed to load cache: CompanyCache
at
org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore.loadCache(CacheAbstractJdbcStore.java:819)
at
org.apache.ignite.internal.processors.cache.store.GridCacheStoreManagerAdapter.loadCache(GridCacheStoreManagerAdapter.java:520)
at
org.apache.ignite.internal.processors.cache.distributed.dht.GridDhtCacheAdapter.localLoadCache(GridDhtCacheAdapter.java:608)
Disconnected from the target VM, address: '127.0.0.1:49386', transport:
'socket'
at
org.apache.ignite.internal.processors.cache.GridCacheProxyImpl.localLoadCache(GridCacheProxyImpl.java:217)
at
org.apache.ignite.internal.processors.cache.GridCacheAdapter$LoadCacheJob.localExecute(GridCacheAdapter.java:5512)
at
org.apache.ignite.internal.processors.cache.GridCacheAdapter$LoadCacheJobV2.localExecute(GridCacheAdapter.java:5561)
at
org.apache.ignite.internal.processors.cache.GridCacheAdapter$TopologyVersionAwareJob.execute(GridCacheAdapter.java:6176)
at
org.apache.ignite.compute.ComputeJobAdapter.call(ComputeJobAdapter.java:132)
at
org.apache.ignite.internal.processors.closure.GridClosureProcessor$C2.execute(GridClosureProcessor.java:1855)
at
org.apache.ignite.internal.processors.job.GridJobWorker$2.call(GridJobWorker.java:568)
at
org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6695)
at
org.apache.ignite.internal.processors.job.GridJobWorker.execute0(GridJobWorker.java:562)
at
org.apache.ignite.internal.processors.job.GridJobWorker.body(GridJobWorker.java:491)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
at
org.apache.ignite.internal.processors.job.GridJobProcessor.processJobExecuteRequest(GridJobProcessor.java:1123)
at
org.apache.ignite.internal.processors.job.GridJobProcessor$JobExecutionListener.onMessage(GridJobProcessor.java:1921)
at
org.apache.ignite.internal.managers.communication.GridIoManager.invokeListener(GridIoManager.java:1556)
at
org.apache.ignite.internal.managers.communication.GridIoManager.processRegularMessage0(GridIoManager.java:1184)
at
org.apache.ignite.internal.managers.communication.GridIoManager.access$4200(GridIoManager.java:125)
at
org.apache.ignite.internal.managers.communication.GridIoManager$9.run(GridIoManager.java:1091)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to load
cache
at
org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore$1.call(CacheAbstractJdbcStore.java:474)
at
org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore$1.call(CacheAbstractJdbcStore.java:434)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
... 3 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax
near ','.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)
at
org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore$1.call(CacheAbstractJdbcStore.java:461)
... 5 more
Process finished with exit code 1
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by "ilya.kasnacheev" <il...@gmail.com>.
Hello!
Unfortunately I still do not understand what kind of problem you are having.
Can you please make screenshot where the problem is apparent?
Regards,
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by wt <wa...@gmail.com>.
POC-project.zip
<http://apache-ignite-users.70518.x6.nabble.com/file/t1892/POC-project.zip>
cols.png
<http://apache-ignite-users.70518.x6.nabble.com/file/t1892/cols.png>
sqljdbc42.jar
<http://apache-ignite-users.70518.x6.nabble.com/file/t1892/sqljdbc42.jar>
here is the project. The underlying database is an sql server database. I
have attached the jar i am also using for the driver.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
We would need examples of auto-generated code or a screenshot at least.
Regards,
--
Ilya Kasnacheev
2018-08-20 13:25 GMT+03:00 wt <wa...@gmail.com>:
> i am having trouble with the auto generated code from the web console.
> There
> are tables with columns that contain values with special characters like
> ",". If i load a table that i know doesn't have special characters it
> works.
> Does anyone know where in the load code i need to look and what sort of
> change might be needed to allow special characters?
>
> example of a value
>
> Twenty-First Century Fox, Inc.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>