Error while testing in message mapping for JDBC lookup

[Former Member]

The error is as follows.

Exception during processing the payload.Problem when calling an adapter by using communication channel JLU_chan (Party: , Service: JLU_BUSS, Object ID: 0519f96e59c534829ebdcdea837dacb7) XI AF API call failed. Module exception: (No information available). Cause Exception: 'Error processing request in sax parser: Error when executing statement for table/stored proc. 'table' (structure 'statement'): java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Missing ), ], or Item in query expression 'username = [Ljava.lang.String;@d7f610 and password = [Ljava.lang.String;@5b96eb'.'.

com.sap.aii.mapping.lookup.LookupException: Problem when calling an adapter by using communication channel JLU_chan (Party: , Service: JLU_BUSS, Object ID: 0519f96e59c534829ebdcdea837dacb7) XI AF API call failed. Module exception: (No information available). Cause Exception: 'Error processing request in sax parser: Error when executing statement for table/stored proc. 'table' (structure 'statement'): java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Missing ), ], or Item in query expression 'username = [Ljava.lang.String;@d7f610 and password = [Ljava.lang.String;@5b96eb'.'.

at com.sap.aii.ibrun.server.lookup.AdapterProxyLocal.execute(AdapterProxyLocal.java:131)

at com.sap.aii.ibrun.server.lookup.SystemAccessorInternal.execute(SystemAccessorInternal.java:68)

at com.sap.aii.ibrun.server.lookup.SystemAccessorHmiServer.process(SystemAccessorHmiServer.java:149)

at com.sap.aii.ibrun.server.lookup.SystemAccessorHmiServer.process(SystemAccessorHmiServer.java:77)

at com.sap.aii.utilxi.hmis.server.HmisServiceImpl.invokeMethod(HmisServiceImpl.java:169)

at com.sap.aii.utilxi.hmis.server.HmisServer.process(HmisServer.java:178)

at com.sap.aii.utilxi.hmis.web.HmisServletImpl.processRequestByHmiServer(HmisServletImpl.java:296)

at com.sap.aii.utilxi.hmis.web.HmisServletImpl.processRequestByHmiServer(HmisServletImpl.java:211)

at com.sap.aii.utilxi.hmis.web.workers.HmisInternalClient.doWork(HmisInternalClient.java:70)

at com.sap.aii.utilxi.hmis.web.HmisServletImpl.doWork(HmisServletImpl.java:496)

at com.sap.aii.utilxi.hmis.web.HmisServletImpl.doPost(HmisServletImpl.java:634)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.runServlet(HttpHandlerImpl.java:401)

at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.handleRequest(HttpHandlerImpl.java:266)

at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:387)

at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:365)

at com.sap.engine.services.httpserver.server.RequestAnalizer.invokeWebContainer(RequestAnalizer.java:944)

at com.sap.engine.services.httpserver.server.RequestAnalizer.handle(RequestAnalizer.java:266)

at com.sap.engine.services.httpserver.server.Client.handle(Client.java:95)

at com.sap.engine.services.httpserver.server.Processor.request(Processor.java:160)

at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:33)

at com.sap.engine.core.cluster.impl6.session.MessageRunner.run(MessageRunner.java:41)

at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)

at java.security.AccessController.doPrivileged(Native Method)

at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:100)

at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:170)

Root Cause:

