Mon Nov 29, 2021 6:32 am
Login Register Lost Password? Contact Us


Query for ROXIE

Topics around the use of the JDBC driver for HPCC Systems

Mon Feb 11, 2013 12:08 pm Change Time Zone

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!!
Attachments
HPCCDriverTest1.txt
(4.18 KiB) Downloaded 775 times
target_clusters.PNG
(31.25 KiB) Downloaded 1490 times
prachi
 
Posts: 46
Joined: Mon Jul 23, 2012 11:50 am

Tue Feb 12, 2013 3:34 pm Change Time Zone

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.
rodrigo.pastrana@lexisnexis.com
 
Posts: 22
Joined: Fri Oct 07, 2011 2:24 pm


Return to Java Tools

Who is online

Users browsing this forum: No registered users and 1 guest