Java Agent - How to pass parameters into an SQL Update statement?

HCL Domino Domino Forum
1

How do I pass values into the select statement correctly? Using this example. The values come from a document!

sql.append(“Update ifsapp.person_info_comm_method_tab b”);

	sql.append(" Set b.value =  ' " + entry.getDocument().getItemValueString("IFSEmail" )+ " ' ");

	sql.append(" WHERE method_id = 'E_MAIL' "); 

      sql.append(" AND b.person_id ="); 

	sql.append(" (select b.person_id from ifsapp.pers_comms_work_tab a, ifsapp.person_info_comm_method_tab b");

	sql.append(" where method_id = 'E_MAIL'");

	sql.append(" and a.person_id = b.person_id");

	sql.append(" and a.comm_line_id = b.comm_id");

	sql.append(" and a.company_id = '01'");

	sql.append(" and b.method_default = 'TRUE'");

	sql.append(" and b.valid_to > sysdate");

	sql.append(" AND b.person_id ='" + entry.getDocument().getItemValueString("IFSPersonID") + " ' ");

     sql.append(" AND ROWNUM =1)");

=============================

FYI…If I supply the details the statement works:

sql.append(“Update ifsapp.person_info_comm_method_tab b”);

	sql.append(" Set b.value =  'N.J@myaddress.co.uk' ");

	sql.append(" WHERE method_id = 'E_MAIL' "); 

      sql.append(" AND b.person_id ="); 

	sql.append(" (select b.person_id from ifsapp.pers_comms_work_tab a, ifsapp.person_info_comm_method_tab b");

	sql.append(" where method_id = 'E_MAIL'");

	sql.append(" and a.person_id = b.person_id");

	sql.append(" and a.comm_line_id = b.comm_id");

	sql.append(" and a.company_id = '01'");

	sql.append(" and b.method_default = 'TRUE'");

	sql.append(" and b.valid_to > sysdate");

	sql.append(" AND b.person_id ='NJOUNSTONE'" );

     sql.append(" AND ROWNUM =1)");
2

Subject: Java Agent - How to pass parameters into an SQL Update statement?

I’m not sure if you added the spaces just for readability in this post but I think your problem might be an additional space in both lines where you are trying to insert data from the document. It appears to me that the line:

sql.append(" Set b.value = ’ " + entry.getDocument().getItemValueString(“IFSEmail” )+ " ’ ");

would give you the following:

’ N.J@myaddress.co.uk ’ (padded with spaces) instead of what you want which is ‘N.J@myaddress.co.uk’.

This, of course, would throw off your SQL statement. You have a similar problem with the b.person_id line, except in that case, you only have a trailing space added.

Try removing those spaces by the single quotes in those two lines and see if that helps.

3

Subject: RE: Java Agent - How to pass parameters into an SQL Update statement?

"Try removing those spaces by the single quotes in those two lines and see if that helps. "

That worked.

Thank you

4

Subject: RE: Java Agent - How to pass parameters into an SQL Update statement?

Although I am not a SQL programmer, the fact that you are simply substituting IFSEmail or IFSPersonID values directly into the SQL template triggers warning alarms in my head. Unless SQL language escapement has already been handled when these values are stored in the fields, the resultant SQL string may not be what you desire.

Also, it may be useful to place your SQL template in a property and use java.text.Format.