Pass dynamic values to WSO2 DBLook 'like' query

up vote
0
down vote

favorite

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)

asked 1 hour ago

JeewanaSL

214

Possible duplicate of java.sql.SQLException Parameter index out of range (1 > number of parameters, which is 0)
– ophychius
32 mins ago

add a comment |

up vote
0
down vote

favorite

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)

asked 1 hour ago

JeewanaSL

214

Possible duplicate of java.sql.SQLException Parameter index out of range (1 > number of parameters, which is 0)
– ophychius
32 mins ago

add a comment |

up vote
0
down vote

favorite

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)

asked 1 hour ago

JeewanaSL

214

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)

mysql wso2 ei

asked 1 hour ago

JeewanaSL

214

asked 1 hour ago

JeewanaSL

214

asked 1 hour ago

JeewanaSL

214

asked 1 hour ago

JeewanaSL

214

asked 1 hour ago

JeewanaSL

214

Possible duplicate of java.sql.SQLException Parameter index out of range (1 > number of parameters, which is 0)
– ophychius
32 mins ago

add a comment |

Possible duplicate of java.sql.SQLException Parameter index out of range (1 > number of parameters, which is 0)
– ophychius
32 mins ago

Possible duplicate of java.sql.SQLException Parameter index out of range (1 > number of parameters, which is 0)
– ophychius
32 mins ago

add a comment |

1 Answer
1

active

oldest

votes

up vote
1
down vote

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

answered 1 hour ago

WeAreGroot

111

New contributor

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53204458%2fpass-dynamic-values-to-wso2-dblook-like-query%23new-answer', 'question_page');
}
);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
1
down vote

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

answered 1 hour ago

WeAreGroot

111

New contributor

add a comment |

up vote
1
down vote

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

answered 1 hour ago

WeAreGroot

111

New contributor

add a comment |

up vote
1
down vote

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

answered 1 hour ago

WeAreGroot

111

New contributor

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

answered 1 hour ago

WeAreGroot

111

New contributor

answered 1 hour ago

WeAreGroot

111

New contributor

answered 1 hour ago

WeAreGroot

111

answered 1 hour ago

WeAreGroot

111

New contributor

WeAreGroot is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Name

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Agfdhyk