Page 1 of 1
How to write a text file with SQL Query
Posted: Sat Jun 14, 2014 8:39 pm
by misery
Hi Oleg,
I've created a table with MySQL called
name, and this table has many rows but only 3 fields (
name, coordinate1, coordinate2) each with their own data. I'm trying to Query this table (name) so that any results will be written to a text file. I'm not sure if Dataset Loop is needed for this or not.
I've successfully created an SQL Query with Connection String:
Code: Select all
Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Main;DESCRIPTION=Main Coordinates List;SERVER=localhost;UID=root;DATABASE=names;PORT=3306"
and in the "SQL Query" field I have:
Code: Select all
SELECT name, ( 6371 * acos( cos( radians(45.0843184) ) * cos( radians( coordinate1 ) )
* cos( radians( coordinate2 ) - radians(-64.398319) ) + sin( radians(45.0843184) ) * sin(radians(coordinate1)) ) ) AS distance
FROM name
HAVING distance < 25
ORDER BY distance
LIMIT 0 , 20;
The task executes successfully but I have no idea how to write all the results to a text file. Please help!
Thank you
Re: How to write a text file with SQL Query
Posted: Sun Jun 15, 2014 9:31 am
by Oleg
You should use "Dataset loop" for SELECT query.
You can create the text line by line in the loop and then save it into a file.
see the example below:
Code: Select all
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|1416408852
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task120"
Hide=INTEGER|0
ID=INTEGER|2054445370
LogOnAsUser=INTEGER|1
Name=STRING|"save the text from SQL query"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0
[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
[Actions\Action1]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""FILENAME"" with value ""{TempDir}\sqltext.txt"""
Params=FOLDER
[Actions\Action1\Params]
expand=STRING|"1"
varkind=STRING|"1"
varname=STRING|"FILENAME"
varvalue=STRING|"{TempDir}\sqltext.txt"
[Actions\Action2]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""ROW"" with value """""
Params=FOLDER
[Actions\Action2\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"ROW"
[Actions\Action3]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""TXT"" with value """""
Params=FOLDER
[Actions\Action3\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"TXT"
[Actions\Action4]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER
[Actions\Action4\Params]
commatext=STRING|"0"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DSN=Main;DESCRIPTION=Main Coordinates List;SERVER=localhost;UID=root;DATABASE=names;PORT=3306"""
override=STRING|"0"
password=STRING|"1928819338208742010719083"
sql=STRING|"""SELECT name, ( 6371 * acos( cos( radians(45.0843184) ) * cos( radians( coordinate1 ) ) "",""* cos( radians( coordinate2 ) - radians(-64.398319) ) + sin( radians(45.0843184) ) * sin(radians(coordinate1)) ) ) AS distance "",""FROM name"",""HAVING distance < 25 "",""ORDER BY distance "",""LIMIT 0 , 20;"""
timeout=STRING|"60"
var2=STRING|"ROW"
withmemo=STRING|"0"
[Actions\Action5]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""TXT"" with value ""{TXT}name={row(0)}; distance={row(1)}{eol}"""
Params=FOLDER
[Actions\Action5\Params]
expand=STRING|"1"
varname=STRING|"TXT"
varvalue=STRING|"{TXT}name={row(0)}; distance={row(1)}{eol}"
[Actions\Action6]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action7]
ActionID=STRING|"A_FILE_TEXTWRITE"
Enabled=INTEGER|-1
Name=STRING|"Create text file {filename}"
Params=FOLDER
[Actions\Action7\Params]
encode=STRING|"0"
fileexists=STRING|"0"
filname=STRING|"{filename}"
line0=STRING|"{txt}"
linecount=STRING|"1"
suppress=STRING|"0"
Re: How to write a text file with SQL Query
Posted: Mon Jun 16, 2014 3:28 am
by misery
That worked perfectly Oleg! Thank you