select count (*) doesn't give me a valid result

Maike_Hagebusch · August 13, 2015, 8:59am

Hi,

may be here is someone who can help me.

I have created a select count (*) statement for my database in order to check if there is a record and afterwards update the db entry.

First of all i am not sure if i used the right way for variable substitution.

Is it:

PZ_VON = ‘${PZ}’ and FileType= ‘${Typ}’ or
(PZ_VON = ? AND FileType = ?)

the complete sql statement works if i use the first way of variable substitution, but for some reason i get 0 as result, but it should be 1. Any ideas on what i am doing wrong?

Thanks very much in advance

Holger_von_Thomsen · August 13, 2015, 2:18pm

Hi Maike,

the format of the variables depend on the service template being used.
See JDBC Adapter Developers Guide for Details.

Can you share a screen shot of the adapter service?

Additionally to avoid full table scans it is better to use count(0) or count(1) instead of count(*).

Regards,
Holger

Maike_Hagebusch · August 13, 2015, 2:28pm

I just shared a screenshot of the attachment.

The table itself isn’t to big. It will have less then 200 lines when done.

Thanks!

Ahamed · August 13, 2015, 3:34pm

Hi Maike,

You just remove the single quotes on your dynamic content.

PZ_VON = ${PZ} and FileType= ${Typ}

Hope it’ll work…

Maike_Hagebusch · August 14, 2015, 7:20am

If i remove the single quotes i get the error message below:

Configuration name: create_entries_ProcessedPmesFiles
Configuration location: C:/Users/te_dvpmes_004/workspace95/.metadata/.plugins/org.eclipse.debug.core/.launches/create_entries_ProcessedPmesFiles.launch

