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

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

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!

https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files3/611efb40ff53250f7091006a6d512d80abc7eb50_2_1035x480.png 1.5x, https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/611efb40ff53250f7091006a6d512d80abc7eb50.png 2x" data-dominant-color="F6F7F7">

select_count.PNG1263×587 18.4 KB

Hi Maike,

You just remove the single quotes on your dynamic content.

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

Hope it’ll work…

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

Is it custom or dynamic SQL?

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

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

No need to define input parameters for Dynamic Sql.

Try like the below screenshot.

Hope it’ll work…

https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/1063dac5da6ea7a5014421cf18030bfbf3f97987.jpeg 1.5x, https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/1063dac5da6ea7a5014421cf18030bfbf3f97987.jpeg 2x" data-dominant-color="F8F8F9">

1.JPG905×401 46.1 KB

It is a custom sql

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

Are we good now?

For now we are yes.

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

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