2

is it possible to define some kind of macro/scripts in Aqua Data studio?

Basically, what I want to do: I have a folder containing several SQL-queries named xyz.sql, I want to run all of them and save them as xyz.csv

Is this somehow possible?

Thanks very much!

Willi Fischer
  • 455
  • 6
  • 21

1 Answers1

1

Aqua Data Studio has a feature called FluidShell which is an integrated shell, where you can use FluidShell commands to execute SELECT query and write it to File.

FluidShell Introduction
FluidShell Commands

A few of the command which would be useful to achieve your task would be
Executing SQL
[Source][4]

[4]: http://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation15/page/289/source

To test I created 2 tables named foo and bar under SQL Server database named Northwind under a schema named dbo and inserted a few records. Next, I created a file named xyz.sql and put below 2 lines and saved the file.

sqlexport -f csv -d Northwind -s dbo foo -o /Users/tariq/Desktop/stack1.csv
sqlexport -f csv -d Northwind -s dbo bar -o /Users/tariq/Desktop/stack2.csv

Then opened a fluidshell and used the source command source xyz.sql. Now the files were exported successfully to 2 files.

Two things which I can think of are :
A.) Contents in query.sql is SELECT * FROM dbo.Orders where ShipVia > 2 @
Then I use the source combined with tee to get the results with
source query.sql | \tee result.txt

B.) Contents in query2.sql is as below
select * from dbo.Orders where ShipVia > 2
\go > mydata.txt
Then at the FluidShell prompt use source query2.sql

tariq
  • 615
  • 5
  • 12
  • Thanks, I will have a look – Willi Fischer Nov 04 '14 at 12:44
  • I could run a query from the shell via `source xyz.sql`, but I did not find out how to save the output to the file I wanted. I think it has to be `sqlexport`, but it is not clear to me from the documentation how to combine the two. Can you please help? – Willi Fischer Nov 04 '14 at 19:22
  • Thanks, following your eyample I was now able to export some table from my DB. But how can I export the result of another query? Lets say I have `query.sql` containing `select * from foo`, how can I export this result set? – Willi Fischer Nov 07 '14 at 10:01
  • Thanks, both works fine! And also Thanks for your patience. The second solution is better for me because it doesn't contain the query, but only the results. Now, 1. how can I specify the directory where to save the file? if i write 'go > C:\...\mydata.txt', the file will still be stored in the original directory. – Willi Fischer Nov 10 '14 at 11:18
  • 2. The file is formated containing Headers, leading and trailing spaces and a bottom line like 'xyz many results'. I dont want any of these. I can post-process my files, but it would be cleaner to have it from the beginning – Willi Fischer Nov 10 '14 at 11:21
  • 1
    For #1, try to enclose the filename with quotes. Mine works for \go > "/Users/tariq/Documents/stackoverflow.txt" – tariq Nov 13 '14 at 00:49
  • Thanks! This allows me to store the files in child directories. How can I do it with parent directories or specify the whloe file path? – Willi Fischer Nov 13 '14 at 11:13
  • I have figured it out with `\..\..\` , but how do I go the root directory? – Willi Fischer Nov 13 '14 at 11:27