com.sap.aii.af.service.api.AFException: XI AF API call failed. Module exception: (No information available). Cause Exception: 'Error processing request in sax parser: Error when executing statement for table/stored proc. 'table' (structure 'statement'): java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Missing ), ], or Item in query expression 'username = [Ljava.lang.String;@d7f610 and password = [Ljava.lang.String;@5b96eb'.'.

at com.sap.aii.af.service.api.AdapterAccess.sendMsg(AdapterAccess.java:214)

at com.sap.aii.af.service.api.AdapterAccess.execute(AdapterAccess.java:283)

at com.sap.aii.ibrun.server.lookup.AdapterProxyLocal.execute(AdapterProxyLocal.java:123)

at com.sap.aii.ibrun.server.lookup.SystemAccessorInternal.execute(SystemAccessorInternal.java:68)

at com.sap.aii.ibrun.server.lookup.SystemAccessorHmiServer.process(SystemAccessorHmiServer.java:149)

at com.sap.aii.ibrun.server.lookup.SystemAccessorHmiServer.process(SystemAccessorHmiServer.java:77)

at com.sap.aii.utilxi.hmis.server.HmisServiceImpl.invokeMethod(HmisServiceImpl.java:169)

at com.sap.aii.utilxi.hmis.server.HmisServer.process(HmisServer.java:178)

at com.sap.aii.utilxi.hmis.web.HmisServletImpl.processRequestByHmiServer(HmisServletImpl.java:296)

at com.sap.aii.utilxi.hmis.web.HmisServletImpl.processRequestByHmiServer(HmisServletImpl.java:211)

at com.sap.aii.utilxi.hmis.web.workers.HmisInternalClient.doWork(HmisInternalClient.java:70)

at com.sap.aii.utilxi.hmis.web.HmisServletImpl.doWork(HmisServletImpl.java:496)

at com.sap.aii.utilxi.hmis.web.HmisServletImpl.doPost(HmisServletImpl.java:634)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.runServlet(HttpHandlerImpl.java:401)

at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.handleRequest(HttpHandlerImpl.java:266)

at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:387)

at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:365)

at com.sap.engine.services.httpserver.server.RequestAnalizer.invokeWebContainer(RequestAnalizer.java:944)

at com.sap.engine.services.httpserver.server.RequestAnalizer.handle(RequestAnalizer.java:266)

at com.sap.engine.services.httpserver.server.Client.handle(Client.java:95)

at com.sap.engine.services.httpserver.server.Processor.request(Processor.java:160)

at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:33)

at com.sap.engine.core.cluster.impl6.session.MessageRunner.run(MessageRunner.java:41)

at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)

at java.security.AccessController.doPrivileged(Native Method)

at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:100)

at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:170)

[Former Member · Accepted solution]

I changed the query as u said but now a new error has come as follows.

12:25:06 Start of test

Cannot produce target element /ns0:Output_MT_JLU/role. Check xml instance is valid for source xsd and target-field mapping fulfills requirements of target xsd Cannot produce target element /ns0:Output_MT_JLU/role. Check xml instance is valid for source xsd and target-field mapping fulfills requirements of target xsd

12:25:09 End of test

[Former Member · Accepted solution]

Manikantan

This error occurs in your message mapping when your mapping doesnt produce a target element that has been mapped. Check your message mapping. If there are more than one occurrence in the target , then a value has to be present in runtime for each of the occurrence.

<i>Hey, did you know that you get a point if you reward points?;)</i>

regards

krishna

[Former Member]

I gave the same code.got an error thats why changed it

[Former Member]

Ok.

What about the single quotes that you have missed to wrap around the username and password?

can you check that?? compare with the correct query statement

regards

krishna

[Former Member]

I have followed those steps.I am doing an example of jdbc lookup given in blogs.i.e for the given username and password it should select the role from the database.

I think there is some problem in the query.Can you check my code?

[Former Member]

Hi manikantan

The blogs query stmnt is

// Build the Query String

Query = "Select URole from LoginMaster_1 where UName = '" + UName[0] + "' and UPassword = '" + Pwd[0] +"'"

Do you see the difference in your code?

Why you are not having username[0]???

<i>Note: You are passing a string array </i>

regards

krishna

[bhavesh_kantilal]

><i>

Why you are not having username[0]???</i>

This is needed only in case of an Advanced User Defined Function. In this case, the UDF I think is a simple UDF.

Regards

Bhavesh

[Former Member]

Bhavesh,

Why does the blog mentiones username[0] ?

Even i was wondering because none of the source node had multiple occurence specified

Regards

krishna