com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service dp.de.inp.proc.pdv_dv_infra.v1_0.adapterInternal.table_INP_WM_PDV_DV_INFRA_PROCESSED_PMES_FILES:check_ExistingValue.
[ADA.1.316] Cannot execute the SQL statement “select count (1) from [DV_INFRA].[dbo].[INP_WM_PDV_DV_INFRA_PROCESSED_PMES_FILES] where PZ_VON = ${PZ} and FileType= ${Typ}”. "
(S0001/102) Incorrect syntax near ‘{’."
Incorrect syntax near ‘{’.
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceException.java:122)
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceException.java:113)
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceException.java:100)
at com.wm.pkg.art.ns.AdapterServiceNode.invokeService(AdapterServiceNode.java:388)
at com.wm.pkg.art.ns.ARTNSService.baseInvoke(ARTNSService.java:53)
at com.wm.app.b2b.server.invoke.InvokeManager.process(InvokeManager.java:640)
at com.wm.app.b2b.server.util.tspace.ReservationProcessor.process(ReservationProcessor.java:39)
at com.wm.app.b2b.server.invoke.StatisticsProcessor.process(StatisticsProcessor.java:49)
at com.wm.app.b2b.server.invoke.ServiceCompletionImpl.process(ServiceCompletionImpl.java:243)
at com.wm.app.b2b.server.invoke.ValidateProcessor.process(ValidateProcessor.java:49)
at com.wm.app.b2b.server.invoke.PipelineProcessor.process(PipelineProcessor.java:171)
at com.wm.app.b2b.server.ACLManager.process(ACLManager.java:289)
at com.wm.app.b2b.server.invoke.DispatchProcessor.process(DispatchProcessor.java:34)
at com.wm.app.b2b.server.AuditLogManager.process(AuditLogManager.java:368)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:544)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:383)
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java:233)
at com.wm.app.b2b.server.BaseService.invoke(BaseService.java:205)
at com.wm.lang.flow.FlowInvoke.invoke(FlowInvoke.java:259)
at com.wm.lang.flow.FlowState.invokeNode(FlowState.java:511)
at com.wm.lang.flow.FlowState.stepIncremental(FlowState.java:428)
at com.wm.lang.flow.FlowState.invoke(FlowState.java:331)
at wm.server.flowdebugger.stepFlow(flowdebugger.java:935)
at wm.server.flowdebugger.execute(flowdebugger.java:465)
at sun.reflect.GeneratedMethodAccessor321.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.wm.app.b2b.server.JavaService.baseInvoke(JavaService.java:453)
at com.wm.app.b2b.server.invoke.InvokeManager.process(InvokeManager.java:640)
at com.wm.app.b2b.server.util.tspace.ReservationProcessor.process(ReservationProcessor.java:39)
at com.wm.app.b2b.server.invoke.StatisticsProcessor.process(StatisticsProcessor.java:49)
at com.wm.app.b2b.server.invoke.ServiceCompletionImpl.process(ServiceCompletionImpl.java:243)
at com.wm.app.b2b.server.invoke.ValidateProcessor.process(ValidateProcessor.java:49)
at com.wm.app.b2b.server.invoke.PipelineProcessor.process(PipelineProcessor.java:171)
at com.wm.app.b2b.server.ACLManager.process(ACLManager.java:289)
at com.wm.app.b2b.server.invoke.DispatchProcessor.process(DispatchProcessor.java:34)
at com.wm.app.b2b.server.AuditLogManager.process(AuditLogManager.java:368)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:544)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:383)
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java:233)
at com.wm.app.b2b.server.comm.DefaultServerRequestHandler.handleMessage(DefaultServerRequestHandler.java:119)
at com.wm.app.b2b.server.HTTPMessageHandler.process(HTTPMessageHandler.java:164)
at com.wm.app.b2b.server.HTTPDispatch.handleRequest(HTTPDispatch.java:174)
at com.wm.app.b2b.server.Dispatch.run(Dispatch.java:382)
at com.wm.util.pool.PooledThread.run(PooledThread.java:119)
at java.lang.Thread.run(Thread.java:724)
Caused by: com.wm.adk.error.AdapterServiceException: [ADA.1.316] Cannot execute the SQL statement “select count (1) from [DV_INFRA].[dbo].[INP_WM_PDV_DV_INFRA_PROCESSED_PMES_FILES] where PZ_VON = ${PZ} and FileType= ${Typ}”. "
(S0001/102) Incorrect syntax near ‘{’."
Incorrect syntax near ‘{’.
at com.wm.adk.error.AdapterException.(AdapterException.java:299)
at com.wm.adk.WmAdapter.createAdapterException(WmAdapter.java:182)
at com.wm.adapter.wmjdbc.connection.ConnectionInfo.handleSQLException(ConnectionInfo.java:1219)
at com.wm.adapter.wmjdbc.connection.ConnectionInfo.handleSQLException(ConnectionInfo.java:1230)
at com.wm.adapter.wmjdbc.services.CustomSQL.execute(CustomSQL.java:297)
at com.wm.adk.cci.interaction.WmInteraction.execute(WmInteraction.java:76)
at com.wm.pkg.art.ns.AdapterServiceNode.invokeService(AdapterServiceNode.java:338)
… 42 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘{’.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322)
at com.wm.adapter.wmjdbc.services.CustomSQL.execute(CustomSQL.java:237)
… 44 more

Launch completed: 2015-08-14 07:26:48.11

Mahesh_K_Sreenivas · August 14, 2015, 8:23am

Is it custom or dynamic SQL?

Try populating the Input Field as shown in your screen shot and see if that works for you.

Holger_von_Thomsen · August 14, 2015, 12:26pm

Hi Maike,

please add the two fields in the input fields list.

This makes them available in the signature of the service and can be used for mapping when the services is invoked inside a flow service.

Regards,
Holger

Ahamed · August 14, 2015, 12:54pm

No need to define input parameters for Dynamic Sql.

Try like the below screenshot.

Hope it’ll work…

Maike_Hagebusch · August 14, 2015, 12:57pm

It is a custom sql

I will let you know when I tried all your suggestions.

Mahesh_K_Sreenivas · August 21, 2015, 8:17am

Are we good now?

Maike_Hagebusch · August 21, 2015, 8:21am

For now we are yes.

Mahesh_K_Sreenivas · August 21, 2015, 8:23am

What was done to make it work? You may share it here it might be helpful for other forum members

Maike_Hagebusch · August 21, 2015, 8:30am

I used the tips from Wassid and somehow got it to work.

Sometimes i am not sure how webMethods works, but this time it worked