Query for ROXIE

[prachi]

Hi,
I have started with following steps:

Step1:Create Superfile
Step2:Create one logical file with record structure and add it to Superfile as subfile
Step3:Create INDEX on Superfile
Step4:ECL code to publish file on ROXIE.Code is as follow:

Code: Select all

IMPORT STD;

Export QueryToRunOnRoxie():=Function
STRING100 insertid_value := '' : STORED('insertid');

Layout_ClientWebCrawlData := RECORD
   VARSTRING Controller_ID;
   VARSTRING User_ID;
   VARSTRING URL_Link;
   VARSTRING URL_Content;
   REAL URL_Sentiment;
   VARSTRING URL_Date;
   INTEGER Unique_Search_ID;
   STRING100  InsertID;
   VARSTRING StatusID;
   VARSTRING Search_Pattern;
   VARSTRING Word_Ignored;
   VARSTRING Search_Date;
   VARSTRING Detected_Language;
   END;
   
VARSTRING Superfilename := '~roxietrail::superfile::clientwebcrawldata';
VARSTRING index_filename := '~roxietrail::superfile::index';

// create dataset of superfile
SuperFile_Dataset := DATASET(Superfilename,{Layout_ClientWebCrawlData,
UNSIGNED8 fileposition {VIRTUAL(fileposition)}}, CSV);

// create index on superfile
IDX_SuperFile := INDEX(SuperFile_Dataset,{InsertID,fileposition},index_filename);

//fetch records
fetched_records := FETCH(SuperFile_Dataset, IDX_SuperFile(insertid=insertid_value), RIGHT.fileposition);

//store output
getOutput := OUTPUT(fetched_records);

RETURN getOutput;
END;



Above code was published successfully!


Now when i am trying to call <published_file_name>(in my case it is querytorunonroxie) through java code error is generated as:

Code1:

Code: Select all

info.put("ServerAddress", "10.101.2.170");
info.put("LazyLoad", "true");
info.put("TargetCluster", "myroxie");
info.put("QuerySet", "myroxie");
info.put("WsECLWatchPort", "8010");
info.put("WsECLDirectPort", "8010");
info.put("EclResultLimit", "10");
info.put("PageSize", "");

infourl = "jdbc:hpcc;ServerAddress=10.101.2.170;TargetCluster=myroxie;EclResultLimit=8";


success &= executeFreeHandSQL(info,"call QueryToRunOnRoxie( )",params);

Error:(for Code1)

Code: Select all

2013/02/11 04:55:55.083 - HPCCConnection jdbc url:
2013/02/11 04:55:55.083 - HPCCDriver::connect10.101.2.170
2013/02/11 04:55:55.083 - HPCCDatabaseMetaData ServerAddress: 10.101.2.170 TargetCluster: myroxie eclwatch: http://10.101.2.170:8010
2013/02/11 04:55:55.083 - HPCCDatabaseMetaData Fetching HPCC INFO: http://10.101.2.170:8010/WsSMC/Activity?rawxml_
2013/02/11 04:55:55.380 - HPCC file and published query info not pre-fetched (LazyLoad enabled)
2013/02/11 04:55:55.380 - Invalid cluster name found: myroxie. using: hthor
2013/02/11 04:55:55.380 - Invalid query set name found: myroxie. using: hthor
2013/02/11 04:55:55.380 - HPCCDatabaseMetaData initialized
2013/02/11 04:55:55.380 - HPCCConnection initialized - server: 10.101.2.170
2013/02/11 04:55:55.380 - HPCCConnection: prepareStatement( call QueryToRunOnRoxie( ) )
2013/02/11 04:55:55.380 - HPCCStatementConstructor(conn)
2013/02/11 04:55:55.380 - HPCCPreparedStatement Constructor: Sqlquery: call QueryToRunOnRoxie( )
2013/02/11 04:55:55.380 - HPCCStatementAttempting to process sql query: call QueryToRunOnRoxie( )
2013/02/11 04:55:55.380 - INCOMING SQL: call QueryToRunOnRoxie( )
2013/02/11 04:55:55.443 - Invalid Stored Procedure found, verify name and QuerySet: QueryToRunOnRoxie
Errr: Invalid Stored Procedure found, verify name and QuerySet: QueryToRunOnRoxie


Code2:

Code: Select all

info.put("ServerAddress", "10.101.2.170");
info.put("LazyLoad", "true");
info.put("TargetCluster", "roxie");
info.put("QuerySet", "roxie");
info.put("WsECLWatchPort", "8010");
info.put("WsECLDirectPort", "8010");
info.put("EclResultLimit", "10");
info.put("PageSize", "");

infourl = "jdbc:hpcc;ServerAddress=10.101.2.170;TargetCluster=roxie;EclResultLimit=8";


success &= executeFreeHandSQL(info,"call QueryToRunOnRoxie( )",params);

Error:(for Code2)

Code: Select all

2013/02/11 05:14:14.154 - HPCCConnection jdbc url:
2013/02/11 05:14:14.169 - HPCCDriver::connect10.101.2.170
2013/02/11 05:14:14.169 - HPCCDatabaseMetaData ServerAddress: 10.101.2.170 TargetCluster: roxie eclwatch: http://10.101.2.170:8010
2013/02/11 05:14:14.169 - HPCCDatabaseMetaData Fetching HPCC INFO: http://10.101.2.170:8010/WsSMC/Activity?rawxml_
2013/02/11 05:14:14.497 - HPCC file and published query info not pre-fetched (LazyLoad enabled)
2013/02/11 05:14:14.497 - HPCCDatabaseMetaData initialized
2013/02/11 05:14:14.497 - HPCCConnection initialized - server: 10.101.2.170
2013/02/11 05:14:14.497 - HPCCConnection: prepareStatement( call QueryToRunOnRoxie( ) )
2013/02/11 05:14:14.497 - HPCCStatementConstructor(conn)
2013/02/11 05:14:14.497 - HPCCPreparedStatement Constructor: Sqlquery: call QueryToRunOnRoxie( )
2013/02/11 05:14:14.497 - HPCCStatementAttempting to process sql query: call QueryToRunOnRoxie( )
2013/02/11 05:14:14.497 - INCOMING SQL: call QueryToRunOnRoxie( )
2013/02/11 05:14:14.560 - Invalid Stored Procedure found, verify name and QuerySet: QueryToRunOnRoxie
Errr: Invalid Stored Procedure found, verify name and QuerySet: QueryToRunOnRoxie

I have attached screenshot of Target Clusters and Java code.

Question:
1. Where is the problem in java code?
2. What name should be provided: myroxie/roxie?? Asked because when i use myroxie it says that

Invalid query set name found: myroxie. using: hthor

Thanks and Regards!!

[rodrigo.pastrana@lexisnexis.com]

Hi Prachi,
The TargetCluster configuration entry is the name of the HPCC cluster on which to execute your "Select" queries.
One quick way to find the available TargetClusters is here:
http:///<yourHPCCip>:<yourwseclwatchport>/WsSMC/Activity.
The names follow the cluster types and are separated by a dash.

The QuerySet entry is the name of the queryset which contains the published query (stored procedure) you intend to execute.

You can find the list of querysets here:
http://<yourHPCCip>:<yourwseclwatchport>/WsWorkunits/WUQuerysets

You can also fully-qualify the published query if you'd like to override the "QuerySet" configuration value: call <yourquerysetname>::<yourpublishedqueryname(...);

Let us know if there are any other questions. Thanks.