cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X

How to search "content" from "textbox" in database and show related information in grid

mlohokare
5-Regular Member

How to search "content" from "textbox" in database and show related information in grid

Hi,

I want to search "content" from "textbox" in database and show "content - related" information/columns from specified table.

I have followed below steps,

  • Create Mashup
  • Call service
  • Place textbox
  • Place button
    • onclick - call service which will take input from user and return table
    • Returned table will show in grid
  • Place grid

But, I am not able to assign "textbox specific content" to query.

  1. Function is created in databse (Database used Oracle 11g)
  2. it is working fine in database

Please guide.

Regards,

Mahesh

1 ACCEPTED SOLUTION

Accepted Solutions
jkaczynski
4-Participant
(To:mlohokare)

Hello,

I'm not quite sure if I understood you correctly - you're missing the mechanism between textbox and database? If yes, please find below my answer. If any step is already done by you, skip it.

1. I assume that you don't have the JDBC connector on your Thingworx yet. To create one and upload the Oracle Driver, you can use convenience extension that can be found here: https://marketplace.thingworx.com/Items/jdbc-connector-extension

All you need to do is to import the extension, navigate to the extension mashup, pick the Oracle DB Driver jar file: Oracle Database 11g Release 2 JDBC Driver Downloads

Then you can download the prepared JDBC extension and import it to the Thingworx Platform.

2. Then you need to create a Thing on the basis of Database Template. In the configuration panel you should put all the necessary settings (credentials, driver information, connection url).

3. The last step is to create a new service that would take as an input parameter a string value and perform an SQL Query. To do so, when you click on Add My Service button, you should pick the appropriate type from the drop down list near the New Service label.

Then you can put the SQL statement in the script box, using [[ parameter_name ]] as a parameter substitution (less error-prone) or << parameter_name >> as a string substitution (beware the SQL injection!).

4. After that, you need to add a newly created service to your mashup, bind the textbox Text parameter with your input parameter and the result of your service with grid.

Please note, that if you do not specify a data shape on the output InfoTable, you should mark the ShowAllColumns in the grid widget to true.

Hope that helps and I didn't misunderstood you. If you have any additional question, please let me know.

Regards,

J.

View solution in original post

2 REPLIES 2
jkaczynski
4-Participant
(To:mlohokare)

Hello,

I'm not quite sure if I understood you correctly - you're missing the mechanism between textbox and database? If yes, please find below my answer. If any step is already done by you, skip it.

1. I assume that you don't have the JDBC connector on your Thingworx yet. To create one and upload the Oracle Driver, you can use convenience extension that can be found here: https://marketplace.thingworx.com/Items/jdbc-connector-extension

All you need to do is to import the extension, navigate to the extension mashup, pick the Oracle DB Driver jar file: Oracle Database 11g Release 2 JDBC Driver Downloads

Then you can download the prepared JDBC extension and import it to the Thingworx Platform.

2. Then you need to create a Thing on the basis of Database Template. In the configuration panel you should put all the necessary settings (credentials, driver information, connection url).

3. The last step is to create a new service that would take as an input parameter a string value and perform an SQL Query. To do so, when you click on Add My Service button, you should pick the appropriate type from the drop down list near the New Service label.

Then you can put the SQL statement in the script box, using [[ parameter_name ]] as a parameter substitution (less error-prone) or << parameter_name >> as a string substitution (beware the SQL injection!).

4. After that, you need to add a newly created service to your mashup, bind the textbox Text parameter with your input parameter and the result of your service with grid.

Please note, that if you do not specify a data shape on the output InfoTable, you should mark the ShowAllColumns in the grid widget to true.

Hope that helps and I didn't misunderstood you. If you have any additional question, please let me know.

Regards,

J.

mlohokare
5-Regular Member
(To:jkaczynski)

Hi J,

Thanks for your detail reply.

I did what you have suggested and found I was doing wrong with "inserting parameter in query".

Thanks a lot, now it is working as expected.

Regards,

Mahesh

Top Tags