1

Scenario : I have used DBLookup mediator to retrieve the full name by passing part of the name. For that I used like option in sql.

Full Name : John Smith

Value passed : John

SQL : SELECT * FROM table WHERE FullName like '%John%'

Used config :

    <dblookup>
   <connection>
      <pool>
         <driver>com.mysql.jdbc.Driver</driver>
         <url>jdbc:mysql://localhost:3306/world</url>
         <user>root</user>
         <password>root</password>
      </pool>
   </connection>
   <statement>
      <sql>SELECT TP_ID, TP_FULL_NAME, TP_USER_NAME, TP_USER_PASSWORD, TP_ACTIVE, TP_CHANGED_TIME, TP_TENANT_ID FROM tp_user WHERE TP_FULL_NAME like ('%?%');</sql>
      <parameter expression="get-property('name')" type="VARCHAR" />
   </statement>
</dblookup>

Error :

    [2018-11-08 13:04:14,943] [] ERROR - DBLookupMediator SQL Exception occurred while executing statement : SELECT TP_ID, TP_FULL_NAME, TP_USER_NAME, TP_USER_PASSWORD, TP_ACTIVE, TP_CHANGED_TIME, TP_TENANT_ID FROM tp_user WHERE TP_FULL_NAME like ('%?%'); against DataSource : jdbc:mysql://localhost:3306/world
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
JeewanaSL
  • 233
  • 1
  • 2
  • 10
  • Possible duplicate of [java.sql.SQLException Parameter index out of range (1 > number of parameters, which is 0)](https://stackoverflow.com/questions/10896151/java-sql-sqlexception-parameter-index-out-of-range-1-number-of-parameters-wh) – ophychius Nov 08 '18 at 09:59

1 Answers1

2

Change your sql statement as below;

SELECT TP_ID, TP_FULL_NAME, TP_USER_NAME, TP_USER_PASSWORD, TP_ACTIVE, TP_CHANGED_TIME, TP_TENANT_ID FROM tp_user WHERE TP_FULL_NAME like CONCAT('%',?,'%');

For further reference you can follow this LINK

WeAreGroot
  • 36
  • 3