Tue Sep 29, 2020 7:20 pm
Login Register Lost Password? Contact Us


Fetch data through SQL.

Comments and questions related to the Enterprise Control Language

Fri Mar 30, 2012 11:32 am Change Time Zone

Hi,
I am trying to fetch data from a table stored in SQL Server 2008 databse and to save it as CSV in HPCC??
I'm trying through the following code:

Code: Select all
IMPORT Std;
rfsserver := '10.173.207.1:1433';
rec := RECORD,MAXLENGTH(8192)
STRING mydata;
END;
OUTPUT(DATASET(STD.File.EncodeRfsQuery( rfsserver,
'SELECT * FROM [TextAnalysis_HPCC].[dbo].[Test_text]'),rec,CSV(MAXLENGTH(8192))));


Error: System error: 1: socket not opened
Target: C!10.173.207.1, Raised in: /var/jenkins/workspace/Candidate-3.4.2/HPCC-Platform/system/jlib/jsocket.cpp, line 1681
Arti
 
Posts: 4
Joined: Fri Mar 30, 2012 8:16 am

Tue Apr 03, 2012 12:46 pm Change Time Zone

The development team is currently investigating this issue.
bforeman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1002
Joined: Wed Jun 29, 2011 7:13 pm

Mon Apr 09, 2012 1:54 pm Change Time Zone

Hi,
Is there any update on this issue.
Arti
 
Posts: 4
Joined: Fri Mar 30, 2012 8:16 am

Mon Apr 09, 2012 6:02 pm Change Time Zone

Arti,

the data access from/to RDBMS using rfs requires an rfsserver running on the database server. As far as I know, the only rfsserver that exists for now is the one for MySQL (not Ms SQL).

This is not a recommended way of accessing data residing on an RDBMS system, as bulk dump/load operations tend to perform significantly better; although there are occassions where access to a small number of records without the intermediate step of the dump/load could justify using it.

Please let me know.

Thanks,

Flavio
flavio
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 73
Joined: Wed Apr 27, 2011 8:59 pm

Tue Apr 10, 2012 5:51 am Change Time Zone

Hi,

we are trying to understand the connection management between any RDBMS and HPCC. If its not possible with MSSQL we can try with MySql.

Here we are not looking for any bulk load of Data for now, just trying with a database which has only 1 table with less than 10 records, so can you please guide us regarding this approach.
Thanks
Arti
 
Posts: 4
Joined: Fri Mar 30, 2012 8:16 am

Tue Apr 10, 2012 12:10 pm Change Time Zone

Arti,

Although I'm not sure if the rfsserver for MySQL gets built by default in our current version, we could certainly add it to our build scripts in future versions, if this is useful to people.

In any case, the sources are at: https://github.com/hpcc-systems/HPCC-Platform/tree/master/dali/rfs/rfsmysql and there is even a makefile, so building it would be as simple as running "make" in the directory that you download it to.

Please let me know if you need any help there.

Thanks,

Flavio
flavio
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 73
Joined: Wed Apr 27, 2011 8:59 pm

Wed Dec 11, 2019 11:27 am Change Time Zone

Hi Team,
Can you please suggest how can i pass query runtime
Trying to pass query runtime to extract as this mainly for count respectives.

IMPORT mysql;
integer ExtractIt(String Query) := EMBED(mysql : user('rchapman'),database('test'),server('127.0.0.1'), port('3306'))
?
ENDEMBED;

Query := 'select count(1) from transaction_log' ;
ExtractIt(Query);




Even if there is query ,How to negotiate quote with escape sequence .

SELECT * FROM transaction_log WHERE account_number NOT IN ('10000001','10000024') AND batch_job_id IS NOT NULL AND SUBSTR(date_added, 1, 10) = SUBDATE(CURDATE(),1) ORDER BY date_added DESC;
harshdesai
 
Posts: 24
Joined: Fri Feb 23, 2018 8:30 am

Tue Dec 17, 2019 9:38 am Change Time Zone

Hi Team ,
Is there a limit to fetch data at one go or we can fetch all the data(hunderd thousands of data) at once

Regards
Harsh Desai
harshdesai
 
Posts: 24
Joined: Fri Feb 23, 2018 8:30 am

Fri Feb 14, 2020 4:19 am Change Time Zone

IMPORT mysql;
integer ExtractIt(String Query) := EMBED(mysql : user('rchapman'),database('test'),server('127.0.0.1'), port('3306'))
?
ENDEMBED;

Query := 'select count(1) from transaction_log' ;
ExtractIt(Query);




Even if there is query ,How to negotiate quote with escape sequence .

SELECT * FROM transaction_log WHERE account_number NOT IN ('10000001','10000024') AND batch_job_id IS NOT NULL AND SUBSTR(date_added, 1, 10) = SUBDATE(CURDATE(),1) ORDER BY date_added DESC;
harshdesai
 
Posts: 24
Joined: Fri Feb 23, 2018 8:30 am


Return to ECL

Who is online

Users browsing this forum: No registered users and 2 guests