[bhavesh_kantilal]

Krishna,

In Siva's blog, the UDF is an Advaced UDF. Reason I guess is that the Select Query returns multiple rowsand each of these has to be sent as output. As the UDF needs to return multiple output, you need to use an advaced udf.

In thise case I guess the need is to just return a single value and hence a Simple UDF.

Also, Like pointed correctly, the User id and password would need the single quote as these have to be String (varchar)in the Database.

regards

Bhavesh

[Former Member]

Bhavesh,

Thanks for pitching in your view.

As per the following help doc, cant we come to a conclusion that if we want to pass a complete context/ queue instead of just individual field values, one needs to go for advacned UDF??

http://help.sap.com/saphelp_nw04/helpdata/en/40/7b8e40496f6f1de10000000a1550b0/frameset.htm

Regards

krishna

[bhavesh_kantilal]

Yes you do need a Advanced UDF if you need the either Context or Queue!

Am not sure what the issue here is

Regards

Bhavesh

[Former Member]

well, there is no issue with using username[0] but with missing the quotes , which was identified and fixed

regards

krishna

[Former Member]

I added my file in Data sources(ODBC) in administrative tools.Is it fine?

[Former Member]

Please check all the steps as given in the guide

regards

krishna

[Former Member]

Manikantan

Am sorry,

there is no need to install any driver explicitly

just check the steps as mentioned in the following blog

/people/sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn

regards

krishna

[Former Member]

How to install the ODBC driver?can you tell me the procedure?

[Former Member]

Hi

check the following doc

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/70ffd890-0201-0010-708f-d5dad2df... or

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/bf4b1055-0d01-0010-32a3-b3848d77...

regards

krishna

[Former Member]

I'll give you the query.Can you please check my code.Code is as follows.

//write your code here

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

//Build the Query String

Query = "Select role from login where username = " + username + " and password = " + password + "" ;

try{

//Determine a channel as created in the configuration

channel = LookupService.getChannel("JLU_BUSS","JLU_chan");

// Get a system accessor for a channel.As the call is being made to the database an database accessor is obtained.

accessor = LookupService.getDataBaseAccessor(channel);

// Execute the Query and get the values in the resultSet.

resultSet = accessor.execute(Query);

for(Iterator rows = resultSet.getRows();rows.hasNext();)

{

Map rowMap = (Map)rows.next();

result.addValue((String)rowMap.get("role"));

}

}

catch(Exception ex){

result.addValue(ex.getMessage());

}

finally{

try{

if (accessor!=null) accessor.close();

}

catch(Exception e){

result.addValue(e.getMessage());

}

}

[Former Member]

I think username and password are defines as arrays of strings (String[]).

Try changing

Query = "Select role from login where username = " + username + " and password = " + password + "" ;

to

Query = "Select role from login where username = " + username<b>[0]</b> + " and password = " + password<b>[0]</b> + "" ;

[bhavesh_kantilal]

This is needed only if you are writing an Advaced UDF and expect multiple rows as the output from the select query.

In this case, the UDF I think is a simple UDF.

Regards

Bhavesh

[Former Member]

The creation of the query fails however: it creates:

.... username = [Ljava.lang.String;@d7f610 and password = [Ljava.lang.String;@5b96eb....

Normally you would see something like

... username = myUser and password = myPassword

This kind of problem is most common when the variable you append is an array instead of a single string.

BTW, I think your query should also include quotes around the username and password.

Please try entering:

Query = "Select role from login where username = \"" + username[0] + "\" and password = \"" + password[0] + "\"" ;

If this still fails,please let me know the exact error it produces.

Kind regards,

Koen

[Former Member]

hi Koen,

The problem was identified earlier and a new problem was faced by the raiser of the issue.:)

Regards

krishna

[Former Member]

Hi Manikantan

Did you install the ODBC driver??

Regards

krishna

[Former Member]

Hello subhash,

Check the table name u r accessing via jdbc query . Check the message construct for query .

Regards,

Keith