Have some doubt regarding the weblog (Lookup's in XI made simpler)

[Former Member]

Hi All,

I have created the same scenario as mentioned in Siva's weblog (Lookup's in XI made simpler).

I having some doubts regarding the scenario, it will be great if you help me to resolve the same.

I am having a file-file scenario where I need to do lookup in database(MS-Access) through mapping.

The standard file-file scenario is in place and in addition I have created a receiver jdbc channel . I have also created the receiver agreement for the same in the cofiguration.

While creating the receiver agreement you have to specify the interface name which includes the message type…I have specified the normal format which we specify while configuring the jdbc receiver adapter.

In the message mapping I have created a advance user defined function as mentioned in your weblog which calls my receiver jdbc channel.

I have also specified the select query to be executed in the mapping program.

While testing I am getting the following error

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

<b>Can you please suggest me what all I need to do in addition to the file-file scenario for this lookup scenario to work.</b>

Thanks and Regards

Rahul

[Former Member · Accepted solution]

Hi Rahul

Use DSN less approach for accessing MS-Access while configuring reciever channel for Database access.

And go for Display queue to your User defined function.

try changing Context of input.

If issue still persists

We are here.

Cheers

Piyush

[Former Member · Accepted solution]

Hi,

I have already used the DSN less approach for accessing MS-Access.

On ckecking the display queue of my user defined function i can find that the output is getting suppressed.

The user defined function is same as mentioned in the weblog.

Thanks and Regards

Rahul

[Former Member · Accepted solution]

Hi,

Following is my user defined function

//write your code here

String Query = "";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

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

try{

channel = LookupService.getChannel("DB_service","JDBC_channel_receiver");

accessor = LookupService.getDataBaseAccessor(channel);

resultSet = accessor.execute(Query);

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

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

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

}

}

catch(Exception ex){

result.addValue(ex.getMessage());

}

finally{

try{

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

}

catch(Exception ex){

result.addValue(ex.getMessage());

}

}

Thanks and Regards

Rahul

[Former Member · Accepted solution]

Hi

Just Check your query,

Keywords Select, from , where should be in UPPERCASE.

Just check it.

Cheers

Piyush

[Former Member · Accepted solution]

Hi Piyush,

I have changed the keywords to uppercase but still i am getting the same error.

Thanks and Regards

Rahul

[Former Member · Accepted solution]

Hi,

As Piyush has given check case. Also check whether control enters the for loop whether you have records and instead of adding the result just add a dummy string value in and check.By this u can chek whether record is retreived or not.

Regards,

Sudharshan

[Former Member · Accepted solution]

Hi Sudharshan,

Thanks for your suggestion.

I tried doing what you have mentioned but i am getting the same earlier mentioned error while testing the mapping.

Please give some more suggestions.

Thanks and Regards

Rahul

[Former Member · Accepted solution]

Hi,

Instead of using

result.addValue("URole");

did you try adding just

result.addValue("any dummy string");

By the Way what are the datatypes u r using in Data Base.

I hope Uname, Password and Uroles are strings.

Also test in Query instead of giving Variables,give Literals(Values) itself.

Regards,

Sudharshan.

Message was edited by: Sudharshan Aravamudan

[Former Member · Accepted solution]

Hi Sudharshan,

I have done the same thing but still it is not working.

Yes, Uname Password and Urole are strings.

I also tried giving literals(values) instead of variable in the Query but still the same error.

The value is getting suppressed on checking in the queue.

Regards

Rahul

[Former Member · Accepted solution]

Hi,

I am just making a crazy suggestion.Try removing the finally block after catch. since it has got accessor.close(). is that affecting???

Regards,

Sudharshan

[Former Member · Accepted solution]

Hi Sudharshan,

It is still not working

How do i check if my function is able to call the receiver jdbc adapter and is able to reterieve the records.

Thanks and Regards

Rahul

[Former Member · Accepted solution]

Hi,

I think u dont get any exception like channel not found,Lookup Exception ....etc when u test the mapping. Then it is able to contact the database.upto that level we can be sure.Just try giving table name and fields in uppercase.hope u have tried with it.

Regards,

Sudharshan

[Former Member · Accepted solution]

Hi

i suggest we should go from basic debugging:

1. check the same query is working Ms access.in Ms access you can heck this.

2. your Communication channel should be reciever.

3. Check you have kept the *.mdb in shared location.

4. With the help of <u>result.addvalue("Entering in loop");</u>, check control flow.

5. Check by changing the context of input.

6. Write a function with queue.

I hope this all things are fine.

Still just have a check.

regards

Piyush

[Former Member · Accepted solution]

Hi Everyone,

Thanks a lot for your suggestions.

I found out that the query which i was executing was returning no records.

I modified the query and it started working.

Thanks again guys

Regards

Rahul

[Shabarish_Nair]

do check your mapping. The occurance might be 1 hence the target will expect an entry ... Did you try debugging the queue while testing ? Do you find any entries ?