How to write a text file with SQL Query

[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

[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"

[misery]

That worked perfectly Oleg! Thank you