How to write dynamic sql query in a data source

[User150433]

I have a scenario where I need to query a database and get the details of users specified in the where condition.The condition looks like this:
select * from myTable where employeeId IN(501763663,501732323,500117338,....100 such entries,501689434)
Now the problem that I am facing is the mid server is taking too long to respond and finally I get an error saying 'Did not get any response from the mid server after waiting for 310 seconds'. This may be due to in efficiency of IN query.
If I run the same query in the PL SQL,I get the response in 10-15 seconds.
So I thought of running the data source multiple times for each employee id by making the sql query dynamic.The query will look like this.
select * from myTable where employeeId = 501763663;
Immediately after running this data source I will update the data source sql statement and run the data source again.
select * from myTable where employeeId = 501732323;

This approach does not seem to work properly.

Can anyone please provide some help on this?

[ideamax]

I am also facing similar issue. I am using Oracle JDBC Probe which is executed by Scheduled Job every hour. Although my query is not much complex, it just retunrs the records updated in last hour. However, I get the results from MID server, in import log it keeps saying Waiting for MID Server to respond and after 300 seconds it process the ECC Queue with the sensor written on it.

Below are the logs from Import Log:

2012-02-18 14:50:31InformationDid not get a response from the MID server after waiting for 310 secondsLoader (CDI User Records 01 Hour)
2012-02-18 14:50:31WarningDid not get a response from the MID server after waiting for 310 secondsLoader (CDI User Records 01 Hour)
2012-02-18 14:50:31InformationUsing import set: IS128221, table u_cdi_user_recordsImportSetTransformer (CDI User Transform
2012-02-18 14:50:31InformationTransforming source table: u_cdi_user_recordsScheduleImportJob
2012-02-18 14:50:31Informationtotal: 1, inserts 0, updates 0, ignored 1, skipped 0, errors 0 (0:00:00.131)ImportSetTransformer (CDI User Transform
2012-02-18 14:50:21InformationWaiting for MID server to respond (300 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:50:11InformationWaiting for MID server to respond (290 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:50:01InformationWaiting for MID server to respond (280 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:49:51InformationWaiting for MID server to respond (270 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:49:40InformationWaiting for MID server to respond (260 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:49:30InformationWaiting for MID server to respond (250 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:49:20InformationWaiting for MID server to respond (240 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:49:10InformationWaiting for MID server to respond (230 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:49:00InformationWaiting for MID server to respond (220 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:48:50InformationWaiting for MID server to respond (210 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:48:40InformationWaiting for MID server to respond (200 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:48:30InformationWaiting for MID server to respond (190 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:48:20InformationWaiting for MID server to respond (180 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:48:07InformationWaiting for MID server to respond (170 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:47:57InformationWaiting for MID server to respond (160 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:47:47InformationWaiting for MID server to respond (150 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:47:37InformationWaiting for MID server to respond (140 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:47:27InformationWaiting for MID server to respond (130 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:47:17InformationWaiting for MID server to respond (120 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:47:07InformationWaiting for MID server to respond (110 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:46:57InformationWaiting for MID server to respond (100 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:46:47InformationWaiting for MID server to respond (90 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:46:37InformationWaiting for MID server to respond (80 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:46:27InformationWaiting for MID server to respond (70 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:46:17InformationWaiting for MID server to respond (60 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:46:07InformationWaiting for MID server to respond (50 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:45:57InformationWaiting for MID server to respond (40 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:45:47InformationWaiting for MID server to respond (30 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:45:37InformationWaiting for MID server to respond (20 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:45:27InformationWaiting for MID server to respond (10 of 300 secs)Loader (CDI User Records 01 Hour)
2012-02-18 14:45:17InformationGot response from MID serverLoader (CDI User Records 01 Hour)
2012-02-18 14:45:17InformationGot response from MID serverLoader (CDI User Records 01 Hour)
2012-02-18 14:45:17InformationProcessed: 0Loader (CDI User Records 01 Hour)
2012-02-18 14:45:17InformationGot response from MID server

[Ravish Shetty]

i have the same query

[Todd O]

I'm hoping someone can respond to this post as I also have a need to feed parameters to my data source that would allow for dynamic SQL statements in my WHERE clause. Thanks.

[vashes]

Hello,

The only way I know - update SQL field in 'sys_data_source' table, then execute your data source.

var gr = new GlideRecord('sys_data_source');
gr.get('YOUR_DATA_SOURCE_SYS_ID');


gr.sql_statement = 'Updated SQL statement';
gr.update();

Beware the data source is not intended for on fly data changes, try to not update the data source while it in progress. 

I hope it can help.